Detailed explanation of the method of establishing master-slave database in MySQL

This article mainly introduces the MySQL master-slave database construction method, and analyzes the principles, steps and specific operation techniques of MySQL master-slave database construction in more detail. Friends in need can refer to the following

This article explains the examples Learn how to build a MySQL master-slave database. Share it with everyone for your reference. The details are as follows:

The master-slave server is a very good solution for mysql real-time data synchronization backup. Now all large, medium and small networks will use the mysql database master-slave server function to The website database has been backed up asynchronously. Let’s introduce the master-slave server configuration steps to you.

Mysql’s 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 an account for the slave database and use REPLICATION SLAVE to grant it 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 value of server-id. After modification, the Mysql service must be restarted

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

After (4), you can get the current binary log name and offset of the main server. The purpose of this operation is to start data processing from this point after starting from the database. Recovery

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 use the cp command to copy the data file to the slave database

Be careful to READ LOCK the master database before exporting the data to ensure data consistency

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 restore the write operation after the main database backup is completed

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

(6) Copy the test.sql of the main data backup just now to the slave database and import it

(7) Then modify the my.cnf of the slave database, add the server-id parameter, and specify The user used for replication, the ip and port of the primary database server, 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) Perform show salve statusverification on the slave server

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) Okay, now we can do some update operations on our main server, and then check whether it has been updated on the slave server

The above is the detailed content of Detailed explanation of the method of establishing master-slave database in MySQL. For more information, please follow other related articles on the PHP Chinese website!