A brief discussion on mysql backup and restore for a single table

A. Installation of MySQL backup tool xtrabackup

1. Percona official xtrabackup binary version; the binary version can be used after decompression.

2. Unzip xtrabackup & 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 & authorization in mysql database

1. Create a user

create user backuper@'localhost' identified by 'backup123';
create user backuper@'127.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'@'127.0.0.1';
grant create,insert,select on percona_schema.xtrabackup_history to 'backuper'@'127.0.0.1';

C. Check before backup. The main purpose of this step is to verify whether the restore is effective when performing the restore operation later. (There is no such step in production.

1. select * from tempdb.dict__major;
select * from dict__major;

+--------------+-----------------+
| column_value | column_mean |
+--------------+-----------------+
| 1 | Chinese Language and Literature |
| 2 | Actuarial Science |
| 3 | Biopharmaceuticals |
| 4 | Materials 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 completed, a directory named with the current time will be generated under the backup directory (/tmp/tempdb), which contains the backup files.

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 you must save a copy of the table definition, which will be used when restoring)

mysql>drop table tempdb.dict__major;

F. In order to obtain a consistent backup set, the log must be rolled forward and rolled back before the restore operation.

1. Roll forward & roll back logs

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

2. Comparison with before rollforward & 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 tempdb.dict__major tablespace file

alter table tempdb.dict__major discard tablespace;

3. Copy the tablespace file from 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. Check the recovery status of the dict__major table

select * from dict__major;
+--------------+-----------------+
| column_value | column_mean |
+--------------+-----------------+
| 1 | Chinese Language and Literature |
| 2 | Actuarial Science |
| 3 | Biopharmaceuticals |
| 4 | Materials Chemistry |
| 5 | Business English |
| 6 | Archaeology |
| 7 | Diplomacy |
| 8 | Tour Guide |
+--------------+-----------------+

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

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

Backup tools can also meet the requirements;

Here are the general steps for mysqldump backup:

A: Create a backup user

1.
create user dumper@'127.0.0.1' identified by 'dumper123';
grant select on *.* to dumper@'127.0.0.1';
grant show view on *.* to dumper@'127.0.0.1';
grant lock tables on *.* to dumper@'127.0.0.1';
grant trigger on *.* to dumper@'127.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 the backed up table

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: Verify the validity of the restoration

1.
select * from dict__major;

+--------------+-----------------+
| column_value | column_mean |
+--------------+-----------------+
| 1 | Chinese Language and Literature |
| 2 | Actuarial Science |
| 3 | Biopharmaceuticals |
| 4 | Materials Chemistry |
| 5 | Business English |
| 6 | Archaeology |
| 7 | Diplomacy |
| 8 | Tour Guide |
+--------------+-----------------+

The above article briefly talks about MySQL backup and restore for a single table. This is all I want to share with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.