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

1. Description:

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

System: CentOS 6. or 7.
MySQL Version: 5.7
MySQL Installation steps link
http://blog.51cto.com/10158955/1926574
Attention! Depending on your installation path and the data directory is different and modified! (including configuration files)
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/mysql
Datadir=/data/mysqldata
Socket=/tmp/mysql.sock
User=mysql
port=3306

Configuration of Master

Server-id=1 # Server ID (master and slave must be different)
Binlog-do-db=employees # Library to sync to slave
Binlog-ignore-db=mysql # Libraries not synchronized to slave (multiple write multiple lines)
Binlog-ignore-db=information_schema
Binlog-ignore-db=performance_schema
Binlog-ignore-db=sys
log-bin= Mysql-bin # Open the log (the host needs to open), this mysql-bin can also be customized;
Expire_logs_days=90 # automatically cleans up 90 days ago log file, can modify as needed
Restart the database for 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.service
Mysqld.service-mysql Server
Loaded:loaded (/usr/lib/systemd/system/mysqld.service; enabled)
Active:active (running) since four 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.cnf

5月 17 11:42:02 tcloud-118 systemd[1]: Started MySQL Server.

Test whether the Log_bin is open successfully

[Email protected] ~]# mysql-uroot-p
Mysql> 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 ';
mysql> flush Privileges;
Mysql> select host,user,authentication_string from Mysql.user;
+--------------+---------------+-------------------------------------------+
| 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>br/>[email protected]% account has been established;
< p="">

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; # Main Library lock table, default 28,800 seconds, that is, 8 hours automatic unlocking;
Mysql> Show master status;
1. Row
file:mysql-bin.000002
position:154
binlog_do_db:
Binlog_ignore_db:mysql
Executed_gtid_set:3f46a487-5984-11e8-8edd-00163e000d73:1-8
1 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 the same as the above,
Export the data on Master (192.168.1.101), and then import slave
Master:
[[email protected] ~]# Mysqldump-uroot-p Employees >/opt/employees.sql # If employees a library already exists for the main library
[[email protected] ~]# yum install openssh-clients-y # ( Note: Slave also needs to be installed)

Slave
[email protected] ~]# yum Install openssh-clients-y
[email protected] ~]# scp/opt/employees.sql [email protected]:/opt/
[email protected]' s password: #输入密码
Employees.bak 100%
5, configuration slave (192.168.1.102)
[email protected] ~]# vim/etc/my.cnf
[Mysqld]
Basedir=/usr/local/mysql # mysql Path
Datadir=/data/mysqldata # mysql Data Catalog
Socket=/tmp/mysql.sock
User=mysql
port=3306

Slave configuration

server_id=2 # Mysqlid Back 2 from the server need to set a different
Skip_slave_start=1 # The replication process does not start with the start of the database and needs to be started manually;
#加上以下参数可以避免更新不及时, the master-slave replication error caused by SLAVE reboot.
READ_ONLY = 1
Master_info_repository=table
Relay_log_info_repository=table
#relay_log_recovery = 1 # slave Write Forbidden
#super_read_only = 1 # slave Write Forbidden
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-p
Mysql> CREATE DATABASE employees; # Create a new library
[[email protected]~]# Mysql-uroot-p Employees </opt/employees.sql
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.service
Mysqld.service-mysql Server
Loaded:loaded (/usr/lib/systemd/system/mysqld.service; enabled)
Active:active (running) since four 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.cnf

5月 17 11:42:02 tcloud-118 systemd[1]: Started MySQL Server.

Log in to the slave database and make the following settings
[[email protected]~]# mysql-uroot-p
mysql> stop Slave;
Mysql> Change Master to
-master_host= ' 192.168.1.101 ', # Master IP
-Master_user= ' backup ', # Backup user name
-master_password= ' 123456 ', # password
--master_log_file= ' mysql-bin.000002 ', # above, and to be consistent with master's parameters
master_log_pos=154; # above, and be consistent with Master's parameters
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=154;
mysql> start slave;
Mysql> Show slave status \g # View slave from the state of the machine
1. Row
Slave_io_state:waiting for Master to send event
Master_host:192.168.1.101
Master_user:backup
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000002
read_master_log_pos:154
relay_log_file:relay-bin.000002
relay_log_pos:921
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:1756
relay_log_space:1122
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:0
Master_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:118
Master_uuid:3f46a487-5984-11e8-8edd-00163e000d73
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:3f46a487-5984-11e8-8edd-00163e000d73:6-8
Executed_gtid_set:3f46a487-5984-11e8-8edd-00163e000d73:6-8
auto_position:0
replicate_rewrite_db:
Channel_name:
Master_tls_version:
1 row in Set (0.00 sec)

[email protected] 15:11: [(None)]>
The following corresponding parameters are the same for the set success, 0 delay;
Slave_io_running:yes
Slave_sql_running:yes
seconds_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 () not NULL);
mysql> INSERT INTO test001 values (null, ' would ');
mysql> INSERT INTO test001 values (null, ' Jim ');
mysql> INSERT INTO test001 values (null, ' Tom ');

Slave
mysql> use employees;
Mysql> Show tables;
Have a picture
Mysql> select * from test001;
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
mysql> drop table test001;
The above experiments prove master-slave synchronization success!!!

MySQL 5.7 Master-slave replication (master-slave synchronization)