mysql dual primary configuration
mysql dual masters are actually synchronous and subordinate to each other.
Any one can perform the insert action
The production environment is used very little, because there are still concerns about data consistency.
The production environment is generally enough
The configuration of 172.19.132.121 starts from 1 and increases by 2 every time. The value is 1, 3, 5, 7...
[mysqld] bind-address=0.0.0.0 port=3306 datadir=/data/mysql socket=/data/mysql/mysql.sock user=mysql skip-name-resolve slow_query_log=on long_query_time=1 slow_query_log_file=/data/mysql/mysql-slow.log innodb-file-per-table=1 innodb_flush_log_at_trx_commit = 2 log_warnings = 1 connect_timeout = 60 net_read_timeout = 120 performance_schema_max_table_instances = 400 server-id = 1 log-bin=master-bin log-bin-index = master-bin.index relay-log = relay-log relay-log-index = relay-log.index binlog_format = ROW auto_increment_offset=1 auto_increment_increment=2 [mysqld_safe] log-error=/data/mysql/mysqld.log pid-file=/data/mysql/mysqld.pid
For the configuration of 192.168.132.122, if it is increased automatically, it will start from 2 and increase by 2 every time. The value is 2,4,6,8...
[mysqld] bind-address=0.0.0.0 port=3306 datadir=/data/mysql socket=/data/mysql/mysql.sock user=mysql skip-name-resolve slow_query_log=on long_query_time=1 slow_query_log_file=/data/mysql/mysql-slow.log innodb-file-per-table=1 innodb_flush_log_at_trx_commit = 2 log_warnings = 1 connect_timeout = 60 net_read_timeout = 120 performance_schema_max_table_instances = 400 server-id = 2 log-bin=master-bin log-bin-index = master-bin.index relay-log = relay-log relay-log-index = relay-log.index binlog_format = ROW auto_increment_offset=2 auto_increment_increment=2 [mysqld_safe] log-error=/data/mysql/mysqld.log pid-file=/data/mysql/mysqld.pid
Prevent index consistency, resulting in insertion failure
Restart database
Give corresponding permission first
192.168.132.121 configuration
mysql> grant replication slave on *.* to 'replication'@'192.168.132.122' identified by '1234567';
192.168.132.122 configuration
mysql> grant replication slave on *.* to 'replication'@'192.168.132.121' identified by '1234567';
Start mutual synchronization
192.168.121 operation
mysql> change master to master_host='192.168.132.122',master_port=3306,master_user='replication',master_password='1234567',master_log_file='master-bin.000001',master_log_pos=154; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.132.122 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 463 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 630 Relay_Master_Log_File: master-bin.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: 463 Relay_Log_Space: 831 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: 141defd2-9dab-11e9-8fe3-000c2963fd11 Master_Info_File: /data/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 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 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified mysql> show mater logs; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mater logs' at line 1 mysql> show master logs; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-bin.000001 | 154 | +-------------------+-----------+
192.168.132.122 operation
mysql> change master to master_host='192.168.132.121',master_port=3306,master_user='replication',master_password='1234567',master_log_file='master-bin.000001',master_log_pos=154; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.132.121 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 154 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 321 Relay_Master_Log_File: master-bin.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: 154 Relay_Log_Space: 522 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: 77278e78-9da8-11e9-bc6c-000c2991dd19 Master_Info_File: /data/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 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 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
Verify dual master status
132.121 Create database mysql> create database darren; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | darren | | mysql | | performance_schema | | sys | +--------------------+ 132.122 View and create database mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | darren | | mysql | | performance_schema | | sys | +--------------------+ mysql> create database darren1; Query OK, 1 row affected (0.00 sec) 132.121 See mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | darren | | darren1 | | mysql | | performance_schema | | sys | +--------------------+
Comparison of other instructions
132.121 operation mysql> use darren; Database changed mysql> create table test( id int primary key NOT NULL auto_increment, name varchar(200) ); Query OK, 0 rows affected (0.01 sec) mysql> show master logs; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-bin.000001 | 554 | +-------------------+-----------+ 1 row in set (0.00 sec) 132.122 Insert a data mysql> use darren; Database changed mysql> insert into test values (null, 'shijiange'); Query OK, 1 row affected (0.00 sec) 132.121 See mysql> show binlog events in 'master-bin.000001'; #The synchronized data is not recorded in the log +-------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------+ | master-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 | | master-bin.000001 | 123 | Previous_gtids | 1 | 154 | | | master-bin.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | master-bin.000001 | 219 | Query | 1 | 324 | create database darren | | master-bin.000001 | 324 | Anonymous_Gtid | 1 | 389 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | master-bin.000001 | 389 | Query | 1 | 554 | use `darren`; create table test( id int primary key NOT NULL auto_increment, name varchar(200) ) | +-------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------+ mysql> select * from test; +----+-----------+ | id | name | +----+-----------+ | 2 | shijiange | #It starts with 2 and adds 2 each time. +----+-----------+ //Insert a piece of data at the same time mysql> insert into test values (null, UUID()); Query OK, 1 row affected (0.01 sec) mysql> select * from test; +----+--------------------------------------+ | id | name | +----+--------------------------------------+ | 2 | shijiange | | 3 | eb11d3b8-9e7d-11e9-8450-000c2991dd19 | #Start with an odd number and add 2 each time +----+--------------------------------------+ mysql> insert into test values (null, UUID()); Query OK, 1 row affected (0.01 sec) mysql> select * from test; +----+--------------------------------------+ | id | name | +----+--------------------------------------+ | 2 | shijiange | | 3 | eb11d3b8-9e7d-11e9-8450-000c2991dd19 | | 5 | f331ca26-9e7d-11e9-8450-000c2991dd19 | +----+--------------------------------------+ 132.122 insert data mysql> insert into test values (null, UUID()); Query OK, 1 row affected (0.01 sec) mysql> select * from test; +----+--------------------------------------+ | id | name | +----+--------------------------------------+ | 2 | shijiange | | 3 | eb11d3b8-9e7d-11e9-8450-000c2991dd19 | | 5 | f331ca26-9e7d-11e9-8450-000c2991dd19 | | 6 | f8ca41f9-9e7d-11e9-ba37-000c2963fd11 | +----+--------------------------------------+ 4 rows in set (0.00 sec) mysql> insert into test values (null, UUID()); Query OK, 1 row affected (0.00 sec) mysql> select * from test; +----+--------------------------------------+ | id | name | +----+--------------------------------------+ | 2 | shijiange | | 3 | eb11d3b8-9e7d-11e9-8450-000c2991dd19 | | 5 | f331ca26-9e7d-11e9-8450-000c2991dd19 | | 6 | f8ca41f9-9e7d-11e9-ba37-000c2963fd11 | | 8 | 043f7a3f-9e7e-11e9-ba37-000c2963fd11 | +----+--------------------------------------+ 132.121 insert data
Dual master configuration complete