目录
1.备份类型
1.1.冷备
冷备即是关闭库后拷贝相关文件，包括配置文件、数据文件、binlog日志文件等。生产中一般没有实际意义，用的很少。
1.2.热备
可以使用快照、复制或专有方法，最大限度地减小对 MySQL和应用程序的影响；
对于某些存储引擎，更好的办法是暂时锁定数据库，进行备份，然后再将数据库解锁，锁在热备做了两件事：
第一记录binlog文件的位置；
第二冷备非事务引擎引的表(MYISAM);
2.1.1.特点
逻辑备份使用的工具，单线程。生产数据量比较大的时候备份可能很花时间。在大数据量备份的时候，由于需要把磁盘的数据读到内存中，会把内存中热点数据踢掉，影响mysql的性能。mysql自带的工具，不需要额外安装。
2.1.2.使用
#mysqldump --help
#mysqldump -h127.0.0.1 -P3306 -uroot -p --single-transaction --master-data=2 dbname> /tmp/dbname.sql
#mysqldump -h127.0.0.1 -P3306 -uroot -p --single-transaction --master-data=2 -A> /tmp/dbnameall.sql
-A表示导出所有的库。
2.1.3.mysqldump备份流程
我们可以使用mysql的general_log来跟踪mysqldump到底做了哪些操作。
mysql> show variables like '%gen%';
+----------------------------------------+-------------------------+
| Variable_name | Value |
+----------------------------------------+-------------------------+
| auto_generate_certs | ON |
| general_log | OFF |
| general_log_file | /mysql/data/dr-prov.log |
| sha256_password_auto_generate_rsa_keys | ON |
+----------------------------------------+-------------------------+
打开general日志
mysql> set global general_log=1;
验证
mysql> show variables like '%gen%';
+----------------------------------------+-------------------------+
| Variable_name | Value |
+----------------------------------------+-------------------------+
| auto_generate_certs | ON |
| general_log | ON |
| general_log_file | /mysql/data/dr-prov.log |
| sha256_password_auto_generate_rsa_keys | ON |
+----------------------------------------+-------------------------+
查看general日志
# tail -f /mysql/data/dr-prov.log
...
2020-04-29T01:55:56.176302+08:00 85757 Connect keepalived@127.0.0.1 on using SSL/TLS
2020-04-29T01:55:56.176597+08:00 85757 Query select @@version_comment limit 1
2020-04-29T01:55:56.176917+08:00 85757 Query SELECT B.MEMBER_HOST=@@hostname as isPrimary FROM performance_schema.global_status A,performance_schema.replication_group_members B WHERE A.VARIABLE_VALUE=B.MEMBER_ID and A.VARIABLE_NAME='group_replication_primary_member'
2020-04-29T01:55:56.178249+08:00 85757 Quit
这个是keepavlie每隔2秒就来连一次mysql查询mysql是否挂了和当前节点是否是mysql的mgr的主节点，用以判断是否需要切换浮动ip的日志。
我们使用mysqldump进行备份，然后去观察general日志里面做了哪些操作。
# mysqldump -P3306 -uroot -p --single-transaction --master-data=2 test> /tmp/test.sql
#view /mysql/data/dr-prov.log
...
2020-04-29T02:02:17.625183+08:00 85949 Connect root@localhost on using Socket
2020-04-29T02:02:17.625426+08:00 85949 Query /*!40100 SET @@SQL_MODE='' */
2020-04-29T02:02:17.625589+08:00 85949 Query /*!40103 SET TIME_ZONE='+00:00' */
2020-04-29T02:02:17.625725+08:00 85949 Query FLUSH /*!40101 LOCAL */ TABLES
2020-04-29T02:02:17.770962+08:00 85949 Query FLUSH TABLES WITH READ LOCK
2020-04-29T02:02:17.771185+08:00 85949 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2020-04-29T02:02:17.771269+08:00 85949 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2020-04-29T02:02:17.771381+08:00 85949 Query SHOW VARIABLES LIKE 'gtid\_mode'
2020-04-29T02:02:17.774001+08:00 85949 Query SELECT @@GLOBAL.GTID_EXECUTED
2020-04-29T02:02:17.774098+08:00 85949 Query SHOW MASTER STATUS
2020-04-29T02:02:17.774183+08:00 85949 Query UNLOCK TABLES
2020-04-29T02:02:17.774334+08:00 85949 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('test'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2020-04-29T02:02:17.775759+08:00 85949 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('test')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2020-04-29T02:02:17.776284+08:00 85949 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2020-04-29T02:02:17.777564+08:00 85949 Init DB test
2020-04-29T02:02:17.777631+08:00 85949 Query SAVEPOINT sp
2020-04-29T02:02:17.777735+08:00 85949 Query show tables
2020-04-29T02:02:17.777907+08:00 85949 Query show table status like 'tmp\_list\_001'
2020-04-29T02:02:17.778119+08:00 85949 Query SET SQL_QUOTE_SHOW_CREATE=1
2020-04-29T02:02:17.778206+08:00 85949 Query SET SESSION character_set_results = 'binary'
2020-04-29T02:02:17.778283+08:00 85949 Query show create table `tmp_list_001`
2020-04-29T02:02:17.778391+08:00 85949 Query SET SESSION character_set_results = 'utf8'
2020-04-29T02:02:17.778474+08:00 85949 Query show fields from `tmp_list_001`
2020-04-29T02:02:17.778765+08:00 85949 Query show fields from `tmp_list_001`
2020-04-29T02:02:17.779063+08:00 85949 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `tmp_list_001`
2020-04-29T02:02:17.779287+08:00 85949 Query SET SESSION character_set_results = 'binary'
2020-04-29T02:02:17.779354+08:00 85949 Query use `test`
2020-04-29T02:02:17.779425+08:00 85949 Query select @@collation_database
2020-04-29T02:02:17.779510+08:00 85949 Query SHOW TRIGGERS LIKE 'tmp\_list\_001'
2020-04-29T02:02:17.779779+08:00 85949 Query SET SESSION character_set_results = 'utf8'
2020-04-29T02:02:17.779866+08:00 85949 Query ROLLBACK TO SAVEPOINT sp
2020-04-29T02:02:17.779933+08:00 85949 Query RELEASE SAVEPOINT sp
2020-04-29T02:02:17.815906+08:00 85949 Quit
...
这个就可以看到mysqldump在备份的时候数据库都做了哪些具体操作。
2.2.1.特点
逻辑备份使用的工具，多线程。跟mysqldump一样，在大数据量备份的时候，由于需要把磁盘的数据读到内存中，会把内存中热点数据踢掉，影响mysql的性能。
2.2.2.使用
下载：
https://launchpad.net/mydumper
编译安装：
#yum -y install cmake glib2-devel mysql-devel zlib-devel pcre-devel openssl-devel
#tar -xzvf mydumper-0.9.1.tar.gz
#cd mydumper-0.9.1
#cmake .
#make
#make install
使用：
# mydumper --help
#mydumper \
--user=root \
--password='R00t_123' \
--socket=/var/lib/mysql/mysql.sock \
--regex '^(?!(mysql))' \
--outputdir=/mybackup/ \
--compress \
--verbose=3 \
--logfile=/mybackup/mydumper.log
备份出来的文件如下
test-schema-create.sql.gz
test.tmp_list_001-schema.sql.gz
test.tmp_list_001.sql.gz
分别对应建库、建表、表数据三部分。
扫一扫
381
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
