1、创建私有网络
在腾讯云服务器控制台,私有网络中创建,选择区域、设置IP网段。
- 区域:上海八区
- IP:172.16.16.0/24
2、创建安全组
在腾讯云服务器控制台,新建安全组,并放通全部端口。
3、安装TEM
(1)选择新创建的私有网络和安全组
- 私有网络:可用区选择正确,才可以找到刚创建的私有网络
- 安全组:开放了所有端口(生产环境不建议放通所有)
(2)其余选择默认配置
- TEM入口域名:没有配置
(3)安装前需确认账户余额
- 充值完成,即可开始安装
(4)开始安装
- 约10分钟,安装完成
- 查看TEM默认的帐号、密码
4、访问TEM
(1)在 实例-公网IP,查看公网IP
(2)通过公网IP,登录TEM
5、在TEM中添加主机
- 因为没有单独购买云主机,所以将TEM主机同时作为TiDB集群的主机来使用。
- 被控主机要安装TiUP组件。
(1)添加凭证
设置-凭证-添加凭证(CVM主机的账号和密码)
(2)在被控主机上安装TiUP组件(即TEM主机)
通过SecureCRT登录腾讯云主机(公网IP、root账号和密码),安装TiUP组件。
curl --proto '=https' --tlsv1.2 -sSf https://tiup-mirrors.pingcap.com/install.sh | sh
(3)添加中控机
(4)添加共享主机
6、创建TiDB集群 (1)设置集群基础配置
- 选择刚添加的中控机
- 选择共享模式
(2)规划集群节点
将PD、TiDB、TiKV均安装在了同一台主机上
(3)未配置参数模板(自动选择默认参数)
(4)创建TiDB集群
约5分钟,集群创建完成
7、创建数据库、表并插入数据
-- 创建数据库 CREATE DATABASE IF NOT EXISTS company_db; USE company_db;
-- 部门表 CREATE TABLE departments ( dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(50) NOT NULL, location VARCHAR(100) NOT NULL, budget DECIMAL(12,2) NOT NULL );
-- 员工表 CREATE TABLE employees ( emp_id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, hire_date DATE NOT NULL, salary DECIMAL(10,2) NOT NULL, dept_id INT, manager_id INT, FOREIGN KEY (dept_id) REFERENCES departments(dept_id), FOREIGN KEY (manager_id) REFERENCES employees(emp_id) );
-- 项目表 CREATE TABLE projects ( project_id INT PRIMARY KEY AUTO_INCREMENT, project_name VARCHAR(100) NOT NULL, start_date DATE NOT NULL, end_date DATE, budget DECIMAL(12,2) NOT NULL, dept_id INT, FOREIGN KEY (dept_id) REFERENCES departments(dept_id) );
-- 员工项目关联表 CREATE TABLE employee_projects ( emp_id INT, project_id INT, hours_worked DECIMAL(5,2) NOT NULL, role ENUM('Manager', 'Developer', 'Analyst', 'Tester') NOT NULL, PRIMARY KEY (emp_id, project_id), FOREIGN KEY (emp_id) REFERENCES employees(emp_id), FOREIGN KEY (project_id) REFERENCES projects(project_id) );
-- 工资历史表 CREATE TABLE salary_history ( history_id INT PRIMARY KEY AUTO_INCREMENT, emp_id INT NOT NULL, old_salary DECIMAL(10,2) NOT NULL, new_salary DECIMAL(10,2) NOT NULL, change_date DATE NOT NULL, reason VARCHAR(255), FOREIGN KEY (emp_id) REFERENCES employees(emp_id) );
-- 插入部门数据 INSERT INTO departments (dept_name, location, budget) VALUES ('Engineering', 'New York', 1000000.00), ('Marketing', 'Chicago', 750000.00), ('Sales', 'Los Angeles', 900000.00), ('HR', 'Boston', 500000.00), ('Finance', 'San Francisco', 850000.00);
-- 插入员工数据 INSERT INTO employees (first_name, last_name, email, hire_date, salary, dept_id, manager_id) VALUES ('John', 'Doe', '
john.doe@company.com', '2018-06-15', 75000.00, 1, NULL), ('Jane', 'Smith', '
jane.smith@company.com', '2019-03-10', 85000.00, 1, 1), ('Robert', 'Johnson', '
robert.j@company.com', '2020-11-22', 65000.00, 1, 1), ('Emily', 'Williams', '
emily.w@company.com', '2021-05-30', 72000.00, 2, NULL), ('Michael', 'Brown', '
michael.b@company.com', '2017-09-05', 90000.00, 2, 4), ('Sarah', 'Davis', '
sarah.d@company.com', '2022-01-15', 60000.00, 3, NULL), ('David', 'Miller', '
david.m@company.com', '2019-08-12', 80000.00, 3, 6), ('Lisa', 'Wilson', '
lisa.w@company.com', '2020-04-18', 68000.00, 4, NULL), ('James', 'Taylor', '
james.t@company.com', '2021-07-22', 55000.00, 5, NULL), ('Linda', 'Anderson', '
linda.a@company.com', '2018-12-01', 95000.00, 5, 9);
-- 插入项目数据 INSERT INTO projects (project_name, start_date, end_date, budget, dept_id) VALUES ('Website Redesign', '2023-01-15', '2023-06-30', 50000.00, 1), ('Mobile App', '2023-02-01', '2023-08-31', 75000.00, 1), ('Marketing Campaign', '2023-03-01', '2023-05-31', 30000.00, 2), ('Sales Training', '2023-01-10', '2023-03-15', 15000.00, 3), ('HR Portal', '2023-04-01', '2023-09-30', 45000.00, 4), ('Financial System', '2023-02-20', '2023-12-31', 100000.00, 5);
-- 插入员工项目数据 INSERT INTO employee_projects (emp_id, project_id, hours_worked, role) VALUES (1, 1, 120.50, 'Manager'), (2, 1, 180.25, 'Developer'), (3, 1, 150.75, 'Tester'), (2, 2, 200.00, 'Manager'), (3, 2, 175.50, 'Developer'), (4, 3, 95.25, 'Manager'), (5, 3, 145.75, 'Analyst'), (6, 4, 80.00, 'Manager'), (7, 4, 120.50, 'Analyst'), (8, 5, 100.25, 'Manager'), (9, 6, 220.75, 'Manager'), (10, 6, 190.25, 'Developer');
-- 插入工资历史数据 INSERT INTO salary_history (emp_id, old_salary, new_salary, change_date, reason) VALUES (1, 70000.00, 75000.00, '2023-01-01', 'Annual raise'), (2, 80000.00, 85000.00, '2023-01-01', 'Promotion'), (3, 60000.00, 65000.00, '2023-01-01', 'Performance increase'), (5, 85000.00, 90000.00, '2023-01-01', 'Annual raise'), (7, 75000.00, 80000.00, '2023-01-01', 'Annual raise'), (10, 90000.00, 95000.00, '2023-01-01', 'Promotion');
8、SQL语句测试 (1)使用窗口函数查询每个部门的工资排名
SELECT first_name, last_name, salary, dept_name,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS salary_rank
FROM employees
JOIN departments USING (dept_id);
(2)使用GROUP_CONCAT列出每个项目的参与者
SELECT p.project_name,
GROUP_CONCAT(CONCAT(e.first_name, ' ', e.last_name) SEPARATOR ', ') AS participants
FROM projects p
JOIN employee_projects ep ON p.project_id = ep.project_id
JOIN employees e ON ep.emp_id = e.emp_id
GROUP BY p.project_id;
(3)使用日期函数查询员工雇佣周年
SELECT first_name, last_name, hire_date,
DATE_ADD(hire_date, INTERVAL 5 YEAR) AS fifth_anniversary
FROM employees;
(4)查询工时最高的员工-项目组合
SELECT e.first_name, e.last_name, p.project_name, ep.hours_worked
FROM employee_projects ep
JOIN employees e ON ep.emp_id = e.emp_id
JOIN projects p ON ep.project_id = p.project_id
ORDER BY ep.hours_worked DESC LIMIT 1;
9、查看SQL执行状况
10、数据闪回测试 (1)删除表
(2)创建闪回任务
(3)查看数据恢复
表数据已正常恢复。
总结
1、TEM界面非常简洁、直观,在界面中可以清晰的看到集群中主机CPU、内存等资源利用状况。
2、通过TEM创建TiDB集群非常方便,可3-5分钟内快速完成集群创建。
3、在TiDB集群中,通过概览可以清晰看到节点数、版本、核心指标、告警等指标,对运维非常友好。
4、通过扩容功能,可以非常快速对PD、TiDB、TiKV进行水平扩容。
5、TiDB集群的参数在Web界面中直观展示,同时可以通过TEM快速进行参数修改。
6、数据闪回功能很实用,创建完闪回任务后,可以将集群、数据库、表快速恢复。
7、测试环境将TEM、PD、TiDB、TiKV全部安装在同一台主机中,但主机资源消耗非常小,在此环境下,SQL执行效率依然非常高。