配置MySQL主从复制（一主一从）

前言

MySQL主从复制简介

MySQL主从复制的目的是实现数据库冗余备份，将master数据库的数据定时同步到slave库中，一旦master数据库宕机，可以将Web应用数据库配置快速切换到slave数据库，确保Web应用有较高的可用性。

MySQL主从同步是一个异步复制的过程，要实现复制，首先需要在master上开启bin-log日志功能，bin-log日志用于记录在master库执行的增删改更新操作的sql语句，整个过程需要开启3个线程，分别是master开启I/O线程，slave开启I/O线程和SQL线程。

主从复制大致流程

• slave上执行slave start，slave I/O线程会通过在master创建的授权用户连接到master，并请求master从指定的文件和位置发送bin-log日志内容。
• master接收到slave的IO线程请求后，master的IO线程根据slave发送的指定bin-log日志position点之后的内容，然后返回给slave的IO线程。
• 返回的信息中除了bin-log日志内容外，还有master最新的bin-log文件名以及在bin-log中的下一个指定更新position点。
• slave的IO线程接收到信息后，将接收到的日志内容依次添加到slave端的relay-log文件的末端，并将读取到的master端的bin-log文件名和position点记录到master.info文件中，以便在下一次读取的时候能告知master从相应的bin-log文件名及最后一个position点开始发起请求
• slave SQL线程检测到relay-log中内容有更新，会立刻解析relay-log的内容，将解析后的SQL语句在slave中执行，执行成功后主库和从库数据一致。

准备

• 主节点IP：192.168.0.11
• 从节点IP：192.168.0.10
• 待同步的数据库：test

步骤

配置主节点

1. vim /etc/my.cnf

2. 在[mysqld]字段中添加：

log-bin=master-bin
binlog-format=ROW
server-id=1

3. 重启mysqld：

systemctl restart mysqld

4. 登入mysql，创建用于同步的账户tongbu，注意修改IP段和密码：

create user 'tongbu'@'192.168.%' identified by '123456';

5. 授权，注意修改IP段和密码：

grant replication slave on *.* to 'tongbu'@'192.168.%';

6. 查看master状态：

# 注意File字段和Position字段
show master status;

配置从节点

1. vim /etc/my.cnf

2. 在[mysqld]字段中添加：

log-bin=master-bin
binlog-format=ROW
server-id=2

3. 重启mysql
4. 登入mysql，配置从库连接主库，注意修改主库的IP，用于同步的账户名和密码，master_log_file为show master status;中的File值，master_log_pos为show master status;中的Position值

change master to master_host='192.168.0.11',master_user='tongbu',master_password='123456',master_log_file='master-bin.000001',master_log_pos=154;

5. 启用从库：

start slave;

6. 查看从库状态

# 注意看 Slave_IO_Running和Slave_SQL_Running是否为yes
show slave status\G

测试

在主库创建一个数据库：create database test123;

在数据库中新建一个表：create table t0(id int,name varchar(20));

查看从库有没有新建的表

只同步部分库或表

方式一：限制master端

在/etc/my.cnf中添加：binlog-do-db=test（多个数据库用逗号隔开，不加这一行默认同步所有库）

方式二：限制slave端

在/etc/my.cnf中添加：replicate-do-db=test

指定需要同步的表：replicate-do-table=test.test_table

解决主从复制问题

如果发生网络异常导致主从同步错误，或者在已有数据基础上搭建主从复制，需要解决数据不一致问题。

主从数据差别不大时的不同步问题

1. master端执行：flush tables with read lock;，将数据库设置为全局读锁。
2. slave端：
   1. 停止IO及SQL线程，stop slave;
   2. 将同步错误的SQL跳过一次，set global sql_slave_skip_counter=1;
   3. 启动slave，start slave;
3. master端执行：unlock tables;解锁。

主从数据差别很大时的不同步问题

1. master端执行：flush tables with read lock;，将数据库设置为全局读锁。
2. 使用mysqldump、mysqlpump、xtrabackup等工具对master数据库进行完整备份
3. 将完整数据导入到从库
4. 重新配置主从关系
5. master端执行：unlock tables;解锁。

主键冲突，错误代码1062

情况：从库上执行show slave status\G，报错last_error:1062，sql线程已停止工作。

原因：从库上执行了写操作，然后在主库上执行了相同的SQL语句，主键冲突，主从复制状态就会报错。

解决：

1. 利用perconna-toolkit工具中的py-slave-restart命令在从库跳过错误（因为主从库有相同的数据）。

主库更新数据，从库找不到而报错，错误代码1032

在从库执行delete删除操作，再在主库执行更新操作，由于从库已经没有该数据，导致主从数据不一致。

解决方法：在从库执行show slave status，根据错误信息所知道的binlog文件和position号，在主库上通过mysqlbinlog命令查找在主库执行的哪条SQL语句导致的主从报错。把从库上丢失的这条数据补上，然后执行跳过错误。如果从库丢失了很多数据，需要考虑重新配置主从环境。