MySQL Master-Slave synchronization

MySQL Master-Slave introduction

MySQL master-slave is called replication, AB replication, A and b from the back, write data on a. B will also synchronize A's data, both to achieve real-time synchronization
MySQL is based on the Binlog log to synchronize, the Lord must turn on the Binlog for master-slave synchronization, the synchronization process is about three steps
(1) record the change of data operation to Binlog
(2) synchronization between master and slave to compare Binlog event record, a record the event to Binlog, from sync to local will also record a Relaylog file on this machine
(3) Perform synchronization from the event records in the Relaylog
The Lord has a log dump thread that is used to communicate with the I/O thread from Binlog
There will be two threads, where I/O threads are used to synchronize Binlog records and generate Relaylog records, another SQL thread is used to perform transactions in Relaylog, and SQL data is synchronized by size, change, and one by one in the Lord
Master-Slave synchronization with two scene modes
Master-Slave: The Master is responsible for all data queries and changes, from only responsible for data backup, only to the role of backup
Master Write-read from: The Master is responsible for the storage and modification of the data source, not for data query. From not only the role of real-time backup, but also provide access to the external query data, so as to reduce the primary database access pressure

主从数据库准备
Install MySQL database can refer to another article
For MySQL, compile and install the parameters reference:

[[email protected] mysql-5.7.22]# cmake . -DCMALE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/var/mysql/data -DSYSCONFDIR=/etc -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH-SYSTEMD=1 -DWITH_BOOST=/usr/local/boost-----------------------省略过程------------------------ CMAKE_C_LINK_FLAGS: -- CMAKE_CXX_LINK_FLAGS: -- CMAKE_C_FLAGS_RELWITHDEBINFO: -O3 -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF-- CMAKE_CXX_FLAGS_RELWITHDEBINFO: -O3 -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF-- Configuring done-- Generating done-- Build files have been written to: /usr/local/src/mysql-5.7.22[[email protected] mysql-5.7.22]# echo $?0

Results without error, continue make compile

[[email protected] mysql-5.7.22]# makeScanning dependencies of target abi_check[ 0%] Built target abi_checkScanning dependencies of target INFO_SRC[ 0%] Built target INFO_SRCScanning dependencies of target INFO_BIN[ 0%] Built target INFO_BINScanning dependencies of target zlib----------------省略过程------------------------[ 99%] Building C object libmysqld/examples/CMakeFiles/mysql_client_test_embedded.dir/__/__/testclients/mysql_client_test.c.o[ 99%] Linking CXX executable mysql_client_test_embedded[ 99%] Built target mysql_client_test_embeddedScanning dependencies of target my_safe_process[100%] Building CXX object mysql-test/lib/My/SafeProcess/CMakeFiles/my_safe_process.dir/safe_process.cc.o[100%] Linking CXX executable my_safe_process[100%] Built target my_safe_process[[email protected] mysql-5.7.22]# aecho $?0

Installation without error, complete final installation make install

-----------------------过程省略----------------------- Installing: /usr/local/mysql/support-files/mysqld_multi.server-- Installing: /usr/local/mysql/support-files/mysql-log-rotate-- Installing: /usr/local/mysql/support-files/magic-- Installing: /usr/local/mysql/share/aclocal/mysql.m4-- Installing: /usr/local/mysql/support-files/mysql.server[[email protected] mysql-5.7.22]# echo $?0

The basic installation is complete and the next step is to configure MySQL startup and configuration items
recursion specifies the permissions of the user and the group to which MySQL belongs

[[email protected] ]# chown -R mysql:mysql /usr/local/mysql/

Initialization of the database
The compiled database is initialized, where the MySQL initialization information contains a randomly generated password

  [[email protected] mysql-5.7.22]#/usr/local/mysql/bin/mysqld--initialize--user=mysql--basedir=/ Usr/local/mysql--datadir=/usr/local/mysql/data2018-07-28t09:08:42.088874z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. --explicit_defaults_for_timestamp server option (see documentation for more details). 2018-07-28t09:0 8:42.590615z 0 [Warning] innodb:new log files created, lsn=457902018-07-28t09:08:42.861362z 0 [Warning] innodb:creating  FOREIGN KEY constraint system tables.2018-07-28t09:08:42.955937z 0 [Warning] No existing UUID have been found, so we assume That's the first time that this server has been started. Generating a new uuid:d342a57c-9245-11e8-bf5e-080027a7f9c2.2018-07-28t09:08:42.965634z 0 [Warning] Gtid table is not rea Dy to be used. Table ' mysql.gtid_executed ' cannot be opened.2018-07-28t09:08:42.966858z 1 [Note] A temporary password are generated for [E Mail protected]: thuqg=%m!12j  

