Detailed description of MySQL master-slave database construction methods and mysql master-slave Database Construction

Detailed description of MySQL master-slave database construction methods and mysql master-slave Database Construction

This article describes how to build a MySQL master-slave database. We will share this with you for your reference. The details are as follows:

The master-slave server is a good solution for mysql Real-time Data Synchronization and backup. Now, all major and small networks use the mysql database master-slave server function to asynchronously back up the website database, next we will introduce you to 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) ensure that the Mysql version on the Master/Slave server is the same.

(2) On the master server, set an account for the slave database to useREPLICATION SLAVEGrant permissions, for example:

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 primary database, enable BINLOG, and set the value of server-id. After modification, the Mysql service must be restarted.

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

(4) The current binary log name and offset of the master server can be obtained. This operation aims to restore the data from this point after the database is started.

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

(5) Now we can stop updating the primary data and generate a backup for the primary database. We can export 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.

Read lock on the primary database before exporting 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 primary database is backed up.

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

(6) copy test. SQL backed up by the primary data to the slave database for import.

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

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

(8) Start the slave process on the slave server

mysql> start slave;

(9) On the slave servershow salve statusVerify

mysql> SHOW SLAVE STATUSG*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: localhostMaster_User: rootMaster_Port: 3306Connect_Retry: 3Master_Log_File: mysql-bin.003Read_Master_Log_Pos: 79Relay_Log_File: gbichot-relay-bin.003Relay_Log_Pos: 548Relay_Master_Log_File: mysql-bin .003Slave_IO_Running: YesSlave_SQL_Running: Yes

(10) Now we can perform some update operations on our master server, and then check whether the updates have been made on the slave server.