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:
Perform modification and writing operations on Master;
MySQL writes modified data into binlog;
Slave Initiate an IO thread and pull the new binlog on the master into the local relay log;
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;
Slave will also write the operation to the local binlog according to normal operation.
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.
Host | ip | Port | Version | Master-Slave |
---|---|---|---|---|
Alibaba Cloud | 192.168.1.100 | 3306 | MySQL5.7.14 | Master |
Tencent Cloud | 192.168.1.200 | 3306 | MySQL5.7.18 | Slave |
Edit under the mysqld option in /etc/my.cnf, and then restart
[mysqld] log-bin=mysql-bin server-id=1003306rrree
/etc/init.d/mysql restart
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)
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;
Edit under the mysqld option in /etc/my.cnf
rsync back.sql root@192.168.1.200:/root
Restart the MySQL service
[mysqld] log-bin=mysql-bin server-id=2003306
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;
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.
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!