一.数据库的分库分表?
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