MY.CNF and/etc/init.d/mysqld files
Copy the service startup file and modify the MY.CNF configuration file. Specify MySQL installation path and path to store data and add system services

[[email protected] ]# cp /usr/local/src/mysql-5.7.22/support-files/mysql.server /etc/init.d/mysqld[[email protected] ]# vim /etc/init.d/mysqld ~basedir=/usr/local/mysql/datadir=/usr/local/mysql/data/~[[email protected] ]# chmod +x /etc/init.d/mysqld [[email protected] ]# ls -l /etc/init.d/mysqld -rwxr-xr-x 1 root root 10609 7月 28 17:21 /etc/init.d/mysqld[[email protected] ]# chkconfig --add mysqld[[email protected] ]# chkconfig --list 注意：该输出结果只显示 SysV 服务，并不包含原生 systemd 服务。SysV 配置数据可能被原生 systemd 配置覆盖。  ? ?  如果您想列出 systemd 服务,请执行 ‘systemctl list-unit-files‘。 ? ?  欲查看对特定 target 启用的服务请执行 ? ? ?‘systemctl list-dependencies [target]‘。mysqld 0:关 ?1:关 2:开 3:开 4:开 5:开 6:关netconsole 0:关 ?1:关 2:关 3:关 4:关 5:关 6:关network 0:关 1:关 2:开 3:开 4:开 5:开 6:关

Edit the my.cnf mysql configuration file, Log-error is the error message that records the start times wrong. In error PID errors, there is an attempt to Basedir = Configuration to comment out, and then MySQL can start normally, check the error log record, log record error unknown variable/usr/local/mysql/unknown variable path, Description MY.CNF The path specified is problematic, here I directly comment out Basedir this path, and then start the success

[[email protected] ]# less /etc/my.cnf[mysqld]basedir = /usr/local/mysql/ ?datadir = /usr/local/mysql/data ?port = 3306 ?character-set-server = utf8 ?explicit_defaults_for_timestamp = true ?# socket = /var/run/mysqld/mysqld.sock # Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Settings user and group are ignored when systemd is used.# If you need to run mysqld under a different user or group,# customize your systemd unit file for mariadb according to the# instructions in http://fedoraproject.org/wiki/Systemd[mysqld_safe]log-error=/var/log/mariadb/mariadb.logpid-file=/var/run/mariadb/mariadb.pidlog-error = /data/mysql/logs/error.log## include all files from the config directory#!includedir /etc/my.cnf.d

MySQL Start-up process
After adding complete following system startup, start the MySQL service:

[[email protected] ]# /etc/init.d/mysqld startStarting MySQL.2018-07-28T09:23:23.822700Z mysqld_safe error: log-error set to ‘/var/log/mariadb/mariadb.log‘, however file don‘t exists. Create writable for user ‘mysql‘. ERROR! The server quit without updating PID file (/usr/local/mysql/data/localhost.pid).

Create a MySQL log file according to the error message, and then try to start

[[email protected] ]# mkdir -p /var/log/mariadb/mariadb[[email protected] ]# touch /var/log/mariadb/mariadb.log[[email protected] ]# chown -R mysql:mysql /var/log/mariadb/mariadb

Try to start the MySQL service again and start the success

[[email protected] support-files]# /etc/init.d/mysqld startStarting MySQL. SUCCESS! [[email protected] support-files]# ps -aux |grep mysqlroot 21812 0.0 0.1 113312 1636 pts/0 S 17:28 0:00 /bin/sh /usr/local/mysql//bin/mysqld_safe --datadir=/usr/local/mysq/data --pid-file=/usr/local/mysql/data/localhost.pidmysql 22000 1.7 16.3 1112372 166604 pts/0 Sl 17:28 0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql//lib/plugin --user=mysql --log-error=/var/log/mariadb/mariadb.log --pid-file=/usr/local/mysql/data/localhost.pid --port=3306root 22030 0.0 0.0 112724 976 pts/0 R+ 17:28 0:00 grep --color=auto mysql

Write the MySQL command to the system file so that you can use the MySQL command directly on the command line.

[[email protected] mysql]# echo "export PATH=$PATH:/usr/local/mysql/bin/" >>/etc/profile[[email protected] mysql]# source /etc/profile[[email protected] mysql]# echo "/usr/local/mysql/lib/" >>/etc/ld.so.conf[[email protected] mysql]# ldconfig

Establish master and slave master and slave communication steps one master configuration

