Mysql master-master replication _ MySQL

The master-slave replication structure of MySQL is different from the master-slave replication structure. In the master-master replication structure, any changes to the data inventory on one of the two servers will be synchronized to the other, so that the two servers are mutually master-slave, and can provide external services. The configuration steps below require that the MySQL master-slave replication structure be different from the master-slave replication structure. In the master-master replication structure, any changes to the data inventory on one of the two servers will be synchronized to the other, so that the two servers are mutually master-slave, and can provide external services. The following configuration steps require two MySQL hosts as servers: for MySQL creation method, see create MySQL database Server-1: 192.168.0.1 Server-2: 192.168.0.2 1. create and authorize users to create a user on each (master) Server and authorize them to access each other's databases on Server-1: create a user named server2 with the password server2 mysql> grant replication slave on *. *> TO 'server2' @ '192. 168.0.2 'identified BY 'server2'; ON Server-2: Create a user named "server1" accessed BY Server-1 with the password "server1 mysql> grant replication slave on *. *> TO 'server1' @ '192. 168. 0.1 'identified BY 'server1'; 2. modify the MySQL master configuration file and add the following content to the MySQL master configuration file: Server-1: [mysqld] server-id = 10 log-bin = mysql-bin replicate-do-db = mydb auto-increment = 2 auto-increment-offset = 1 # service mysqld restart server-2: [mysqld] server-id = 20 log-bin = mysql-bin replicate-do-db = mydb auto-increment = 2 auto-increment-offset = 2 # service mysqld restart note: both of them have different server-IDs. Different from auto-increment-offset, auto-increment-offset is used to set the starting point for automatic growth in the database. in this case, an automatic growth value of 2 is set for both servers, therefore, their starting points must be different to avoid primary key conflicts during data synchronization between the two servers. replicate-do-db specifies the database to be synchronized, we only need to synchronize the mydb database between two servers. The value of auto-increment should be set to the total number of servers in the entire structure. in this case, two servers are used, so the value is set to 2. it is not important to copy the database of one of the servers to another server as the source data. what is important is that in the formal Replication) before the start, both services can prepare data that reflects each other. We can first export data from the source database to the backup file. here we use the mysqldump command to lock the table before using the database on Server-1 as the source database to back up data, ensure Data consistency mysql> flush tables with read lock;> show master status; + pipeline + ------------ + -------------- + parallel + | File | Position | Binlog_Do_DB | parallel | + parallel + ------------ + -------------- + parallel + | mysql-bin.000006 | 213 | + parallel + ------------ + ---------- ------ + -------------------- + 1 row in set (0.01 sec) this result shows, the source server is now at the 213 location of the binary file No. 6 and starts backing up the database # mysqldump -- user = root-p mydb>/tmp/mydb. after the SQL backup is completed, you can UNLOCK the database table mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) 4. import backup data to Server-2. CREATE an empty DATABASE with the same name as mydb on Server-2 # mysql> create database mydb;> \ q # scp 192.168.0.1: /tmp/mydb. SQL. /# mysql-uroot-p mydb </tmp/mydb. SQL at this time, we need to pay attention to the location of the current server's binary log # mysql> Lock talbes with read lock;> show master status; + pipeline + ------------ + -------------- + parallel + | File | Position | Binlog_Do_DB | parallel | + parallel + ------------ + -------------- + parallel + | mysql-bin.000001 | 106 | + parallel + ------------ + ---------------- + -------------------- + 1 row in set (0.00 sec)> unlock tables;> \ q 5. mutual announcement The log location is on Server-1: # mysql> change master to> MASTER_HOST = '2017. 168.0.2 ',> MASTER_USER = 'master1',> MASTER_PASSWORD = 'master1',> MASTER_LOG_FILE = 'MySQL-bin.000001',> MASTER_LOG_POS = 106; on Server-2: # mysql> change master to> MASTER_HOST = '2017. 168.0.1 ',> MASTER_USER = 'master2',> MASTER_PASSWORD = 'master2',> MASTER_LOG_FILE = 'MySQL-bin.000006',> MASTER_LOG_POS = 213; 6. start Replication) the function is executed on both hosts. # Mysql> start slave; configuration is complete! Test: CREATE a DATABASE on any server # mysql> create database repltest;> USE repltest;> create table test (> name char (30);> \ q # view the database information on another server: # mysql> show databases; a database named replrtest is displayed.



