MySQL Sub-database sub-table backup script

vim/data/mysqlback.sh

#! /bin/bash Bakpath=/data/mysql-back Myuser=root Mypass= "123456" Socket=/var/lib/mysql/mysql.sock mycmd= "Mysql-u$myuser-p$mypass-s $SOCKET" mydump= "Mysqldump-u$myuser-p$mypass-s $SOCKET-X-f-r" Dblist= ' $MYCMD-e "show databases;" |sed "1d" |egrep-v "_schema|test" [!-D $BAKPATH] && mkdir-p $BAKPATH For dbname in $DBLIST Do Tlist= ' $MYCMD-e "show tables from $dbname;" |sed "1d" ' For Tname in $TLIST Do Mkdir-p $BAKPATH/$dbname $MYDUMP $dbname $tname |gzip > $BAKPATH/$dbname/${tname}_$ (Date +%f). sql.gz Done Done

After executing the script, you can see that the database is divided into a list of operations

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/8C/BC/wKiom1h14IGDU4JwAAD65ty_Z2I386.png "title=" Qq20170111153420.png "alt=" Wkiom1h14igdu4jwaad65ty_z2i386.png "/>

To execute a script prompt warning:

--warning:skipping The data of table mysql.event. Specify the--events option explicitly.

Reason: mysqldump default is not to back up the event table

Workaround 1:

Add the --events parameter after the mysqldump command

mydump= "Mysqldump-u$myuser-p$mypass-s $SOCKET-x-f-r --events"

Workaround 2:

in --events--ignore-table=mysql.events parameter

mydump= "Mysqldump-u$myuser-p$mypass-s $SOCKET-x-f-r  - -events--ignore-table=mysql.events "

Error Reference Solution:

http://www.linuxbrigade.com/warning-skipping-data-table-mysql-event/

http://bugs.mysql.com/bug.php?id=68376

This article is from the "M April Days" blog, please be sure to keep this source http://msiyuetian.blog.51cto.com/8637744/1891098

MySQL Sub-database sub-table backup script