MySQL主从复制与主主复制详述

147次阅读

共计 3392 个字符，预计需要花费 9 分钟才能阅读完成。

主从复制:

1. 环境说明:

准备两台 linux 虚拟主机
Linux 版本 CentOS7、MySQL 5.7.17
ip：192.168.1.1、192.168.1.2

2. 创建复制账号

mysql 会赋予一些特殊的权限给复制线程, 在备库运行的 I / O 线程会建立一个到主库 TCP/IP 连接, 这意味着必须在主库创建一个用户, 并赋予其合适的权限, 备库 I / O 线程以该用户名连接到主库并取其二进制日志;(摘自: 高性能 mysql => 复制)

在 192.168.1.1 中创建一个 192.168.1.2 主机中可以登录的 MySQL 用户
//192.168.1.1 : 主数据库服务器:

mysql>GRANT REPLICATION SLAVE ON *.* TO 'tongbu'@'192.168.1.2' IDENTIFIED BY 'tongbu';
mysql>FLUSH PRIVILEGES;

注意 1: 这里我把这个账号限制在本地并且只有 192.168.1.2 这个主机可以访问, 因为这是一个特殊权限的账号(尽管这个账号无法执行 select 或者 update), 但是任然能从二进制日志中获取一些数据(摘自: 高性能 mysql => 复制)

3. 配置主库和备库

MySQL 配置文件都在: /etc/my.cnf

1. 在主库的 `my.cnf` 文件的 `[mysqld]` 下添加:log-bin,server_id(具体如下:)

log-bin=mysql-bin # 开启二进制日志
server_id=10 # 0-232 任取 但必须和其他数据库中的不同  (必须唯一)!

注意：二进制日志必须开启，因为数据的同步实质上就是其他的 MySQL 数据库服务器将这个数据变更的二进制日志在本机上再执行一遍。

2. 保存后重启 mysql

3. 查看主库中 MySQL 服务器二进制文件名与位置

mysql>SHOW MASTER STATUS;

注意: 这里重点关注 File 和 Position 这两项;

4. 告知二进制文件名与位置

在备库中执行：

mysql>CHANGE MASTER TO
    >MASTER_HOST='192.168.1.1',
    >MASTER_USER='tongbu',
    >MASTER_PASSWORD='tongbu',
    >MASTER_LOG_FILE='mysql-bin.000001',
    >MASTER_LOG_POS=98;

完成主从复制配置

5. 开启并查看

在备库中执行:

mysql>START SLAVE;   # 开启复制
mysql>SHOW SLAVE STATUS\G   # 查看主从复制是否配置成功

注意: 当看到 Slave_IO_Running: YES、Slave_SQL_Running: YES 才表明状态正常(如有异常请看 3. 关于报错中提供的解决方案!)

主主复制:

1. 设置配置文件(参考上面)

–192.168.1.1

server-id=11   # 任意自然数 n，只要保证两台 MySQL 主机不重复就可以了。
log-bin=mysql-bin   # 开启二进制日志
auto_increment_increment=2   # 步进值 auto_imcrement。一般有 n 台主 MySQL 就填 n 
auto_increment_offset=1   # 起始值。一般填第 n 台主 MySQL。此时为第一台主 MySQL
binlog-ignore=mysql   # 忽略 mysql 库【我一般都不写】
binlog-ignore=information_schema   # 忽略 information_schema 库【我一般都不写】
replicate-do-db=aa   # 要同步的数据库，默认所有库

–192.168.1.2

server-id=12
log-bin=mysql-bin
auto_increment_increment=2
auto_increment_offset=2
replicate-do-db=aa

配置好后重启 MySQL

2. 创建复制账号并配置二进制文件名与位置

第一步：
在 192.168.1.1 中创建一个 192.168.1.2 主机中可以登录的 MySQL 用户

mysql>GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.1.2' IDENTIFIED BY 'root';
mysql>FLUSH PRIVILEGES;

第二步：
在 192.168.1.1 查看二进制日志名和位置

mysql>show master status;

在 192.168.1.2 中执行：

mysql>CHANGE MASTER TO
    >MASTER_HOST='192.168.1.1',
    >MASTER_USER='root',
    >MASTER_PASSWORD='root',
    >MASTER_LOG_FILE='mysql-bin.000001',
    >MASTER_LOG_POS=98;

第三步：
在 192.168.1.2 中创建一个 192.168.1.1 主机中可以登录的 MySQL 用户

mysql>GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.1.1' IDENTIFIED BY 'root';
mysql>FLUSH PRIVILEGES;

第四步：
在 192.168.1.2 查看二进制日志名和位置

mysql>show master status;

在 192.168.1.1 中执行：

mysql>CHANGE MASTER TO
    >MASTER_HOST='192.168.1.2',
    >MASTER_USER='root',
    >MASTER_PASSWORD='root',
    >MASTER_LOG_FILE='mysql-bin.000001',
    >MASTER_LOG_POS=98;

3. 完成 mysql 主主复制

分别开启 START SLAVE;

mysql>START SLAVE;   # 开启复制
mysql>SHOW SLAVE STATUS\G   # 查看主从复制是否配置成功

注意: 当看到两台主机的 Slave_IO_Running: YES、Slave_SQL_Running: YES 才表明状态正常

3. 关于报错

1. 主从同步报错 Fatal error: The slave I/O thread stops because master and slave have equal MySQL server
原因分析：
mysql 5.6 的复制引入了 uuid 的概念，各个复制结构中的 server_uuid 得保证不一样，但是查看到直接 copy data 文件夹后 server_uuid 是相同的，show variables like‘%server_uuid%’;
解决方法：
找到 data 文件夹下的 auto.cnf 文件，修改里面的 uuid 值，保证各个 db 的 uuid 不一样，重启 db 即可

场景二：创建主从关系时 copy 了同样的 my.cnf 文件，报错
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids;
原因分析：
和 server_uuid 类似，servier_id 也得保证不一样
解决方法：
找到 my.cnf 配置文件中的 server_id，修改从库的 server_id 保证和复制结构中的其他 db 不一样，重启 db 即可

1、主主复制配置文件中 auto_increment_increment 和 auto_increment_offset 只能保证主键不重复，却不能保证主键有序。

2、当配置完成 Slave_IO_Running、Slave_SQL_Running 不全为 YES 时，show slave status\G 信息中有错误提示，可根据错误提示进行更正。

3、Slave_IO_Running、Slave_SQL_Running 不全为 YES 时，大多数问题都是数据不统一导致。

常见出错点：

1、两台数据库都存在 db 数据库，而第一台 MySQL db 中有 tab1，第二台 MySQL db 中没有 tab1，那肯定不能成功。

2、已经获取了数据的二进制日志名和位置，又进行了数据操作，导致 POS 发生变更。在配置 CHANGE MASTER 时还是用到之前的 POS。

3、stop slave 后，数据变更，再 start slave。出错。

终极更正法：重新执行一遍 CHANGE MASTER 就好了。

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-09/146676.htm

正文完

星哥说事-微信公众号