Mysql DBA Advanced Operations Learning Note-MYSQL Dual Master and multi-master synchronization process

1.Mysql Dual Master and multi-master synchronous combat, mainly from

Use the main premise:

A. Table's primary key auto-increment (M library id1,3,5;m library ID 2,4,6)

Preparation: Two machines, here with multiple examples to explain

First set:

ip:192.168.1.115
port:3306

Second set:

ip:192.168.1.115
port:3307

1.1 Operation of the first machine

(1) Configuring the MY.CNF configuration file for 3306 to open the following parameters

[[email protected] ~]# egrep "\[mysqld]|auto_increment|log-bin|log-slave" /data/3306/my.cnf [mysqld]auto_increment_increment= 2 自增的间隔如1 3 5 间隔为2auto_increment_offset   = 1 ID的初始位置log-bin = /data/3306/mysql-binlog-slave-updates

(2) Restart 3306mysql database service

[[email protected] ~]# /data/3306/mysql stopStoping MySQL....[[email protected] ~]# /data/3306/mysql startStarting MySQL......

(3) Configuring synchronization parameters

CHANGE MASTER TOMASTER_HOST=‘192.168.1.115‘, MASTER_PORT=3307,MASTER_USER=‘rep‘,MASTER_PASSWORD=‘123456‘;

Tip: If the master-slave synchronization is to be changed to dual-master synchronization, we will take the-master-data parameter back to the main library before importing the data into the slave library.

For example, backing up 3306 of the updated data

mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B --master-data=2 --events >/opt/3306bak.sql

Using the-master-data parameter to back up the data, you do not need to add the following parameters when change master and do not use show master status; View the state of the main library to see the location of Binlog.

MASTER_LOG_FILE=‘mysql-bin.000004‘ MASTER_LOG_POS=1895

(4) Start the sync switch from the library and view the sync status

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.1.115 Master_use  R:rep master_port:3307connect_retry:60 master_log_file:mysql-bin.000004 read_master_log_pos:1895 Relay_Log_File: relay-bin.000012relay_log_pos:1019relay_master_log_file:mysql-bin.000004 slave_io_running:yesslave_sql_running: Yes Replicate_Do_DB:Replicate_Ignore_DB:mysql Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Ta ble:replicate_wild_ignore_table:last_errno:0 last_error:skip_counter:0 exec_master_log_pos:1895 Relay_Log_ space:1315 until_condition:none until_log_file:until_log_pos:0 Master_ssl_allowed:no master_ssl_ca_file:ma Ster_ssl_ca_path:master_ssl_cert:master_ssl_cipher:master_ssl_key:seconds_behind_master:0master_ssl_verify_ Server_cert:nolast_io_errno:0last_io_error:last_sql_errno:0 last_sql_error:replicate_ignore_server_ids:master_server_id:21 Row in Set (0.00 sec) 

1.2 Second machine operation

The second operation is almost the same as the first one.

(1) Configuring the MY.CNF configuration file for 3307 to open the following parameters

[[email protected] ~]# egrep "\[mysqld]|auto_increment|log-bin|log-slave" /data/3307/my.cnf [mysqld]auto_increment_increment= 2auto_increment_offset   = 2log-bin = /data/3307/mysql-binlog-slave-updates

(2) Restart 3307mysql database service

[[email protected] ~]# /data/3307/mysql stopStoping MySQL....[[email protected] ~]# /data/3307/mysql startStarting MySQL......

(3) Configuring synchronization parameters

CHANGE MASTER TOMASTER_HOST=‘192.168.1.115‘, MASTER_PORT=3306,MASTER_USER=‘rep‘,MASTER_PASSWORD=‘123456‘;

(4) Start the sync switch from the library and view the sync status

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.1.115 Master_use  R:rep master_port:3306connect_retry:60 master_log_file:mysql-bin.000015 read_master_log_pos:1895 Relay_Log_File: relay-bin.000042relay_log_pos:1326relay_master_log_file:mysql-bin.000015 slave_io_running:yesslave_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:1895 Relay_Log_Space : 1622 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:0master_ssl_verify_server_ Cert:nolast_io_errno:0last_io_error: last_sql_errno:0 last_sql_error:replicate_ignore_server_ids:master_server_id:11 row in Set (0.00 sec) 

1.3 Test MySQL Database Master master (M-M) synchronous Mutual primary Slave.

(1) 3306 host database operations

A. Now create the student table in the Linzhongniao library

mysql> create table student(-> id int(4) not null AUTO_INCREMENT,-> name char(20) not null,-> primary key(id)-> );Query OK, 0 rows affected (0.01 sec)

B. Insert three data in the student table

mysql> insert into student(name) values(‘nishishei‘);Query OK, 1 row affected (0.00 sec)mysql> insert into student(name) values(‘zhangsan‘);Query OK, 1 row affected (0.00 sec)mysql> insert into student(name) values(‘lisi‘);Query OK, 1 row affected (0.00 sec)

C. Look at the inserted data

mysql> select * from student;+----+-----------+| id | name  |+----+-----------+|  1 | nishishei ||  3 | zhangsan  ||  5 | lisi  |+----+-----------+1   rows in set (0.00 sec)

We find that the increment of the ID of the data is not continuous, because we set the following parameters in the my.cnf of the 3306 host, which means that the starting position of my ID field self-increment is 1 in the way of 2 at a time interval, so the data we insert above is self-increment at 2 intervals, then auto_increment_offset is the value equal to 2? Of course, the auto_increment_increment value of the parameter can also be set.

auto_increment_increment= 2 自增的间隔如1 3 5 间隔为2auto_increment_offset   = 1 ID的初始位置

(2) Next we also insert three data on the 3307 host database

mysql> use linzhongniao;Database changedmysql> insert into student(name) values(‘burenshi‘);Query OK, 1 row affected (0.00 sec)mysql> insert into student(name) values(‘liushishi‘);Query OK, 1 row affected (0.00 sec)mysql> insert into student(name) values(‘luhan‘);Query OK, 1 row affected (0.00 sec)

(3) Look at the inserted data

mysql> select * from student;+----+-----------+| id | name  |+----+-----------+|  1 | nishishei ||  3 | zhangsan  ||  5 | lisi  ||  6 | burenshi  ||  8 | liushishi || 10 | luhan |+----+-----------+

We see that the newly inserted data is self-increasing in the form of 6, 8, 10, because we set the value of the parameter in the 3307 host's my.cnf configuration file auto_increment_increment equal to 2 and auto_increment_offset the value equals 2, the two parameters are set to start at 2 and 2 for the interval increment. So the ID of the inserted data is 6, 8, 10.

Mysql DBA Advanced Operations Learning Note-MYSQL Dual Master and multi-master synchronization process