Shulou(Shulou.com)06/02 Report--

In this paper, we practice using Docker to realize MySql master-slave replication based on binlog. The principle of MySql master-slave replication will be explained in the next article.

Local storage directory structure of some data

Mysql > tree-L 2. ├── data │ ├── master01 │ └── slave01 ├── master01 │ └── master01.cnf └── slave01 └── slave01.cnf

Master01.cnf configuration

[mysqld] log-bin=mysql-master01-bin # uses the prefix server-id=1 # of log filename server-id=1 # unique server ID, which is a non-zero integer and cannot be duplicated with the server-id of other servers

Slave01.cnf configuration

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

Create a master01 container

The copy code is as follows:

Docker run-d-- name master01-v / Users/craneyuan/backup/docker/mysql/data/master01:/var/lib/mysql-v / Users/craneyuan/backup/docker/mysql/master01:/etc/mysql/conf.d-e MYSQL_ROOT_PASSWORD=123456-p 3307 Users/craneyuan/backup/docker/mysql/data/master01:/var/lib/mysql 3306 mysql:5.7

Note: do not connect to the database immediately when you start the container, which will cause some strange problems, such as not being able to connect, you can delete all the data and restart the container.

Create a slave01 container

The copy code is as follows:

Docker run-d-- name slave01-v / Users/craneyuan/backup/docker/mysql/data/slave01:/var/lib/mysql-v / Users/craneyuan/backup/docker/mysql/slave01:/etc/mysql/conf.d-e MYSQL_ROOT_PASSWORD=123456-p 3308 Users/craneyuan/backup/docker/mysql/data/slave01:/var/lib/mysql 3306 mysql:5.7

Add replication users to master01

SET sql_mode= (SELECT REPLACE (@ @ sql_mode,'ONLY_FULL_GROUP_BY','')); GRANT REPLICATION SLAVE ON *. * to 'backup'@'%' identified by' 123456 shows master status; # to view the status of master

Configure master01-related content in slave01

SET sql_mode= (SELECT REPLACE (@ @ sql_mode,'ONLY_FULL_GROUP_BY',''))

Change master to master_host='192.168.10.223',master_port=3307,master_user='backup',master_password='123456',master_log_file='mysql-master01-bin.000003', master_log_pos=154 Master_host is the IP of the host (the operating system IP found by ifconfig, not the IP of the container, must not be mistaken) master_port is the port mapped to 3306 of the master server (default 3306) master_user is the backup user created by master_password, the password of the backup user master_log_file and master_log_pos are the two values in the show master status list, which are the MySQL log name and the starting backup location

Start slave (still operating in slave01)

Start slave; show slave status; or show slave status\ G

If it is a Waiting for master to send event, the master / slave copy is successful. If the Connecting to master must be misconfigured, the landlord will make a mistake several times. Just delete the configuration again. This is the advantage of the container. The cost of deletion is very small, and it can be restored in a short time. If it is Waiting for master to send event, the additions, deletions, changes, changes and changes to the master database will be modified synchronously.

Extension problem

Slave is set to read-only

Add the following options to the configuration file of slave

Read-only=1 # unless you have SUPER permission, read-only super-read-only=1 # SUPER permission is also read-only

Other

If master already has data, how to add slave: you can first import master data into slave, and then start slave. Please refer to here for details.

If you already have master-slave replication, how to add slave: the idea is the same as above, but you don't need to use master data, just use existing slave data. There is no need to stop master, the new slave uses the new server-id. Please refer to here for details.

All of the above are master and slave. If you need slave to synchronize to master, you should set up master and master replication: that is, do it the other way around.

When there is a lot of slave, the load of master can be a problem. You can use master-slave multilevel replication: using slave as the master to introduce a new slave.

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.