shell脚本实现Mysql分库分表备份

一.数据库的分库分表？

12张图把分库分表讲的明明白白！阿里面试：我们为什么要分库分表https://mp.weixin.qq.com/s?__biz=MzU0OTE4MzYzMw==&mid=2247547792&idx=2&sn=91a10823ceab0cb9db26e22783343deb&chksm=fbb1b26eccc63b784879f90540c8ab1731e635b30e5f4fd41de67f87a4fe055473039206f09d&scene=27

二.为什么需要分库分表？

 三.如何分库分表？

四.配置分库分表的准备工作 

4.1.创建三个数据库：compay,jiaowu,goods

#创建“company”数据库
MariaDB [(none)]> create database company character set utf8;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> use company
Database changed
 
#在‘company’中创建“emp”表
MariaDB [company]> CREATE TABLE `emp`  (
    ->   `empno` int(4) NOT NULL,
    ->   `ename` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    ->   `job` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    ->   `mgr` int(4) NULL DEFAULT NULL,
    ->   `hiredate` date NOT NULL,
    ->   `sai` int(255) NOT NULL,
    ->   `comm` int(255) NULL DEFAULT NULL,
    ->   `deptno` int(2) NOT NULL,
    ->   PRIMARY KEY (`empno`) USING BTREE
    -> );
Query OK, 0 rows affected (0.015 sec)
 
#在‘emp’中插入数据
INSERT INTO `emp` VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20);
INSERT INTO `emp` VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO `emp` VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO `emp` VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO `emp` VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO `emp` VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO `emp` VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO `emp` VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO `emp` VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO `emp` VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO `emp` VALUES (1011, '周泰', '文员', 1006, '2007-05-23', 11000, NULL, 20);
INSERT INTO `emp` VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO `emp` VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20);
INSERT INTO `emp` VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO `emp` VALUES (1015, '张三', '保洁员', 1001, '2013-05-01', 80000, 50000, 50);
 
#在‘company’数据库中建立‘dept’表
MariaDB [company]> CREATE TABLE `dept` (
    -> `deptno`  int NOT NULL ,
    -> `dname`  char(9) NOT NULL ,
    -> `loc`  char(6) NOT NULL 
    -> );
Query OK, 0 rows affected (0.055 sec)
 
#在‘dept’中插入数据
MariaDB [company]> INSERT INTO `dept` VALUES (10, '教研部', '北京');
Query OK, 1 row affected (0.003 sec)
 
MariaDB [company]> INSERT INTO `dept` VALUES (20, '学工部', '上海');
Query OK, 1 row affected (0.001 sec)
 
MariaDB [company]> INSERT INTO `dept` VALUES (30, '销售部', '广州');
Query OK, 1 row affected (0.003 sec)
 
MariaDB [company]> INSERT INTO `dept` VALUES (40, '财务部', '武汉');
Query OK, 1 row affected (0.002 sec)
 
 
#上传jiaowu数据库及表
MariaDB [jiaowu]> source /root/jiaowu.sql
 
#上传goods数据库及表
MariaDB [jiaowu]> source /root/goods.sql

4.2.查看数据库及表

 

-e 后面跟上要执行的SQL语句

-N 参数是不显示表头  

五.分库分表备份

mysqldump命令备份数据的原理：就是把数据从MySQL库里以逻辑的sql语句形式直接输出或者生成备份的文件的过程。

-B： 用于备份多个数据库

grep的主要作用是根据关键字检索内容，egrep是grep的拓展，egrep包含grep所有的功能

-v 取反（显示不包含关键词的行）

在bash中，$( )与` `（反引号）都是用来作命令替换的

一般情况下，$var与${var}是没有区别的，但是用${ }会比较精确的界定变量名称的范围

5.1.分库备份

#编写脚本
#!/bin/bash
BAK_DIR=/db
[ -d ${BAK_DIR} ] || mkdir ${BAK_DIR} -pv
for name in $(mysql -N -e "show databases" | egrep -v "information_schema|mysql|performance_schema")
do
	mysqldump -B $name > ${BAK_DIR}/${name}_$(date +%F).sql
done
#执行脚本
[root@server ~]# bash creat_db.sh

 

5.2.分表备份

[root@server ~]# cat create_table.sh
#!/bin/bash
BAK_DIR=/db
[ -d ${BAK_DIR} ] || mkdir ${BAK_DIR} -pv
for name in $(mysql -N -e "show tables from jiaowu")
do
	mysqldump jiaowu $name > ${BAK_DIR}/jiaowu_${name}_$(date +%F).sql
done

5.3.分库分表备份

[root@server ~]# cat create_db_tb.sh
#!/bin/bash
for name in $(mysql -N -e "show databases" | egrep -v "information_schema|mysql|performance_schema")
do
	BAK_DIR=/db
	[ -d ${BAK_DIR}/$name ] || mkdir -pv ${BAK_DIR}/$name
	mysqldump -B $name > ${BAK_DIR}/${name}/${name}_$(date +%F).sql
	for table in $(mysql -N -e "show tables from $name")
	do 
		mysqldump $name $table > ${BAK_DIR}/${name}/${name}_${table}_$(date +%F).sql
	done
done