mysql双主同步

zz775520666

Mysql 5.6.23
CENTOS 6.4 X86
目标: 两个MYSQL主机进行数据同步
NODE1:192.168.59.30
NODE2:192.168.59.31
配置先简单
NODE1配置:
 
Step1. Change /etc/my.cnf
 
[mysqld]
 
log-error=/var/log/mysql/mysql.log
 
auto_increment_increment=2
auto_increment_offset=1
 
server-id=1
log-bin=mysql-bin
binlog_format=ROW
 
 
Step2. Login in Mysql
 
mysql -uroot -padmin
 
mysql> grant replication slave on *.* to 'replication'@'%' identified by 'slave';
 
mysql> change master to master_host='192.168.59.231',
    -> master_user='replication',
    -> master_password='slave',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=0;
Query OK, 0 rows affected, 2 warnings (0.11 sec)
 
 
mysql> start slave;
 
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |      351 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
 
mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.23-log, Binlog ver: 4 |
| mysql-bin.000001 | 120 | Stop        |         1 |         143 |                                       |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
2 rows in set (0.01 sec)
 
　　mysql> show slave status\G;
　　*************************** 1. row ***************************
　　Slave_IO_State: Waiting for master to send event
　　Master_Host: 192.168.59.231
　　Master_User: replication
　　Master_Port: 3306
　　Connect_Retry: 60
　　Master_Log_File: mysql-bin.000002
　　Read_Master_Log_Pos: 815
　　Relay_Log_File: localhost-relay-bin.000005
　　Relay_Log_Pos: 283
　　Relay_Master_Log_File: mysql-bin.000002
　　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: 815
　　Relay_Log_Space: 623
　　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: a491626c-d107-11e4-9b3d-000c29305dd3
　　Master_Info_File: /var/lib/mysql/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)
　　
　　ERROR: 
　　No query specified
　　
 
bin log files default path: /var/lib/mysql/
 
 
NODE2配置:
 
Step1. Change /etc/my.cnf
 
[mysqld]
 
log-error=/var/log/mysql/mysql.log
 
auto_increment_increment=2
auto_increment_offset=2
 
server-id=2
log-bin=mysql-bin
binlog_format=ROW
 
 
Step2. Login in Mysql
 
mysql -uroot -padmin
 
mysql> grant replication slave on *.* to 'replication'@'%' identified by 'slave';
 
mysql> change master to master_host='192.168.59.230',
    -> master_user='replication',
    -> master_password='slave',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=0;
Query OK, 0 rows affected, 2 warnings (0.11 sec)
 
mysql> start slave;
 
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |      351 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
 
mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.23-log, Binlog ver: 4 |
| mysql-bin.000001 | 120 | Stop        |         1 |         143 |                                       |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
2 rows in set (0.01 sec)
 
　　mysql> show slave status\G;
　　*************************** 1. row ***************************
　　Slave_IO_State: Waiting for master to send event
　　Master_Host: 192.168.59.230
　　Master_User: replication
　　Master_Port: 3306
　　Connect_Retry: 60
　　Master_Log_File: mysql-bin.000006
　　Read_Master_Log_Pos: 1039
　　Relay_Log_File: localhost-relay-bin.000007
　　Relay_Log_Pos: 1202
　　Relay_Master_Log_File: mysql-bin.000006
　　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: 1039
　　Relay_Log_Space: 1542
　　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: 871a79c3-d106-11e4-9b36-000c2986dad9
　　Master_Info_File: /var/lib/mysql/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)
　　
　　ERROR: 
　　No query specified
 
 
bin log files default path: /var/lib/mysql/
 
 
最后两边加一下数据测一下就行了