MySQL逻辑备份介绍
逻辑备份：
备份内容：数据库的结构定义语句+数据内容的插入语句，备份出来的文件是可以编辑的。
适用场景：数据量少的数据库，比较适合100G数据量以内的。
逻辑备份的特点：
- sql语句组成的文件，可以截取部分单独使用
- 备份文件比物理的小
- 可以细化到表/表的内容
- 速度慢
- 可以跨平台恢复/迁移
- 只能在线备份，在线恢复
逻辑备份工具：
- mysqldump（单线程）类似于Oracle的exp，mysqlpump（多线程，MySQL 5.7+）类似于Oracle的expdp，在线备份：mysql官方工具
- mydumper：开源的，基于mysqldump的一个优化，多线程，速度介于两者之间，主要用于mariadb、percona、官方的mysql
mysqldump 工具详解与备份恢复案例
mysqldump工具介绍
mysql 5.7之前主用，mysql 5.7之后也用的特别多，特别是互联网的业务，很多都是几个G，几十个G的数据量
create datebases;
create table;
insert into;
存储过程
触发器
函数
调度事件
工具使用演示：
#备份
mysqldump -uroot -proot --all-databases > db_fullbackup.sql
#恢复
#先不创建数据库直接恢复单个数据库会报错，提示数据库不存在
#全库恢复是不需要创建数据库的，恢复单独的数据库是需要先创建数据库（空库，数据还需要从备份文件恢复）
mysql> create database itpux default character set utf8; -- 相关信息可以去备份的文件里找
mysql -uroot -proot -o testdb < db_fullbackup.sql #-o 表示只有某个数据库
mysqldump 工具参数详解
mysqldump 帮助信息
[root@centos7 ~]# mysqldump --help
mysqldump Ver 10.13 Distrib 5.7.43, for linux-glibc2.12 (x86_64)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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 /usr/local/mysql/etc/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,
except for login file.
--defaults-file=# Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=#
Also read groups with concat(group, suffix)
--login-path=# Read this path from the login file.
-A, --all-databases Dump all the databases. This will be same as --databases
with all databases selected.
-Y, --all-tablespaces
Dump all the tablespaces.
-y, --no-tablespaces
Do not dump any tablespace information.
--add-drop-database Add a DROP DATABASE before each create.
--add-drop-table Add a DROP TABLE before each create.
(Defaults to on; use --skip-add-drop-table to disable.)
--add-drop-trigger Add a DROP TRIGGER before each create.
--add-locks Add locks around INSERT statements.
(Defaults to on; use --skip-add-locks to disable.)
--allow-keywords Allow creation of column names that are keywords.
--apply-slave-statements
Adds 'STOP SLAVE' prior to 'CHANGE MASTER' and 'START
SLAVE' to bottom of dump.
--bind-address=name IP address to bind to.
--character-sets-dir=name
Directory for character set files.
-i, --comments Write additional information.
(Defaults to on; use --skip-comments to disable.)
--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. Note: Requires MySQL server
version 4.1.0 or higher. This option is ignored with
earlier server versions.
--compact Give less verbose output (useful for debugging). Disables
structure comments and header/footer constructs. Enables
options --skip-add-drop-table --skip-add-locks
--skip-comments --skip-disable-keys --skip-set-charset.
-c, --complete-insert
Use complete insert statements.
-C, --compress Use compression in server/client protocol.
-a, --create-options
Include all MySQL specific create options.
(Defaults to on; use --skip-create-options to disable.)
-B, --databases Dump several databases. Note the difference in usage; in
this case no tables are given. All name arguments are
regarded as database names. 'USE db_name;' will be
included in the output.
-#, --debug[=#] This is a non-debug version. Catch this and exit.
--debug-check This is a non-debug version. Catch this and exit.
--debug-info This is a non-debug version. Catch this and exit.
--default-character-set=name
Set the default character set.
--delete-master-logs
Delete logs on master after backup. This automatically
enables --master-data.
-K, --disable-keys '/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and
'/*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be put
in the output.
(Defaults to on; use --skip-disable-keys to disable.)
--dump-slave[=#] This causes the binary log position and filename of the
master to be appended to the dumped data output. Setting
the value to 1, will printit as a CHANGE MASTER command
in the dumped data output; if equal to 2, that command
will be prefixed with a comment symbol. This option will
turn --lock-all-tables on, unless --single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump - don't
forget to read about --single-transaction below). In all
cases any action on logs will happen at the exact moment
of the dump.Option automatically turns --lock-tables off.
-E, --events Dump events.
-e, --extended-insert
Use multiple-row INSERT syntax that include several
VALUES lists.
(Defaults to on; use --skip-extended-insert to disable.)
--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.
-F, --flush-logs Flush logs file in server before starting dump. Note that
if you dump many databases at once (using the option
--databases= or --all-databases), the logs will be
flushed for each database dumped. The exception is when
using --lock-all-tables or --master-data: in this case
the logs will be flushed only once, corresponding to the
moment all tables are locked. So if you want your dump
and the log flush to happen at the same exact moment you
should use --lock-all-tables or --master-data with
--flush-logs.
--flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysql
database. This option should be used any time the dump
contains the mysql database and any other database that
depends on the data in the mysql database for proper
restore.
-f, --force Continue even if we get an SQL error.
-?, --help Display this help message and exit.
--hex-blob Dump binary strings (BINARY, VARBINARY, BLOB) in
hexadecimal format.
-h, --host=name Connect to host.
--ignore-error=name A comma-separated list of error numbers to be ignored if
encountered during dump.
--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
Adds 'MASTER_HOST=<host>, MASTER_PORT=<port>' to 'CHANGE
MASTER TO..' in dump produced with --dump-slave.
--insert-ignore Insert rows with INSERT IGNORE.
--lines-terminated-by=name
Lines in the output file are terminated by the given
string.
-x, --lock-all-tables
Locks all tables across all databases. This is achieved
by taking a global read lock for the duration of the
whole dump. Automatically turns --single-transaction and
--lock-tables off.
-l, --lock-tables Lock all tables for read.
(Defaults to on; use --skip-lock-tables to disable.)
--log-error=name Append warnings and errors to given file.
--master-data[=#] This causes the binary log position and filename to be
appended to the output. If equal to 1, will print it as a
CHANGE MASTER command; if equal to 2, that command will
be prefixed with a comment symbol. This option will turn
--lock-all-tables on, unless --single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump; don't
forget to read about --single-transaction below). In all
cases, any action on logs will happen at the exact moment
of the dump. Option automatically turns --lock-tables
off.
--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.
-n, --no-create-db Suppress the CREATE DATABASE ... IF EXISTS statement that
normally is output for each dumped database if
--all-databases or --databases is given.
-t, --no-create-info
Don't write table creation info.
-d, --no-data No row information.
-N, --no-set-names Same as --skip-set-charset.
--opt Same as --add-drop-table, --add-locks, --create-options,
--quick, --extended-insert, --lock-tables, --set-charset,
and --disable-keys. Enabled by default, disable with
--skip-opt.
--order-by-primary Sorts each table's rows by primary key, or first unique
key, if such a key exists. Useful when dumping a MyISAM
table to be loaded into an InnoDB table, but will make
the dump itself take considerably longer.
-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.
--protocol=name The protocol to use for connection (tcp, socket, pipe,
memory).
-q, --quick Don't buffer query, dump directly to stdout.
(Defaults to on; use --skip-quick to disable.)
-Q, --quote-names Quote table and column names with backticks (`).
(Defaults to on; use --skip-quote-names to disable.)
--replace Use REPLACE INTO instead of INSERT INTO.
-r, --result-file=name
Direct output to a given file. This option should be used
in systems (e.g., DOS, Windows) that use carriage-return
linefeed pairs (\r\n) to separate text lines. This option
ensures that only a single newline is used.
-R, --routines Dump stored routines (functions and procedures).
--set-charset Add 'SET NAMES default_character_set' to the output.
(Defaults to on; use --skip-set-charset to disable.)
--set-gtid-purged[=name]
Add 'SET @@GLOBAL.GTID_PURGED' to the output. Possible
values for this option are ON, OFF and AUTO. If ON is
used and GTIDs are not enabled on the server, an error is
generated. If OFF is used, this option does nothing. If
AUTO is used and GTIDs are enabled on the server, 'SET
@@GLOBAL.GTID_PURGED' is added to the output. If GTIDs
are disabled, AUTO does nothing. If no value is supplied
then the default (AUTO) value will be considered.
--single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
--single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off --lock-tables.
--dump-date Put a dump date to the end of the output.
(Defaults to on; use --skip-dump-date to disable.)
--skip-mysql-schema Skip adding DROP DATABASE for mysql schema.
--skip-opt Disable --opt. Disables --add-drop-table, --add-locks,
--create-options, --quick, --extended-insert,
--lock-tables, --set-charset, and --disable-keys.
-S, --socket=name The socket file to use for connection.
--secure-auth Refuse client connecting to server if it uses old
(pre-4.1.1) protocol. Deprecated. Always TRUE
--ssl-mode=name SSL connection mode.
--ssl Deprecated. Use --ssl-mode instead.
(Defaults to on; use --skip-ssl to disable.)
--ssl-verify-server-cert
Deprecated. Use --ssl-mode=VERIFY_IDENTITY instead.
--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.
--tls-version=name TLS version to use, permitted values are: TLSv1, TLSv1.1,
TLSv1.2
--server-public-key-path=name
File path to the server public RSA key in PEM format.
--get-server-public-key
Get server public key
-T, --tab=name Create tab-separated textfile for each table to given
path. (Create .sql and .txt files.) NOTE: This only works
if mysqldump is run on the same machine as the mysqld
server.
--tables Overrides option --databases (-B).
--triggers Dump triggers for each dumped table.
(Defaults to on; use --skip-triggers to disable.)
--tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of
TIMESTAMP data when a server has data in different time
zones or data is being moved between servers with
different time zones.
(Defaults to on; use --skip-tz-utc to disable.)
-u, --user=name User for login if not current user.
-v, --verbose Print info about the various stages.
-V, --version Output version information and exit.
-w, --where=name Dump only selected records. Quotes are mandatory.
-X, --xml Dump a database as well formed XML.
--plugin-dir=name Directory for client-side plugins.
--default-auth=name Default authentication client-side plugin to use.
--enable-cleartext-plugin
Enable/disable the clear text authentication plugin.
Variables (--variable-name=value)
and boolean options {
FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
all-databases FALSE
all-tablespaces FALSE
no-tablespaces FALSE
add-drop-database FALSE
add-drop-table TRUE
add-drop-trigger FALSE
add-locks TRUE
allow-keywords FALSE
apply-slave-statements FALSE
bind-address (No default value)
character-sets-dir (No default value)
comments TRUE
compatible (No default value)
compact FALSE
complete-insert FALSE
compress FALSE
create-options TRUE
databases FALSE
default-character-set utf8
delete-master-logs FALSE
disable-keys TRUE
dump-slave 0
events FALSE
extended-insert TRUE
fields-terminated-by (No default value)
fields-enclosed-by (No default value)
fields-optionally-enclosed-by (No default value)
fields-escaped-by (No default value)
flush-logs FALSE
flush-privileges FALSE
force FALSE
hex-blob FALSE
host (No default value)
ignore-error (No default value)
include-master-host-port FALSE
insert-ignore FALSE
lines-terminated-by (No default value)
lock-all-tables FALSE
lock-tables TRUE
log-error (No default value)
master-data 0
max-allowed-packet 25165824
net-buffer-length 1046528
no-autocommit FALSE
no-create-db FALSE
no-create-info FALSE
no-data FALSE
order-by-primary FALSE
port 0
quick TRUE
quote-names TRUE
replace FALSE
routines FALSE
set-charset TRUE
single-transaction FALSE
dump-date TRUE
skip-mysql-schema FALSE
socket (No default value)
secure-auth TRUE
ssl TRUE
ssl-verify-server-cert FALSE
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-key (No default value)
ssl-crl (No default value)
ssl-crlpath (No default value)
tls-version (No default value)
server-public-key-path (No default value)
get-server-public-key FALSE
tab (No default value)
triggers TRUE
tz-utc TRUE
user (No default value)
verbose FALSE
where (No default value)
plugin-dir (No default value)
default-auth (No default value)
enable-cleartext-plugin FALSE
[root@centos7 ~]#
mysqldump 默认选项
Variables (--variable-name=value)
and boolean options {
FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
all-databases FALSE
all-tablespaces FALSE
no-tablespaces FALSE
add-drop-database FALSE
add-drop-table TRUE
add-drop-trigger FALSE
add-locks TRUE
allow-keywords FALSE
apply-slave-statements FALSE
bind-address (No default value)
character-sets-dir (No default value)
comments TRUE
compatible (No default value)
compact FALSE
complete-insert FALSE
compress FALSE
create-options TRUE
databases FALSE
default-character-set utf8
delete-master-logs FALSE
disable-keys TRUE
dump-slave 0
events FALSE
extended-insert TRUE
fields-terminated-by (No default value)
fields-enclosed-by (No default value)
fields-optionally-enclosed-by (No default value)
fields-escaped-by (No default value)
flush-logs FALSE
flush-privileges FALSE
force FALSE
hex-blob FALSE
host (No default value)
ignore-error (No default value)
include-master-host-port FALSE
insert-ignore FALSE
lines-terminated-by (No default value)
lock-all-tables FALSE
lock-tables TRUE
log-error (No default value)
master-data 0
max-allowed-packet 25165824
net-buffer-length 1046528
no-autocommit FALSE
no-create-db FALSE
no-create-info FALSE
no-data FALSE
order-by-primary FALSE
port 0
quick TRUE
quote-names TRUE
replace FALSE
routines FALSE
set-charset TRUE
single-transaction FALSE
dump-date TRUE
skip-mysql-schema FALSE
socket (No default value)
secure-auth TRUE
ssl TRUE
ssl-verify-server-cert FALSE
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-key (No default value)
ssl-crl (No default value)
ssl-crlpath (No default value)
tls-version (No default value)
server-public-key-path (No default value)
get-server-public-key FALSE
tab (No default value)
triggers TRUE
tz-utc TRUE
user (No default value)
verbose FALSE
where (No default value)
plugin-dir (No default value)
default-auth (No default value)
enable-cleartext-plugin FALSE
mysqldump 服务器相关的常用参数
#1. mysqldump 客户端读取：
my.cnf
[client]
[mysqldump]
#2. 相关参数
mysqldump -uroot -proot -h192.168.1.51 -P3306 .....
-u --user=
-p --password=
-h --host=
-P --port=
--protocol= tcp,socket,memory 指定协议
--max-allowed-packet= 指定最大分配的包
--net-buffer-legth 网络缓存的长度
mysqldump 备份内容的常用参数
可以备份：
可以备份所有数据库，几个数据库，一个数据库，一个表，几个表，一个表里面的内容，存储过程，函数
- -A，–all-databases 备份所有数据库
- -B，–databases database[tables] 备份一个或者几个数据库，可以分开写（用空格隔开）
- -y，-all-tablespaces 备份所有的表空间
- -n，–no-create-db 不导出数据库结构
- -t，–no-create-info 不导出表结构
- -d，–no-data 不导出数据，只导结构
- -R，–routines 导出存储过程和函数，默认是不导出的。
- –triggers 导出触发器,默认导出
- –skip-triggers 不导出触发器
- -E，–events 导出调度事件
- –ignore-table 不导出某个库的某个表，或者不导出几个表：testdb.test_t1
- -w，–where=‘’ 导出的时候加上条件
mysqldump 备份与事物和锁相关的选项
-
–single-transaction:
可以得到一致性的导出结果。只针对innodb，导出过程中不允许运行表的DDL 操作。
因为事务持有表的metadata lock 的共享锁，而DDL 会申请metadata lock 的互斥锁，所以会阻塞。
–single-transaction 还会关掉你默认的–lock-tables 选项（ 即不加锁），因为mysqldump 默认会打开一个lock-tables,在导出过程中锁定所有的表。那么只能加–master-data 才能加锁。 -
–lock-tables
默认打开的。这个锁表是导一个锁一个，导完解锁。 -
–lock-all-tables
会把所有的表都给锁了，慢慢导，导完解锁。
上面这三个参数是互斥的（1,2,3），只能同时用一个。 -
–flush-logs
导出数据时刷新二进制日志，达到一致性导出。 -
–flush-privileges
导出权限 -
–master-data
有3 个值：
0：默认就是0，不写入binlog 日志记录
1：change master to … 记录binlog 文件及终点（当前备份到什么位置，在恢复或者做增量的时候就可以根据这个记录从特定的位置开始恢复，如果是从库会根据这个位置从master端去复制二进制日志）
2：#change master to … 记录binlog 文件及终点（加了这个参数，但是是注释的状态）mysqldump -uroot -p --single-transaction --master-data=2 --flush-logs --flush-privileges --routines testdb >testdb.sql
mysqldump 与复制相关的选项
- –master-data
有3 个值：
0：默认就是0，不写入binlog 日志记录
1：change master to … 记录binlog 文件及终点
2：#change master to … 记录binlog 文件及终点 - –dump-slave
在从库上面使用的，和–master-data 参数一样，是为了slave 建立下一级的slave - –apply-slave-statements
和–master-data=1 类似 - –include-master-host-port
结合–dump-slave=1/2，在导出中加入host、port - –delete-master-logs
在备份之后，删除master 的bin log 日志，默认打开–master-data=2，一般不用，因为日志一般不能随便删除。 - –set-gtid-purged
用于在gtid 的环境使用
mysqldump 与字符集相关的选项
- –set-charset
默认开启，–set-charset=1/0，是否开启字符集 - –default-character-set
指定是什么字符集，utf8,gbk,utf8mb4 - -N,–no-set-names
关闭–set-charset
mysqldump 控制是否生成DDL 语句的选项
–add-drop-database(add:drop database ddl)，先删除数据库
–add-drop-table, 先删除表，默认打开的，禁用：–skip-add-drop-table
–add-drop-trigger 先删除触发器
–no-create-db,-n 不创建数据库（也就是说数据库必须存在才能往里面导数据）
–no-create-info,-t 不创建表（也就是说表必须存在才能往里面导数据）
mysqldump 其它语法
- -f，–force 强制性导出
- –log-error=/tmp/1.log
- –compatible=(oracle/mssql/postgresql)
mysqldump 常用备份命令使用案例
mysql> create database testdb;
Query OK, 1 row affected (0.00 sec)
mysql> exit
Bye
[root@centos7 ~]#
[root@centos7 ~]# mysql -uroot -prootroot -S /mysql/data/3306/mysql.sock < testdb.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@centos7 ~]#
mysql> use testdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| bm |
| dd |
| dq |
| gj |
| gw |
| jl |
| test11 |
| test12 |
| test_m1 |
| test_m5 |
| test_obj |
| test_sales |
| test_yg |
| yg |
+------------------+
14 rows in set (0.00 sec)
mysql>
命令案例
- 导出所有的数据库（库/表结构/数据）
mysqldump -uroot -proot --all-databases > fullbak01.sql
- 导出所有的数据库（库/表结构，但是不保存数据）
mysqldump -uroot -proot --all-databases --no-data > fullbak02.sql
- 只导出某一个表（结构+数据：testdb.yg）
mysqldump -uroot -proot testdb yg > testdb_yg_01.sql
- 只导出某一个表（结构，不包括数据：testdb.yg）
mysqldump -uroot -proot --no-data testdb yg > testdb_yg_02.sql
OR：
show create table testdb.yg\G
- 导出多个表（结构+数据：testdb.yg，testdb.bm）
mysqldump -uroot -proot testdb yg bm > testdb_yg_bm_01.sql
- 不创建表结构（只要数据）
mysqldump -uroot -proot --no-create-info testdb bm > testdb_bm.sql
OR
select * from testdb.bm into outfile '/tmp/testdb-bm.sql';
- 导出单个数据库（结构和数据）
mysqldump -uroot -proot --databases testdb > testdb07.sql
#如果不加 --datebases参数，在备份脚本中是不会出现创建数据库的语句
- 导出多个数据库（结构+数据）
mysqldump -uroot -proot --databases testdb testdb2 > testdb08.sql
- 导出单个数据库（只要结构，不要数据）
mysqldump -uroot -proot --no-data --databases testdb > testdb09.sql
- 导出多个数据库（只要结构，不要数据）
mysqldump -uroot -proot --no-data --databases testdb testdb2 > testdb10.sql
- 导出单个数据库（只要数据，不要结构）
mysqldump -uroot -proot --no-create-db --no-create-info --databases testdb > testdb11.sql
- 导出单个库（排除某个表）
mysqldump -uroot -proot --databases testdb --ignore-table=testdb.test_m5 > testdb12.sql
- 导出某个表的某一些数据（按条件）
mysqldump -uroot -proot testdb yg --where="employee_id < 105" > testdb13.sql
- 导出所有的数据库（库/表结构/数据/函数/存储过程）
mysqldump -uroot -proot -R -E --all-databases > fullbak14.sql
- 导出所有数据库（数据一致+导出权限+刷新日志）
mysqldump -uroot -proot --single-transaction --master-data=2 --flush-logs --flush-privileges --routines --all-databases >fullbak15.sql
16.备份系统库
mysqldump -uroot -proot --skip-lock-tables --databases information_schema sys performance_schema > all-info-db.sql
mysqldump 深入解析与实现原理
必须打开数据库的general log，在my.cnf 加入下列参数，重启mysql 生效：
general_log = on
general_log_file=/mysql/log/3306/testdb-general.err
mysql> show variables like '%general_log%';
+------------------+------------------------------------+
| Variable_name | Value |
+------------------+------------------------------------+
| general_log | ON |
| general_log_file | /mysql/log/3306/testdb-general.err |
+------------------+------------------------------------+
2 rows in set (0.00 sec)
分析mysqldump 全备的过程
mysqldump -uroot -proot --single-transaction --master-data=2 --flush-logs --routines --all-databases >fullbak16.sql
看日志
more /mysql/log/3306/testdb-general.err
43 2023-11-05T18:15:38.560334+08:00 2 Connect root@localhost on using Socket
44 2023-11-05T18:15:38.560806+08:00 2 Query show variables like '%bin%'
45 2023-11-05T18:16:58.126307+08:00 3 Connect root@localhost on using Socket
46 2023-11-05T18:16:58.126380+08:00 3 Connect Access denied for user 'root'@'localhost' (using password: YES)
47 2023-11-05T18:17:07.614228+08:00 4 Connect root@localhost on using Socket
48 2023-11-05T18:17:07.614580+08:00 4 Query /*!40100 SET @@SQL_MODE='' */
49 2023-11-05T18:17:07.614755+08:00 4 Query /*!40103 SET TIME_ZONE='+00:00' */
50 2023-11-05T18:17:07.615056+08:00 4 Query SHOW VARIABLES LIKE 'gtid_mode'
51 2023-11-05T18:17:07.615806+08:00 4 Query FLUSH /*!40101 LOCAL */ TABLES
52 2023-11-05T18:17:07.616684+08:00 4 Query FLUSH TABLES WITH READ LOCK
53 2023-11-05T18:17:07.616913+08:00 4 Refresh
54 /mysql/app/mysql/bin/mysqld, Version: 5.7.43-log (MySQL Community Server (GPL)). started with:
55 Tcp port: 3306 Unix socket: /mysql/data/3306/mysql.sock
56 Time Id Command Argument
57 2023-11-05T18:17:07.622607+08:00 4 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
58 2023-11-05T18:17:07.622746+08:00 4 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
59 2023-11-05T18:17:07.622941+08:00 4 Query SHOW MASTER STATUS
60 2023-11-05T18:17:07.623186+08:00 4 Query UNLOCK TABLES
63 2023-11-05T18:17:07.625654+08:00 4 Query SHOW DATABASES
64 2023-11-05T18:17:07.626218+08:00 4 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
65 2023-11-05T18:17:07.626834+08:00 4 Init DB mysql
66 2023-11-05T18:17:07.627065+08:00 4 Query SHOW CREATE DATABASE IF NOT EXISTS `mysql`
67 2023-11-05T18:17:07.627303+08:00 4 Query SAVEPOINT sp
68 2023-11-05T18:17:07.627531+08:00 4 Query show tables
69 2023-11-05T18:17:07.627963+08:00 4 Query show table status like 'columns\_priv'
70 2023-11-05T18:17:07.628638+08:00 4 Query SET SQL_QUOTE_SHOW_CREATE=1
71 2023-11-05T18:17:07.628775+08:00 4 Query SET SESSION character_set_results = 'binary'
72 2023-11-05T18:17:07.628977+08:00 4 Query show create table `columns_priv`
73 2023-11-05T18:17:07.629271+08:00 4 Query SET SESSION character_set_results = 'utf8'
74 2023-11-05T18:17:07.629506+08:00 4 Query show fields from `columns_priv`
75 2023-11-05T18:17:07.630154+08:00 4 Query show fields from `columns_priv`
76 2023-11-05T18:17:07.630755+08:00 4 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `columns_priv`
493 2023-11-05T18:17:07.793839+08:00 4 Init DB sakila #处理sakila数据库
494 2023-11-05T18:17:07.794048+08:00 4 Query SHOW CREATE DATABASE IF NOT EXISTS 
最低0.47元/天 解锁文章
扫一扫
578
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
