前言
上一篇学着在Linux下编译安装Mysql5.7.9,刚好有时间,在此基础上搭建一下Mysql的主从、主主同步!
环境
Master:
Linux发行版本: Oracle Linux Server release 6.5
Os版本: 3.8.13-98.4.1.el6uek.x86_64
Host: 192.168.1.253
Slave:
Linux发行版本: CentOS release 6.6 (Final)
Os版本:2.6.32-504.el6.x86_64
Host: 192.168.1.225
Slave数据库安装
参见:Linux编译安装Mysql5.7.9 | 57°极客闷烧
Master配置
在my.cnf中添加如下配置:
1 2 3 4 5 6 7 8 9 10
| [mysqld] server-id=1 log-bin=mysql-bin binlog_format="ROW" max_binlog_size = 500M binlog_cache_size = 128K binlog-do-db = xwy binlog-ignore-db = mysql log-slave-updates expire_logs_day=2
|
参数说明:
server-id:
主从复制中标识不同的主机
log-bin:
开启二进制日志,默认存在Mysql数据文件目录下,文件名以此为前缀,如mysql-bin.000001;默认大小1G
binlog_format:
日志格式,有SATEMENT、ROW、MIXED三个选项,对应复制模式:SBR、RBR、MBR.详细说明可参考:MYSQL复制参数binlog_format | Dong‘s Note
max_binlog_size:
每个bin-log最大大小,当此大小等于500M时会自动生成一个新的日志文件。一条记录不会写在2个日志文件中,所以有时日志文件会超过此大小。
binlog_cache_size:
日志缓存大小
binlog-do-db:
需要同步的数据库名字,如果是多个,就以此格式在写一行即可。
binlog-ignore-db:
不需要同步的数据库名字,如果是多个,就以此格式在写一行即可。
log-slave-updates:
当Slave从Master数据库读取日志时更新新写入日志中,如果只启动log-bin 而没有启动log-slave-updates则Slave只记录针对自己数据库操作的更新。
expire_logs_day:
设置bin-log日志文件保存的天数,此参数mysql5.0以下版本不支持。
Slave配置
在my.cnf中添加如下配置:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
| [mysqld] server-id=2
log-bin=mysql-bin binlog_format=ROW
binlog_cache_size = 4M max_binlog_size = 500M max_binlog_cache_size = 500M sync_binlog = 1 replicate-do-db = xwy replicate-ignore-db = mysql replicate_do_table=t_xwy replicate_ignore_iable=t_test master-connect-retry=30 slave-skip-errors = 1062 expire_logs_days = 3 skip-slave-start=1 slave_net_timeout=20 slave_parallel_workers = 0 read-only =0
relay-log=/home/tools/mysql/data/relay.log max_relay_log_size = 500M
relay_log_purge = 1 relay_log_recovery = 1
binlog_checksum = CRC32 slave_allow_batching = 1 master_verify_checksum = 1 slave_sql_verify_checksum = 1 binlog_rows_query_log_events = 1
log_slave_updates
|
参数说明:
binlog-row-image:
这个选项允许应用程序只能对行的镜像数据进行复制,而不在关心行是否已经进行了DML操作。这提高了主从机器的复制吞吐量,减少了二进制日志所占用的磁盘空间、网络资源和内存占用。
replicate-do-db:
复制的db
replicate-ignore-db:
忽略复制的db
replicate_do_table:
设定需要复制的Table
replicate_ignore_iable:
设定可以忽略的Table
master-connect-retry:
这个选项控制重试间隔,默认为60秒。
slave-skip-errors:
同步过程中忽略掉的错误,如果有多个用逗号隔开。这些错误不会影响数据的完整性,有事经常出现的错误,一般设置忽略。其中1062为主键重复错误。
expire_logs_days:
设置bin-log日志文件保存的天数,此参数mysql5.0以下版本不支持。
skip-slave-start:
动从库但不立即启动从库的复制进程,以便对从库进行进一步的设置。
slave_net_timeout:
设置网络超时时间,即多长时间测试一下主从是否连接,默认为3600秒。
slave_parallel_workers:
默认是0,不开启,最大并发数为1024个线程。主从复制启用4个sql线程,提高从服务器吞吐量,减少延迟,使用并发的 SQL 线程对不同数据库并行应用事件,如果只同步一个库的,指定为0,否则会阻塞。
read-only:
设置Slave为只读,但具有super权限的用户仍然可写。主主复制时必须关闭。
log_slave_updates:
该参数用来控制Slave上的更新是否写入二进制日志,默认为0;若Slave只作为从服务器,则不必启用;若Slave作为其他服务器的Master,则需启用,启用时需和log-bin、binlog-format一起使用,这样Slave从主库读取日志并重做,然后记录到自己的二进制日志中.
Mater创建复制用户
1
| mysql> GRANT REPLICATION SLAVE on *.* to 'slave'@'%' IDENTIFIED BY '123456';
|
Mater备份数据
锁表
1
| mysql> flush tables with read lock;
|
获取日志及偏移量
1 2 3 4 5 6
| mysql> show master status; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000020 | 194 | | | 44c86870-999c-11e5-b608-08002718c663:1-3 | +------------------+----------+--------------+------------------+------------------------------------------+
|
备份
1 2
| >/home/mysql/data/dbback.sql;
|
恢复写操作
Slave同步
恢复数据
1
| mysql -uroot -pmyroot </home/mysql/data/dbback.sql
|
同步设置
1 2 3 4 5 6 7 8
| mysql> slave stop; mysql> reset slave; mysql> change master to -> master_host='192.168.1.253', -> master_user='slave', -> master_password='123456', -> master_log_file='mysql-bin.000020', -> master_log_pos=194;
|
启动同步线程
查看slave状态
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
| mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.225 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000020 Read_Master_Log_Pos: 154 Relay_Log_File: new-relay-bin.000006 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000020 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 154 Relay_Log_Space: 785 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_UUID: aaf502d8-a886-11e5-a976-08002787c6af Master_Info_File: /home/mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 44c86870-999c-11e5-b608-08002718c663:1-3 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name:
|
说明:
Slave_IO_Running――此进程负责从Slave从Master上读取binlog日志,并写入Slave上的中继日志。
Slave_SQL_Running――此进程负责读取并执行中继日志中的binlog日志。
这两个进程的状态需全部为YES,只要有一个为NO,则复制就会停止。
当Slave中Relay_Master_Log_File和Master_Log_File相同且Read_Master_Log_Pos和Exec_Master_Log_Pos完全相同时,则表明Slave和Master处于完全同步的状态。
测试
1 2 3 4 5 6 7 8 9
| Master上: create database xwy; create table xwy.test (id int,name varchar(200)); insert into xwy.test values (1,'xwy'); insert into xwy.test values (2,'xwy'); select * from xwy.test; Slave上: use xwy; select * from xwy.test;
|
观察两边结果是否一致。
Slave修改启动开启同步进程
注释掉配置文件my.cnf中的skip-slave-start=1 or skip-slave-start=true
添加新的Slave
原理跟添加第一台Slave一样,修改配置文件my.cnf中的server-id=3,就可以啦。
主主复制
只需要将Slave配置中相关参数添加到Master中即可。注意因为双方需要复制更新,因此记得关闭my.cnf中的read-only =1为read-only =0,并开启log_slave_updates(slave更新写入bin-log).
常见错误及处理
问题1
现象
在从库上面show slave status\G;显示:
Slave_IO_Running: Yes
Slave_SQL_Running: No
Seconds_Behind_Master: NULL
原因
**a.**程序可能在slave上进行了写操作;
**b.**也可能是slave机器重起后,事务回滚造成的;
**c.**有可能是在同步过程中遇到某种错误,这个会在查看从库中状态时看到错误提示,最少见的就是主键重复1062的错误。
解决方法
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
| Master: mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000021 | 102 | | | 44c86870-999c-11e5-b608-08002718c663:1-3 | +------------------+----------+--------------+------------------+------------------------------------------+
Slave: mysql> slave stop; mysql> change master to -> master_host='192.168.1.253', -> master_user='slave', -> master_password='123456', -> master_log_file='mysql-bin.000021', -> master_log_pos=102; mysql> slave start;
|
问题2
现象
从数据库无法同步
在从库上面show slave status\G;显示:
1 2 3 4
| show slave status\G; Slave_IO_Running: No Slave_SQL_Running: Yes Seconds_Behind_Master: NULL
|
原因
这个现象主要是master数据库存在问题,由于连接主库信息错误、主库数据库挂掉如果说常见错等原因引起的,我在实际的操作中先重启master后重启slave即可解决这问题,出现此问题,必须要要重启master数据库。
解决
首先查看数据库的err日志,查看是什么错误提示,看从库连接主库的IP、用户、密码等相关信息是否有误,如果有误,重新执行同步;如果确认无误,重启主数据库。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
| Master: mysql> show master status; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000001 | 94 | | | 44c86870-999c-11e5-b608-08002718c663:1-3 | +------------------+----------+--------------+------------------+------------------------------------------+ Slave: mysql> slave stop; mysql> change master to -> master_log_file='mysql-bin.000001', -> master_log_pos=94; mysql> slave start; 或: mysql> slave stop; mysql> set global sql_slave_skip_counter =1; mysql> slave start;
|
参考
Mysql数据库主从心得整理 | Sina App Engine Blog
MySQL同步参数Seconds_Behind_Master=0 并不能确定主从是否延时 – WEB·攻城志
mysql relay log参数汇总-wzq609-ITPUB博客
mysql5.7.9主从同步配置 - tanweii163的专栏 - 博客频道 - CSDN.NET