Shulou(Shulou.com)06/01 Report--

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

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

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.