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

<strong>innobackupex --host=127.0.0.1 --user=backuper --password=backup123 --port=3306 --include=&#39;tempdb.dictmajor&#39; /tmp/tempdb</strong>

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)

<strong><a href="https://www.php.cn/php/php-tp-remove.html" target="_blank">   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</a></strong>

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!