MYSQL 数据库同步主从配置

本文将介绍 MYSQL 数据库同步的相关配置。

主-服务器创建同步用户
创建一个登录名为 rsync 密码为 123456 的同步帐户。

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'rsync'@'%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;

主-数据库备份
这里仅将 db1 db2 数据库备份出来。

/www/wdlinux/mysql/bin/mysqldump -u root -h 127.0.0.1 -p --lock-all-tables --databases db1 db2 > /www/200110_trans.sql

这里仅将 db1 db2 数据库备份出来，另外会删除旧的数据库、表、单次更新 200M，并显示备份详情。

mysqldump -uroot -p --lock-all-tables --extended-insert=true --add-drop-database --add-drop-table -v --max-allowed-packet=100M --databases db1 db2> /www/200110_trans.sql

主-服务器 mysql 配置 [mysqld] 项中增加
这里只同步 db1，db2，忽略 db3 和 db4 的同步。保存好配置文件后，重启服务。

log-bin=mysql-bin
binlog_format=mixed
server-id	= 1
replicate-do-db=db1
replicate-do-db=db2
binlog_ignore_db=db3
binlog_ignore_db=db4

从-数据库还原
将主服务器的备份上传到从服务器上，以便进行还原。

/www/wdlinux/mysql/bin/mysql -u root -h 127.0.0.1 -p < /www/wwwroot/200110_trans.sql

从-服务器 mysql 配置 [mysqld] 项中增加
保存好配置文件后，重启服务。

server-id	= 2

主-服务器上查看位置信息
记录下结果中的File（文件名）、Position（位置）。

SHOW MASTER STATUS;

从-服务器上执行同步操作

stop slave;
reset slave;
change master to
master_host='主服务器地址',
master_user='同步用户名',
master_password='同步密码',
master_port=3306,
master_log_file='文件名',
master_log_pos=位置;
start slave;

从-服务器查看状态

show slave status;

查看 Slave_IO_Running、Slave_SQL_Running 项是否为 Yes。
如果存在 No，请查看从服务器 mysql 日志，

如果有 “Could not find first log file name in binary log index file” 的错误，则在主服务器上运行 SQL：

flush logs;

再运行，

SHOW MASTER STATUS;

确定新的文件名和位置。之后，重新在从服务器上填写它。

至此，MYSQL 主从数据库配置完毕。

从-服务器报错
[ERROR] Slave I/O for channel '': Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). Error_code: 1593

解决方法：在从服务器 mysql 配置文件中，查找 server-id，确定是否存在多个，该值不能为1，且不等于其他从服务器 ID。

Slave I/O: error connecting to master 'sync_user@sync_host:3306' - retry-time: 60 retries: 3, Error_code: 1045

解决方法：连接同步服务器失败，检查同步用的用户名或密码。

从-服务器 Slave_SQL_Running No
Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

报错信息为从库“无法读取 relay log 里的条目”，可能原因为 master 库的 binglog 错误，或 slave 库的中继日志错误。或者为网络问题及 bug 原因。
一般是由于网络故障或 slave 库压力过大，导致 relay-log 格式错误造成的。找到当前已经同步的时间点，重新设置主从同步，就会产生新的中继日志，恢复正常。

输入 SQL ：

SHOW SLAVE STATUS;

查询从服务器同步进度。留意 Relay_Master_Log_File、Exec_Master_Log_Pos 值。
如：

Relay_Master_Log_File: mysql-bin.000037 //slave库已读取的 master 的 binlog
Exec_Master_Log_Pos: 3870 // 在slave上已经执行的 position 位置点

以在设置的位置点重新同步。

再次同步：

stop slave;
change master to master_log_file='mysql-bin.000037' , master_log_pos=3870;
start slave;

MySQL Replication 主从复制全方位解决方案

学会用各种姿势备份MySQL数据库

MySQL案例09:Last_IO_Error: Got fatal error 1236 from master when reading data from binary log

'Could not find first log file name in binary log index file'的解决办法

MySQL数据同步，出现Slave_SQL_Running：no和slave_io_running：no问题的解决方法

MySQL同步故障：" Slave_SQL_Running:No" 两种解决办法

相关文章：