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

Mysql分库分表备份方法实现

2022年11月09日 21:11:49数据库8

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;"

Mysql分库分表备份方法实现 _ JavaClub全栈架构师技术笔记

 

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

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

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

Mysql分库分表备份方法实现 _ JavaClub全栈架构师技术笔记

 (4)mysqldump命令多数据库备份

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

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

Mysql分库分表备份方法实现 _ JavaClub全栈架构师技术笔记

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

Mysql分库分表备份方法实现 _ JavaClub全栈架构师技术笔记

(5)mysqldump压缩备份

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

查看:

Mysql分库分表备份方法实现 _ JavaClub全栈架构师技术笔记

 

(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;"

Mysql分库分表备份方法实现 _ JavaClub全栈架构师技术笔记

(7)mysql备份表结构

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

查看:

Mysql分库分表备份方法实现 _ JavaClub全栈架构师技术笔记

 (8)mysql备份表数据

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

查看结果如下提所示:

Mysql分库分表备份方法实现 _ JavaClub全栈架构师技术笔记

 

3.mysql分库备份shell脚本

具体shel脚本如下:

#!/bin/bash

DATE=$(date '+%Y%m%d')

HOST=10.9.1.43

USER=root

PASS=123456

BACKUP_DIR=/home/centos/db_backup

DB_LIST=$(mysql -h$Host -u$USER -p$PASS -s -e "show databases;" 2>/dev/null |egrep "dandan_test|oozie")

if [ ! -d $BACKUP_DIR ]

then

mkdir -p "$BACKUP_DIR"

fi

for DB in $DB_LIST; do

    BACKUP_NAME=$BACKUP_DIR/${DB}_${DATE}.sql

mysqldump -h$HOST -u$USER -p$PASS -B $DB > $BACKUP_NAME 2>/dev/null

    if [ $? != 0 ]; then

        echo "$BACKUP_NAME 备份失败!"

    fi

done

4 mysql分表备份shell脚本

具体shell脚本如下:

#!/bin/bash

DATE=$(date '+%Y%m%d')

HOST=10.9.1.43

USER=root

PASS=123456

BACKUP_DIR=/home/centos/db_backup

DB_LIST=$(mysql -h$HOST -u$USER-p$PASS -s -e "show databases;" 2>/dev/null |egrep "dandan_test|oozie")

for DB in $DB_LIST; do

    BACKUP_DB_DIR=$BACKUP_DIR/${DB}_${DATE}

    [ ! -d $BACKUP_DB_DIR ] && mkdir -p $BACKUP_DB_DIR &>/dev/null

    TABLE_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "use $DB;show tables;" 2>/dev/null)

    for TABLE in $TABLE_LIST; do

        BACKUP_NAME=$BACKUP_DB_DIR/${TABLE}.sql

        if ! mysqldump -h$HOST -u$USER -p$PASS $DB $TABLE > $BACKUP_NAME 2>/dev/null; then

            echo "$BACKUP_NAME 备份失败!"

        fi

    done

done

5.小结

   本文主要阐述了Mysql分库分表的备份方法,并对mysqldump工具具体操作进行了分析研究,给出了具体备份的shell脚本,可以通过crontab定时任务按照项目需求进行备份。

  本文要点:

  • (1)连接mysql的指令,前提需要安装mysql客户端。
  • (2)mysqldump指令,注意分库、分表指定的参数,及恢复的方法。
  • (3)mysql分库的shell脚本的编写
  • (4)mysql分表的shell脚本编写(建议编写时,基本的指令需要先在linux中实验成功)

作者:莫叫石榴姐
来源链接:https://blog.csdn.net/godlovedaniel/article/details/104525006

版权声明:
1、JavaClub(https://www.javaclub.cn)以学习交流为目的,由作者投稿、网友推荐和小编整理收藏优秀的IT技术及相关内容,包括但不限于文字、图片、音频、视频、软件、程序等,其均来自互联网,本站不享有版权,版权归原作者所有。

2、本站提供的内容仅用于个人学习、研究或欣赏,以及其他非商业性或非盈利性用途,但同时应遵守著作权法及其他相关法律的规定,不得侵犯相关权利人及本网站的合法权利。
3、本网站内容原作者如不愿意在本网站刊登内容,请及时通知本站(javaclubcn@163.com),我们将第一时间核实后及时予以删除。


本文链接:https://www.javaclub.cn/database/69237.html

分享给朋友:

“Mysql分库分表备份方法实现” 的相关文章

MySQL面试有这一篇就够了

MySQL面试有这一篇就够了

MySQL面试常见知识点 1、 MySQL常用的存储引擎有什么?它们有什么区别? InnoDB InnoDB是MySQL的默认存储引擎,支持事务、行锁和外键等操作。 MyISAM MyISAM是M...

mysql|mysql删除重复数据,一条sql就搞定

SQL 逻辑: 首先找出所有重复的sku_id 在找出这些sku_id中关联的主键id最大的一条记录(用来保留,不删除) 两个sql合并 就可以筛选出来最终需要删除的id select id from sku_and...

Mysql 查询结果赋值到变量

1. 单个赋值 - 使用into SELECT `name` INTO @name FROM table WHERE `id`=1 ; 2.多个赋值 - 直接赋值 SELECT @name:=`name`, @phone:=`phone` FROM t...

mysql查询最后一条记录

首先要确定什么是最后一条。 是编辑时间最新的为最后一条,还是某个字段数字最大的未最后一条。 比如以时间最大为最后一条,则将符合条件的资料都筛选出来,再按时间排序,再取一笔资料。 SQL如下: select a,b from table whe...

mysql字段值中带空格、mysql查询参数带空格的查询方法

问题描述 当我们在使用mysql查询的时候,如果数据库中的这个字段的值含有空格(字符串内部,非首尾),或者我们查询的参数中间有空格,而字段值中没有空格。那么我们很有可能就什么都查不到。 假如有下面的一张表content:...

如何查看MySQL的版本?

查看MySQL的版本,主要有以下几个方法: 1. 没有连接到MySQL服务器,就想查看MySQL的版本。打开cmd,切换至mysql的bin目录,运行下面的命令即可:...

mysql查询重复的

mysql查询重复的

表全部数据 ------------------- 1 查询people_no重复的记录 select * from people where people_no in (select people_no from people...

Mysql查询用户最后一次登陆时间

Mysql查询用户最后一次登陆时间

类似的问题还有,学生得分最高的课程 数据库如下: 第一种方案: SELECT sid,max(add_time) from test GROUP BY sid 第二种方案 S...

MySQL查询指定行的记录

1、查询第一行记录: select * from table limit 1; 2、查询第n行到第m行记录,或者第n行 -- 2.1 查询连续的多行记录(第n~m行) select * from table1 li...

mysql版本查询

SHOW VARIABLES LIKE "%version%"; 参考: 2.2 Determining Your Current MySQL Version 转载于:https://my.oschina.net/fxt...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。