How to exclude the specified database during MySQL backup.

How to exclude the specified database during MySQL backup.

When you use the mysqldump command to back up data, -- all-databases can back up all databases. You can use ignore-table to exclude the specified table. However, mysqldump does not have any parameter to exclude the database.

If the number of databases to be backed up is small, you can use mysqldump-uroot-p123456 -- databases db1 db2 db3> mysqldump. SQL to back up data.

However, if there are dozens of databases, it would be very tiring and low. There are still some solutions, as shown below:

[The following shows the root user name and password of mysql 123456]

Mysql-uroot-p123456-e 'show databases; '| grep-E-v "Database | information_schema | mysql | test" | xargs mysqldump-uroot-p123456 -- databases> mysqldump1. SQL

However, unfortunately, an error is returned when the backup is executed on mysql5.5.

I checked the information and found that it was caused by the performance_schema database of mysql after mysql 5.5. Skip this database during backup. You can choose either of the following two methods:

1. mysql-uroot-p123456-e 'show databases; '| grep-E-v "Database | information_schema | mysql | test" | xargs mysqldump-uroot-p123456 -- skip-lock-tables performance_schema -- databases> 2. SQL

2. mysql-uroot-p123456-e 'show databases; '| grep-E-v "Database | information_schema | mysql | performance_schema" | xargs mysqldump-uroot-p123456 -- databases> 3. SQL

I prefer the second method.

The following is a script for backing up mysql. It is executed once every night. It is easier to write, and the statement is not optimized.

#!/bin/bash# Description: backup mysql data# Author: leeFILE="mysql_`date +%F`"BACKDIR="/mysqlbackup"CONF_FILE="/etc/my.cnf"mysql -uroot -e 'show databases'|egrep -v "Database|information_schema|performance_schema" \ |xargs mysqldump -uroot --databases > $BACKDIR/$FILE.sql && cp $CONF_FILE $BACKDIR/$FILE.cnf \&& echo -e "Machine: Oracle\nIP: 172.16.10.12\nStatus: MySQL is backup complete" | \ mail -s "MySQL BackUP Complete" lee@126.com

Code to see more clearly.

The following describes how to exclude a specified table when MySQL uses mysqldump to back up a database.

#! /Bin/shfor j in 'mysql-uroot-e "USE spservice; show tables" | grep-v Tables 'do case $ j in mo_log | mt_log | report_info ):;; *) mysqldump-uroot -- default-character-set = gbk -- opt spservice $ j >>$ j. SQL esacdone> the table name is not fixed #! /Bin/shfor j in 'mysql-uroot-e "USE spservice; show tables" | grep-v Tables 'do for I in "$ @" do if [$ j! = $ I]; then mysqldump-uroot -- default-character-set = gbk -- opt spservice $ j >>$ j. SQL fi donedone >> more concise method #! /Bin/shfor j in 'mysql-uroot-e "USE spservice; show tables "| grep-v Tables 'do echo $ @ | grep-wq" \ <$ j \> "if [$? -Ne 0]; then mysqldump-uroot -- default-character-set = gbk -- opt spservice $ j >>$ j. SQL fidone> usage sh exclude. sh mo_log mt_log report_log # defines a shell array tables = (mo_log mt_log report_log) sh exclude. sh $ {tables [@]}

Articles you may be interested in:

• PHP backup/restoration of MySQL database code
• Mysql users do not have the lock tables permission when backing up databases.
• How to back up and restore a database in a mysql ndb Cluster
• Summary of Common commands for MySQL database backup and Restoration
• How to use mysql commands to restore a Database Backup
• In linux, Vps automatically backs up web and mysql database scripts
• How to implement one-way master-slave backup for MySql Databases on Windows Server
• Summary of statements used to back up and restore a MySQL database in Command Line Mode
• Automatic MySql database backup in Window System