Eg: 192.168.0.186 master1 192.168.0.167 master 2
MASTER 1 [mysql @ master1 ~] $ Cat/usr/local/mysql/my. cnf # For advice on how to change settings please see
[Mysqld]
Port = 3308
Skip-name-resolve
Datadir =/cifpay/mysqldb
Basedir =/usr/local/mysql
Log-error =/cifpay/mysqldb/oracle11g.com. err
Pid-file =/cifpay/mysqldb/oracle11g.com. pid
Plugin-dir =/usr/local/mysql/lib/plugin
Socket =/tmp/mysql. sock

# ---- Master -----#
Server_id = 1
Log-bin =/cifpay/mysql-bin.log
Binlog-ignore-db = mysql
Auto-increment = 2
Auto-increment-offset = 1
SQL-mode = NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION ======================= MASTER 2
[Mysql @ master2 ~] $ Cat/usr/local/mysql/my. cnf # For advice on how to change settings please see
[Mysqld]
Port = 3308
Basedir =/usr/local/mysql
Datadir =/cifpay/mysqldb
Log-error =/cifpay/mysqldb/dominic. mysql1.err
Pid-file =/cifpay/mysqldb/dominic. mysql1.pid
Plugin-dir =/usr/local/mysql/lib/plugin
# ---- Master 2 -------#
Server-id = 2
Read_only = TURE
Binlog-ignore-db = mysql
# Log_slave_updates = 1
Log-bin =/cifpay/mysql-bin.log
Auto-increment = 2
Auto-increment-offset = 2
SQL-mode = NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

Restart MASTER 1 and MASTER 2 databases to make the configuration take effect:
MASTER 1: mysql> change master to-> MASTER_HOST = '2017. 168.0.167 ',-> MASTER_USER = 'copy1', -- the user should not be the same-> MASTER_PASSWORD = '000000',-> MASTER_LOG_FILE = 'MySQL-bin.000002 ', -- from master 2-> MASTER_LOG_POS = 753, -- from master 2-> MASTER_PORT = 3308; Query OK, 0 rows affected, 2 warnings (0.08 sec)
Mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status \ G ***************************** 1. row ************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.167 Master_User: copy1 Master_Port: 3308 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 753 Relay_Log_File: oracle11g-relay-bin.000002 Relay_Log_Pos: 283 labels: mysql-bin.000002 labels: Yes labels: failed: counters: 0 Last_Error: Skip_Counter: 0 condition: 753 Relay_Log_Space: 460 Until_Condition: None Until_Log_File: Failed: 0 condition: No condition: Master_SSL_Cert: Keys: Master_SSL_Key: usage: 0 usage: No usage: 0 Last_IO_Error: usage: 0 Last_ SQL _Error: usage: Master_Server_Id: 2 Master_UUID: Master_Info_File:/cifpay/mysqldb/master.info SQL _Delay: 0 usage: NULL usage: slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: role: Master_SSL_Crl: Master_SSL_Crlpath: role: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec)
MASTER 2
Mysql> change master to-> MASTER_HOST = '2017. 168.0.186 ',-> MASTER_USER = 'copy2',-> MASTER_PASSWORD = '000000',-> MASTER_LOG_FILE = 'MySQL-bin.000005', -- from master 1-> MASTER_LOG_POS = 123456, -- from master 1-> MASTER_PORT = 3308; ERROR 1198 (HY000): This operation cannot be stored Med with a running slave; run stop slave first mysql> stop slave; Query OK, 0 rows affected( 0.03 sec)
Mysql> change master to-> MASTER_HOST = '2017. 168.0.186 ',-> MASTER_USER = 'copy2',-> MASTER_PASSWORD = '000000',-> MASTER_LOG_FILE = 'MySQL-bin.000005',-> MASTER_LOG_POS = 123456, -> MASTER_PORT = 3308; Query OK, 0 rows affected, 2 warnings (0.05 sec)
Mysql> start slave; Query OK, 0 rows affected (0.03 sec)
Mysql> show slave status \ G ***************************** 1. row ************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.186 Master_User: copy2 Master_Port: 3308 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 548 Relay_Log_File: dominic-relay-bin.000002 Relay_Log_Pos: 615 labels: mysql-bin.000005 labels: Yes labels: failed: counters: 0 Last_Error: Skip_Counter: 0 condition: 548 Relay_Log_Space: 790 Until_Condition: None Until_Log_File: Failed: 0 condition: No condition: Master_SSL_Cert: Keys: Master_SSL_Key: usage: 0 usage: No usage: 0 Last_IO_Error: usage: 0 Last_ SQL _Error: usage: Master_Server_Id: 1 Master_UUID: Master_Info_File:/cifpay/mysqldb/master.info SQL _Delay: 0 usage: NULL usage: slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: role: Master_SSL_Crl: Master_SSL_Crlpath: role: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec)



