MySql主从复制：异步复制

目录

一、Master配置

二、Slave配置

三、简单排错

四、常用命令与参数配置

---

主库将编辑（非查询）操作写入到bin log中，从库负责读取主库的bin log，并在本地复制一份到relay log中，然后将relay log中的操作在从库执行一遍。

每个从库需要配置读取主库日志的文件名和日志位置，否则会执行已经存在数据的操作，从而导致异常。

主库和从库都必须有一个唯一的server_id,在my.cnf中配置。

一、Master配置

1、在my.cnf中开启bin log并设置server-id：vi /etc/my.cnf

[mysqld]
log-bin=mysql-bin  #设置mysql binlog的文件名前缀，如果不设置则默认为hostname-bin,如果修改了主机名，binlog文件也会随之改变，所以建议自己定义log-bin的文件名
server-id=11  #在主从库中保持唯一，取值范围为正整数uint32

     重启数据库：systemctl restart mysqld;

2、关闭主库my.cnf中的skip-networking参数，否则会导致主从库之间不能通讯。【默认可省略】

3、查看bin-log 开启状态：show variables like '%log_bin%';

     查看server-id配置是否成功：show variables like '%server%';

4、添加一个用于同步的主库用户，此用户只需要有复制权限：

     创建用户： create user '用户名'@'%' identified by '密码';

     赋予权限： GRANT REPLICATION SLAVE ON *.* TO '用户名'@'%';

5、设置主库为只读，不允许配置期间有任何的数据库修改操作：FLUSH TABLES WITH READ LOCK;

6、获取主库bin log日志信息，并记录下文件名称（file）和位置信息(position)：show master status;

7、将当前的整个数据库备份还原到从库上：备份还原：mysqldump  或  mysql备份还原：文件迁移

      备份数据库：mysqldump -u root -p --databases test > /data/mysql/test.sql

      使用sftp传输文件过去：

                                    连接从库服务器：  sftp 192.168.119.130

                                    传输文件：  put /data/mysql/test.sql /data/mysql/test.sql
              在从库中登录mysql并执行命令来还原数据库：source /data/mysql/test.sql

8、做好数据库备份之后就可以释放只读锁了：UNLOCK TABLES;

二、Slave配置

1、配置从库my.cnf的server-id，不做级联复制不必开启bin log：vi /etc/my.cnf

       server-id=12

       重启mysql：systemctl restart mysqld;

2、指定主库信息

CHANGE MASTER TO
MASTER_HOST = '主库IP',
MASTER_PORT = 主库端口,
MASTER_USER = '主库登录用户名',
MASTER_PASSWORD = '主库登录密码',
MASTER_LOG_FILE = '主库bin log日志文件名（配置Master第6步记录下的）',
MASTER_LOG_POS = 主库bin log文件位置（配置Master第6步记录下的）;

 

3、启动从库的复制进程：start slave;

4、查看复制状态：show slave status\G   

       看到slave_io_running和slave_sql_running皆为yes就成功了，否则查看last_error来排错

     5、在主库中修改数据，并在从库中查看。

三、简单排错

1、连接错误

a、将my.cnf文件中的以下命令取消注释，如果没有此命令则加上：

           default-authentication-plugin=mysql_native_password 

b、重启Mysql服务: systemctl restart mysqld; 

      启动从库复制进程：start slave;

      重新查看状态：show slave status\G   

 2、其他错误

3、当从节点遇到无关紧要的错误，可以直接跳过：

STOP SLAVE;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

START SLAVE; 

四、常用命令与参数配置

命令	描述
show slave status	检查从库slave复制的状态，是否正常。 Slave_IO_State：代表当前slave的状态； Slave_IO_Running：负责读取主库bin log的IO线程是否运行； Slave_SQL_Running：负责执行备库relay log的SQL线程是否运行； Last_IO_Error：IO线程最后一次发生的错误信息; Last_SQL_Error：SQL线程最后一次发生的错误信息; Seconds_Behind_Master：备库的SQL线程比主库的bin log晚多少秒，0代表木有复制延迟； SQL_Delay：人工设置的主从复制延迟时间；
show processlist	查看MySql线程信息。
stop slave	停止slave复制
stop slave IO_THREAD 或SQL_THREAD	停止IO或者SQL执行线程
start slave	启动slave复制
start slave IO_THREAD 或SQL_THREAD	启动IO或者SQL执行进程

参数	描述	使用
replicate-do-db	指定需要复制的数据库。	在my.cnf配置文件中使用：replicate-do-db=库名 多个数据库需要把这个参数写多份。
replicate-do-table	指定需要复制的表	在my.cnf配置文件中使用：replicate-do-db=库名.表名
replicate-ignore-table	指定不需要复制的表，把它过滤掉	在my.cnf中使用：replicate-ignore-db=库名.表名
replicate-wild-do-table	复制符合匹配的表，可以使用_和%作为通配符	在my.cnf中使用：replicate-wild-do-table=库名.表名%
replicate-wild-ignore-table	排除符合匹配的表，可以使用_和%作为通配符	在my.cnf中使用：replicate-wild-ignore-table=库名.表名%
binlog-format	binlog日志格式，有以下三种格式； row：基于行，更安全但数据量更大 statement：基于sql语句 mixed：以上两者的混合使用	在my.cnf中使用：binlog-format=row
slave-parallel-workers	在slave上启用多个sql thread线程来并行应用数据。默认为0。取值范围0-1024	在my.cnf中使用：slave-parallel-workers=3
skip-slave-start	在mysql启动时是否先不启动slave线程，暂停复制	在my.cnf中使用：skip-slave-start=1
slave-parallel-type	启动并行之后，采用什么粒度的并行方式： database：按不同的数据库并行，默认的。 logical_clock：按binlog中一组提交的事务。	在my.cnf中使用：slave-parallel-type=database
slave-skip-errors	当slave的sql thread执行过程中可以忽略的错误，并继续执行数据复制，正常情况下当有错误发生的时候会停止复制，人工处理后才能继续。	在my.cnf中使用： slave-skip-errors=errcode1,errcode2,.... slave-skip-errors=all slave-skip-errors=ddl_exist_errors
binlog-do-db	指定被记录到binlog的数据库	在my.cnf中使用：binlog-do-db=库名
change master to master_delay	从库复制的延迟时间（秒）	在从库my.cnf中使用：change master to master_delay=60