https://cloud.tencent.com/edu/learning/course-3277-57186免密配置cd tdsql_install/scripts/vim ip_passwd_listIP 密码sh nokey.shfor ip in cat ip_passwd_list | awk {print $1}; do ssh $ip echo $ip: ssh success; done0、安装unzip tdsql_10.3.22.6.0.x86_64.zipcd /root/tdsql_10.3.22.6.0/tdsql_install/scripts./start_deploy.shCPU:请填写CPU实际逻辑核数内存:请填写实际内存绝对值的75%(经验值)磁盘:请设置为实际磁盘空间(RAID后)绝对值的75%~90%。其中建议数据盘:日志盘的比例为3:1(经验值)1、扩容赤兔登录控制机/root/tdsql_10.3.22.6.0/tdsql_installvim tdsql_add_hosts[tdsql_newchitu]tdsql_newchitu1 ansible_ssh_host192.168.0.3 ---需要填写扩容的赤兔机器ipcd /root/tdsql_10.3.22.6.0/tdsql_installansible-playbook -i tdsql_add_hosts playbooks/tdsql_add_chitu.yml在原赤兔节点执行scp -p /data/website/tdsqlpcloud/www/config/database.php 192.168.0.x:/data/website/tdsqlpcloud/www/config/scp -p /data/website/tdsqlpcloud/www/config/install.lock 192.168.0.x:/data/website/tdsqlpcloud/www/config/在新赤兔节点执行chown -R nginx:nginx /data/website新赤兔服务地址http://192.168.0.x/tdsqlpcloud2、升级上传升级工具包/data目录tdsql_10.3.22.6.0_WEB_UPGRADE.x86_64.ziptdsql_10.3.22.6.0_WEB_UPGRADE解压unzip tdsql_10.3.22.6.0_WEB_UPGRADE.x86_64.zipcd /data/tdsql_10.3.22.6.0_WEB_UPGRADE/tdsql_install启动升级程序bash scripts/upgrade.sh /root/tdsql_10.3.22.6.0/tdsql_installyhttp://192.168.0.154:8081admin/Huawei12#$% 赤兔的账号密码软件包管理上传软件包tdsql-mysql5717-22.6.0.tl2.x86_64.tgztdsql-oss-22.6.3.tl2.x86_64.tgz组件升级同步组件状态先升级DB公共包在升级OSS最后升级实例实例 升级 存储节点\s 检查数据库版本3、物理备份hdfs备份恢复9870非分布式表物理回档会创建一个新的表。启动hdfssu - tdsqlhdfs --daemon start journalnode2在hdfs1和hdfs2上启动namenodehdfs --daemon start namenode3在hdfs1和hdfs2上启动zkfchdfs --daemon start zkfc4在所有hdfs机器上启动datanodehdfs --daemon start datanodehdfs dfsadmin -report4、主备切换5、赤兔修改参数select max_connections;6、创建分布式实例7、创建非分布式实力8、查询实例9、安装10、DCN同步11、闪回12、导数据分析优化 必考sql优化SELECT u.username, u.phone FROM orders o JOIN users u ON o.user_id u.user_id WHERE o.user_id用户ID;建议增加索引alter table testdb.orders add index idx_user_id(user_id);增加索引实时诊断冗余索引alter table testdb.orders add index idx_id_user_id(id,user_id);死锁13、赤兔账号创建添加用户、设置权限、添加集群管理人、添加实例管理人14、注释透传功能-- set数量、名称、ip、hash_range范围、主备节点等/*proxy*/show status;-- 一致性读(consistent_read)是否开启、日志相关等/*proxy*/show config;/*sets:set_1*//*sets:set_1,set_2*/ set名字可以通过/*proxy*/show status查询/*sets:allsets*/15、#创建单表create table single_t(a int,b int,PRIMARY KEY(a));16、#创建广播表create table global_t(a int,b int,PRIMARY KEY(a)) shardkeynoshardkey_allset;17、#创建一级HASHcreate table test1(a int,b int,c char(20),primary key(a)) shardkeya;18、#创建一级range分区create table t1(a int key ,b int) tdsql_distributed by range(a) (s1 values less than(100),s2 values less than(200));19、#创建一级分表LIST分区create table t2(a int key ,b int) tdsql_distributed by list(a) (s1 values in(1,2),s2 values in (3,4));20、#创建二级RANGE分区CREATE TABLE employees_int(id INT key NOT NULL,fname VARCHAR(30),lname VARCHAR(30),hired date,Separated DATE NOT NULL DEFAULT9999-12-31,job_code INT,store_id INT)shardkeyidPARTITION BY RANGE(year(hired))(PARTITION P0 VALUES LESS THAN(1991),PARTITION P1 VALUES LESS THAN(1996),PARTITION P2 VALUES LESS THAN(2001));21、#创建二级LIST分区CREATE TABLE customers_1 (first_name VARCHAR(25) key,last_name VARCHAR(25),street_1 VARCHAR(30),street_2 VARCHAR(30),city VARCHAR(15),renewal DATE)shardkeyfirst_namePARTITION BY LIST(city) (PARTITION pRegion_1 VALUES IN(Beijing,Tianjin,Shanghai),PARTITION pRegion_2 VALUES IN(chongqing,Wulumuqi,Dalian),PARTITION pRegion_3 VALUES IN(Suzhou,Hangzhou,Xiamen),PARTITION pRegion_4 VALUES IN(Shenzhen,Guangzhou,chengdu));插入数据INSERT INTO customers_1 (first_name, last_name, street_1,street_2, city,renewal) VALUES(Alice,Wang,123 Main St,Apt 4,Beijing,2023-01-01);#删除和新增二级分区alter table customers_1 drop partition pRegion_1;alter table customers_1 add partition(partition pRegion_5 VALUES IN( Wuhan,Nanjing,Guiyang));explain SElEcT u.username,u.phone FRoM orders o JoIN users u ON o.user_id u.user_id WHERE o.user_id99968;ALTER TABLE orders add idx_user_id(user_id);22、序列创建自增字段的表create table auto_inc (a int,b int,c int auto_increment,d int,key auto(c),primary key p(a,d)) shardkeyd;insert into auto_inc (a,b,d,c) values(1,2,3,0),(1,2,4,0);select * from auto_inc;23、导数据100万数据压测sysbench --db-drivermysql --mysql-hostxxxx --mysql-portxxxx --mysql-userxx --mysql-passwordxxxxx --mysql-dbxx --tables1 --table-size1000000 oltp_read_write prepare非分布式1登录数据库创建库表。CREATE DATABASE testdb;USE testdb;CREATE TABLE users (user_id INT PRIMARY KEY,username VARCHAR(50) NOT NULL,phone VARCHAR(100) NOT NULL);CREATE TABLE orders (id INT PRIMARY KEY,user_id INT NOT NULL,product_name VARCHAR(100) NOT NULL,amount DECIMAL(10, 2) NOT NULL);2在部署机执行下面脚本插入数据。(mysql配置改为你自己实例的信息)#!/bin/bash# MySQL 配置DB_USER用户名DB_PASS密码DB_NAMEtestdbDB_HOST实例IPDB_PORT实例端口BATCH_SIZE2000 # 每批次插入量TOTAL_USERS1000000 # 总用户数TOTAL_ORDERS1000000 # 总订单数# 进度显示函数show_progress() {local current$1local total$2local type$3printf \r插入%s: %d%% [%d/%d] $type $((current*100/total)) $current $total}# 安全执行函数含错误重试safe_exec() {local sql$1for attempt in {1..3}; doif mysql -h $DB_HOST -P $DB_PORT -u $DB_USER --password$DB_PASS $DB_NAME -e $sql ; thenreturn 0elseecho 尝试 $attempt 失败正在重试...sleep $((attempt*2))fidoneecho 永久失败: $sqlexit 1}# 插入用户数据单线程echo 开始插入用户数据...for ((i1; iTOTAL_USERS; iBATCH_SIZE)); doend$((iBATCH_SIZE-1))((end TOTAL_USERS)) end$TOTAL_USERS# 构建批量插入SQLSQLSTART TRANSACTION;SQLINSERT INTO users (user_id, username, phone) VALUES for ((ji; jend; j)); doSQL($j, user$j, 123456789${j:0:8}),doneSQL${SQL%,}; COMMIT;safe_exec $SQLshow_progress $end $TOTAL_USERS 用户doneecho -e \n用户数据插入完成# 插入订单数据单线程echo 开始插入订单数据...for ((i1; iTOTAL_ORDERS; iBATCH_SIZE)); doend$((iBATCH_SIZE-1))((end TOTAL_ORDERS)) end$TOTAL_ORDERS# 构建批量插入SQLSQLSTART TRANSACTION;SQLINSERT INTO orders (id, user_id, product_name, amount) VALUES for ((ji; jend; j)); douser_id$((1 RANDOM % TOTAL_USERS)) # 随机用户IDamount$((RANDOM % 101)) # 0-100随机整数SQL($j, $user_id, product$j, $amount),doneSQL${SQL%,}; COMMIT;safe_exec $SQLshow_progress $end $TOTAL_ORDERS 订单doneecho -e \n订单数据插入完成# 数据完整性验证echo -e \n最终数据校验:mysql -h $DB_HOST -P $DB_PORT -u $DB_USER --password$DB_PASS $DB_NAME EOFSELECT(SELECT COUNT(*) FROM users) AS total_users,(SELECT COUNT(*) FROM orders) AS total_orders,(SELECT COUNT(DISTINCT user_id) FROM orders) AS active_users,(SELECT MAX(amount) FROM orders) AS max_amount,(SELECT AVG(amount) FROM orders) AS avg_amount;EOFSELECT u.username, u.phone FROM orders o JOIN users u ON o.user_id u.user_id WHERE o.user_id用户ID;分布式1登录数据库创建库表。CREATE DATABASE testdb;USE testdb;CREATE TABLE users (user_id INT PRIMARY KEY,username VARCHAR(50) NOT NULL,phone VARCHAR(100) NOT NULL) shardkeyuser_id;CREATE TABLE orders (id INT PRIMARY KEY,user_id INT NOT NULL,product_name VARCHAR(100) NOT NULL,amount DECIMAL(10, 2) NOT NULL)shardkeyid;2在部署机执行下面脚本插入数据。(mysql配置改为你自己实例的信息)#!/bin/bash# MySQL 配置DB_USER用户名DB_PASS密码DB_NAMEtestdbDB_HOST实例IPDB_PORT实例端口BATCH_SIZE2000 # 每批次插入量TOTAL_USERS1000000 # 总用户数TOTAL_ORDERS1000000 # 总订单数# 进度显示函数show_progress() {local current$1local total$2local type$3printf \r插入%s: %d%% [%d/%d] $type $((current*100/total)) $current $total}# 安全执行函数含错误重试safe_exec() {local sql$1for attempt in {1..3}; doif mysql -h $DB_HOST -P $DB_PORT -u $DB_USER --password$DB_PASS $DB_NAME -e $sql ; thenreturn 0elseecho 尝试 $attempt 失败正在重试...sleep $((attempt*2))fidoneecho 永久失败: $sqlexit 1}# 插入用户数据单线程echo 开始插入用户数据...for ((i1; iTOTAL_USERS; iBATCH_SIZE)); doend$((iBATCH_SIZE-1))((end TOTAL_USERS)) end$TOTAL_USERS# 构建批量插入SQLSQLSTART TRANSACTION;SQLINSERT INTO users (user_id, username, phone) VALUES for ((ji; jend; j)); doSQL($j, user$j, 123456789${j:0:8}),doneSQL${SQL%,}; COMMIT;safe_exec $SQLshow_progress $end $TOTAL_USERS 用户doneecho -e \n用户数据插入完成# 插入订单数据单线程echo 开始插入订单数据...for ((i1; iTOTAL_ORDERS; iBATCH_SIZE)); doend$((iBATCH_SIZE-1))((end TOTAL_ORDERS)) end$TOTAL_ORDERS# 构建批量插入SQLSQLSTART TRANSACTION;SQLINSERT INTO orders (id, user_id, product_name, amount) VALUES for ((ji; jend; j)); douser_id$((1 RANDOM % TOTAL_USERS)) # 随机用户IDamount$((RANDOM % 101)) # 0-100随机整数SQL($j, $user_id, product$j, $amount),doneSQL${SQL%,}; COMMIT;safe_exec $SQLshow_progress $end $TOTAL_ORDERS 订单doneecho -e \n订单数据插入完成# 数据完整性验证echo -e \n最终数据校验:mysql -h $DB_HOST -P $DB_PORT -u $DB_USER --password$DB_PASS $DB_NAME EOFSELECT(SELECT COUNT(*) FROM users) AS total_users,(SELECT COUNT(*) FROM orders) AS total_orders,(SELECT COUNT(DISTINCT user_id) FROM orders) AS active_users,(SELECT MAX(amount) FROM orders) AS max_amount,(SELECT AVG(amount) FROM orders) AS avg_amount;EOF查看下面查询语句的执行计划。SELECT u.username, u.phone FROM orders o JOIN users u ON o.user_id u.user_id WHERE o.user_id1234;实验指导手册TDSQL分布式数据库数据表应用实践第一部分公有云或私有云TDSQLMySQL版的连接方法介绍【TDSQLMySQL版以下简称TDSQL】TDSQL分布式实例通过Proxy接口提供和mysql兼容的连接方式用户通过IP地址、端口号以及用户名、密码进行连接注意公有云TDSQL需要在实例页面申请公网连接地址连接示例mysql -h172.21.32.13 proxy地址 -P3306proxy端口 -utest 数据库账号 -p与普通的mysql连接方法一致分布式实例兼容mysql的协议和语法支持SSL加密等功能。当然您也可以使用navicat、 jdbc、 odbc、 php、 Python等来连接分布式TDSQL实例。第二部分TDSQL分布式实例支持的表类型介绍和应用1、TDSQL分布式实例支持表的类型介绍a、分布式表即水平拆分表也称为“分表”该表从业务视角是一张完整的逻辑表但后端根据分表键shardkey的HASH值将数据分布到不同的物理节点组set中。b、普通表又名Noshard表即无需拆分的表和传统集中式数据库中的表一致且没有做任何特殊处理的表目前分布式实例将该表默认存放在第一个物理节点组set中。c、广播表又名小表广播技术即设置为广播表后该表的所有操作都将广播到所有物理节点组set中每个set都有该表的全量数据常用于业务系统关联查询较多修改较少的小表或配置表等。表类型选用注意事项在分布式实例中如果两张表分表键相等这意味着两张表相同的分表键对应的行存储在相同的物理节点组中。这种场景通常被称为组拆分groupshard,会极大的提升业务联合查询等语句的处理效率。由于单表默认放置在第一个set上如果在分布式实例中建立大的单表则会导致第一个set的负载太大。除非特别需要在分布式实例中尽量使用分布式表这也是分布式实例的特点之一。2、TDSQL分布式实例表的创建接下来我们来看下分布式数据库TDSQL所支持的三种类型表的使用方法和注意事项。a、分布式表的使用简述普通的分表创建时必须在最后面指定分表键shardkey的值该值为表中的一个字段名字会用于后续sql的路由选择。连接到TDSQL分布式实例后我们创建一个本次操作使用的数据库名为testdbmysql create database testdb;mysqluse testdb;接下来我们创建分布式表命名以分布式拼音首字母命名建表语句1MySQL testdb create table fbs ( a int, b int, c char(20),primary key (a),unique key u_1(a,c) ) shardkeya;Query OK, 0 rows affected (0.07 sec)建表语句2MySQL testdb create table fbs2 ( a int, b int, c char(20), primary key (a,b) ) shardkeya;Query OK, 0 rows affected (0.09 sec)b、广播表的创建简述支持建小表广播表此时该表在所有set中都是全部数据这个主要方用于跨set的join操作同时通过分布式事务保证修改操作的原子性使得所有set的数据是完全一致的 。**语句**MySQL testdb create table gbb(a int,b int key) shardkeynoshardkey_allset;Query OK, 0 rows affected (0.03 sec)c、传统普通表简述支持建立普通的表语法和传统mysql完全一样此时该表的数据全量存在第一个set节点中所有该类型的表都放在第一个set中。MySQL testdb create table ptb(a int ,b varchar(10));Query OK, 0 rows affected (0.03 sec)注意事项1、在分布式实例中分布式表shardkey对应后端数据库的分区字段因此必须是主键以及所有唯一索引的一部分 否则可能无法完成建表操作。2、分布式表shardkey字段的值不包含中文, 否则proxy会转换字符集可能会出错。另外SQL语法上如shardkeya 一般放在SQL语句最后来写。3、TDSQL分布式实例表的数据操作为了更好的发挥分布式架构的优势在进行SQL操作时和传统数据库还是有部分差异。接下来我们从数据库的插入更新删除方面分别来看有哪些注意事项。INSERT插入操作插入语句1MySQL testdb insert into fbs(a,b) values(10,1000);Query OK, 1 row affected (0.00 sec)插入语句2MySQL testdb insert into fbs values(1,10,1000);或MySQL testdb insert into test1 (b,c) values(100,record3);ERROR 810 (HY000): Proxy ERROR:sql is too complex,need to send to only noshard table.Shard table insert must has field spec注意语句2报错的原因insert时字段需要包含shardkey否则会拒绝执行该sql因为Proxy不知道该sql发往哪个后端分片节点。UPDATE、DELETE更新、删除操作更新语句1MySQL testdb update fbs set b2000 where a10;Query OK, 1 row affected (0.00 sec)更新语句2MySQL testdb update fbs set b2000 ;ERROR 658 (HY000): Proxy ERROR: Join internal error: update query has no where clause删除操作MySQL testdb delete from fbs;ERROR 913 (HY000): Proxy ERROR:Join internal error: delete query has no where clause注意事项1、出于数据操作安全上和减少人为误操作导致数据丢失情况的出现TDSQL禁止update 无 where 条件的更新动作。2、同样的delete操作无where条件也会被禁止执行如果确认要删除表数据或表建议备份后用truncate或drop方式操作。3、同样的update操作时尽量避免更新shardkey字段因为影响Proxy中的路由更新会导致错误。第三部分TDSQL分布式实例SQL透传功能指定节点访问1、TDSQL透传功能介绍对于分布式实例会对SQL进行语法解析有一定的限制如果用户想在某个set中获取单个节点数据或在指定节点执行SQL可以使用TDSQL的透传SQL的功能。使用透传功能我们需要重新连接登录TDSQL分布式实例时指定- c选项。普通登录方式不支持指定节点执行SQL的透传功能。登录如下mysql -h172.21.32.13 proxy地址 -utest -P3306 -p -c(透传必须指定-c)2、TDSQL透传操作演示首先我们重新登陆TDSQL分布式实例 mysql -h172.21.32.13 -utest -P3306 -p -c仍旧切换使用testdb数据库。a、查看分布式实例set节点使用/*proxy*/show status 查看当前的TDSQL分布式实例的节点信息共有两个set 分别为set_1605181898_1、set_1605181972_3MySQL testdb /*proxy*/show status ;------------------------------------------------------------------------------------------------| status_name | value |------------------------------------------------------------------------------------------------| cluster | group_1605181791_302290 || **set_1605181898_1:ip | 10.53.179.14:4322;s110.53.178.227:43221IDC_GZ_YDSS0301_792630 || set_1605181898_1:hash_range | 0---31 || **set_1605181972_3:ip | 10.53.179.14:4323;s110.53.178.227:43231IDC_GZ_YDSS0301_792630 || set_1605181972_3:hash_range | 32---63 || set | set_1605181898_1,set_1605181972_3 |------------------------------------------------------------------------------------------------6 rows in set (0.00 sec)b、演示数据插入我们针对之前创建的fbs分布式表进行数据的插入MySQL testdb insert into fbs(a,b,c) values(10,1,AAA),(20,2,bbb),(30,3,ccc),(40,4,dddd),(50,5,eee),(60,6,fff),(70,7,ggg),(80,8,hhhh);MySQL testdb select * from fbs order by 1;----------------| a | b | c |----------------| 10 | 1 | AAA || 20 | 2 | bbb || 30 | 3 | ccc || 40 | 4 | dddd || 50 | 5 | eee || 60 | 6 | fff || 70 | 7 | ggg || 80 | 8 | hhhh |----------------8 rows in set (0.00 sec)c、透传查看数据在各个节点的分布情况MySQL testdb /*proxy*/show status;------------------------------------------------------------------------------------------------| status_name | value |------------------------------------------------------------------------------------------------| cluster | group_1605181791_302290 || **set_1605181898_1:ip | 10.53.179.14:4322;s110.53.178.227:43221IDC_GZ_YDSS0301_792630 || set_1605181898_1:hash_range | 0---31 || set_1605181972_3:ip | 10.53.179.14:4323;s110.53.178.227:43231IDC_GZ_YDSS0301_792630 || set_1605181972_3:hash_range | 32---63 || set | set_1605181898_1,set_1605181972_3 |------------------------------------------------------------------------------------------------6 rows in set (0.00 sec)查看数据在set_1605181898_1 节点上的分布MySQL testdb /*sets:set_1605181898_1*/select * from fbs order by 1;----------------------------------| a | b | c | info |----------------------------------| 10 | 1 | AAA | set_1605181898_1 || 30 | 3 | ccc | set_1605181898_1 || 40 | 4 | dddd | set_1605181898_1 || 50 | 5 | eee | set_1605181898_1 || 80 | 8 | hhhh | set_1605181898_1 |----------------------------------5 rows in set (0.00 sec)查看数据在set_1605181972_3节点上的分布MySQL testdb /*sets:set_1605181972_3*/select * from fbs order by 1;----------------------------------| a | b | c | info |----------------------------------| 20 | 2 | bbb | set_1605181972_3 || 60 | 6 | fff | set_1605181972_3 || 70 | 7 | ggg | set_1605181972_3 |----------------------------------3 rows in set (0.00 sec)d、通过shardkey分片号查看数据MySQL testdb /*shardkey:2*/select * from fbs order by 1;----------------| a | b | c |----------------| 20 | 2 | bbb || 60 | 6 | fff || 70 | 7 | ggg |----------------3 rows in set (0.00 sec)支持透传种类和使用格式1、set名字可以通过/*proxy*/show status查询2、/*sets:set_1名称*/ 透传指定节点3、/*sets:allsets*/ 透传所有节点4、/*shardkey:10*/ 透传到shardkey分片对应的set5、支持透传sql到对应的一个或者多个set分布式表的DDL部分的语句限制暂不支持CREATE TABLE ... LIKE暂不支持CREATE TABLE ... SELECT暂不支持CREATE TEMPORARY TABLE暂不支持CREATE/DROP/ALTER SERVER/LOGFILE GROUP/暂不支持ALTER对分表键(shardkey)进行重命名不过可以修改类型分布式表的DML部分的语句限制暂不支持SELECT INTO OUTFILE/INTO DUMPFILE/INTO LOAD DATA导出暂不支持INSERT ... SELECT暂不支持UPDATE 分布式shardkey列的值
TDSQL_MYSQL记录
https://cloud.tencent.com/edu/learning/course-3277-57186免密配置cd tdsql_install/scripts/vim ip_passwd_listIP 密码sh nokey.shfor ip in cat ip_passwd_list | awk {print $1}; do ssh $ip echo $ip: ssh success; done0、安装unzip tdsql_10.3.22.6.0.x86_64.zipcd /root/tdsql_10.3.22.6.0/tdsql_install/scripts./start_deploy.shCPU:请填写CPU实际逻辑核数内存:请填写实际内存绝对值的75%(经验值)磁盘:请设置为实际磁盘空间(RAID后)绝对值的75%~90%。其中建议数据盘:日志盘的比例为3:1(经验值)1、扩容赤兔登录控制机/root/tdsql_10.3.22.6.0/tdsql_installvim tdsql_add_hosts[tdsql_newchitu]tdsql_newchitu1 ansible_ssh_host192.168.0.3 ---需要填写扩容的赤兔机器ipcd /root/tdsql_10.3.22.6.0/tdsql_installansible-playbook -i tdsql_add_hosts playbooks/tdsql_add_chitu.yml在原赤兔节点执行scp -p /data/website/tdsqlpcloud/www/config/database.php 192.168.0.x:/data/website/tdsqlpcloud/www/config/scp -p /data/website/tdsqlpcloud/www/config/install.lock 192.168.0.x:/data/website/tdsqlpcloud/www/config/在新赤兔节点执行chown -R nginx:nginx /data/website新赤兔服务地址http://192.168.0.x/tdsqlpcloud2、升级上传升级工具包/data目录tdsql_10.3.22.6.0_WEB_UPGRADE.x86_64.ziptdsql_10.3.22.6.0_WEB_UPGRADE解压unzip tdsql_10.3.22.6.0_WEB_UPGRADE.x86_64.zipcd /data/tdsql_10.3.22.6.0_WEB_UPGRADE/tdsql_install启动升级程序bash scripts/upgrade.sh /root/tdsql_10.3.22.6.0/tdsql_installyhttp://192.168.0.154:8081admin/Huawei12#$% 赤兔的账号密码软件包管理上传软件包tdsql-mysql5717-22.6.0.tl2.x86_64.tgztdsql-oss-22.6.3.tl2.x86_64.tgz组件升级同步组件状态先升级DB公共包在升级OSS最后升级实例实例 升级 存储节点\s 检查数据库版本3、物理备份hdfs备份恢复9870非分布式表物理回档会创建一个新的表。启动hdfssu - tdsqlhdfs --daemon start journalnode2在hdfs1和hdfs2上启动namenodehdfs --daemon start namenode3在hdfs1和hdfs2上启动zkfchdfs --daemon start zkfc4在所有hdfs机器上启动datanodehdfs --daemon start datanodehdfs dfsadmin -report4、主备切换5、赤兔修改参数select max_connections;6、创建分布式实例7、创建非分布式实力8、查询实例9、安装10、DCN同步11、闪回12、导数据分析优化 必考sql优化SELECT u.username, u.phone FROM orders o JOIN users u ON o.user_id u.user_id WHERE o.user_id用户ID;建议增加索引alter table testdb.orders add index idx_user_id(user_id);增加索引实时诊断冗余索引alter table testdb.orders add index idx_id_user_id(id,user_id);死锁13、赤兔账号创建添加用户、设置权限、添加集群管理人、添加实例管理人14、注释透传功能-- set数量、名称、ip、hash_range范围、主备节点等/*proxy*/show status;-- 一致性读(consistent_read)是否开启、日志相关等/*proxy*/show config;/*sets:set_1*//*sets:set_1,set_2*/ set名字可以通过/*proxy*/show status查询/*sets:allsets*/15、#创建单表create table single_t(a int,b int,PRIMARY KEY(a));16、#创建广播表create table global_t(a int,b int,PRIMARY KEY(a)) shardkeynoshardkey_allset;17、#创建一级HASHcreate table test1(a int,b int,c char(20),primary key(a)) shardkeya;18、#创建一级range分区create table t1(a int key ,b int) tdsql_distributed by range(a) (s1 values less than(100),s2 values less than(200));19、#创建一级分表LIST分区create table t2(a int key ,b int) tdsql_distributed by list(a) (s1 values in(1,2),s2 values in (3,4));20、#创建二级RANGE分区CREATE TABLE employees_int(id INT key NOT NULL,fname VARCHAR(30),lname VARCHAR(30),hired date,Separated DATE NOT NULL DEFAULT9999-12-31,job_code INT,store_id INT)shardkeyidPARTITION BY RANGE(year(hired))(PARTITION P0 VALUES LESS THAN(1991),PARTITION P1 VALUES LESS THAN(1996),PARTITION P2 VALUES LESS THAN(2001));21、#创建二级LIST分区CREATE TABLE customers_1 (first_name VARCHAR(25) key,last_name VARCHAR(25),street_1 VARCHAR(30),street_2 VARCHAR(30),city VARCHAR(15),renewal DATE)shardkeyfirst_namePARTITION BY LIST(city) (PARTITION pRegion_1 VALUES IN(Beijing,Tianjin,Shanghai),PARTITION pRegion_2 VALUES IN(chongqing,Wulumuqi,Dalian),PARTITION pRegion_3 VALUES IN(Suzhou,Hangzhou,Xiamen),PARTITION pRegion_4 VALUES IN(Shenzhen,Guangzhou,chengdu));插入数据INSERT INTO customers_1 (first_name, last_name, street_1,street_2, city,renewal) VALUES(Alice,Wang,123 Main St,Apt 4,Beijing,2023-01-01);#删除和新增二级分区alter table customers_1 drop partition pRegion_1;alter table customers_1 add partition(partition pRegion_5 VALUES IN( Wuhan,Nanjing,Guiyang));explain SElEcT u.username,u.phone FRoM orders o JoIN users u ON o.user_id u.user_id WHERE o.user_id99968;ALTER TABLE orders add idx_user_id(user_id);22、序列创建自增字段的表create table auto_inc (a int,b int,c int auto_increment,d int,key auto(c),primary key p(a,d)) shardkeyd;insert into auto_inc (a,b,d,c) values(1,2,3,0),(1,2,4,0);select * from auto_inc;23、导数据100万数据压测sysbench --db-drivermysql --mysql-hostxxxx --mysql-portxxxx --mysql-userxx --mysql-passwordxxxxx --mysql-dbxx --tables1 --table-size1000000 oltp_read_write prepare非分布式1登录数据库创建库表。CREATE DATABASE testdb;USE testdb;CREATE TABLE users (user_id INT PRIMARY KEY,username VARCHAR(50) NOT NULL,phone VARCHAR(100) NOT NULL);CREATE TABLE orders (id INT PRIMARY KEY,user_id INT NOT NULL,product_name VARCHAR(100) NOT NULL,amount DECIMAL(10, 2) NOT NULL);2在部署机执行下面脚本插入数据。(mysql配置改为你自己实例的信息)#!/bin/bash# MySQL 配置DB_USER用户名DB_PASS密码DB_NAMEtestdbDB_HOST实例IPDB_PORT实例端口BATCH_SIZE2000 # 每批次插入量TOTAL_USERS1000000 # 总用户数TOTAL_ORDERS1000000 # 总订单数# 进度显示函数show_progress() {local current$1local total$2local type$3printf \r插入%s: %d%% [%d/%d] $type $((current*100/total)) $current $total}# 安全执行函数含错误重试safe_exec() {local sql$1for attempt in {1..3}; doif mysql -h $DB_HOST -P $DB_PORT -u $DB_USER --password$DB_PASS $DB_NAME -e $sql ; thenreturn 0elseecho 尝试 $attempt 失败正在重试...sleep $((attempt*2))fidoneecho 永久失败: $sqlexit 1}# 插入用户数据单线程echo 开始插入用户数据...for ((i1; iTOTAL_USERS; iBATCH_SIZE)); doend$((iBATCH_SIZE-1))((end TOTAL_USERS)) end$TOTAL_USERS# 构建批量插入SQLSQLSTART TRANSACTION;SQLINSERT INTO users (user_id, username, phone) VALUES for ((ji; jend; j)); doSQL($j, user$j, 123456789${j:0:8}),doneSQL${SQL%,}; COMMIT;safe_exec $SQLshow_progress $end $TOTAL_USERS 用户doneecho -e \n用户数据插入完成# 插入订单数据单线程echo 开始插入订单数据...for ((i1; iTOTAL_ORDERS; iBATCH_SIZE)); doend$((iBATCH_SIZE-1))((end TOTAL_ORDERS)) end$TOTAL_ORDERS# 构建批量插入SQLSQLSTART TRANSACTION;SQLINSERT INTO orders (id, user_id, product_name, amount) VALUES for ((ji; jend; j)); douser_id$((1 RANDOM % TOTAL_USERS)) # 随机用户IDamount$((RANDOM % 101)) # 0-100随机整数SQL($j, $user_id, product$j, $amount),doneSQL${SQL%,}; COMMIT;safe_exec $SQLshow_progress $end $TOTAL_ORDERS 订单doneecho -e \n订单数据插入完成# 数据完整性验证echo -e \n最终数据校验:mysql -h $DB_HOST -P $DB_PORT -u $DB_USER --password$DB_PASS $DB_NAME EOFSELECT(SELECT COUNT(*) FROM users) AS total_users,(SELECT COUNT(*) FROM orders) AS total_orders,(SELECT COUNT(DISTINCT user_id) FROM orders) AS active_users,(SELECT MAX(amount) FROM orders) AS max_amount,(SELECT AVG(amount) FROM orders) AS avg_amount;EOFSELECT u.username, u.phone FROM orders o JOIN users u ON o.user_id u.user_id WHERE o.user_id用户ID;分布式1登录数据库创建库表。CREATE DATABASE testdb;USE testdb;CREATE TABLE users (user_id INT PRIMARY KEY,username VARCHAR(50) NOT NULL,phone VARCHAR(100) NOT NULL) shardkeyuser_id;CREATE TABLE orders (id INT PRIMARY KEY,user_id INT NOT NULL,product_name VARCHAR(100) NOT NULL,amount DECIMAL(10, 2) NOT NULL)shardkeyid;2在部署机执行下面脚本插入数据。(mysql配置改为你自己实例的信息)#!/bin/bash# MySQL 配置DB_USER用户名DB_PASS密码DB_NAMEtestdbDB_HOST实例IPDB_PORT实例端口BATCH_SIZE2000 # 每批次插入量TOTAL_USERS1000000 # 总用户数TOTAL_ORDERS1000000 # 总订单数# 进度显示函数show_progress() {local current$1local total$2local type$3printf \r插入%s: %d%% [%d/%d] $type $((current*100/total)) $current $total}# 安全执行函数含错误重试safe_exec() {local sql$1for attempt in {1..3}; doif mysql -h $DB_HOST -P $DB_PORT -u $DB_USER --password$DB_PASS $DB_NAME -e $sql ; thenreturn 0elseecho 尝试 $attempt 失败正在重试...sleep $((attempt*2))fidoneecho 永久失败: $sqlexit 1}# 插入用户数据单线程echo 开始插入用户数据...for ((i1; iTOTAL_USERS; iBATCH_SIZE)); doend$((iBATCH_SIZE-1))((end TOTAL_USERS)) end$TOTAL_USERS# 构建批量插入SQLSQLSTART TRANSACTION;SQLINSERT INTO users (user_id, username, phone) VALUES for ((ji; jend; j)); doSQL($j, user$j, 123456789${j:0:8}),doneSQL${SQL%,}; COMMIT;safe_exec $SQLshow_progress $end $TOTAL_USERS 用户doneecho -e \n用户数据插入完成# 插入订单数据单线程echo 开始插入订单数据...for ((i1; iTOTAL_ORDERS; iBATCH_SIZE)); doend$((iBATCH_SIZE-1))((end TOTAL_ORDERS)) end$TOTAL_ORDERS# 构建批量插入SQLSQLSTART TRANSACTION;SQLINSERT INTO orders (id, user_id, product_name, amount) VALUES for ((ji; jend; j)); douser_id$((1 RANDOM % TOTAL_USERS)) # 随机用户IDamount$((RANDOM % 101)) # 0-100随机整数SQL($j, $user_id, product$j, $amount),doneSQL${SQL%,}; COMMIT;safe_exec $SQLshow_progress $end $TOTAL_ORDERS 订单doneecho -e \n订单数据插入完成# 数据完整性验证echo -e \n最终数据校验:mysql -h $DB_HOST -P $DB_PORT -u $DB_USER --password$DB_PASS $DB_NAME EOFSELECT(SELECT COUNT(*) FROM users) AS total_users,(SELECT COUNT(*) FROM orders) AS total_orders,(SELECT COUNT(DISTINCT user_id) FROM orders) AS active_users,(SELECT MAX(amount) FROM orders) AS max_amount,(SELECT AVG(amount) FROM orders) AS avg_amount;EOF查看下面查询语句的执行计划。SELECT u.username, u.phone FROM orders o JOIN users u ON o.user_id u.user_id WHERE o.user_id1234;实验指导手册TDSQL分布式数据库数据表应用实践第一部分公有云或私有云TDSQLMySQL版的连接方法介绍【TDSQLMySQL版以下简称TDSQL】TDSQL分布式实例通过Proxy接口提供和mysql兼容的连接方式用户通过IP地址、端口号以及用户名、密码进行连接注意公有云TDSQL需要在实例页面申请公网连接地址连接示例mysql -h172.21.32.13 proxy地址 -P3306proxy端口 -utest 数据库账号 -p与普通的mysql连接方法一致分布式实例兼容mysql的协议和语法支持SSL加密等功能。当然您也可以使用navicat、 jdbc、 odbc、 php、 Python等来连接分布式TDSQL实例。第二部分TDSQL分布式实例支持的表类型介绍和应用1、TDSQL分布式实例支持表的类型介绍a、分布式表即水平拆分表也称为“分表”该表从业务视角是一张完整的逻辑表但后端根据分表键shardkey的HASH值将数据分布到不同的物理节点组set中。b、普通表又名Noshard表即无需拆分的表和传统集中式数据库中的表一致且没有做任何特殊处理的表目前分布式实例将该表默认存放在第一个物理节点组set中。c、广播表又名小表广播技术即设置为广播表后该表的所有操作都将广播到所有物理节点组set中每个set都有该表的全量数据常用于业务系统关联查询较多修改较少的小表或配置表等。表类型选用注意事项在分布式实例中如果两张表分表键相等这意味着两张表相同的分表键对应的行存储在相同的物理节点组中。这种场景通常被称为组拆分groupshard,会极大的提升业务联合查询等语句的处理效率。由于单表默认放置在第一个set上如果在分布式实例中建立大的单表则会导致第一个set的负载太大。除非特别需要在分布式实例中尽量使用分布式表这也是分布式实例的特点之一。2、TDSQL分布式实例表的创建接下来我们来看下分布式数据库TDSQL所支持的三种类型表的使用方法和注意事项。a、分布式表的使用简述普通的分表创建时必须在最后面指定分表键shardkey的值该值为表中的一个字段名字会用于后续sql的路由选择。连接到TDSQL分布式实例后我们创建一个本次操作使用的数据库名为testdbmysql create database testdb;mysqluse testdb;接下来我们创建分布式表命名以分布式拼音首字母命名建表语句1MySQL testdb create table fbs ( a int, b int, c char(20),primary key (a),unique key u_1(a,c) ) shardkeya;Query OK, 0 rows affected (0.07 sec)建表语句2MySQL testdb create table fbs2 ( a int, b int, c char(20), primary key (a,b) ) shardkeya;Query OK, 0 rows affected (0.09 sec)b、广播表的创建简述支持建小表广播表此时该表在所有set中都是全部数据这个主要方用于跨set的join操作同时通过分布式事务保证修改操作的原子性使得所有set的数据是完全一致的 。**语句**MySQL testdb create table gbb(a int,b int key) shardkeynoshardkey_allset;Query OK, 0 rows affected (0.03 sec)c、传统普通表简述支持建立普通的表语法和传统mysql完全一样此时该表的数据全量存在第一个set节点中所有该类型的表都放在第一个set中。MySQL testdb create table ptb(a int ,b varchar(10));Query OK, 0 rows affected (0.03 sec)注意事项1、在分布式实例中分布式表shardkey对应后端数据库的分区字段因此必须是主键以及所有唯一索引的一部分 否则可能无法完成建表操作。2、分布式表shardkey字段的值不包含中文, 否则proxy会转换字符集可能会出错。另外SQL语法上如shardkeya 一般放在SQL语句最后来写。3、TDSQL分布式实例表的数据操作为了更好的发挥分布式架构的优势在进行SQL操作时和传统数据库还是有部分差异。接下来我们从数据库的插入更新删除方面分别来看有哪些注意事项。INSERT插入操作插入语句1MySQL testdb insert into fbs(a,b) values(10,1000);Query OK, 1 row affected (0.00 sec)插入语句2MySQL testdb insert into fbs values(1,10,1000);或MySQL testdb insert into test1 (b,c) values(100,record3);ERROR 810 (HY000): Proxy ERROR:sql is too complex,need to send to only noshard table.Shard table insert must has field spec注意语句2报错的原因insert时字段需要包含shardkey否则会拒绝执行该sql因为Proxy不知道该sql发往哪个后端分片节点。UPDATE、DELETE更新、删除操作更新语句1MySQL testdb update fbs set b2000 where a10;Query OK, 1 row affected (0.00 sec)更新语句2MySQL testdb update fbs set b2000 ;ERROR 658 (HY000): Proxy ERROR: Join internal error: update query has no where clause删除操作MySQL testdb delete from fbs;ERROR 913 (HY000): Proxy ERROR:Join internal error: delete query has no where clause注意事项1、出于数据操作安全上和减少人为误操作导致数据丢失情况的出现TDSQL禁止update 无 where 条件的更新动作。2、同样的delete操作无where条件也会被禁止执行如果确认要删除表数据或表建议备份后用truncate或drop方式操作。3、同样的update操作时尽量避免更新shardkey字段因为影响Proxy中的路由更新会导致错误。第三部分TDSQL分布式实例SQL透传功能指定节点访问1、TDSQL透传功能介绍对于分布式实例会对SQL进行语法解析有一定的限制如果用户想在某个set中获取单个节点数据或在指定节点执行SQL可以使用TDSQL的透传SQL的功能。使用透传功能我们需要重新连接登录TDSQL分布式实例时指定- c选项。普通登录方式不支持指定节点执行SQL的透传功能。登录如下mysql -h172.21.32.13 proxy地址 -utest -P3306 -p -c(透传必须指定-c)2、TDSQL透传操作演示首先我们重新登陆TDSQL分布式实例 mysql -h172.21.32.13 -utest -P3306 -p -c仍旧切换使用testdb数据库。a、查看分布式实例set节点使用/*proxy*/show status 查看当前的TDSQL分布式实例的节点信息共有两个set 分别为set_1605181898_1、set_1605181972_3MySQL testdb /*proxy*/show status ;------------------------------------------------------------------------------------------------| status_name | value |------------------------------------------------------------------------------------------------| cluster | group_1605181791_302290 || **set_1605181898_1:ip | 10.53.179.14:4322;s110.53.178.227:43221IDC_GZ_YDSS0301_792630 || set_1605181898_1:hash_range | 0---31 || **set_1605181972_3:ip | 10.53.179.14:4323;s110.53.178.227:43231IDC_GZ_YDSS0301_792630 || set_1605181972_3:hash_range | 32---63 || set | set_1605181898_1,set_1605181972_3 |------------------------------------------------------------------------------------------------6 rows in set (0.00 sec)b、演示数据插入我们针对之前创建的fbs分布式表进行数据的插入MySQL testdb insert into fbs(a,b,c) values(10,1,AAA),(20,2,bbb),(30,3,ccc),(40,4,dddd),(50,5,eee),(60,6,fff),(70,7,ggg),(80,8,hhhh);MySQL testdb select * from fbs order by 1;----------------| a | b | c |----------------| 10 | 1 | AAA || 20 | 2 | bbb || 30 | 3 | ccc || 40 | 4 | dddd || 50 | 5 | eee || 60 | 6 | fff || 70 | 7 | ggg || 80 | 8 | hhhh |----------------8 rows in set (0.00 sec)c、透传查看数据在各个节点的分布情况MySQL testdb /*proxy*/show status;------------------------------------------------------------------------------------------------| status_name | value |------------------------------------------------------------------------------------------------| cluster | group_1605181791_302290 || **set_1605181898_1:ip | 10.53.179.14:4322;s110.53.178.227:43221IDC_GZ_YDSS0301_792630 || set_1605181898_1:hash_range | 0---31 || set_1605181972_3:ip | 10.53.179.14:4323;s110.53.178.227:43231IDC_GZ_YDSS0301_792630 || set_1605181972_3:hash_range | 32---63 || set | set_1605181898_1,set_1605181972_3 |------------------------------------------------------------------------------------------------6 rows in set (0.00 sec)查看数据在set_1605181898_1 节点上的分布MySQL testdb /*sets:set_1605181898_1*/select * from fbs order by 1;----------------------------------| a | b | c | info |----------------------------------| 10 | 1 | AAA | set_1605181898_1 || 30 | 3 | ccc | set_1605181898_1 || 40 | 4 | dddd | set_1605181898_1 || 50 | 5 | eee | set_1605181898_1 || 80 | 8 | hhhh | set_1605181898_1 |----------------------------------5 rows in set (0.00 sec)查看数据在set_1605181972_3节点上的分布MySQL testdb /*sets:set_1605181972_3*/select * from fbs order by 1;----------------------------------| a | b | c | info |----------------------------------| 20 | 2 | bbb | set_1605181972_3 || 60 | 6 | fff | set_1605181972_3 || 70 | 7 | ggg | set_1605181972_3 |----------------------------------3 rows in set (0.00 sec)d、通过shardkey分片号查看数据MySQL testdb /*shardkey:2*/select * from fbs order by 1;----------------| a | b | c |----------------| 20 | 2 | bbb || 60 | 6 | fff || 70 | 7 | ggg |----------------3 rows in set (0.00 sec)支持透传种类和使用格式1、set名字可以通过/*proxy*/show status查询2、/*sets:set_1名称*/ 透传指定节点3、/*sets:allsets*/ 透传所有节点4、/*shardkey:10*/ 透传到shardkey分片对应的set5、支持透传sql到对应的一个或者多个set分布式表的DDL部分的语句限制暂不支持CREATE TABLE ... LIKE暂不支持CREATE TABLE ... SELECT暂不支持CREATE TEMPORARY TABLE暂不支持CREATE/DROP/ALTER SERVER/LOGFILE GROUP/暂不支持ALTER对分表键(shardkey)进行重命名不过可以修改类型分布式表的DML部分的语句限制暂不支持SELECT INTO OUTFILE/INTO DUMPFILE/INTO LOAD DATA导出暂不支持INSERT ... SELECT暂不支持UPDATE 分布式shardkey列的值
