How to implement Table-level backup in MySQL Database

This article shows you how to achieve table-level backup in MySQL database, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Backup script

#! / bin/bash # this is mysqldump auto table backup script # Copyright by hwb backup_path= "/ pasdata/backup" user= "root" passwd= "xx.xx.x.x dbname=" pas_prod "host=" localhost "tablename=" t_att_overtime_application "today= `date +"% Y%m%d-%H%M%S "`sqlname=$tablename$today.sql if [- e / home/scripts/log_function.sh] then source / home/scripts/log_function.sh else echo-e"\ 033 [41 37m / home/scripts/log_function.sh is not exist. 033 [0m "exit 1 fi backup () {log_correct" starts the backup script and deletes the 14-day expired backup "# backup mysqldump-h$host-u$user-p$passwd $dbname-- tables $tablename > $backup_path/$sqlname} delete () {# delete expired 14 days find $backup_path-mtime + 14-type f-name'* .sql'- exec rm-f {}\;} size () {cd $backup_path dd= `du-sh $sqlname` if [- s. / $sqlname] Then log_correct'fi table backup normal 'log_correct $dd else log_error' rest table backup failed'fi} backup delete size

The core command is as follows: mysqldump-h$host-u$user-p$passwd $dbname-- tables $tablename > $backup_path/$sqlname

Scheduled task

The scheduled tasks are as follows: 30 minutes every 1 hour.

300-23 * sh / home/scripts/table_backup.sh

Appendix: commands about mysqldump Export Table

1. Export the data of the specified table

Mysqldump-t database-u username-ppassword-- tables table_name1 table_name2 table_name3 > table.sql

2. Export the structure of the specified table

Mysqldump-d database-u username-ppassword-- tables table_name1 table_name2 table_name3 > table.sql

3. Export the data and structure of the table.

Mysqldump database-u username-ppassword-- tables table_name1 table_name2 table_name3 > table.sql

4. With the exception of some tables, other tables need to be exported

Mysqldump-h IP-u username-ppassword-- default-character-set=utf8-- database database_name-- ignore-table=database_name.table_name1-- ignore-table=database_name.table_name2-- ignore-table=database_name.table_name3 > table.sql the above content is how to implement table-level backup in MySQL database. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.