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: