Master-Slave synchronization of MySQL database

First, overview:

mysql master-Slave synchronization architecture is one of the most used database schemas, Master-Slave synchronization allows data to be replicated from one database server to another, when data is replicated, one server acts as the primary server (master), and the remaining servers act as slave servers (slave).

Second, the topology diagram illustrates:

As shown, 192.168.4.10 (host name "10.mysql") as the MySQL master database, 192.168.4.20 (hostname "20.mysql") as MySQL from the database, responsible for synchronizing data from the primary database.

Third, master-slave synchronization configuration instructions:

1. master Configuration

(1) Database authorization:

mysql> grant replication Slave on * * to [e-mail protected] "192.168.4.12" identified by "123456";

(2) Enable Binlog log:

[Email protected] ~]# vim/etc/my.conf
[Mysqld]
server_id=10 #指定主MySQL数据库的ID
Log-bin=master10 #启用binglog日志, the log file is saved in the MySQL installation home directory (/VAR/LIB/MYSQL), the format of the file name is "master22.000001" (Maximum capacity 500M per file, Automatically generate the next log file after exceeding 500M or restarting the MySQL service)

(3) View master status:

Mysql>show Master status;

+-----------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+-----------------+----------+--------------+------------------+-------------------+
|      master10.000001 |              154 |                  |                   | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in Set (0.00 sec)
2, slave configuration:

(1) Configuration server_id:

     (2) Configuring the primary database information (log in using the root user of this machine)
       Mysql>change Master to master_host= "192.168.4.10",              ,
             master_user= "Slaveuser",
             master_password= "123456",
              matser_log_file= "master10.000001",
              master_log_pos=154;
    (3) Turn on slave status
             mysql> Start slave;
     (4) Check slave status
             mysql> show slave status\g;

The following two items in the command execution result indicate that the master-slave synchronization configuration is normal
Slave_io_running:yes
Slave_sql_running:yes

3. Verification:

In the main database (192.168.4.10) above the data add delete operations, on the database above (192.168.4.20) can be seen in a timely manner.

Three, the principle explanation:

(1) Read the Binlog log of the primary database from the IO thread of the database, write to the trunk log file from the database, read the SQL command from the database's SQL thread from the native relay log, and complete the data synchronization;
(2) If the master-slave database configuration before the data inconsistency, the SQL thread will error, if the primary database to create an authorized user error, resulting from the database can not connect to the primary database, the IO thread will error;
(3) When the SQL command from the database's SQL thread executes in the trunk log fails, the SQL thread immediately goes down.

It is important to note that before you configure the MySQL master-slave database, you need to ensure that data from the database cannot be more than the primary database.

Master-Slave synchronization of MySQL database