TiDB 迁移上云实践 (一) 之自建 MySQL

[root@lincloud259 ~]# cd /data[root@lincloud259 data]# mkdir -m 777 tidb_cluster
[root@lincloud259 ~]# curl --proto '=https' --tlsv1.2 -sSf https://tiup-mirrors.pingcap.com/install.sh | sh[root@lincloud259 ~]# . /root/.bash_profile[root@lincloud259 ~]# tiup cluster

# 配置 SSH，允许更多并发 Session[root@lincloud259 ~]# vi /etc/ssh/sshd_config[root@lincloud259 ~]# grep -i '^maxsessions' /etc/ssh/sshd_configMaxSessions 90
[root@lincloud259 ~]# systemctl restart sshd.service[root@lincloud259 ~]# sshd -T | grep -i maxsessionsmaxsessions 90

[root@lincloud259 ~]# cd /data/tidb_cluster[root@lincloud259 tidb_cluster]# vi topo.yaml# # Global variables are applied to all deployments and used as the default value of# # the deployments if a specific deployment value is missing.global: user: "tidb" ssh_port: 22 deploy_dir: "/data/tidb_cluster/tidb-deploy/jacky" data_dir: "/data/tidb_cluster/tidb-data/jacky"
# # Monitored variables are applied to all the machines.monitored: node_exporter_port: 9100 blackbox_exporter_port: 9115
server_configs: tidb:   log.slow-threshold: 300 tikv:   readpool.storage.use-unified-pool: false   readpool.coprocessor.use-unified-pool: true pd:   replication.enable-placement-rules: true tiflash:   logger.level: "info"
pd_servers: - host: 10.23.199.10
tidb_servers: - host: 10.23.199.10   port: 4000   status_port: 10080
 - host: 10.23.199.10   port: 4001   status_port: 10081
 - host: 10.23.199.10   port: 4002   status_port: 10082
tikv_servers: - host: 10.23.199.10   port: 20160   status_port: 20180
 - host: 10.23.199.10   port: 20161   status_port: 20181
 - host: 10.23.199.10   port: 20162   status_port: 20182
tiflash_servers: - host: 10.23.199.10
monitoring_servers: - host: 10.23.199.10
grafana_servers: - host: 10.23.199.10

[mysql@lincloud259 tidb_tmp]$ curl -L --remote-name-all https://s3.amazonaws.com/capitalbikeshare-data/2017-capitalbikeshare-tripdata.zip
[mysql@lincloud259 tidb_tmp]$ unzip \*-tripdata.zip
[mysql@lincloud259 tidb_tmp]$ mysql -h10.24.200.10 -P4000 -uroot
(root@bj4) [(none)]> CREATE DATABASE bikeshare;Query OK, 0 rows affected (0.11 sec)
(root@bj4) [(none)]> USE bikeshare;Database changed(root@bj4) [bikeshare]> CREATE TABLE trips (        trip_id bigint NOT NULL PRIMARY KEY AUTO_INCREMENT,        duration integer not null,        start_date datetime,        end_date datetime,        start_station_number integer,        start_station varchar(255),        end_station_number integer,        end_station varchar(255),        bike_number varchar(255),        member_type varchar(255)       );Query OK, 0 rows affected (0.15 sec)
(root@bj4) [bikeshare]> LOAD DATA LOCAL INFILE '2017Q1-capitalbikeshare-tripdata.csv' INTO TABLE trips         FIELDS TERMINATED BY ',' ENCLOSED BY '"'         LINES TERMINATED BY '\r\n'         IGNORE 1 LINES       (duration, start_date, end_date, start_station_number, start_station,       end_station_number, end_station, bike_number, member_type);Query OK, 646510 rows affected (25.24 sec)Records: 646510  Deleted: 0  Skipped: 0  Warnings: 0