-- Test, OK!

-- Master-> Slave: same as master-slave

V. configuration parameters

Server-id

The ID value uniquely identifies the master and slave servers in the replication cluster, so they must be different. Master_id must be a positive integer between 1 and 232-1, and slave_id must be a positive integer between 2 and 232-1.

Log-bin
　　
Indicates that binlog is enabled. enabling this option can write data to Slave's relay-log through I/O, which is also a prerequisite for replication;

Binlog-do-db
　　
Indicates the database for which logs need to be logged. Multiple databases can be separated by commas (,), or multiple binlog-do-db options are used.

Binlog-ignore-db

Indicates the database that does not need to record binary logs. Multiple databases can be separated by commas (,), or multiple binlog-do-db options are used.

Replicate-do-db

Indicates the database to be synchronized. if multiple databases are separated by commas, or multiple replicate-do-db options are used.

Replicate-ignore-db = mysql

Indicates the database that does not need to be synchronized. if multiple databases are separated by commas, or multiple replicate-ignore-db = mysql options are used.

Log-slave-updates

Configure whether the update operation on the slave database is written to the binary file. if this slave database is used as the master database of another slave database, this parameter is required, so that the slave database of the slave database can synchronize logs

Slave-skip-errors

During the replication process, an SQL error occurs in the binlog due to various reasons. by default, slave database stops copying and requires user intervention. You can set Slave-skip-errors to define the error number. if the error number encountered during the replication process is the defined error number, you can skip it. If the slave database is used for backup, set this parameter to avoid data inconsistency. If you want to share the query pressure on the master database, consider it.

Sync_binlog = 1 or N
　　
The default value of sync_binlog is 0. In this mode, MySQL is not synchronized to the disk. In this case, MySQL relies on the operating system to refresh binary logs, just like the mechanism by which the operating system refreshes other files. Therefore, if the operating system or machine (not just the MySQL server) crashes, the last statement in binlog may be lost. To prevent this situation, you can use the sync_binlog global variable to synchronize the binlog with the hard disk after each N binlog writes. When the sync_binlog variable is set to 1, it is the safest, because in the case of crash, your binary log may only lose up to one statement or transaction. However, this is also the slowest way (unless the disk uses a cache with battery backup power, so that the synchronization to the disk is very fast ).

Even if sync_binlog is set to 1, the table content and binlog content may be inconsistent when a crash occurs. If the InnoDB table is used, the MySQL server processes the COMMIT statement, which writes the entire transaction to the binlog and submits the transaction to InnoDB. If a crash occurs between two operations, the transaction is rolled back by InnoDB during the restart, but it still exists in the binlog. You can use the-innodb-safe-binlog option to increase the consistency between InnoDB table content and binlog. (Note:-innodb-safe-binlog is not required in MySQL 5.1. because XA transaction support is introduced, this option is voided.) This option provides greater security, synchronize the binlog (sync_binlog = 1) and (true by default) InnoDB logs of each transaction with the hard disk. this option is effective after the crash and restart, after the transaction is rolled back, the MySQL server splits the InnoDB transaction for rollback from the binlog. This ensures that the binlog reports the exact data of the InnoDB table and keeps the slave server synchronized with the master server (the Rollback statement is not received ).

Auto_increment_offset and auto_increment_increment

Auto_increment_increment and auto_increment_offset are used for master-to-master replication and can be used to control operations on the AUTO_INCREMENT column. Both variables can be set to global or local variables, and each value can be an integer between 1 and 65,535. If one of the variables is set to 0, the variable is set to 1.

These two variables affect the AUTO_INCREMENT column method: auto_increment_increment controls the increment value of values in the column, auto_increment_offset determines the starting point of values in the AUTO_INCREMENT column.

