Master-Slave replication Application of Relational Database 04/03 Update SLTechnology News&Howtos

Master-Slave replication Application of Relational Database

2025-04-03 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

1. MySQL simply replicates related concepts:

1. The significance of mysql replication: Mysql replication is a prerequisite for mysql to complete high-performance applications.

2. Mysql replication mechanism:

Slave side thread:

IO thread: request events in the binary log from the main service

After reading, the IO thread will go to sleep, and when the master server has new data, the master server will wake up the IO thread of the slave server.

SQL thread: read events from the relay log and execute them locally

If binary logging is on, binary logs will also be recorded, but in order to save space and improve performance, you need to turn off

The slave server cannot perform a write operation, and if a write operation is performed, it is out of sync with the master server.

Master side:

Binlog dump: sends the event requested by IO thread to the other party

The default is asynchronous working mode: the master server finishes writing by itself, regardless of whether the slave server is finished or not, it will return

Second, the common framework of MySQL replication

1. Master-slave framework

1) for one-slave and multi-master, only the new version of Mysql can be implemented. Generally speaking, a slave can only have one master server, but it can be rotated. There can be only one primary server at a time.

MariaDB-10: support for multi-master model, multi-source replication (multi-source replication)

2) one master and multiple slaves: write operations can only be performed on the master server. Multi-slave will increase the pressure on the master server.

At this point, a scheduler is needed to separate read and write requests to the master and slave server. The so-called separation of reading and writing

3) read-write separation: under the master-slave model, the front-end dispenser can recognize read / write and schedule to the target host as needed.

Amoeba,mysql-proxy can achieve read-write separation scheduling.

Read the server for load balancing, using consistent hashing algorithm, virtual nodes to assign access.

two。 Double main frame

1) use server_id to avoid cyclic assignment

2) the auto-growth attributes of both sides must be set to avoid conflicts, one using even numbers and one practical technique.

First server:

Auto_increment_increment=1 defines the starting value of the auto-grow field

Auto_increment_offset=2 step size

Second server

Auto_increment_increment=2 defines the starting value of the auto-grow field

Auto_increment_offset=2 step size

Use the above settings to resolve the conflict.

3) the data is inconsistent; at some time under the dual-master model, the data will be out of sync.

The first server locks the first field and changes the second field.

The second server locks the second field and changes the first field.

For example, a table contains: Age, Salary

If a person is 31 and earns 2900, do the following

A: update T1 set Salary=salary+1000 WHERE Age > = 30

B: update t1 set Age=Age-3 WHERE Salary

< 3000; 会导致：服务器A Salary 变为3900，年龄31 服务器B Salary 变为年龄28，工资2900 4)功能：不能分摊写请求，两个服务器写压力一致。三、复制构架扩展 1.主从服务器之间的差距长时间运行后，主从可能不同步。因为主服务器可以写并发，但是从服务器的同步只能是但进程。从服务器落后，有时候需要认为设计，来做备份。 2.一主多从的环境： 1)一主多从的环境中，为了利用各从服务器的缓存能力。需要一些负载均衡算法，来绑定特定查询到特定服务器上，来使得缓存命中。这是这样做使得均衡效果被打破，使得有的服务器过于繁忙。 2)为了解决此问题，可以引入中心缓存服务器。 3)由于换从服务器工作在旁路模式下，所以是否缓存取决于客户端程序。 memcached：可以提供缓存能力+API 公共缓存服务器，性能比Mysql自身差 3.多级复制：主指向一个从，从同时也作为其他从服务器的主 master ->

Slave/master-> slave

The intermediate server needs to open binary log and relay log.

Multilevel replication can reduce the pressure on the primary server to generate mysqldump and apportion the pressure to the next episode. But it may not make the data better and up-to-date.

You can change the read and write engine of the intermediate server to black hole to reduce local pressure, generate only binary logs as relay servers (relay server), and then send binary logs to downstream servers

4. Simulate one-slave multi-master model:

According to time, but with a different master.

It is usually used to collect data between different servers.

5. Ring model:

Each server is not only the master server of the next server, but also the slave server of the previous server, so that each server is the master and slave, forming a transmission ring.

Changes to each server are synchronized to any server on the ring.

Server_id cannot be the same.

6. Common mysql architecture

1) one master and one slave, and read and separate

2) one master and many followers, the master is responsible for reading and the slave is responsible for writing

