Master slave replication configuration of MySQL

Abstract

Large projects pay special attention to backup , Generally, there is dual computer backup , Hot standby and cold standby , Remote disaster recovery and so on … Today, let's talk about... On two servers MySQL Master and slave copy backup , The requirements are simple ： Slave data to be synchronized with master , But not too often , keep 15 Minutes of data difference , It means every 15 Minutes to synchronize once modified data .

Text

Our environment here is two Linux The server , One is on the public network , One is on the intranet , The public network is the master , Intranet is from , The databases are MySQL 5.7（ Use LNMP install ）.

Main warehouse operation

First, let's create a new data synchronization user , Let users use this from the library to synchronize data

CREATE USER 'slave'@'%' IDENTIFIED BY ' password ';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

The percent sign indicates that the user can use any number of IP Sign in .

Then we need to back up the main database , If your library is not accessed at this time , Then you don't have to lock the watch , But it's best to lock the table first and then back up

mysql> flush tables with read lock;

Use mysqldump Back up libraries that need to be synchronized , I'm syncing here mikublog This library

mysqldump -uroot -p --databases mikublog | gzip > mysql_bak.$(date + %F)sql.gz

After the backup, record the current location , remember File and Position Value

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000014 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Last unlock table

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

Operation from the library

Copy the compressed package just backed up , And extract the ：gzip -d Compressed package name , And then put sql Brush it into the database .

modify my.cnf, Generally, you only need to change two values , hold server-id Change to unique value

[mysqld]
log-bin=mysql-slave-bin
server-id   = 2

After the change, restart the database

service mysql restart

Set the master-slave configuration

mysql> change master to master_host='192.168.100.110', master_user='slave', master_password='mypassword', master_port=3306, master_log_file='mysql-bin.000014', master_log_pos= 154, master_delay=900, master_connect_retry=30;

Meaning of each parameter ：

master_host ： The address of the main library 

master_port： The port number of the main library 

master_user： Users for data synchronization 

master_password： Password of the user for synchronization 

master_log_file： Appoint  Slave  From which log file to start copying data , As mentioned above  File  Value of field 

master_log_pos： From which  Position  Start reading , As mentioned above  Position  Value of field 

master_delay： Delayed replication configuration , Set the delay from the database  N  Seconds later , And then synchronize with the master database 

master_connect_retry： If the connection fails , Retry interval , The unit is seconds , The default is  60  second 

Enable master-slave replication

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

View synchronization status

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                             ...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                             ...
1 row in set (0.00 sec)

SlaveIORunning and SlaveSQLRunning All are Yes Indicates that master-slave replication has been turned on .

The end

That's all MySQL The content of the master-slave replication configuration , Welcome friends to exchange and discuss .