Shulou(Shulou.com)05/31 Report--

How to achieve the separation of reading and writing in MySQL? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

First, create a configuration file for the Master database

Vi master.cnf [mysqld] # master server id server-id = 1 # bin log log_bin = mysql-master-bin

Server-id, in MySQL cluster database, this parameter must be unique

Log_bin,MySQL uses the file name copied by the binary log file

Second, copy the configuration file to the MySQL Docker container

Docker run-name mysql-e MYSQL_ROOT_PASSWORD=root-d mysql docker cp master.cnf fdb98bbd52b6:/etc/mysql/conf.d

Third, submit the modified Docker container

Docker commit-m "add master configure file" fdb98bbd52b6 mysql:master [root@dev01 ~] # docker images REPOSITORY TAG IMAGE ID CREATED SIZE mysql master 345465966cb5 3 hours ago 445MB

Commit-m is the information submitted after modification of the container, similar to Git submission

Fdb98bbd52b6 is the container you just modified.

Mysql:master is when we tag the modified container with a tag tag master

Next we modify the container configuration file for MySQL Slave (from the database)

First, create a configuration file for the Slave database

Vi slave.cnf [mysqld] # slave server id server-id = 2 # bin log log_bin = mysql-slave-bin relay_log = mysql-relay-bin log_slave_updates = 1 read_only = 1

Server-id is the ID of the slave database. This parameter must be unique in the MySQL cluster.

Log_bin if slave is the master of other slave, bin_log must be set. Here, we enable it temporarily.

Relay_log configuration Relay Log

Log_slave_updates indicates that slave writes replication events to its own binary log (see its usefulness later)

Read_only uses read_only as much as possible, which prevents data changes (except for special threads)

Second, copy the configuration file to the container

Docker run-name mysql-e MYSQL_ROOT_PASSWORD=root-d mysql docker cp slave.cnf 8ee82abb2e91:/etc/mysql/conf.d

Third, submit the modified Docker container

Docker commit-m "add slave configure file" 8ee82abb2e91 mysql:slave [root@dev01 ~] # docker images REPOSITORY TAG IMAGE ID CREATED SIZE mysql slave 3a53cd39ee45 4 hours ago 445MB

At this point, the two required MySQL containers have been modified and stored in our local container repository, and then we start to start the two containers just modified for subsequent configuration.

First, start the Master database

Docker run-- name master-e MYSQL_ROOT_PASSWORD=root-d mysql:master

-- name master is the name of the container we started.

Mysql:master is the Docker image we just modified and submitted to the local image.

The password for the default database root is set to root

Second, start the Slave database

Docker run-- link master:master-- name slave-e MYSQL_ROOT_PASSWORD=root-d mysql:slave

For the network interconnection between the master container and the slave container, we added the-- line option to connect to the master container we just started

-- name slave is the name of the container we started.

Mysql:slave is the Docker image we just modified and submitted to the local image.

The password for the default database root is set to root

Third, enter the master container and use the mysql command to enter the database

Docker exec-it master / bin/bash mysql-u root-proot

Fourth, create an account in the master database for copying data, and give the account the corresponding permissions.

Create user 'repl'@'%' identified by' repl-pwd'; grant replication slave on *. * to 'repl'@'%'; flush privileges

Fifth, check the status of the master database

Mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | + -+ | mysql-master-bin.000003 | 2743 | +- -+ 1 row in set (0.00 sec)

6. Enter the slave container and use the mysql command to enter the database

Docker exec-it slave / bin/bash mysql-u root-proot

Seventh, configure slave, point the master database to the master database node you just configured, and start slave

Change master to master_host='master', master_user='repl', master_password='repl-pwd', master_log_file='mysql-master-bin.000003', master_log_pos=0; start slave

Eighth, check the status of the slave database

Mysql > show slave status\ G * * 1. Row * * Slave_IO_State: Master_Host: master Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-master-bin.000003 Read_Master_Log_Pos: 4 Relay_Log_File: mysql -relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-master-bin.000003 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 4 Relay_Log_Space: 155 Until _ Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 2061 Last_IO_Error: error connecting to master 'repl@master:3306'-retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_UUID: Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: 190912 06:06:14 Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_ Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set (0.00 sec)

Since the latest version of MySQL has changed the password verification plug-in to improve security, we will use the previous password verification plug-in here. We use the following command on the master database to modify the user we just created in the master database

Alter user 'repl'@'%' identified by' repl-pwd' password expire never; alter user 'repl'@'%' identified with mysql_native_password by' repl-pwd'; flush privileges

Nine, check the slave status again

Mysql > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: master Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-master-bin.000003 Read_Master_Log_Pos: 2743 Relay_ Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 2971 Relay_Master_Log_File: mysql-master-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 2743 Relay_Log _ Space: 3179 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server _ Ids: Master_Server_Id: 1 Master_UUID: f6e8062e-d521-11e9-9009-0242ac110008 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log Waiting for more updates Master_Retry_Count: 0 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set (86400 sec)

We mainly look at Slave_IO_Running and Slave_SQL_Running, indicating that our slave database node has successfully connected to our master database node.

Ten, verify that we create an empty database on the master database node

Mysql > create database data; Query OK, 1 row affected (0.10 sec) mysql > show databases; +-+ | Database | +-+ | data | | information_schema | | mysql | | performance_schema | | sys | +-+ 5 rows in set (0.01sec)

Eleven, verify slave to see if the database you just created on the master database node is synchronized to the slave data node

Mysql > show databases; +-+ | Database | +-+ | data | | information_schema | | mysql | | performance_schema | | sys | +-+ 5 rows in set (0.01 sec)

You can see that the database we just created on the master database node has been synchronized to our slave database node, and the database data for master and slave has been consistent.

This is the answer to the question about how to achieve the separation of reading and writing in MySQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about 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.