3) one master and multiple slaves plus a cold backup server, which is only used for backup. Turn it off for backup every period of time.

4) Multi-master model: detect the health status of the master server through heartbeat information. If one master dies, switch to another master immediately.

Potential problem: uncommitted transactions on the first server will be rolled back when switching.

GTID (GLOBAL TRANSACTION id): ensures that the transaction on a particular server is intact and rolls back if the execution is unsuccessful.

7. Advanced Application Architecture:

Read-write separation + load balancing: read until read from the server, write only on the main server. You need to add memocached before the read-write separator

Amoeba, mysql proxy

8. Remote synchronization: mainly to avoid natural disasters

IV. Simple master-slave architecture configuration process

1. Pay attention to the configuration:

1) Note: the master and slave versions are the same, or the master version is higher than the slave version.

2) where to start copying:

1. All start with 0:

two。 The primary server has been running for some time and there is a large dataset:

Back up the primary server, and then start replicating from the location where the backup was made from the slave service

Application case (1): configure master-slave replication simple architecture

The Master server is configured as follows:

-- stop the mysql service # service mysqld stop-- create the mysql service log directory and grant permissions to # mkdir-pv / mydata/binlogs/# chown-R mysql.mysql / mydata/binlogs/-- modify the my.cnf configuration file: [mysqld] server-id = 1 mysql service # service mysqld start-- to query whether the binary log parameters are Start mysql > SHOW GLOBAL VARIABLES LIKE 'log_bin%' +-- +-+ | Variable_name | Value | +-+-+ | log_bin | ON | +- -- +-- create a user with replication permissions mysql > GRANT REPLICATION SLAVE REPLICATION CLIENT ON *. * TO 'repluser'@'172.16.%.%' IDENTIFIED BY' replpass' Mysql > FLUSH PRIVILEGES

The Slave server is configured as follows:

The Slave server connects to the primary server:

Mysql > CHANGE MASTER TO MASTER_HOST='172.16.100.7',MASTER_USER='repluser',MASTER_PASSWORD='replpass' Command: CHANGE MASTER TO MASTER_HOST ='', which user does the primary server address MASTER_USER='', use to connect to the MASTER_PASSWORD='', password MASTER_LOG_FILE='', uses to copy which specific binary file MASTER_LOG_POS= From which location of the binary file to copy the most important parameters only the first three, you can start to work.

After connecting to the master server, query the Master server thread and log location:

After connecting to the master server, query the Slave server thread status and log location:

-- query Slave server thread status and Slave replication log status mysql > SHOW SLAVE STATUS\ Graph * 1. Row * * Slave_IO_State: Master_Host: 172.16.100.7Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: Read_ Master_Log_Pos: 4Relay_Log_File: relay-bin.000001Relay_Log_Pos: 4Relay_Master_Log_File: Slave_IO_Running: NoSlave_SQL_Running: No-- start replication thread: mysql > START SLAVE Mysql > SHOW SLAVE STATUS\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send eventMaster_Host: 172.16.100.7Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: master-bin.000001Read_Master_Log_Pos: 496Relay_Log_File : relay-bin.000002Relay_Log_Pos: 781Relay_Master_Log_File: master-bin.000001Slave_IO_Running: YesSlave_SQL_Running: Yes-- query thread service startup information It records the log information in the error message log file-query log location directory: # ps aux | grep mysqld-- query error log # tail / mydata/data/slave.samlee.com.err 160621 17:43:48 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='', master_log_pos='4'. New state master_host='172.16.100.7', master_port='3306', master_log_file='', master_log_pos='4'.

According to the above information query, we can clearly know the current status and location information of the Slave service.

The above is the simple application deployment process of Mysql master-slave replication.

Master-slave replication architecture application test (1): copy from 0-- >

Step (1): create a database write operation on the Master server

Step (2): the Slave service performs the query operation

-- query the status information of the Slave server to know that the Slave server has been synchronized to the location 579 > SHOW SLAVE STATUS\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send eventMaster_Host: 172.16.100.7Master_User: repluserMaster_Port : 3306Connect_Retry: 60Master_Log_File: master-bin.000001Read_Master_Log_Pos: 579Sync log location Relay_Log_File: relay-bin.000002Relay_Log_Pos: 864Relay_Master_Log_File: master-bin.000001Slave_IO_Running: YesSlave_SQL_Running: Yes

Step (3): create a table insert data write operation on the Master server

Step (4): the Slave service performs the query operation

