Shulou(Shulou.com)06/01 Report--

Background:

At present, MySQL still only supports one Slave to copy data from one Master. Although it can also achieve one-master, multi-slave (M-> S), dual-master replication (MM) and other architectures, it is still very limited. Due to the requirements of the project, the tables of each main library need to be integrated into one place for statistics and analysis. It is a very strenuous operation to connect different instances each time. So continue with an example similar to multi-master and one-slave.

Installation:

Sudo apt-get install software-properties-common

Sudo apt-key adv-recv-keys-keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db

Sudo add-apt-repository 'deb http://mirrors.hustunique.com/mariadb/repo/10.0/ubuntu trusty main'

Sudo apt-get update

Sudo apt-get install mariadb-server

Environment building:

Master 1VR 200.51 (MySQL)

Master 2vl 200.52 (MySQL)

Slave: 200.73 (MariaDB) modified server-id

Confirm the POS of Master:

M1:

Rep@192.168.200.51: (none) 10:26:11 > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin51.000013 | 107 | | test |

+-+

1 row in set (0.01 sec)

M2:

Rep@192.168.200.52: R2 10:26:23 > show master status; +-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin_52.000106 | 107 | | test |

+-+

1 row in set (0.00 sec)

Slave operation:

MariaDB [(none)] > change master 'r1' to master_host='192.168.200.51',master_user='rep',master_password='rep123456',master_log_file='mysql-bin51.000013',master_log_pos=107

Query OK, 0 rows affected (0.23 sec)

MariaDB [(none)] > change master 'r2' to master_host='192.168.200.52',master_user='rep',master_password='rep123456',master_log_file='mysql-bin_52.000106',master_log_pos=107

Query OK, 0 rows affected (0.25 sec)

MariaDB's change method is a little different from MySQL, with an extra ['connection_name'], which is the key to multi-master and one-slave. Set a channel identity for each master so that multi-master replication can be supported.

How do I save copied information? Single-master replication saves the replication information in master.info, similar to that in multi-master replication, except that the channel identification name is added at the end. Such as:

-rw-rw---- 1 mysql mysql 113 November 17 10:30 master-r1.info

-rw-rw---- 1 mysql mysql 114 November 17 10:31 master-r2.info

-rw-rw---- 1 mysql mysql 248 November 17 10:30 mysqld-relay-bin-r1.000001

-rw-rw---- 1 mysql mysql 29 November 17 10:30 mysqld-relay-bin-r1.index

-rw-rw---- 1 mysql mysql 248 November 17 10:31 mysqld-relay-bin-r2.000001

-rw-rw---- 1 mysql mysql 29 November 17 10:31 mysqld-relay-bin-r2.index

-rw-rw---- 1 mysql mysql 54 November 17 10:30 relay-log-r1.info

-rw-rw---- 1 mysql mysql 55 November 17 10:31 relay-log-r2.info

View synchronization:

# View all channels

MariaDB [(none)] > show all slaves status\ G

* * 1. Row *

Connection_name: r1

Slave_SQL_State:

Slave_IO_State:

Master_Host: 192.168.200.51

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin51.000013

Read_Master_Log_Pos: 107

Relay_Log_File: mysqld-relay-bin-r1.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin51.000013

Slave_IO_Running: No

Slave_SQL_Running: No

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: 107

Relay_Log_Space: 248

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: NULL

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: 0

Master_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: No

Gtid_IO_Pos:

Retried_transactions: 0

Max_relay_log_size: 104857600

Executed_log_entries: 0

Slave_received_heartbeats: 0

Slave_heartbeat_period: 1800.000

Gtid_Slave_Pos:

* 2. Row * *

Connection_name: r2

Slave_SQL_State:

Slave_IO_State:

Master_Host: 192.168.200.52

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin_52.000106

Read_Master_Log_Pos: 107

Relay_Log_File: mysqld-relay-bin-r2.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin_52.000106

Slave_IO_Running: No

Slave_SQL_Running: No

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: 107

Relay_Log_Space: 248

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: NULL

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: 0

Master_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: No

Gtid_IO_Pos:

Retried_transactions: 0

Max_relay_log_size: 104857600

Executed_log_entries: 0

Slave_received_heartbeats: 0

Slave_heartbeat_period: 1800.000

Gtid_Slave_Pos:

2 rows in set (0.00 sec)

ERROR: No query specified

# View a single channel

MariaDB [(none)] > show slave 'r1' status\ G

* * 1. Row *

Slave_IO_State:

Master_Host: 192.168.200.51

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin51.000013

