CENTOS7 configuring MySQL master-slave replication

Main 192.168.2.10

From 192.168.2.11

Configure primary MySQL First

Vi/etc/my.cnf

Locate [mysqld] and then add the following configuration:
Uniquely identifies this MySQL server with a default value of 1, typically using the end value of the owning IP
Server-id=1

Binary log file name, MySQL master server must enable this configuration
Log-bin=master-bin-log

The name of the database that participates in master-slave replication in the MySQL master server, and if there are multiple databases, this parameter can have multiple, one per line, and a division corresponding to a different database
Binlog-do-db=db_master_backup
The index name of the binary log file in the MySQL master server
Og-bin-index=master-bin-log.index

Create a database Db_master_backup

Create Database Db_master_backup;

Create the user backup and set the password backup_123456:
Create user ' backup ' @ '% ' identified by ' backup_123456 ';

Assign all permissions to the Db_master_backup database for user backup:
Grant all on db_master_backup.* to ' backup ' @ '% ';

To assign local access permissions to user backup:
Grant all privileges the db_master_backup.* to ' backup ' @ ' localhost ' identified by ' backup_123456 ';

Authorization from the server can access the primary server through the backup user:
Grant Replication Slave on * * to ' backup ' @ ' percent ' identified by ' backup_123456 ' with GRANT option;

Configuration from the database

Vi/etc/my.cnf

# uniquely identifies this MySQL server with a default value of 1, typically using the end value of the owning IP
server-id=2
# MySQL Log index file name from the server

Relay-log-index=slave-relay-log.index
# MySQL log file name from the server

Relay-log=slave-relay-log

Create a database Db_master_backup

Create Database Db_master_backup;

Restart two MySQL services

To view the status of the primary server:
Show master status;

Perform the following configuration from the service:

Change Master to master_host= ' 192.168.2.10 ',//home Server IP
master_port=3306,
Master_user= ' User_master ',
Master_password= ' password_master_123456 ',
Master_log_file= ' master-bin-log.000001 ',///Primary server Master_log_file value
master_log_pos=364; The value of the primary server Master_log_pos

mysql> stop Slave;

mysql> start slave;

Perform show slave status \g

If there is an Error reading packet from the server for Channel ": Could not the Find first log file name in binary log index file Wrong

Steps to resolve:

Restart Master Library: Service mysqld restart

Mysql> Show master status;

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/95/59/wKiom1kUMOeS6n-2AAAkpiZ1zJE369.png "title=" Qq20170511173851.png "alt=" Wkiom1kumoes6n-2aaakpiz1zje369.png "/>

In the execution from the library

mysql> stop Slave;

mysql> Change Master to master_log_file= ' master-bin-log.000002 ', master_log_pos=154;

mysql> slave start;

Mysql> Show Slave Status\g

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/95/59/wKioL1kUL-fh01KVAACo0TjE9wc504.png "title=" Qq20170511173329.png "alt=" Wkiol1kul-fh01kvaaco0tje9wc504.png "/>

Add data for user name T_user in the Db_master_backup Library of the MySQL master server:
mysql> INSERT into T_user (ID, name) VALUES (1, ' idea1 ');
Mysql> select * from T_user;

Create a script on the primary server to insert data into T_user

#!/bin/bash
I=1;
max_insert_row_count=$1;
While [$i-LT 100]
Do
mysql-uroot-p123456 db_master_backup-e "INSERT into T_user () VALUES (' $i ', $i);"
echo "INSERT HELLO $i @@ ZZFCTHOTFIXZ"
i=$ (($i + 1))
Sleep 0.05
Done
Exit 0

To see if data is synchronized from the database

Mysql> select * from T_user;

CENTOS7 configuring MySQL master-slave replication