当前位置：首页 >数据库 > Mysql分库分表备份方法实现

Mysql分库分表备份方法实现

1.mysqldump工具介绍

mysqldump命令是MySQL数据库自带的一个备份命令，它支持数据库全备也可以指定库进行备份，它备份的文件以文件形式备份，且文件内容都是SQL语句形式的存在。

2.基本命令的使用

（1）mysqldump语法格式

mysqldump [options] –u 用户名 -p 密码数据库名 > 备份的文件

（2）mysqldump[options]参数列表如下表所示：

参数	意义
-A	备份所有数据库
-B	表示指定备份多个库，同时执行了use db和create databases db命令
-F	刷新binlog日志
-d	只备份表结构
-t	只备份表数据
-l	指定表上锁，让用户不能访问写入数据库指定表的数据
-x	所有表上锁
--master-data --master-data=1 --master-data=2	（1）增加binlog日志文件名及对应的位置记录点。（2）不注释位置记录点信息，从库会使用到. （3）注释位置记录点信息
--single-transaction	应答innodb事务数据库备份
--events	转储事件，把events警告事件取消显示
--flush-privileges	刷新数据库更新
mysqldump -uroot -p'123456' --all-databases \ --flush-privileges –-lock-all-tables \ --master-data=1 --flush-logs --triggers --routines --events \ --hex-blob > /opt/mysql_bak.sql	Mysql全备数据命令（适合MyISAM引擎）
mysqldump -uroot -p'123456' --all-databases \ --flush-privileges --single-transaction \ --master-data=1 --flush-logs --triggers --routines --events \ --hex-blob > /opt/mysql_bak.sql	Mysql全备数据命令（适合InnoDB引擎）

（3）连接mysql命令

[root@bigdata3 ~]# mysql -h10.9.1.43 -uroot -p123456 -e "show databases;"

去掉周围边框使用-s命令

[root@bigdata3 ~]# mysql -h10.9.1.43 -uroot -p123456@3306 -s -e "show databases;"

命令执行结果如下图所示：

（4）mysqldump命令多数据库备份

[root@bigdata3 ~]# mysqldump -h10.9.1.43 -uroot -p123456 -B dandan_test oozie > /home/centos/mysql_back.bak

cd 到/home/centos目录下查看生成了该文件

进入该文件中查看，备份的数据全部以sql命令的形式生成，如下图所示。

（5）mysqldump压缩备份

[root@bigdata3 ~]# mysqldump -h10.9.1.43 -uroot -p123456 -B dandan_test | gzip > /home/centos/mysql_back_B.sql.gz

查看：

（6）数据库备份恢复

删掉dandan_test:drop database dandan_test;

非压缩恢复：

[root@bigdata3 centos]# mysql -h10.9.1.43 -uroot -p123456 </home/centos/mysql_back.bak

压缩恢复：

[root@bigdata3 centos]# gunzip < /home/centos/mysql_back_B.sql.gz | mysql -h10.9.1.43 -uroot -p123456

验证：

mysql -h10.9.1.43 -uroot -p123456 -e "use dandan_test;select * from tb_score1;"

[root@bigdata3 centos]# mysql -h10.9.1.43 -uroot -p123456 -e "use dandan_test;select * from tb_score1;"

(7)mysql备份表结构

[root@bigdata3 centos]# mysqldump -h10.9.1.43 -uroot -p123456 -d dandan_test >/home/centos/dandan_test_back.bak

查看：

（8）mysql备份表数据

[root@bigdata3 ~]# mysqldump -h10.9.1.43 -uroot -p123456 -t dandan_test >/home/centos/dandan_test_back.bak

查看结果如下提所示：

3.mysql分库备份shell脚本

具体shel脚本如下：

#!/bin/bashDATE=$(date '+%Y%m%d')HOST=10.9.1.43USER=rootPASS=123456BACKUP_DIR=/home/centos/db_backupDB_LIST=$(mysql -h$Host -u$USER -p$PASS -s -e "show databases;" 2>/dev/null |egrep "dandan_test|oozie")if [ ! -d $BACKUP_DIR ]thenmkdir -p "$BACKUP_DIR"fifor DB in $DB_LIST; doBACKUP_NAME=$BACKUP_DIR/${DB}_${DATE}.sqlmysqldump -h$HOST -u$USER -p$PASS -B $DB > $BACKUP_NAME 2>/dev/nullif [ $? != 0 ]; thenecho "$BACKUP_NAME 备份失败!"fidone

4 mysql分表备份shell脚本

具体shell脚本如下：

#!/bin/bashDATE=$(date '+%Y%m%d')HOST=10.9.1.43USER=rootPASS=123456BACKUP_DIR=/home/centos/db_backupDB_LIST=$(mysql -h$HOST -u$USER-p$PASS -s -e "show databases;" 2>/dev/null |egrep "dandan_test|oozie")for DB in $DB_LIST; doBACKUP_DB_DIR=$BACKUP_DIR/${DB}_${DATE}[ ! -d $BACKUP_DB_DIR ] && mkdir -p $BACKUP_DB_DIR &>/dev/nullTABLE_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "use $DB;show tables;" 2>/dev/null)for TABLE in $TABLE_LIST; doBACKUP_NAME=$BACKUP_DB_DIR/${TABLE}.sqlif ! mysqldump -h$HOST -u$USER -p$PASS $DB $TABLE > $BACKUP_NAME 2>/dev/null; thenecho "$BACKUP_NAME 备份失败!"fidonedone