[root@lincloud259 tidb_cluster]# tiup cluster edit-config jackyserver_configs:  tidb:    binlog.enable: true    binlog.ignore-error: true
Please check change highlight above, do you want to apply the change? [y/N]: yApply the change...Apply change successfully, please use `tiup cluster reload jacky [-N <nodes>] [-R <roles>]` to reload config.
[root@lincloud259 tidb_cluster]# su - tidb[tidb@lincloud259 ~]$ ssh-keygen -t rsaGenerating public/private rsa key pair.Enter file in which to save the key (/home/tidb/.ssh/id_rsa):Enter passphrase (empty for no passphrase):Enter same passphrase again:Your identification has been saved in /home/tidb/.ssh/id_rsa.Your public key has been saved in /home/tidb/.ssh/id_rsa.pub.The key fingerprint is:SHA256:x/piOeorfeNWt8gEHUDWGR/mKg/+Z5/B9mO5t43xyg4 tidb@iZ2zefd13t0f76cgvidpzlZThe key's randomart image is:+---[RSA 2048]----+|       .+o.oo    ||       .  ++ .   ||         . .o    ||        ....     ||        S.+      ||       . *o ..   ||     .  o=.oE.= .||    . . Ooo ++ @o||     o+B.+oo oO+O|+----[SHA256]-----+[tidb@lincloud259 ~]$ cd .ssh[tidb@lincloud259 .ssh]$ cat id_rsa.pub  >> authorized_keys[tidb@lincloud259 .ssh]$ ssh 10.23.199.10[tidb@lincloud259 .ssh]$ ssh 10.26.33.96The authenticity of host '10.26.33.96 (10.26.33.96)' can't be established.ECDSA key fingerprint is SHA256:CrPbE+hWNud80v12LVq1t9KAWSx09fC7xw0+CTJEi9Y.ECDSA key fingerprint is MD5:ae:30:87:d0:35:c0:9e:20:f2:77:2c:0c:55:f7:d9:11.Are you sure you want to continue connecting (yes/no)? yesWarning: Permanently added '10.26.33.96' (ECDSA) to the list of known hosts.
Welcome to Alibaba Cloud Elastic Compute Service !
[tidb@lincloud259 ~]$ exit
[tidb@lincloud259 .ssh]$ exit
[root@lincloud259 tidb_cluster]# tiup cluster reload jacky
root@lincloud259 tidb_cluster]# tiup cluster display jackyStarting component `cluster`: /root/.tiup/components/cluster/v1.0.6/tiup-cluster display jackyTiDB Cluster: jackyTiDB Version: v4.0.1ID                  Role        Host          Ports                            OS/Arch       Status   Data Dir                                            Deploy Dir--                  ----        ----          -----                            -------       ------   --------                                            ----------10.23.199.10:3000   grafana     10.23.199.10  3000                             linux/x86_64  Up       -                                                   /data/tidb_cluster/tidb-deploy/jacky/grafana-300010.23.199.10:2379   pd          10.23.199.10  2379/2380                        linux/x86_64  Up|L|UI  /data/tidb_cluster/tidb-data/jacky/pd-2379          /data/tidb_cluster/tidb-deploy/jacky/pd-237910.23.199.10:9090   prometheus  10.23.199.10  9090                             linux/x86_64  Up       /data/tidb_cluster/tidb-data/jacky/prometheus-9090  /data/tidb_cluster/tidb-deploy/jacky/prometheus-909010.23.199.10:8250   pump        10.23.199.10  8250                             linux/x86_64  Up       /data/tidb_cluster/tidb-data/jacky/pump-8250        /data/tidb_cluster/tidb-deploy/jacky/pump-825010.23.199.10:8251   pump        10.23.199.10  8251                             linux/x86_64  Up       /data/tidb_cluster/tidb-data/jacky/pump-8251        /data/tidb_cluster/tidb-deploy/jacky/pump-825110.23.199.10:8252   pump        10.23.199.10  8252                             linux/x86_64  Up       /data/tidb_cluster/tidb-data/jacky/pump-8252        /data/tidb_cluster/tidb-deploy/jacky/pump-825210.23.199.10:4000   tidb        10.23.199.10  4000/10080                       linux/x86_64  Up       -                                                   /data/tidb_cluster/tidb-deploy/jacky/tidb-400010.23.199.10:4001   tidb        10.23.199.10  4001/10081                       linux/x86_64  Up       -                                                   /data/tidb_cluster/tidb-deploy/jacky/tidb-400110.23.199.10:4002   tidb        10.23.199.10  4002/10082                       linux/x86_64  Up       -                                                   /data/tidb_cluster/tidb-deploy/jacky/tidb-400210.23.199.10:9000   tiflash     10.23.199.10  9000/8123/3930/20170/20292/8234  linux/x86_64  Up       /data/tidb_cluster/tidb-data/jacky/tiflash-9000     /data/tidb_cluster/tidb-deploy/jacky/tiflash-900010.23.199.10:20160  tikv        10.23.199.10  20160/20180                      linux/x86_64  Up       /data/tidb_cluster/tidb-data/jacky/tikv-20160       /data/tidb_cluster/tidb-deploy/jacky/tikv-2016010.23.199.10:20161  tikv        10.23.199.10  20161/20181                      linux/x86_64  Up       /data/tidb_cluster/tidb-data/jacky/tikv-20161       /data/tidb_cluster/tidb-deploy/jacky/tikv-2016110.23.199.10:20162  tikv        10.23.199.10  20162/20182                      linux/x86_64  Up       /data/tidb_cluster/tidb-data/jacky/tikv-20162       /data/tidb_cluster/tidb-deploy/jacky/tikv-20162

