MySQL Sub-database sub-table backup

One, backup the database separately

Mysqldump-uroot-poldboy Oldboy >/opt/oldboy.sql The simplest backup

1) MySQL based MyISAM engine

Mysqldump-uroot-poldboy-b-x-f Oldboy | Gzip >/opt/oldboy.sql.gz

2) After 5.5 The default is InnoDB (recommended)

Mysqldump-uroot-poldboy-b-F--single-transactio Oldboy | Gzip >/opt/oldboy.sql.gz

second, multi-instance sub-library backup database (for writing shell scripts)

The main idea is to list the library, a for-loop backup

Mysql-uroot-poldboy-e "show databases;" | Egrep-evi "info|perfor" |sed-r ' s#^ ([a-z].*$) #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 is a backup of statements that create libraries and query libraries, so it is recommended to add (when you typically back up multiple libraries), x all tables in all databases are locked

Backup table

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

Three, multi-instance sub-table backup

The main idea is to list the tables, two-tier for-loop backup

/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.gz         done         $MYSQLDUMP     $dbname  |gzip > /$BAKPATH/${dbname}_$ () $ (date +%f). Sql.gzdone

Note: (For all hands, please point out the question)

If the database is not recommended to be backed up by two, it is sometimes only required to restore a table

-D parameter backs up the table structure only

-T parameter backs up data only

-F Flush Binlog Log

Other parameters mysqldump--help query

Bulk Recovery Library Approximate process

For dbname in ' ls *.sql ';d o mysql-uroot-p123456 < $dbname. sql;done

Four, incremental backup

mysqlbinlog/data/3306/data/mysql-bin.0001 >>/tmp/all.sql

Specific content:http://blog.51yip.com/mysql/1042.html

http://blog.csdn.net/jesseyoung/article/details/41211841

This article is from the "Linux operation and Maintenance" blog, reproduced please contact the author!

MySQL Sub-database sub-table backup