The principle of master-slave replication in MySQL, the configuration process and the actual case

The principle of master-slave replication in MySQL, the configuration process and the actual case
1. What is master-slave replication?
Principle: Master-slave separation, what does it mean? We may as well draw a picture to see. 1 is shown below:

2. Preparation: Prepare two servers, I use virtual machine to install two centos6.7_64-bit operating system, and install MySQL on two servers respectively. My IP addresses are: 192.168.1.15/192.168.1.16, where I define 15 as the primary server and 16 for the slave server.
First, we edit the MySQL configuration file in the master server. (because my MySQL is installed with a non-root user, the configuration file is placed in the/home/formal/mysql/directory, the non-root user installation step can refer to my csdn blog: http://blog.csdn.net/xushouwei/ article/details/52227975)
Before you edit the MySQL configuration file you have to consider, in a computer room, a master more from, or is the main master replication or passive mode of the main master replication, and so many other cases, then the multiple servers between their log copy to Binlog, copy to copy to Will not mess it? It could be messy, so what are you going to do about it? Therefore, we have to give each server a unique server-id. Then this ID, the normal situation of the decision from the server will be in the same LAN, because the consent of a LAN IP in the first three paragraphs are the same, only the last paragraph is not the same, so we generally take the end of IP as a server-id, of course, this is not a rigid rule, just habit. So we started configuring the My.cnf file.
Add the following configuration:
Log-bin=mysql-bin
Binlog_format=mixed
Server-id=15
2 is shown below:

For the explanation of the main binlog-format=row/statement in the configuration:
Statement: Record execution statements, such as update ...
Row: Disk changes are logged
Which is good?
Update age=age+1 where id=3;//statements are long and disk changes are small, it is advisable to use row
Update Set MONEY=MONEY+1000;//statement short and varied, it is advisable to use statement
If you are unsure of the above two, please use mixed, which is determined by the system according to the statement.
Go to the/home/formal/mysql/data directory and view the previous MySQL log information and delete the previous log home server logs information
Cd/home/formal/mysql/data
ll
Rm-rf./mysql-bin.*
3 is shown below:

At this point, our master server is configured, then we configure the server ...
First we need to configure the mysql.cnf file in the/home/formal/mysql/directory.
Vi/home/formal/mysql/mysql.cnf
Add the following configuration:
Log-bin=mysql-bin
Binlog_format=mixed
Server-id=16
Relay-log=mysql-relay
Similarly, go to the/home/formal/mysql/data directory and view the previous MySQL log information, delete the previous log master server logs information
Cd/home/formal/mysql/data
ll
Rm-rf./mysql-bin.*
4 is shown below:

The above master-slave server related configuration has been completed, then we began to formally test this feature!
First login to the main server, enter the/home/formal/mysql directory to login MySQL
Cd/home/formal/mysql/bin
./mysql-uroot-pxushouweidb
5 is shown below:

Log in the same way from the server
Cd/home/formal/mysql/bin
./mysql-uroot-pxushouweidb
6 is shown below:

Grant Read permissions to the slave server account on the primary server
Grant replication client,replication Slave on * * to [e-mail protected] ' 192.168.1.% ' identified by ' xushouweidb ';
Flush privileges;
7 is shown below:

Specify the primary server to replicate from the server through the statement (note that can be a master multi-slave, not a bundle of multi-master), before using the following statement, we need to use the show master status as long as the current primary server log files go to.
Show master status;
8 is shown below:

Execute the following statement from the server
Change Master to
Master_host= ' 192.168.1.15 ',
Master_user= ' Xushouwei ',
Master_password= ' Xushouweidb ',
Master_log_file= ' mysql-bin.000011 ',
master_log_pos=540;
9 is shown below:

To view two server statuses from the server.
Show slave status \g
10 is shown below:

Start the function from the server and view the status again.
Start slave;
Show slave status \g
11-1, 11-2 shows:

Found an error, what is the reason?
We might as well look at our connection from the server to the master server
Ping 192.168.1.15
12 is shown below:

We find that we can ping, the network is through, what is the reason? According to our experience, the most doubtful thing is the firewall problem, so we try to shut down the firewall of the main server.
Service Iptables Stop
Here you can also add firewall port in the main server processing, in order to facilitate my here directly using the form of a firewall to deal with the problem, in general, the internal LAN is not required to use the firewall, otherwise it will affect efficiency.
13 is shown below:

So, we try to start again and look at the status again.
Start slave;
Show slave status \g
14 is shown below:

At this time no error has been made, the explanation has been successful! Let's start the test!
3. Real-Combat testing
See which databases each master and slave server has
Primary server:
show databases;
15 is shown below:

From the server:
show databases;
16 is shown below:

In this way, we create the database demo on the primary server and look again at what databases the master server has.
Create database demo;
show databases;
17 is shown below:

At this point we are looking at what databases are available from the server.
show databases;
18 is shown below:

Did you see that? From the server is not more than a demo database?
Don't believe me to test again ...
We use the demo of the master server, then create a student table and insert two data to see if the server is automatically created for us to create a new Sutent table that contains the two data we just inserted from the primary server. Let's Test it:
Use demo;
CREATE TABLE Stutent
(
ID int primary KEY auto_increment,
Name varchar (20),
Age varchar (20)
);
INSERT into stutent values (1, ' Xu Shouwei 1 ', ' 24 ');
INSERT into stutent values (2, ' Xu Shouwei 2 ', ' 23 ');
19 is shown below:

After processing, we look at the server is not more than a table, and there are two of data.
20 is shown below:

Well, MySQL master-slave replication problem we first understand here!

The principle of master-slave replication in MySQL, the configuration process and the actual case