[root@lincloud259 local]# mkdir -m 777 /data/soft/tidb[root@lincloud259 local]# cd /data/soft/tidb[root@lincloud259 tidb]# wget -c 'https://download.pingcap.org/tidb-enterprise-tools-nightly-linux-amd64.tar.gz' -O tidb-enterprise-tools-nightly-linux-amd64.tar.gz[root@lincloud259 tidb]# cp -rp tidb-enterprise-tools-nightly-linux-amd64.tar.gz /usr/local/[root@lincloud259 tidb]# cd /usr/local/[root@lincloud259 local]# tar zxpf tidb-enterprise-tools-nightly-linux-amd64.tar.gz
[root@lincloud259 local]# rm -f tidb-enterprise-tools-nightly-linux-amd64.tar.gz[root@lincloud259 local]# ln -s /usr/local/tidb-enterprise-tools-nightly-linux-amd64/ /usr/local/tidb_tools[root@lincloud259 local]# lsaegis  bin  etc  games  include  lib  lib64  libexec  sbin  share  src  tidb-enterprise-tools-nightly-linux-amd64  tidb_tools[root@lincloud259 bin]# vi /etc/profile# Add for /usr/local utilitiesPATH=$PATH:/usr/local/tidb_tools/binexport PATH
[root@lincloud259 bin]# . /etc/profile[root@lincloud259 bin]# which mydumper/usr/local/tidb_tools/bin/mydumper

[root@lincloud259 bin]# cd /data/tidb_cluster/[root@lincloud259 tidb_cluster]# mkdir -p tidb_backup/jacky[root@lincloud259 tidb_cluster]# cd tidb_backup/jacky/[root@lincloud259 jacky]# mkdir `date '+%d-%m-%Y_%H_%M_%S'`[root@lincloud259 ]# mydumper -h 127.0.0.1 -P 4000 -u root -t 32 -F 256 -B bikeshare --skip-tz-utc -o ./[root@lincloud259 ]# lsbikeshare-schema-create.sql  bikeshare.trips-schema.sql  bikeshare.trips.sql  metadata
[root@lincloud259 22-03-2024_14_46_23]# cat metadataStarted dump at: 2024-03-22 14:47:02SHOW MASTER STATUS:  Log: tidb-binlog  Pos: 417545171224756227  GTID:
Finished dump at: 2024-03-22 14:47:05

[mysql@lincloud259 tidb_tmp]$ ls2024-lincloud259-tripdata.zip  2024Q1-capitalbikeshare-tripdata.csv  2024Q2-capitalbikeshare-tripdata.csv  2017Q3-capitalbikeshare-tripdata.csv  2017Q4-capitalbikeshare-tripdata.csv[mysql@lincloud259 tidb_tmp]$ mysql -h10.24.200.10 -P4000 -uroot(root@bj4) [(none)]> use bikeshare(root@bj4) [bikeshare]> load data local infile '2024Q2-capitalbikeshare-tripdata.csv' into table trips FIELDS TERMINATED BY ',' ENCLOSED BY '"'   LINES TERMINATED BY '\r\n'   IGNORE 1 LINES (duration, start_date, end_date, start_station_number, start_station, end_station_number, end_station, bike_number, member_type);Query OK, 1104418 rows affected (44.30 sec)Records: 1104418  Deleted: 0  Skipped: 0  Warnings: 0

