Introduction to the configuration process of MySQL Primary Primary replication (dual Primary replication)
1. Modify the configuration file my.cnf
Server A (172.16.16.70) is configured as follows
Server_id = 70
Socket = / tmp/mysql.sock
Innodb_buffer_pool_size = 10G
Character-set-server=utf8
Log_bin=mysql-bin
Expire_logs_days=3
Replicate-do-db=ixinnuo_sjcj
Binlog-ignore-db=mysql,information_schema
Auto-increment-increment = 2
Auto-increment-offset = 1
Server B (172.16.16.71) is configured as follows:
Server_id = 71
Socket = / tmp/mysql.sock
Innodb_buffer_pool_size = 10G
Character-set-server=utf8
Log_bin=mysql-bin
Expire_logs_days=3
Replicate-do-db=ixinnuo_sjcj
Replicate-ignore-db = mysql,information_schema
Auto-increment-increment = 2
Auto-increment-offset = 2
Restart both servers for the configuration to take effect:
# service mysqld restart
Description: auto-increment-offset is used to set the starting point of automatic growth in the database, back to set an automatic growth value of 2 for these two servers, so their starting points must be different, so as to avoid primary key conflicts when the two servers synchronize data.
Replicate-do-db specifies the synchronized database, which in this case is the ixinnuo_sjcj library. In addition, it is recommended that the hardware configuration of both servers be the same.
2. Synchronize data and establish replication account:
On server A (172.16.16.70):
Mysql > GRANT REPLICATION SLAVE ON *. * TO 'slave'@'172.16.16.71' IDENTIFIED BY' 123456'
Query OK, 0 rows affected (0.00 sec)
Mysql > flush privileges
Query OK, 0 rows affected (0.00 sec)
On server B (172.16.16.71):
Mysql > GRANT REPLICATION SLAVE ON *. * TO 'slave'@'172.16.16.70' IDENTIFIED BY' 123456'
Query OK, 0 rows affected (0.00 sec)
Mysql > flush privileges
Query OK, 0 rows affected (0.00 sec)
Execute the change master command to synchronize:
On server A (172.16.16.70):
Mysql > show master status
+-+ +
| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |
+-+ +
| | mysql-bin.000047 | 411 | | mysql,information_schema |
+-+ +
1 row in set (0.00 sec)
On server B (172.16.16.71):
Mysql > show master status
+-+
| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |
+-+
| | mysql-bin.000003 | 618 | |
+-+
1 row in set (0.00 sec)
Execute on server A:
Mysql > change master to master_host='172.16.16.71',master_user='slave',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=618
Query OK, 0 rows affected, 2 warnings (0.29 sec)
Execute on server B:
Mysql > change master to master_host='172.16.16.70',master_user='slave',master_password='123456',master_log_file='mysql-bin.000047',master_log_pos=411
Query OK, 0 rows affected, 2 warnings (0.34 sec)
Execute the following command on both servers:
Mysql > start slave
Query OK, 0 rows affected (0.02 sec)
four。 View status:
A server (172.16.16.70) status is as follows:
Mysql > show slave status\ G
* * 1. Row *
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.16.71
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 618
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: ixinnuo_sjcj
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: 618
Relay_Log_Space: 460
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: 71
Master_UUID: 32197cd9-2957-11e7-a5c4-525400e493a4
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
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)
The status of server B (172.16.16.71) is as follows:
Mysql > show slave status\ G
* * 1. Row *
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.16.70
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000047
Read_Master_Log_Pos: 411
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000047
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: ixinnuo_sjcj
Replicate_Ignore_DB: mysql,information_schema
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: 411
Relay_Log_Space: 460
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: 70
Master_UUID: f1366940-266c-11e7-92c2-525400f39f79
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
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)
It means that the configuration has been successful.
Test:
Create the table chenfeng in the ixinnuo_ sjcj library on server A (172.16.16.70):
Mysql > use ixinnuo_sjcj
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-A
Database changed
Mysql > create table chenfeng (name varchar (10))
Query OK, 0 rows affected (0.23 sec)
Mysql > insert into chenfeng values ('duansf')
Query OK, 1 row affected (0.13 sec)
Mysql > insert into chenfeng values ('liuyb')
Query OK, 1 row affected (0.02 sec)
View the table chenfeng created on server An on server B
Mysql > select * from chenfeng
+-+
| | name |
+-+
| | duansf |
| | liuyb |
+-+
2 rows in set (0.00 sec)
Mysql > desc chenfeng
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | name | varchar (10) | YES | | NULL |
+-+ +
1 row in set (0.00 sec)
Create the table duansf in the ixinnuo_ sjcj library on server B (172.16.16.71):
Mysql > create table duansf (name varchar (20))
Query OK, 0 rows affected (0.18 sec)
Mysql > insert into duansf values ('duansf')
Query OK, 1 row affected (0.02 sec)
Mysql > insert into duansf values ('liuky')
Query OK, 1 row affected (0.03 sec)
View the table duansf created on server B on server A
Mysql > select * from duansf
+-+
| | name |
+-+
| | duansf |
| | liuky |
+-+
2 rows in set (0.00 sec)
Mysql > desc duansf
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | name | varchar (20) | YES | | NULL |
+-+ +
1 row in set (0.00 sec)
The test is successful, which means that the MySQL master replication configuration is successful.
