MySQL master-slave replication principle and configuration process

First, the MySQL database master-slave replication principle process:

MySQL's master-slave replication is an asynchronous replication process where data is copied from one MySQL database (master) to another MySQL database (slave), and the process of implementing the master-slave replication between master and slave is done by a three-thread engagement. There are two threads (SQL thread and I/O thread) on the slave side, and another thread (I/O thread) on the master side, to realize the master-slave replication of MySQL, first of all the Binlog logging function must be turned on, otherwise it will not be implemented. Because the entire replication process is actually slave to get the Binlog log from master, and then perform the various SQL operations that are recorded in the Binlog log in the same order on slave to turn on the Binlog logging feature of MySQL, This can be achieved by adding the "log-bin" parameter option to the Mysqld module ([mysqld]) in the MySQL configuration file my.cnf, as follows:

[mysqld]log-bin =/data/3306/mysql-bin

Second, the MySQL master-slave copy process description

1, execute the start slave command on the slave server, turn on the master-slave copy switch, start the master-slave replication.

2. At this point, the I/O thread of the slave server connects to the master server through a request for replication user rights already authorized on master, and requests a specified location from the specified Binlog log file
(The log file name and location are specified by the Change Master command when you configure the master-slave Replication Service) to start sending Binlog log content

3. After the master server receives a request from an I/O thread from the slave server, the I/O thread on which the replication is responsible reads the specified data in batches based on the information requested by the slave server I/O thread
The Binlog log file specifies the Binlog log information after the location, and then returns the I/O thread to the slave side, with the information returned in addition to the Binlog log content, as well as the new binlog of the master server-side record
The file name, and the next specified update location in the new Binlog

4. When the I/O thread of the slave server obtains the log content, log files, and location points sent to the I/O thread on the master server, the Binlog log contents are written sequentially to the relay log of the slave side itself
(that is, the trunk log) file (mysql-relay-bin.xxxxxx), and the new Binlog file name and location are recorded in the Master-info file so that the next time you read the new Binlog log on the master side, you can
Tell the master server to start requesting new Binlog log content from the specified file and location in the new Binlog log

5. The SQL thread of the slave server detects the added log contents of the I/O line assigns in the local relaylog in real time, then resolves the contents of the Relaylog file into SQL statements and executes the SQL statements on the slave server in the order in which they were parsed , and record the file name and location point of the current application's relay log in the Relay-log.info

Through the above process, you can ensure that the master side and the slave side execute the same SQL statements, when the replication state is normal, the master and slave end of the data is exactly the same

Third, the master-slave configuration process (as the instance is described on a single server)

# # # #在Mysql主库上执行的操作过程

vim/data/3306/my.cnf Modify the two lines under [mysqld] log-bin =/data/3306/mysql-binserver-  ID6

Egrep " Server-id|log-bin " /data/3306/my.cnf log-bin =/data/3306/mysql-binserver- ID 6

[Email protected] ~]#/data/3306/mysql restart  #重启Mysql主库

Mysql-u root-p123456-s/data/3306/Mysql.sockmysql> Show variables like'server_id';+---------------+-------+| variable_name | Value |+---------------+-------+| server_id |6|+---------------+-------+1RowinchSet (0.00sec) MySQL> Show variables like'Log_bin';+---------------+-------+| variable_name | Value |+---------------+-------+| Log_bin | On |+---------------+-------+1RowinchSet (0.00Sec

#根据主从复制的原理, from the library to synchronize with the main library, you must have an account that can connect to the main library, and this account is created on the main library, permissions are allowed to connect from the library and synchronize data

mysql> grant replication Slave on * * to ' rep ' @ ' 192.168.56.% ' identified by ' 123456 ';

Query OK, 0 rows Affected (0.00 sec)

#刷新权限
mysql> flush Privileges;
Query OK, 0 rows affected (0.01 sec)

#检查主库创建的rep账号
Mysql> select User,host from Mysql.user;
+------+-----------------------+
| user | Host |
+------+-----------------------+
| Root | 127.0.0.1 |
| Rep | 192.168.56.% |
| Root | :: 1

#检查权限
Mysql> Show grants for [email protected] ' 192.168.56.% ';
+-------------------------------------------------------------------------------------------------------------- -------------+
| Grants for [email protected]56.% |
+-------------------------------------------------------------------------------------------------------------- -------------+
| GRANT REPLICATION SLAVE on * * to ' rep ' @ ' 192.168.56.% ' identified by PASSWORD ' *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 ' |
+-------------------------------------------------------------------------------------------------------------- -------------+
1 row in Set (0.00 sec)

#对主数据库锁表只读
Mysql> Flush table with read lock;
Query OK, 0 rows Affected (0.00 sec)

#查看自动解锁时长
Mysql> Show variables like '%timeout% ';
+----------------------------+----------+
| variable_name | Value |
+----------------------------+----------+
| Connect_timeout | 10 |
| Delayed_insert_timeout | 300 |
| Innodb_lock_wait_timeout | 120 |
| Innodb_rollback_on_timeout | OFF |
| Interactive_timeout | 28800 |
| Lock_wait_timeout | 31536000 |
| Net_read_timeout | 30 |
| Net_write_timeout | 60 |
| Slave_net_timeout | 3600 |
| Wait_timeout | 28800 |
+----------------------------+----------+
Rows in Set (0.00 sec)

#锁表后查看主库状态, the command displays the information to be recorded, after the import from the library is fully prepared, the continuation and the main library copy is to start from this location

Mysql> Show master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 334 | | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

# #或者使用mysql-uroot-p123456-s/data/3306/mysql.sock-e "Show Master Status"

#锁表后, you must open a new SSH window, export all data of the database, if the amount of data is large (more than 50G), and allow downtime, can be parked directly packaged data files for migration, so faster

#新开一个窗口, back up the database to migrate the data to the library

[Email protected] ~]# mkdir/server/backup-p

