MySQL master slave replication, master master master replication

Want to make APP Same thing as WeChat , Can run small programs smoothly ？ | Experience will send you to Xinjiang 、 Huawei 、 Cherry keyboard ！>>>

See 《 High performance MySql》 Third edition No 10 Chapter copy

Replication is not a backup , It can't replace backup , Please do the database backup work regularly

Suppose two servers ,192.168.0.1（ Lord ） and 192.168.0.2（ from ）

Master slave copy

1.1. Create a copy account

Execute on Main Library ：

mysql> grant replication slave, replication client on *.* to repl@'192.168.0.%' identified by 'password';

explain ：

'repl' To copy the account ;192.168.0.%' It means that the account permission is restricted to the local network to improve the security

replication slave For permission to copy ,replication client Rights to monitor and manage replication , Authorize two permissions to the same account to facilitate management

1.2. Configure master database and slave database

1.2.1. Main library my.cnf Add or modify the following configuration

log_bin=mysql-bin # Specify the binary log name 
server_id=10 # The server id
binlog_format=row # Binary log format changed to line row Pattern

1.2.2. Slave Library my.cnf Add or modify the following configuration

log_bin=mysql-bin # Specify the binary log name 
server_id=20 # The server id
binlog_format=row # Binary log format changed to line row Pattern 
relay_log=/var/lib/mysql/mysql-relay-bin # Specify the location and naming of the relay log 
log_slave_updates=1 # The standby database is allowed to record its replay events in its own binary log 
read_only=1 # read-only

explain ：

The server id A unique must be specified id, Server default id Usually it is 1, In order to avoid conflict, it's best to use meaningful conventions and follow （ Such as ip Address ）;

*_do_db( Such as ：binlog_do_db) and *_ignore_db( Such as ：replicate_ignore_db) Statements that may cause errors to be executed or ignored , It is not recommended to use .

1.3. After modifying the configuration, restart the master-slave server

1.3.1. restart

systemctl restart mysqld

1.3.2. Check whether the restart is normal

View all log configuration commands :

mysql> show global variables like '%log%';

log_error Record start 、 Problems in running or stopping , Warning messages are also recorded .

View error log location ：

mysql> show global variables like 'log_error';
+---------------+------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------+
| log_error | /var/lib/mysql/localhost.localdomain.err |
+---------------+------------------------------------------+
1 row in set (0.00 sec)

Check out the last 100 Line logs ：

tail -100 /var/lib/mysql/localhost.localdomain.err

If there is an error in the log , After fixing the error, continue with the next steps .

1.4. Synchronize database initial data

Before starting master-slave replication, it is necessary to ensure that the data of the two databases are consistent , If the database is newly installed or the data is already consistent, you can skip this step .

MyISAM The engine does not support hot standby ,InnoDB The engine supports hot standby .

1.4.1. MyISAM Engine backup

Read lock first :

mysql> flush tables with read lock;

Then copy the physical files to the slave library , Finally unlock ：

mysql> unlock tables;

1.4.2. InnoDB Engine backup

Hot standby , export sql file ：

mysqldump -uroot -p --single-transaction --master-data=2 --databases dbname --routines > /backup/mysql/dbname_`date '+%Y-%m-%d'`.sql

Or export the compressed file directly ：

mysqldump -u root -p --single-transaction --master-data=2 --databases dbname --routines | gzip > /backup/mysql/dbname_`date '+%Y-%m-%d'`.sql.gz

explain ：

--single-transaction: Based on this option, hot standby can be realized InnoDB surface ; therefore , You don't need to use it at the same time --lock-all-tables;

--master-data=2 Record the location of the binary log at the time of backup , And comment out ,1 It is not annotated ; This is useful for

--databases dbname Specify the database to back up

--all-databases,-A Dump all tables in all databases

--routines,-R Export stored procedures and custom functions

The general database will MyISAM The engine and InnoDB Engines work together , At this time MyISAM The engine is mostly used in tables that read more data and write less data ; If you can be sure MyISAM The engine's table can be used directly without modification InnoDB Backup by hot standby , conversely , You can do it first 'flush tables with read lock' The command is locked and exported .

Directly export compressed sql Files are smaller and easier to transfer , We will introduce the direct import of compressed sql Method of file ; It is said that when the amount of data is large, it is directly imported into the compressed sql Documents are much faster ; Personal test 500MBsql The original document , Compressed size 50MB, Import sql The speed of the original file and the compressed file imported directly is almost the same , about 2 branch 30 second ; In the future, if you encounter a large amount of data, you can try it first , In order to save time in production operation .

Remote copy backup files to slave library ：

scp /backup/mysql/dbname_20161213.sql root@192.168.0.2:/backup/mysql/

Import sql File to Library ：

mysql -u root -p dbname < /backup/mysql/dbname_2016-12-13.sql

Or log in mysql After using source Command import ：

mysql> source /backup/mysql/dbname_2016-12-13.sql;

If you import compressed sql file , The order is ：

gunzip -c dbname_2016-12-13.sql.gz | mysql -uroot -p

1.5. View master server status and binary log information

Execute on Main Library ：

mysql> show master status;

 +------------------+----------+--------------+------------------+
 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 +------------------+----------+--------------+------------------+
 | mysql-bin.000001 | 120 | | |
 +------------------+----------+--------------+------------------+
 1 row in set (0.00 sec)

Current log file name （File=mysql-bin.000001） And the current binary log location （Position=120）, We'll use that later ; If you use the above InnoDB Hot standby way , You can also see the annotated binary file name and log location information in front of the exported file , Similar to the following ：

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=120;

