[MySQLFAQ] series-how to deploy MySQLReplication slave database when there is a large amount of data

When we deploy the MySQLReplication slave database, we usually do a slave database from the beginning, and then gradually replicate the data to the slave server as the business changes. However, if we want to deploy and copy slave databases for a database that has been online for a long time and has this large amount of data, what should we do? This article uses my recent Zabbix database deployment

When we deploy the MySQL Replication slave database, we usually do a slave database from the beginning, and then gradually replicate the data to the slave server as the business changes. However, if we want to deploy and copy slave databases for a database that has been online for a long time and has this large amount of data, what should we do? This article uses my recent Zabbix database deployment

When we deploy the MySQL Replication slave database, we usually do a slave database from the very beginning. Then, as the business changes, the data is gradually replicated to the slave server.

However, if we want to deploy and copy slave databases for a database that has been online for a long time and has this large amount of data, what should we do?

This article takes my recent deployment of MySQL Replication slave database in Zabbix database as an example to show you a new Replication deployment method. Because Zabbix has a large amount of historical data, the InnoDB engine before TokuDB conversion is close to 700 GB. After it is converted to TokuDB, there are more than 300 GB, it is mainly concentrated on several large tables, such as trends_uint and history_uint. It takes too long to restore the database after a full backup. This article is shared only when the data written to the master database is affected too much.

There are several steps for Zabbix data migration:

1. Initialize an empty Zabbix library. 2. Start replication, but ignore several common errors. (for specific meanings of these error codes, check the manual.) # ignore unimportant errors, in extreme cases, you can even ignore all errors directly, for example, # slave-skip-errors = allslave-skip-errors = 10623,. Normally back up and export most small tables, import and restore on the SLAVE server. Here, the data can be exported normally. You do not need to specify the -- master-data Option 4. Export the remaining tables one by one. When backing up a large table, you can also export it in batches to facilitate concurrent import. Use the "-w" parameter of mysqldump and then import and restore the table on SLAVE (you can open the reference article link below) 5. After all the data has been imported, wait until there is no replication delay. Disable the ignore error option and restart to officially provide external services.

After the preceding steps are completed, some inconsistent data may exist, but the data will be gradually overwritten or deleted as expired historical data.

The steps in this case are not applicable to all scenarios and mainly apply:

Data Consistency is not required, and the data volume is relatively large, especially when a single table is large, just like the Zabbix data.

References:

Migrate Zabbix database to TokuDB

[MySQL FAQ] series-mysqldump plus-w parameter backup

This entry was published on. Belongs to the database category and is labeled with MySQL Replication, mysqldump, tokudb, and zabbix. The author is yejr. [MySQL FAQ] series-mysqldump plus-w parameter backup