Detailed explanation of MySQL master-slave database construction method

This article describes how to build a MySQL master-slave database. Share with you for your reference, the details are as follows:

The master-slave server is a very good solution for MySQL real-time data synchronization and backup. Now all large, medium and small websites use the MySQL database master-slave server function to perform asynchronous backup of the website database. Now we will introduce the master-slave server configuration steps.

MySQL master-slave replication requires at least two MySQL services. Of course, MySQL services can be distributed on different servers, or multiple services can be started on one server.

(1) First, ensure that the MySQL versions on the master and slave servers are the same

(2) On the master server, set up a slave database account and grant permissions using REPLICATION SLAVE , such as:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave001'@'192.168.0.99' IDENTIFIED BY
'123456';
Query OK, 0 rows affected (0.13 sec)

(3) Modify the configuration file my.cnf of the main database, enable BINLOG, and set the value of server-id. After the modification, you must restart the MySQL service.

[mysqld]
log-bin = /home/mysql/log/mysql-bin.log
server-id=1

(4) You can then get the current binary log name and offset of the master server. The purpose of this operation is to recover data from this point after the slave database is started.

mysql> show master statusG;
*************************** 1. row ***************************
File:mysql-bin.000003
Position: 243
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

(5) OK, now we can stop updating the master data and generate a backup of the master database. We can export the data to the slave database using mysqldump. Of course, you can also directly copy the data file to the slave database using the cp command.

Note that you should READ LOCK the primary database before exporting data to ensure data consistency.

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.19 sec)

Then mysqldump

mysqldump -h127.0.0.1 -p3306 -uroot -p test > /home/chenyz/test.sql

It is best to restore the write operation after the primary database is backed up.

mysql> unlock tables;
Query OK, 0 rows affected (0.28 sec)

(6) Copy the test.sql file from the master data backup to the slave database and import it

(7) Then modify the my.cnf file of the slave database, add the server-id parameter, specify the user used for replication, the IP and port of the master database server, and the file and location where the replication log is to be executed.

[mysqld]
server-id=2
log_bin = /var/log/mysql/mysql-bin.log
master-host =192.168.1.100
master-user=test
master-pass=123456
master-port =3306
master-connect-retry=60
replicate-do-db =test

(8) On the slave server, start the slave process

mysql> start slave;

(9) Verify the slave server by running show salve status

mysql> SHOW SLAVE STATUS G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: root
Master_Port: 3306
Connect_Retry: 3
Master_Log_File:mysql-bin.003
Read_Master_Log_Pos: 79
Relay_Log_File: gbichot-relay-bin.003
Relay_Log_Pos: 548
Relay_Master_Log_File:mysql-bin.003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

(10) OK, now we can do some update operations on our master server, and then check whether the update has been completed on the slave server.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.