In the actual development process, I have never had the actual combat experience of master-slave replication, but whenever I look for a job, I have to start building rockets. As a PHPer, I’m really in pain. None of the companies experienced has done business, let alone a large number of users. As an amateur webmaster, I have more traffic than those companies.
Too bad to criticize.
1. Docker create container
#Pull MySQL image docker pull mysql #View mirror list docker images #Create and run container without directory mapping set docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql #Create and run containers and set up directory mappings docker run -p 3306:3306 --name mysql -v /usr/local/docker/mysql/conf:/etc/mysql -v /usr/local/docker/mysql/logs:/var/log/mysql -v /usr/local/docker/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql #- – Name: container name, named here ` mysql` #- E: configuration information. Here, configure the login password of the root user of MySQL #- P: port mapping, where host port 3306 is mapped to container port 3306 #- D: run the container in the background to ensure that the container continues to run after exiting the terminal #- V: Directory mapping relationship between host and container. Before ":" is host directory, followed by container directory #View container list Docker PS - a # view a list of all containers Docker PS # view the list of currently running containers #Enter docker container docker exec -it mymysql /bin/bash #To exit the docker container, use the shortcut Ctrl + Q + P #Enter MySQL mysql -uroot -p123456 #If the MySQL instance cannot be linked remotely #1. Check the host firewall port #2. Check MySQL user permissions #Enter docker container，设置所有客户端ip都能登录mysql docker exec -it mysql /bin/bash mysql -uroot -p123456 mysql> grant all privileges on *.* to [email protected]'%' identified by "password"; mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.24 | +-----------+ 1 row in set (0.02 sec)
1. Create two containers, mysql_ Master and MySQL_ The slave port is bound to the 3339 3340 ports of the host computer. Since it’s a test, you don’t bother to set up directory mapping.
docker run -p 3339:3306 --name mysql_master -e MYSQL_ROOT_PASSWORD=123456 -d mysql docker run -p 3340:3306 --name mysql_slave -e MYSQL_ROOT_PASSWORD=123456 -d mysql
2. Enter MySQL_ Master container, modify MySQL configuration file
docker exec -it mysql_master /bin/bash vim /etc/my.cnf #If there is no VIM tool, install the VIM tool apt-get update apt-get install vim
In the [mysqld] node of / etc / my.cnf, add the following configuration information
[mysqld] #Master slave configuration Server id = 1 # server ID Log bin = MySQL bin # binary file storage path Binlog do DB = Palan dev # database to be synchronized Binlog ignore DB = MySQL # unsynchronized data
3. Authorize the account so that it can be copied from the database.
#In docker testing, IP is not limited for convenience #Create user #Create user 'rootslave' @ 'slave server IP address' identified with MySQL_ native_ password BY 'root123'; CREATE USER 'rootslave'@'%' IDENTIFIED WITH mysql_native_password BY 'root123'; #Authorize users #Grant replication slave on *. * to 'rootslave' @ 'slave server IP address'; grant replication slave on *.* to 'rootslave'@'%'；
4. Restart MySQL (exit the container and restart the container)
#Exit MySQL client mysql> quit; #CTRL + Q + P exit container #Restart container docker restart mysql_master #Re enter the container docker exec -id mysql_master /bin/bash #Enter Mysql to view the main database status mysql -uroot -p123456 mysql> show master status;
show master status; The implementation effect is as follows
5. Modify the slave library configuration and also add it in the configuration file / etc / my.cnf
**[mysqld]** #Master slave configuration Server id = 4 # server ID Log bin = MySQL bin # binary file storage path Replicate do DB = Palan dev # database to be synchronized Replicate ignore DB = MySQL # unsynchronized data
6. Restart the slave library
docker restart mysql_slave
7. Enter the container and log in to the MySQL client to realize master-slave synchronization (MySQL master-slave replication – change master to syntax is detailed at the end of the article)
master_ Host: corresponds to the IP of the master database. Here, because the master database and the slave database are on the same server, you can fill in the host IP
master_ Port: the port number of the host machine bound to the corresponding primary library
master_ User: corresponds to the user created in the main library
master_ Password: the corresponding user password
master_ log_ File: show master status of the corresponding master database; File value for
master_ log_ Pos: show master status of the corresponding master database; Position value of
mysql>stop slave; # Close slave Library mysql> change master to master_ Host = 'host IP', master_ port=3339,master_ user='rootslave',master_ password='root123',master_ log_ file='mysql-bin.000002',master_ log_ pos=2374; mysql> start slave; # Enable slave Library (stop Slave: close slave Library) mysql> show slave status \G; # Check server status
mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.136.148 Master_User: rootslave Master_Port: 3339 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 2374 Relay_Log_File: dfeefafe7679-relay-bin.000003 Relay_Log_Pos: 2542 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: palan-dev Replicate_Ignore_DB: mysql 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: 2374 Relay_Log_Space: 2758 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: 100 Master_UUID: 703673b4-aa67-11eb-9570-0242ac110004 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: 86400 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, 1 warning (0.00 sec) ERROR: No query specified
This is just a simple simulation test of master-slave replication environment. The principle of master-slave replication I understand is to create an account in the master database, and then let the slave database read the binary log file of the master database through the account of the master database, so as to update the current operation of the slave database.
In practice, master-slave replication must have many needs for fine setting and optimization.
MySQL master-slave replication used by Alibaba and is easy to use. Why do many small companies like this and ask PHPer to master these skills? I don’t understand.
I am also Xiaobai. All operations refer to other people’s blogs
- Using docker to build MySQL service
- Build master-slave database
- MySQL master-slave replication – change master to syntax explanation
This work adoptsCC agreement, reprint must indicate the author and the link to this article