文章目录
一、概述
MySQL进行实时数据同步，不会回放已经执行的导入数据库脚本。所以同步开始前，两端的数据要保持一致。
-
数据同步：
- 主从复制（主库挂掉，从库不会自动变为master，应用场景：读写分离）
- 主主复制（并发高容易主键冲突）
-
实现原理：
软件版本
mysql5.7.20
二、配置主从
1.修改配置文件
Linux : /etc/my.cnf
windows : mysql.ini
-
主库配置
[mysqld] # 数据库ID号， 为1时表示为Master,其中master_id必须为1到232–1之间的一个正整数值，主从server-id不能一样;[必须]服务器唯一ID，默认是1，一般取IP最后一段（主从都设置成各自IP最后一段） server-id=1 # 主库启用二进制日志，从库非必须； log-bin=mysql-bin # 表示每次事务提交，MySQL都会把binlog刷到磁盘上，是最安全但是性能损耗最大的设置。 sync-binlog=1 auto_increment_increment=1 #步长 auto_imcrement。一般有n台主MySQL就填n auto_increment_offset=1 #起始值。一般填第n台主MySQL。此时为第一台主MySQL # 需要同步数据库名，同步多个数据库，复制即可 （主库设置，从库不用设置）是指定binlog日志记录那些库的二进制日志。 binlog-do-db=wr # binlog_format = MIXED //binlog日志格式，mysql默认采用statement，建议使用mixed # log-bin = /data/mysql/mysql-bin.log //binlog日志文件 # expire_logs_days = 7 //binlog过期清理时间 # max_binlog_size = 100m //binlog每个日志文件大小 # binlog_cache_size = 4m //binlog缓存大小 # max_binlog_cache_size = 512m //最大binlog缓存大小重启MySQL
mysql restart -
从库配置
[mysqld] # 数据库ID号， 为1时表示为Master,其中master_id必须为1到232–1之间的一个正整数值，主从server-id不能一样;[必须]服务器唯一ID，默认是1，一般取IP最后一段（主从都设置成各自IP最后一段） server-id=2 # 主库启用二进制日志，从库非必须； log-bin=mysql-bin # 表示每次事务提交，MySQL都会把binlog刷到磁盘上，是最安全但是性能损耗最大的设置。 sync-binlog=1 auto_increment_increment=1 #步进值auto_imcrement。一般有n台主MySQL就填n auto_increment_offset=1 #起始值。一般填第n台主MySQL。此时为第一台主MySQL # 在slave库中指定同步那些库的binlog日志。 默认所有？ replicate-do-db=wr # 官方说明：https://dev.mysql.com/doc/refman/5.7/en/replication-options-replica.html # binlog_format = MIXED //binlog日志格式，mysql默认采用statement，建议使用mixed # log-bin = /data/mysql/mysql-bin.log //binlog日志文件 # expire_logs_days = 7 //binlog过期清理时间 # max_binlog_size = 100m //binlog每个日志文件大小 # binlog_cache_size = 4m //binlog缓存大小 # max_binlog_cache_size = 512m //最大binlog缓存大小重启MySQL：
mysql restart查看日志模式：
默认： mysql> show variables like '%binlog_format'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.00 sec) mysql参数：binlog-do-db和replicate-do-db http://blog.sina.com.cn/s/blog_747f4c1d0102w9pp.html
2.构建主从复制
-
在主库创建一个从库可以登录的专门用户同步的账户
mysql>CREATE USER 'sync_dba'@'%' IDENTIFIED BY '<password>'; mysql>GRANT REPLICATION SLAVE ON *.* TO 'sync_dba'@'%' IDENTIFIED BY '<password>'; mysql>FLUSH PRIVILEGES; -
在从库指定主库信息
-
在主库查看主库信息
mysql>show master status; -
在从库指定主库信息
mysql>CHANGE MASTER TO MASTER_HOST='192.168.0.1', MASTER_PORT=60001, MASTER_USER='sync_dba', MASTER_PASSWORD='<password>', MASTER_LOG_FILE='loganalysis.000002', MASTER_LOG_POS=194; # MASTER_LOG_POS ： 同步点（同步点和端口是数值类型） -
启动从服务器复制功能
# 建库无法复制 mysql>start slave; -
查看主从复制是否配置成功
mysql>show slave status\G; # \G为按行垂直显示结果 # Master_Log_File，Read_Master_Log_Pos 记录了IO thread读到的当前master binlog文件和位置， 对应master的binlog文件和位置。 Master_Log_File: loganalysis.000006 Read_Master_Log_Pos: 194 # Relay_Log_File，Relay_Log_Pos记录了SQL thread执行到relay log文件和位置，对应的是slave上的relay log文件和位置。 Relay_Log_File: weileyi-officeapp.000016 Relay_Log_Pos: 322 # Relay_Master_Log_File，Exec_Master_Log_Pos记录的是SQL thread执行到master binlog的文件和位置，对应的master上binlog的文件和位置。 Relay_Master_Log_File: loganalysis.000004 # Slave_IO_Running & Slave_SQL_Running == Yes ===> 主从复制正常 Slave_IO_Running: Yes # 从库与主库IO通信 : 正常运行 Slave_SQL_Running: Yes # 从库进程 ： 正常运行
-
三、异常
-
异常：在从库指定主库信息
- 现象：
ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.-
原因：
已绑定过。
-
解决：
# 停止已启用绑定 stop slave; # 从库：重置绑定 reset master; reset slave; # 重新指定主库信息 CHANGE MASTER TO MASTER_HOST='192.168.0.1', MASTER_PORT=60001, MASTER_USER='sync_dba', MASTER_PASSWORD='blockchain@2019', MASTER_LOG_FILE='loganalysis.000001', MASTER_LOG_POS=154; # 重新启动复制 start slave;
-
异常：Slave_IO_Running: No
-
现象：
Slave_IO_Running: No -
原因：
从库Master_Log_File不对应主库的File。
-
1.查看主库
mysql> show master status\G; *************************** 1. row *************************** File: loganalysis.000006 Position: 194 Binlog_Do_DB: cbei_core Binlog_Ignore_DB: Executed_Gtid_Set: 705fa506-ddfa-11ea-99c4-fa163e6d1441:1-183 1 row in set (0.00 sec) -
查看从库
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.2 Master_User: sync_dba Master_Port: 60001 Connect_Retry: 60 Master_Log_File: loganalysis.000006 # 对应主库File Read_Master_Log_Pos: 194 # 对应主库Position Relay_Log_File: adam-officeapp.000016 Relay_Log_Pos: 322 Relay_Master_Log_File: loganalysis.000004 Slave_IO_Running: No Slave_SQL_Running: No
-
-
解决：
mysql> slave stop; mysql> change master to MASTER_LOG_FILE='loganalysis.000006', MASTER_LOG_POS=0; mysql> slave start; mysql> show slave status\G;
-
-
异常：Slave_SQL_Running: No
-
现象：
Slave_SQL_Running: No -
原因：
从库上进行了写操作或者重启，事务回滚造成记录File和Position对应的值和主库不同。
Master Slave File master_log_file Position master_log_pos -
解决：
# 重新指定 mysql> stop slave; # 重新指定主库信息 mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.1', MASTER_PORT=60001, MASTER_USER='sync_dba', MASTER_PASSWORD='blockchain@2019', MASTER_LOG_FILE='loganalysis.000006', MASTER_LOG_POS=194; # 重新启动复制 mysql> start slave; mysql> show slave status\G;
-
-
异常：主从状态正常却无法同步。
-
现象：
Slave_IO_Running: Yes # 从库与主库IO通信 : 正常运行 Slave_SQL_Running: Yes # 从库进程 ： 正常运行状态正常，但是从库不同步。
-
原因：
1.从库日志回放位置是主库最新位置。
2.主库空间问题，日志被截断。
-
解决：
-
Slave
# 停止从库同步 mysql> stop slave; -
Master
-
导出数据
-
清理日志
# 查看主库信息 mysql> show master status\G; # 主库重新创建一个binlog文件，Position还原。 # mysql> flush logs; # 清除所有binlog文件，并创建binlog.000001 mysql> reset master;
-
-
Slave
指定MASTER_LOG_FILE和MASTER_LOG_POS;
# 清除所有binlog文件，并创建binlog.000001 mysql> reset master; # 重新指定主库信息 mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.1', MASTER_PORT=60001, MASTER_USER='sync_dba', MASTER_PASSWORD='blockchain@2019', MASTER_LOG_FILE='loganalysis.000001', MASTER_LOG_POS=154; # 重新启动复制 mysql> start slave; mysql> show slave status\G; -
Master
- 导入数据
-
-
-
异常：从机找不到日志文件。
-
现象：
MySQL [(none)]> show slave status\G; 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' -
原因：
BINLOG目录中.index日志索引文件中没有.000001号索引文件。
-
解决：
清除BINLOG中所有文件，然后重启MySQL。
reset slave;无法清除BINLOG，直接在磁盘删除。
-