MySQL backup and restore for a single sheet

A, MySQL Backup tool xtrabackup installation1. Percona the binary version of the official xtrabackup; The binary version is available for decompression. 2. Unzip Xtrabackup &Create a connectionTar-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/Xtrabackup3. Set the PATH environment variable export path=/usr/local/xtrabackup/bin/: $PATH B, create a user backup user in the MySQL database&Authorized1. Creating user create users [email protected]'localhost'Identified by'backup123'; Create user [email protected]'127.0.0.1'Identified by'backup123'; 2. Authorized Grant Reload,lock Tables,replication Client,process,super on*. * To'Backuper'@'localhost'; Grant Create,insert,SelectOn 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,SelectOn Percona_schema.xtrabackup_history to'Backuper'@'127.0.0.1'; C, pre-backup check, this step of the main goal is to do after the restore operation, verify that the restore is not valid; (production is not this step,1.Select*From Tempdb.dict__major; Select*From Dict__major; +--------------+-----------------+        | Column_value |        Column_mean | +--------------+-----------------+        |1|        Chinese Language and Literature | |2|        Actuarial | |3|        Bio-Pharma | |4|        Material Chemistry | |5|        Business English | |6|        Archaeology | |7|        Diplomacy | |8|        Guide | +--------------+-----------------+D, backup tempdb.dict__major table1. Backup command Innobackupex--host=127.0.0.1--user=backuper--password=backup123--port=3306--include='Tempdb.dict__major'/tmp/tempdb2. After the backup is complete, the backup directory (/tmp/tempdb), a directory named after the current time is generated, and the backup file is stored in the tree/tmp/tempdb//tmp/tempdb/└── .- the-10_18- -- -├──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 it is important to save a copy of the table definition, which will be used when restoring) MySQL>drop table tempdb.dict__major; F, in order to get a consistent backup set before the restore operation also to do a log roll forward and rollback1. Roll Forward &rolling back the log Innobackupex--apply-log--export/tmp/tempdb/ .- the-10_18- -- -/2. With Roll forward &the comparison tree before rolling back/tmp/tempdb//tmp/tempdb/└── .- the-10_18- -- -├──backup-my.cnf├──ibdata1├──ib_logfile0├──ib_logfile1├──tempdb│ ├──dict__major.cfg│├──dict__major.exp│├──dict__major.frm│└──dict__majo R.ibd├──xtrabackup_binlog_info├──xtrabackup_binlog_pos_innodb├──xtrabackup_checkpoi Nts├──xtrabackup_info└──xtrabackup_logfile G, restore tempdb.dict__major table1Create Tempdb.dict__major table dict__major (column_value tinyint notNULL, Column_mean varchar ( +) notNULL, constraint Pk__dict__major primary key (Column_value)); 2. Delete tempdb.dict__major tablespace file alter TABLE tempdb.dict__major discard tablespace; 3Copy the tablespace file from the backup to the location where the Tempdb.dict__major table space should beCP/tmp/tempdb/ .- the-10_18- -- -/tempdb/dict__major.ibd/usr/local/mysql/data/tempdb/CP/tmp/tempdb/ .- the-10_18- -- -/tempdb/dict__major.exp/usr/local/mysql/data/tempdb/CP/tmp/tempdb/ .- the-10_18- -- -/tempdb/dict__major.cfg/usr/local/mysql/data/tempdb/Chown-R mysql:mysql/usr/local/mysql/data/tempdb/*            4. Import table space file 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 |        Bio-Pharma |            | 4 |        Material Chemistry |            | 5 |        Business English |            | 6 |        Archaeology |            | 7 |        Diplomacy |            | 8 |        Guide | +--------------+-----------------+----------------------------------------------------------------------------- -----------------------------------------------------------------the last section of the Xtrabackup is to back up the table,         Its application scenario is that a single table of data is large and in the process of backup to support the write operation of the table, that is, in the current scenario mysqldump This simple backup tool can also meet the requirements; The general steps of Mysqldump Backup A: Create a backup User 1.        Create user [email protected] ' 127.0.0.1 ' identified by ' dumper123 '; Grant SelectOn *. * to [email protected] ' 127.0.0.1 ';        Grant Show View on * * to [email protected] ' 127.0.0.1 ';        Grant Lock tables on * * to [email protected] ' 127.0.0.1 ';        Grant Trigger on * * to [email protected] ' 127.0.0.1 ';        B: Backup 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 Table 1 that has been backed up.        Mysql>drop table tempdb.dict__major;        D: Restore tempdb.dict__major table 1.        mysql-uroot-pxxxxx-h127.0.0.1-p3306 tempdb </tmp/tempdb.dict__major.sql E: Validity of the proof restore 1.        SELECT * from Dict__major; +--------------+-----------------+        | Column_value |        Column_mean |            +--------------+-----------------+        | 1 |        Chinese Language and Literature |            | 2 |        Actuarial |            | 3 |        Bio-Pharma |            | 4 |        Material Chemistry |            | 5 |        Business English | |            6 |        Archaeology |            | 7 |        Diplomacy |            | 8 |        Guide | +--------------+-----------------+

MySQL backup and restore for a single sheet