Backup and restoration of a single mysql table and a single mysql table

Backup and restoration of a single mysql table and a single mysql table

A. Install MySQL backup tool xtrabackup

1. percona official xtrabackup binary version; decompress the binary version to use it.

2. Decompress xtrabackup and create a connection.

Tar-xzvf percona-xtrabackup-2.3.4-Linux-x86_64.tar.gz-C/usr/local/
Ln-s/usr/local/percona-xtrabackup-2.3.4/usr/local/xtrabackup

3. Set the PATH environment variable
Export PATH =/usr/local/xtrabackup/bin/: $ PATH

B. Create a user backup user in the mysql database

1. Create a user

Create user backuper @ 'localhost' identified by 'backup123 ';
Create user backuper @ '2014. 0.0.1 'identified by 'backup123 ';

2. Authorization

Grant reload, lock tables, replication client, process, super on *. * to 'backuper' @ 'localhost ';
Grant create, insert, select on percona_schema.xtrabackup_history to 'backuper' @ 'localhost ';
Grant reload, lock tables, replication client, process, super on *. * to 'backuper' @ '192. 0.0.1 ';
Grant create, insert, select on percona_schema.xtrabackup_history to 'backuper' @ '2017. 0.0.1 ';

C. check before backup. The main purpose of this step is to verify whether the restoration is effective when performing subsequent restoration operations. (production does not have this step,

1. select * from tempdb. dict _ major;
Select * from dict _ major;

+ -------------- + ----------------- +
| Column_value | column_mean |
+ -------------- + ----------------- +
| 1 | Chinese Language and Literature |
| 2 | actuarial |
| 3 | biopharmaceutical |
| 4 | Material Chemistry |
| 5 | Business English |
| 6 | archaeology |
| 7 | diplomacy |
| 8 | tour guide |
+ -------------- + ----------------- +

D. Back up the tempdb. dict _ major table

1. Backup command

Innobackupex -- host = 127.0.0.1 -- user = backuper -- password = backup123 -- port = 3306 -- include = 'tempdb. dict _ major '/tmp/tempdb

2. After the backup is complete, a directory named after the current time will be generated in the Backup Directory (/tmp/tempdb), which stores the backup file

Tree/tmp/tempdb/
/Tmp/tempdb/
── 2016-09-10_18-25-16
── Backup-my.cnf
── Ibdata1
├ ── Tempdb
│ ── Dict _ major. frm
│ ── Dict _ major. ibd
── Xtrabackup_binlog_info
── Xtrabackup_checkpoints
── Xtrabackup_info
── Xtrabackup_logfile

E. After the backup is complete, you can delete the tempdb. dict _ major table (note that the definition of a table must be saved here, which will be used during restoration)

Mysql> drop table tempdb. dict _ major;

F. To obtain a consistent backup set, log rollback and rollback are performed before the restoration operation.

1. Roll Forward and rollback logs

Innobackupex -- apply-log -- export/tmp/tempdb/2016-09-10_18-25-16/

2. Comparison with rollback and rollback

Tree/tmp/tempdb/
/Tmp/tempdb/
── 2016-09-10_18-25-16
── Backup-my.cnf
── Ibdata1
── Ib_logfile0
── Ib_logfile1
├ ── Tempdb
│ ── Dict _ major. cfg
│ ── Dict _ major. exp
│ ── Dict _ major. frm
│ ── Dict _ major. ibd
── Xtrabackup_binlog_info
── Xtrabackup_binlog_pos_innodb
── Xtrabackup_checkpoints
── Xtrabackup_info
── Xtrabackup_logfile

G. Restore the tempdb. dict _ major table

1. Create the tempdb. dict _ major table

Create table dict _ major (
Column_value tinyint not null,
Column_mean varchar (32) not null,
Constraint pk _ dict _ major primary key (column_value ));

2. Delete the tablespace file of tempdb. dict _ major.

Alter table tempdb. dict _ major discard tablespace;

3. Copy the tablespace file in the backup to the location where the tempdb. dict _ major tablespace should be located.

Cp/tmp/tempdb/2016-09-10_18-25-16/tempdb/dict _ major. ibd/usr/local/mysql/data/tempdb/
Cp/tmp/tempdb/2016-09-10_18-25-16/tempdb/dict _ major. exp/usr/local/mysql/data/tempdb/
Cp/tmp/tempdb/2016-09-10_18-25-16/tempdb/dict _ major. cfg/usr/local/mysql/data/tempdb/
Chown-R mysql: mysql/usr/local/mysql/data/tempdb /*

4. Import tablespace files

Alter table tempdb. dict _ major import tablespace;

5. View dict _ major table recovery

Select * from dict _ major;
+ -------------- + ----------------- +
| Column_value | column_mean |
+ -------------- + ----------------- +
| 1 | Chinese Language and Literature |
| 2 | actuarial |
| 3 | biopharmaceutical |
| 4 | Material Chemistry |
| 5 | Business English |
| 6 | archaeology |
| 7 | diplomacy |
| 8 | tour guide |
+ -------------- + ----------------- +

---------------------------------------------------------------

In the previous section, xtrabackup is used to back up a table. The Application Scenario of xtrabackup is that the data volume of a single table is large and the table write operation must be supported during the backup process; that is to say, in the current scenario, mysqldump is simple.

Backup tools can also meet the requirements;

The general steps for mysqldump backup are provided.

A: create A backup user.

1.
Create user dumper @ '2014. 0.0.1 'identified by 'dumper123 ';
Grant select on *. * to dumper @ '192. 0.0.1 ';
Grant show view on *. * to dumper @ '2017. 0.0.1 ';
Grant lock tables on *. * to dumper @ '2017. 0.0.1 ';
Grant trigger on *. * to dumper @ '2017. 0.0.1 ';

B: Back up the tempdb. dict _ major table

1.
Mysqldump -- host = 127.0.0.1 -- port = 3306 -- user = dumper -- password = dumper123 -- quick tempdb dict _ major>/tmp/tempdb. dict _ major. SQL

C: delete a table that has been backed up.

1.
Mysql> drop table tempdb. dict _ major;

D: Restore the tempdb. dict _ major table

1.
Mysql-uroot-pxxxxx-h127.0.0.1-p3306 tempdb </tmp/tempdb. dict _ major. SQL

E: validity of the Certificate Restoration

1.
Select * from dict _ major;

+ -------------- + ----------------- +
| Column_value | column_mean |
+ -------------- + ----------------- +
| 1 | Chinese Language and Literature |
| 2 | actuarial |
| 3 | biopharmaceutical |
| 4 | Material Chemistry |
| 5 | Business English |
| 6 | archaeology |
| 7 | diplomacy |
| 8 | tour guide |
+ -------------- + ----------------- +

The above discussion about the backup and restoration of mysql for a single table is all the content shared by Alibaba Cloud. I hope you can give us a reference and support for the customer's house.