Detailed explanation of MySQL master slave database construction method

This paper describes the construction method of MySQL master-slave database with examples. Share it for your reference, as follows:

Master-slave server is a very good scheme for mysql real-time data synchronous backup. Now all large, medium and small networks will use mysql database master-slave server function to carry out asynchronous backup of website database. Let's introduce the master-slave server configuration steps for everyone.

Master-slave replication of Mysql requires at least two Mysql services. Of course, Mysql services can be distributed on different servers, and multiple services can also be started on one server.

(1) First make sure the Mysql versions on the master and slave servers are the same

(2) On the master server, set up an account from the database, and use the REPLICATION SLAVE Give permissions, 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, open BINLOG, and set the values of server-id. After modification, the Mysql service must be restarted

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

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

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) Well, now you can stop the update operation of the master data and generate a backup of the master database. We can export the data to the slave database through mysqldump. Of course, you can also directly copy the data file to the slave database with cp command

Note that READ LOCK is performed on the master database before exporting data to ensure the uniformity of data

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

Followed by mysqldump

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

It is best to resume the write operation after the backup of the main database is completed

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

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

(7) Then modify the my. cnf of the slave database, add server-id parameters, specify the user used for replication, the ip of the master database server, the port, and the file and location where the replication log is started

[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) In the slave server show salve status Validation

mysql> SHOW SLAVE STATUSG
*************************** 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 updates on our master server, and then check whether it has been updated on the slave server

More readers interested in MySQL can check out the topics on this site: "MySQL Query Skills Encyclopedia", "MySQL Transaction Operation Skills Summary", "MySQL Stored Procedure Skills Encyclopedia", "MySQL Database Lock Skills Summary" and "MySQL Common Functions Summary"

I hope this article is helpful to everyone's MySQL database.