Linux under MySQL 5.7 Master-slave replication (master-Slave synchronization)

1. Description:

The first is to prepare two servers, one master server (master), the other from the server (Slave), and then to ensure that the master and Slave version of the same and master can not be higher than Slave version, it is generally prudent to make the same version, Because the Binlog (binary log) format between different versions of MySQL may not be the same, it will eventually cause synchronization to occur unexpectedly.

IP	Host name	role
192.168.1.101	MySQL-001	Master
192.168.1.102	MySQL-002	Slave

Version:

System: CentOS 6. or 7.
MySQL Version: 5.7

2. The master configuration file is set as follows

General MySQL configuration file in/etc/my.cnf

(It may also be in these directories if not found:/ETC/MYSQL/MY.CNF,/USR/LOCAL/MYSQL/ETC/MY.CNF,~/.MY.CNF)

[[email protected] ~]# vim /etc/my.cnf[mysqld]basedir=/usr/local/mysqldatadir=/data/mysqldatasocket=/tmp/mysql.sockuser=mysqlport=3306# master的配置server-id=1             # 服务器id (主从必须不一样)binlog-do-db=employees  # 要给从机同步的库binlog-ignore-db=mysql  # 不给从机同步的库(多个写多行)binlog-ignore-db=information_schemabinlog-ignore-db=performance_schemabinlog-ignore-db=syslog-bin=mysql-bin       # 打开日志(主机需要打开)，这个mysql-bin也可以自定义；expire_logs_days=90     # 自动清理 90 天前的log文件,可根据需要修改

Restarting the database causes the configuration to take effect:
CentOS 6.*:

[[email protected] ~]# service mysqld restart [[email protected] ~]# service mysqld status SUCCESS! MySQL running (15607)

CentOS 7.*:

[[email protected] ~]# systemctl restart  mysqld.service[[email protected] ~]# systemctl status mysqld.servicemysqld.service - MySQL Server   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled)   Active: active (running) since 四 2018-05-17 11:42:02 CST; 2h 5min ago     Docs: man:mysqld(8)        http://dev.mysql.com/doc/refman/en/using-systemd.html Main PID: 29959 (mysqld)   CGroup: /system.slice/mysqld.service       └─29959 /opt/mysql/bin/mysqld --defaults-file=/etc/my.cnf5月 17 11:42:02 tcloud-118 systemd[1]: Started MySQL Server.

Test whether the Log_bin is open successfully

[[email protected] ~]# mysql -uroot -pmysql> show variables like ‘%log_bin%‘;+---------------------------------+---------------------------------+| Variable_name                   | Value                           |+---------------------------------+---------------------------------+| log_bin                         | ON                              || log_bin_basename                | /opt/mysql/logs/mysql-bin       || log_bin_index                   | /opt/mysql/logs/mysql-bin.index || log_bin_trust_function_creators | ON                              || log_bin_use_v1_row_events       | OFF                             || sql_log_bin                     | ON                              |+---------------------------------+---------------------------------+6 rows in set (0.00 sec)mysql>

You can see that Log_bin is on;

3, Master in the database to establish master-slave synchronization account Backup:

Backup is the user name, 192.168.1.% means only allow 192.168.1 network segment of the client connection, 123456 is the password;

  mysql> Grant replication Slave on * * to ' backup ' @ ' 192.168.1.% ' identified by ' 123456 ';                                                                  # Create a Sync account mysql> flush privileges;                            # Refresh Permissions mysql> select Host,user,authentication_string from Mysql.user; # Check if create +--------------+---------------+-------------------------------------------+| Host | User | authentication_string |+--------------+---------------+-------------------------------------------+| localhost | Root | *6c362347ebeaa7df44f6d34884615a35095e80eb | | localhost | mysql.session | *thisisnotavalidpasswordthatcanbeusedhere | | localhost | Mysql.sys | *thisisnotavalidpasswordthatcanbeusedhere | | 192.168.1.% | Backup | *9bb58b7f11a03b83c396ff506f3df45727e79614 |+--------------+---------------+------------------------------------- ------+5 rows in Set (0.00 sec) Mysql>  

[Email protected]% account has been established;

4, Master Lock table, to slave copy data

Restart the MySQL service and set the read lock, read lock meaning can only read, cannot update, in order to obtain a consistent snapshot;

mysql> flush table with read lock;     # 主库锁表；默认28800秒，即8小时自动解锁；mysql> show master status \G*************************** 1. row ***************************             File: mysql-bin.000002         Position: 1621     Binlog_Do_DB: Binlog_Ignore_DB: mysqlExecuted_Gtid_Set: 1d3d078c-59a7-11e8-9a08-00163e000b3f:1-71 row in set (0.00 sec)mysql>

To view the current binary log name and offset values on the primary server here the file and position are consistent with the above;
Export the data on Master (192.168.1.101) and import it into the slave

Master
Format: Mysqldump-uuser-ppassword DATABASE TABLE > Name.sql

