MySQL数据库主从同步

主从配置

准备两台服务器或者虚机，这里两台服务器的IP用127.0.0.1（主）和127.0.0.2（从）代替。

1、修改数据库配置（/etc/mysql/my.cnf）

配置好之后重启数据库。

127.0.0.1

[mysqld]
log-bin=mysql-bin
server-id=1
replicate-do-db=db_name
log_slave_updates
slave-skip-errors=all

127.0.0.2

[mysqld]
log-bin=mysql-bin
server-id=2
replicate-do-db=db_name
log_slave_updates
slave-skip-errors=all

参数注释：

参数	注释
log-bin	log-bin文件路径，开启二进制日志
server-id	设置server-id，必须唯一
replicate-do-db	设置同步的数据库
log_slave_updates	从库作为其他从库的主库时（主主同步）一定要添加，不作主库可不加
slave-skip-errors	跳过错误，视情况而定是否使用该参数

备注： [mysqld] 这个头如果配置文件中没有，别忘记加，否则会报错。

2、重启后，登录数据库进行账号授权

具体分配的权限可自行选择

mysql> CREATE USER 'sync'@'127.0.0.2' IDENTIFIED BY 'slavepassword';# 创建用户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'sync'@'127.0.0.2';# 分配权限
mysql> flush privileges;   # 刷新权限

3、127.0.0.1查看master状态，二进制文件名(mysql-bin.000001)和位置(1528)

mysql > show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 1528     |              |                  |
+------------------+----------+--------------+------------------+

4、127.0.0.2执行同步SQL语句

mysql > CHANGE MASTER TO MASTER_HOST='127.0.0.1',MASTER_USER='sync',MASTER_PASSWORD='slavepassword',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1528;

参数注释：

参数	注释
MASTER_HOST	主库IP
MASTER_USER	账号
MASTER_PASSWORD	密码
MASTER_LOG_FILE	主库二进制文件名
MASTER_LOG_POS	位置

5、启动slave同步进程并查看slave状态

mysql> start slave;

mysql> show slave status\G;

Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: sync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.00001
Read_Master_Log_Pos: 1528
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Slave_IO_Running和Slave_SQL_Running状态均为YES的时候就表示主从同步设置成功了。

如果需要配置成主主同步，则将127.0.0.1服务器再关联到127.0.0.2即可

mysql > CHANGE MASTER TO MASTER_HOST='127.0.0.2',MASTER_USER='sync',MASTER_PASSWORD='slavepassword',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1528;

错误总结

在配置过程中也会出现一些错误，在这里做下总结。

1、io running 连接失败

Slave_IO_Running: Connecting

这个情况是连接不到主库，可以从以下几点进行排查。
(1)、服务器安全组端口权限
(2)、防火墙
(3)、账号权限
(4)、ipv4 转发未启用（出现的概率较小，这种情况不只是主从无法连接，除服务器自己以外，其他远程连接均会失败）

(4.1)、可通过如下方式检测IP转发是否启用

sysctl net.ipv4.ip_forward

如果结果返回为0，说明转发未启用。

(4.2)、启用IP装发

sysctl -w net.ipv4.ip_forward=1

(4.3)、也可设置永久转发，修改 /etc/sysctl.conf 文件，添加或修改以下行

net.ipv4.ip_forward = 1

(4.4)、应用更改

sysctl -p

2、error connecting to master ‘sync@127.0.0.1:3306’ - retry-time: 60 retries: 1 message: Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection.

是主库sync的plugin是caching_sha2_password 导致连接不上，修改为mysql_native_password即可。

ALTER USER 'sync'@'127.0.0.2' IDENTIFIED WITH mysql_native_password BY 'slavepassword';

3、Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

# 分别在主从库输入命令查看对应的UUID
show variables like '%server_uuid%';

UUID重复造成的，这个问题正常不会出现，一般都是使用克隆的主机或者镜像时才会出现UUID相同。
如果出现了修改主从其中一台机器的UUID即可。

修改方法：找到auto.cnf文件，修改UUID或者删除文件。

# 查找auto.cnf文件
find -name auto.cnf

修改或删除后重启Mysql服务

4、Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’

应该是bin-log日志的错误，重新刷新下主库的日志，

flush logs;

刷新日志后重新修改,MASTER_LOG_FILE，MASTER_LOG_POS参数，再重新CHANGE MASTER。
如果依然报同样的错误可是试试重置

reset slave;

5、Slave_IO_Running和Slave_SQL_Running状态均为YES，但同步不成功。

首先要保证主库的从库的数据相同，另外在修改my.cnf有一个参数，之前提到过的。slave-skip-errors使用该参数可以解决，该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况。

6、主从库意外重启

mysql意外重启后再次启动slave可能会出现无法启动等情况。

mysql> reset slave;

reset之后即可重新CHANGE MASTER，关联同步。

以上仅为个人在配置过程中遇到的问题与解决方法，仅供参考。