[root@lincloud259 22-03-2024_14_46_23]# cd /data/soft[root@lincloud259 soft]# mkdir mysql && cd mysql[root@lincloud259 mysql]# lsmysql-5.7.24-linux-glibc2.12-x86_64.tar.gz[root@lincloud259 mysql]# cp -rp mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz /usr/local/c[root@lincloud259 mysql]# cd /usr/local[root@lincloud259 local]# tar zxpf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz[root@lincloud259 local]# ln -s /usr/local/mysql-5.7.24-linux-glibc2.12-x86_64/ /usr/local/mysql[root@lincloud259 local]# vi /etc/profile# Add for /usr/local utilitiesPATH=$PATH:/usr/local/tidb_tools/bin:/usr/local/mysql/binexport PATH[root@lincloud259 local]# . /etc/profile[root@lincloud259 local]# which mysql/usr/local/mysql/bin/mysql
[root@lincloud259 local]# groupadd -g 500 mysql[root@lincloud259 local]# useradd -u 500 -g 500 -G disk -d /home/mysql -s /bin/bash -m mysql[root@lincloud259 local]# passwd mysql
[root@lincloud259 local]# cd /data[root@lincloud259 data]# mkdir -p mysql_data/jacky[root@lincloud259 data]# chown -R mysql:mysql mysql_data/
[root@lincloud259 data]# su - mysql[mysql@lincloud259 ~]$ cd /data/mysql_data/[mysql@lincloud259 mysql_data]$ vi my.cnf
# The MySQL server[mysqld]# Basic parametersuser  = mysqlport  = 3701character_set_server      = utf8mb4skip-character-set-client-handshake  = 1basedir          = /usr/local/mysqldatadir          = /data/mysql_data/jackypid-file        = /data/mysql_data/jacky/mysql.pidsocket          = /data/mysql_data/jacky/mysql.socklower_case_table_names      = 1
# Logginglog_error        = error.loglog_queries_not_using_indexes    = 0long_query_time        = 1slow_query_log        = 1slow_query_log_file      = mysql-slow.log
# Binary Logginglog_bin          = binlogbinlog_format        = rowbinlog_row_image      = FULLexpire_logs_days      = 0sync_binlog        = 1
# Replicationserver_id        = 1819034970master-info-repository      = filerelay-log-info_repository    = filegtid-mode        = onenforce-gtid-consistency    = true
# InnoDBinnodb_data_file_path=ibdata1:200M:autoextendinnodb_buffer_pool_size = 1Ginnodb_file_per_table = ONinnodb_flush_log_at_trx_commit = 1innodb_flush_method = O_DIRECTinnodb_log_files_in_group=2innodb_log_file_size=1048576000innodb_io_capacity = 1000innodb_io_capacity_max = 2000innodb_large_prefix = ONinnodb_undo_directory=./
# Caches & Limitsmax_connections = 500max_allowed_packet = 1048576000
[root@lincloud259 ~]# mysqld --defaults-file=/data/mysql_data/my.cnf --initialize
[root@lincloud259 ~]# cp -rp /data/mysql_data/my.cnf /data/mysql_data/jacky/my.cnf
[root@lincloud259 ~]# nohup mysqld_safe --defaults-file=/data/mysql_data/jacky/my.cnf &[root@lincloud259 ~]# netstat -nlt | grep 3701tcp6       0      0 :::3701                 :::*                    LISTEN
[root@lincloud259 ~]# su - mysql
[mysql@lincloud259 ~]$ cd /data/mysql_data/jacky/
[mysql@lincloud259 jacky]$ grep -i root ./error.log2024-03-22T08:38:07.961728Z 1 [Note] A temporary password is generated for root@localhost: ZTy-48D,/r0k
[mysql@lincloud259 jacky]$ mysql -hlocalhost -uroot -p'ZTy-48D,/r0k' -S ./mysql.sock
mysql> set password=password('xxx');Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all privileges on *.* to 'root'@'%' identified by 'xxx' with grant option;Query OK, 0 rows affected, 1 warning (0.00 sec)
## ECS 控制台修改安全组，允许公网 3701 访问，然后验证下远程可以访问
[mysql@lincloud259 tidb_tmp]$ mysql -h10.24.200.10 -uroot -pxxx -P3701(root@bj4) [(none)]> quit