[[email protected] ~]# mysqldump -uroot -p employees > /opt/employees.sql    # 假如employees为主库已经存在的库[[email protected] ~]# yum install openssh-clients -y                        # 可选[[email protected] ~]# scp /opt/employees.sql [email protected]:/opt/

Slave

[[email protected] ~]# yum install openssh-clients -y                        # 可选

5, configuration slave (192.168.1.102)

[[email protected] ~]# vim /etc/my.cnf[mysqld]basedir=/usr/local/mysql    # mysql程序路径datadir=/data/mysqldata     # mysql数据目录socket=/tmp/mysql.sockuser=mysqlport=3306# slave配置server-id=2                 # MySQLid 后面2个从服务器需设置不同skip_slave_start=1          # 复制进程不会随着数据库的启动而启动,重启数据库后需手动启动；#加上以下参数可以避免更新不及时，SLAVE 重启后导致的主从复制出错。read_only = 1               # 从库普通账户只读；master_info_repository=TABLErelay_log_info_repository=TABLE#relay_log_recovery=1       # 从机禁止写#super_read_only=1          # 从机禁止写

Restart database

CentOS 6.*:

[[email protected] ~]# service mysqld restart [[email protected] ~]# service mysqld status SUCCESS! MySQL running (15604)

CentOS 7.*:

[[email protected] ~]# systemctl restart mysqld.service[[email protected] ~]# systemctl status mysqld.servicemysqld.service - MySQL Server   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled)   Active: active (running) since 四 2018-05-17 11:42:02 CST; 2h 5min ago     Docs: man:mysqld(8)        http://dev.mysql.com/doc/refman/en/using-systemd.html Main PID: 29959 (mysqld)   CGroup: /system.slice/mysqld.service       └─29959 /opt/mysql/bin/mysqld --defaults-file=/etc/my.cnf5月 17 11:42:02 tcloud-118 systemd[1]: Started MySQL Server.

It is then imported into the MySQL database, the employees database on the slave is not present, then it is created and then imported

[[email protected] ~]# mysql -uroot -pmysql> create database employees;        # 新建这个库[[email protected] ~]# mysql -uroot -p employees < /opt/employees.sql

Log in to the slave database and make the following settings

[[email protected] ~]# mysql -uroot -pmysql> stop slave;                  # 关闭slave同步，第一次可略过；mysql> change master to-> master_host=‘192.168.1.101‘,     # master的ip-> master_user=‘backup‘,            # 备份用户名-> master_password=‘123456‘,        # 密码-> master_log_file=‘mysql-bin.000002‘,    # 上面，且要与master的参数一致-> master_log_pos=1621;              # 上面，且要与master的参数一致

Write as:

mysql> Change Master to master_host= ' 192.168.1.101 ', master_user= ' backup ', master_password= ' 123456 ', Master_log_                 File= ' mysql-bin.000002 ', master_log_pos=1621;mysql> start slave; # start Sync mysql> show slave status \g # View slave from the State *************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos              t:192.168.1.101 master_user:backup master_port:3306 connect_retry:60 master_log_file:mysql-bin.000002 read_master_log_pos:1621 Relay_log_file:relay-bin.            000002 relay_log_pos:320 relay_master_log_file:mysql-bin.000002 Slave_io_running:yes Slave_sql_running:yes Replicate_Do_DB:Replicate_Ignore_DB:mysql 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:1 621 relay_log_space:521 Until_condition:none Until_log_file:unti l_log_pos:0 Master_ssl_allowed:no Master_SSL_CA_File:Master_SSL_CA_Path:Mas Ter_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:101 master_uuid:1d3d078c-          59a7-11e8-9a08-00163e000b3f Master_Info_File:mysql.slave_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:5037e479-59a7-11e8-a35b-00163e00 0402:1-3 auto_position:0 Replicate_Rewrite_DB:Channel_Name:Master_TLS_ Version:1 row in Set (0.00 sec) mysql>

The following corresponding parameters are the same for the set success, 0 delay;

Slave_IO_Running: YesSlave_SQL_Running: YesSeconds_Behind_Master: 0

6. Shut down the read lock on the main database and test

mysql> unlock tables;

Create a new table in Marster and see if there is data in the slave

Master

mysql> use employees;mysql> create table test001(id int auto_increment primary key,name varchar(20) not null);mysql> insert into test001 values(null,‘will‘);mysql> insert into test001 values(null,‘jim‘);mysql> insert into test001 values(null,‘tom‘);mysql> select * from employees.test001;+----+------+| id | name |+----+------+|  1 | will ||  2 | jim  ||  3 | tom  |+----+------+3 rows in set (0.01 sec)

Slave

mysql> use employees;mysql> select * from employees.test001;+----+------+| id | name |+----+------+|  1 | will ||  2 | jim  ||  3 | tom  |+----+------+

Test 2: Restart shutdown from the database, the master deletes the test001 table, and then the master and slave databases are restarted to see if it is normal

Master

mysql> drop table employees.test001;

Slave

mysql> use employees;mysql> show tables;

The above experiments prove master-slave synchronization success!!!

Linux under MySQL 5.7 Master-slave replication (master-Slave synchronization)