[Email protected] ~]# mysqldump-uroot-p123456-s/data/3306/mysql.sock--events-a-B | gzip >/server/backup/mysql_bak.$ (Date +%f). sql.gz

#-a means backing up all libraries, and-B means adding use DB and drop, etc. (the original is overwritten directly by the Guide library)

#导出数据完毕后, unlock the main library, recover writable, because the main library also has to provide services to the outside, can not always lock the user to access

mysql> unlock tables;
Query OK, 0 rows Affected (0.00 sec)

##### #在Mysql从库上执行的操作过程

Set Server-id
and turn off the Binlog function (if you do not CASCADE replication from the library, and do not use as a backup, do not open binlog, but will increase the pressure from the library disk I/O, etc.)

vim/data/3307/my.cnfserver-ID7      # (parameter to be placed under [mysqld] module, and parameters cannot be duplicated)

3306 Egrep " Server-id|log-bin " /data/3307/my.cnf #log-bin =/data/3307/mysql-binserver-ID 7

/data/3307/mysql restart

Mysql-uroot-p123456-s/data/3307/Mysql.sockmysql> Show variables like"server_id";+---------------+-------+| variable_name | Value |+---------------+-------+| server_id |7|+---------------+-------+1RowinchSet (0.00sec) MySQL> Show variables like"Log_bin";+---------------+-------+| variable_name | Value |+---------------+-------+| Log_bin | OFF |+---------------+-------+1RowinchSet (0.00Sec

#把从主库导出的数据恢复到从库
cd/server/backup/
Gzip-d mysql_bak.2018-07-03.sql.gz (d) Delete source files after decompression

#还原数据库
[Email protected] backup]# Mysql-uroot-p123456-s/data/3307/mysql.sock < Mysql_bak.2018-07-03.sql

Tip: If you use the-a parameter for the backup, the password for the login 3307 instance will also match the password for the 3306 main Library when you restore the data to the 3307 instance, because the authorization table for the 3307 instance is also covered by MySQL

#登录3307从库, configuring Replication parameters
Mysql-uroot-p123456-s/data/3307/mysql.sock

mysql> change MASTER to master_host= ' 192.168.56.11 ', #主库的IP
-master_port=3306, #主库的端口, from the library port can be different from the main library
-Master_user= ' rep ', #主库上建立的用于复制的用户rep
-master_password= ' 123456 ', #rep用户的密码
-master_log_file= ' mysql-bin.000001 ', #show MASTER status when you see the binary log file name, note that there can be no spaces
master_log_pos=334; Check the binary log offset when #show master status, no more spaces
Query OK, 0 rows affected (0.01 sec)

Tip: strings are enclosed in single quotes, values are not quoted, and note that there can be no spaces behind them

#启动从库主从复制开关 and view the replication status
Mysql-uroot-p ' 123456 '-s/data/3307/mysql.sock-e "start slave;"

[Email protected] backup]# mysql-uroot-p ' 123456 '-s/data/3307/mysql.sock-e "show slave status\g;"

[Email protected] backup]# mysql-uroot-p ' 123456 '-s/data/3307/mysql.sock-e "show slave status\g;" | Egrep "Io_running| Sql_running|_behind_master "
Slave_io_running:yes
Slave_sql_running:yes
seconds_behind_master:0

Slave_io_running:yes
#这个是I/O thread state, the I/OS thread is responsible for reading the Binlog log from the library to the main library and writing the trunk log from the library with a status of yes indicating that I/O threads are working properly

Slave_sql_running:yes
#这个是SQL线程状态, the SQL thread is responsible for reading the data in the relay log (Relay-log) and converting it to a SQL statement applied to the slave database, with a status of yes indicating that I/O thread is working properly

seconds_behind_master:0
#这个是复制过程中从库比主库延迟的秒数, this parameter is important, but it is more accurate in the enterprise to determine the master-slave delay method is: Write the time stamp in the main library, then read the timestamp from the library, and the current database time to compare, so as to determine whether the delay

Write the data on the main library and observe the data status from the library:
Mysql-uroot-p123456-s/data/3306/mysql.sock-e "CREATE database template;"

[Email protected] backup]# mysql-uroot-p123456-s/data/3307/mysql.sock-e "show databases like ' template ';"
+---------------------+
| Database (template) |
+---------------------+
| Template |
+---------------------+

Mysql-uroot-p123456-s/data/3306/mysql.sock-e "drop database template;"
Mysql-uroot-p123456-s/data/3307/mysql.sock-e "Show databases like ' template ';"

#根据测试可以判断, the master-slave library is synchronous

MySQL master-slave replication principle and configuration process