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