MySQL full backup and recovery

MySQL full backup and recovery skill targets

• Master the method of backup using Nysqldump
• How to master MySQL data recovery

MySQL full backup 1, the importance of database backup (1) Improve the system's high availability and disaster recoverability, when the database crashes without backup will not be able to retrieve the previous data (2) Use a database backup to restore the database, Is the minimum cost of providing data recovery when a database crashes (3) Backing up a database is an important way to protect against disasters. 2: Several causes of database crash errors (1) program error: Data loss due to error in database operator (2) human error: Refers to data loss and destruction caused by human causes (3) computer failure: refers to running the database server operation or software corruption (4) disk failure: A hardware device that stores data such as a hard disk, data loss due to damage caused by prolonged operation (5) disaster (into a fire, earthquake) and theft: refers to a large amount of data loss resulting from a natural disaster 2, Classification of database backups (1) Physical backup: Refers to a backup cold backup of the physical files (into data files, log files) of the database operating system: operating in a database shutdown hot backup: Backing up in a database run (2) logical backup: Refers to a logical component of data such as a table, Databases and other objects) can be divided into full backups, differential backups, Incremental backup (1) Full backup: Every time a full backup of the data (2) A differential backup: Backup after a full backup but this may cause a duplicate backup (3) Incremental backup: Operate a backup once after a full backup does not appear duplicate backup MySQL backup walkthrough physical backup folder Backup with tar package

#数据库文件都在/usr/local/mysql/data文件夹里[[email protected] ~]# cd /usr/local/mysql/data/[[email protected] data] ls #整个data文件夹进行备份@[email protected]@[email protected]@8868  ib_buffer_pool  ib_logfile0  ibtmp1  performance_schema  sysauto.cnf                   ibdata1         ib_logfile1  mysql   shcool[[email protected] ~] tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/[[email protected] ~] cd /opt/[[email protected] opt] ls #会压缩成tar.xz格式的压缩包mysql-2018-07-02.tar.xz[[email protected] opt] du -sh /usr/local/mysql/data/134M    /usr/local/mysql/data/[[email protected] opt] du -sh  #用du -sh查看包的大小节省了很多空间现在把data的文件夹删除都没事 /opt/mysql-2018-07-02.tar.xz 704K    /opt/mysql-2018-07-02.tar.xz#解压缩恢复数据文件[[email protected] opt] tar Jxvf /opt/mysql-2018-07-02.tar.xz /usr/local/mysql/data/

Logical Backup with mysqldump tool backup format mysqldump-u user-P [database name or table name] >/backup path/backup file name. SQL #以. SQL End Demo Instance

mysql> show databases;  #对shcool数据库进行完全备份+--------------------+| Database           |+--------------------+| information_schema || ×××表         || mysql              || performance_schema || shcool             || sys                |+--------------------+6 rows in set (0.00 sec)[[email protected] opt]# mysqldump -u root -p shcool > /opt/shcool.sql #-p后面跟密码可以免交互Enter password: [[email protected] opt]# lsshcool.sql

To back up the info table in the Shcool database

mysql> show tables;+------------------+| Tables_in_shcool |+------------------+| info             |+------------------+1 row in set (0.00 sec)[[email protected] opt] mysqldump -u root -p shcool info > /opt/info.sql

Backup format for multiple databases mysqldump-u root-p--databases [Database] [Database] >/backup path/backup name

mysql> show databases;  #对shcool数据库和mysql数据库进行备份+--------------------+| Database           |+--------------------+| information_schema || ×××表         || mysql              || performance_schema || shcool             || sys                |+--------------------+[[email protected] ~] mysqldump -u root -p --databases shcool mysql > /opt/shcool-mysql.sql[[email protected] ~] cd /opt/[[email protected] opt] lsshcool-mysql.sql

Backup format for all databases Mysqldump-u root-p--all-databases >/backup path/backup name

[[email protected] opt] mysqldump -u root -p --all-databases > /opt/all.sqlEnter password:[[email protected] opt] lsall.sql        

Back up the table structure (because sometimes we don't need the data in the table just want the structure of the table if creating a table with Create tables will be cumbersome) there is a backup format for the table structure only mysqldump-u root-p-d [Database name] [table name] >/backup path /Backup Name

[[email protected] opt] mysqldump -u root -p -d mysql usr > /opt/usr.sqlEnter password: [[email protected] opt] lsusr.sql

There is a backup to recover, restore two methods 1:source in the MySQL database operation format: Mysql>source/backup path 2:mysql not login database in Linux mode operation format: [[email protected] ~] Mysql-u root-p [restored database name] </backup path/Backup name Instance source app

#把备份的shcool数据库恢复 (Note that the database is made up of tables, so to restore data to first create Chcool database into the database with source recovery) mysql> show databases; #这边没有shcool数据库先创建 +--------------------+| Database |+--------------------+| Information_schema | | XXX Table | | MySQL | | Performance_schema | |  SYS |+--------------------+5 rows in Set (0.00 sec) mysql> CREATE database shcool;         #创建数据库Query OK, 1 row Affected (0.00 sec) mysql> Show databases; #已经有这个数据库了 +--------------------+| Database |+--------------------+| Information_schema | | XXX Table | | MySQL | | Performance_schema | | Shcool | |  SYS |+--------------------+6 rows in Set (0.00 sec) mysql> use Shcool;  #进入数据库看有没有表Database changedmysql> Show tables; #里面没有任何表Empty Set (0.00 sec) mysql> source/opt/shcool.sqlquery OK, 0 rows Affected (0.00 sec) #执行成功Query OK, 0 rows AF    Fected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) mysql> Show tables; #数据库恢复成功+------------------+| Tables_in_shcool |+------------------+| Info |+------------------+1 row in Set (0.00 sec)

Restore the database in Linux mode with MySQL

#同样先创建同名数据库[[email protected] opt] mysql -u root -p shcool < /opt/shcool.sql Enter password: [[email protected] opt] mysql -u root -p Enter password: mysql> show tables;   #恢复完成+------------------+| Tables_in_shcool |+------------------+| info             |+------------------+1 row in set (0.00 sec)

To summarize: Restore the database, restore the database must have a database with the same name in order to recover the tables inside the database is the same as the MySQL command or the source command.

MySQL full backup and recovery