This document has been updated to Github.

Docker mysql installation

# primary serverDocker run --name master-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.30# slave serverDocker run --name sub mysql -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.30Copy the code

Configuring the primary Database

vi /etc/mysql/my.cnf
Copy the code

If you cannot use vi, please refer to ** * at the end of this article

Edit the main database configuration file and add the following code to the end of the configuration file:

[mysqld]
Set the ID of the primary server
server-id=100
innodb_flush_log_at_trx_commit=2
Enable the binlog synchronization function
sync_binlog=1
# binlog Specifies the name of the log file
log-bin=mysql-bin-5
# this means that only one library is synchronized.
# binlog-do-db=xxxx
Copy the code

Docker restart master-mysql, and master database container, connect to mysql

> mysql -u root -p
Enter password:
Copy the code

Set a login account and password for the secondary database

CREATE USER 'slave'@The '%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@The '%';
Copy the code

Show master status \G show master status \G

mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin-5.000001
         Position: 617
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
Copy the code

Record the File and Position fields shown here, which need to be used by the slave server. The values of the two fields here are mysql-bin-5.000001 and 617 respectively. The order cannot be reversed, because the database write operation will cause the value of Position to change.

Configuring the secondary Database

vi /etc/mysql/my.cnf
Copy the code

If you cannot use vi, please refer to q&A at the end of this article.

Edit the main database configuration file and add the following code to the end of the configuration file:

[mysqld]
server-id=200
innodb_flush_log_at_trx_commit=2
sync_binlog=1
log-bin=mysql-bin-6
Copy the code

Docker restart sub-mysql

> mysql -u root -p
Enter password:
Copy the code

Example Set the connection information of the primary/secondary replication

mysql> change master to master_host='10.192.32.156', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql - bin - 5.000001', master_log_pos= 617, master_connect_retry=30;
Copy the code

Parameter description: master_host: IP address of the host (that is, the real machine) master_user: login account assigned by the primary database to the secondary database master_password: login password assigned by the primary database to the secondary database master_port: Master_log_file: specifies the log file of the primary database. The log file capacity will change when the write operation of the primary database reaches the threshold. Master_log_pos: Location in the primary database log file. The value may change due to write operations on the primary database or restart of the container. Master_connect_retry: Reconnection interval when a connection fails

Check master status show slave status \G, Slave_IO_Running and Slave_SQL_Running are both NO, because they are only configured but not enabled

mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 10.192.32.156 Master_User: slave Master_Port: 3306 Connect_Retry: 30 Master_Log_File: Mysql -bin-5.000001 Read_Master_Log_Pos: 617 Relay_Log_File: d0678CE118a6 - relay_bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin-5.000001 Slave_IO_Running: No Slave_SQL_Running: NoCopy the code

Enable the primary/secondary synchronization function

Log in to the mysql database and start the slave/slave synchronization function. Then check the slave/slave synchronization status again

mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 10.192.32.156 Master_User: slave Master_Port: 3306 Connect_Retry: 30 Master_Log_File: Mysql -bin-5.000001 Read_Master_Log_Pos: 617 Relay_Log_File: d0678CE118a6 - relay_bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin-5.000001 Slave_IO_Running: Yes Slave_SQL_Running: YesCopy the code

Now that all master/slave replication has been configured, let’s see what happens

q&a

1. Run the vi /etc/mysql.my. CNF command before the operation fails

apt-get update
apt-get install vim
Copy the code

Check that the Position of the primary database has changed because of database write operations or container restart

3, Slave_IO_Running check connection status change master to… ; Check whether the configuration is correct, where master_host is the address of the host (i.e. the address of the real machine, not 127.0.0.1 or localhost).

For more documentation, see:

Online address [Front-end Tangerine]

GitHub Warehouse