Shulou(Shulou.com)06/01 Report--

Today, I will talk to you about how to build master-slave replication of MYSQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

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.

After reading the above, do you have any further understanding of how to build MYSQL master-slave replication? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.