Mac configures master/slave database synchronization for MySQL through Docker. The master library is responsible for writing, and the slave library is responsible for querying. The slave library replicates data synchronously through bin-log.

  1. Install Docker: www.docker.com/
  2. pull mysql
  3. Prepare the mysql configuration file
my-master.cnf
[mysqld]
server-id=100
log-bin=mysql-bin
Copy the code
my-slave1.cnf
[mysqld]
server-id=101
log-bin=mysql-slave-bin
relay_log=mysql-relay-bin
Copy the code
my-slave2.cnf
[mysqld]
server-id=102  
log-bin=mysql-slave-bin   
relay_log=mysql-relay-bin 
Copy the code

4. Start master, create slave users, and authorize them

docker run -d -e MYSQL_ROOT_PASSWORD=root \ --name mysql-master \ -v /Users/rubin/docker/mysql/my-master.cnf:/etc/mysql/my.cnf \ -p 3307:3306 -v / Users/rubin/docker/mysql/mysql - files: / var/lib/mysql - files/mysql: 5.7Copy the code

Master creates and authorizes users. Note that the master must be restarted, otherwise the authorization will not be valid, and the slave library will not be connected.

CREATE USER 'user_slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* to 'user_slave'@'%' identified by '123456';
commit;
show master status;
Copy the code

5. Start Slave1 and connect to Master

docker run -d -e MYSQL_ROOT_PASSWORD=root \ --name mysql-slave1 \ -v /Users/rubin/docker/mysql/my-slave1.cnf:/etc/mysql/my.cnf \ -p 3308:3306 -v / Users/rubin/docker/mysql/mysql - files: / var/lib/mysql - files/mysql: 5.7Copy the code

Master_port is the port mapped to the master

stop slave;
change master to master_host='192.168.1.7', 
    master_port=3307, 
    master_user='user_slave', 
    master_password='123456', 
    master_log_file='mysql-bin.000007', 
    master_log_pos=154;
start slave;
show slave status;
Copy the code

6. Start Slave2 and connect to Master

docker run -d -e MYSQL_ROOT_PASSWORD=root \ --name mysql-slave2 \ -v /Users/rubin/docker/mysql/my-slave2.cnf:/etc/mysql/my.cnf \ -p 3309:3306 -v / Users/rubin/docker/mysql/mysql - files: / var/lib/mysql - files/mysql: 5.7Copy the code

Master_port is the port mapped to the master

stop slave;
change master to master_host='192.168.1.7', 
    master_port=3307, 
    master_user='user_slave', 
    master_password='123456', 
    master_log_file='mysql-bin.000007', 
    master_log_pos=154;
start slave;
show slave status;
Copy the code

Note:

  1. Mysql 5.7, 5.8, I have a problem, can not synchronize.

  2. Mysql and slave versions need to be the same.

  3. The configuration file is written in advance. Ensure that key information is correctly written.

  4. show slave status; After starting the library, you can wait 3 seconds to check the status. Otherwise, you may think that you have been connecting an error message.

Advantages of MySQL master/slave synchronous replication: 1. Read/write separation enables the database to support greater concurrency, which is especially important in reports. As part of the report SQL statements are very slow, resulting in table lock, affecting the front desk services. If the foreground uses master and the report uses slave, the report SQL will not cause foreground lock, ensuring foreground speed. 2. Take advantage of different table engines. Currently Myisam tables are queried slightly faster than InnoDB and write concurrency innoDB is better than Myisam. So we can use InnoDB as master to handle high concurrency writes and master as slave to accept queries. Or create a full text index in MyISam Slave to solve innoDB’s lack of full text index. 3. Data on the slave and master are synchronized in semi-real time.

Mysql replication technology has the following characteristics: 1. Data distribution 2. Load balancing 3. Backups 4. High Availability and failover