Clears the iptables firewall rule because iptables is not cleared. Causes incoming data to be forwarded to another IP address.

  [[email protected] ~]# iptables-nvlchain INPUT (policy ACCEPT 0 packets, 0 bytes) pkts bytes Target Prot  Opt in Out source destination????  4811 348K Accept All--* * 0.0.0.0/0 0.0.0.0/0 State related,established?? 1 * ICMP--* * 0.0.0.0/0 0.0.0.0/0?  ? ? ? ?  ?? 4 324 ACCEPT All-lo * 0.0.0.0/0 0.0.0.0/0????? ?? 2 104 ACCEPT TCP--* * 0.0.0.0/0 0.0.0.0/0 State NEW TCP dpt:2213467 1028K REJECT All--* * 0.0.0.0/0 0.0.0.0/0 REJEC T-with Icmp-host-prohibitedchain FORWARD (policy ACCEPT 0 packets, 0 bytes) pkts bytes Target prot opt in Out source Desti  Nation???? ?? 0 0 REJECT All--* * 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibitedchain OUTPUT (Policy ACCEPT 3311 packets, 147 6K bytes) pkts bytes Target prot opt in out source destination???? [[email protected] ~]# iptables-f  

After installing MySQL, modify the my.cnf configuration file, add Binlog log records, and specify the ServerID of the master/slave runtime.
Modify MY.CNF, add the Binlog log generation configuration and specify the running server level ID for MySQL
If you are synchronizing or not synchronizing a library, you will need to specify a library that is not synchronized or synchronized in MY.CNF
#binlog-do-db=db1???? #只针对指定库同步
#binlog-ignore-db=mysql?? ? Out of sync for some libraries

[[email protected] /]# vim /etc/my.cnf[mysqld]basedir = /usr/local/mysql/datadir = /usr/local/mysql/dataport = 3306character-set-server = utf8explicit_defaults_for_timestamp = trueserver-id = 10log-bin = zidingyi

Create an authorized user in the database to use access from the database for reading data from master and synchronizing it

mysql> grant replication slave on *.* to ‘repl‘@192.168.1.220 identified by ‘xiangchen‘ ;Query OK, 0 rows affected, 1 warning (0.00 sec)

View Binlog stored values in the primary database, which is used to record the size of the data store, and change if the data table reads and writes. Before master and slave synchronization needs to stop the database read and write in the main Library lock table. File is the name of the specified generated Binlog file, position is the change value of the data store, which is synchronized by this value, which also expresses the size of the Binlog file.

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

Temporarily disable read write for the main Library lock table

mysql> flush tables with read lock;Query OK, 0 rows affected (0.01 sec)

Step two slave from the configuration

The slave role also needs to clear the iptables rules, so as not to communicate between master and slave
Slave from the configuration my.cnf, modify Server-id, this ID can not be smaller than the configuration of the Lord, the smaller the value of the higher priority. The my.cnf from the server does not need to be configured Log-bin, because it is the role of slave, so only the data from the master can be synchronized

[[email protected] ~]# vim /etc/my.cnf[mysqld]#bashdir = /usr/local/mysql/datadir = /data/mysqlport = 3306character-set-server = utf8explicit_defaults_for_timestamp = trueserver-id = 20

After restarting, log in from the database, pause the slave role, and write some information in the database that communicates with master (the value of the Binlog record).
Master_log_file is the name of the designated Lord to generate the Binlog file, slave will find the corresponding Binlog file by this name.
Master_log_pos is the change value of the specified master data store, which is synchronized by this value, which also expresses the size of the Binlog file.

[[email protected] ~]# /etc/init.d/mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS! mysql> stop slave ;Query OK, 0 rows affected (0.01 sec)mysql> change master to master_host=‘192.168.1.234‘, master_port=3306, master_user=‘slave‘, master_password=‘xiangchen‘, master_log_file=‘zidingyi.000001‘, master_log_pos=1108;Query OK, 0 rows affected, 2 warnings (0.02 sec)

Step three remove the master table lock and start the slave role for data synchronization

After setting up the authorization and access account settings for master and slave, unlock Master's table lock and start the slave role, and copy the library from master to slave to ensure consistency of data on both sides of the startup synchronization
Copy master data to Slave, ignoring clear-text password warnings before releasing Master's table lock

[[email protected] ~]# mysqldump -uroot [email protected] mysql2 > my2.dbmysqldump: [Warning] Using a password on the command line interface can be insecure.[[email protected] ~]# mysqldump -uroot [email protected] zrlog > zrlog.dbmysqldump: [Warning] Using a password on the command line interface can be insecure.

The suffix backup file ending in db is transferred to slave by means of SCP, Rzsz, or FTP, and slave in the library needs to create an empty library that needs to be recovered for data import

mysql> create database mysql2;Query OK, 0 rows affected (0.02 sec)mysql> create database zrlog;Query OK, 0 rows affected (0.02 sec)mysql> exitBye[[email protected] ~]#  

Master can remove the data table lock, so that the primary database can read and write access

mysql> unlock tables;mysql> flush privileges;

