Mysqldump Multiple db

Background

The machine under the frame, need to migrate the source db to the new machine, DB_XX has a fixed prefix, according to the segment number of 100 libraries, need to be 100

It is now necessary to migrate these 100 db to the new machine.

Because you have previously used mysqldump to back up databases, you also want to use the Mysqldump method.

Introduction to 1.mysqldump Backup

mysqldump 备份少数数据库：mysqldump -uusername -ppassword  -hxxxxx --databases db1 db2 > db1db2.sqlmysqldump 备份少量数据库时，可以通过--databases 将要备份的db 列出来, 进行备份，但 如果使用这种方法，将会使 --databases 后的 列表很长, 而且 很容易漏写( 除非你用脚本罗列 拼接)

Introduction to 2.source Recovery

mysqldump出的数据,可以通过  source命令来恢复，使用方法:mysql> source db1db2.sql;该方法要求进入mysql , 当然还有其他方法。

3. Think about using scripts to bulk backup

要想使用脚本，需要解决下面的问题:1.需要备份的db名字列表2.能在shell管理台执行备份操作

3.1 Get DB list

show databases like ‘db%’ 来获取 需要备份 的 db列表，并导出到文件:mysql -hxxx -uxxx -pxxx -e "show databases like ‘db%‘"  > db.txt

3.2 Shell side perform backup

mysql -hxxx -uxxx -pxxx -e -e 参数能够在shell命令行 执行 一些 mysql语句，而不用进入mysql 客户端.

4. Script Backup

该脚本备份方法是,对每个db, mysqldump 后, 立即 导入到 目的db

import sys import commandswith open(‘db.txt‘, ‘r‘) as f:    for line in f.readlines():        line = line.strip()        if(line == ""):            continue;        print "dump: %s ..." % line        cmd = ‘mysqldump -hxxxx -uxxxx -pxxx -B ‘ + line + ‘ > ‘ + line + ‘.sql‘        (status, result) = commands.getstatusoutput(cmd)        print "dump: %s ... return %d:%s" % (line,status,result)        if status != 0:            print "dump: %s ... error " % line            exit(-1)        sourc_cmd = "mysql -hdstip -udstuser -pdstcode -e ‘source " + line + ".sql‘"        (status, result) = commands.getstatusoutput(sourc_cmd)        print "source: %s, return %d:%s" % (sourc_cmd, status, result)        if status != 0:            print "source: %s ... error " % line            exit(-1)print "end"

Mysqldump Multiple db