mysql主从复制实战

[TOC]

准备工作

1、主+从：保证主从MySQL版本一致

SELECT VERSION();

2、主库：为从库创建复制专用账户

该账户必须授予 REPLICATION SLAVE 权限

CREATE USER 'dba_repl'@'%' IDENTIFIED BY 'MA6RuouuZZn4x_Hd';
GRANT SELECT,RELOAD,FILE,SUPER,REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'dba_repl'@'%';
FLUSH PRIVILEGES;

3、主库：启用binlog并配置serverid

[mysqld]
server-id=1001
log-bin=mysql-bin

注意：主库的server_id应介于1到2^32-1。如果没有这些选项，启用binlog后重启mysql服务。

4、从库：启用binlog及relaylog并配置serverid

[mysqld]
server-id=1002
log-bin=mysql-bin
relay-log=mysql-relay-bin

注意：从库的server_id值也应介于1到2^32-1，从库的 server-id 不能与主库相同；

5、主库：备份数据库

• 备份所有库：添加 --set-gtid-purged=off 可取消gtid属性（若配置GTID复制则不可取消gtid属性）

mysqldump -h 192.168.0.11 --single-transaction --master-data=2 --flush-logs --triggers --events --routines --skip-tz-utc --set-gtid-purged=off -uroot -p --all-databases > /data/alldb.20190822.sql

• 备份指定库：若只对指定库配置主从同步，则不需要备份所有库；

mysqldump -h 192.168.0.11 --single-transaction --master-data=2 --flush-logs --triggers --events --routines --skip-tz-utc --set-gtid-purged=off -uroot -p --databases db1 db2 > /data/2db.20190822.sql

6、主库：拷贝dump文件到从库服务器

• 实测发现，scp比rsync快，且不丢失大小。

scp -r -P1618 -l 16000 /data/alldb.20190822.sql 192.168.0.12:/data/

主从配置过程

点位复制

1、从库：导入主库的dump文件

从库> mysql -h 192.168.0.12 -uroot -p < /data/alldb.20190822.sql

• 遇到的问题：若主库启用了gtid，同时dump的时候没有取消gtid属性，则从库导入备份之前应该先执行 reset master; 否则会报如下错误：

报错：ERROR 1840 (HY000) at line 33: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
处理：reset master;

• [x] 加快导入速度的几个参数

set global innodb_flush_log_at_trx_commit=0;
set global sync_binlog=500000;
show variables like 'sync_binlog';

2、从库：从备份文件中获取点位信息

cat /data/alldb.20190822.sql | grep "CHANGE MASTER"

3、从库：根据实际情况修改如下参数，并执行 start slave; 启动同步

CHANGE MASTER TO MASTER_HOST='x.x.x.x',MASTER_PORT=3406,MASTER_USER='dba_repl',MASTER_PASSWORD='MA6RuouuZZn4x_Hd',MASTER_LOG_FILE='mysql-bin.xxx',MASTER_LOG_POS=xxx;
START SLAVE;
SHOW SLAVE STATUS \G;

4、从库：确认 Slave_IO_Running 及 Slave_SQL_Running 均为 yes，则表示相关进程运行无报错，待 Seconds_Behind_Master 变为 0 之后，则表示主从已完全同步。

从库> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
             Slave_IO_Running: Yes    #.看这里
            Slave_SQL_Running: Yes    #.看这里
        Seconds_Behind_Master: 2403   #.看这里

• x.优化：禁用skip-slave-start（需重启mysql服务以生效），slave进程会随着mysql启动而启动

[root@localhost ~]# cat /data/mysql_3306/my_3306.cnf | grep "skip-slave-start"
skip-slave-start=0