-- query the status information of the Slave server to know that the Slave server has been synchronized to the location of 864 > SHOW SLAVE STATUS\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send eventMaster_Host: 172.16.100.7Master_User: repluserMaster_Port : 3306Connect_Retry: 60Master_Log_File: master-bin.000001Read_Master_Log_Pos: 864Relay_Log_File: relay-bin.000002Relay_Log_Pos: 1149Relay_Master_Log_File: master-bin.000001Slave_IO_Running: YesSlave_SQL_Running: Yes-- query mysql > USE mydb Mysql > SHOW TABLES;+-+ | Tables_in_mydb | +-+ | T1 | +-+ mysql > SELECT * FROM T1 + | id | +-+ | 1 | | 2 | 3 | 4 | | 5 | +-+

Master-slave replication architecture application test (2): start replication from a specified location

Step (1): test preparation operation:

The Slave server prepares:

Mysql > STOP SLAVE;mysql > DROP DATABASE mydb

The Master server prepares the operation:

Mysql > DROP DATABASE mydb

Step (2):

To demonstrate the effect, we asked the Master server to generate large amounts of data directly, and then asked the Slave server not to replicate from 0.

Operations on the Master server:

-- Import data # mysql

< hellodb.sql MariaDB [(none)]>

SHOW MASTER STATUS +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +- -+-+ | master-bin.000001 | 8544 | +-+ mysql > DROP DATABASE mydb Mysql > CREATE DATABASE mydb;mysql > USE hellodb;mysql > CREATE TABLE T1 (id int);-- Lock log location to perform backup # mysqldump-- all-databases-- flush-logs-- master-data=2-- lock-all-tables > all.sql-- transfer to Slave server # scp all.sql root@172.16.100.8:/root

Operations on the Slave server:

-- restore the database mysql > SHOW SLAVE STATUS\ gateway * 1. Row * * Slave_IO_State: Master_Host: 172.16.100.7Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: master-bin.000001Read_Master_ under stopping the Slave service Log_Pos: 864Relay_Log_File: relay-bin.000002Relay_Log_Pos: 1149Relay_Master_Log_File: master-bin.000001Slave_IO_Running: Nolave_SQL_Running: No-- restore backup # mysql

< all.sql --查询主从起始位置信息，要去备份SQL中查询# cat all.sql | grep 'master-bin'-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000002', MASTER_LOG_POS=245;--指定从备份位置开始同步主从数据mysql >

CHANGE MASTER TO MASTER_HOST='172.16.100.7',MASTER_USER='repluser',MASTER_LOG_FILE='master-bin.000002',MASTER_LOG_POS=245;-- starts the Slave replication thread mysql > START SLAVE Mysql > SHOW SLAVE STATUS\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send eventMaster_Host: 172.16.100.7Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: master-bin.000002Read_Master_Log_Pos: 245Relay_Log_File : relay-bin.000002Relay_Log_Pos: 530Relay_Master_Log_File: master-bin.000002Slave_IO_Running: YesSlave_SQL_Running: Yes

Operations on the Master server:

Operations on the Slave server:

-- query Slave service status information > SHOW SLAVE STATUS\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send eventMaster_Host: 172.16.100.7Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: master-bin.000002 -- you can see that Read_Master_Log_Pos: 336 is synchronized from the master-bin.000002 log file-- the log event location is: 336Relay_Log_File: relay-bin.000002Relay_Log_Pos: 621Relay_Master_Log_File: master-bin.000002Slave_IO_Running: YesSlave_SQL_Running: Yes

Some summaries of the experimental process of simple master-slave replication:

1) if the master server binary log is opened during database initialization, the slave server needs to specify the log after initialization to copy. Otherwise, it seems that some strange mistakes will occur. Maybe it's because the system library can't be created again.

2) it is best for the master server to open the binary log after the initialization of the system library is completed. This allows you to copy directly from the first binary log from the server.

3) if there is an error in the error log, it is usually caused by the slave sql thread, Slave I Dot O. If there is a problem with slave I Dot O, it usually has something to do with permissions and links. The problem with Slave sql is usually related to binary log execution from the server.

1. Files related to replication in the master-slave architecture:

1) master.info: used to save the information needed to connect from the server to the master server. Each row has a value. Different mysql versions have different formats. If you don't want to link, just delete it.

2) relay-log.info: the location of binary log and relay log, log coordinates, will be updated automatically. Since the update will not be performed immediately, there will be a buffer, and if the power is cut off, the information will be lost.