[root@lincloud259 22-03-2024_14_46_23]# loader -L info -P 3701 -checkpoint-schema="tidb_loader" -d ./ -h 10.24.200.10 -t 32 -u root -p 'xxx'2024/03/22 21:20:38 printer.go:52: [info] Welcome to loader2024/03/22 21:20:38 printer.go:53: [info] Release Version: v1.0.0-79-gf505ab32024/03/22 21:20:38 printer.go:54: [info] Git Commit Hash: f505ab3ce55cd9cbb29e2346317164055a1b1c152024/03/22 21:20:38 printer.go:55: [info] Git Branch: master2024/03/22 21:20:38 printer.go:56: [info] UTC Build Time: 2024-03-23 02:40:182024/03/22 21:20:38 printer.go:57: [info] Go Version: go version go1.13 linux/amd642024/03/22 21:20:38 main.go:51: [info] config: {"log-level":"info","log-file":"","status-addr":":8272","pool-size":32,"dir":"./","db":{"host":"10.24.200.10","user":"root","port":3701,"sql-mode":"@DownstreamDefault","max-allowed-packet":67108864},"checkpoint-schema":"tidb_loader","config-file":"","route-rules":null,"do-table":null,"do-db":null,"ignore-table":null,"ignore-db":null,"rm-checkpoint":false}2024/03/22 21:20:38 loader.go:532: [info] [loader] prepare takes 0.000142 seconds2024/03/22 21:20:38 checkpoint.go:207: [info] calc checkpoint finished. finished tables (map[])2024/03/22 21:20:38 loader.go:715: [info] [loader][run db schema].//bikeshare-schema-create.sql[start]2024/03/22 21:20:38 loader.go:720: [info] [loader][run db schema].//bikeshare-schema-create.sql[finished]2024/03/22 21:20:38 loader.go:736: [info] [loader][run table schema].//bikeshare.trips-schema.sql[start]2024/03/22 21:20:38 loader.go:741: [info] [loader][run table schema].//bikeshare.trips-schema.sql[finished]2024/03/22 21:20:38 loader.go:773: [info] [loader] create tables takes 0.037831 seconds2024/03/22 21:20:38 loader.go:788: [info] [loader] all data files have been dispatched, waiting for them finished2024/03/22 21:20:38 loader.go:158: [info] [loader][restore table data sql].//bikeshare.trips.sql[start]2024/03/22 21:20:38 loader.go:216: [info] data file bikeshare.trips.sql scanned finished.2024/03/22 21:20:43 status.go:32: [info] [loader] finished_bytes = 32997729, total_bytes = GetAllRestoringFiles91120404, progress = 36.21 %2024/03/22 21:20:48 status.go:32: [info] [loader] finished_bytes = 68995049, total_bytes = GetAllRestoringFiles91120404, progress = 75.72 %2024/03/22 21:20:51 loader.go:165: [info] [loader][restore table data sql].//bikeshare.trips.sql[finished]2024/03/22 21:20:51 loader.go:791: [info] [loader] all data files has been finished, takes 13.170611 seconds2024/03/22 21:20:51 status.go:32: [info] [loader] finished_bytes = 91120404, total_bytes = GetAllRestoringFiles91120404, progress = 100.00 %2024/03/22 21:20:51 main.go:88: [info] loader stopped and exits
[root@iZ2ze5ffbqqbeatcv1j01yZ 22-03-2024_14_46_23]# mysql -h10.24.200.10 -P3701 -uroot -p'xxx' bikesharemysql> show tables;+---------------------+| Tables_in_bikeshare |+---------------------+| trips               |+---------------------+1 row in set (0.00 sec)
mysql> select count(*) from trips;+----------+| count(*) |+----------+|   646510 |+----------+1 row in set (0.15 sec)

[mysql@lincloud259 tidb_tmp]$ mysql -h10.24.200.10 -uroot -P4000
(root@bj4) [(none)]> use bikeshareReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A
Database changed(root@bj4) [bikeshare]> select count(*) from trips;+----------+| count(*) |+----------+|  1750928 |+----------+1 row in set (0.40 sec)
(root@bj4) [bikeshare]> load data local infile '2024Q3-capitalbikeshare-tripdata.csv' into table trips FIELDS TERMINATED BY ',' ENCLOSED BY '"'   LINES TERMINATED BY '\r\n'   IGNORE 1 LINES (duration, start_date, end_date, start_station_number, start_station, end_station_number, end_station, bike_number, member_type);Query OK, 1191585 rows affected (54.81 sec)Records: 1191585  Deleted: 0  Skipped: 0  Warnings: 0
(root@bj4) [bikeshare]> select count(*) from trips;+----------+| count(*) |+----------+|  2942513 |+----------+1 row in set (0.64 sec)

[root@lincloud259 tidb_cluster]# mysql -h10.24.200.10 -P3701 -uroot -p'xxx' bikeshare
mysql> select count(*) from trips;+----------+| count(*) |+----------+|  2942513 |+----------+1 row in set (0.71 sec)
mysql>