Enable the slave role from the database and synchronize data with the master database (two database data must be consistent prior to data synchronization, otherwise synchronization can result in a different data situation)

mysql> start slave;Query OK, 0 rows affected (0.00 sec)

View the master-slave synchronization information, here the main focus on Slave_io_running:yes and? Slave_sql_running:yes These two values, a state that indicates whether the communication with Master is normal, Slave_io is a thread that communicates with Master's IO process, and yes indicates that it is running normally. The other is whether the SQL execution is in the listening state

Mysql> Show slave status\g;*************************** 1. Row ***************************???????  Slave_io_state:waiting for Master to send event????????  master_host:192.168.1.234????????  Master_user:slave????????  master_port:3306???????  Connect_retry:60??????  master_log_file:zidingyi.000001???? read_master_log_pos:1108???????  Relay_log_file:huaching-2-relay-bin.000002???????  relay_log_pos:319??? relay_master_log_file:zidingyi.000001??????  Slave_io_running:yes?????  Slave_sql_running:yes??????  replicate_do_db:???? replicate_ignore_db:????? Replicate_do_table:???  Replicate_ignore_table:?? Replicate_wild_do_table:replicate_wild_ignore_table:????????? Last_errno:0????????? Last_error:????????  skip_counter:0????  exec_master_log_pos:1108??????  relay_log_space:531?????? Until_condition:none??????? Until_log_File:??????? until_log_pos:0????? Master_ssl_allowed:no????? Master_ssl_ca_file:?????  Master_ssl_ca_path:??????  Master_ssl_cert:????? Master_ssl_cipher:???????  Master_ssl_key:???  Seconds_behind_master:0master_ssl_verify_server_cert:no???????  Last_io_errno:0??????? Last_io_error:??????? Last_sql_errno:0??????? Last_sql_error:replicate_ignore_server_ids:??????  Master_server_id:10???????? Master_uuid:9174f3d7-9c9a-11e8-88a9-08002733edda??????  Master_info_file:/data/mysql/master.info??????????  Sql_delay:0????  Sql_remaining_delay:null?? Slave_sql_running_state:slave have read all relay log; Waiting for more updates?????  master_retry_count:86400????????  Master_bind:?? Last_io_error_timestamp:?? Last_sql_error_timestamp:??????? MASTER_SSL_CRL:????? Master_ssl_crlpath:?????  Retrieved_gtid_set:????? Executed_gtiD_set:??????? Auto_position:0???? replicate_rewrite_db:???????? Channel_name:?????  Master_tls_version:

Test the synchronization configuration used in My.cnf master and slave for master-slave synchronization

MY.CNF in master can specify that synchronization is excluded or only some libraries are synchronized
#binlog-do-db=db1???? #只针对指定库同步
#binlog-ignore-db=mysql?? ? Out of sync for some libraries

Slave role configuration fully records execution for some libraries and does not ignore a SQL execution statement in Binlog
Replicate_wild_do_table=? Wildcard characters , wildcard libraries, such as user , are supported.
Replicate_wild_ignore_table=? Indicates that the contents of this configuration will be ignored for execution.

The following configuration parameters are not recommended because the libraries that match synchronization may also be ignored and not executed
Matching libraries
replicate_do_db=
replicate_ignore_db=
Match table
replicate_do_table=
replicate_ignore_table=

In the Lord Query the operation of the library, query the master and slave data is consistent, the same library, the same table

mysql> use mysql2Database changedmysql> select count(*) from user;+----------+| count(*) |+----------+| 6 ? ? ?  |+----------+1 row in set (0.00 sec)

Slave results from the previous query

mysql> use mysql2Database changedmysql> select count(*) from user;+----------+| count(*) |+----------+| 6 ? ? ?  |+----------+1 row in set (0.00 sec)

The Lord empties the contents of the datasheet and looks at the number of rows in the data table

mysql> truncate table user;Query OK, 0 rows affected (0.00 sec)mysql> select count(*) from user;+----------+| count(*) |+----------+| 0 ? ? ?  |+----------+1 row in set (0.00 sec)

Slave only executes the query data table rows from above, and the result is that the same data will be executed synchronously.

mysql> select count(*) from user;+----------+| count(*) |+----------+| 0 ? ? ?  |+----------+1 row in set (0.00 sec)

If the deletion of the table from the deletion of the library operation, the master Lord if mistakenly executed slave on the deleted content, but also cause master-slave synchronization problems, can only be re-slave on the value of the specified position to synchronize. This mis-operation can disrupt the synchronization between master and slave.

An error message that occurs when you synchronize again after you delete some content

Last_ SQL_ Error: Error ‘Can‘t drop database ‘user‘; database doesn‘t exist‘ on query. Default database: ‘user‘.  Query: ‘drop database user‘

MySQL Master-Slave synchronization