MySQL master-Slave Introduction, preparation, configuration master, configuration slave, test master-Slave synchronization

MySQL Master-Slave introduction

MySQL master-slave is also called replication, AB replication. Simply said that A and b two machines from the back, on a to write the data, the other B will follow the writing data, the two data in real-time synchronization. MySQL master-slave is based on Binlog, the Lord must open Binlog to carry out master and slave.

Master-Slave process:

You need to keep the data consistent to replicate the data

1. The master server logs the change operation to Binlog

2. Synchronize the Binlog event (SQL statement) of the primary server from the server to the native (from the server) and log in the Relaylog (trunk log)

3, from the server according to relaylog inside the SQL statements executed sequentially

Description: The process has three threads, and the master has a log dump thread that is used to transfer binlog from the I/O thread, with two threads on it, where I/O threads are used to synchronize the main binlog and generate Relaylog. Another SQL thread is used to execute the SQL statements inside the Relaylog.

650) this.width=650; "Src=" Https://s2.51cto.com/oss/201711/22/481561cedd4edc4138e9f4a38fe699a2.png-wh_500x0-wm_3 -wmp_4-s_3073073493.png "title=" 1.png "alt=" 481561cedd4edc4138e9f4a38fe699a2.png-wh_ "/>

Scenarios for application:

1. Back up important data

2, share the main library server read pressure, let from the server read, master server write operation

Preparatory work

Primary server: 192.168.3.74 installation mysql5.6

From server: 192.168.3.83 install mysql5.6

Configure the Primary

1, [[email protected] system]# VI/ETC/MY.CNF

[Mysqld]

server-id=132

Log_bin=jacklinux

2. Restart:

[Email protected] system]#/etc/init.d/mysqld restart

Shutting down MySQL. success!

Starting MySQL. success!

[[email protected] system]# ls-lt/data/mysql/#二进制文件, cannot view

-RW-RW----1 MySQL mysql 09:33 jacklinux.index

-RW-RW----1 MySQL mysql 09:33 jacklinux.000001

3. Create a new database to prepare for the test:

[Email protected] mysql]# mysqldump-uroot-p123456 zrlog >/tmp/zrlog.sql

[Email protected] mysql]# mysql-uroot-p123456-e "CREATE Database Jacktest"

[Email protected] mysql]# mysql-uroot-p123456 Jacktest </tmp/zrlog.sql

4. Create a Sync Account

mysql> grant replication Slave on * * to ' repl ' @ ' 192.168.3.83 ' identified by ' 123456 ';

#只给复制权限, all tables for all libraries, IP is from the server IP, account number is REPL, password: 123456

Query OK, 0 rows Affected (0.00 sec)

Mysql> flush tables with read lock;

#先进行锁表, and so on the master server data consistency, and then unlock the table

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

+------------------+----------+--------------+------------------+-------------------+

|    jacklinux.000001 |              10989 |                  |                   | |

+------------------+----------+--------------+------------------+-------------------+

#需要记录file, Position location

Configuration from

1, [[email protected] ~]# VIM/ETC/MY.CNF

[Mysqld]

server-id=130

#必须得和主服务器上的server-id is different.

2, [[email protected] ~]# SCP 192.168.3.74:/tmp/*.sql.

3, mysql> create DATABASE Zrlog;

4. Restore the database

[Email protected] ~]# Mysql-uroot Zrlog < Zrlog.sql

Implement Master-Slave synchronization:

mysql> stop Slave;

Query OK, 0 rows affected, 1 Warning (0.00 sec)

mysql> Change Master to master_host= ' 192.168.3.74 ', master_user= ' repl ', master_password= ' 123456 ', master_log_file= ' jacklinux.000001 ', master_log_pos=10989;

#master_log_file, Master_log_pos: on the primary server

mysql> start slave;

Mysql> Show Slave Status\g

Slave_io_running:yes

Slave_sql_running:yes

5. Unlock the table of the main library (operation in the Lord):

mysql> unlock tables;

At this time, master-slave replication is completed, next verification.

Test Master-Slave synchronization

Primary server:

Binlog-do-db= only the specified libraries are synchronized

binlog-ignore-db= ignores the specified library

From the server:

replicate_do_db= synchronizing the specified library

replicate_ignore_db= ignores the specified library

replicate_do_table= Synchronizing a specified table

Replicate_ignore_table= ignores the specified table

Replicate_wild_do_table= such as aming.%, wildcard characters supported

replicate_wild_ignore_table= Ignore

Verify:

Create a table zrtest on the primary server and insert the data

Mysql> Use Zrlog

Mysql> CREATE TABLE zrtest (id int);

Query OK, 0 rows affected (0.02 sec)

mysql> desc zrtest;

+-------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| ID | Int (11) |     YES | |       NULL | |

+-------+---------+------+-----+---------+-------+

1 row in Set (0.01 sec)

mysql> Insert Zrtest value (1);

Query OK, 1 row affected (0.01 sec)

mysql> Insert Zrtest Value (20);

Query OK, 1 row Affected (0.00 sec)

Mysql> select * from Zrtest;

+------+

| ID |

+------+

| 1 |

| 20 |

+------+

See if there is a zrtest table on the server and the data

mysql> use Zrlog;

Mysql> select * from Zrtest;

+------+

| ID |

+------+

| 1 |

| 20 |

+------+

Verification complete, can be synchronized

This article is from the "Discover new things" blog, make sure to keep this source http://jacksoner.blog.51cto.com/5802843/1983983

MySQL master-Slave Introduction, preparation, configuration master, configuration slave, test master-Slave synchronization