Backup and restore of MySQL database

mysql5.5

How it works: It's actually the process of outputting data directly from the MySQL repository in a logical SQL statement or generating a backup file.

Grammar:

Backup

Mysqldump-u User name-p password database name, table name > Backup location

Example: Mysqldump-uroot-p ' 123456 ' Test>/opt/mysql_bak.sql

View: Egrep-v "#|\*|^$|--"/opt/mysql_bak.sql

Back up a single table: Mysqldump-u user-p password database name table name > location

Back up multiple tables: mysqldump-u user-p password database name table 1 Table 2 > location

Example: mysqldump-uroot-p123456 test Test_1>/back/sql

Meaning: If all databases are backed up into one data file at backup time, it is more troublesome to recover data from a library.

# # # #备份就是以数据库实际语句导出, if it is garbled, because the exported format does not have a character set, the general recovery into the database will be normal, just outside the system to view the abnormal. Insert is also the way to bulk insert. The efficiency of recovery is very high. ###### multiple instances is to specify sock and then backup.

use in the work

InnoDB Recommended Use

Mysqldump-uroot-p123456-a-B--master-data=2--single-transaction--events |gzip >/opt/mysql.all.gz

MyISAM

Mysqldump-uroot-p123456-a-B--master-data=2-x--events |gzip >/opt/mysql.all.gz

If the hybrid engine is dominated by InnoDB;

the parameters that are common in the mysqldump backup command are:

-A back up the entire database; Note add:--events;

-B increase the command to create the database and connect to the data base , and specify multiple library backups ;

#实际就是增加use DB and CREATE database db information, do not add the-B option when backing up the table;

|gzip to backup database, improve compression efficiency;

Example:

Back up the library test and compress the example:

Mysqldump-uroot-p123456-b Test|gzip >/kong/mysql.back.gz

Back up multiple sub-libraries and compress the example:

mysql-uroot-p123456- e "show databases;" | Grep-evi "Infor|perfor|database|mysql" |sed "s#^ #mysqldump-uroot-p123456-b >/kong/#g" |bash

-D Backup database table structure;

-F Flush Binlog Log

Example: Mysqldump-uroot-p123456-a-b-f--events >/opt/mysql.back

#如备份没有指定-F, when recovering, will not be able to write data, because do not know where to write data, the original part of the data exists when the conflict;

#使用mysqlbinlog View the Binlog under Mysql/data (Binlog need to be opened in my.cnf file in advance)

#binlog的记录是通过位置点 (file size) or time to record

-T only backs up data;

-L read-only lock table;

-X,--lock-all-tables lock table, All people can not operate, to maintain the consistency of data;

--compact, remove comments, suitable for debug debugging to reduce output;

--master-data, add binlog log file name and corresponding location point

--master-data=1

--master-data=2 add comments when backing up

--defult-character-set= Specifies the character set export:

Example:

Mysqldump-ukong-p ' 123456 '--defult-character-set=latin1 test>/opt/mysql_bak1.sql

--single-transaction is suitable for innodb things database backup to ensure the consistency of data;

#就是一个隔离级别, the data submitted by other users during backup is not processed.

To back up multiple libraries do not use the shell steps:

1. Use-e to show which databases are available on the command line:

Mysql-uroot-p123456-e "Show databases;"

2. Use Egrep to filter out the libraries that do not need to be backed up;

3, the use of SED replacement function, before the name of the library to add mysqldump backup commands;

Example: sed "s#^ #mysqldump-uroot-p123456-b-a# #^ #g"

Sed-r "s#^ ([a-z].*$) #mysqldump-uroot-p123456--events-b \1 >/opt/\1.sql#g"

4, then pass to bash execution can;

Mysql-uroot-p123456-e "Show Databases" | Egrep-v "Database|information_schema" |sed ' s#^ #mysqldump-uroot-p123456-b--events |gzip >/back/# ' |bash

Mysql-uroot-p123456-e "show databases;" | Egrep-iv "Database|information_schema" |sed-r "s#^ ([a-z].*$) #mysqldump-uroot-p123456--events-b \1 >/opt/\1.sql #g "| Bash

Fool-style shell backup:

For dbname in ' mysql-uroot-p123456-e ' show databases | Egrep-v "Database|information_schema"
Do
Mysqldump-uroot-p123456-b--events $adbname |gzip >/back/${dbname}_back.sql.gz
Done

Sub-Library Backup:

#!/bin/bash
User=root
passwd=123456
Mysqlcmd= "MYSQL-U$USER-P$PASSWD"
mydump= "Mysqldump-u$user-p$passwd-b |gzip"
For database in test MySQL name

#for database in ' $MYSQLCMD-e "show databases;" | Sed ' 2,4p '

Do
$MYDUMP $database >/back/${database}_$ (date +%f). sql.gz
Done

Note: When there is extra content behind the variable, use the {} number, for example: ${dbnamel}_back.sql.gz

Sub-table Backup:

#!/bin/bash
User=root
passwd=123456
Mysqlcmd= "MYSQL-U$USER-P$PASSWD"
mydump= "MYSQLDUMP-U$USER-P$PASSWD"
For database in ' $MYSQLCMD-e ' show databases; "| Egrep-v "Database|information_schema|mysql"
Do
For tables in ' $MYSQLCMD-e ' show tables from $database; "|sed" 1d "'
Do
Mkdir/back/${database}-P
$MYDUMP $database $tables |gzip >/back/${database}s_${tables}_$ (date +%f). sql.gz
Done
Done

Restore recovery

1. SOURCE command (Login to database):

To view the location of a data backup: System ls/kong/

Recovery: Location of source backup files

Recovery: mysql-uroot-p123456 </kong/mysql

#如果在备份的时候没有添加-B option, you need to specify the name of the recovery database when recovering.

#如果是压缩文件, you need to gzip-d unzip the backup database (the original file will be deleted), and then directly restore

Batch recovery:

Example of a fool recovery:

For dbname in ' ls *.sql | Awk-f. ' {print '} '; Do mysql-uroot-p123456 < ${dbname}.sql;d One

#ls *.sql | Awk-f. ' {print '} ' intercepts database name

-------------------------------------------------------------------------------------------

This article from "As always" blog, declined reprint!

Backup and restore of MySQL database