In order to replicate security, the following parameters need to be enabled on the slave server:

Sync_master_info = 1 sync_relay_log = 1 sync_relay_log_info = 1

3) when the server crashes unexpectedly, it is recommended to use the pt-slave-start command to start slave

two。 Row-based and statement-based replication:

1) based on statement:

Advantages: small amount of data, easy to view and identify, strong adaptability

Disadvantages: some statements can not be copied accurately, using code applications such as triggers, stored procedures, etc., to achieve precise replication.

2) Line-based replication:

Advantages: can accurately complete replication, including hair stored procedures, can complete almost all replication functions, less CPU resources, can reduce the use of locks

Cons: it is impossible to determine which statements have been executed through the log, and the data may be slightly larger.

3) statement-based scenarios used alone are almost non-existent, and the default is mixed mode.

3. How does the slave server lag behind the master server to detect:

Mysql > SHOW SLAVE STATUS\ G;-- you can check Seconds_Behind_Master: 0, which shows how many seconds behind the master server. If the primary server is busy, this value is usually positive, if the value is fluctuating rather than increasing. It's acceptable.

4. Evaluate whether the master-slave server table data is consistent:

Some tools in procona-toolkit can be monitored.

Pt-table-checksum runs on the main server and can automatically find out which table is out of sync

Out-of-sync solution:

1. Back up again and import the data from the server.

two。 Use pt-table-sync to complete synchronization

Pt-summary can get some mysql statistics, which are sometimes useful.

5. To improve data security during replication, the settings on the primary server:

Sync_binlog = 1 innodb_flush_log_at_trx_commit = 1 if the parameter is set to 1, the performance will be degraded seriously. Therefore, when it is generally set to 2 and so on, it is still possible for the master server to crash so that all binary log events cannot be obtained from the slave server. If the master server crashes unexpectedly and the events in the binary log are corrupted, you can ignore the slave server by using the following parameter: sql_slave_skip_counter = 0 data directory does not have enough disk space, or replication cannot be carried out. Need to be combined with monitoring tools to do an overall assessment. The primary server is modified too much, so that the bandwidth is not enough.

5. MySQL simple master-slave replication application extension

1. Master and slave servers need to ensure time synchronization, and you can use ntp services

# crontab-eBay Universe 5 * / usr/sbin/ntpdate 172.16.100.9

two。 How do I restrict read-only from the server?

Read-only = ON-- defined in the [mysqld] section of the slave server my.cnf

Extended application implementation: let only slave servers achieve read-only state

Step (1):

Operations on the Slave server:

# vim / etc/ my.cnf [mysqld] read-only = ON# service mysqld startmysql > SHOW GLOBAL VARIABLES LIKE'% read_only';+-+-+ | Variable_name | Value | +-+-+ | read_only | ON | +-+-+

Operations on the Master server:

-- create an authorized test user mysql > GRANT ALL ON mydb.* TO 'testuser'@'172.16.%.%' IDENTIFIED BY' testpass'

Step (2):

Operations on the Slave server:

Operations on the Master server:

-- remote connection to the Slave server tests whether # mysql-utestuser-h272.16.100.8-ptestpassmysql > USE mydb;mysql > SHOW TABLES;Empty set (0.01 sec) mysql > CREATE TABLE T1 (id int) is enabled; ERROR 1290 (HY000): The MariaDB server is running with the-- read-only option so it cannot execute this statement##MariaDB 's server is running, and the read-only option cannot execute this statement

Tips: only users without SUPER privileges can be restricted from performing write operations; therefore, the administrator is still valid and do not use administrator privileges to modify data on the slave server.

To restrict all users:

Mysql > FLUSH TABLES WITH READ LOCK

3. How to secure transactions in master-slave replication

When the master server transaction has not been synchronized to the binary log, if the master server is down, the slave server cannot get the complete transaction, which may cause the transaction to be rolled back when the slave server points to another master server. Thus, the transaction is lost.

Configure the parameter resolution on the primary server to synchronize the transaction from the binary buffer to the binary log as soon as the transaction commits.

Sync_binlog=1innodb_flush_log_at_trx_commit=1

Tips: when this variable is set, there will be some performance loss, mainly because MyISAM or Innodb automatically commit transactions by default. To improve performance, you can turn off autocommit and turn on the above options.

Full synchronization process: master server write operation-> write operation synchronized to binary log-> write operation is copied to binary log of slave server-> write operation is performed from server-> and then returned to master server

