Shulou(Shulou.com)05/31 Report--

This article focuses on "the construction of MYSQL master-slave replication". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "the way to build MYSQL master-slave replication".

The principle of master-slave replication of MYSQL is actually the same as that of master-master replication of MYSQL, but the slave database cannot be used for write operations, so there will be some differences in the configuration of some parameters in the process of configuration.

Master-master and master-slave replication architecture diagram

The specific configuration steps for master-slave replication are as follows:

I. configuration of the parameters of the main database

Log-bin=mysql-bin

# binary logging format-mixed recommended

Binlog_format=mixed / / master-slave replication mode, mixed mode (MBR) default

Binlog_cache_size=4M / / sets the binlog cache size

Max_binlog_size=300M / / sets the maximum volume of binlog files

Replicate_do_db=retail

Second, create communication users on the main database

The script is as follows:

Mysql > GRANT REPLICATION SLAVE ON *. * TO 'server01'@'%' IDENTIFIED BY' server01'

3. Back up the main database

To back up the data on Master, first perform the table lock operation, as shown in the SQL statement:

Mysql > FLUSH TABLES WITH READ LOCK; / / lock the database first, and then unlock it after the recovery is complete.

Query OK, 0 rows affected (0.00 sec)

Mysql > reset master

Query OK, 0 rows affected (0.00 sec)

Do not exit the terminal, or the lock will be invalidated; if you do not exit the terminal, open another terminal to package the compressed data file directly or use the mysqldump tool to export the data.

# mysqldump-uroot-p retail > / tmp/retail.sql

Mysql > show master status; / / View the master server binlog and Postion

* * 1. Row *

File: mysql-bin.000001

Position: 2424

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

3. Set the parameter file of SLAVE

# binlog_format=mixed

# required unique id between 1 and 2 ^ 32-1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

Server-id= 3 / / is distinguished from master.

Read_only=1

Replicate_do_db= retail

4. Restore on the SLAVE database

Create retail data

Mysql > create database retail

Import data

Mysql > SOURCE / tmp/retail.sql

Set the connection Master on the SLAVE and start the SLAVE

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.47.149',MASTER_USER='server',MASTER_PASSWORD='server', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=2424

Mysql > SLAVE START / / start the SLAVE service

Mysql > SHOW SLAVE STATUS\ G; / / check the connection of slave

Mysql > SHOW SLAVE STATUS\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.47.149

Master_User: server

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 2424

Relay_Log_File: mysql03-relay-bin.000002

Relay_Log_Pos: 666

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 2424

Relay_Log_Space: 824

Until_Condition: None

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

When the above two values are both YES, the master / slave configuration has been successful.

At this point, I believe you have a deeper understanding of "MYSQL master-slave replication". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.