# 中型数据库（10GB-100GB）
– 完全备份：每周日凌晨2:00
– 增量备份：每天凌晨2:00

# 关键表备份：每天凌晨3:00

avg-cpu: %user %nice %system %iowait %steal %idle
10.00 0.00 5.00 2.00 0.00 83.00

device r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
nvme0n1 10.00 5.00 0.20 0.10 42.67 0.10 6.67 5.00 10.00 2.00 3.00

# 备份中
# iostat -x 1 10
Linux 5.14.0-362.el9.x86_64 (fgedu.net.cn) 04/01/2026 _x86_64_ (16 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
20.00 0.00 10.00 5.00 0.00 65.00

device r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
nvme0n1 50.00 10.00 2.00 0.20 68.27 0.50 8.33 6.00 16.67 3.00 18.00

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Dumping structure and contents of MySQL databases and tables.

Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] –databases [OPTIONS] DB1 [DB2 DB3…]
OR mysqldump [OPTIONS] –all-databases [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
The following groups are read: mysqldump client
The following options may be given as the first argument:
–print-defaults Print the program argument list and exit.
–no-defaults Don’t read default options from any option file.
–defaults-file=# Only read default options from the given file #.
–defaults-extra-file=# Read this file after the global files are read.

-A, –all-databases Dump all databases.
-B, –databases Dump several databases. Note the difference in usage;
in this case no tables are given.
Use this option if you want to dump multiple databases
or an entire MySQL server.
-C, –compress Use compression in server/client protocol.
-E, –events Dump events.
-F, –flush-logs Flush logs before starting dump.
-l, –lock-tables Lock all tables for read.
-n, –no-create-db Suppress the CREATE DATABASE … IF EXISTS statement
that normally is included in the output.
-t, –no-create-info Don’t write CREATE TABLE statements.
-d, –no-data No row information.
-O, –set-variable var=option
Set a variable=value, e.g., -O max_allowed_packet=16M.
-p, –password[=name] Password to use when connecting to server.
If password is not given, it’s solicited on the tty.
-P, –port=# Port number to use for connection.
-q, –quick Don’t buffer result, dump directly to stdout.
-r, –result-file=name
Direct output to a given file.
-S, –socket=name The socket file to use for connection.
-u, –user=name User for login if not current user.
-v, –verbose Print info about the various stages.
-V, –version Print version information and exit.
-X, –xml Dump a database as well formed XML.
–add-drop-database Add a DROP DATABASE before each CREATE DATABASE.
–add-drop-table Add a DROP TABLE before each CREATE TABLE.
–add-locks Add locks around INSERT statements.
–all-tablespaces Dump all tablespaces.
–allow-keywords Allow creation of column names that are keywords.
–apply-slave-statements
Add statements to start replication on the slave.
–character-sets-dir=name
Directory where character sets are installed.
–comments Add comments to the dump file.
–compatible=name Change the dump to be compatible with a given mode.
By default tables are dumped in a format optimized for
MySQL. Legal modes are: ansi, mysql323, mysql40,
postgresql, oracle, mssql, db2, maxdb, no_key_options,
no_table_options, no_field_options. One can use several
modes separated by commas.
–complete-insert Use complete INSERT statements that include column names.
–create-options Include all MySQL specific create options.
–databases Dump several databases.
–debug[=name] Write a debugging log.
–debug-check Check memory and open file usage at exit.
–default-character-set=name
Set the default character set.
–delayed-insert Insert rows with INSERT DELAYED.
–delete-master-logs
Delete logs on master after backup.
–disable-keys For MyISAM tables, disable keys during INSERT.
–dump-date Include dump date in output.
–events Dump events.
–extended-insert Use multiple-row INSERT syntax that include several
VALUES lists.
–fields-terminated-by=name
Fields in the output file are terminated by the given
string.
–fields-enclosed-by=name
Fields in the output file are enclosed by the given
character.
–fields-optionally-enclosed-by=name
Fields in the output file are optionally enclosed by the
given character.
–fields-escaped-by=name
Fields in the output file are escaped by the given
character.
–first-slave Deprecated, use –source instead.
–flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the
mysql database.
–force Continue even if an SQL error occurs.
–hex-blob Dump binary columns in hexadecimal format.
–host=name Connect to host.
–ignore-table=name Do not dump the specified table. To specify more than
one table to ignore, use the directive multiple times,
once for each table. Each table must be specified with
both database and table names, e.g.,
–ignore-table=database.table.
–include-master-host-port
Include MASTER_HOST and MASTER_PORT in CHANGE MASTER
statement.
–insert-ignore Insert rows with INSERT IGNORE.
–lines-terminated-by=name
Lines in the output file are terminated by the given
string.
–lock-all-tables Lock all tables across all databases.
–log-error=name Append warnings and errors to the given file.
–master-data[=#] Write the binary log position and filename to the output.
1 = write as a CHANGE MASTER statement, 2 = write as a
comment.
–max-allowed-packet=#
The maximum packet length to send to or receive from
server.
–net-buffer-length=#
The buffer size for TCP/IP and socket communication.
–no-autocommit Wrap tables with autocommit/commit statements.
–no-data No row information.
–no-set-names Don’t write SET NAMES statement.
–opt Same as –add-drop-table, –add-locks, –create-options,
–disable-keys, –extended-insert, –lock-tables,
–quick, –set-charset.
Enabled by default, disable with –skip-opt.
–order-by-primary Order rows by primary key or first unique index.
–pipe Use named pipes to connect to server.
–plugin-dir=name Directory for client-side plugins.
–port=name Port number to use for connection.
–protocol=name The protocol to use for connection (tcp, socket, pipe, memory).
–quick Don’t buffer result, dump directly to stdout.
–quote-names Quote table and column names with backticks.
–replace Use REPLACE INTO instead of INSERT INTO.
–result-file=name Direct output to a given file.
–routines Dump stored routines (functions and procedures).
–set-charset Add SET NAMES default_character_set to the output.
–shared-memory-base-name=name
Base name of shared memory.
–single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction.
–skip-add-drop-table
Don’t add a DROP TABLE statement before each CREATE
TABLE statement.
–skip-add-locks Don’t add locks around INSERT statements.
–skip-comments Don’t add comments to the dump file.
–skip-disable-keys
Don’t disable keys during INSERT statements.
–skip-extended-insert
Turn off extended-insert.
–skip-lock-tables Don’t lock tables during the dump.
–skip-quick Disable the –quick option.
–skip-set-charset Don’t add SET NAMES statement.
–skip-tz-utc Don’t convert TIMESTAMP columns to UTC.
–socket=name The socket file to use for connection.
–ssl Enable SSL for connection (automatically enabled with
other flags).
–ssl-ca=name CA file in PEM format.
–ssl-capath=name CA directory.
–ssl-cert=name X509 cert in PEM format.
–ssl-cipher=name SSL cipher to use.
–ssl-key=name X509 key in PEM format.
–ssl-crl=name Certificate revocation list.
–ssl-crlpath=name Certificate revocation list path.
–ssl-mode=name SSL connection mode (disabled|preferred|required|verify_ca|verify_identity).
–ssl-verify-server-cert
Verify server’s “Common Name” in its cert against
hostname used when connecting. This option is disabled by
default.
–tables Overrides the –databases (-B) option and specifies
table names to dump. Note that using this option
without –databases (-B) will result in no CREATE
DATABASE statement being written to the dump file.
–triggers Dump triggers for each dumped table.
–tz-utc Convert TIMESTAMP columns to UTC.
–user=name User for login if not current user.
–verbose Print info about the various stages.
–version Print version information and exit.
–where=name Dump only rows selected by the given WHERE condition.
–xml Dump a database as well formed XML.
–plugin-auth=name The plugin to use for authentication.
–column-statistics[=name]
Control column statistics generation. Enabled by default.
–enable-cleartext-plugin
Enable/disable the clear text authentication plugin.
–default-auth=name Default authentication client-side plugin to use.
–server-public-key-path=name
File path to the server public RSA key in PEM format.
–get-server-public-key
Get server public key from the server.
–connect-timeout=# Number of seconds before connection timeout.
–max-connect-errors=#
Maximum number of interrupted connections before
aborting.
–net-read-timeout=#
Number of seconds to wait for more data from the server.
–net-write-timeout=#
Number of seconds to wait for a block to be written to
the server.
–select-limit=# The maximum number of rows to include in a SELECT
statement.
–show-create-skip-secondary-indexes
Don’t include CREATE INDEX statements for secondary
indexes in SHOW CREATE TABLE output.
–zstd-compression-level=#
The compression level to use for connections to the
server that use zstd compression.
–compression-algorithms=#
A comma-separated list of compression algorithms to
use for connections to the server.
–ssl-fips-mode={OFF|ON|STRICT}
FIPS mode for SSL operations.
–tls-version=# TLS version to use, permitted values are:
TLSv1, TLSv1.1, TLSv1.2, TLSv1.3
–allow-deprecated-client-plugin
Allow the use of deprecated client plugins.
–enable-cleartext-plugin
Enable the clear text authentication plugin.
–password-validation-plugin
Plugin to use for password validation.
–login-path=name Read login path options from .mylogin.cnf.
–connect-expired-password
Indicate that the password provided is expired and that
the client should prompt the user to set a new one.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Dumping structure and contents of MySQL databases and tables.

Usage: mysqlpump [OPTIONS] [database [tables]]
OR mysqlpump [OPTIONS] –databases [OPTIONS] DB1 [DB2 DB3…]
OR mysqlpump [OPTIONS] –all-databases [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
The following groups are read: mysqlpump client
The following options may be given as the first argument:
–print-defaults Print the program argument list and exit.
–no-defaults Don’t read default options from any option file.
–defaults-file=# Only read default options from the given file #.
–defaults-extra-file=# Read this file after the global files are read.

-A, –all-databases Dump all databases.
-B, –databases Dump several databases. Note the difference in usage;
in this case no tables are given.
Use this option if you want to dump multiple databases
or an entire MySQL server.
-C, –compress Use compression in server/client protocol.
-E, –events Dump events.
-F, –flush-logs Flush logs before starting dump.
-l, –lock-tables Lock all tables for read.
-n, –no-create-db Suppress the CREATE DATABASE … IF EXISTS statement
that normally is included in the output.
-t, –no-create-info Don’t write CREATE TABLE statements.
-d, –no-data No row information.
-O, –set-variable var=option
Set a variable=value, e.g., -O max_allowed_packet=16M.
-p, –password[=name] Password to use when connecting to server.
If password is not given, it’s solicited on the tty.
-P, –port=# Port number to use for connection.
-q, –quick Don’t buffer result, dump directly to stdout.
-r, –result-file=name
Direct output to a given file.
-S, –socket=name The socket file to use for connection.
-u, –user=name User for login if not current user.
-v, –verbose Print info about the various stages.
-V, –version Print version information and exit.
-X, –xml Dump a database as well formed XML.
–add-drop-database Add a DROP DATABASE before each CREATE DATABASE.
–add-drop-table Add a DROP TABLE before each CREATE TABLE.
–add-locks Add locks around INSERT statements.
–all-tablespaces Dump all tablespaces.
–allow-keywords Allow creation of column names that are keywords.
–apply-slave-statements
Add statements to start replication on the slave.
–character-sets-dir=name
Directory where character sets are installed.
–comments Add comments to the dump file.
–compatible=name Change the dump to be compatible with a given mode.
By default tables are dumped in a format optimized for
MySQL. Legal modes are: ansi, mysql323, mysql40,
postgresql, oracle, mssql, db2, maxdb, no_key_options,
no_table_options, no_field_options. One can use several
modes separated by commas.
–complete-insert Use complete INSERT statements that include column names.
–create-options Include all MySQL specific create options.
–databases Dump several databases.
–debug[=name] Write a debugging log.
–debug-check Check memory and open file usage at exit.
–default-character-set=name
Set the default character set.
–delayed-insert Insert rows with INSERT DELAYED.
–delete-master-logs
Delete logs on master after backup.
–disable-keys For MyISAM tables, disable keys during INSERT.
–dump-date Include dump date in output.
–events Dump events.
–extended-insert Use multiple-row INSERT syntax that include several
VALUES lists.
–fields-terminated-by=name
Fields in the output file are terminated by the given
string.
–fields-enclosed-by=name
Fields in the output file are enclosed by the given
character.
–fields-optionally-enclosed-by=name
Fields in the output file are optionally enclosed by the
given character.
–fields-escaped-by=name
Fields in the output file are escaped by the given
character.
–first-slave Deprecated, use –source instead.
–flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the
mysql database.
–force Continue even if an SQL error occurs.
–hex-blob Dump binary columns in hexadecimal format.
–host=name Connect to host.
–ignore-table=name Do not dump the specified table. To specify more than
one table to ignore, use the directive multiple times,
once for each table. Each table must be specified with
both database and table names, e.g.,
–ignore-table=database.table.
–include-master-host-port
Include MASTER_HOST and MASTER_PORT in CHANGE MASTER
statement.
–insert-ignore Insert rows with INSERT IGNORE.
–lines-terminated-by=name
Lines in the output file are terminated by the given
string.
–lock-all-tables Lock all tables across all databases.
–log-error=name Append warnings and errors to the given file.
–master-data[=#] Write the binary log position and filename to the output.
1 = write as a CHANGE MASTER statement, 2 = write as a
comment.
–max-allowed-packet=#
The maximum packet length to send to or receive from
server.
–net-buffer-length=#
The buffer size for TCP/IP and socket communication.
–no-autocommit Wrap tables with autocommit/commit statements.
–no-data No row information.
–no-set-names Don’t write SET NAMES statement.
–opt Same as –add-drop-table, –add-locks, –create-options,
–disable-keys, –extended-insert, –lock-tables,
–quick, –set-charset.
Enabled by default, disable with –skip-opt.
–order-by-primary Order rows by primary key or first unique index.
–parallel-schemas=name
Number of parallel threads to use for schemas.
–parallel-tables=name
Number of parallel threads to use for tables.
–pipe Use named pipes to connect to server.
–plugin-dir=name Directory for client-side plugins.
–port=name Port number to use for connection.
–protocol=name The protocol to use for connection (tcp, socket, pipe, memory).
–quick Don’t buffer result, dump directly to stdout.
–quote-names Quote table and column names with backticks.
–replace Use REPLACE INTO instead of INSERT INTO.
–result-file=name Direct output to a given file.
–routines Dump stored routines (functions and procedures).
–set-charset Add SET NAMES default_character_set to the output.
–shared-memory-base-name=name
Base name of shared memory.
–single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction.
–skip-add-drop-table
Don’t add a DROP TABLE statement before each CREATE
TABLE statement.
–skip-add-locks Don’t add locks around INSERT statements.
–skip-comments Don’t add comments to the dump file.
–skip-disable-keys
Don’t disable keys during INSERT statements.
–skip-extended-insert
Turn off extended-insert.
–skip-lock-tables Don’t lock tables during the dump.
–skip-quick Disable the –quick option.
–skip-set-charset Don’t add SET NAMES statement.
–skip-tz-utc Don’t convert TIMESTAMP columns to UTC.
–socket=name The socket file to use for connection.
–ssl Enable SSL for connection (automatically enabled with
other flags).
–ssl-ca=name CA file in PEM format.
–ssl-capath=name CA directory.
–ssl-cert=name X509 cert in PEM format.
–ssl-cipher=name SSL cipher to use.
–ssl-key=name X509 key in PEM format.
–ssl-crl=name Certificate revocation list.
–ssl-crlpath=name Certificate revocation list path.
–ssl-mode=name SSL connection mode (disabled|preferred|required|verify_ca|verify_identity).
–ssl-verify-server-cert
Verify server’s “Common Name” in its cert against
hostname used when connecting. This option is disabled by
default.
–tables Overrides the –databases (-B) option and specifies
table names to dump. Note that using this option
without –databases (-B) will result in no CREATE
DATABASE statement being written to the dump file.
–triggers Dump triggers for each dumped table.
–tz-utc Convert TIMESTAMP columns to UTC.
–user=name User for login if not current user.
–verbose Print info about the various stages.
–version Print version information and exit.
–where=name Dump only rows selected by the given WHERE condition.
–xml Dump a database as well formed XML.
–plugin-auth=name The plugin to use for authentication.
–column-statistics[=name]
Control column statistics generation. Enabled by default.
–enable-cleartext-plugin
Enable/disable the clear text authentication plugin.
–default-auth=name Default authentication client-side plugin to use.
–server-public-key-path=name
File path to the server public RSA key in PEM format.
–get-server-public-key
Get server public key from the server.
–connect-timeout=# Number of seconds before connection timeout.
–max-connect-errors=#
Maximum number of interrupted connections before
aborting.
–net-read-timeout=#
Number of seconds to wait for more data from the server.
–net-write-timeout=#
Number of seconds to wait for a block to be written to
the server.
–select-limit=# The maximum number of rows to include in a SELECT
statement.
–show-create-skip-secondary-indexes
Don’t include CREATE INDEX statements for secondary
indexes in SHOW CREATE TABLE output.
–zstd-compression-level=#
The compression level to use for connections to the
server that use zstd compression.
–compression-algorithms=#
A comma-separated list of compression algorithms to
use for connections to the server.
–ssl-fips-mode={OFF|ON|STRICT}
FIPS mode for SSL operations.
–tls-version=# TLS version to use, permitted values are:
TLSv1, TLSv1.1, TLSv1.2, TLSv1.3
–allow-deprecated-client-plugin
Allow the use of deprecated client plugins.
–enable-cleartext-plugin
Enable the clear text authentication plugin.
–password-validation-plugin
Plugin to use for password validation.
–login-path=name Read login path options from .mylogin.cnf.
–connect-expired-password
Indicate that the password provided is expired and that
the client should prompt the user to set a new one.

BACKUP_DIR=”/backup/mysql/logical/full”
DATE=$(date +%Y%m%d_%H%M%S)
DB_USER=”fgedu_admin”
DB_PASS=”StrongPassword123!”

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行完全备份
mysqldump –user=$DB_USER –password=$DB_PASS –single-transaction –flush-logs –master-data=2 –all-databases > $BACKUP_DIR/full_backup_$DATE.sql

# 压缩备份文件
gzip $BACKUP_DIR/full_backup_$DATE.sql

# 删除7天前的备份文件
find $BACKUP_DIR -name “full_backup_*.sql.gz” -mtime +7 -delete

# 记录备份日志
echo “[$(date +%Y-%m-%d%H:%M:%S)] Full backup completed: $BACKUP_DIR/full_backup_$DATE.sql.gz” >> /mysql/logs/backup.log

BACKUP_DIR=”/backup/mysql/logical/single_db”
DATE=$(date +%Y%m%d_%H%M%S)
DB_USER=”fgedu_admin”
DB_PASS=”StrongPassword123!”
DB_NAME=”fgedudb”

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行单库备份
mysqldump –user=$DB_USER –password=$DB_PASS –single-transaction –flush-logs –master-data=2 –database $DB_NAME > $BACKUP_DIR/${DB_NAME}_backup_$DATE.sql

# 压缩备份文件
gzip $BACKUP_DIR/${DB_NAME}_backup_$DATE.sql

# 删除7天前的备份文件
find $BACKUP_DIR -name “${DB_NAME}_backup_*.sql.gz” -mtime +7 -delete

# 记录备份日志
echo “[$(date +%Y-%m-%d%H:%M:%S)] Single database backup completed: $BACKUP_DIR/${DB_NAME}_backup_$DATE.sql.gz” >> /mysql/logs/backup.log

BACKUP_DIR=”/backup/mysql/logical/single_table”
DATE=$(date +%Y%m%d_%H%M%S)
DB_USER=”fgedu_admin”
DB_PASS=”StrongPassword123!”
DB_NAME=”fgedudb”
TABLE_NAME=”fgedu_users”

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行单表备份
mysqldump –user=$DB_USER –password=$DB_PASS –single-transaction –flush-logs –master-data=2 $DB_NAME $TABLE_NAME > $BACKUP_DIR/${DB_NAME}_${TABLE_NAME}_backup_$DATE.sql

# 压缩备份文件
gzip $BACKUP_DIR/${DB_NAME}_${TABLE_NAME}_backup_$DATE.sql

# 删除7天前的备份文件
find $BACKUP_DIR -name “${DB_NAME}_${TABLE_NAME}_backup_*.sql.gz” -mtime +7 -delete

# 记录备份日志
echo “[$(date +%Y-%m-%d%H:%M:%S)] Single table backup completed: $BACKUP_DIR/${DB_NAME}_${TABLE_NAME}_backup_$DATE.sql.gz” >> /mysql/logs/backup.log

# 步骤2：执行完全备份
# mysqldump –user=fgedu_admin –password=StrongPassword123! –single-transaction –flush-logs –master-data=2 –all-databases > /backup/mysql/logical/full/full_backup_20260401_120000.sql

# 步骤3：压缩备份文件
# gzip /backup/mysql/logical/full/full_backup_20260401_120000.sql

# 步骤4：查看备份文件
# ls -la /backup/mysql/logical/full/
-rw-r–r– 1 root root 1024000 Apr 1 12:00 full_backup_20260401_120000.sql.gz

# 步骤2：执行单表备份
# mysqldump –user=fgedu_admin –password=StrongPassword123! –single-transaction –flush-logs –master-data=2 fgedudb fgedu_users > /backup/mysql/logical/single_table/fgedudb_fgedu_users_backup_20260401_130000.sql

# 步骤3：压缩备份文件
# gzip /backup/mysql/logical/single_table/fgedudb_fgedu_users_backup_20260401_130000.sql

# 步骤4：查看备份文件
# ls -la /backup/mysql/logical/single_table/
-rw-r–r– 1 root root 102400 Apr 1 13:00 fgedudb_fgedu_users_backup_20260401_130000.sql.gz

# 步骤2：恢复备份
# mysql –user=fgedu_admin –password=StrongPassword123! < /backup/mysql/logical/full/full_backup_20260401_120000.sql # 步骤3：验证恢复结果 mysql> SELECT * FROM fgedudb.fgedu_users LIMIT 10;
+—-+——+——————+————-+
| id | name | id_card | phone |
+—-+——+——————+————-+
| 1 | 张三 | 110101199001011234 | 13812345678 |
| 2 | 李四 | 310101199102022345 | 13987654321 |
| 3 | 王五 | 440101199203033456 | 13765432109 |
+—-+——+——————+————-+
3 rows in set (0.00 sec)

# 步骤4：恢复单表备份
# gzip -d /backup/mysql/logical/single_table/fgedudb_fgedu_users_backup_20260401_130000.sql.gz
# mysql –user=fgedu_admin –password=StrongPassword123! fgedudb < /backup/mysql/logical/single_table/fgedudb_fgedu_users_backup_20260401_130000.sql # 步骤5：验证单表恢复结果 mysql> SELECT * FROM fgedudb.fgedu_users LIMIT 10;
+—-+——+——————+————-+
| id | name | id_card | phone |
+—-+——+——————+————-+
| 1 | 张三 | 110101199001011234 | 13812345678 |
| 2 | 李四 | 310101199102022345 | 13987654321 |
| 3 | 王五 | 440101199203033456 | 13765432109 |
+—-+——+——————+————-+
3 rows in set (0.00 sec)