MySQL主从同步、主主同步配置

MySQL的 Replication 是一个异步的复制过程,从一个 MySQL Instance( Master)复制到另一个 MySQL Instance(Slave)。在 Master 与 Slave 之间的实现整个复制过程主要由三个线程来完成,其中两个线程(SQL线程和IO线程)在 Slave 端,另外一个线程(IO线程)在 Master 端。

软件环境:CentOS7,MySQL5.6.37 
服务器:192.168.1.100(Master),192.168.1.101(Slave)

1、配置192.168.1.100(Master)my.cnf

vim /etc/my.cnf
log-bin=mysql-bin #打开二进制日志(必须)
server-id=100 #设置唯一id,任意数。
binlog-do-db=test_db #设置要复制的数据库,多个数据库重复设置
#不需要复制的数据库,多个数据库重复设置,一般设置了bindlog-do-db,就不需要设置这个了。
binlog-ignore-db=mysql

注意:如果要实现复杂的主从同步:比如说,A->B->C,其中B是A的从服务器,同时B又是C的主服务器,那么B服务器除了需要打开log-bin之外,还需要打开log-slave-updates选项,可以在B上使用“show variables like ‘log%’;”来确认是否已经生效。

2、配置192.168.1.101(Slave)my.cnf

server-id=101
replicate-do-db=test_db 
log-bin=mysql-bin

3、在Master服务器中创建一个Slave可以登录的用户

GRANT ALL PRIVILEGES ON *.* TO 'abswolf'@'192.168.1.101' IDENTIFIED BY 'abswolf' WITH GRANT OPTION;

4、保持主从数据库的test_db数据库状态一致

数据量小的话可以用mysqldump,它有一个master-data参数很有用,通过使用此参数,导出的SQL文件里会自动包含CHANGE MASTER TO MASTER_LOG_FILE=’…’, MASTER_LOG_POS=…;,这样创建从服务器就更方便了。如果数据量大的话不太适合使用mysqldump(慢),如果是myisam表的话,加上–lock-all-tables参数,如果是innodb表的话,加上–single-transaction参数。
先在主服务器上锁定所有的表,以免在复制过程中数据发生变化:

mysql> flush tables with read lock;

然后在主服务器上查询当前二进制文件的文件名及偏移位置:

mysql > show master status;

然后停止主服务器上的MySQL服务:

shell> mysqladmin -u root shutdown

注意:如果仅是MyISAM的话,可以不停止MySQL服务,但要在复制数据文件的过程中保持只读锁,如果是InnoDB的话,必须停止MySQL服务。
再拷贝数据文件:

shell> tar -cvf /tmp/mysql-snapshot.tar .

拷贝完别忘了启动主服务上的MySQL服务了。
然后把数据文件应用到从服务器上,再次启动slave的时候使用,记得启动时加上skip-slave-start选项,使之不会立刻去连接master。

5、在Master服务器上执行命令mysql>show master status

+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000001 | 120 | test_db | | |
+——————+———-+————–+——————+——————-+
1 row in set (0.00 sec)
记录File的名字和Position,下面从服务器上设置需要用到。

6、在Salve服务器上设置相关的二进制日志信息

mysql> stop slave;
mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.1.100',
    -> MASTER_USER='abswolf',
    -> MASTER_PASSWORD='abswolf',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=120;
mysql> start slave; #开启Salve数据库复制功能
mysql> show slave status\G #查看是否开启成功,

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.100
Master_User: abswolf
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如果看到Slave_IO_Running和Slave_SQL_Running都是Yes的话,表示配置正确。

7、主主同步

主主同步就是两台数据库的数据进行变更的时候,另一台也会进行相应的变更。那么我们只需要把两个主从配置和起来就可以。需要不同的是一些主键的配置,分别在两台服务器的my.cnf配置文件中加上如下配置:

auto_increment_increment=2   #步进值auto_imcrement。一般有n台主MySQL就填n
auto_increment_offset=1   #起始值。一般填第n台主MySQL。此时为第一台主MySQL

