Simple MySQL large table backup method

MySQL large table backup is a common problem. Next we will introduce you to a simple MySQL large table backup method, hoping to help you learn about MySQL large table backup.

The large table mentioned here is a table larger than 4G. I have seen a single table larger than 60G at present. It is very painful for such a table to have full backup every day.
Is there a way to implement a full backup plus Incremental backup.
Yes, of course.

You can directly use full backup and binlog for saving in the conventional environment.
Most of these environments can be backed up on a server Load balancer instance.

Ideas:
Stop Slave synchronization and refresh the buffer. If Innodb wants to copy the data directly, set innodb_max_dirty_pages_pct to zero, and then execute flush tables once;
Cp. This can be done if Dump is used.

This solution is perfect at present, but the Binlog of a highly concurrent application may reach 50 GB to 60 GB in a day, such a system's Binlog can be said to have an early impact on the system's IO performance and overall performance.

Another solution is to back up data based on the changes in the data on the table.
Main Idea: Full backup and logical backup.
Logical backup: when data is inserted, the trigger is used to write data to another table at the same time. When the data is updated, we record the updated data to another table.
When there is a delete operation, you only need to record the deleted primary ID.

Example:
Table to be backed up:

 
 

  
  
CREATE TABLE `wubx` (  `id` int(11) NOT NULL auto_increment,   
  
  
`user_id` int(11) NOT NULL default '0',   
  
  
`friend_id` int(11) NOT NULL default '0',   
  
  
`dir_id` int(11) NOT NULL default '0',   
  
  
`created` int(11) NOT NULL default '0',   
  
  
UNIQUE KEY `id` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
 
 

For this table, we need to create a table that records new data changes:

 
 

  
  
mysql> create table wubx_ii like wubx;  
  
  
Query OK, 0 rows affected (0.00 sec)  
  
  
 
  
  
mysql> create table wubx_uu like wubx;  
  
  
Query OK, 0 rows affected (0.00 sec)  
  
  
mysql> create table wubx_dd ( id int(11));  
  
  
Query OK, 0 rows affected (0.00 sec)  
  
  
 
 
 

Create a trigger

 
 

  
  
Insert record operations:
  
  
Delimiter //
  
  
Create trigger wubx_ii after insert on wubx for each row begin insert into wubx_ii set id = new. id, user_id = new. user_id, friend_id = new. friend_id, dir_id = new. dir_id, created = new. created; end //
  
  
Record update operations:
  
  
Create trigger wubx_uu after update on wubx for each row begin replace into wubx_uu set id = new. id, user_id = new. user_id, friend_id = new. friend_id, dir_id = new. dir_id, created = new. created; end //
  
  
Record deletion operations:
  
  
Create trigger wubx_dd after delete on wubx for each row begin insert into wubx_dd values (old. id); end //
  
  
 
  
  
Delimiter;
  
  
 
 
 

Operation:
First, back up the data in the original table wubx:
Run:

 
 

  
  
insert into wubx values(”,1,10,1,1198464252);  
  
  
insert into wubx values(”,1,11,1,1198464252);  
  
  
insert into wubx values(”,1,2,1,1198464252);  
  
  
insert into wubx values(”,2,10,1,1198464252);  
  
  
insert into wubx values(”,2,12,1,1198464252);  
  
  
insert into wubx values(”,3,12,1,1198464252);  
  
  
update wubx set dir_id=5 where user_id=3;  
  
  
update wubx set dir_id=4 where user_id=3;  
  
  
delete from wubx where user_id=2 and friend_id=12;  
 
 

Now we need to implement Incremental Backup:
Insert removal:

 
 

  
  
mysql -e ” select concat(‘replace into wubx set id=’,id,’,user_id=’,user_id,’,friend_id=’,friend_id,’,dir_id=’,dir_id,’,created=’,created,’;') from wubx_ii;”>>backup_ii.sql  
 
 

Retrieve update:

 
 

  
  
mysql -e ” select concat(‘update wubx set user_id=’,user_id,’,friend_id=’,friend_id,’,dir_id=’,dir_id,’,created=’,created,’ where id=’,id,’;') from wubx_uu;”>>backup_uu.sql  
 
 

Delete removal:

 
 

  
  
mysql -e “select concat(‘delete from wubx where id=’,id,’;') from wubx_dd”>>backup_dd.sql  
 
 

In this way, it is easy to use the logical backup plus to complete the backup and restore to the current recovery point. This is not demonstrated here.

It is best to use a program to complete this operation. After the backup is completed, make a mark point to identify the backup data to ensure that the data volume in the logical record table is relatively small.
 

 

Implementation of MySQL table sharding

MySQL authorization table usage example

Implementation of MySQL multi-Table Deletion

Advantages and disadvantages of MySQL independent tablespace

Restoration of MySQL MyISAM Table Structure