MySql的复制

复制的作用

1. 读写分离，分担读负载
2. 分库分表时，数据转移
3. 实现高可用架构时，故障自动切换(failover)，避免单点故障造成系统不可用

复制并不等于备份

主从复制的延迟较小，主库被删除后，从库也会很快没有了， 所以备份要单独进行

基于日志的复制原理

基于主库的二进制日志，日志记录了主库DDL（对表结构的创建、修改、删除操作）、DML（对记录的增删改操作）操作，异步增量传输到从库，让从库重现二进制日志记录的操作

1. master将变更写入二进制日志中
2. slave开辟单独的I/O线程跟master建立TCP连接（master会建立一个转储线程与之响应）读取master的二进制日志的变更（首次全量复制，之后是master有新的变更后通过信号告诉slave可以读取）并写入到本地的中继日志(relay_log)中
3. slave的sql线程读取中继日志relay_log，重现master上的数据操作

开启基于行ROW格式的二进制日志(基于行复制的主从节点表结构要一致)

配置my.cnf

mkdir /usr/local/mysql/all-logs
vim /usr/local/mysql/my.cnf
server-id=1
log_bin=/usr/local/mysql/all-logs/mysql-bin
binlog_format=ROW
binlog_row_image=MINIMA

基于日志点的复制配置步骤

1. 在Master上建立mysql的复制用户账号并授权

   create user repluser@"192.168.16.%" identified by "repluser123";
   grant replication slave on *.* to repluser @"192.168.16.%";

2. 配置Master开启二进制日志

mkdir /usr/local/mysql/all-logs
chown mysql:mysql  /usr/local/mysql/all-logs
chmod -R 755 /usr/local/mysql/all-logs
vim /usr/local/mysql/my.cnf
server-id=1
log_bin=/usr/local/mysql/all-logs/mysql-bin
binlog_format=ROW
binlog_row_image=MINIMA

3. 配置Slave的relaylog等

mkdir /usr/local/mysql/all-logs
chown mysql:mysql  /usr/local/mysql/all-logs
chmod -R 755 /usr/local/mysql/all-logs
vim /usr/local/mysql/my.cnf
server-id=2
log_bin=/usr/local/mysql/all-logs/mysql-bin
binlog_format=ROW
binlog_row_image=MINIMA
relay_log=/usr/local/mysql/all-logs/mysql-relay-bin
read_only=on
log_slave_update=on

log_slave_update开启后可以当做其他从节点的主节点
read_only=on设置只读，但是在mysql5.7之前就算在Slave的my.cnf中设置了read_only=on,有权限的用户仍然可以执行写操作成功
配置完my.conf记得重启mysql

4. 初始化Slave的数据
通过master的备份数据库还原到从库
慎用mysqldump备份会枷锁，阻塞当前mysql运行的性能
首先，在Master上执行数据导出

/usr/local/mysql/bin/mysqldump --single-transaction --master-data --triggers --routines --all-databases -uroot -p >> all.sql

对于所有的表都是InnoDB时可以用xtrabackup备份，不会造成枷锁

然后，将master的数据拷贝到在slave上，执行数据导入完成数据初始化

mysql -uroot -p < all.sql

5. 在Slave中执行启动复制的命令

进入mysql：
首先,执行change

change master to master_host = "192.168.16.11",
	master_user="repluser",
	master_password="repluser123",
	master_log_file="mysql-bin.000001",
	master_log_pos=440;

master_log_file和master_log_pos的值取自刚才导出的主库数据all.sql里写明的CHANGE MASTER TO MASTER_LOG_FILE这行信息，代表从库是从主库的哪个二进制文件记录的哪个数据偏移量开始复制

**然后，执行start slave;开启复制**

 start slave;

6.查看主从复制状态
在Master上可以看到为每个从节点建立了dump线程

show processlist\G

在Slave上可以看到为复制而生的IO线程和SQL线程

show processlist\G

show slave status \G;

常见错误

Error executing row event: 'Cannot execute statement: impossible to write to binary loformat and BINLOG_FORMAT = STATEMENT.'

原因:主从节点的二进制日志格式不一致，
解决办法:统一配置所有节点my.cnf的binlog_format同一个值,最好是ROW

Could not execute Write_rows event on table project1.t_users; Duplicate entry ‘6’ for 062; handler error HA_ERR_FOUND_DUPP_KEY; the event’s master log mysql-bin.000001, en

原因：从库执行了写操作且成功了，因为主从数据不一致导致这台从库复制失败，在mysql5.7之前就算在Slave的my.cnf中设置了read_only=on,有权限的用户仍然可以执行写操作成功
解决：一般保留主库的数据，跳过本次复制错误，从库重新从主库复制,具体操作如下

stop slave;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;