一.數(shù)據(jù)庫(kù)的分庫(kù)分表？

12張圖把分庫(kù)分表講的明明白白！阿里面試：我們?yōu)槭裁匆謳?kù)分表https://mp.weixin.qq.com/s?__biz=MzU0OTE4MzYzMw==&mid=2247547792&idx=2&sn=91a10823ceab0cb9db26e22783343deb&chksm=fbb1b26eccc63b784879f90540c8ab1731e635b30e5f4fd41de67f87a4fe055473039206f09d&scene=27

二.為什么需要分庫(kù)分表？

?三.如何分庫(kù)分表？

四.配置分庫(kù)分表的準(zhǔn)備工作?

4.1.創(chuàng)建三個(gè)數(shù)據(jù)庫(kù)：compay,jiaowu,goods

#創(chuàng)建“company”數(shù)據(jù)庫(kù)
MariaDB [(none)]> create database company character set utf8;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> use company
Database changed

#在‘company’中創(chuàng)建“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’中插入數(shù)據(jù)
INSERT INTO `emp` VALUES (1001, '甘寧', '文員', 1013, '2000-12-17', 8000, NULL, 20);
INSERT INTO `emp` VALUES (1002, '黛綺絲', '銷(xiāo)售員', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO `emp` VALUES (1003, '殷天正', '銷(xiāo)售員', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO `emp` VALUES (1004, '劉備', '經(jīng)理', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO `emp` VALUES (1005, '謝遜', '銷(xiāo)售員', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO `emp` VALUES (1006, '關(guān)羽', '經(jīng)理', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO `emp` VALUES (1007, '張飛', '經(jīng)理', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO `emp` VALUES (1008, '諸葛亮', '分析師', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO `emp` VALUES (1009, '曾阿牛', '董事長(zhǎng)', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO `emp` VALUES (1010, '韋一笑', '銷(xiāo)售員', 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, '龐統(tǒng)', '分析師', 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’數(shù)據(jù)庫(kù)中建立‘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’中插入數(shù)據(jù)
MariaDB [company]> INSERT INTO `dept` VALUES (10, '教研部', '北京');
Query OK, 1 row affected (0.003 sec)

MariaDB [company]> INSERT INTO `dept` VALUES (20, '學(xué)工部', '上海');
Query OK, 1 row affected (0.001 sec)

MariaDB [company]> INSERT INTO `dept` VALUES (30, '銷(xiāo)售部', '廣州');
Query OK, 1 row affected (0.003 sec)

MariaDB [company]> INSERT INTO `dept` VALUES (40, '財(cái)務(wù)部', '武漢');
Query OK, 1 row affected (0.002 sec)


#上傳jiaowu數(shù)據(jù)庫(kù)及表
MariaDB [jiaowu]> source /root/jiaowu.sql

#上傳goods數(shù)據(jù)庫(kù)及表
MariaDB [jiaowu]> source /root/goods.sql

4.2.查看數(shù)據(jù)庫(kù)及表

?

-e 后面跟上要執(zhí)行的SQL語(yǔ)句

-N 參數(shù)是不顯示表頭 ?

五.分庫(kù)分表備份

mysqldump命令備份數(shù)據(jù)的原理：就是把數(shù)據(jù)從MySQL庫(kù)里以邏輯的sql語(yǔ)句形式直接輸出或者生成備份的文件的過(guò)程。

-B： 用于備份多個(gè)數(shù)據(jù)庫(kù)

grep的主要作用是根據(jù)關(guān)鍵字檢索內(nèi)容，egrep是grep的拓展，egrep包含grep所有的功能

-v 取反（顯示不包含關(guān)鍵詞的行）

在bash中，$( )與` `（反引號(hào)）都是用來(lái)作命令替換的

一般情況下，$var與${var}是沒(méi)有區(qū)別的，但是用${ }會(huì)比較精確的界定變量名稱(chēng)的范圍

5.1.分庫(kù)備份

#編寫(xiě)腳本
#!/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
#執(zhí)行腳本
[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

文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-836327.html

5.3.分庫(kù)分表備份

[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

到了這里，關(guān)于shell腳本實(shí)現(xiàn)Mysql分庫(kù)分表備份的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！