Replication and read-write separation for the most complete MySQL

MySQL replication and MySQL read and write separation is never a simple topic, today I will be detailed to record my study of MySQL.
MySQL day to type are: binary log, transaction log, error log, general query log, relay log, slow query log.

Binary log is generally placed in the data directory, the general file is called Mysql-bin.xxxx, when the data is large enough to set the maximum size of the time will be scrolling, there are similar to flush, logs, the server restarts will cause scrolling, in MySQL delete log generally we do not use RM to delete, Instead, the MySQL command uses purge to clear the
Binary record formats are statement-based, row-based, and mixed. In addition to mysql-bin-xxx there are some mysql-bin-index files, which is the epitome of MSYQL binary log files
File. To view the binary files currently used by MySQL we use show MASTER STATUS under mysql>;
Show view MySQL binary log related content,mysql> SHOW BINLOG EVENTS in "file";
View a list of binary log files currently present on MySQL. mysql> SHOW BINARY LOGS;
A log is generally recorded: Generation time (timestamp); Position (position) time itself (offset),

Binary logging is not perfect, so if you do not back up your database, it may not be possible to recover data by simply binary files. So the best way is to put the database back on one side


Because the primary server allows parallel execution, such as having multiple CPUs to perform several writes at the same time, writing to the binary log allows only one write, so the transfer from the server can only be one transmission, so from the server will be slower than the supervisor server, or even a lot slower.


If MySQL replication is synchronous, the user will need to wait for the primary server to fully transfer the binaries on the server to perform the next operation, and this is not scientific, so the replication on MySQL is asynchronous, semi-synchronous is as long as the data sent to the tcp/from the server The IP cache is OK, but for MySQL this is not practical, because MySQL also need to save them to the trunk log, one to read. So semi-synchronous and asynchronous are almost the same, so MySQL generally uses async. The biggest difference between MySQL and DRBD is that MySQL supports a master multi-slave.

Look, it's MySQL's basic schematic.

In a master multi-slave mode, as long as there is a return from the server and save the successful information, we will not go to the other from the server, this way we call the semi-synchronous.
MySQL is not supported for semi-synchronous before 5.5,

All data from the server is copied from the primary server, because from the server is backed up, so we need to save the same data as the primary server, if the data from the server and the primary server data is not the same, we lose the meaning of the server, So we don't allow writing from the server and delivering things to the primary server.

Hot backup is complex, and prone to inconsistent data, with the slave server we can stop the MySQL service from the server, and then a cold backup, so much safer.

From the server does not need the binary log, does not work, also delays the speed from the server

MySQL also has multi-level support


In order to speed up our reading and writing, we can assign tasks to them, write only on the primary server, and read from the server only. But so much from the server, we read it on that, simple, load balancer. Looking for a MySQL route in front, if we want to launch MySQL read and write, I need to contact the MySQL agent, it can judge what we do, read the contact from the server, write to contact the main server, this is called MySQL read and write separation. If we read a lot, how can we try to make our reading speed faster, how can we know that my read operation is the one that responds to us from the server? Simple, add a LVS on the line. Just add a directory to the front end.

What if the directory is broken? What if the MySQL agent breaks down? Good, all made high-availability, such as three in order to speed up, we can add a shared cache, regardless of the server's query can be temporarily cached to Memcnche, regardless of that server query first to Memcnche query.

In order to increase our read and write speed we add a lot from the server, but so many from the server does every time we have to start a thread from the primary server to send the log from the binary log to the slave log from the server? Will this generate a lot of Io, and will that speed be fast? Based on this situation, a multi-level replication approach has been created

Multi-level replication, is from the server to select a dedicated relay master server log sent to the other slave server.

PHP does not need to have a relationship with MySQL, the relationship is the PHP language development program.
We can let the development language, we in the configuration time to solve the read and write program, but the development process is more troublesome.

If you do not want the front-end program to be complex, and do not want to provide read-write separation, this is the main main mode, but we do not recommend the use of.
Four, the black line represents the order in which we start, the data that we save in the binary log, the binary log is passed to the trunk log in the other server, the relay log is read out in the database through the local server, stored in the binary log, and then begins the Red section, and the same process, Copied into the first process. But then we find that there is no, this will fall into a loop, awkward, then all we need to do is to add a number to the process so that we no longer run the same server with the same ID locally, so it will not be sent in the binary log.
The two-master model, in fact, can not achieve the average load write operation, because the data on the two nodes must be identical, or it will be wrong. The dual-master model is also prone to crash situations where skin-toning data inconsistencies occur.


