Shulou(Shulou.com)05/31 Report--

This article mainly shows you "mysql how to achieve by-table backup script", the content is easy to understand, well-organized, hope to help you solve your doubts, the following let the editor lead you to study and learn "mysql how to achieve by-table backup script" this article.

There are two versions, one in linux and one in windows. I don't know how to python, but it's much easier to use python. Python can be cross-platform.

The version of the linux platform comes from your second thought:

= = LINUX Shell script =

# more / data/mysqldata/scripts/mysql_full_backup_by_table.sh

#! / bin/sh

# created by junsansi 20130505

Source / data/mysqldata/scripts/mysql_env_ini

DATA_PATH=/data/mysqldata/backup/mysql_full_bytables

DATA_FILE=$ {DATA_PATH} / dbfullbak_by_tables_ `date +% F`.sql.gz

LOG_FILE=$ {DATA_PATH} / dbfullbak_ `date +% F`.log

MYSQL_PATH=/usr/local/mysql/bin

MYSQL_CMD= "${MYSQL_PATH} / mysql-u ${MYSQL_USER}-p$ {MYSQL_PASS}-S / data/mysqldata/$ {HOST_PORT} / mysql.sock"

MYSQL_DUMP= "${MYSQL_PATH} / mysqldump-u ${MYSQL_USER}-p$ {MYSQL_PASS}-S / data/mysqldata/$ {HOST_PORT} / mysql.sock-- single-transaction-l"

Echo > $LOG_FILE

Echo-e "= Jobs started at `date+%F''% T''% w` =\ n" > $LOG_FILE

For dbs in `$ {MYSQL_CMD}-e "show databases" | sed 'ld' | egrep-v "information_schema | mysql | performance_schema" `

Do

Mkdir-p ${DATA_PATH} / ${dbs}

Echo-e "* Database:$ {dbs} Backup Start_Time: `date +% F'% T'% w` *\ n" > ${LOG_FILE}

For tbls in `${MYSQL_CMD}-D ${dbs}-e "show tables" | sed 'ld'`

Do

Echo-e "# Begin$ {dbs}. ${tbls} Dumpstarted Startasking time: `date +% F'% T`" > > ${LOG_FILE}

Echo-e "Execute Command:$ {MYSQL_DUMP}-- tables ${dbs} ${tbls} | gzip > ${DATA_PATH} / ${dbs} / ${dbs} _ ${tbls} .sql.gz" > > ${LOG_FILE}

${MYSQL_DUMP}-- tables ${dbs} ${tbls} | gzip > ${DATA_PATH} / ${dbs} / ${dbs} _ ${tbls} .sql.gz

Echo-e "# End$ {dbs}. ${tbls} Dumptable stopping time: `date +% F'% T`\ n" > ${LOG_FILE}

Echo > > ${LOG_FILE}

Done

Echo-e "* Database:$ {dbs} Backup Stop_Time: `date +% F'% T'% w` *\ n" > ${LOG_FILE}

Echo-e "* Backup file size: `du-sh ${DATA_PATH} / ${dbs} `*\ n" > ${LOG_FILE}

Done

Echo-e "- Find expired backup and delete those files -" > ${LOG_FILE}

For tfile in $(/ usr/bin/find ${DATA_PATH} /-mtime + 6)

Do

If [- d $tfile]: then

Rmdir $tfile

Elif [- f $tfile]: then

Rm-f $tfile

Fi

Echo-e "- Delete file: $tfile -" > > ${LOG_FILE}

Done

Echo-e "\ nthanks thanks = Jobs ended at `date +% F'% T''% w` =\ n" > > $LOG_FILE

= = Windows Bat script =

Prompt $P$G$T$G

If% time:~0,2% lss 10 (

Set ymd=%date:~0,4%-%date:~5,2%-%date:~8,2%_0%time:~1,1%

) else (

Set ymd=%date:~0,4%-%date:~5,2%-%date:~8,2%_%time:~0,2%

)

Set date=%date:~0,4%-%date:~5,2%-%date:~8,2%

Set v_backup_file_name=xedk_%ymd%_bak.sql

Set v_backup_error_name=xedk_%ymd%_error.txt

Cd D:\ DBbak\ INPUT

D:

Md date%

Rem pause

Cd D:\ mysql-5.6.19-winx64\ bin

Mysql-uxedk-P 3308-p "admin"-N mysqldumpsc_%date%.bat

Rem pause

Cd D:\ DBbak\ INPUT

Move D:\ Quarkp2p\ mysql-5.6.19-winx64\ bin\ mysqldumpsc_%date%.bat D:\ DBbak\ INPUT\% date%

Rem pause

Cd D:\ mysql-5.6.19-winx64\ bin

Call\ DBbak\ INPUT\% date%\ mysqldumpsc_%date%.bat

Rem pause

Cd D:\ INPUT

Move D:\ DBbak\ INPUT\ * .sql D:\ DBbak\ INPUT\% date%\

Rem pause

The content in D:\ backupSc\ backTabSc.sql is:

SELECT CONCAT ("mysqldump-uroot-h292.168.30.4-p\" P\ # 92\ ^ z\!\ "- P 3308-tables", "xedk", table_name, "> D:", "\", "DBbak", "\", "INPUT", "\", "Table_", table_name, ".sql") FROM information_schema.tables where table_schema='xedk'

=

Summary: in fact, the idea of the two versions is the same, that is, read out the names of all tables, and then use mysqldump to export by the name of each table.

Other:

1. Under windows, mysql can output the execution results of sql statements to the specified file "mysqldumpsc.txt" by adding into outfile 'mysqldumpsc.txt'; when executing sql statements, but the location of this file is under the datadir of mysql by default. If you specify the reserved location of the file, you should pay attention to whether mysql has read and write access to its directory

2. Under windows, if you use the following statement: mysql-uxedk-P 3308-p "admin"-N mysqldumpsc2.txt will output the execution result to mysqldumpsc2.txt, the default location of this file is under the execution path of mysql.

3. When mysql-uxedk-P 3308-p "admin"-e "show databases;" is used to execute the sql statement directly in the command interface, if there is a function in the sql statement, it cannot be recognized.

The above is all the contents of the article "how to back up the script by table in mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.