|
|
Posted on 9/30/2021 2:42:47 PM
|
|
|
|
Scheduled database backup is a must-try for every developer, and regular backup data can solve the problem of accidentally deleting some data, which can be restored to a certain moment and minimize losses.
MySQL provides a convenient tool for exporting database data and files from the command line, mysqldump, which can export databases through the command line.
Review:
This article is usedCentOS 7environment, backing up remote databases to the local via mysqldump. If we run the mysqldump command directly, the error will be as follows:
-bash: mysqldump: command not found
GitHub address:The hyperlink login is visible.
The installation command is as follows:
In this way, both mydumper and myloader commands can already be used, mydumper is responsible for exporting, and myloader is responsible for importing, as shown in the figure below:
mydumperParameter explanation
-B, --database The database to be backed up, if not specified, back up all libraries
-T, --tables-list The tables that need to be backed up, the names are separated by commas
-o, --outputdir to back up the directory where the file is output
-s, --statement-size The number of bytes of the insert statement generated by --statement-size is 1000000 by default
-r, --rows When dividing a table into rows, specify the number of block rows, specifying this option will turn off --chunk-filesize
-F, --chunk-filesize When chunking a table by size, the specified block size in MB
-c, --compress compress compress the output file
-e, --build-empty-files if the table data is empty, or it produces an empty file (by default no data only the table structure file)
-x, --regex is the same as the regular expression matching 'db.table'
-i, --ignore-engines ignore storage engines, and use thick partitioning
-m, --no-schemas do not back up table structures
-k, --no-locks do not use temporary shared read-only locks, and using this option will cause data inconsistencies
--less-locking reduces the time it takes to apply locks to InnoDB tables (the mechanics of this pattern are explained in detail below)
-l, --long-query-guard Set the timeout time for long queries that block backups, in seconds, and the default is 60 seconds (mydumper will exit by default after the timeout).
--kill-long-queries kill long queries (without exiting)
-b, --binlogs export binlogs
-D, --daemon enable daemon mode, which backs up the database at certain intervals without interruption
-I, --snapshot-interval dump Snapshot interval time, default 60s, needs to be in daemon mode
-L, --logfile uses the log file name (the log generated by mydumper), and uses standard output by default
--tz-utc across time zones is an option used, not explained
--skip-tz-utc Id
--use-savepoints Use savepoints to reduce lock time caused by metadata collection, which requires SUPER permissions
--success-on-1146 Not increment error count and Warning instead of Critical in case of table doesn't exist
-h, --host The hostname of the connection
-u, --user back up the user used
-p, --password password
-P, --port port
-S, --socket The socket file when using socket communication
-t, --threads The default number of backup threads enabled is 4
-C, --compress-protocol compresses data communicating with mysql
-V, --version displays the version number
-v, --verbose output information pattern, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default to 2
-d, --directory folder for the backup file
-q, --queries-per-transaction The number of queries executed per thing is 1000 by default
-o, --overwrite-tables If the table to be restored exists, drop the table first, use this parameter, and back up the table structure when you need to back up
-B, --database The database that needs to be restored
-e, --enable-binlog enables binary logs to restore data
-h, --hosthost
-u, --user restored
-p, --password password
-P, --port port
-S, --socket socket file
-t, --threads restores the number of threads used, the default is 4
-C, --compress-protocol
-V, --version displays the version
-v, --verbose output mode, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default to 2
The database export command is as follows:
After execution, mydumper will create a specified directory address that we pass through parameters, which contains a metadata file that records the binary log file name at the backup time and the location where the log was written. Each table has two backup files:database.table-schema.sql a table structure file, and database.table.sql table data file.
Scheduled backups
The reference articles are as follows:
Let's create a new shell script named :backup_uat.sh, as follows:
Grant Execution Permissions:
Add a task
The task is performed every day at 00:10 am
Reload the configuration
(End)
|
Previous:Linux environment basics: the difference between installing el6, el7, and el8 in rpm packagesNext:RAID Technology Explained - RAID0, RAID1, RAID3, RAID5, RAID6, RAID10 comparison
|
Landlord|
Posted on 9/30/2021 5:03:15 PM
|
Posted on 10/2/2021 4:58:15 PM
|
