Tutorial to teach you how to easily set up a traditional master-slave replication environment on Alibaba Cloud and Tencent Cloud

MySQL master-slave replication environment can be said to be the foundation of all high availability. Its principle is also relatively simple. Let's first understand the principle of master-slave replication:

Although there are 7 steps in the picture, it can be simplified to help memory and understanding:

1. Perform modification and writing operations on Master;

2. MySQL writes modified data into binlog;

3. Slave Initiate an IO thread and pull the new binlog on the master into the local relay log;

4. Replay the relay log so that modifications and new operations on the master can be reflected on the slave Run it again on the machine;

5. Slave will also write the operation to the local binlog according to normal operation.

Hardware environment

I have a Diaosi machine each from Alibaba Cloud and Tencent Cloud, so I use it for testing. To protect privacy, the IP has been replaced, but it doesn’t work. affect operations.

Settings on Master

Enable binlog and set server-id

Edit under the mysqld option in /etc/my.cnf, and then restart

[mysqld]
log-bin=mysql-bin
server-id=1003306

Create a dedicated account for replication

/etc/init.d/mysql restart

Export data and synchronize it to Tencent Cloud

Use mysqldump to export all database data. In addition, the current binlog file and position will be recorded in the backup file.

mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slave;
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.200';

You can also view it through the following methods:

#防止DDL、写操作
mysql>FLUSH TABLES WITH READ LOCK;
shell>mysqldump -uroot -p --single-transaction --master-data=2 -A>back.sql

Unlock the table

root@localhost [mysql]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000045 |      939 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Remotely transfer the file to the past

If the data file is too large or there is a lot of data, you can Use percona's xtrabackup to perform backup compression and then transfer it.

mysql> UNLOCK TABLES;

Slave operation

Enable binlog and set server-id

Edit under the mysqld option in /etc/my.cnf

rsync back.sql root@192.168.1.200:/root

Restart the MySQL service

Import data

[mysqld]
log-bin=mysql-bin
server-id=2003306

Point to Master

The MASTER_LOG_FILE and MASTER_LOG_POS are just show master status value, of course you can also use more to view the location that should be specified.

shell>mysqldump -uroot -p --databases t1 <back.sql><p>The <em>MASTER_LOG_FILE</em> and <em>MASTER_LOG_POS</em> here are the values ​​of <em>show master status</em> just now. Of course, you can also use <em>more</em> to view them. The location should be specified. </p>
<pre class="brush:php;toolbar:false">CHANGE MASTER TO MASTER_HOST='192.168.1.200',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='slave',MASTER_LOG_FILE='mysql-bin.000045',MASTER_LOG_POS=939;

Start slave

shell>more back.sql
-- MySQL dump 10.13  Distrib 5.7.14, for linux-glibc2.5 (x86_64)
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version   5.7.14-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

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

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000045', MASTER_LOG_POS=939;

If you see double YES for Slave_IO_Running and Slave_SQL_Running, there is usually no problem.

Verification

Now you can create a new database, create a new data table, insert output, etc. on the master to verify whether the master-slave is effective. This step is up to you to do whatever you want!

The above is the detailed content of Tutorial to teach you how to easily set up a traditional master-slave replication environment on Alibaba Cloud and Tencent Cloud. For more information, please follow other related articles on the PHP Chinese website!