Mysql主主复制配置

[MySQL Replication 主从复制全方位解决方案] https://www.cnblogs.com/clsn/p/8150036.html

https://www.cnblogs.com/moss_tan_jun/p/6614032.html

 

mysql主主复制配置：

HOSTNAME IPADDR PORT
节点1:my-prod01.oracle.com 192.168.10.97 3306
节点2:my-prod02.oracle.com 192.168.10.5 3306

1、修改my.cnf文件
节点1:
[mysqld]
server_id = 1
datadir = /data/mysql_3306/data
port = 3306
socket = /data/mysql_3306/run/mysql.sock
log-error=/data/mysql_3306/logs/mysqlerror.log
log-bin = /data/mysql_3306/logs/binlog
relay-log = /data/mysql_3306/logs/relaylog
binlog_format = row
skip-name-resolve
sync_binlog = 1
log_slave_updates = 1
read_only = 0

auto-increment-increment = 2

auto-increment-offset = 1

 

 

 

节点2:
[mysqld]
server_id = 2
datadir = /data/mysql_3306/data
port = 3306
socket = /data/mysql_3306/run/mysql.sock
log-error=/data/mysql_3306/logs/mysqlerror.log
log-bin = /data/mysql_3306/logs/binlog
relay-log = /data/mysql_3306/logs/relaylog
binlog_format = row
skip-name-resolve
sync_binlog = 1
log_slave_updates = 1
read_only = 0

auto-increment-increment = 2
auto-increment-offset = 2

双主复制模式
auto_increment_offset表示自增长字段从哪个数开始,取值范围是1 .. 65535
auto_increment_increment表示自增长字段每次递增的量,其默认值是1,取值范围是1 .. 65535
在主主同步配置时,需要将两台服务器的auto_increment_increment增长量都配置为2,而要把auto_increment_offset分别配置为1和2.
这样避免两台服务器同时做更新时自增长字段的值之间发生冲突

log-slave-updates
log-slave-updates这个参数用来配置从服务器的更新是否写入二进制日志，这个选项默认是不打开的，但是，如果这个从服务器B是服务器A的从服务器，同时还作为服务器C的主服务器，那么就需要开发这个选项，这样它的从服务器C才能获得它的二进制日志进行同步操作

 

从3306中导出数据

[root@master mysqlData]# mysqldump --defaults-file=/etc/my.cnf -uroot -p --master-data=2 --single-transaction --set-gtid-purged=off --log-error=all.log -A > d:\mysql\all.sql

-A 导出全库
--single-transaction 来保证备份的一致性
--master=2 可以得到binlog和pos的值

 

导入到3307库中

[root@slave mysqlData]# mysql -uroot -p

mysql> source d:\mysql\all.sql

mkdir -p /data/mysql_3306/data/logs/binlog
mkdir -p /data/mysql_3306/data/logs/relaylog

 

创建复制权限用户：
节点1:
mysql> GRANT replication slave ON *.* TO 'replicator'@'192.168.10.5' identified by 'replicator';
mysql> flush privileges;
mysql> show master status \G
*************************** 1. row ***************************
File: binlog.000001
Position: 120
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.03 sec)

节点2:
mysql> GRANT replication slave ON *.* TO 'replicator'@'192.168.10.97' identified by 'replicator';
mysql> flush privileges;
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status \G
*************************** 1. row ***************************
File: binlog.000001
Position: 120
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.03 sec)

互为master:
节点1:
change master to master_host='192.168.10.5', master_port=3306, master_user='replicator',master_password='replicator', master_log_file='binlog.000001',master_log_pos=120;

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.5
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 120
Relay_Log_File: binlog.000002
Relay_Log_Pos: 280
Relay_Master_Log_File: binlog.000001
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: 120
Relay_Log_Space: 444
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: 98b6f1e4-7026-11e7-b381-e8611f1a5e44
Master_Info_File: /data/mysql_3306/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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:
Auto_Position: 0
1 row in set (0.00 sec)

节点2:

change master to master_host='192.168.10.97', master_port=3306, master_user='replicator',master_password='replicator', master_log_file='binlog.000001',master_log_pos=120;

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.97
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 120
Relay_Log_File: binlog.000002
Relay_Log_Pos: 280
Relay_Master_Log_File: binlog.000001
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: 120
Relay_Log_Space: 444
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: 1
Master_UUID: cd2fab5f-7025-11e7-b37c-e8611f1a5ff8
Master_Info_File: /data/mysql_3306/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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:
Auto_Position: 0
1 row in set (0.00 sec)

#需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库

#不需要同步数据库,比如这里的mysql系统数据库

解除主从复制关系
MariaDB [mysql]> stop slave;
QueryOK, 0 rowsaffected (0,00 sec)
MariaDB [mysql]> reset slave all;
QueryOK, 0 rowsaffected (0,04 sec)
MariaDB [mysql]> show slave status\G;
Emptyset (0,00 sec)
RESET MASTER
功能说明：删除所有的binglog日志文件，且binlog从000001 开始记录，并将日志索引文件清空，重新开始所有新的日志文件。用于第一次进行搭建主从库时，进行主库binlog初始化工作；

但是如果当前是主数据库，且主从数据库正常的时候，千万不能用这种方式删除。

RESET SLAVE
功能说明：用于删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件；

RESET SLAVE将使SLAVE忘记主从复制关系的位置信息。该语句将被用于干净的启动, 它删除master.info文件和relay-log.info 文件以及所有的relay log 文件并重新启用一个新的relaylog文件。

使用场景：当原来的主从关系被破坏之后，从库经过重新初始化后直接连接会报 ERROR 1201的错误，运行reset slave后，需要重新配置主从连接(重新执行change master)；

slave_io_running 和 slave_sql_running 的状态必须是 yes，否则主从就出错了！