MySQL Master-Slave introduction

MySQL Master-Slave introduction

? MySQL master-slave is also called replication, AB replication. Simply speaking is a and b two machines from the back, write the data on a, the other B will follow the writing data, both data real-time synchronization
? MySQL master-slave is based on Binlog, the Lord must open Binlog to carry out master and slave.
The basic process for replication is as follows:
1), slave above the Io_thread connection on master, and request from the specified log file at the specified location (or from the beginning of the log) after the contents of the log;
2), Master received a request from slave io_thread, through the IO process responsible for copying the request information to read the log information after the designated location, return to slave io_thread. In addition to the information contained in the log, the returned information includes the information returned to the master side of the Bin-log file and Bin-log pos;
3), slave io_thread received the message, the received log content is added to the end of the slave relay-log file, and the read to the master side of the Bin-log file name and location to the Master-info files, In order to be able to tell Master clearly at the next reading, "I need to start from somewhere in the Bin-log log content, please send me";
4), slave Sql_thread detects the new content in the Relay-log, will immediately parse the content of Relay-log as the actual execution of the master side of the executable content, and in this database execution.

Preparatory work

First, two machines are ready.
A machine: 192.168.177.100 Main
B Machine: 192.168.177.200 from
Both machines need to have MySQL installed, and MySQL will be started.

Configure the Primary
1. Modify the MySQL configuration file for a machine
vim /etc/my.cnf

Add the following content

server-id=100log_bin=test1

2. Reload the configuration file when you are finished modifying the Save
/etc/init.d/mysqld restart

3. After restarting the MySQL directory will produce two files test1 the beginning of the file is to achieve the root of the master-slave

-rw-rw----. 1 mysql mysql       15 4月    3 22:15 test1.index-rw-rw----. 1 mysql mysql      120 4月   3 22:15 test1.000001

4. Create MySQL user, username testuser, password is testpasswd

The following are all performed in MySQL
create user ‘repl‘@‘slave_ip‘ identified by ‘1234‘;

5. Hold Status
flush tables with read lock;

6. Remember file and position, execute command show master status;

mysql> show master status;+--------------+----------+--------------+------------------+-------------------+| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+--------------+----------+--------------+------------------+-------------------+| test1.000001 |    620      |                       |                           |                              |+--------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

Configuration from:
1. Configure the server ID to be different from the master
vim /etc/my.cnf

Add the following content:
server-id=200

2. After modifying the configuration file, restart the MYSQLD service
/etc/init.d/mysqld restart

3. Copy the backup database on the A machine to the B machine, my backup is stored in the/tmp directory, and all the files at the end of SQL
scp 192.168.177.100:/tmp/*.sql /tmp/

4. Create the corresponding database on B after copying the database of a

create database zrlog;create database wordpress;

5. Recovering the database on the B machine

mysql zrlog < /tmp/zrlog.sqlmysql wordpress < /tmp/wordpress.sql

6.B machine login mysql, execute command
stop slave；

7.change Master
change master to master_host=‘192.168.177.100‘, master_user=‘testuser‘, master_password=‘testpasswd‘, master_log_file=‘test1.000001‘, master_log_pos=620;

8. Start the service
start slave;

9. How to determine whether Master and slave is configured successfully
Using commands

mysql> show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.177.100                  Master_User: testuser                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: test1.000001              Read_Master_Log_Pos: 1368               Relay_Log_File: lz02-relay-bin.000008                Relay_Log_Pos: 279        Relay_Master_Log_File: test1.000001             Slave_IO_Running: Yes            Slave_SQL_Running: Yes                        #主要查看这里是否为YES                Last_IO_Errno: 0                Last_IO_Error:               Last_SQL_Errno: 0               Last_SQL_Error:

Test master/Slave success

1. Several configuration parameters
? on the primary server
? binlog-do-db=//Synchronize only the specified libraries
? binlog-ignore-db=//Ignore specified library

? From the server
? replicate_do_db=
? replicate_ignore_db=
? replicate_do_table=
? replicate_ignore_table=
? replicate_wild_do_table=//As aming.%, wildcard% supported
? replicate_wild_ignore_table=

MySQL Master-Slave introduction