Read_Master_Log_Pos: 107

Relay_Log_File: mysqld-relay-bin-r1.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin51.000013

Slave_IO_Running: No

Slave_SQL_Running: No

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: 107

Relay_Log_Space: 248

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: NULL

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: 0

Master_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: No

Gtid_IO_Pos:

1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [(none)] > show slave 'r2' status\ G

* * 1. Row *

Slave_IO_State:

Master_Host: 192.168.200.52

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin_52.000106

Read_Master_Log_Pos: 107

Relay_Log_File: mysqld-relay-bin-r2.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin_52.000106

Slave_IO_Running: No

Slave_SQL_Running: No

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: 107

Relay_Log_Space: 248

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: NULL

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: 0

Master_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: No

Gtid_IO_Pos:

1 row in set (0.00 sec)

ERROR: No query specified

Enable synchronization:

# Open a single channel

MariaDB [(none)] > start slave 'r1'

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)] > start slave 'r2'

Query OK, 0 rows affected (0.00 sec)

# close multiple channels

MariaDB [(none)] > start all slaves

Query OK, 0 rows affected, 2 warnings (0.01 sec)

MariaDB [(none)] > show warnings

+-+

| | Level | Code | Message | |

+-+

| | Note | 1937 | SLAVE 'r2' started |

| | Note | 1937 | SLAVE 'r1' started |

+-+

2 rows in set (0.00 sec)

You can tell if the synchronization is successful through the show all slaves status command.

Turn off synchronization:

# close a single channel

MariaDB [(none)] > stop slave 'r1'

Query OK, 0 rows affected (0.14 sec)

MariaDB [(none)] > stop slave 'r2'

Query OK, 0 rows affected (0.03 sec)

# close all channels

MariaDB [(none)] > stop all slaves

Query OK, 0 rows affected, 2 warnings (0.08 sec)

MariaDB [(none)] > show warnings

+-+

| | Level | Code | Message | |

+-+

| | Note | 1938 | SLAVE 'r2' stopped |

| | Note | 1938 | SLAVE 'r1' stopped |

+-+

2 rows in set (0.00 sec)

Multi-source replication has several variables on the basis of the previous copy. Now let's explain:

MariaDB [(none)] > show all slaves status\ G

* * 1. Row *

Connection_name: R1 # master connection name, channel name, the first parameter.

Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.200.51

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin51.000013

Read_Master_Log_Pos: 107

Relay_Log_File: mysqld-relay-bin-r1.000005

Relay_Log_Pos: 396

Relay_Master_Log_File: mysql-bin51.000013

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: 107

Relay_Log_Space: 845

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_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: No

Gtid_IO_Pos:

Retried_transactions: 0 # the number of times this connection retries the transaction

Max_relay_log_size: the maximum value of 104857600 # relay log. If it is 0, it will be set to the size of max_binlog_size at startup

Executed_log_entries: 17 # slave has pointed to how many log entries

Slave_received_heartbeats: 0 # how many heartbeats have we received from master

Slave_heartbeat_period: 1800.000 # how often a heartbeat packet is requested from master (in seconds)

Gtid_Slave_Pos:

Test replication

Master 1:

Rep@192.168.200.51: (none) 01:52:34 > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mha_test |

| | mysql |

| | performance_schema |

| | xtra_test |

+-+

5 rows in set (0.00 sec)

Rep@192.168.200.51: (none) 01:52:37 > create database R1 default charset utf8

Query OK, 1 row affected (0.01sec)

Rep@192.168.200.51: (none) 01:53:36 > use R1

Database changed

Rep@192.168.200.51: R1 01:53:44 > create table R1 (id int not null auto_increment primary key,name varchar (30)) default charset utf8

Query OK, 0 rows affected (1.35 sec)

Rep@192.168.200.51: R1 01:54:09 > insert into R1 (name) values ('a'), ('b'), ('c')

Query OK, 3 rows affected (0.01sec)

Records: 3 Duplicates: 0 Warnings: 0

Rep@192.168.200.51: R1 01:54:56 > select * from R1

+-+ +

| | id | name |

+-+ +

| | 1 | a |

| | 2 | b | |

| | 3 | c |

+-+ +

3 rows in set (0.00 sec)

Master 2:

Rep@192.168.200.52: (none) 01:52:13 > create database R2 default charset utf8

Query OK, 1 row affected (0.01sec)

Rep@192.168.200.52: (none) 01:54:27 > use R2

Database changed

Rep@192.168.200.52: R2 01:54:30 > create table R2 (id int not null auto_increment primary key,name varchar (30)) default charset utf8

Query OK, 0 rows affected (0.23 sec)

Rep@192.168.200.52: R2 01:54:32 > insert into R2 (name) values ('A'), ('B'), ('C')

Query OK, 3 rows affected (0.28 sec)

Records: 3 Duplicates: 0 Warnings: 0

Rep@192.168.200.52: R2 01:55:18 > select * from R2

+-+ +

| | id | name |

+-+ +

| | 1 | A |

| | 2 | B |

| | 3 | C |

+-+ +

3 rows in set (0.01sec)

Slave:

MariaDB [(none)] > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | R1 |

| | R2 |

+-+

5 rows in set (0.00 sec)

MariaDB [(none)] > use R1

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

MariaDB [R1] > select * from R1

+-+ +

| | id | name |

+-+ +

| | 1 | a |

| | 2 | b | |

| | 3 | c |

+-+ +

3 rows in set (0.00 sec)

MariaDB [R1] > use R2

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

MariaDB [R2] > select * from R2

+-+ +

| | id | name |

+-+ +

| | 1 | A |

| | 2 | B |

| | 3 | C |

+-+ +

3 rows in set (0.00 sec)

If the synchronization is successful, how to skip it by error (default_master_connection)?

To create an R2 database on Master 1, an error will be reported because it exists on Slave:

Master 1:

Rep@192.168.200.51: R1 01:55:52 > create database R2 default charset utf8

Query OK, 1 row affected (0.01sec)

Rep@192.168.200.51: R1 01:59:51 > insert into R1 (name) values ('d'), ('e'), ('f')

Query OK, 3 rows affected (0.01sec)

Records: 3 Duplicates: 0 Warnings: 0

Rep@192.168.200.51: R1 02:04:22 > select * from R1

+-+ +

| | id | name |

+-+ +

| | 1 | a |

| | 2 | b | |

| | 3 | c |

| | 4 | d | |

| | 5 | e |

| | 6 | f | |

+-+ +

6 rows in set (0.00 sec)

Slave:

MariaDB [R2] > show slave 'r1' status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.200.51

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin51.000013

Read_Master_Log_Pos: 767

Relay_Log_File: mysqld-relay-bin-r1.000005

Relay_Log_Pos: 956

Relay_Master_Log_File: mysql-bin51.000013

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1007

Last_Error: Error 'Can't create database' R2; database exists' on query. Default database:'R2. Query: 'create database R2 default charset utf8'

Skip_Counter: 0

Exec_Master_Log_Pos: 667

Relay_Log_Space: 1505

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: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 1007

Last_SQL_Error: Error 'Can't create database' R2; database exists' on query. Default database:'R2. Query: 'create database R2 default charset utf8'

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: No

Gtid_IO_Pos:

1 row in set (0.00 sec)

MariaDB [R1] > select * from R1

+-+ +

| | id | name |

+-+ +

| | 1 | a |

| | 2 | b | |

| | 3 | c |

+-+ +

3 rows in set (0.00 sec)

R1 synchronization failed, can R2 continue to synchronize?

Master 2:

Rep@192.168.200.52: R2 01:55:59 > insert into R2 (name) values ('D'), ('E'), ('F')

Query OK, 3 rows affected (0.01sec)

Records: 3 Duplicates: 0 Warnings: 0

Rep@192.168.200.52: R2 02:02:19 > select * from R2

+-+ +

| | id | name |

+-+ +

| | 1 | A |

| | 2 | B |

| | 3 | C |

| | 4 | D | |

| | 5 | E |

| | 6 | F |

+-+ +

6 rows in set (0.01 sec)

Slave:

MariaDB [R2] > select * from R2

+-+ +

| | id | name |

+-+ +

| | 1 | A |

| | 2 | B |

| | 3 | C |

| | 4 | D | |

| | 5 | E |

| | 6 | F |

+-+ +

6 rows in set (0.00 sec)

It can be concluded that the synchronization of R2 is not affected after the failure of R1 synchronization. If you want R1 synchronization to work, you need to ignore or skip the error. Such as:

MariaDB [R1] > stop slave'R1'

Query OK, 0 rows affected (0.12 sec)

MariaDB [R1] > set @ @ default_master_connection='r1'; # here is the point: specify a channel, and then use a single-channel sql_slave_skip_counter.

Query OK, 0 rows affected (0.00 sec)

MariaDB [R1] > select @ @ default_master_connection

+-+

| | @ @ default_master_connection |

