说明：本次试验接上次配置，《配置Mysql数据库的主从同步(一主一从)》，其中一主、一丛已经配置好。
已经配置好的：SourceByrd's Weblog-https://note.t4x.org/database/master-to-master/
主库：192.168.199.177
从库：192.168.199.178
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
[root@Master-Mysql ~]# egrep "server-id|log-slave|log-bin|auto_increment|slave-skip-errors" /etc/my.cnf log-bin = /usr/local/mysql/data/mysql-bin #必须 server-id = 1 #必须 log-slave-updates #必须 auto_increment_increment = 2 #必须 auto_increment_offset = 1 #必须 slave-skip-errors = 1032,1062,1007 #非必须，建议 ########################主库、从库分隔符######################## [root@Slave-Mysql data]# egrep "server-id|log-slave|log-bin|auto_increment|slave-skip-errors|read-only" /etc/my.cnf #log-bin = /usr/local/mysql/data/mysql-bin server-id = 2 log-slave-updates log-bin = /usr/local/mysql/data/mysql-bin #read-only #双主，此选项要注释掉 slave-skip-errors = 1032,1062,1007 auto_increment_increment = 2 #ID自增间隔 auto_increment_offset = 2 #ID初始位置 |
192.168.199.178:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> stop slave; mysql> flush table with read lock; mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+ mysql> system /usr/local/mysql/bin/mysqldump -uroot -p'' -A -B >/tmp/192.168.199.178.sql #如果主、从一致非必须 mysql> unlock tables; #同上 mysql> system ls -l /tmp/ -rw-r--r--. 1 root root 2887406 Jul 12 22:24 192.168.199.178.sql mysql> start slave; |
192.168.199.177:
|
0 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 |
[root@Master-Mysql ~]# /usr/local/mysql/bin/mysql -uroot -p'' < /tmp/192.168.199.178.sql #如果主、从一致非必须 mysql> update mysql.user set password=PASSWORD('admin') where user='root'; [root@Master-Mysql ~]# cat |/usr/local/mysql/bin/mysql -uroot -p'admin' <<EOF #必须 > CHANGE MASTER TO > MASTER_HOST='192.168.199.178', > MASTER_PORT=3306, > MASTER_USER='byrd', > MASTER_PASSWORD='admin', > MASTER_LOG_FILE='mysql-bin.000004', > MASTER_LOG_POS=120; > EOF mysql> start slave; mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.199.178 Master_User: byrd Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 938 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 1101 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_Errno: 0 Skip_Counter: 0 Exec_Master_Log_Pos: 938 Relay_Log_Space: 1275 Until_Condition: None Until_Log_Pos: 0 Master_SSL_Allowed: No Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_SQL_Errno: 0 Master_Server_Id: 2 Master_UUID: 34d672c3-d292-11e3-9ff5-00155dc7834c Master_Info_File: /usr/local/mysql/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 |
测试：
192.168.199.177:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql> use hitest; mysql> CREATE TABLE `ces` ( -> `REL_ID` bigint(12) NOT NULL auto_increment COMMENT 'id', -> `TITLE` varchar(255) NOT NULL COMMENT 'biaoti', -> PRIMARY KEY (`REL_ID`) -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; mysql> insert into ces(TITLE) values('test'); mysql> insert into ces(TITLE) values('test'); mysql> insert into ces(TITLE) values('test'); mysql> insert into ces(TITLE) values('test25'); mysql> select * from ces; +--------+-------+ | REL_ID | TITLE | +--------+-------+ | 1 | test | | 3 | test | | 5 | test | | 25 | test25| +--------+--------+ 3 rows in set (0.03 sec) |
192.168.199.178:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> use hitest; mysql> insert into ces(TITLE) values('test26'); mysql> insert into ces(TITLE) values('test28'); mysql> insert into ces(TITLE) values('test30'); mysql> select * from ces; +--------+--------+ | REL_ID | TITLE | +--------+--------+ | 1 | test | | 3 | test | | 5 | test | | 26 | test26 | | 28 | test28 | | 30 | test30 | +--------+--------+ 17 rows in set (0.00 sec) |
说明：如果一主、一丛已经做好，只要知道从库位置点（show master status;）、然后之前主库执行（CHANGE MASTER）、之前主库开启slave（start slave）即可。其中数据库备份等步骤可以省略，如果主从有一些数据库不一致则同上操作！SourceByrd's Weblog-https://note.t4x.org/database/master-to-master/
申明：本文由BYRD原创(基于Mysql5.6.16)，未经许可禁止转载！SourceByrd's Weblog-https://note.t4x.org/database/master-to-master/ SourceByrd's Weblog-https://note.t4x.org/database/master-to-master/
申明：除非注明Byrd's Blog内容均为原创,未经许可禁止转载!详情请阅读版权申明!
