Quickly build the master-slave database of mysql

Quickly build MySQL master-slave database

What is MySQL master-slave replication?

MySQL Replication (mysql master-slave replication) refers to the way in which data can be replicated from a master node of a mysq database to one or more slave nodes.

  • The main purposes of MySQL's master-slave replication are:
  • Read and write separation: The database has a lock mechanism. When the table is locked, it will affect the read operation. Use master-slave replication, let the master library be responsible for writing, and the slave library for reading. In this way, the lock table appears, and the normal business can also be ensured by reading from the slave library.
  • Real-time data backup: when a node of the database fails, it can quickly switch
  • Highly available HA
  • Institutional expansion

MySQL master-slave replication principle

graph LR A((开始)) --> B(master更新数据) B --> C(master的Log dump Thread线程写binlog) C ==> D([slave的I/O Thread读取binlog]) D --> E([slave把binlog日志写到relaylog中]) E ==> F([slave的sql Thread执行更新]) 
mysql20201209141501.png

728 x 470 958 x 619

Build MySQL master-slave replication

First, we prepare a Linux machine (take CentOS7 as an example), install docker in Linux, and use docker to create the master and slave of the data MySQL database.

Install docker

# 安装docker依赖 yum install -y yum-utils device-mapper-persistent-data lvm2 # 安装docker curl -fsSL https://get.docker.com | bash -s docker --mirror Aliyun # 设置docker开机启动 systemctl enable docker 

Install the master master database of MySQL

docker run -itd --restart always --name mysql-master \ -p 3307:3306 \ -e MYSQL_ROOT_PASSWORD=123456 \ daocloud.io/mysql:5.7 # --restart always 设置数据库容器为开机自启动 # mysql-master为主数据库容器名称,可以自行修改 # -p 3307:3306 为mysql默认的3306端口映射到宿主机的3307端口,可以自行修改 # -e MYSQL_ROOT_PASSWORD=123456 设置主数据库的root密码为123456,可以自行修改 # daocloud.io/mysql:5.7 为mysql数据库镜像,mysql的版本为5.7 
For the first execution, it takes a little longer to download the image of MySQL. After the image is downloaded, it will be very, very fast when the slave database container is created later.

After the script is executed, the main database has been created successfully. At this time, open port 3307 on the host firewall, or close the firewall (the article takes the firewall as an example)

# 关闭防火墙 systemctl disable firewalld 

Install MySQL slave from the database

docker run -itd --restart always --name mysql-slave \ -p 3308:3306 \ -e MYSQL_ROOT_PASSWORD=123456 \ daocloud.io/mysql:5.7 # 命令和创建主数据库一样,只需要修改name和port端口 

At this point, the master database and the slave database have been installed, the next step is to configure them to connect together

Associate mysql-master and mysql-slave to form a master-slave database

Use Navicat and other clients to connect to the mysql-master master database

mysql20201208153434.png

482 x 505

Create a master-slave replication account

GRANT REPLICATION SLAVE ON . TO 'backup'@'%' IDENTIFIED BY 'backup'; SHOW GRANTS FOR 'backup'@'%'; 
mysql20201208154328.png

Modify the mysqld.cnf of the container

Modify mysqld.cnf of mysql-master

Copy mysqld.cnf from the container to the host

# 从mysql-master容器中,拷贝mysqld.cnf文件到宿主机当前目录 docker cp mysql-master:/etc/mysql/mysql.conf.d/mysqld.cnf $PWD/mysqld.cnf 

Modify mysqld.cnf

# 在宿主机上修改mysqld.cnf,修改如下 [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql server-id = 100 log-bin = mysql-bin # server-id 是唯一的服务器id,非0整数即可,但不能重复 # log-bin 使用binary logging, mysql-bin是log的文件名称前缀 

Copy the mysqld.cnf file from the host to the container

# 从宿主机本地路径中,拷贝修改后的mysql.cnf文件到mysql-master容器 docker cp mysqld.cnf mysql-master:/etc/mysql/mysql.conf.d/mysqld.cnf 

Modify mysqld.cnf of mysql-slave

Modify mysqld.cnf

# 在宿主机上修改mysqld.cnf,修改如下 [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql server-id = 101 log-bin = mysql-bin # 与mysql-master相比,server-id发生了变化,不能相同 

Copy the mysqld.cnf file from the host to the container

# 从宿主机本地路径中,拷贝修改后的mysqld.cnf文件到mysql-master容器 docker cp mysqld.cnf mysql-slave:/etc/mysql/mysql.conf.d/mysqld.cnf 

Restart container

docker restart mysql-master # 重启主数据库 docker restart mysql-slave # 重启从数据库 

Use Navicat and other clients to connect to mysql-slave from the database

carried out

CHANGE MASTER TO MASTER_HOST='192.168.1.239', --宿主机ip MASTER_PORT=3307, --mysql-master映射到宿主机的端口 MASTER_USER='backup', MASTER_PASSWORD='backup';START SLAVE; 
mysql20201208184917.png

After success, execute

mysql20201208185110.png

Seeing that Slave_IO_Runing and Slave_SQL_Runing are both Yes, it means that the configuration has been successful

At this point, the master-slave database has been configured and can be verified.

verification

Use Navicat and other client tools to create a library in the mysql-master database, and then open the mysql-slave connection in Navicat and other clients, you can see the same library, which is also created from the library.

Knowledge expansion

Ⅰ. Navicat and other tools, I don’t know which library to choose to create a new query script?

After connecting to the MySQL database with Navicat and other tools, it is found that there are multiple databases. However, before the database is selected, click the query button, and the "New Query" function can not be used to open the query window to execute the script.

If you encounter such a problem, you can select the "MySQL" library, then click Query, and then create a new query, you can open the editing window and write SQL statements.

Ⅱ. The configuration file my.cnf of the master-slave database is incorrect?

Anyone who has some knowledge of MySQL database may know that the configuration file of MySQL database is my.cnf, not the mysqld.cnf file in our article.

Yes, the MySQL configuration file is indeed my.cnf, but when we use docker to install MySQL version 5.7, the configuration file is not the my.cnf file. This is because the version has changed.

Therefore, we chose the /etc/mysql/mysql.conf.d/mysqld.cnf file.

Ⅲ. What does binary logging in the configuration file mean?

Log is an important part of MySQL. MySQL logs mainly include: error log, query log, slow query log, transaction log, and binary log.

The binary log (binlog) is used to record information about write operations (not including queries) performed by the database, and is stored in the disk in binary form. In practical applications, binlog is mainly used in two scenarios: master-slave replication and data recovery

Ⅳ. The slave database cannot connect to the master database, and Slave_IO_Runing and Slave_SQL_Runing are not in the Yes state

Such a situation, indicating that you are connected mysql-slave database script from a problem with the parameters, first STOP SLAVEstop the service from the node, and then execute the modified script, and then START SLAVEstart the service from the node, and then view statusSHOW SLAVE STATUS


This article was originally created by teacher Allen of the Lemon Class, and the source must be indicated for reprinting!
Students who want to know more about consultation can add a counselor to receive related learning materials: qd20150815 Remarks: CSDN to receive