MySQL/MariaDB数据库的冷备份和还原
MySQL/MariaDB数据库的冷备份和还原
作者：尹正杰
版权声明：原创作品，谢绝转载！否则将追究法律责任。
一.MySQL/MariaDB数据库的备份和还原概述
1>.为什么要备份
为了预防灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景。
2>.备份注意要点
能容忍最多丢失多少数据
恢复数据需要在多长时间内完成
需要恢复哪些数据
3>.还原要点
做还原测试，用于测试备份的可用性
还原演练(备份和备份成功是两码事！一定要进行还原演练！！！最好有相关的操作文档，遇到问题你也不慌了直接按照你自己写的文档执行就好！)
4>.备份类型
完全备份：
整个数据集
部分备份：
只备份数据子集，如部分库或表
完全备份、增量备份、差异备份
增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂
差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单
温馨提示：
二进制日志文件不应该与数据文件放在同一磁盘
5>.冷、温、热备份
冷备：
读写操作均不可进行
温备：
读操作可执行；但写操作不可执行
热备：
读写操作均可执行
存储引擎支持的备份类型:
MyISAM：支持冷备和温备，不支持热备
InnoDB：都支持
6>.物理和逻辑备份
物理备份：
直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
逻辑备份：
从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度
7>.备份时需要考虑的因素
温备的持锁多久
备份产生的负载
备份过程的时长
恢复过程的时长
8>.备份什么
数据
二进制日志、InnoDB的事务日志
程序代码（存储过程、函数、触发器、事件调度器）
服务器的配置文件
9>.备份工具
cp, tar等复制归档工具：
物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
LVM的快照：
先加锁，做快照后解锁，几乎热备；借助文件系统工具进行备份
mysqldump：
逻辑备份工具，适用所有存储引擎，温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份
xtrabackup：
由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份
MariaDB Backup：
从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现
mysqlbackup：
热备份， MySQL Enterprise Edition组件
mysqlhotcopy：
PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库
二.冷备份实战案例(完全备份)
为了试验方便，本案例采用多实例来模拟数据库的备份，关于MySQL的多实例安装方式可参考我之前的笔记。 博主推荐阅读: https://www.cnblogs.com/yinzhengjie/p/11741690.html
1>.查看两个实例的数据库信息
[root@node105.yinzhengjie.org.cn ~]# /mysql/3306/mysqld start Starting MySQL... [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ss -ntl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* LISTEN 0 80 :::3306 :::* LISTEN 0 128 :::22 :::* [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql/3306/socket/mysql.sock Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.2.19-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | yinzhengjie | +--------------------+ 4 rows in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> USE yinzhengjie Database changed MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | +-----------------------+ 7 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SELECT * FROM students; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 25 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> QUIT Bye [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# /mysql/3307/mysqld start Starting MySQL... [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ss -ntl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* LISTEN 0 80 :::3306 :::* LISTEN 0 80 :::3307 :::* LISTEN 0 128 :::22 :::* [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql/3307/socket/mysql.sock Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 10.2.19-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> QUIT Bye [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]#
2>.关闭两个实例并将主节点的数据拷贝到从节点并修改相应的权限
[root@node105.yinzhengjie.org.cn ~]# ss -ntl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* LISTEN 0 80 :::3306 :::* LISTEN 0 80 :::3307 :::* LISTEN 0 128 :::22 :::* [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# /mysql/3307/mysqld stop Stoping MySQL... [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# /mysql/3306/mysqld stop Stoping MySQL... [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ss -ntl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* LISTEN 0 128 :::22 :::* [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll /mysql/3306/data/ total 176204 -rw-rw---- 1 mysql mysql 16384 Nov 5 07:18 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Nov 5 07:18 aria_log_control -rw-rw---- 1 mysql mysql 2926 Nov 5 07:18 ib_buffer_pool -rw-rw---- 1 mysql mysql 79691776 Nov 5 07:18 ibdata1 -rw-rw---- 1 mysql mysql 50331648 Nov 5 07:18 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Oct 26 22:17 ib_logfile1 -rw-rw---- 1 mysql mysql 0 Oct 26 23:26 multi-master.info drwx------ 2 mysql mysql 4096 Oct 26 22:17 mysql -rw-rw---- 1 mysql mysql 29032 Oct 26 22:17 mysql-bin.000001 -rw-rw---- 1 mysql mysql 19 Oct 26 22:17 mysql-bin.index -rw-rw---- 1 mysql mysql 7 Oct 26 22:17 mysql-bin.state -rw-rw---- 1 mysql mysql 3880 Nov 4 18:48 node105.log -rw-rw---- 1 mysql mysql 948 Nov 4 17:41 node105-slow.log drwx------ 2 mysql mysql 20 Oct 26 22:17 performance_schema drwx------ 2 mysql mysql 272 Nov 4 22:03 yinzhengjie [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ll /mysql/3307/data/ total 110660 -rw-rw---- 1 mysql mysql 16384 Nov 5 07:18 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Nov 5 07:18 aria_log_control -rw-rw---- 1 mysql mysql 942 Nov 5 07:18 ib_buffer_pool -rw-rw---- 1 mysql mysql 12582912 Nov 5 07:18 ibdata1 -rw-rw---- 1 mysql mysql 50331648 Nov 5 07:18 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Oct 26 22:38 ib_logfile1 -rw-rw---- 1 mysql mysql 0 Oct 26 23:27 multi-master.info drwx------ 2 mysql mysql 4096 Oct 26 22:38 mysql -rw-rw---- 1 mysql mysql 29032 Oct 26 22:38 mysql-bin.000001 -rw-rw---- 1 mysql mysql 19 Oct 26 22:38 mysql-bin.index -rw-rw---- 1 mysql mysql 7 Oct 26 22:38 mysql-bin.state drwx------ 2 mysql mysql 20 Oct 26 22:38 performance_schema [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# scp -r /mysql/3306/data/* /mysql/3307/data/ [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ll /mysql/3307/data/ total 262028 -rw-rw---- 1 mysql mysql 16384 Nov 5 07:21 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Nov 5 07:21 aria_log_control -rw-rw---- 1 mysql mysql 2926 Nov 5 07:21 ib_buffer_pool -rw-rw---- 1 mysql mysql 79691776 Nov 5 07:21 ibdata1 -rw-rw---- 1 mysql mysql 50331648 Nov 5 07:21 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Nov 5 07:21 ib_logfile1 -rw-rw---- 1 mysql mysql 0 Nov 5 07:21 multi-master.info drwx------ 2 mysql mysql 4096 Oct 26 22:38 mysql -rw-rw---- 1 mysql mysql 29032 Nov 5 07:21 mysql-bin.000001 -rw-rw---- 1 mysql mysql 19 Nov 5 07:21 mysql-bin.index -rw-rw---- 1 mysql mysql 7 Nov 5 07:21 mysql-bin.state -rw-r----- 1 root root 3880 Nov 5 07:21 node105.log -rw-r----- 1 root root 948 Nov 5 07:21 node105-slow.log drwx------ 2 mysql mysql 20 Oct 26 22:38 performance_schema drwx------ 2 root root 272 Nov 5 07:21 yinzhengjie [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll /mysql/3306/data/ total 176204 -rw-rw---- 1 mysql mysql 16384 Nov 5 07:18 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Nov 5 07:18 aria_log_control -rw-rw---- 1 mysql mysql 2926 Nov 5 07:18 ib_buffer_pool -rw-rw---- 1 mysql mysql 79691776 Nov 5 07:18 ibdata1 -rw-rw---- 1 mysql mysql 50331648 Nov 5 07:18 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Oct 26 22:17 ib_logfile1 -rw-rw---- 1 mysql mysql 0 Oct 26 23:26 multi-master.info drwx------ 2 mysql mysql 4096 Oct 26 22:17 mysql -rw-rw---- 1 mysql mysql 29032 Oct 26 22:17 mysql-bin.000001 -rw-rw---- 1 mysql mysql 19 Oct 26 22:17 mysql-bin.index -rw-rw---- 1 mysql mysql 7 Oct 26 22:17 mysql-bin.state -rw-rw---- 1 mysql mysql 3880 Nov 4 18:48 node105.log -rw-rw---- 1 mysql mysql 948 Nov 4 17:41 node105-slow.log drwx------ 2 mysql mysql 20 Oct 26 22:17 performance_schema drwx------ 2 mysql mysql 272 Nov 4 22:03 yinzhengjie [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ll /mysql/3307/data/ total 262028 -rw-rw---- 1 mysql mysql 16384 Nov 5 07:21 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Nov 5 07:21 aria_log_control -rw-rw---- 1 mysql mysql 2926 Nov 5 07:21 ib_buffer_pool -rw-rw---- 1 mysql mysql 79691776 Nov 5 07:21 ibdata1 -rw-rw---- 1 mysql mysql 50331648 Nov 5 07:21 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Nov 5 07:21 ib_logfile1 -rw-rw---- 1 mysql mysql 0 Nov 5 07:21 multi-master.info drwx------ 2 mysql mysql 4096 Oct 26 22:38 mysql -rw-rw---- 1 mysql mysql 29032 Nov 5 07:21 mysql-bin.000001 -rw-rw---- 1 mysql mysql 19 Nov 5 07:21 mysql-bin.index -rw-rw---- 1 mysql mysql 7 Nov 5 07:21 mysql-bin.state -rw-r----- 1 root root 3880 Nov 5 07:21 node105.log -rw-r----- 1 root root 948 Nov 5 07:21 node105-slow.log drwx------ 2 mysql mysql 20 Oct 26 22:38 performance_schema drwx------ 2 root root 272 Nov 5 07:21 yinzhengjie [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# chown -R mysql:mysql /mysql/3307/data/ [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ll /mysql/3307/data/ total 262028 -rw-rw---- 1 mysql mysql 16384 Nov 5 07:21 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Nov 5 07:21 aria_log_control -rw-rw---- 1 mysql mysql 2926 Nov 5 07:21 ib_buffer_pool -rw-rw---- 1 mysql mysql 79691776 Nov 5 07:21 ibdata1 -rw-rw---- 1 mysql mysql 50331648 Nov 5 07:21 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Nov 5 07:21 ib_logfile1 -rw-rw---- 1 mysql mysql 0 Nov 5 07:21 multi-master.info drwx------ 2 mysql mysql 4096 Oct 26 22:38 mysql -rw-rw---- 1 mysql mysql 29032 Nov 5 07:21 mysql-bin.000001 -rw-rw---- 1 mysql mysql 19 Nov 5 07:21 mysql-bin.index -rw-rw---- 1 mysql mysql 7 Nov 5 07:21 mysql-bin.state -rw-r----- 1 mysql mysql 3880 Nov 5 07:21 node105.log -rw-r----- 1 mysql mysql 948 Nov 5 07:21 node105-slow.log drwx------ 2 mysql mysql 20 Oct 26 22:38 performance_schema drwx------ 2 mysql mysql 272 Nov 5 07:21 yinzhengjie [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]#
3>.启动从节点实例验证备份是否成功
[root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# /mysql/3307/mysqld start Starting MySQL... [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ss -ntl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* LISTEN 0 80 :::3307 :::* LISTEN 0 128 :::22 :::* [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql/3307/socket/mysql.sock Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 10.2.19-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | yinzhengjie | +--------------------+ 4 rows in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> USE yinzhengjie Database changed MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | +-----------------------+ 7 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SELECT * FROM students; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 25 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]>
三.基于LVM的备份
1>.将MySQL实例的数据文件和二进制文件分开存放并启动数据库实例
[root@node105.yinzhengjie.org.cn ~]# lsblk NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT sda 8:0 0 1000G 0 disk ├─sda1 8:1 0 1G 0 part /boot └─sda2 8:2 0 999G 0 part ├─centos-root 253:0 0 50G 0 lvm / ├─centos-swap 253:1 0 2G 0 lvm [SWAP] └─centos-home 253:2 0 947G 0 lvm /home sdb 8:16 0 50G 0 disk sr0 11:0 1 1024M 0 rom [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# fdisk /dev/sdb Welcome to fdisk (util-linux 2.23.2). Changes will remain in memory only, until you decide to write them. Be careful before using the write command. Device does not contain a recognized partition table Building a new DOS disklabel with disk identifier 0x0ba7f903. Command (m for help): p Disk /dev/sdb: 53.7 GB, 53687091200 bytes, 104857600 sectors Units = sectors of 1 * 512 = 512 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes Disk label type: dos Disk identifier: 0x0ba7f903 Device Boot Start End Blocks Id System Command (m for help): n Partition type: p primary (0 primary, 0 extended, 4 free) e extended Select (default p): p Partition number (1-4, default 1): First sector (2048-104857599, default 2048): Using default value 2048 Last sector, +sectors or +size{K,M,G} (2048-104857599, default 104857599): +5G Partition 1 of type Linux and of size 5 GiB is set Command (m for help): t Selected partition 1 Hex code (type L to list all codes): 8e Changed type of partition 'Linux' to 'Linux LVM' Command (m for help): p Disk /dev/sdb: 53.7 GB, 53687091200 bytes, 104857600 sectors Units = sectors of 1 * 512 = 512 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes Disk label type: dos Disk identifier: 0x0ba7f903 Device Boot Start End Blocks Id System /dev/sdb1 2048 10487807 5242880 8e Linux LVM Command (m for help): w The partition table has been altered! Calling ioctl() to re-read partition table. Syncing disks. [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# lsblk NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT sda 8:0 0 1000G 0 disk ├─sda1 8:1 0 1G 0 part /boot └─sda2 8:2 0 999G 0 part ├─centos-root 253:0 0 50G 0 lvm / ├─centos-swap 253:1 0 2G 0 lvm [SWAP] └─centos-home 253:2 0 947G 0 lvm /home sdb 8:16 0 50G 0 disk └─sdb1 8:17 0 5G 0 part sr0 11:0 1 1024M 0 rom [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# partprobe #同步分区信息 [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# lsblk NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT sda 8:0 0 1000G 0 disk ├─sda1 8:1 0 1G 0 part /boot └─sda2 8:2 0 999G 0 part ├─centos-root 253:0 0 50G 0 lvm / ├─centos-swap 253:1 0 2G 0 lvm [SWAP] └─centos-home 253:2 0 947G 0 lvm /home sdb 8:16 0 50G 0 disk └─sdb1 8:17 0 5G 0 part sr0 11:0 1 1024M 0 rom [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# lsblk NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT sda 8:0 0 1000G 0 disk ├─sda1 8:1 0 1G 0 part /boot └─sda2 8:2 0 999G 0 part ├─centos-root 253:0 0 50G 0 lvm / ├─centos-swap 253:1 0 2G 0 lvm [SWAP] └─centos-home 253:2 0 947G 0 lvm /home sdb 8:16 0 50G 0 disk └─sdb1 8:17 0 5G 0 part sr0 11:0 1 1024M 0 rom [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# pvcreate /dev/sdb1 #创建物理卷 Physical volume "/dev/sdb1" successfully created. [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# vgcreate vg0 /dev/sdb1 #创建卷组 Volume group "vg0" successfully created [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# lvcreate -n mysql -L 1G vg0 #创建名为mysql的逻辑卷 Logical volume "mysql" created. [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# lvcreate -n binlog -L 1G vg0 #创建名为binlog的逻辑卷 Logical volume "binlog" created. [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# lvs LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert home centos -wi-ao---- 946.99g root centos -wi-ao---- 50.00g swap centos -wi-ao---- 2.00g binlog vg0 -wi-a----- 1.00g mysql vg0 -wi-a----- 1.00g [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# lsblk NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT sda 8:0 0 1000G 0 disk ├─sda1 8:1 0 1G 0 part /boot └─sda2 8:2 0 999G 0 part ├─centos-root 253:0 0 50G 0 lvm / ├─centos-swap 253:1 0 2G 0 lvm [SWAP] └─centos-home 253:2 0 947G 0 lvm /home sdb 8:16 0 50G 0 disk └─sdb1 8:17 0 5G 0 part ├─vg0-mysql 253:3 0 1G 0 lvm └─vg0-binlog 253:4 0 1G 0 lvm sr0 11:0 1 1024M 0 rom [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mkfs.xfs /dev/vg0/mysql meta-data=/dev/vg0/mysql isize=512 agcount=4, agsize=65536 blks = sectsz=512 attr=2, projid32bit=1 = crc=1 finobt=0, sparse=0 data = bsize=4096 blocks=262144, imaxpct=25 = sunit=0 swidth=0 blks naming =version 2 bsize=4096 ascii-ci=0 ftype=1 log =internal log bsize=4096 blocks=2560, version=2 = sectsz=512 sunit=0 blks, lazy-count=1 realtime =none extsz=4096 blocks=0, rtextents=0 [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mkfs.xfs /dev/vg0/binlog meta-data=/dev/vg0/binlog isize=512 agcount=4, agsize=65536 blks = sectsz=512 attr=2, projid32bit=1 = crc=1 finobt=0, sparse=0 data = bsize=4096 blocks=262144, imaxpct=25 = sunit=0 swidth=0 blks naming =version 2 bsize=4096 ascii-ci=0 ftype=1 log =internal log bsize=4096 blocks=2560, version=2 = sectsz=512 sunit=0 blks, lazy-count=1 realtime =none extsz=4096 blocks=0, rtextents=0 [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# blkid /dev/sda1: UUID="8464dcda-1c53-46f4-9af1-c5e0b0321eed" TYPE="xfs" /dev/sda2: UUID="dvckLL-l1Y6-BQtf-VTx5-NHL7-2Gf0-n2ZzXp" TYPE="LVM2_member" /dev/sdb1: UUID="19dBzc-s160-ZgAl-ssGZ-7Mzf-52f3-tAb9jm" TYPE="LVM2_member" /dev/mapper/centos-root: UUID="9a7895fd-f750-43db-a863-052a35542278" TYPE="xfs" /dev/mapper/centos-swap: UUID="d5f2e7d3-4917-431b-8679-bcedee595ff9" TYPE="swap" /dev/mapper/centos-home: UUID="20688187-61a7-4cae-b9a3-f215ea37a0e8" TYPE="xfs" /dev/mapper/vg0-mysql: UUID="34772274-02e8-4943-a2d2-9dad20aa7e8e" TYPE="xfs" /dev/mapper/vg0-binlog: UUID="30f93a13-98d4-49bd-896c-98f0b3b6014e" TYPE="xfs" [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mkdir -pv /mydata/{mysql,binlog} mkdir: created directory ‘/mydata’ mkdir: created directory ‘/mydata/mysql’ mkdir: created directory ‘/mydata/binlog’ [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mount /dev/vg0/mysql /mydata/mysql/ #这是临时挂载，生产环境应该写入"/etc/fstab" [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mount /dev/vg0/binlog /mydata/binlog/ [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# df Filesystem 1K-blocks Used Available Use% Mounted on /dev/mapper/centos-root 52403200 3592648 48810552 7% / devtmpfs 929020 0 929020 0% /dev tmpfs 941148 0 941148 0% /dev/shm tmpfs 941148 8776 932372 1% /run tmpfs 941148 0 941148 0% /sys/fs/cgroup /dev/sda1 1038336 148576 889760 15% /boot /dev/mapper/centos-home 992508420 426364 992082056 1% /home tmpfs 188232 0 188232 0% /run/user/0 /dev/mapper/vg0-mysql 1038336 32992 1005344 4% /mydata/mysql /dev/mapper/vg0-binlog 1038336 32992 1005344 4% /mydata/binlog [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf [mysqld] log_bin = /data/logbin/mysql-bin binlog_format = STATEMENT character-set-server = utf8mb4 default_storage_engine = InnoDB port = 3306 datadir = /mysql/3306/data socket = /mysql/3306/socket/mysql.sock [mysqld_safe] log-error = /mysql/3306/log/mariadb.log pid-file = /mysql/3306/pid/mariadb.pid [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# vim /mysql/3306/etc/my.cnf [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf #咱们也可以规划更详细的目录 [mysqld] log_bin = /mydata/binlog/mysql-bin binlog_format = STATEMENT character-set-server = utf8mb4 default_storage_engine = InnoDB port = 3306 datadir = /mydata/mysql socket = /mydata/mysql/mysql.sock [mysqld_safe] log-error = /mydata/mysql/mariadb.log pid-file = /mydata/mysql/mariadb.pid [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# cp -a /data/logbin/* /mydata/binlog/ [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ll /mydata/binlog/ total 12 -rw-rw---- 1 mysql mysql 351 Nov 5 07:14 mysql-bin.000003 -rw-rw---- 1 mysql mysql 351 Nov 5 07:18 mysql-bin.000004 -rw-rw---- 1 mysql mysql 60 Nov 5 07:14 mysql-bin.index -rw-rw---- 1 mysql mysql 0 Nov 5 07:18 mysql-bin.state [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# cp -a /mysql/3306/data/* /mydata/mysql/ [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ll /mydata/mysql/ total 176204 -rw-rw---- 1 mysql mysql 16384 Nov 5 07:18 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Nov 5 07:18 aria_log_control -rw-rw---- 1 mysql mysql 2926 Nov 5 07:18 ib_buffer_pool -rw-rw---- 1 mysql mysql 79691776 Nov 5 07:18 ibdata1 -rw-rw---- 1 mysql mysql 50331648 Nov 5 07:18 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Oct 26 22:17 ib_logfile1 -rw-rw---- 1 mysql mysql 0 Oct 26 23:26 multi-master.info drwx------ 2 mysql mysql 4096 Oct 26 22:17 mysql -rw-rw---- 1 mysql mysql 29032 Oct 26 22:17 mysql-bin.000001 -rw-rw---- 1 mysql mysql 19 Oct 26 22:17 mysql-bin.index -rw-rw---- 1 mysql mysql 7 Oct 26 22:17 mysql-bin.state -rw-rw---- 1 mysql mysql 3880 Nov 4 18:48 node105.log -rw-rw---- 1 mysql mysql 948 Nov 4 17:41 node105-slow.log drwx------ 2 mysql mysql 20 Oct 26 22:17 performance_schema drwx------ 2 mysql mysql 272 Nov 4 22:03 yinzhengjie [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll /mydata/ total 0 drwxr-xr-x 2 root root 100 Nov 5 08:08 binlog drwxr-xr-x 5 root root 326 Nov 5 08:06 mysql [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# chown mysql:mysql -R /mydata/ [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ll /mydata/ total 0 drwxr-xr-x 2 mysql mysql 100 Nov 5 08:08 binlog drwxr-xr-x 5 mysql mysql 326 Nov 5 08:06 mysql [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ss -ntl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* LISTEN 0 128 :::22 :::* [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# /mysql/3306/mysqld start Starting MySQL... [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ss -ntl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* LISTEN 0 80 :::3306 :::* LISTEN 0 128 :::22 :::* [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mydata/mysql/mysql.sock Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.2.19-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> SHOW BINARY LOGS; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000003 | 351 | | mysql-bin.000004 | 351 | | mysql-bin.000005 | 328 | +------------------+-----------+ 3 rows in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> SYSTEM ls /mydata/binlog mysql-bin.000003 mysql-bin.000004 mysql-bin.000005 mysql-bin.index MariaDB [(none)]> MariaDB [(none)]>
2>.请求锁定所有表
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK; #这个终端不要退出,退出的话就默认解锁啦，要等待以下操作执行完毕 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]>
3>.记录二进制日志文件及事件位置
MariaDB [(none)]> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000005 | 328 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) MariaDB [(none)]>
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mydata/mysql/mysql.sock -e 'SHOW MASTER STATUS' > post.log [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# cat post.log File Position Binlog_Do_DB Binlog_Ignore_DB mysql-bin.000005 328 [root@node105.yinzhengjie.org.cn ~]#
4>.创建快照
[root@node105.yinzhengjie.org.cn ~]# lvcreate -n mysql_snapshot -L 200M -s -p r /dev/vg0/mysql Logical volume "mysql_snapshot" created. [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# lvs LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert home centos -wi-ao---- 946.99g root centos -wi-ao---- 50.00g swap centos -wi-ao---- 2.00g binlog vg0 -wi-ao---- 1.00g mysql vg0 owi-aos--- 1.00g mysql_snapshot vg0 sri-a-s--- 200.00m mysql 0.00 [root@node105.yinzhengjie.org.cn ~]#
5>.释放锁
MariaDB [(none)]> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]>
6>.挂载快照卷，执行数据备份(注意:用户可进行读写操作,但该过程会降低服务器性能)
MariaDB [(none)]> USE yinzhengjie Database changed MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000005 | 328 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SELECT * FROM students; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 25 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> INSERT INTO students (name,age) VALUES ('a',10),('b',20),('c',30); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SELECT * FROM students; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | | 29 | a | 10 | F | NULL | NULL | | 30 | b | 20 | F | NULL | NULL | | 31 | c | 30 | F | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 28 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000005 | 572 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) MariaDB [yinzhengjie]>
[root@node105.yinzhengjie.org.cn ~]# blkid /dev/sda1: UUID="8464dcda-1c53-46f4-9af1-c5e0b0321eed" TYPE="xfs" /dev/sda2: UUID="dvckLL-l1Y6-BQtf-VTx5-NHL7-2Gf0-n2ZzXp" TYPE="LVM2_member" /dev/sdb1: UUID="19dBzc-s160-ZgAl-ssGZ-7Mzf-52f3-tAb9jm" TYPE="LVM2_member" /dev/mapper/centos-root: UUID="9a7895fd-f750-43db-a863-052a35542278" TYPE="xfs" /dev/mapper/centos-swap: UUID="d5f2e7d3-4917-431b-8679-bcedee595ff9" TYPE="swap" /dev/mapper/centos-home: UUID="20688187-61a7-4cae-b9a3-f215ea37a0e8" TYPE="xfs" /dev/mapper/vg0-mysql: UUID="34772274-02e8-4943-a2d2-9dad20aa7e8e" TYPE="xfs" /dev/mapper/vg0-binlog: UUID="30f93a13-98d4-49bd-896c-98f0b3b6014e" TYPE="xfs" /dev/mapper/vg0-mysql_snapshot: UUID="34772274-02e8-4943-a2d2-9dad20aa7e8e" TYPE="xfs" [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mount -o nouuid,norecovery /dev/vg0/mysql_snapshot /mnt/ mount: /dev/mapper/vg0-mysql_snapshot is write-protected, mounting read-only [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ls /mnt/ aria_log.00000001 ibdata1 ibtmp1 multi-master.info mysql-bin.index node105.log yinzhengjie aria_log_control ib_logfile0 mariadb.log mysql mysql-bin.state node105-slow.log ib_buffer_pool ib_logfile1 mariadb.pid mysql-bin.000001 mysql.sock performance_schema [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# tar -cf /root/mysql.tar /mnt/ tar: Removing leading `/' from member names tar: /mnt/mysql.sock: socket ignored [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ll total 190228 -rw-r--r-- 1 root root 3729 Nov 4 22:08 binlog.sql -rw-r--r-- 1 root root 194785280 Nov 5 21:30 mysql.tar -rw-r--r-- 1 root root 67 Nov 5 21:15 post.log [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# lvs LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert home centos -wi-ao---- 946.99g root centos -wi-ao---- 50.00g swap centos -wi-ao---- 2.00g binlog vg0 -wi-ao---- 1.00g mysql vg0 owi-aos--- 1.00g mysql_snapshot vg0 sri-aos--- 200.00m mysql 0.20 [root@node105.yinzhengjie.org.cn ~]#
7>.备份完成后，删除快照卷
[root@node105.yinzhengjie.org.cn ~]# lvs LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert home centos -wi-ao---- 946.99g root centos -wi-ao---- 50.00g swap centos -wi-ao---- 2.00g binlog vg0 -wi-ao---- 1.00g mysql vg0 owi-aos--- 1.00g mysql_snapshot vg0 sri-aos--- 200.00m mysql 0.20 [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# umount /mnt/ [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# lvremove /dev/vg0/mysql_snapshot Do you really want to remove active logical volume vg0/mysql_snapshot? [y/n]: y Logical volume "mysql_snapshot" successfully removed [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# lvs LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert home centos -wi-ao---- 946.99g root centos -wi-ao---- 50.00g swap centos -wi-ao---- 2.00g binlog vg0 -wi-ao---- 1.00g mysql vg0 -wi-ao---- 1.00g [root@node105.yinzhengjie.org.cn ~]#
8>.制定好策略，通过原卷备份二进制日志
[root@node105.yinzhengjie.org.cn ~]# rm -rf /mysql/3307/data/* [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ll /mysql/3307/data/ total 0 [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ll total 190228 -rw-r--r-- 1 root root 3729 Nov 4 22:08 binlog.sql -rw-r--r-- 1 root root 194785280 Nov 5 21:30 mysql.tar -rw-r--r-- 1 root root 67 Nov 5 21:15 post.log [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# tar xf mysql.tar [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ll total 190232 -rw-r--r-- 1 root root 3729 Nov 4 22:08 binlog.sql drwxr-xr-x 5 mysql mysql 4096 Nov 5 08:11 mnt -rw-r--r-- 1 root root 194785280 Nov 5 21:30 mysql.tar -rw-r--r-- 1 root root 67 Nov 5 21:15 post.log [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ls mnt/ aria_log.00000001 ibdata1 ibtmp1 multi-master.info mysql-bin.index node105-slow.log aria_log_control ib_logfile0 mariadb.log mysql mysql-bin.state performance_schema ib_buffer_pool ib_logfile1 mariadb.pid mysql-bin.000001 node105.log yinzhengjie [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mv mnt/* /mysql/3307/data/ [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ll /mysql/3307/data/ total 188500 -rw-rw---- 1 mysql mysql 16384 Nov 5 07:18 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Nov 5 07:18 aria_log_control -rw-rw---- 1 mysql mysql 2926 Nov 5 07:18 ib_buffer_pool -rw-rw---- 1 mysql mysql 79691776 Nov 5 08:11 ibdata1 -rw-rw---- 1 mysql mysql 50331648 Nov 5 08:11 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Oct 26 22:17 ib_logfile1 -rw-rw---- 1 mysql mysql 12582912 Nov 5 08:11 ibtmp1 -rw-rw---- 1 mysql mysql 2720 Nov 5 21:15 mariadb.log -rw-rw---- 1 mysql mysql 5 Nov 5 08:11 mariadb.pid -rw-rw---- 1 mysql mysql 0 Oct 26 23:26 multi-master.info drwx------ 2 mysql mysql 4096 Oct 26 22:17 mysql -rw-rw---- 1 mysql mysql 29032 Oct 26 22:17 mysql-bin.000001 -rw-rw---- 1 mysql mysql 19 Oct 26 22:17 mysql-bin.index -rw-rw---- 1 mysql mysql 7 Oct 26 22:17 mysql-bin.state -rw-rw---- 1 mysql mysql 3880 Nov 4 18:48 node105.log -rw-rw---- 1 mysql mysql 948 Nov 4 17:41 node105-slow.log drwx------ 2 mysql mysql 20 Oct 26 22:17 performance_schema drwx------ 2 mysql mysql 272 Nov 4 22:03 yinzhengjie [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql/3307/socket/mysql.sock Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 10.2.19-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> USE yinzhengjie Database changed MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SELECT * FROM students; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 25 rows in set (0.00 sec) MariaDB [yinzhengjie]>
[root@node105.yinzhengjie.org.cn ~]# ll total 190228 -rw-r--r-- 1 root root 3729 Nov 4 22:08 binlog.sql drwxr-xr-x 2 mysql mysql 6 Nov 5 21:59 mnt -rw-r--r-- 1 root root 194785280 Nov 5 21:30 mysql.tar -rw-r--r-- 1 root root 67 Nov 5 21:15 post.log [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# cat post.log #这条语句记录着咱们备份时数据所在位置 File Position Binlog_Do_DB Binlog_Ignore_DB mysql-bin.000005 328 [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# cd /mydata/binlog/ [root@node105.yinzhengjie.org.cn /mydata/binlog]# [root@node105.yinzhengjie.org.cn /mydata/binlog]# ll total 16 -rw-rw---- 1 mysql mysql 351 Nov 5 07:14 mysql-bin.000003 -rw-rw---- 1 mysql mysql 351 Nov 5 07:18 mysql-bin.000004 -rw-rw---- 1 mysql mysql 572 Nov 5 21:26 mysql-bin.000005 -rw-rw---- 1 mysql mysql 92 Nov 5 08:11 mysql-bin.index [root@node105.yinzhengjie.org.cn /mydata/binlog]# [root@node105.yinzhengjie.org.cn /mydata/binlog]# mysqlbinlog --start-position=328 mysql-bin.000005 > /root/diff.sql #我们找到咱们记录的位置并将该位置后的所有日志内容追加到同一个文件，然后通过"SOURCE"命令对其进行还原。 [root@node105.yinzhengjie.org.cn /mydata/binlog]#
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'sql_log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SET sql_log_bin=OFF; Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'sql_log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | OFF | +---------------+-------+ 1 row in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SELECT * FROM students; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 25 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SOURCE /root/diff.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Database changed Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Charset changed Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SELECT * FROM students; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | | 29 | a | 10 | F | NULL | NULL | | 30 | b | 20 | F | NULL | NULL | | 31 | c | 30 | F | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 28 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SET sql_log_bin=ON; Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'sql_log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) MariaDB [yinzhengjie]>
当你的才华还撑不起你的野心的时候，你就应该静下心来学习。当你的能力还驾驭不了你的目标的时候，你就应该沉下心来历练。问问自己，想要怎样的人生。
欢迎交流学习技术交流，个人微信: "JasonYin2020"（添加时请备注来源及意图备注）
作者: 尹正杰, 博客: https://www.cnblogs.com/yinzhengjie/p/11795536.html
