Detailed introduction to mysql sample code for backup and restore of a single table

The editor below will bring you a brief discussionmysql on the backup and restoration of a single table. The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor and take a look.

A. Installation of MySQL backup tool xtrabackup

1. The binary version of percona’s official xtrabackup; binary The version can be decompressed and used.

2. Unzip xtrabackup & create 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 PATH environmentVariable

export PATH=/usr/local/xtrabackup/bin/:$PATH

B, Create a user backup user & authorization in mysql database

##1. Create user

    create user backuper@'localhost' ident
if
ied 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 doing the restore operation later. ;(There is no such step in production,##1.

select * from tempdb.dictmajor;   
select * from dictmajor;
        +--------------+-----------------+        
        | column_value | column_mean     |        
        +--------------+-----------------+        
        |            1 | 汉语言文学      |        
        |            2 | 精算            |        
        |            3 | 生物制药        |        
        |            4 | 材料化学        |        
        |            5 | 商务英语        |        
        |            6 | 考古            |        
        |            7 | 外交            |        
        |            8 | 导游            |        
        +--------------+-----------------+

D. Back up the tempdb.dictmajor table

1.

Backup command

innobackupex --host=127.0.0.1 --user=backuper --password=backup123 --port=3306 --include='tempdb.dictmajor' /tmp/tempdb

2. After the backup is completed, the current file will be generated in the backup directory (/tmp/tempdb) The catalog named, the backup file is stored

## 时间 时间

# E, you can delete it after the backup is completed.

tempdb.dictmajor table (note that a table definition must be saved here, which will be used when restoring)

   tree /tmp/tempdb/
        /tmp/tempdb/
        └── 2016-09-10_18-25-16
            ├── backup-my.cnf
            ├── ibdata1
            ├── tempdb
            │   ├── dictmajor.frm
            │   └── dictmajor.ibd
            ├── xtrabackup_binlog_info
            ├── xtrabackup_checkpoints
            ├── xtrabackup_info
            └── xtrabackup_logfile

F. In order to get a A consistent backup set requires a log roll forward and roll back before the restore operation

1. Roll forward & roll back log

mysql>drop table tempdb.dictmajor;

2. Comparison with rollforward & rollback

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

           

##G, restore tempdb.dictmajor table

1. Create tempdb.dictmajor table

  tree /tmp/tempdb/
        /tmp/tempdb/
        └── 2016-09-10_18-25-16
            ├── backup-my.cnf
            ├── ibdata1
            ├── ib_logfile0
            ├── ib_logfile1
            ├── tempdb
            │   ├── dictmajor.cfg
            │   ├── dictmajor.exp
            │   ├── dictmajor.frm
            │   └── dictmajor.ibd
            ├── xtrabackup_binlog_info
            ├── xtrabackup_binlog_pos_innodb
            ├── xtrabackup_checkpoints
            ├── xtrabackup_info
            └── xtrabackup_logfile

2. Delete the table space file of tempdb.dictmajor

  create table dictmajor(
        column_value tinyint not null,
        column_mean varchar(32) not null,
        constraint pkdictmajor primary key (column_value));

3. Copy the table space file in the backup to the location where the tempdb.dictmajor table space should be

alter table tempdb.dictmajor discard tablespace;

       

4. Import the table space file

   cp /tmp/tempdb/2016-09-10_18-25-16/tempdb/dictmajor.ibd /usr/local/mysql/data/tempdb/
        cp /tmp/tempdb/2016-09-10_18-25-16/tempdb/dictmajor.exp /usr/local/mysql/data/tempdb/
        cp /tmp/tempdb/2016-09-10_18-25-16/tempdb/dictmajor.cfg /usr/local/mysql/data/tempdb/
        chown -R mysql:mysql /usr/local/mysql/data/tempdb/*

5. Check the dictmajor table recovery status

alter table tempdb.dictmajor import tablespace;

- -------------------------------------------------- ------------The previous section used xtrabackup to back up the table. Its application scenario is that the data volume of a single table is large and it must be supported during the backup process. Write operations to the table; that is to say, in the current scenario, the simple

backup tool mysqldump can also meet the requirements;

The general steps for mysqldump backup are now given

A: Create a backup user

1.

      select * from dictmajor;
        +--------------+-----------------+
        | column_value | column_mean     |
        +--------------+-----------------+
        |            1 | 汉语言文学      |
        |            2 | 精算            |
        |            3 | 生物制药        |
        |            4 | 材料化学        |
        |            5 | 商务英语        |
        |            6 | 考古            |
        |            7 | 外交            |
        |            8 | 导游            |
        +--------------+-----------------+

B: Back up the tempdb.dictmajor table

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';

C: Delete the backed up table

1.

mysqldump --host=127.0.0.1 --port=3306 --user=dumper --password=dumper123 --quick tempdb dictmajor >/tmp/tempdb.dictmajor.sql

D: Restore tempdb.dictmajor table

1.

mysql>drop table tempdb.dictmajor;

E: Verify the validity of the restore

1.select * from dictmajor;

mysql -uroot -pxxxxx -h127.0.0.1 -p3306 tempdb </tmp/tempdb.dictmajor.sql

The above is the detailed content of Detailed introduction to mysql sample code for backup and restore of a single table. For more information, please follow other related articles on the PHP Chinese website!