MySQL Backup and restore

1. Backup Type

Depending on whether the server is able to continue to provide services in the case of a backup: hot, warm, cold backup.

Hot backup: Online, read, write not affected;

Warm backup: Online, but can only perform read operations;

Cold backup: Offline, read, write operations can not be carried out;

Depending on whether the data file is copied directly or the data export is backed up into a physical backup, a logical backup.

Physical Backup: copying data files directly;

Logical backup: Export data to a text file;

Depending on the backup content is divided into: full backup, incremental backup, differential backup.

Full backup: Back up all data;

Incremental backup: Backs up only data that has changed since the last full or incremental backup;

Differential backup: Backs up only data that has changed since the last full backup;

2. Back up and restore data using the Mysqldump tool

Mysqldump is a logical backup tool that can implement Win Bei for the MyISAM engine, and the InnoDB engine can be hot prepared. Slow, not suitable for full backups of large databases. The binary logging function should be switched off temporarily (Sql_log_bin=off) when the data is restored and enabled after the restore.

The usage of the mysqldump command and its common options:

mysqldump-uroot-p [db_name] [TB1] [TB2] > Backfile #备份单个数据库, or a specific table in the library--master-data={0|1|2} 0: Do not log binary log files and their 1: Record position in Chnage master to, can be used to start directly from server after recovery, 2: Record position in change MASTER to, but default is commented;--lock-all-tables: Lock All Table--flush-logs: Perform log scrolling--all-databases: Back up all libraries--databases db_name,db_name,... : Backing up the specified library--events: Backup event--routines: Backing up stored procedures and storage functions--triggers: Backup trigger--no-data: Back up the table structure if the table type in the specified library is InnoDB, you can use--singl E-transaction Start hot standby;

Mysqldump full backup plus binary log incremental backup and restore instance:

Backup:

Mysqldump-uroot-p--master-data=2--flush-logs--all-databases--lock-all-tables >/root/all.sql #完全备份mysqlbinlog mysql-bin.000011 >/root/first-incremental.sql #第一次增量备份

Delete data files simulate a failure restore (assuming that the binary log file used in the failure is mysql-bin.000012 and that the binary log files and data files are stored separately):

Killall mysqld/usr/local/mysql/scripts/mysql_install_db--user=mysql--datadir=/mydata/data #初始化数据库service mysqld Startmysql-uroot-p </root/all.sql #还原完全备份mysql-uroot-p </root/first-incremental.sql #还原增量备份mysqlbinlog m ysql-bin.000012 >/tmp/tmp.sqlmysql-uroot-p </tmp/tmp.sql #即时点恢复

3. Back up data with the Select command and restore

Select is typically used to back up a single table, and the restore table structure needs to be pre-created.

Backup:

Mysql> SELECT * to OUTFILE '/path/to/somefile.txt ' from tb_name [WHERE clause];

Restores:

mysql> LOAD DATA INFILE '/path/to/somefile.txt ' into TABLE tb_name;

MySQL Backup and restore