MySQL master-slave copy construction and detailed

Environment Introduction

Primary server: CentOS6.5 under mysql5.6.30
Installation Please refer to this website: http://blog.csdn.net/hsd2012/article/details/51232612
From server: Win7 under 5.6.17

Principle introduction and advantages and disadvantages

MySQL provides replication from version 3.23, where replication primarily refers to the DDL and DML operations of the primary server, through binary logs (Binlog logs), to the server of the service, and then from the server to the new execution of these logs from the server and the master server to synchronize data. MySQL supports a primary server to replicate from the server at the same time, from the server can also act as a master server for other servers, to achieve chain-like replication.

Advantages

• If there is a problem with the primary server, you can quickly switch to serving from the server.
• Can reduce the pressure on the primary server by executing queries from the server
• Backups can be performed from the server to avoid affecting the performance of the primary server during backup

Limitations

Since MySQL implements asynchronous replication, there is a certain difference between the master and slave servers, and the high-real-time data still needs to be obtained from the primary server.

Pre-preparation

1. Create an account under Linux
Enter Ipconfig in the win command prompt to view the IP

Because my win under IP is 192.168.153.1, so I created to set the host to 192.168.153.%, for the reason, refer to Here

2. Granting permissions

REPLICATION slave permissions are for all databases, only through . , not shool.*, because REPLICATION slave is a copy binlog log.

3. Copy the data from the master server to the slave server and ensure that the data is consistent before the two are built. About this, before writing an article specifically introduced, you can refer to here

Configuring the master server configuration

1. Turn on the Binlog log and set the SERVER_ID

From server configuration

1. Configure server_id
Settings: server-id=2

2. Specify the primary server configuration format as follows
Change Master to
Master_host= ' master_host_name ',//host from server
Master_user= ' replication_user_name ',//Perform replication user name
Master_password= ' Replication_password ',//execute copy user password
Master_log_file= ' recorded_log_file_name ',//binary log file name
master_log_pos= ' recorded_log_postion ';//copy start position

Based on my configuration information
Bin-log Information

Copy user Information

The configuration code I executed is as follows:
Change Master to
Master_host= ' 192.168.153.140 ',
Master_user= ' Lidequan ',
Master_password= ' Lidequan ',
Master_log_file= ' bin-log.000003 ',
master_log_pos=120;

Check whether the primary server is connected from the server to execute start slave;

Execute show Processlist;

Modify the master server data to see the sync effect

In this case, the primary server data is modified to perform the insert and update operation.
Before the operation, the data information is as follows

Perform an insert operation

`class` (`name`) values (‘三年二班‘),(‘三年五班‘),(‘三年七班‘);

Data is also available from the server at this time.

Perform an update operation

`name`=‘三年三班‘ where id=3;

observed, and found that the data had indeed changed

Delete operation

Observation, the data is also synchronized

Data table Definition Operations

1. Add a student table

CREATE TABLEStudent' id ' INT(3) UNSIGNED not NULLAuto_increment COMMENT' Student number ',' name ' CHAR(Ten) not NULL DEFAULT "'COMMENT' Student name ',' class_id ' INT(3) UNSIGNED not NULL DEFAULT 0COMMENT' class number ',PRIMARY KEY(' id ')) Engine=myisamDEFAULTCharset=utf8;

2. Add a field to the student table

ALTER TABLE student ADD COLUMN age TINYINT(2) UNSIGNED NOT NULL DEFAULT 0 COMMENT‘学生年龄‘;

Specify the responsible database or table

Can be replicate-do-db, replicate-do-table, Replicate-ignore-db, Replicate-ignore-table or replicate-wild-do-table to specify that the master-slave database is copied to the database or table from the database.

Some important parameters about data replication

The Master_host,master_port,master_user,master_password,master_log_file,master_log_ is used when building a master-slave server. Pos These parameters are to be configured from the server, the following is a few common startup options, such as Log_slave_updates, Read_Only, master_verify_checksum

Log_slave_updates

Log_slave_updates This parameter is used to configure whether the update operation from the server is written to the binary log, which is not opened by default.
First, let's take a look at the Binlog log content of MySQL that just win

It can be found that we have just executed the increment, delete, change and other operations, it is not recorded.
We can see that log_slave_updates is not activated.

This property is read-only and cannot be set dynamically and can only be set in the configuration file, such as setting will error

Read_Only

Read-only option: Read-only control of all non-temporal tables. But there are two kinds of special cases.
1. Exceptions to the replication threads to ensure that slave is able to conduct replication normally.
2. For users with super privileges, this option can be ignore.

When you log in with a user who does not have super privileges, you will be prompted as follows:

This ensures that the data is read-only and the operation is denied.
Add:
SUPER Privilege:
1. You can have change master to, kill other user's thread's permissions.
2. Purge binary logs to delete binary log, set global to dynamically provision the permissions of the variable.
3. Execute the mysqladmin debug command, turn log on or off, and perform update/insert operation when Read-only is open.
4. Execute start slave, stop slave.
5. You can also connect to the server when the number of connections has reached the maximum value of max_connections.

Master_verify_checksum

Because of hardware and software or network transmission error, resulting in SQL statements running on the primary server inconsistent with the SQL statements running from the server, it is difficult to find the cause of the problem, the MySQL developer in 5.6 Milestone development Release version added Replication Event Checksum (master-slave replication events check) feature. The master_verify_checksum is primarily used to replicate event checksums. When an event is written to binary log (binary logs), checksum also writes binary log, and after the event is transmitted over the network to the Slave server (slave), it is validated from the server and written to the relay from the server Log Because the event and checksum are recorded at each step, we can quickly find out where the problem is.

Manage and maintain view from server state

Use show slave stauts;

In view of this information, it is more important that the two processes "slave_io_runing" and "slave_sql_runing"
Slave_io_runing: This process is responsible for reading the Binlog log from the server from the primary server and writing it from the trunk log on the server.
Slave_sql_runing: This process is responsible for reading and executing the binlog days in the trunk log.
The replication process stops as long as no is in the status of a process in the interim.

Summarize

Master-Slave configuration
First, the configuration on the primary server
1. Create a user and give replication slave permissions
2. Turn on the Binlog log and set the SERVER_ID
Second, from the server configuration
1. Specify server_id
2. Specify the primary server configuration

Note:

1.win MySQL on and off (if you need to configure the path path)

What does 2.server-id do with it?

• MySQL's synchronized data contains Server-id, which identifies which server the statement was originally written from, so Server-id must have, if you set multiple slave servers, each from the server has a unique Server-id value, Must be different from the primary server and other slave servers.
• The slave in each synchronization corresponds to a master thread on master, which is identified by the slave Server-id, and each slave has at most one master thread on the master side, if two slave server-id Is the same, the last one will be kicked off when the next connection succeeds.
• In the MySQL master from synchronization, to ensure that a data does not fall into the dead loop, this is achieved by Server-id.

MySQL master-slave copy construction and detailed