MySQL 5.7 Multi-Master one-Plex database configuration (merge multiple data sources into one o'clock)

Work needs, use of notes. The article is very long, pour a cup of tea slowly look.

There are many application scenarios of database, such as database synchronization , a master multi -Slave, multi- master and multi-slave , Multi-master one from , etc. the following records the configuration and testing of multiple master-one .

Most replication scenarios are one master or one master multi-slave. This topology is used for high-availability scenarios, read-write separations. The host is responsible for writing the data, the cluster is responsible for reading the data, scaling the reading program. However, a multi-master is written to multiple DB instances and is eventually merged into one result.

Multi-master one from the host to synchronize the receipt of Business information (transactions), so that a server for multiple host servers to backup, merge data tables, federated data. (No weight)

　　　　

MySQL version: 5.7.10

Configuration machine: Two Master one from

1, from the machine configuration, to ensure that the slave warehouse has a table;

[Mysqld]master-info-repository=tablerelay-log-info-repository=table

After making sure that it is correct, you can check that the configuration is normal as shown below:

mysql> SHOW VARIABLES like '%repository% '; +---------------------------+-------+| variable_name | Value |+---------------------------+-------+| Master_info_repository | TABLE | | Relay_log_info_repository | TABLE |+---------------------------+-------+2 rows in Set (0.00 sec)

Next, modify the server_id, which uses the following three bits of IP:

[mysqld]server-id=141

Enter the following code to pick up the test:

mysql> SHOW VARIABLES WHERE variable_name = ' server_id '; +---------------+-------+| variable_name | Value |+---------------+-------+| server_id | 141 |+---------------+-------+1 row in Set (0.00 sec)

Note: After MySQL 5.7 is installed, please go to the error log to find the password, as follows:

T<e-hd0cgi!d　　

You'll need to use this password next, so it's best to change the example to "password":

ALTER USER ' root ' @ ' localhost ' identified by ' password ';</font>

　　

One of the most critical tasks is to ensure that you do not have the same primary key in your two (or more) source data, especially if you are using the Auto_increment column, if you have two of the same primary key you can imagine the data will be disturbed when you synchronize to the slave machine. Here is an alternative way to use as a reference. It is best to turn off Gtid during configuration, or you will encounter a problem when MySQL initialize initializes, and these records will be copied to the slave. Suppose you start the Gtid first, the configuration is complete, and then you try to copy it on the slave machine. When you check the status of the host (enter SHOW master status), you will see the result that 138 execution records on the host are now copied to the slave:

mysql> SHOW MASTER status\g*************************** 1. Row ***************************file:mysql-bin.000002position:1286binlog_do_db:binlog_ignore_db:executed_gtid_ set:73fdfd2a-9e36-11e5-8592-00a64151d129:1-1381 row in Set (0.00 sec)

And on the off-machine SHOW SLAVE STATUS you will see some errors:

Last_error:error ' Can ' t create database ' MySQL '; Database exists ' on query. Default database: ' MySQL '. Query: ' CREATE DATABASE MySQL;

And the retrieved GTID SET will show that 138 records have been retrieved to the slave:

mysql> SHOW SLAVE status\g ... retrieved_gtid_set:73fdfd2a-9e36-11e5-8592-00a64151d129:1-138.

Of course, if you turn off the Gtid before the configuration is complete, there will be no such mistakes.

After three machine configurations are complete, enter show MASTER STATUS as shown below:

mysql> SHOW MASTER status\g*************************** 1. Row ***************************file:mysql-bin.000002position:398binlog_do_db:binlog_ignore_db:executed_gtid_set : 1 row in Set (0.00 sec)

To this, the entire schema has been created, and some tests are performed below. , create a comic collection database:

Slave machine

CREATE DATABASE ' comicbookdb '; use COMICBOOKDB; CREATE TABLE ' Comics ' (' comic_id ' int (9) NOT NULL auto_increment, ' comic_title ' varchar = NOT null, ' Issue_number ' Decima L (9,0) not null, ' pub_year ' varchar (NO) NULL, ' pub_month ' varchar ' not null,primary KEY (' comic_id ')) Engine=innodb A Uto_increment=1;

The same SQL statements can be run on both the host and the slave. (because we used auto_increment on some of the columns on the host, you might not feel auto_increment on the machine.) But since we don't do any write operations on the slave, you can still run the same statement, and then modify the primary key. The article will be detailed later. ）

When data is merged from multiple hosts into a slave machine, the replicator will handle the auto_increment problem.

The following table is created on the slave machine for comic_id columns, there is no declaration auto_increment an error occurred.

mysql> SHOW SLAVE status\g ... last_sql_error: Error ' Field ' comic_id ' doesn ' t has a default value ' on query. Default database: ' COMICBOOKDB '. Query: ' INSERT into COMICS (Comic_title, Issue_number, Pub_year, Pub_month) VALUES (' Fly man ', ' 5 ', ' 2014 ', ' 03 ') ' ...

In order to handle the COMIC_ID primary key problem, the most convenient way is to configure auto_increment_increment in the host, in MY.CNF or My.ini:

[mysqld]auto_increment_increment = 2

Adding this variable requires restarting the MySQL service, but you can also operate directly on the command line as follows:

mysql> SET @ @auto_increment_increment = 2; Query OK, 0 rows Affected (0.00 sec)

Verify that:

mysql> SHOW VARIABLES WHERE variables_name = ' auto_increment_increment '; +-----------------------------+-------+| variable_name | Value |+-----------------------------+-------+| auto_increment_increment | 2 |+-----------------------------+-------+1 row in Set (0.00 sec)

As per the increment of each primary key is 2, as long as the initial value of each host is different. However, you cannot simply set 0 or 1, because if it is 0, it will revert to 1 by default, which will cause conflicts. So we set a larger value, the lowest bit setting is 0 and 1, the code is as follows:

Host # 1

auto_increment=100000;

Host # 2

auto_increment=100001;

Build the table and start GTID on all hosts. Here the slave also started the Gtid, in case later in this slave to add another from the machine. To start Gtid, you need to modify the configuration:

[mysqld]auto_increment_increment = 2gtid-mode = Onenforce-gtid-consistency = 1

After restarting each server, you can check the status of each host in the same state:

mysql> SHOW MASTER status\g*************************** 1. Row ***************************file:mysql-bin.000005position:154binlog_do_db:binlog_ignore_db:executed_gtid_set : 1 row in Set (0.00 sec)

Next, a server reset (reset Master)was done for all the master and slave, not necessary. Resetting clears all binnary logs and creates a new two-duty log.

mysql-bin.000001 154 Binlog_do_db:binlog_ignore_db:executed_gtid_set:1 row in Set (0.00 sec)

You can see the new binary log (mysql-bin.000001), where the starting position is 154. We insert some data into the host and then look at the host status.

Host #1

63a7971c-b48c-11e5-87cf-f7b6a723ba3d:11 row in Set (0.00 sec)

Visible inserted GTID is 63a7971c-b48c-11e5-87cf-f7b6a723ba3d:1, the colon is preceded by the host's UUID, this information can be found in the data directory auto.cnf inside.

Host #1

# Cat Auto.cnf[auto]server-uuid=63a7971c-b48c-11e5-87cf-f7b6a723ba3d

Insert one more row of data:

Host #1

63a7971c-b48c-11e5-87cf-f7b6a723ba3d:1-21 row in Set (0.00 sec) mysql> Select * from comics;+----------+---- ---------+--------------+----------+-----------+| comic_id | Comic_title | Issue_number | Pub_year | Pub_month |+----------+-------------+--------------+----------+-----------+| 100001 | Fly Man | 1 | 2014 | 01 | | 100003 | Fly Man | 2 | 2014 | |+----------+-------------+--------------+----------+-----------+2 rows in Set (0.00 sec)

Will find that this value becomes 2, then we insert two rows of data into the second host and view the status:

Host #2

Mysql> INSERT into COMICS (Comic_title, Issue_number, Pub_year, Pub_month) VALUES (' Fly man ', ' 3 ', ' n ', ' '); MySQL > INSERT into COMICS (Comic_title, Issue_number, Pub_year, Pub_month) VALUES (' Fly man ', ' 4 ', ' All ', ' ');mysql> Show Master status\g*************************** 1. Row ***************************file:mysql-bin.000005position:974binlog_do_db:binlog_ignore_db:executed_gtid_set : 75e2e1dc-b48e-11e5-83bb-1438deb0d51e:1-21 row in Set (0.00 sec) mysql> Select * from comics;+----------+------------ -+--------------+----------+-----------+| comic_id | Comic_title | Issue_number | Pub_year | Pub_month |+----------+-------------+--------------+----------+-----------+| 100002 | Fly Man | 3 | 2014 | 03 | | 100004 | Fly Man | 4 | 2014 | |+----------+-------------+--------------+----------+-----------+2 rows in Set (0.00 sec)

Host # # has different uuid, which is how we distinguish the gtid corresponding to which step the host. We now have two groups of Gtid that will replicate to the slave. Of course, slave machines also have their own UUID.

Host # # with host # # of Gtid settings:

63a7971c-b48c-11e5-87cf-f7b6a723ba3d:1-275e2e1dc-b48e-11e5-83bb-1438deb0d51e:1-2

　　

In this case, I will usually confirm that the slave is not running:

Slave machine

Mysql> show slave status\gEmpty Set (0.00 sec)

Unlike normal replication, in multi-master replication, you need to create a channel for each host to name the channel. This is called "master-142" (host-142) and "master-143" (host 143) to match server_id (just like IP). The next step is to show how to turn on host # # (server_id=142) data replication.

Slave machine

mysql> change MASTER to master_host= ' 192.168.1.142 ', master_user= ' replicate ', master_password= ' PASSWORD ', master_ Auto_position = 1 for CHANNEL ' master-142 '; Query OK, 0 rows affected, 2 warnings (0.23 sec)

Here are two warnings that can be ignored.

Mysql> SHOW warnings\g*************************** 1. Row ***************************level:notecode:1759message:sending passwords in plain text without SSL/TLS is extremely insecure.*************************** 2.  Row ***************************level:notecode:1760message:storing MySQL user name or password information in the master Info repository are not secure and are therefore not recommended. Consider using the USER and PASSWORD connection options for START SLAVE; See the ' START SLAVE Syntax ' on the MySQL Manual for more information.2 rows in Set (0.00 sec)

Now we can turn on the slave Channel "master-142":

Mysql> START SLAVE for CHANNEL ' master-142 '; Query OK, 0 rows affected (0.03 sec)

This command also initiates the Sql_thread and Io_thread. In the future you will consider stopping one or more threads, so here are some syntax and how to specify a channel to modify:

START SLAVE sql_thread for CHANNEL ' master-142 '; START SLAVE io_thread for CHANNEL ' master-142 ';

You can also send a simple command "start SLAVE" to open these two threads for a channel that needs to perform a copy operation. Starting from the machine, you can see that Gtid is removed and starts writing to the database.

Mysql> SHOW SLAVE STATUS for CHANNEL ' master-142 ' \g*************************** 1. Row ***************************slave_io_state:waiting for master to send Eventmaster_host:192.168.1.142...master_ Uuid:63a7971c-b48c-11e5-87cf-f7b6a723ba3d ... Slave_sql_running_state:slave have read all relay log; Waiting for more updates ... Retrieved_gtid_set:63a7971c-b48c-11e5-87cf-f7b6a723ba3d:1-2executed_gtid_set: 63a7971c-b48c-11e5-87cf-f7b6a723ba3d:1-2auto_position:1...channel_name:master-142

Check the slave to see the relevant data:

select * FROM comics, +----------+-------------+--------------+----------+-----------+| comic_id | Comic_title | Issue_number | Pub_year | Pub_month |+----------+-------------+--------------+----------+-----------+| 100001 | Fly Man | 1 | 2014 | 01 | | 100003 | Fly Man | 2 | 2014 | |+----------+-------------+--------------+----------+-----------+2 rows in Set (0.00 sec)

After host # # is complete, we start configuring host#:

Change MASTER to master_host= ' 192.168.1.143 ', master_user= ' replicate ', master_password= ' PASSWORD ', master_auto_ POSITION = 1 for CHANNEL ' master-143 ';

Then confirm the slave status again:

Slave machine

Mysql> SHOW SLAVE STATUS for CHANNEL ' master-143 ' \g*************************** 1. Row ***************************slave_io_state:waiting for master to send Eventmaster_host:192.168.1.143...master_ uuid:75e2e1dc-b48e-11e5-83bb-1438deb0d51e ... Slave_sql_running_state:slave have read all relay log; Waiting for more updates ... retrieved_gtid_set:75e2e1dc-b48e-11e5-83bb-1438deb0d51e:1-2executed_gtid_set:63a7971c-b48c-11e5-87cf-  F7b6a723ba3d: 1-2,75e2e1dc-b48e-11e5-83bb-1438deb0d51e: 1-2,auto_position:1...channel_name: master-143

We can see that the slave has acquired to two Gtid and is already executing. Looking at the contents of the database table, you can also find that four rows of data have been merged into the slave :

Mysql> SELECT * from comics;+----------+-------------+--------------+----------+-----------+| comic_id | Comic_title | Issue_number | Pub_year | Pub_month |+----------+-------------+--------------+----------+-----------+| 100001 | Fly Man | 1 | 2014 | 01 | | 100002 | Fly Man | 3 | 2014 | 03 | | 100003 | Fly Man | 2 | 2014 | 02 | | 100004 | Fly Man | 4 | 2014 | |+----------+-------------+--------------+----------+-----------+4 rows in Set (0.01 sec)

The copy process handles the value of the self-increment auto_increment. If you check out the exact copy of the SQL statement, you'll find something:

Insert into COMICS (Comic_title, Issue_number, Pub_year, Pub_month) of VALUES (' Fly man ', ' 1 ', ' "', '" ') ' INSERT INTO COMICS ( Comic_title, Issue_number, Pub_year, Pub_month) VALUES (' Fly man ', ' 2 ', ' n ', ' the ') '; INSERT into COMICS (Comic_title, Issue_number, Pub_year, Pub_month) VALUES (' Fly man ', ' 3 ', ' n ', ' a ') '; INSERT into COMICS (Comic_title, Issue_number, Pub _year, Pub_month) VALUES (' Fly man ', ' 4 ', ' 2014 ', ' 04 ');

The self-increment generated on the host is passed to the slave with the declaration. Check the binary log on the host, come to comic_id this column "SET insert_id = 100001", the whole paragraph will be passed along with the SQL statement from the machine ;

Slave machine

# Mysqlbinlog mysql-bin.000001...# at 349#160106 21:08:01 Server ID 142 end_log_pos 349 CRC32 0x48fb16a2 IntvarSET INSERT_ id=100001/*!*/; #160106 21:08:01 server ID 142 end_log_pos 543 CRC32 0xbaf55210 Query thread_id=1exec_time=0 error_code=0u Se ' comicbookdb '/*!*/; SET Timestamp=1452132481/*!*/;insert into COMICS (Comic_title, Issue_number, Pub_year, Pub_month) VALUES (' Fly man ', ' 1 ', ' 2014 ', ' 01 ')/*!*/;

The whole explanation is complete, please give us a lot of advice.

Reference:

1,mysql 5.7 multi-source replication–automatically combining Data from multiple Databases into one

https://mysqlhighavailability.com/ mysql-5-7-multi-source-replication-automatically-combining-data-from-multiple-databases-into-one/

2, MySQL multi-source Replication Overview

Https://dev.mysql.com/doc/refman/5.7/en/replication-multi-source-overview.html

MySQL 5.7 Multi-Master one-Plex database configuration (merge multiple data sources into one o'clock)