然后在192.168.1.101中设置Master的日志信息。参考上面的步骤反过来做即可。

8、注意事项 参考(转)mysql主从同步 binlog-do-db replicate-do-db

应该保证从服务器上任何数据的修改都是通过从主服务器上复制操作获取的,换句话说,从服务器应该是只读的,如果不能保证这一点,则可能造成主从数据不一致。
可以在从服务器的my.cnf里加入read-only参数来实现这一点,唯一需要注意的一点事read-only仅对没有super权限的用户有效。所以最好核对一下连接从服务器的用户,确保其没有super权限。

从理想角度看,主从数据库应该无故障的运转下去,可以有时候还是会出现一些莫名其妙的问题,比如说即便从未在从服务器上手动更新过数据,但还是可能遇到“Error: 1062 Duplicate entry”错误,具体原因不详,可能是MySQL本身的问题。遇到这类问题的时候,从服务器会停止复制操作,我们只能手动解决问题,具体的操作步骤如下:

mysql> set global sql_slave_skip_counter = 1;
mysql> start slave;

同样的操作可能需要进行多次,也可以设置自动处理此类操作,在从服务器的my.cnf里设置: slave-skip-errors=1062

最后再唠叨一下日志的问题:时间长了,数据库服务器上的二进制文件会越来越多,清理是必要的,你可以设置自动清理,相关参数是expire_logs_days,也可以使用手动删除的方式,但这里说的手动不是指rm,而是指PURGE BINARY LOGS,删除任何日志前,最好在所有的从服务器上通过show slave status命令确认一下相关日志是否已经无用。

补充:[ERROR] Error in Log_event::read_log_event(): ‘Event too big’ 在使用主从复制的时候,出现的问题多半是和日志(主服务器的二进制日志,从服务器的延迟日志)相关的。比如说加入你遇到了上面的错误,你可以根据错误日志的信息在主从数据库服务器上分别执行:

mysqlbinlog 日志文件 > /dev/null 查看错误,如果没有错误,则不会有任何输出,反之会输出错误信息,如果确定了错误是出现在主服务器二进制日志上,可以跳过适当的位置,再在从服务器上重新设定LOG_POS,如果确定了错误是出现在从服务器延迟日志上,则可以删除从服务器的延迟日志(使用CHANGE TO MASTER的时候,除非设定了延迟日志信息,否则会自动删除延迟日志),并在从服务器上重新设定LOG_POS。期间也可以考虑手动执行不能自动执行的SQL日志。

补充:配置的时候如果版本允许最好打开sync_binlog选项。

补充:有时候,从服务器延迟日志可能已经损坏,这时需要执行CHANGE MASTER TO设置新的日志文件信息,但是在从服务器上SHOW SLAVE STATUS会显示很多日志信息,他们的含义有所不同: Master_Log_File:Read_Master_Log_Pos 是IO相关的日志信息 Relay_Master_Log_File:Exec_Master_Log_Pos 是SQL相关的日志信息 从服务器需要设置的是SQL相关的日志信息: slave stop; change master to master_log_file=’(binlog name in relay_master_log_file)’, master_log_pos=(exec_master_log_pos number); slave start; 1) When you are using the master as a consistent snapshot, use SHOW MASTER STATUS to determine the position. 2) When you are using a slave as a consistent snapshot, use SHOW SLAVE STATUS and Exec_Master_Log_Pos.

MySQL 5.7 并行复制实现原理与调优

# slave
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

show master status empty

问题出现在做Mysql的主主复制或者主从复制的时候,查看Master日志的使用命令:

mysql>show master status;
Empty set (0.00 sec)

解决方法:
1、如果是从yum install mysql的话,在/etc/my.cnf配置文件中的[mysqld] 增加一行:
log-bin=mysql-bin
2、找到log.bin的位置,并在配置文件/etc/my.cnf中指定:
log-bin=/var/lib/mysql/log-bin.log

重启数据库。

mysql> show master status;
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000005 | 1234552 | | | |
+——————+———-+————–+——————+——————-+
1 row in set (0.00 sec)