Master-Slave deployment of mysql5.7.13 based on centos6 (2)
Master-Slave deployment of mysql5.7.13 based on centos6 (2)
First, deploy the master mysql
Description: mysql1 is the host and mysql2 is the standby.
Create a library
[root@mysql1] # mysql-uroot-p123456
Mysql > create database shiyan
Mysql > exit
Bye
Copy the mysql library to the shiyan library
[root@mysql1] # mysqldump-uroot-p123456 mysql > 234.sql
[root@mysql1] # mysql-uroot-p123456 shiyan
< 234.sql 查看是否拷贝成功 [root@mysql1 ~]# mysql -uroot -p123456 mysql>Use shiyan
Mysql > show tables
Modify the main my.cnf configuration file
[root@mysql1 ~] # vim / etc/my.cnf
The modifications are as follows:
21 server_id = 1 # set 1 as master and 2 as slave. Note that the id of master and slave is not the same.
15 log_bin = mysql-bin # Open the comment. The name can be changed, but it must be the same as the slave library configuration.
16 binlog-ignore-db = mysql # does not allow mysql library synchronization
17 binlog-do-db = shiyan # allow shiyan library synchronization
Save and exit!
Description:
Binlog-do-db=db1,db2 / / similar to whitelist, add libraries that need synchronization
Binlog-ignore-db=db3 / / similar to blacklist, listing libraries that do not need synchronization
Restart the mysqld service
[root@mysql1 ~] # / etc/init.d/mysqld restart
[root@mysql1 ~] # ll / usr/local/mysql/data
You can see two files that start with mysql-bin, and this is its log-bin!
To do authorization-related, master and slave must have a user who specializes in transmitting mysql-bin (log-bin).
[root@mysql1] # mysql-uroot-p123456
Mysql > grant replication slave on *. * to 'repl'@'192.168.100.128' identified by' 123456'
Description:
The above command means: authorize the slave (slave) IP:192.168.100.128 to remotely log in to the host copy with the account repl and password 123456!
Authorization (grant) format: grant permissions (select query, insert insert, update update, delete delete) on database objects (tables, chains) to users
Refresh permissions
[root@mysql1] # mysql-u root-p123456
Mysql > flush privileges
The reading lock of the watch
Mysql > flush tables with read lock
Note: in order to prevent data update during the operation, resulting in data inconsistency, so you need to refresh the data and lock the database: flush tables with read lock.
Read a piece of data
Mysql > show master status
Login database verification (on slave computer)
[root@mysql2] # mysql-urepl-h292.168.100.129-p123456
Second, deploy from mysql2
Edit the profile from mysql2
[root@mysql2 ~] # vim / etc/my.cnf
The modifications are as follows:
21 server_id = 2 # set 1 as master and 2 as slave. Note that the id of master and slave is not the same.
15 log_bin = mysql-bin # Open the comment. The name can be changed, but it must be the same as the slave library configuration.
16 binlog-ignore-db = mysql # does not allow mysql library synchronization
17 binlog-do-db = shiyan # allow shiyan library synchronization
Save and exit!
Description:
Binlog-do-db=db1,db2 / / similar to whitelist, add libraries that need synchronization
Binlog-ignore-db=db3 / / similar to blacklist, listing libraries that do not need synchronization
Restart the mysql service
[root@mysql2 ~] # / etc/init.d/mysqld restart
[root@mysql2 ~] # ll / usr/local/mysql/data
Create a shiyan library in mysql2
[root@mysql2] # mysql-uroot-p123456
Mysql > create database shiyan
Query OK, 1 row affected (0.01sec)
Import the 234.sql file backed up by the master library into the shiyan library in the slave library
[root@mysql1 ~] # scp 234.sql root@192.168.100.128:/root/
Error report:
Solution:
[root@mysql1 ~] # yum install openssh-clients-y
Description: the openssh-clients package should be installed on the master and slave servers!
[root@mysql2] # mysql-uroot-p123456 shiyan
< 234.sql 查看下shiyan库 mysql>Use shiyan
Mysql > show databases shiyan
Stop (from) mysql2
[root@mysql2] # mysql-uroot-p123456
Mysql > stop slave
Query OK, 0 rows affected, 1 warning (0.00 sec)
Specify that the master library information (a very important step) operates on the slave library!
Mysql > change master to master_host='192.168.100.129',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=603
Description:
Master_host here is the ip address of the main library, pay special attention to syntax, numbers do not need to add single quotation marks!
Master_log_file: the file part that is viewed through show master status; in the main library
Master_log_pos: the position section that is viewed in the main library.
Start (from) mysql2 and operate on mysql2!
Mysql > start slave
Check whether the synchronization is successful
Description: operate on mysql2
Mysql > show slave status\ G
Note: Slave_IO_Running and Slave_SQL_Running must both be yes!
At this point, the two database servers are in a state of synchronization, and the next step is to carry out synchronization testing!
Extend:
If the following error occurs when mysql2 does a synchronization check:
You can see the Slave_IO_Running:NO
Solution:
Check the mysql error log first
[root@mysql2 ~] # cd / usr/local/mysql/data
[root@mysql2 data] # cat mysqld.err
Cause analysis:
The replication of mysql5.7 introduces the concept of uuid. The server_uuid in each replication structure has to be guaranteed to be different, but the server_uuid is the same after viewing the direct copy data folder, show variables like'% server_uuid%'
Solution:
Find the auto.cnf file under the data folder, modify the UUID value in it, make sure that the uuid of each db is different, and restart db!
