- 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 [email protected]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 [email protected]'%' 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