Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 【SOP 系列 36】数据库备份与恢复（TiDB）
|
username: Billmay表妹
Thanks to @会飞的土拨鼠 for sharing
Backup Tools
1. Tool Download Address
# In the download link, {version} is the version number of Dumpling. For example, the download link for version v5.2.2 is
https://download.pingcap.org/tidb-toolkit-v5.2.2-linux-amd64.tar.gz
2. Tool Content Details
[root@ncayu8847 tidb-toolkit-v5.2.2-linux-amd64]# cd bin/
[root@ncayu8847 bin]# ll
Total 535180
-rwxr-xr-x 1 root root 115875760 Oct 29 11:26 br
-rwxr-xr-x 1 root root 79583835 Oct 29 11:26 dumpling
-rwxr-xr-x 1 root root 12782832 Oct 29 11:26 mydumper
-rwxr-xr-x 1 root root 17258378 Oct 29 11:26 pd-tso-bench
-rwxr-xr-x 1 root root 71600963 Oct 29 11:26 sync_diff_inspector
-rwxr-xr-x 1 root root 129306304 Oct 29 11:26 tidb-lightning
-rwxr-xr-x 1 root root 121594400 Oct 29 11:26 tidb-lightning-ctl
It is officially recommended to use the br tool for data backup and recovery. Due to environmental limitations, we are currently using the dumpling tool for backup and the tidb-lightning tool for data recovery.
Database Backup with Dumpling
Backup Command
# Backup command:
./dumpling -P 4000 -h 192.168.70.133 -u root -p ncayu123456 --filetype sql -B ncayu_test -t 1 --loglevel warn -F 64MiB -o /data1/tidb_tool/backup/ncayu_test1130
# Parameter Explanation:
-P Port
-h Database IP address
-u Database user
-p Database password
--filetype sql Save backup file as SQL type
-B Specify the name of the database to be backed up
-t Specify the number of threads for export
--loglevel Output log level
Log levels {debug, info, warn, error, dpanic, panic, fatal} Default is "info"
-F 64MiB Specify the size of a single SQL backup file
-o /data1/tidb_tool/backup/ncayu_test1130 Specify the path to store the backup files
Database Recovery with tidb-lightning
1. Start tidb-lightning
It is recommended to run tidb-lightning. If you start the program directly in the command line with nohup, it may exit due to the SIGHUP signal. It is recommended to put nohup in a script.
Method 1: Start the program first, then add the import configuration on the page
mkdir /tmp/tidb-lightning
#!/bin/bash
nohup ./tidb-lightning --server-mode --status-addr :8289 > /tmp/tidb-lightning/lightning.log 2>&1 &
Method 2: Modify the configuration file directly and start
#!/bin/bash
nohup ./tidb-lightning -config tidb-lightning.toml > nohup.out &
2. Content of tidb-lightning.toml File
[lightning]
# Log
level = "info"
file = "/data1/tidb_tool/backup/push_log.log"
#[checkpoint]
#enable = true
# Name of the database to store checkpoints.
#schema = "tidb_lightning_checkpoint"
# Method to store checkpoints.
# - file: Store in the local file system.
# - mysql: Store in a MySQL-compatible database server.
#driver = "file"
[tikv-importer]
# Set backend to local mode
backend = "local"
# Set the local temporary storage path
sorted-kv-dir = "/tmp/sorted-kv-dir"
[mydumper]
# Mydumper source data directory.
data-source-dir = "/data1/tidb_tool/backup/ncayu_test1201"
[tidb]
# Information of the target cluster. The listening address of tidb-server, fill in one.
# Note here, use the TiDB IP address from the Cluster Info in the TiDB management console
host = "192.168.70.133"
port = 4000
user = "root"
password = "ncayu123456"
# Table schema information is obtained from the "status port" of TiDB.
status-port = 10080
# Address of pd-server, fill in one
# Note here, use the PD information from the Cluster Info in the TiDB management console
pd-addr = "192.168.70.133:2379"
In the tidb-lightning.toml file, for different TiDB databases, you need to modify the corresponding database information and the data content path.
3. Adjust the GC Time of the TiDB Cluster
If the data volume is large, it is best to extend the GC (garbage collection) time before importing.
# Adjust the GC time of the TiDB cluster
SELECT * FROM mysql.tidb WHERE VARIABLE_NAME = 'tikv_gc_life_time';
update mysql.tidb set VARIABLE_VALUE = '10h' where VARIABLE_NAME = 'tikv_gc_life_time';
# Change back to the original value after the import is complete
update mysql.tidb set VARIABLE_VALUE = '10m' where VARIABLE_NAME = 'tikv_gc_life_time';
4. Note the Abnormal Exit of Lightning
# If tidb-lightning exits abnormally, the cluster may still be in "import mode", which is not suitable for production environments. You can execute the following command to check the current mode:
tidb-lightning-ctl --fetch-mode
# You can execute the following command to force switch back to "normal mode":
tidb-lightning-ctl --switch-mode=normal
5. Import Completion
After the import is complete, TiDB Lightning will automatically exit. If the import is successful, the last line of the log will show
tidb lightning exit.
Part of the Logs During Data Import
1. nohup.out File
[root@ncayu618 bin]# tail -f nohup.out
Verbose debug logs will be written to /data1/tidb_tool/backup/push_log.log
+---+------------------------------------------------------------------------------------------------+-------------+--------+
| # | CHECK ITEM | TYPE | PASSED |
+---+------------------------------------------------------------------------------------------------+-------------+--------+
| 1 | Source csv files size is proper | performance | true |
+---+------------------------------------------------------------------------------------------------+-------------+--------+
| 2 | checkpoints are valid | critical | true |
+---+------------------------------------------------------------------------------------------------+-------------+--------+
| 3 | table schemas are valid | critical | true |
+---+------------------------------------------------------------------------------------------------+-------------+--------+
| 4 | Cluster is available | critical | true |
+---+------------------------------------------------------------------------------------------------+-------------+--------+
| 5 | Lightning has the correct storage permission | critical | true |
+---+------------------------------------------------------------------------------------------------+-------------+--------+
| 6 | local disk resources are rich, estimate sorted data size 7.689GiB, local available is 45.29GiB | critical | true |
+---+------------------------------------------------------------------------------------------------+-------------+--------+
| 7 | Cluster capacity is rich, capacity is 2.402TiB, we need 23.07GiB | critical | true |
+---+------------------------------------------------------------------------------------------------+-------------+--------+
2. push_log.log Log File
[2021/12/01 16:34:40.805 +08:00] [INFO] [restore.go:1221] ["add back PD leader®ion schedulers"]
[2021/12/01 16:34:40.805 +08:00] [INFO] [restore.go:1224] ["cleanup task metas"]
[2021/12/01 16:34:42.973 +08:00] [INFO] [restore.go:1173] ["cancel periodic actions"] [do=true]
[2021/12/01 16:34:42.974 +08:00] [INFO] [restore.go:1038] ["switch to normal mode"]
[2021/12/01 16:34:43.310 +08:00] [WARN] [meta_manager.go:843] ["there are unfinished table in table meta table, cleanup skipped."]
[2021/12/01 16:34:43.310 +08:00] [INFO] [restore.go:1488] ["skip full compaction"]
[2021/12/01 16:34:43.318 +08:00] [INFO] [restore.go:1660] ["clean checkpoints start"] [keepAfterSuccess=false] [taskID=1638344968777845215]
[2021/12/01 16:34:43.319 +08:00] [INFO] [restore.go:1667] ["clean checkpoints completed"] [keepAfterSuccess=false] [taskID=1638344968777845215] [takeTime=105.15µs] []
[2021/12/01 16:34:43.319 +08:00] [INFO] [restore.go:431] ["the whole procedure completed"] [takeTime=45m14.509755009s] []
[2021/12/01 16:34:43.320 +08:00] [INFO] [main.go:93] ["tidb lightning exit"]
[2021/12/01 16:34:43.320 +08:00] [INFO] [checksum.go:468] ["service safe point keeper exited"]
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: