MySQL master synchronization of the database (dual master model)

1 Overview

Mutual main from: two nodes each to open binlog and relay log;

1, inconsistent data;

2. Automatic growth ID; To prevent ID collisions, the workaround is to use an odd ID for one server, and the other to use an even ID, which is typically not conflicting when combined, and is set as follows

Define a node with an odd ID

Auto_increment_offset=1

auto_increment_increment=2

The other node uses an even ID

auto_increment_offset=2

auto_increment_increment=2

However, it is generally not recommended to let MySQL automatically generate ID, because this will create a gap, such as table one inserts the first and third data, table two needs to start from the fourth data inserted, so that the second data in Table II is not inserted, resulting in a gap. The workaround is to generate a unique ID from the ID generator to prevent a gap-producing

Configuration:

1, server_id must use different values;

2. Binlog and relay log are enabled;

3, there is an automatic Growth ID table, in order to make the ID does not conflict, need to define its automatic growth mode;

After the service starts, perform the following two steps:

4, all authorized to have the Copy permission user account;

5, each other designated as the main node;

2 issues to be aware of when copying

2.1 Set from service to "read Only"

Start Read_Only from the server, but only for users who are not super privileged;

Block All Users:

Mysql> FLUSH TABLES with READ LOCK;

2.2 Try to ensure transactional security when replicating

Enable parameters on the master node:

Sync_binlog = on #sync_binlog表示当前节点有事务在复制时提交, the log is written directly into the binary log, ensuring that the log is replicated from the server to commit the transaction

If you are using the InnoDB storage engine, it is recommended to enable the following options

Synchronizing logs when innodb_flush_logs_at_trx_commit=on# transactions are committed

Innodb_support_xa=on #让innodb支持分布式事务

2.3 Try to avoid automatically starting the replication line when the server is aborted unexpectedly

Note that, because when you configure change master to this command, the Master.info file is generated under directory/var/lib/mysql, which indicates which user which password is used to connect to which database, so that the next time it starts, the replication function is started on this file. The problem here is that when the last statement is copied to half of the time, that is, from the node copy half of the time from the node crashes, at this time when the MySQL service from the node is started again, when the last copy half of the statement, re-copy or do not replicate there will be problems, therefore, the occurrence of unexpected termination from the server, It is recommended that you do not start the thread automatically when restarting from the server, either by shutting down the network, manually checking for incomplete statements, manually deleting the statement, manually starting the replication thread from the last crash location, or by restarting replication, otherwise the resulting data may be inconsistent.

2.4 From node: setting parameters

Sync_master_info=on #生成master. Info This file, when the information changes, immediately synchronized to the disk of the file, to prevent the copied data is again copied again

Sync_relay_log_info=on # Generate Relay-log.info This file, which records the location of the binary file master-log that has been copied to the master node, corresponds to the location Relay-log from the node, and if the file is not updated, it may overwrite the information of the previous relay file when it is re-copied. When the unexpected crash from the server, unless you can ensure that the data is consistent (there are tools to check whether the data is consistent, but the corresponding running MySQL service host, not recommended to check), otherwise it is recommended to re-copy, but this is based on the actual situation, such as the data is already large, it is not recommended to re-copy.

3 Example implementing primary master replication

71 and 732 servers as primary master asynchronous replication machines

Stop the MySQL server

[[Email protected] ~] #systemctl Stop mariadb

[[Email protected] ~] #systemctl Stop mariadb

Edit the configuration file, note that the configuration cannot be added read_only option, server_id Otherwise, set the auto-Grow ID, odd and even different

Configuration on Master Node 71

[[Email protected] ~] #vim/etc/my.cnf.d/server.cnf

[Server]

Skip_name_resolve = On

Innodb_file_per_table = On

Max_connections = 20000

Log_bin = Master-log

server_id = 1

Relay_log = Relay-log

Auto_increment_offset=1

auto_increment_increment=2

Start the service

[[Email protected] ~] #systemctl start mariadb

Configuration on Master Node 73

[[Email protected] ~] #vim/etc/my.cnf.d/server.cnf

[Server]

Skip_name_resolve = On

Innodb_file_per_table = On

Innodb_buffer_pool_size = 256M

max_connections = 2000

Relay_log = Relay-log

server_id = 2

Log_bin = Master-log

auto_increment_offset=2

auto_increment_increment=2

Start the service

[[Email protected] ~] #systemctl start mariadb

Check if MySQL has copy copy permission account, if not, need authorization, here have account sunnycopy

MariaDB [(None)]> select User,host,password from Mysql.user;

View the file name and location of the binary at this time

MariaDB [(None)]> Show Master status;

Set the replication parameters, note that the file name and location of the binaries are on the server that is being replicated to the

71 Settings on

MariaDB [(none)]> change master to master_host= ' 192.168.1.73 ', master_user= ' sunnycopy ', master_password= ' Pass1234 ' , master_log_file= ' master-log.000001 ', master_log_pos=245;

Start thread

MariaDB [(None)]> start slave;

73 Settings on

MariaDB [(none)]> change master to master_host= ' 192.168.1.71 ', master_user= ' sunnycopy ', master_password= ' Pass1234 ' , master_log_file= ' master-log.000003 ', master_log_pos=245;

Start thread

MariaDB [(None)]> start slave;

Two-master test, do anything on both servers, both server data is the same, but the automatic generation of inserted ID is different, odd and even different

On 71 do the following, the ID is automatically generated, do not know the ID, then 71 this is

MariaDB [sunny]> INSERT INTO students (Name,age,gender,major) VALUES ("Test1", +, "F", "Maths"), ("Test2", "a", "M", " Chinese "), (" Test3 "," F "," 中文版 ");

With the following select, it is true that ID is technology and automatically grows

MariaDB [sunny]> SELECT * from students where id>=1050;

+------+--------+------+--------+---------+

| ID | name | Age | Gender | Major |

+------+--------+------+--------+---------+

| 1050 |   new005 | 18 | M | NULL |

| 1051 |   new051 | 18 | M | Maths |

| 1053 |   Test1 | 21 | F | Maths |

| 1055 |   Test2 | 22 | M | Chinese |

| 1057 |   Test3 | 23 | F | 中文版 |

+------+--------+------+--------+---------+

5 rows in Set (0.00 sec)

73 Insert data, for even growth

MariaDB [sunny]> INSERT INTO students (Name,age,gender,major) VALUES ("Test2", A, "F", "Maths"), ("Test4", "a", "M", " Chinese "), (" Test6 "," E "," F "," 中文版 ");

MariaDB [sunny]> SELECT * from students where id>=1050;

+------+--------+------+--------+---------+

| ID | name | Age | Gender | Major |

+------+--------+------+--------+---------+

| 1050 |   new005 | 18 | M | NULL |

| 1051 |   new051 | 18 | M | Maths |

| 1053 |   Test1 | 21 | F | Maths |

| 1055 |   Test2 | 22 | M | Chinese |

| 1057 |   Test3 | 23 | F | 中文版 |

| 1058 |   Test2 | 22 | F | Maths |

| 1060 |   test4 | 24 | M | Chinese |

| 1062 |   Test6 | 26 | F | 中文版 |

+------+--------+------+--------+---------+

8 rows in Set (0.00 sec)

MySQL master synchronization of the database (dual master model)