MySQL的主从复制、读写分离、双机热备

MySQL的主从复制、读写分离、双机热备

准备

需要准备两个虚拟机，并且完成安装MariaDB/MySQL

下文都以我准备的为例：

系统环境：

名称	地址	操作系统	软件	地位
MariaDB A	192.168.116.132	CentOS7	mariadb-server-5.5.64-1	主
MariaDB B	192.168.116.133	CentOS7	mariadb-server-5.5.64-1	从

一、实现MySQL的主从复制

1.1 主从复制原理

MySQL数据库的主从复制方案，是其自带的功能，并且主从复制并不是复制磁盘上的数据库文件，而是通过binlog日志复制到需要同步的从服务器上。

MySQL数据库支持单向、双向、链式级联，等不同业务场景的复制。在复制的过程中，一台服务器充当主服务器（Master），接收来自用户的内容更新，而一个或多个其他的服务器充当从服务器（slave），接收来自Master上binlog文件的日志内容，解析出SQL，重新更新到Slave，使得主从服务器数据达到一致。

主从复制的逻辑有以下几种

一主一从，单向主从同步模式，只能在Master端写入数据

一主多从

双主主复制逻辑架构，此架构可以在Master1或Master2进行数据写入，或者两端同时写入（特殊设置）

在生产环境中，MySQL主从复制都是异步的复制方式，即不是严格的实时复制，但是给用户的体验都是实时的。MySQL主从复制集群功能使得MySQL数据库支持大规模高并发读写成为可能，且有效的保护了服务器宕机的数据备份

应用场景 ：

利用复制功能当Master服务器出现问题时，我们可以人工的切换到从服务器继续提供服务，此时服务器的数据和宕机时的数据几乎完全一致。复制功能也可用作数据备份，但是如果人为的执行drop,delete等语句删除，那么从库的备份功能也就失效了。

主从机制实现原理：

(1) master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；

(2) slave将master的binary log events拷贝到它的中继日志(relay log)；

(3) slave重做中继日志中的事件，将改变反映它自己的数据。

1.2 配置实现

（1）、打开A的数据库配置文件，/etc/my.cnf 进行编辑

#打开/etc/my.cnf
sudo vim /etc/my.cnf

配置详情：

#在[mysqld]下面添加如下配置内容
#master ID,主数据库一定写1且从数据库此处配置不能相同
server-id=1  
#指定binlog日志目录
log-bin=mysql-bin
#指定生成binlog日志的数据库（需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可）
binlog-do-db=test_slave
#不需要复制的数据库命，如果复制多个数据库，重复设置这个选项即可
binlog-ignore-db=test3

保存后重启数据库服务

#重启mariadb
sudo systemctl restart mariadb 

#若启动报错可使用下列语句查看报错信息
sudo grep 'ERROR' /var/log/mariadb/mariadb.log

重启完成后打开端口3306的防火墙配置，使之允许访问。

# 3306端口 mysql 默认端口
sudo firewall-cmd --zone=public --add-port=3306/tcp --permanent
# 重新加载规则
sudo firewall-cmd --reload

进入数据库查看bin-log和master状态 ：

show variables like "log_bin";
show master status;

创建用于数据同步的用户

-- 用于同步的用户save
-- 配置192.168.116.% 表示只有在116网段可以访问，提高安全性
grant replication slave on *.* to 'slave'@'192.168.116.%' identified by 'slave';
-- 查看用户和权限
select user,host from mysql.user;

建立测试用户（从库也一样）

-- 创建用户
CREATE USER 'test'@'%' IDENTIFIED BY 'test';
-- 授权
GRANT all ON *.* TO 'test'@'%';
flush privileges;

（2）、打开B的数据库配置文件，/etc/my.cnf 进行编辑

#打开/etc/my.cnf
sudo vim /etc/my.cnf

配置详情：

#在[mysqld]下面添加如下配置内容
#master ID,主数据库一定写1且从数据库此处配置不能相同
server-id=2 
#指定生成binlog日志的数据库（需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可）
binlog-do-db=test_slave
#不需要复制的数据库命，如果复制多个数据库，重复设置这个选项即可
binlog-ignore-db=test3

以root用户登录数据库B,执行下列sql

-- master_host 为master地址，master_user为进行同步的用户，master_log_file和master_log_pos可在master中输入sql--show master status;查看配置
change master to master_host='192.168.116.132', master_port=3306, master_user='slave', master_password='slave', master_log_file='mysql-bin.000004', master_log_pos=245;
-- 开启同步
start slave;
-- 查看同步状态
show slave status\G

1.3测试

• 在A上新建数据库test_slave

-- 新建test_slave数据库
CREATE DATABASE `test_slave`;
--查看
SHOW databases;

在B上查看test_slave是否被同步过来

--查看
SHOW databases;

数据库test_slave同步成功

• 在A中的test_slave中新建表test

-- 使用数据库test_slave
USE test_slave;
-- 创建表test
CREATE TABLE `test_slave`.`Untitled`  (
  `name` varchar(255) NULL,
  `id` int(0) NULL
);
-- 查看数据库下的表
SHOW tables;
-- 查看表结构
DESC test;

在B中进行查看

-- 使用数据库test_slave
USE test_slave;
-- 查看数据库下的表
SHOW tables;
-- 查看表结构
DESC test;

B与A完全一致表test同步成功

• 给A中的test_slave数据库中的test表插入一条数据

-- 给test插入一条数据
INSERT INTO test (name,id) VALUES ('张',1);
-- 查看
SELECT * FROM test;

出现乱码，查看表的字符集

-- 查看test表编码
SHOW CREATE TABLE test;

-- 查看表内字段编码
SHOW FULL COLUMNS FROM test;

-- 修改表字符集为utf-8
ALTER TABLE `test_slave`.`test` CHARACTER SET = utf8, COLLATE = utf8_general_ci;
-- 修改字段字符集为utf-8
ALTER TABLE `test_slave`.`test` 
MODIFY COLUMN `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL FIRST;

再次添加，进行查看

-- 给test插入一条数据
INSERT INTO test (name,id) VALUES ('张',2);
-- 查看
SELECT * FROM test;

同样在B中进行查看

-- 查看
SELECT * FROM test;

结果相同

在A中将乱码数据删除

DELETE FROM test WHERE id = 1;
-- 再次查看
SELECT * FROM test;

查看B中数据

SELECT * FROM test;

删除也可成功同步

注意事项:

1、在进行同步配置前，若主数据库中有数据库test_slave，而从数据库中无test_slave，则配置完同步后数据无法进行同步，且从库会报找不到数据库的错误

未完待更新

参考

[1]: https://blog.51cto.com/andyxu/2162383 "Centos7配置mysql主从：一主多从”

[2]: https://www.cnblogs.com/pyyu/p/9467289.html “mysql+centos7+主从复制”