--
-- Current Database: `dbname`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `dbname` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `dbname`;
...

1.6. Start replication

Use the binary log file name recorded above （File=mysql-bin.000001） And the current binary log location （Position=120）

Execute from the library ：

mysql> change master to master_host='192.168.0.1', master_user='repl', master_password='password',master_log_file='mysql-bin.000001',master_log_pos=120;
mysql> start slave;

View the status of the slave server ：

Execute from the library ：

mysql> show slave status\G

You will see the following information ：

*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 10.16.1.202
 Master_User: repl
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000005
 Read_Master_Log_Pos: 4284
 Relay_Log_File: mysql-relay-bin.000002
 Relay_Log_Pos: 2447
 Relay_Master_Log_File: mysql-bin.000005
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 ...

If you see 'Slave_IO_Running: Yes Slave_SQL_Running: Yes' The master-slave copy operation is successful .

You can also view the master-slave server thread status ：

Lord / Execute from the library ：

mysql> show processlist\G

You'll see from the library ：

*************************** 2. row ***************************
 Id: 3
 User: system user
 Host:
 db: NULL
Command: Connect
 Time: 17480
 State: Waiting for master to send event
 Info: NULL
*************************** 3. row ***************************
 Id: 4
 User: system user
 Host:
 db: NULL
Command: Connect
 Time: 17099
 State: Slave has read all relay log; waiting for the slave I/O thread to update it
 Info: NULL

You'll see ：

*************************** 1. row ***************************
 Id: 2
 User: repl
 Host:
 db: NULL
Command: Binlog Dump
 Time: 17604
 State: Master has sent all binlog to slave; waiting for binlog to be updated
 Info: NULL

Two Master master copy （ Take the initiative - Active in passive mode - Master copy ）

Doing the master-slave replication in reverse is master master replication , There's no repetition here , Just talk about the things to pay attention to

2.1. Here's a list of primary and secondary replication configurations

Main library my.cnf Add or modify the following configuration

log_bin=mysql-bin # Specify the binary log name 
server_id=10 # The server id
binlog_format=row # Binary log format changed to line row Pattern 
relay_log=/var/lib/mysql/mysql-relay-bin # Specify the location and naming of the relay log 
log_slave_updates=1 # The standby database is allowed to record its replay events in its own binary log 
read_only=0 # read-write

another “ Lord ” library my.cnf Add or modify the following configuration

log_bin=mysql-bin # Specify the binary log name 
server_id=20 # The server id
binlog_format=row # Binary log format changed to line row Pattern 
relay_log=/var/lib/mysql/mysql-relay-bin # Specify the location and naming of the relay log 
log_slave_updates=1 # The standby database is allowed to record its replay events in its own binary log 
read_only=1 # read-only

explain ：

The mode in which both the master and the master can read and write is called “ Take the initiative - The master in active mode - Master copy ”, It is not recommended to use ; The mode in which one can read and write and the other can only read is called “ Take the initiative - Active in passive mode - Master copy ”, Recommended , It is convenient to switch when the primary server fails , perform 'mysql-> set global read_only=0' The command can set the library to read-write mode ;

Take the initiative - The master in active mode - There is a problem with master replication , Such as ： Database fields a=1, Now execute two sql, One is set a=a+1, One is set a=a*2; a+1 First in db1 The result of execution on the server is 1+1=2,a*2 On the server db2 The result is 1*2=2; then db1 Sync db2 Of sql perform *2 The operation of , That is to say 2*2=4,db2 Also synchronized db1 Of sql perform +1 The operation of , That is to say 2+1=3; The end result of the two machines is different , One is 4 One is 3. Because replication is asynchronous , So there's a delay , If both the master and slave can write data, the consistency of the data may be broken .

Be careful ：

Set up read_only=1 Read only mode , It can limit ordinary users to modify data , But not limited to having super Data modification operations of users with permissions ; It is a good habit to strictly control the permissions of database users .

General application connection database account creation example （ useless super jurisdiction ）：

mysql-> GRANT SELECT,INSERT,DELETE,UPDATE,CREATE,INDEX,ALTER,CREATE VIEW,SHOW VIEW,EXECUTE ON database.* TO username@'%' IDENTIFIED BY 'password';

3、 ... and Routine maintenance

3.1. Turn on 、 stop it 、 Clear copy

Turn on the master-slave , Execute... From the server ：

mysql> start slave;

Master and slave stop , Execute... From the server ：

mysql> stop slave;

Clear the master and slave , Execute... From the server ：

mysql> stop slave;
mysql> change master to master_host=' '; # There is a space between the quotation marks , No space will report an error 
mysql> reset slave;

It can be used show slave status\G The command is in the slave server to see if the master-slave replication state is stopped

3.2. Measuring replication latency

Use on the slave library "show slave status\G" Command output “Seconds_Behind_Master” The delay of slave library is shown theoretically .

Percona Toolkit In the kit pt-heartbeat Is one of the most popular implementations of measuring replication latency .

3.3. Copy data consistency check

use Percona Toolkit In the kit pt-table-checksum Check consistency

3.4. Fix inconsistent data

use Percona Toolkit In the kit pt-table-sync Fix inconsistent data

3.5. There is an error , Need to skip error reporting

mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;

Four appendix

4.1. Percona Toolkit

Official documents ：https://www.percona.com/doc/percona-toolkit/LATEST/index.html

Blog reference ：http://bbs.chinaunix.net/thread-3751657-1-1.html

Blog reference ：https://segmentfault.com/a/1190000004309169

Blog reference ：http://blog.csdn.net/wulantian/article/details/12062005