MySQL master-slave synchronization and read/write separation

MySQL Master-Slave synchronization
MySQL AB Replication
1. Geo-synchronization of the specified library.
2.MySQL---from the implementation of the replication architecture.
Read-only control of the 3.MySQL server.

Subordinate:
When you copy one-way, it is recommended that you set it from library to read-only.

The principle of Master-slave replication:
Master, record data change operations.
-Start Binlog Log
-Set Binlog log format
-Set server_id

Slave, run 2 threads.
-SLAVE_IO: Copy the SQL from the Master host Binlog log file into the native Relay-log file.
-slave_sql: Executes the SQL statement in the native Relay-log file to reproduce the master's data operations.

Building Master-Slave synchronization
1. Make sure the data is the same
-You must have data on the main library from the library.

2. Configure the primary server
-Enable Binlog log and format, set server_id, authorize user.

3. Configure the slave server
-Set SERVER_ID to be configured from the database server.

4. Test the Sync effect
-The client connects to the main library and writes the data, which can also be seen when accessing from the library.

Make sure the data is the same
Master server:
-All libraries that you want to synchronize should be included
-For libraries with MyISAM, you can back up offline
Mysql>reset master; Reset Binlog Log
...
#mysqldump-u root-p 123456-b mysql Test >/root/mytest.sql

Slave server:
-Offline import with backup provided by master
-Empty libraries with the same name (if present)
Mysql>drop database test; Clean up the target library first
#scp master:/root/mytest.sql.///Direct SCP remote copy
#mysql-u root-p 123456 </root/mytest.sql//ensure the same data as Master

Configuring the primary server
1. Enable Binlog and allow synchronization:
Vim/etc/my.cnf
[Mysqld]
Log_bin=master-bin//Enable Binlog log
server_id = 10//Specify Server ID number
binlog_format=mixed//Specify log format
Sync-binlog=1//Allow log synchronization
....

#systemctl start mysqld//Start service

2. Authorize Backup users:
-Allow Lisi to access from 192.168.4.0/24 network segment
-Synchronize permissions on all libraries (not allowed for a single library by default)
Mysql>grant replication Slave " Lisi" @ "192.168.4.%" identified by "123456";

3. Check the Master status:
-Remember the current log file name, offset location
Mysql>show Master Status\g;
......
file:master-bin.000002//log file name
position:334//Offset
.....

Configure the slave server
1. Enable Binlog and allow synchronization to enable read-only mode.
Vim/etc/my.cnf
[Mysqld]
Log_bin=slave-bin//Enable Binlog log
server_id = 20//Specify Server ID number
Sync-binlog=1//Allow log synchronization
Read_only=1//read-only mode, synchronization and Super privilege user exceptions

#systemctl start mysqld//Start service

2. Specify master-related parameters
Mysql>change Master to master_host= "192.168.4.10",
->master_user= "Lisi",
->master_password= "123456",
->master_log_file= "master-bin.000002",//log file
->master_log_pos=334; Offset amount

Mysql>start slave; Start replication

The master information is automatically saved to the/var/lib/mysql/master.info file, and the stop slave should be the first to change the master information;

3. View slave status
-Verify that the IO thread and SQL thread are running
Mysql>show slave Status\g
.....
Slave_io_running:yes//io Thread is running
Slave_sql_running:yes//sql Thread is running

To file out files from the database directory:
/var/lib/mysql/master.info//Connecting master server information
/var/lib/mysql/relay-log.info//Relay log information
/var/lib/mysql/Host name-relay-bin.xxxxxx//relay log file
/var/lib/mysql/bogon-relay-bin.index//trunk Log index file

To test the master-slave synchronization configuration:
1 adding users who access data on the primary library server
2 When the client uses an authorized user to connect to the main library, the resulting data can also be viewed from the Kuben.

master server Common options:
binlog_do_db=//Allow only replicated libraries binlog_do_db= library name 1, library name 2, library name n
binlog_ignore_db=//Not allowed to replicate library binlog_ignore_db= library name 1, library name 2, library name n

Slave Server Common options:
Relay_log=slave-relay-bin//Specify the trunk log file name
Log_slave_updates//record from Library Update, allow chained replication (cascade replication)
replicate_do_db= Library name 1, library name 2, library name N//Copy only the specified library, other libraries will be ignored (copy all libraries when omitted)
replicate_ignore_db= Library name 1, library name 2, library name N//Do not copy which libraries, other libraries will be ignored

Note: replicate_do_db and replicate_ignore_db only need to select one of the

Structure of master-slave replication
One-way malfeasance: from
Chained copy:-----from
Bidirectional replication: Master <--> Slave
Radial replication: From <--to Main----from

        MySQL读写分离

Read-Write separation: Requests the client to access the data when the query request select and write insert to different database server processing.
The principle of Read and write separation:
1. More than one MySQL server
-Provide read and write services, and balanced traffic
-Maintain consistency through master-slave replication
2. Client-facing by MySQL agent
-When a SQL write request is received, submit it to server A for processing
-When a SQL read request is received, it is given to Server B for processing
-specific policy is set by service

Build MySQL read-write separation
1. Build MySQL master-slave replication
-slave as read-only

2. Add a MySQL proxy server
-Deploy/Enable Maxscale

3. Client access to MySQL database via proxy host

Deploying a MySQL Agent
Install Maxscale:
RPM-IVH maxscale-2.1.2-1.rhel.7.x86_64.rpm
RPM-QC Maxscale
/ETC/MAXSCALE.CNF//Master configuration file
....

To modify a configuration file:
Vim/etc/maxscale.cnf
[Server1]//define database server
Type=server
address=192.168.4.10//master Host IP address
port=3306
Protocol=mysqlbackend

[Server2]//define database server
Type=server
address=192.168.4.20//slave Host IP address
port=3306
Protocol=mysqlbackend

[MySQL Monitor]
Type=monitor
Module=mysqlmon
Server=server1,server2//definition of master, slave database server list
User=lisi//user name
passwd=123456//password
monitor_interval=10000

[Read-write Service]
Type=service
Router=readwritesplit
Servers=server1,server2//definition of master, slave database server list
User=zhangsan//user name
passwd=123456//password
max_slave_connections=100%

Create an authorized user on the primary, from the database server
Mysql>grant replication Slave,replication Client on . to [email protected] '% ' identified by ' 123456 '; Create a monitoring user
Mysql>grant select on MySQL. To [e- mail protected] '% ' identified by "123456";//Create Routing User
Mysql>grant * to [e-mail protected] '% ' identified by "123456"; Create an Access user

Start Maxscale
Main command:
-Start Service
-View Ports
-Stop Service
Maxscale--config=/etc/maxscale.cnf//start-up service
or Maxscale-f/etc/maxscale.cnf
NETSTAT-ALNTPU |grep Maxscale
Pkill-9 Maxscale//Stop service

The port used by the read-write separation service, the port used by the management service
4006 4009

Client Testing
Log in to the MySQL agent:
-MYSQL-H Proxy IP address-p Port-u user name-p password

Test SQL queries, update operations
-Can query table record successfully
-Data can be written successfully

Log in to the MySQL agent:
mysql-h192.168.4.100-p4006-uadmin-p123456
Mysql>select @ @hostname; To view the host name of the current access

MySQL master-slave synchronization and read/write separation