Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 【 SOP 系列 42 】Tiup 常用的操作命令
|
username: Billmay表妹
Thanks to @Qingfeng Mingyue for the contribution.
TiUP is a cluster operation and maintenance tool introduced in TiDB version 4.0. TiUP cluster is a cluster management component written in Golang provided by TiUP. With the TiUP cluster component, you can perform daily maintenance tasks, including deploying, starting, stopping, destroying, scaling, upgrading TiDB clusters, and managing TiDB cluster parameters.
Currently, TiUP supports the deployment of TiDB, TiFlash, TiDB Binlog, TiCDC, and monitoring systems, among other related tools and components.
1. TiUP Installation
1. Install
curl --proto '=https' --tlsv1.2 -sSf https://tiup-mirrors.pingcap.com/install.sh | sh
2. Declare global variables
cat ~/.bash_profile
source ~/.bash_profile
3. Check TiUP version
tiup --binary cluster
2. TiUP Installation of TiDB
1. Edit configuration file
tiup cluster template > top.yaml
2. Check if the configuration is OK
tiup cluster check ./top.yaml --apply --user root -p
3. Deploy TiDB cluster
tiup cluster deploy tidb v6.1.0 ./top.yaml --user root -p
4. Check the status of the TiUP managed cluster
tiup cluster list
5. Check the status of the TiDB cluster
tiup cluster display tidb-test
6. Start the machine
tiup cluster start tidb-test
7. Connect to the TiDB database cluster
mysql -uroot -p -h 172.16.5.146 -P4001
8. Check the list of TiKV version components
tiup list tikv
9. Check all installed components
tiup list --installed
3. TiUP Scaling
1. TiUP scale-out
tiup cluster scale-out tidb-test cdc.yaml -uroot -p
2. Scale-in
tiup cluster scale-in tidb-test -N 172.16.4.122:8300 --force
3. Clean node information
tiup cluster prune tidb-test
4. Rename
tiup cluster rename tidb-test tidb-prod
5. Upgrade TiDB cluster
tiup cluster upgrade tidb-test v6.2.0
6. Downgrade TiDB cluster
10. Check CDC tasks
tiup ctl:v6.1.0 cdc changefeed list --pd=http://172.16.5.146:2399
11. Delete CDC tasks
tiup ctl:v6.1.0 cdc changefeed remove --pd=http://172.16.5.146:2399 -c kafka-2
12. Create tasks
Step 1: Create configuration file
cat kafka.conf
[sink]
dispatchers = {matcher = ['*.*'], topic = "tidb_{schema}_{table}", partition="index-value"},
{matcher = ['yz0920.*'], topic = "tidb_{schema}_{table}", partition="index-value"},
Step 2: Create command through TiUP
tiup ctl:v6.1.0 cdc changefeed create --pd="http://172.16.5.146:2399" --sink-uri="kafka://172.16.6.132:9092/tidb-kafka-3?protocol=avro&partition-num=1&max-message-bytes=67108864&replication-factor=1" --changefeed-id="kafka-2" --config="/home/kafka/kafka.conf" --schema-registry="http://172.16.6.132:8081"
4. Basic Operations of TiDB Database
1. Enter the database
mysql -uroot -p -h 172.16.5.146 -P4001
2. Check the database version
select tidb_version();
3. View the list of databases
show databases;
4. Create a database
create database tidb;
5. Enter the database
use tidb;
6. View the tables in the database
show tables;
7. Create a table
create table `tidb_tab`
(`id` int(11) not null auto_increment,
`name` varchar(20) not null default '',
`age` int(11) not null default 0,
`version` varchar(20) not null default '',
primary key (`id`), key `idx_age` (`age`));
8. Insert data into the table
insert into `tidb_tab` values (1,'tidb',5,'v5.0');
9. View data
select * from tidb_tab;
10. Check the database connection status:
## Create a test table t1
CREATE TABLE t1 (a int not null primary key auto_increment);
show processlist;
+---------------------+------+--------------------+------+---------+------+------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------------------+------+--------------------+------+---------+------+------------+------------------+
| 3006706905130266271 | root | 172.16.5.146:35600 | tidb | Query | 0 | autocommit | show processlist |
+---------------------+------+--------------------+------+---------+------+------------+------------------+
1 row in set (0.00 sec)
11. Check the system parameter auto_increment_increment
show session variables like 'auto_increment_increment';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
+--------------------------+-------+
1 row in set (0.00 sec)
12. Set auto_increment_increment=10, insert data
set auto_increment_increment=10;
MySQL [tidb]> insert into t1 values ();
Query OK, 1 row affected (0.00 sec)
MySQL [tidb]> select * from t1;
+----+
| a |
+----+
| 1 |
| 2 |
| 11 |
+----+
3 rows in set (0.00 sec)
MySQL [tidb]> show session variables like 'auto_increment_increment';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
+--------------------------+-------+
1 row in set (0.00 sec)
Session-level parameter modifications only affect the current session. For other sessions or local session modifications, the parameters are invalid and only effective for the current session.
13. Modify globally
set global auto_increment_increment=10;
The current session is still 1. Restart a client, auto_increment_increment=10
After restarting, it is still 10
5. TiDB User Management and Security
1. Create a user
create user 'lqb'@'%' identified by '123456';
2. Create roles
create role r_manager, r_staff;
3. View the user table
MySQL [tidb]> select user, host, authentication_string from mysql.user;
+-----------+------+-------------------------------------------+
| user | host | authentication_string |
+-----------+------+-------------------------------------------+
| root | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| lqb | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| r_manager | % | |
| r_staff | % | |
+-----------+------+-------------------------------------------+
4 rows in set (0.00 sec)
### Users and roles are stored in the mysql.user table. Roles do not have passwords.
4. Grant permissions:
GRANT ALL ON *.* TO 'lqb'@'%';
4. View role details
MySQL [tidb]> select * from mysql.user where user='r_staff'\G;
*************************** 1. row ***************************
Host: %
User: r_staff
authentication_string:
plugin: mysql_native_password
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Process_priv: N
Grant_priv: N
References_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Index_priv: N
Create_user_priv: N
Event_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Trigger_priv: N
Create_role_priv: N
Drop_role_priv: N
Account_locked: Y
Shutdown_priv: N
Reload_priv: N
FILE_priv: N
Config_priv: N
Create_Tablespace_Priv: N
1 row in set (0.01 sec)
ERROR: No query specified
###
The role is locked: Account_locked: Y
The role has no password: authentication_string: is null
5. Change user password
alter user 'lqb'@'%' identified by 'tidb';
6. Delete users and roles
drop role r_staff;
drop user lqb;
7. Create roles and grant permissions
create role r_mgr, r_emp;
grant select on tidb.* to r_emp;
grant insert, update, delete on tidb.* to r_mgr;
## Grant the permissions of the r_emp role to the r_mgr role and user lqb;
grant r_emp to r_mgr, 'lqb'@'%';
Enable roles and view roles:
set role all;
MySQL [(none)]> select current_role();
+-------------------------+
| current_role() |
+-------------------------+
| `r_emp`@`%`,`r_mgr`@`%` |
+-------------------------+
View grants
MySQL [(none)]> show grants;
+----------------------------------------------------------+
| Grants for User |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lqb'@'%' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON tidb.* TO 'lqb'@'%' |
| GRANT 'r_emp'@'%', 'r_mgr'@'%' TO 'lqb'@'%' |
|
username: 
|
username: 
|
username: