MySQLMaster-slave replication is the foundation of high performance and high availability. For applications with intensive database read operations, the load balance of database requests is distributed to different
MySQLServer can effectively reduce the pressure of database. When
MySQLIn the single point fault, it can also realize the failover in a short time. This article is about
MySQLThe built-in replication function of is described.
MySQLReplication data process:
- The main database records the event to the binlog binary log file asynchronously before the data update is submitted. After the logging is completed, the storage engine submits the transaction
- Start an I / O thread from the library to establish a connection with the main library to request the binlog to be updated in the main library. At this time, the binlog dump thread created by the main database, which is a binary dump thread, will notify the I / O thread if there is a new update event; when the thread dumps the binary log and there is no new log, the thread will enter the sleep state.
- After receiving the new event log from the I / O thread of the library, save it to its own relay log
- Read the events in the relay log from the SQL thread of the library and perform an update save.
Configure master-slave Library
In the main library
my.cnfOpen binary log in and set the service ID.
log-bin = mysql-bin server-id = 1
server-idMust be a unique number, must have inconsistent master-slave, and master-slave library must have items set.
log-bin = mysql-bin server-id = 2 log-slave-updates = 1 read-only = 1
From library also on
log-slave-updatesWhen the relay log is set to be replayed from the slave database and recorded in its own binary log, the slave database can be used as the master database of other servers to forward the binary log to other slave databases. This scheme can be considered when making a master multi slave scheme.
Dockerfile building MySQL image
Build required files
slaveThe files are saved separately and not shared. Create a folder first
/usr/local/mysqlThen create it in the directory
slaveTwo directories, and create them separately
- The directory where data files are stored
- Dockerfile save dockerfile content
- init.sql Initialize SQL for database
- my.cnf Database configuration file, configuration mode mentioned above
- start.sh Script when dockerfile builds MySQL
#Using MySQL image to create a new image FROM mysql:5.7.17 ENV MYSQL_ROOT_PASSWORD ytao COPY start.sh /mysql/start.sh COPY my.cnf /etc/mysql/my.cnf COPY init.sql /mysql/init.sql EXPOSE 3306 CMD ["sh", "/mysql/start.sh"]
slaveThey are all built on the same image. It is better to use the same storage engine and other components. Otherwise, exceptions may occur during the replication process.
init.sql Initialization data
--Create data_ Copy database DROP DATABASE IF EXISTS `data_copy`; CREATE DATABASE `data_copy` /*!40100 DEFAULT CHARACTER SET utf8mb4 collate utf8mb4_general_ci */; --Create person table USE `data_copy`; DROP TABLE IF EXISTS `person`; CREATE TABLE `person` ( `id` int(32) NOT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
#!/bin/sh Echo 'start MySQL' service mysql start sleep 5 Echo 'initialize database' mysql -uroot -pytao < /mysql/init.sql Echo 'initialization complete! ' tail -f /dev/null
slaveMirror and run container
docker build -t master/mysql .
docker build -t slave/mysql .
Build success will return
Successfuly, or by
docker imagesCommand view image
Use the image you just built to run the container
#Master container docker run --name master -p 3306:3306 -v /usr/local/mysql/master/data/:/var/lib/mysql -d master/mysql #Slave container docker run --name slave -p 3307:3306 -v /usr/local/mysql/slave/data/:/var/lib/mysql -d slave/mysql
3307, the mount data directory is the directory where the data is stored.
Verify that the database is initialized successfully after connecting to the database
log-binOpen or not
Create a copy account
As mentioned earlier, the slave I / O thread needs to establish a connection with the main database, so it needs to use an account for verification. In addition to the connection client, the account should also have the replication slave.
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO [email protected]'%' IDENTIFIED BY 'ytao';
The access address set here is open, and it must be specified for the sake of security in actual use.
Start replication from library
Connect from the library to the main library, get the binary log and replay it. First of all, configure the account created above to connect, and use the command to set the corresponding settings.
CHANGE MASTER TO MASTER_HOST = '47.107.xx.xxx', MASTER_PORT = 3306, MASTER_USER = 'muser', MASTER_PASSWORD = 'ytao', MASTER_LOG_FILE = 'mysql-bin.000006';
The replication has not started yet. You need to start from the library again
SHOW SLAVE STATUS\G;Command to view the situation after startup
Output information marked above
Slave_SQL_Running: YesYou can see that the I / O thread and the SQL thread are running.
Test synchronization data
If you add, update or delete a data in the master database, there should be data changes corresponding to the master database in the slave database.
Add a piece of data to the main library
INSERT INTO `data_copy`.`person` (`id`, `name`) VALUES ('1', 'ytao');
Query the data from the database. The data has been synchronized.
The above is the simplest and most basic configuration, but understanding the above configuration process, you can customize different schemes according to your own situation, realize one master and many slaves, master master copy (active or active passive mode), etc. to meet your own needs.
MySQLAlthough the replication of is simple and convenient to use, there are also some problems that need to be solved in use, such as: can’t recover from abnormal stop of server, delay of data synchronization, etc. fortunately, most of the problems encountered now have been solved in the industry. Interested in this aspect, you can learn about the middleware implementation solutions to solve these problems.
Personal blog: https://ytao.top
My official account ytao