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//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 *: *:*
LISTEN ::: :::*
LISTEN ::: :::*
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 10.2.-MariaDB-log MariaDB Server Copyright (c) , , 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 |
+--------------------+
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 |
+-----------------------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| | Shi Zhongyu | | M | | |
| | Shi Potian | | M | | |
| | Xie Yanke | | M | | |
| | Ding Dian | | M | | |
| | Yu Yutong | | M | | |
| | Shi Qing | | M | | NULL |
| | Xi Ren | | F | | NULL |
| | Lin Daiyu | | F | | NULL |
| | Ren Yingying | | F | | NULL |
| | Yue Lingshan | | F | | NULL |
| | Yuan Chengzhi | | M | | NULL |
| | Wen Qingqing | | F | | NULL |
| | Tian Boguang | | M | | NULL |
| | Lu Wushuang | | F | | NULL |
| | Duan Yu | | M | | NULL |
| | Xu Zhu | | M | | NULL |
| | Lin Chong | | M | | NULL |
| | Hua Rong | | M | | NULL |
| | Xue Baochai | | F | | NULL |
| | Diao Chan | | F | | NULL |
| | Huang Yueying | | F | | NULL |
| | Xiao Qiao | | F | | NULL |
| | Ma Chao | | M | | NULL |
| | Xu Xian | | M | NULL | NULL |
| | Sun Dasheng | | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> QUIT
Bye
[root@node105.yinzhengjie.org.cn ~]#
查看3306实例的数据库信息
[root@node105.yinzhengjie.org.cn ~]# /mysql//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 *: *:*
LISTEN ::: :::*
LISTEN ::: :::*
LISTEN ::: :::*
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 10.2.-MariaDB MariaDB Server Copyright (c) , , 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 |
+--------------------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> QUIT
Bye
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]#
查看3307实例数据库信息
2>.关闭两个实例并将主节点的数据拷贝到从节点并修改相应的权限
[root@node105.yinzhengjie.org.cn ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN *: *:*
LISTEN ::: :::*
LISTEN ::: :::*
LISTEN ::: :::*
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# /mysql//mysqld stop
Stoping MySQL...
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# /mysql//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 *: *:*
LISTEN ::: :::*
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]#
关闭3306和3307两个数据库实例
[root@node105.yinzhengjie.org.cn ~]# ll /mysql//data/
total
-rw-rw---- mysql mysql Nov : aria_log.
-rw-rw---- mysql mysql Nov : aria_log_control
-rw-rw---- mysql mysql Nov : ib_buffer_pool
-rw-rw---- mysql mysql Nov : ibdata1
-rw-rw---- mysql mysql Nov : ib_logfile0
-rw-rw---- mysql mysql Oct : ib_logfile1
-rw-rw---- mysql mysql Oct : multi-master.info
drwx------ mysql mysql Oct : mysql
-rw-rw---- mysql mysql Oct : mysql-bin.
-rw-rw---- mysql mysql Oct : mysql-bin.index
-rw-rw---- mysql mysql Oct : mysql-bin.state
-rw-rw---- mysql mysql Nov : node105.log
-rw-rw---- mysql mysql Nov : node105-slow.log
drwx------ mysql mysql Oct : performance_schema
drwx------ mysql mysql Nov : yinzhengjie
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll /mysql//data/
total
-rw-rw---- mysql mysql Nov : aria_log.
-rw-rw---- mysql mysql Nov : aria_log_control
-rw-rw---- mysql mysql Nov : ib_buffer_pool
-rw-rw---- mysql mysql Nov : ibdata1
-rw-rw---- mysql mysql Nov : ib_logfile0
-rw-rw---- mysql mysql Oct : ib_logfile1
-rw-rw---- mysql mysql Oct : multi-master.info
drwx------ mysql mysql Oct : mysql
-rw-rw---- mysql mysql Oct : mysql-bin.
-rw-rw---- mysql mysql Oct : mysql-bin.index
-rw-rw---- mysql mysql Oct : mysql-bin.state
drwx------ mysql mysql Oct : performance_schema
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# scp -r /mysql//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 ~]# scp -r /mysql/3306/data/* /mysql/3307/data/ #生产环境可用使用tar压缩再拷贝
[root@node105.yinzhengjie.org.cn ~]# ll /mysql//data/
total
-rw-rw---- mysql mysql Nov : aria_log.
-rw-rw---- mysql mysql Nov : aria_log_control
-rw-rw---- mysql mysql Nov : ib_buffer_pool
-rw-rw---- mysql mysql Nov : ibdata1
-rw-rw---- mysql mysql Nov : ib_logfile0
-rw-rw---- mysql mysql Oct : ib_logfile1
-rw-rw---- mysql mysql Oct : multi-master.info
drwx------ mysql mysql Oct : mysql
-rw-rw---- mysql mysql Oct : mysql-bin.
-rw-rw---- mysql mysql Oct : mysql-bin.index
-rw-rw---- mysql mysql Oct : mysql-bin.state
-rw-rw---- mysql mysql Nov : node105.log
-rw-rw---- mysql mysql Nov : node105-slow.log
drwx------ mysql mysql Oct : performance_schema
drwx------ mysql mysql Nov : yinzhengjie
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll /mysql//data/
total
-rw-rw---- mysql mysql Nov : aria_log.
-rw-rw---- mysql mysql Nov : aria_log_control
-rw-rw---- mysql mysql Nov : ib_buffer_pool
-rw-rw---- mysql mysql Nov : ibdata1
-rw-rw---- mysql mysql Nov : ib_logfile0
-rw-rw---- mysql mysql Nov : ib_logfile1
-rw-rw---- mysql mysql Nov : multi-master.info
drwx------ mysql mysql Oct : mysql
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
-rw-rw---- mysql mysql Nov : mysql-bin.state
-rw-r----- root root Nov : node105.log
-rw-r----- root root Nov : node105-slow.log
drwx------ mysql mysql Oct : performance_schema
drwx------ root root Nov : 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//data/
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll /mysql//data/
total
-rw-rw---- mysql mysql Nov : aria_log.
-rw-rw---- mysql mysql Nov : aria_log_control
-rw-rw---- mysql mysql Nov : ib_buffer_pool
-rw-rw---- mysql mysql Nov : ibdata1
-rw-rw---- mysql mysql Nov : ib_logfile0
-rw-rw---- mysql mysql Nov : ib_logfile1
-rw-rw---- mysql mysql Nov : multi-master.info
drwx------ mysql mysql Oct : mysql
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
-rw-rw---- mysql mysql Nov : mysql-bin.state
-rw-r----- mysql mysql Nov : node105.log
-rw-r----- mysql mysql Nov : node105-slow.log
drwx------ mysql mysql Oct : performance_schema
drwx------ mysql mysql Nov : yinzhengjie
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# chown -R mysql:mysql /mysql/3307/data/
3>.启动从节点实例验证备份是否成功
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# /mysql//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 *: *:*
LISTEN ::: :::*
LISTEN ::: :::*
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 10.2.-MariaDB MariaDB Server Copyright (c) , , 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 |
+--------------------+
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 |
+-----------------------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| | Shi Zhongyu | | M | | |
| | Shi Potian | | M | | |
| | Xie Yanke | | M | | |
| | Ding Dian | | M | | |
| | Yu Yutong | | M | | |
| | Shi Qing | | M | | NULL |
| | Xi Ren | | F | | NULL |
| | Lin Daiyu | | F | | NULL |
| | Ren Yingying | | F | | NULL |
| | Yue Lingshan | | F | | NULL |
| | Yuan Chengzhi | | M | | NULL |
| | Wen Qingqing | | F | | NULL |
| | Tian Boguang | | M | | NULL |
| | Lu Wushuang | | F | | NULL |
| | Duan Yu | | M | | NULL |
| | Xu Zhu | | M | | NULL |
| | Lin Chong | | M | | NULL |
| | Hua Rong | | M | | NULL |
| | Xue Baochai | | F | | NULL |
| | Diao Chan | | F | | NULL |
| | Huang Yueying | | F | | NULL |
| | Xiao Qiao | | F | | NULL |
| | Ma Chao | | M | | NULL |
| | Xu Xian | | M | NULL | NULL |
| | Sun Dasheng | | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
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 : 1000G disk
├─sda1 : 1G part /boot
└─sda2 : 999G part
├─centos-root : 50G lvm /
├─centos-swap : 2G lvm [SWAP]
└─centos-home : 947G lvm /home
sdb : 50G disk
sr0 : 1024M 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.). 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, bytes, sectors
Units = sectors of * = bytes
Sector size (logical/physical): bytes / bytes
I/O size (minimum/optimal): bytes / bytes
Disk label type: dos
Disk identifier: 0x0ba7f903 Device Boot Start End Blocks Id System Command (m for help): n
Partition type:
p primary ( primary, extended, free)
e extended
Select (default p): p
Partition number (-, default ):
First sector (-, default ):
Using default value
Last sector, +sectors or +size{K,M,G} (-, default ): +5G
Partition of type Linux and of size GiB is set Command (m for help): t
Selected partition
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, bytes, sectors
Units = sectors of * = bytes
Sector size (logical/physical): bytes / bytes
I/O size (minimum/optimal): bytes / bytes
Disk label type: dos
Disk identifier: 0x0ba7f903 Device Boot Start End Blocks Id System
/dev/sdb1 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 : 1000G disk
├─sda1 : 1G part /boot
└─sda2 : 999G part
├─centos-root : 50G lvm /
├─centos-swap : 2G lvm [SWAP]
└─centos-home : 947G lvm /home
sdb : 50G disk
└─sdb1 : 5G part
sr0 : 1024M 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 : 1000G disk
├─sda1 : 1G part /boot
└─sda2 : 999G part
├─centos-root : 50G lvm /
├─centos-swap : 2G lvm [SWAP]
└─centos-home : 947G lvm /home
sdb : 50G disk
└─sdb1 : 5G part
sr0 : 1024M rom
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# partprobe #同步分区信息
[root@node105.yinzhengjie.org.cn ~]# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda : 1000G disk
├─sda1 : 1G part /boot
└─sda2 : 999G part
├─centos-root : 50G lvm /
├─centos-swap : 2G lvm [SWAP]
└─centos-home : 947G lvm /home
sdb : 50G disk
└─sdb1 : 5G part
sr0 : 1024M 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---- .99g
root centos -wi-ao---- .00g
swap centos -wi-ao---- .00g
binlog vg0 -wi-a----- .00g
mysql vg0 -wi-a----- .00g
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda : 1000G disk
├─sda1 : 1G part /boot
└─sda2 : 999G part
├─centos-root : 50G lvm /
├─centos-swap : 2G lvm [SWAP]
└─centos-home : 947G lvm /home
sdb : 50G disk
└─sdb1 : 5G part
├─vg0-mysql : 1G lvm
└─vg0-binlog : 1G lvm
sr0 : 1024M 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= agcount=, agsize= blks
= sectsz= attr=, projid32bit=
= crc= finobt=, sparse=
data = bsize= blocks=, imaxpct=
= sunit= swidth= blks
naming =version bsize= ascii-ci= ftype=
log =internal log bsize= blocks=, version=
= sectsz= sunit= blks, lazy-count=
realtime =none extsz= blocks=, rtextents=
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mkfs.xfs /dev/vg0/binlog
meta-data=/dev/vg0/binlog isize= agcount=, agsize= blks
= sectsz= attr=, projid32bit=
= crc= finobt=, sparse=
data = bsize= blocks=, imaxpct=
= sunit= swidth= blks
naming =version bsize= ascii-ci= ftype=
log =internal log bsize= blocks=, version=
= sectsz= sunit= blks, lazy-count=
realtime =none extsz= blocks=, rtextents=
[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 % /
devtmpfs % /dev
tmpfs % /dev/shm
tmpfs % /run
tmpfs % /sys/fs/cgroup
/dev/sda1 % /boot
/dev/mapper/centos-home % /home
tmpfs % /run/user/
/dev/mapper/vg0-mysql % /mydata/mysql
/dev/mapper/vg0-binlog % /mydata/binlog
[root@node105.yinzhengjie.org.cn ~]#
格式化逻辑卷并挂载到指定路径
[root@node105.yinzhengjie.org.cn ~]# cat /mysql//etc/my.cnf
[mysqld]
log_bin = /data/logbin/mysql-bin
binlog_format = STATEMENT
character-set-server = utf8mb4
default_storage_engine = InnoDB
port =
datadir = /mysql//data
socket = /mysql//socket/mysql.sock [mysqld_safe]
log-error = /mysql//log/mariadb.log
pid-file = /mysql//pid/mariadb.pid
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# vim /mysql//etc/my.cnf
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# cat /mysql//etc/my.cnf #咱们也可以规划更详细的目录
[mysqld]
log_bin = /mydata/binlog/mysql-bin
binlog_format = STATEMENT
character-set-server = utf8mb4
default_storage_engine = InnoDB
port =
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 ~]#
修改MySQL实例的配置文件并将数据文件和二进制文件拷贝到逻辑卷路径中
[root@node105.yinzhengjie.org.cn ~]# ll /mydata/
total
drwxr-xr-x root root Nov : binlog
drwxr-xr-x root root Nov : 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
drwxr-xr-x mysql mysql Nov : binlog
drwxr-xr-x mysql mysql Nov : 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 *: *:*
LISTEN ::: :::*
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# /mysql//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 *: *:*
LISTEN ::: :::*
LISTEN ::: :::*
[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
Server version: 10.2.-MariaDB-log MariaDB Server Copyright (c) , , 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. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SYSTEM ls /mydata/binlog
mysql-bin. mysql-bin. mysql-bin. 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]>
修改数据库中表的内容（在备份之后故意往students表中添加部分信息，让备份数据和现有数据存在差异，便于下面进行还原操作）
[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. 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. 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
-rw-r--r-- root root Nov : binlog.sql
-rw-r--r-- root root Nov : mysql.tar
-rw-r--r-- root root Nov : 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---- .99g
root centos -wi-ao---- .00g
swap centos -wi-ao---- .00g
binlog vg0 -wi-ao---- .00g
mysql vg0 owi-aos--- .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---- .99g
root centos -wi-ao---- .00g
swap centos -wi-ao---- .00g
binlog vg0 -wi-ao---- .00g
mysql vg0 owi-aos--- .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---- .99g
root centos -wi-ao---- .00g
swap centos -wi-ao---- .00g
binlog vg0 -wi-ao---- .00g
mysql vg0 -wi-ao---- .00g
[root@node105.yinzhengjie.org.cn ~]#
8>.制定好策略，通过原卷备份二进制日志
[root@node105.yinzhengjie.org.cn ~]# rm -rf /mysql//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]>
利用备份的文件还原数据库，但发现students表中丢失了部分数据
[root@node105.yinzhengjie.org.cn ~]# ll
total
-rw-r--r-- root root Nov : binlog.sql
drwxr-xr-x mysql mysql Nov : mnt
-rw-r--r-- root root Nov : mysql.tar
-rw-r--r-- root root Nov : 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.
[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
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
[root@node105.yinzhengjie.org.cn /mydata/binlog]#
[root@node105.yinzhengjie.org.cn /mydata/binlog]# mysqlbinlog --start-position= mysql-bin. > /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 |
+---------------+-------+
row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SET sql_log_bin=OFF;
Query OK, rows affected (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | OFF |
+---------------+-------+
row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| | Shi Zhongyu | | M | | |
| | Shi Potian | | M | | |
| | Xie Yanke | | M | | |
| | Ding Dian | | M | | |
| | Yu Yutong | | M | | |
| | Shi Qing | | M | | NULL |
| | Xi Ren | | F | | NULL |
| | Lin Daiyu | | F | | NULL |
| | Ren Yingying | | F | | NULL |
| | Yue Lingshan | | F | | NULL |
| | Yuan Chengzhi | | M | | NULL |
| | Wen Qingqing | | F | | NULL |
| | Tian Boguang | | M | | NULL |
| | Lu Wushuang | | F | | NULL |
| | Duan Yu | | M | | NULL |
| | Xu Zhu | | M | | NULL |
| | Lin Chong | | M | | NULL |
| | Hua Rong | | M | | NULL |
| | Xue Baochai | | F | | NULL |
| | Diao Chan | | F | | NULL |
| | Huang Yueying | | F | | NULL |
| | Xiao Qiao | | F | | NULL |
| | Ma Chao | | M | | NULL |
| | Xu Xian | | M | NULL | NULL |
| | Sun Dasheng | | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SOURCE /root/diff.sql
Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Database changed
Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Charset changed
Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| | Shi Zhongyu | | M | | |
| | Shi Potian | | M | | |
| | Xie Yanke | | M | | |
| | Ding Dian | | M | | |
| | Yu Yutong | | M | | |
| | Shi Qing | | M | | NULL |
| | Xi Ren | | F | | NULL |
| | Lin Daiyu | | F | | NULL |
| | Ren Yingying | | F | | NULL |
| | Yue Lingshan | | F | | NULL |
| | Yuan Chengzhi | | M | | NULL |
| | Wen Qingqing | | F | | NULL |
| | Tian Boguang | | M | | NULL |
| | Lu Wushuang | | F | | NULL |
| | Duan Yu | | M | | NULL |
| | Xu Zhu | | M | | NULL |
| | Lin Chong | | M | | NULL |
| | Hua Rong | | M | | NULL |
| | Xue Baochai | | F | | NULL |
| | Diao Chan | | F | | NULL |
| | Huang Yueying | | F | | NULL |
| | Xiao Qiao | | F | | NULL |
| | Ma Chao | | M | | NULL |
| | Xu Xian | | M | NULL | NULL |
| | Sun Dasheng | | M | NULL | NULL |
| | a | | F | NULL | NULL |
| | b | | F | NULL | NULL |
| | c | | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SET sql_log_bin=ON;
Query OK, rows affected (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
row in set (0.00 sec) MariaDB [yinzhengjie]>
还原数据时记得临时关闭二进制日志功能（sql_log_bin）,还原成功后记得再将二进制日志功能打开。