MYSQL The principle of master-slave ：

1. First, you need two servers 【 Can work with colleagues / Build with friends 】

find mysql Edit and modify the configuration file of ：( /etc/my.cnf)

Master database modification ：

In the main warehouse mysqld This paragraph is added below

# The primary server is unique ID
server-id=1
# Enable binary logging
log-bin=mysql-bin
# Set up logbin Format
binlog_format=STATEMENT
# Set up the database not to be copied , You can set multiple
binlog-ignore-db=mysql 
# Set up the database to be synchronized （ Database not created ）
binlog-do-db=table1 
#binlog-do-db After setting , You can leave it blank binlog-ignore-db

Exit and reload the configuration ：

# heavy load mysql To configure
service mysqld restart

Get into mysql terminal , In the main warehouse mysql Register the account and password of the slave database on ：

# Create a remote connection account password
GRANT REPLICATION SLAVE ON *.* TO 'slave_username'@'%' IDENTIFIED BY 'slave_password';
# Refresh mysql System permission related table
flush privileges;
# Check whether the establishment is complete
select user from mysql.user where user='slave_password';

【slave_username Slave account ,slave_password Slave password 】

After configuration, you can check the configuration status of the main database

show master status;

• File：binlog The name of the Journal
• Position ： Access point  
• Binlog_Do_DB ： The database that needs to be replicated
• Binlog_Ignore_DB ： Databases that don't need to be replicated
• Executed_Gtid_Set： write in binlog Generated by logged transactions Gtid Set

Switch from server to server mysql To configure

stay mysqld Add this paragraph below ,

# The primary server is unique ID
server-id=2
# Enable relay logging
relay-log=mysql-relay
# Enable binary logging
log-bin=mysql-bin

Overload configuration ：

show master status;

If master and slave have been configured , You need to restart the server before configuring

# Stop service from
stop slave;
# Reset master
reset master;

  Then make the master-slave connection

CHANGE MASTER TO MASTER_HOST=' master server IP Address ', // The server ip Address
MASTER_USER=' Remote connection account ',                                           //slave_username
MASTER_PASSWORD=' Remote connection password ',                                //slave_password
MASTER_LOG_FILE=' Main library binlog Log name ',                          //FIle
MASTER_LOG_POS= Main library access point ;                                        //postion

  Start the synchronization function after the master-slave connection is successful

# Turn on the synchronization function

start slave;

# View the slave library status

show slave status\G;

Be careful ：Slave_IO_Running and Slave_SQL_Running The states of are Yes when , The configuration of slave database is successful .

Master slave test

（1）、 Create... On the master library jgyw Pattern , namely ：

create schema jgyw;

（2）、 On the main library jgyw Create... In the pattern comm_config surface , namely ：

use jgyw;
CREATE TABLE comm_config (configId varchar(200) NOT NULL ,configValue varchar(1024) DEFAULT NULL ,description varchar(2000) DEFAULT NULL ,PRIMARY KEY (configId)) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

（3）、 Go up the main warehouse comm_config Insert a record in the table , namely ：

insert into comm_config(configId, configValue, description) values('name', ' Architecture and me ', ' Test it ');

（4）、 View the mode on the slave Library , namely ：

show schemas;

The result is ：

+--------------------+
| Database           |
+--------------------+
| information_schema |
| jgyw               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

explain jgyw Mode synchronized to slave Library

（5）、 Check it out from the library jgyw Tables and data in mode , namely ：

use jgyw;
show tables;

The result is ：

+----------------+
| Tables_in_jgyw |
+----------------+
| comm_config    |
+----------------+
1 row in set (0.00 sec)

The instruction table is synchronized , Look at the data , namely ：

select * from comm_config;

The result is ：

+----------+--------------+--------------+
| configId | configValue  | description  |
+----------+--------------+--------------+
| name     |  Architecture and me      |  Test it      |
+----------+--------------+--------------+
1 row in set (0.00 sec)

It means that the data is synchronized .