Summary of several practical methods for MySQL backup and restore

Summary of several common MySQL backup methods:

Direct copy of database files

First, the data in memory is flushed to disk, while the data table is locked to ensure that no new data is written in the copy process:

Mysql>flush TABLES with READ LOCK;

Use the TAR or CP commands to back up the database files, using tar here:

Tar zcvf/backup/mysql_$ (date "+%y%m%d"). Tar.gz/var/lib/mysql

C, after the backup to unlock the data table

mysql> unlock tables;

This method back up the data recovery is also very simple, directly copied back to the original database directory. It is important to note that for INNODB type tables, you also need to back up their log files, that is, ib_logfile* files. Because when the Innodb table is corrupted, you can rely on these log files to recover.

Second, use mysqlhotcopy backup

Mysqlhotcopy is a PERL program that uses LOCK tables, FLUSH tables, and CP or SCP to quickly back up databases. It is the quickest way to back up a database or a single table, but it can only run on the same machine as the database file (including the datasheet definition file, the data file, the index file).
Mysqlhotcopy can only be used for backup MyISAM.

Cases:

Back up the database test to the/backup directory:

/usr/local/mysql/bin/mysqlhotcopy-uroot-pxxx Test/backup

Mysqlhotcopy will lock the database before execution, and will automatically release the lock after completion

Restore :

Mysqlhotcopy Backup is the entire database directory, restore direct copy overwrite on the line, attention to permissions issues
cp-rf/backup/test/var/lib/mysql/
Note that to use mysqlhotcopy, you must have SELECT, RELOAD (to perform FLUSH TABLES) permissions, and you must also have permission to read the Datadir/db_name directory.

Third, mysqldump

Mysqldump is a SQL-level backup mechanism that guides data tables into SQL script files, and can be easily restored or upgraded between different versions of MySQL, which is also the most common method of backup.

The following mysqldump parameters are described below:

–compatible=name
It tells Mysqldump which database or older version of the MySQL server the exported data will be compatible with. Values can be ANSI, mysql323, MYSQL40, PostgreSQL, Oracle, MSSQL, DB2, MAXDB, No_key_options, no_tables_options, no_field_options, etc. , you use a few values to separate them with commas. Of course, it does not guarantee full compatibility, but is as compatible as possible.
–complete-insert,-c
The exported data takes a complete INSERT with the field name, which means that all values are written on one line. This can increase the efficiency of the insertion, but may be affected by the Max_allowed_packet parameter and cause the insertion to fail. Therefore, you need to use this parameter with caution, at least I do not recommend it.
–default-character-set=charset
Specifies what character set to use when exporting data, and if the datasheet is not the default Latin1 character set, you must specify this option when exporting, otherwise the garbled problem will occur after you import the data again.
–disable-keys
Tell mysqldump to add/*!40000 ALTER table Table DISABLE KEYS at the beginning and end of the INSERT statement. and/*!40000 ALTER table table to ENABLE the KEYS * *; statement, which greatly increases the speed of the INSERT statement because it rebuilds the index after all the data has been inserted. This option is only suitable for MyISAM tables.
–extended-insert = True|false
By default, Mysqldump opens the –complete-insert mode, so if you don't want to use it, use this option to set its value to false.
–hex-blob
Exports a binary string field using hexadecimal format. You must use this option if you have binary data. The field types that are affected are BINARY, VARBINARY, and blobs.
–lock-all-tables,-x
Commit the request to lock all tables in all databases before starting the export to ensure data consistency. This is a global read lock and automatically turns off the –single-transaction and –lock-tables options.
–lock-tables
Similar to –lock-all-tables, it locks the currently exported datasheet instead of locking the table under all the libraries at once. This option applies only to MyISAM tables, and if the Innodb table can use the –single-transaction option.
–no-create-info,-t
Exports only data without adding a CREATE TABLE statement.
–no-data,-d
Does not export any data, only the database table structure is exported.
–opt
This is just a shortcut option, equivalent to adding –add-drop-tables–add-locking–create-option–disable-keys–extended-insert–lock-tables–quick– at the same time Set-charset option. This option allows mysqldump to quickly export data, and the exported data can be quickly returned. This option is turned on by default, but can be disabled with –skip-opt. Note that if you run mysqldump without specifying the –quick or –opt option, the entire result set is placed in memory. Problems may occur if you export a large database.
–quick,-q
This option is useful when exporting large tables, forcing mysqldump to obtain records directly from a server query instead of having all the records cached in memory.
–routines,-r
Export stored procedures and custom functions.
–single-transaction
This option submits a BEGIN SQL statement before the data is exported, and the begin does not block any applications and guarantees the consistency state of the database at the time of export. It applies only to transaction tables, such as InnoDB and BDB.
This option and the –lock-tables option are mutually exclusive, because lock tables will cause any pending transactions to be implicitly committed.
To export a large table, you should use the –quick option in combination.
–triggers
The trigger is also exported. This option is enabled by default, and it is disabled with –skip-triggers.
Cases:

Use mysqldump Backup:

/usr/local/mysql/bin/mysqldump-uroot-pxxx--opt Test >/backup/test.sql
Restores:
/usr/local/mysql/bin/mysqldump-uroot-pxxx Test IV, using master-slave replication mechanism (replication) (real-time database backup) Recommendation

See: MySQL master copy

Finally provide a MySQL automatic backup script, using the Mysqldump tool

#!/bin/bash
#caishzh 20120509

Bakdir= "/backup/mysql" #mysql备份目录
Mail= "Xxx@xxx.com" #邮箱地址
user= "Root" #mysql用户名
Passwd= "JJJ" #mysql密码
db= "Test" #数据库名
date=$ (date "+%y%m%d") #日期

[-D "$BAKDIR"] | | Mkdir-p "$BAKDIR" #如果备份目录不存在则创建

MYSQLDUMP-OPT-U$USER-P$PASSWD $DB |gzip > $BKDIR/$DB _$data.gz #备份并使用gzip打包

If [$?-eq 0];then
#echo "$DB mysql backup" |mutt $MAIL-a $BKDIR/$DB _$data.sql-s "$DB mysql Backup"
LS-LH $BAKDIR/*.gz> $BAKDIR/messages.txt
Mutt $MAIL-a $BKDIR/$DB _$data.gz-s "$DATE: $DB mysql Backup" < $BAKDIR/messages.txt
Else
echo "$DATE MySQL backup Failed" |mail-s "$DATE msyql Bacup Failed" $MAIL
Fi