Shulou(Shulou.com)05/31 Report--

This article mainly explains the "Mysql master-slave replication construction process". The explanation content in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian slowly and deeply to study and learn the "Mysql master-slave replication construction process" together.

I. Related concepts

mysql master-slave copy of the official concept can be Baidu, here to talk about personal understanding and the difference between it and DataGuard (understand wrong please correct).

The master library dumps all sql statements executed in the master library into a binary log file (binlog) through the mysql engine, and then transmits this binlog to the slave end through the network, and then parses the statements in the binlog into sql statements, and then executes them in the backup library. It can be seen that the master-slave replication function of mysql is based on the transmission method of sql statement logic, so parameters must be optimized properly in the process of configuring mysql master-slave replication, otherwise various errors will occur due to parameter limitations.。

- Comparison with DataGuard

DataGuard (DG) is called Data Guard in Chinese and is a disaster recovery solution provided by oracle. DG is generally called primary (corresponding to master in mysql), standby library is called standby (corresponding to slave in mysql), it has three modes, namely physical standby, logical standby and snapshot standby. We generally adopt the configuration method of physical standby, which has the advantages of simple configuration, less error, and less optimization of parameters. It is applied at the standby end by transmitting the archive log to the standby database through the network and then block-to-block (block-to-block replication).(Note: The difference between mysql here is that it does not involve sql statements, and is presented by the method of data block replication). The advantage is that it is simple to maintain, does not involve the logic of sql statements, and is suitable for most production environments. (Mysql is still slightly inferior to DG in this respect. Alright, let's not talk nonsense. Let's start configuring below)

Second, the experimental environment (in fact, this is a real production environment, desensitized)

Platform: Hyper-V

OS:CentOS 6.5

DB: Mysql 5.6

III. Start building preconditions

- Operating system and database installed

- Version consistency

- Close iptables, selinux

Modify master profile

- Master Library

vi /etc/my.cnf

[mysqld]

log-bin=mysql-bin //enable binlog function

server-id=1 //service-id Master and slave must be distinguished

- From the library.

vi /etc/my.cnf

[mysqld]

log-bin=mysql-bin //enable binlog function

server-id=2 //service-id Master and slave must be distinguished

Create sync account and authorize slave

mysql>GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'mysync';

lock master library

mysql>flush tables with read lock;

Determine master library status values

mysql>show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000039 | 308 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

Note: Do not operate MYSQL after this step to prevent the status value of the main server from changing

Configure slave from library

mysql>change master to master_host='xx.xx.xx.xx',master_user='mysync',master_password='mysync',master_log_file='mysql-bin.000089',master_log_pos=592700228;

Mysql>start slave; //Start copying from server

Unlock Master Library

mysql>unlock tables;

Check master-slave copy status

mysql> show slave status \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.9.40.70

Master_User: mysync

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000089

Read_Master_Log_Pos: 182083231

Relay_Log_File: mysqld-relay-bin.000100

Relay_Log_Pos: 182083394

Relay_Master_Log_File: mysql-bin.000089

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

……………………………………………………

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

Note: IO and SQL status marked in red are both yes, master-slave configuration completed!

IV. Troubleshooting

In the actual environment process, there may be a variety of exceptions, here will not simulate the exception (really can not be simulated), mainly discuss the handling method.

Generally, after an exception occurs between the master and slave, you can see whether it is an IO problem or a SQL execution problem by using show slave status\G. If IO is NO, please check whether there is an exception in the network between the master and slave or whether the firewall is enabled.

If the SQL option is NO, then it is necessary to analyze the specific problem. In Last_SQL_Error, it will prompt which sql statement card is the main one. Note. There are many pits here. If it is a parameter problem that causes the card sql, then there should be no problem restarting the mysql service after optimizing the specified parameter file. If there is a write from the library that causes inconsistency, you can only skip the error. Once encountered in a project to execute a table update has been stuck, no matter how to skip also can not, parameters are not a problem, was pit for a long time after the original library connected to a backup emergency environment, the job inside will be updated every once in a while, due to different environments resulting in different initial values so update will inevitably fail, but if it is block-to-block copy mode will shield this error. Solution: Stop the application service of this library.

- Common ways to handle master-slave errors:

start slave;

set global sql_slave_skip_counter=1;

start slave;

Here's a script for you to paste a master-slave copy error report and then automatically send an email reminder

#!/ bin/bash

array=($(mysql -u root -pxxxx -e "show slave status\G"|grep "Running" |awk '{print $2}'))

if [ "${array[0]}" == "Yes" ] || [ "${array[1]}" == "Yes" ]

then

echo "slave is OK" >/var/lib/mysql/backup/script/sync_log.tmp

else

echo "mysql master-slave copy error" >/var/lib/mysql/backup/script/sync_log.tmp

mailx -s "mysql_sync_check" tsl-baijin0829@tasly.com < /var/lib/mysql/backup/script/sync_log.tmp

fi

Thank you for reading, the above is the content of "Mysql master-slave replication construction process", after learning this article, I believe that everyone has a deeper understanding of Mysql master-slave replication construction process, the specific use situation still needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.