4. Semi-synchronous replication (semi-synchronously) mechanism can be used to achieve better synchronization under the premise of certain performance loss of the master server.

The previous section is the same as full synchronization, except that in an one-master-multi-slave scenario, the master server only waits for the fastest slave server-wide write completion state.

Semi-synchronous configuration: provided by google after mysql5.5, the two plug-ins semisync_master.so and semisync_slave.so are installed in the lib/plugin directory of the program.

Extended application implementation: realizing semi-synchronous replication configuration and application

Master server configuration actions:

-- after loading the semisync_master.so module mysql > INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';--, there will be several more options: > SHOW GLOBAL VARIABLES LIKE'% semi%' +-- +-+ | Variable_name | Value | +-- +-+ | rpl_semi_sync_master_enabled | OFF | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | +-- +-+ rpl_semi_sync_master_enabled: controls whether semi-synchronous rpl_semi is enabled _ sync_master_timeout: semi-synchronous timeout How long does it take for semi-synchronization to reply from the server and skip half-synchronization to continue execution. Unit is millisecond, default is 10 seconds rpl_semi_sync_master_wait_no_slave: if the semi-synchronous server does not respond, skip the semi-synchronous server to continue execution. -- modify the first two parameters: mysql > SET GLOBAL rpl_semi_sync_master_enabled='ON'; mysql > SET GLOBAL rpl_semi_sync_master_timeout=2000;-- configuration completed as follows: mysql > SHOW GLOBAL VARIABLES LIKE'% semi%' +-- +-+ | Variable_name | Value | +-- +-+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 2000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | +-+-+

Slave server configuration actions:

-- when the semisync_slave.so module mysql > INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';-- is loaded, the following parameter options appear: mysql > SHOW GLOBAL VARIABLES LIKE'% semi%' +-- +-+ | Variable_name | Value | +-+-+ | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync _ slave_trace_level | 32 | +-- +-- from the server, you only need to turn on the first one: mysql > SET GLOBAL rpl_semi_sync_slave_enabled=1. Mysql > SHOW GLOBAL VARIABLES LIKE'% semi%' +-- +-+ | Variable_name | Value | +-+-+ | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync _ slave_trace_level | 32 | +-+-- if you do not restart the Icano thread The semi-synchronous slave server feature is not recognized by the master server, and the operation on the master server times out, and then skips semi-synchronization. Mysql > STOP SLAVE IO_THREAD;mysql > START SLAVE IO_THREAD

Master server configuration operation: perform data write operation

Mysql > USE mydb;Database changedmysql > CREATE TABLE T2 (name char (20)); Query OK, 0 rows affected (0.09 sec) mysql > CREATE TABLE T3 (name char (20)); Query OK, 0 rows affected (0.02 sec) mysql > CREATE TABLE T4 (name char (20)); Query OK, 0 rows affected (0.01 sec)-verify whether semi-synchronous replication works on the master server: mysql > SHOW GLOBAL STATUS LIKE'% semi_sync%' +-+-+ | Variable_name | Value | +-+- -+ | Rpl_semi_sync_master_clients | 1 | shown here There is a semi-synchronous client, which indicates that semi-synchronous is in normal use. If a semi-synchronous timeout is detected due to network or other reasons, the 1 / O thread from the slave server can be restarted to restart the semi-synchronous function.

5. In the master-slave replication architecture, only part of the data solution is replicated, implemented using replication filters

Primary server filtering: the primary server records only decent servers in the binary log

Pros and cons: primary server disk Iram O bandwidth savings

Disadvantages: but other database data is not secure, unable to complete point-in-time restore

Parameter: binlog_do_db= binlog_igore_db=

On the slave server, only the corresponding information is read

Based on the library:

Replicate_do_db= replicate_ignore_db=

Based on table

Replicate_do_table=db_name.tb_name replicate_ignore_table=

Use wildcards based on tables

Replicate_wild_do_table=replicate_wild_ignore_table=

6. Configure multi-master model

Configuration on the MasterA server:

