Shulou(Shulou.com)06/01 Report--

This article mainly tells you how to build a million-level mysql slave database. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on how to build a million-level mysql slave database can bring you some practical help.

Background: 1. The current mysql:Ucloud highly available database instance is located in Beijing. two。 Remote disaster recovery requirements, cross-platform synchronization, and remote dual-living requirements in the later stage. 3. Newly built slave library: Aliyun South China self-built mysql. Inspection:

The      1.Ucloud database instance is redeveloped based on the original mysql, and there are some adjustments in the structure. It is relatively simple to manage each instance with a phpadmin. The master database of highly available instances has been used as a dual master hot backup. One slave and multiple masters are supported after Mysql5.7.

The      2.Aliyun database instance has also been adjusted with many modifications, and the mysql database is strictly controlled to match the database management of the console.

     3.Aliyun database management provides DTS tools for migrating data with powerful functions: migration granularity, continuous data migration (indirectly achieving data synchronization), and more suitable for the environment. The reason why the current demand is not applicable: DTS migration of self-built database requires public network access to the source instance, which is too insecure.

4. At present, cross-platform and remote living can only be achieved through self-building, and the business architecture needs to be greatly adjusted. Let's start with cross-platform remote backup.

The packet of      5.Mysl synchronization is transmitted in clear text by default, which supports ssl verification, but it seems to be difficult to achieve on the cloud platform (private network access). First, security problems are solved by using security groups to control access, then * is established later, and packet transmission is improved through tunneling.

Scenario summary: Mysql master (10.10.123.231): ucloud Beijing server room, highly available instances, private network access, version of 5.6.20-ucloudrel1-logMysql slave (172.17.3.42): aliyun South China self-building steps:

1. Preparatory work:

Create a new ECS instance on the      aliyun platform; install a similar version according to the source mysql version, select mysql5.6.38, download the rpm package, install it using yum, initialize the database, modify the configuration file, and start it.

     adds the configuration as recommended, considering that DTS will be used later. After mysql5.6, GTID is added for synchronization, and the master / slave configuration should be consistent:

Server_id=23gtid_mode=onenforce_gtid_consistency=onlog_bin=mysql_binbinlog_format=rowbinlog_row_image=fulllog-bin-trust-function-creators=1

two。 Data import: find the latest backup of the source mysql from the ucloud console, download it and import it into the self-built mysql.

    download backup encountered problems: browser can download normally, download report 403 using wget under linux

     solution: quote the download address.

The     backup package is close to 40G, and it takes more than a day to import using source.

3. Build port forwarding (similar to mysql proxy CVM)

    selects a centos 7 (private network IP:10.10.123.233, public network IP:123.123.123.123) with the same private network as the source mysql, and configures iptables to forward traffic at port 3306, so that the newly created mysql instance can access the main database.

# echo 1 > / proc/sys/net/ipv4/ip_forward# iptables-t nat-A PREROUTING-d 10.10.123.233-p tcp-- dport 3306-j DNAT-- to-destination 10.10.123.231-p nat-A POSTROUTING-d 10.10.123.231-p tcp-- dport 3306-j SNAT-to-source 10.10.123.233

   also tests connectivity in the new slave library.

4. Master-slave configuration

Main command: > GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *. * TO 'slave'@'%' IDENTIFIED BY' 123456synchronization; / / add master / slave synchronized users > change master to\ master_host= "123.123.123.123",\ master_port=3306,\ master_user='slave',\ master_password='123456',\ master_log_file='mysql-bin.000383',\ master_log_pos='888039274'; / / configure master library information > start slave; > show slave status\ G

     gets synchronized binlog files and locations: some of the downloaded backup files can be found in the first 100 lines of the file (it is inferred that ucloud backups are backed up with the mysqldump command with the-single-transaction-- master-data=2 parameter).

Problems encountered and their solutions

1. Check master-slave synchronization, error: unable to get log from master

    Last_IO_Error: Relay log write failure: could not queue event from master

    reason: the binlog location of configuring master-slave synchronization is abnormal and needs to be re-specified.

> show master status; > show master logs; / / View available binlog files > show binlog events in 'mysql-bin.000383' from 888039274 limit 2; / / specify start location and display quantity

Sure enough,    is a special location, which can not be found directly under mysql. Fortunately, you can also use the mysqlbinlog tool to view it.

   downloads binlog and decompresses it to get mysql-bin.000383 file

# mysqlbinlog-- start-position=888039200-- stop-position=888039300 mysql-bin.000383 / / search with the approximate location and report an error

   can only be exported, and the location is as follows:

# mysqlbinlog mysql-bin.000383 > 383bin.log

The range found by    is:

   can reconfigure the position location and restart the slave process.

two。 Check the master-slave synchronization status and report an error: duplicate key problem, which may be caused by just adjusting the log_position

Considering that you only make a backup first, you can ignore similar errors.

     solution: add slave-skip-errors = 1062 to the mysqld configuration file and restart the mysql service.

Millions of mysql from how to build the library will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.