网站编程语言,网站自己做的记者证,重庆知名网站建设公司,做mla网站一.数据库的分库分表#xff1f;
12张图把分库分表讲的明明白白#xff01;阿里面试#xff1a;我们为什么要分库分表https://mp.weixin.qq.com/s?__bizMzU0OTE4MzYzMwmid2247547792idx2sn91a10823ceab0cb9db26e22783343debchksmfbb1b26eccc63b784879…一.数据库的分库分表
12张图把分库分表讲的明明白白阿里面试我们为什么要分库分表https://mp.weixin.qq.com/s?__bizMzU0OTE4MzYzMwmid2247547792idx2sn91a10823ceab0cb9db26e22783343debchksmfbb1b26eccc63b784879f90540c8ab1731e635b30e5f4fd41de67f87a4fe055473039206f09dscene27 二.为什么需要分库分表 三.如何分库分表 四.配置分库分表的准备工作
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.sql4.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)
domysqldump -B $name ${BAK_DIR}/${name}_$(date %F).sql
done
#执行脚本
[rootserver ~]# bash creat_db.sh5.2.分表备份
[rootserver ~]# 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)
domysqldump jiaowu $name ${BAK_DIR}/jiaowu_${name}_$(date %F).sql
done5.3.分库分表备份
[rootserver ~]# cat create_db_tb.sh
#!/bin/bash
for name in $(mysql -N -e show databases | egrep -v information_schema|mysql|performance_schema)
doBAK_DIR/db[ -d ${BAK_DIR}/$name ] || mkdir -pv ${BAK_DIR}/$namemysqldump -B $name ${BAK_DIR}/${name}/${name}_$(date %F).sqlfor table in $(mysql -N -e show tables from $name)do mysqldump $name $table ${BAK_DIR}/${name}/${name}_${table}_$(date %F).sqldone
done