# service mysqld stop# vim / etc/my.cnflog-bin=/mydata/binlogs/master-binrelay-log = / mydata/relaylogs/relay-binauto_increment_offset = 1 # starting position auto_increment_increment = 2 # automatic growth step server-id = 1skipslave slave startup-create a binary log and relay log storage directory and grant permissions to # mkdir-pv / mydata/ {binlogs,relaylogs} # chown-R mysql.mysql / mydata/ {binlogs Relaylogs} # service mysqld start-- create a user with replication permissions > GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *. * TO 'repluser'@'172.16.%.%' IDENTIFIED BY' replpass' Affected (0.01 sec) > FLUSH PRIVILEGES;-- query the current server binaries and event location mysql > SHOW MASTER STATUS +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +- -+-+ | master-bin.000004 | 506 | +-+

Configuration on the MasterB server:

# service mysqld stop# vim / etc/my.cnfserver-id = 11log-bin=/mydata/binlogs/master-logrelay-log = / mydata/relaylogs/relay-binauto_increment_increment = 2auto_increment_offset = 2skipslave slave start service mysqld start---create a binary log and relay log storage directory and grant permissions to # mkdir-pv / mydata/ {binlogs,relaylogs} # chown-R mysql.mysql / mydata/ {binlogs,relaylogs} # service mysqld start-- create a user with replication permissions > GRANT REPLICATION SLAVE REPLICATION CLIENT ON *. * TO 'repluser'@'172.16.%.%' IDENTIFIED BY' replpass' Affected (0.01 sec) > FLUSH PRIVILEGES;-- query the current server binaries and event location mysql > SHOW MASTER STATUS +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +- -+-+ | master-log.000001 | 506 | +-+

MasterB server connection MasterA server configuration operation:

Mysql > CHANGE MASTER TO MASTER_HOST='172.16.100.7',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000004',MASTER_LOG_POS=506 Mysql > SHOW MASTER STATUS\ sec * 1. Row * * File: master-log.000001Position: 506Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec) mysql > START SLAVE Mysql > SHOW SLAVE STATUS\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send eventMaster_Host: 172.16.100.7Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: master-bin.000004Read_Master_Log_Pos: 684Relay_Log_File : relay-bin.000002Relay_Log_Pos: 708Relay_Master_Log_File: master-bin.000004Slave_IO_Running: YesSlave_SQL_Running: Yes

MasterA server connection MasterB server configuration operation:

Mysql > CHANGE MASTER TO MASTER_HOST='172.16.100.8',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-log.000001',MASTER_LOG_POS=506 Mysql > SHOW MASTER STATUS\ gateway * 1. Row * * File: master-bin.000004Position: 506Binlog_Do_DB: Binlog_Ignore_DB: mysql > START SLAVE Mysql > SHOW SLAVE STATUS\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send eventMaster_Host: 172.16.100.8Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: master-log.000001Read_Master_Log_Pos: 506Relay_Log_File : relay-bin.000002Relay_Log_Pos: 530Relay_Master_Log_File: master-log.000001Slave_IO_Running: YesSlave_SQL_Running: Yes

Test (1):

MasterA server operation:

Mysql > CREATE DATABASE newdb

MasterB server operation:

Mysql > USE newdb;mysql > CREATE TABLE T1 (id int unsigned not null primary key auto_increment,name char (20))

Test (2):

MasterA server operation:

Mysql > USE newdb;mysql > SHOW TABLES;+-+ | Tables_in_newdb | +-+ | T1 | +-+

MasterB server operation:

Mysql > USE newdb;mysql > SHOW TABLES;+-+ | Tables_in_newdb | +-+ | T1 | +-+ 1 row in set (0.00 sec) mysql > INSERT INTO T1 (name) values ('jerry'), (' tom'), ('samlee'); mysql > SELECT * FROM T1 +-+-+ | id | name | +-+-+ | 2 | jerry | | 4 | tom | | 6 | samlee | +-+-+

MasterA server operation:

Mysql > INSERT INTO T1 (name) values ('samlee1'), (' samlee2'), ('samlee3'); mysql > SELECT * FROM T1 + | id | name | +-- +-+ | 2 | jerry | 4 | tom | 6 | samlee | | 7 | samlee1 | 9 | samlee2 | 11 | samlee3 | +-+-+

Through the test, we can see that the multi-master model is mainly realized by locking the step value.

Multi-master model and highly available solution:

MMM:Multi Master MySQL----, please continue to follow my blog http://gzsamlee.blog.51cto.com/MHA MMM:Multi Master MySQL---- HA----, please continue to follow my blog http://gzsamlee.blog.51cto.com/.

7. Implement master-slave replication based on SSL:

Please continue to follow my blog http://gzsamlee.blog.51cto.com.

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.