If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored. For example, if some data already exists in the table, the existing maximum auto-increment value is used as the initial value.

VI. clear binary logs

The binary logs generated by the master synchronization server occupy a large amount of disk space. expired bin-logs should be deleted regularly.

A. delete it through PURGE MASTER LOGS

If you have a slave server that is currently reading one of the logs you are trying to delete, this statement does not work, but fails with an error. However, if the slave server is stopped and you happen to have cleared one of the logs it wants to read, the slave server cannot be copied after it is started. This statement can be run safely when the slave server is being copied. You do not need to stop them.

To clear logs, follow these steps:

1. use show slave status on each SLAVE server to check which log it is reading.
2. use show master logs to obtain a series of LOGS on the MASTER server.
3. identify the earliest logs among all slave servers. This is the target log. If all slave servers are updated, this is the last log in the list.
4. back up all logs you want to delete. (Backup is recommended)
5. clear all logs, but not the target logs.

PURGE syntax

PURGE {MASTER | BINARY} logs to 'log _ name'
PURGE {MASTER | BINARY} logs before 'date'

Deletes all binary logs listed in the log index before the specified log or date. These logs will also be deleted from the list of records in the log index file, so that the given log becomes the first.

The date argument for the BEFORE variable can be in the 'yyyy-MM-DD hh: mm: SS' format. Both MASTER and BINARY are synonyms.

For example:

? View Code BASH

123456789

# Delete binlog.000002 without including binlog.000002 mysql>Purge master logs to 'binlog. 100'; # delete mysql before 000002 1:35:00>Purge master logs before '2017-05-28 1:35:00 '; # clear binlogmysql three days ago>Purge master logs before DATE_SUB(NOW( ), INTERVAL 3 DAY);

B. set the expire-logs-days parameter.
　　
The default expire-logs-days is 30 days. It is set to 7 days and can be adjusted as needed.

? View Code BASH

12	[mysqld] expire-logs-days = 7

VII. SQL statements used to control the master and slave servers

A. SQL statements used to control the master server

PURGE MASTER LOGS

Deletes all binary logs listed in the log index before the specified log or date. These logs will also be deleted from the list of records in the log index file, so that the given log becomes the first.

RESET MASTER

You can delete all binary logs listed in the index file, reset the binary log index file to null, and create a new binary log file.

SET SQL _LOG_BIN

If the client uses an account with the SUPER permission to connect, you can disable or enable the binary log of the current connection. If the client does not have this permission, the statement is denied and an error occurs.

SHOW BINLOG EVENTS

Displays events in binary logs. If you do not specify 'log _ name', the first binary log is displayed.

SHOW MASTER LOGS

Used to list binary log files on the server.

SHOW MASTER STATUS
　　
It is used to provide the status information of the binary log file of the master server.

SHOW SLAVE HOSTS

Displays the list of replication slave servers currently registered with the master server.

B. SQL statements used to control slave servers

CHANGE MASTER
　　
You can change the parameters used by the slave server to connect to and communicate with the master server.

LOAD DATA FROM MASTER
　　
It is used to take snapshots of the master server and copy them to the slave server.

Load table tbl_name FROM MASTER

Transfers a copy of a table from the master server to the slave server.

MASTER_POS_WAIT ()

This is actually a function, not a statement. It is used to confirm that the slave server has read and executed the given location of the binary log arriving at the master server.

RESET SLAVE

It is used to make the slave server forget its replication location in the binary log of the master server.

Set global SQL _SLAVE_SKIP_COUNTER

Skips the next n events from the master server. It is useful to recover the replication abort caused by the statement.

SHOW SLAVE STATUS

It is used to provide information about key parameters of the slave server thread.

START SLAVE

Used to start a slave server thread

STOP SLAVE

Used to abort a slave server thread

The above content from the MySQL Official Manual, detailed usage see: http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#reset-master

8. how to improve the reliability of master-slave replication

One-way master-slave replication only saves a copy of the master server in real time. When the master server fails, you can switch to the slave server for further query, but cannot update it.

If two-way replication is used, that is, the two mysql servers act as the master server and the slave server. Both of them can perform update operations and achieve load balancing. when one party fails, the other party will not be affected. However, the bidirectional replication will fail unless any update operation sequence is secure.