MySQL master-slave Replication

MySQL master-slave Replication

The principle of MySQL master-slave replication is the same as that of MySQL master-slave replication, but slave databases cannot be used for write operations, therefore, some parameter configurations may be different during the configuration process;

Architecture of Master/Slave Replication

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

1. master database parameter configuration

Log-bin = mysql-bin
# Binary logging format-mixed recommended
Binlog_format = mixed // master-slave replication mode. The default value of the hybrid mode (MBR) is
Binlog_cache_size = 4 M // you can specify the binlog cache size.
Max_binlog_size = 300 M // you can specify the maximum size of the binlog file.
Replicate_do_db = retail
 

2. Create a communication user on the master database

The script is as follows:

Mysql> grant replication slave on *. * TO 'server01' @ '%' identified by 'server01 ';

3. Back up the primary database

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

Mysql> flush tables with read lock; // first LOCK the database and unlock it after the restoration 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. Otherwise, the lock becomes invalid. If you do not exit the terminal, open another terminal to directly package and compress the data file or use the mysqldump tool to export the data.

# Mysqldump-uroot-p retail>/tmp/retail. SQL

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

* *************************** 1. row **************************************

File: mysql-bin.000001

Position: 2424

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

3. Set the SLAVE parameter file

# 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 // separate it from the master
Read_only = 1
Replicate_do_db = retail

4. Restore the SLAVE Database

Create retail data

Mysql> create database retail

Import Data

Mysql> SOURCE/tmp/retail. SQL

5. Set the connection to the 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 = 192;

Mysql> slave start // START the SLAVE Service

Mysql> show slave status \ G; // check the connection STATUS of the 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

If both values are YES, the Master/Slave configuration is successful.

Load Nginx in Ubuntu for high-performance WEB Server 5 --- MySQL master/Master Synchronization

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL Master/Slave failure error Got fatal error 1236

MySQL master-slave replication, implemented on a single server

Build a MySQL proxy server for read/write splitting + Master/Slave Synchronization

MySQL 5.5 master-slave bidirectional Synchronization

MySQL 5.5 master-slave synchronization troubleshooting

MySQL master-slave replication asynchronous semi-sync instance

This article permanently updates the link address: