Shulou(Shulou.com)06/01 Report--

I. back up the database separately

Mysqldump-uroot-poldboy oldboy > / opt/oldboy.sql 's simplest backup

1) mysql is based on myisam engine

Mysqldump-uroot-poldboy-B-x-F oldboy | gzip > / opt/oldboy.sql.gz

Backup given by DBA:

Mysqldump-user=root-all-databases-flush-privileges-lock-all-tables-master-data=1-flush-logs-triggers-routines-events-hex-blob > / mybackup/alldatabase.sql

2) innodb is the default value after 5.5 (recommended)

Mysqldump-uroot-poldboy-B-F-- single-transactio oldboy | gzip > / opt/oldboy.sql.gz

Backup given by DBA:

Mysqldump-user=root-all-databases-flush-privileges-single-transaction-master-data=1-flush-logs-triggers-routines-events-hex-blob > / mybackup/alldatabase.sql

2. Backup the database with multiple instances and sub-libraries (for writing shell scripts)

The main idea is to list the library, a for circular backup.

Mysql-uroot-poldboy-e "show databases;" | egrep-Evi "info | perfor" | sed-r's # ^ ([Amurz]. * $) # mysqldump-uroot-poldboy-B\ 1 | gzip > / opt/1.sql.gz # g' | bash

/ bin/bashBAKPATH=/opt/mysqlMYUSER=rootMYPASSWD=123456SOCKET=/data/3308/mysql.sockMYCMD= "mysql-u$MYUSER-p$MYPASSWD-S $SOCKET" MYSQLDUMP=mysqldump-u$MYUSER-P$MYPASSWD-S $SOCKET-B-x-F-R [!-d $BAKPATH] & & mkdir-p $BAKPATHDBLIST= `$ MYCMD-e "show databases; | sed 1d" | egrep-Evi "mysql | info" `for dbname in DBLISTDO $MYSQLDUMP $dbname | gzip > / $BAKPATH/$ {dbname} _ $(date +% F). Sql.gzDONE

Tip:-B even the statements for creating and querying libraries are backed up, so it is recommended to add (usually used when backing up multiple libraries),-x all tables in all databases are locked

Backup tabl

Mysqldump-uroot-poldboy Library name Table name > / opt/table.sql

3. Multi-instance sub-table backup

The main idea is to list the table and back up in a two-tier for loop.

/ bin/bashBAKPATH=/opt/mysqlMYUSER=rootMYPASSWD=123456SOCKET=/data/3308/mysql.sockMYCMD= "mysql-u$MYUSER-p$MYPASSWD-S $SOCKET" MYSQLDUMP=mysqldump-u$MYUSER-P$MYPASSWD-S $SOCKET-x-F-R [!-d $BAKPATH] & & mkdir-p $BAKPATHDBLIST= `$MYCMD-e "show databases; | sed 1d" | egrep-Evi "mysql | info" `for dbname in DBLISTdo TLIST=` $MYCMD-e "show tables from $dbname | | sed 1d` for tname in $TLIST do mkdir-p $BAKPATH/$dbname $MYSQLDUMP $dbname $tname | gzip > / $BAKPATH/$dbname/$ {tname} _ $(date +% F). Sql.gzdone $MYSQLDUMP $dbname | gzip > / $BAKPATH/$ {dbname} _ $() $(date +% F) .sql.gzdone

Note: (pure hand fight, please point out if you have any questions)

If the database is not recommended to back up both, sometimes only one table is required to restore

-d parameter backup table structure only

The-t parameter only backs up data

-F refresh binlog log

Other parameters mysqldump-help query

Approximate process of batch recovery of libraries

For dbname in `ls * .sql`; do mysql-uroot-p123456

< $dbname.sql;done 四、增量备份 增量备份时一定要了解二进制的三种模式 可参考内容：http://blog.51yip.com/mysql/1042.html http://blog.csdn.net/jesseyoung/article/details/41211841 五、数据库恢复 恢复时最好的办法时停库，不让往里面写 1.数据库可能有多个，但是日志文件记录的是全部数据库的变化，所以先进行将二进制文件按照数据库进行拆分，用-d参数可以实现： mysqlbinlog -d oldboy /var/lib/mysql/mysql-bin.000002 >

/ tmp/oldboy.sql (get rid of misoperation statements), import complete equipment first, and add equipment after import

two。 There may be errors in the specified time, and half of them will be restored at the specified position. After confirming the skipping point, the recovery is as follows:

A) mysqlbinlog mysql-bin.000002-start-position=370-stop-position=440 | mysql- uroot-p

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.