For MySQL, a master server can take multiple slave servers, but one cannot take more than one master, and if each master server writes different data from the server, it will be completely replicated to data inconsistencies or conflicts.



Basic steps for configuring MySQL replication:
Simple download decompression will not say, we directly from the configuration to start.

First, configure the primary server

Copy the configuration file to/etc/my.cof
Copy script to/etc/rc.d/in.d/mysqld
Chkconfig-add mysqld
Vim/etc/my.cnf
All of our replication-related configurations are on the mysqld side.
We need to start log-bin=master-bin this one.


2, choose a unique Server-id =1
Server-id = {0-2^32}
innodb_file_per_table = 1
Datedir =/mydata/data

Then service mysqld start


Then configure the file to go from the server

3. Create a user with copy permissions
REPLICATION SLAVE
REPLICATION CLIENT

Second, configure the slave server

1. Enable the relay log
Add Relay-log = Relay-log
Add Relay-log-index = Relay-log.index

2, select a unique Server-id = (must not be equal to the primary server)
Server-id = {0-2^32}

3, connect to the master server, and start copying data;
Mysql> CHANGER MASTER to master_host= ', master_port= ', master_log_file= ', master_log_fiel_pos= ', master_user= ' , master_password= ';
Mysql>show SLAVE status\g (view current status
)
mysql> start SLAVE; (Start service)

mysql> START SLAVE Io_thread; (Yes is normal)
mysql> START SLAVE Sql_thread; (Yes is normal)

Replication Threads:
Master:dump
Slave:io_thread, Sql_thread

How to set the slave server to read-only
Adding in the configuration file
read-only = YES Restart Service valid
But not for anyone with administrator status.

Sync-binlog = On
Set on the primary server for transaction security;

All information from the server will be stored under/mydata/daya/node2.magedu.com.err.


How to make MySQL semi-synchronous, which requires a plugin
This plugin has two files semisync_slave.so and semisync_maste.so in/usr/local/mysql/lib/plugin. One Master one from

To set up a semi-synchronous step:

Run the following code on the MySQL command line in master and slave:

# on Master
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ' semisync_master.so ';
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;
Set whether to enable and time out
# on Slave
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ' semisync_slave.so ';
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
mysql> STOP SLAVE Io_thread; START SLAVE Io_thread;

Edit in master and slave my.cnf:

# on Master
[Mysqld]
Rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000 # 1 Second

# on Slave
[Mysqld]
Rpl_semi_sync_slave_enabled=1


# can also be set by setting a global variable, as follows:
Set Global rpl_semi_sync_master_enabled=1
# Cancel Loading Plugin
mysql> UNINSTALL PLUGIN Rpl_semi_sync_master;




Set primary-primary replication:
1. Establish a user with copy permission on both servers;
2. Modify the configuration file:
# on the master server
[Mysqld]
Server-id = 10
Log-bin = Mysql-bin
Relay-log = Relay-mysql
Relay-log-index = Relay-mysql.index
Auto-increment-increment = 2
Auto-increment-offset = 1

# from the server
[Mysqld]
Server-id = 20
Log-bin = Mysql-bin
Relay-log = Relay-mysql
Relay-log-index = Relay-mysql.index
Auto-increment-increment = 2
Auto-increment-offset = 2

3, if the two servers are newly established, and no other write operations, each server only need to record the current binary log files and the location of the event, as another server replication starting location can be

server1|mysql> SHOW MASTER Status\g
1. Row ***************************
file:mysql-bin.000001
position:710
binlog_do_db:
binlog_ignore_db:
1 row in Set (0.00 sec)

server2|mysql> SHOW MASTER Status\g
mysql> SHOW MASTER Status\g
1. Row ***************************
file:mysql-bin.000003
position:811
binlog_do_db:
binlog_ignore_db:
1 row in Set (0.00 sec)

4, each server next specify to another server as its own master server can:
server1|mysql> change MASTER to ..., master_log_file= ' mysql-bin.000003 ', master_log_pos=811

server2|mysql> change MASTER to ..., master_log_file= ' mysql-bin.000001 ', master_log_pos=710

Replication and read-write separation for the most complete MySQL