+-+

| | R1 |

+-+

1 row in set (0.00 sec)

MariaDB [R1] > SET GLOBAL sql_slave_skip_counter = 1

Query OK, 0 rows affected (0.00 sec)

MariaDB [R1] > start slave'R1'

Query OK, 0 rows affected (0.00 sec)

MariaDB [R1] > show slave 'R1 'status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.200.51

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin51.000013

Read_Master_Log_Pos: 993

Relay_Log_File: mysqld-relay-bin-r1.000006

Relay_Log_Pos: 396

Relay_Master_Log_File: mysql-bin51.000013

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: 993

Relay_Log_Space: 1731

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_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: No

Gtid_IO_Pos:

1 row in set (0.00 sec)

MariaDB [R1] > select * from R1

+-+ +

| | id | name |

+-+ +

| | 1 | a |

| | 2 | b | |

| | 3 | c |

| | 4 | d | |

| | 5 | e |

| | 6 | f | |

+-+ +

6 rows in set (0.00 sec)

After seeing the skip / ignore error, R1 replication is normal.

The above tests show that when using multi-master-slave replication, it is necessary to ensure that the Master Schema of each master is unique and cannot be duplicated.

Finally, take a look at how to initialize:

MariaDB [R1] > show all slaves status\ G

* * 1. Row *

Connection_name: r1

Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.200.51

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin51.000013

Read_Master_Log_Pos: 1376

Relay_Log_File: mysqld-relay-bin-r1.000006

Relay_Log_Pos: 779

Relay_Master_Log_File: mysql-bin51.000013

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: 1376

Relay_Log_Space: 2114

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_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: No

Gtid_IO_Pos:

Retried_transactions: 0

Max_relay_log_size: 104857600

Executed_log_entries: 39

Slave_received_heartbeats: 4

Slave_heartbeat_period: 1800.000

Gtid_Slave_Pos:

* 2. Row * *

Connection_name: r2

Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.200.52

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin_52.000106

Read_Master_Log_Pos: 893

Relay_Log_File: mysqld-relay-bin-r2.000005

Relay_Log_Pos: 1183

Relay_Master_Log_File: mysql-bin_52.000106

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: 893

Relay_Log_Space: 1633

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_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: No

Gtid_IO_Pos:

Retried_transactions: 0

Max_relay_log_size: 104857600

Executed_log_entries: 28

Slave_received_heartbeats: 4

Slave_heartbeat_period: 1800.000

Gtid_Slave_Pos:

2 rows in set (0.00 sec)

# initialization of single channel

MariaDB [R1] > reset slave 'r1' all

ERROR 1198 (HY000): This operation cannot be performed as you have a running slave 'r1clients; run STOP SLAVE 'r1' first

MariaDB [R1] > stop slave'R1'

Query OK, 0 rows affected (0.03 sec)

MariaDB [R1] > reset slave 'r1' all

Query OK, 0 rows affected (0.04 sec)

MariaDB [R1] > stop slave'R2'

Query OK, 0 rows affected (0.02 sec)

MariaDB [R1] > reset slave 'r2' all

Query OK, 0 rows affected (0.02 sec)

MariaDB [R1] > show all slaves status\ G

Empty set (0.00 sec)

# initialization of all channels

MariaDB [R1] > stop all slaves

Query OK, 0 rows affected, 2 warnings (0.05 sec)

MariaDB [R1] > show warnings

+-+

| | Level | Code | Message | |

+-+

| | Note | 1938 | SLAVE 'r2' stopped |

| | Note | 1938 | SLAVE 'r1' stopped |

+-+

2 rows in set (0.00 sec)

MariaDB [R1] > reset slave all; # found that only R1 can be initialized, not R2. So initialization still needs to be performed in a single channel.

Summary:

After the above test, the data synchronization of multiple master instances to one slave instance is realized, and the data tables concentrated for analysis can be synchronized together for analysis and processing, which greatly reduces the intermediate processing time and security of the data. There is also a special note here that filtering options (Replicate_Do_Table, Replicate_Ignore_Table, Replicate_Wild_Do_Table, Replicate_Wild_Ignore_Table) can be used when synchronizing the database. It looks like you need to synchronize the watches you need, and you don't need to synchronize the unnecessary ones.

For more information, see:

Https://mariadb.com/kb/en/mariadb/documentation/replication/standard-replication/multi-source-replication/

Http://www.penglixun.com/tech/database/diy_multi_master_replication.html

Original link: http://www.cnblogs.com/zhoujinyi/p/4102984.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.