数据库物理备份就是拷贝数据库数据文件，但是需要考虑的问题也有：

需要确保数据文件的时间一致性？

1.冷备：停止数据库，实现物理备份;

但是一个真正生产数据库不能实现离线操作，那我们就可以通过数据库的主从复制实现；

主数据库服务器的数据同步到从服务器，而我们在主从复制中从服务器停止数据库是不会影响业务的，就能够实现物理备份；备份完成从服务器上线后会自动从主服务器同步数据；这是一种理想的备份方案；

2.第二种方法是基于LVM2的快照实现数据库的备份，可以实现几乎热备，

前提数据库数据必须放置在物理卷上；

下面我们就做基于LVM2的快照实现数据库备份的实现的实验


一.准备工作：

1.备份所有数据库。

[root@node1 ~]# mysqldump -A --lock-all-tables > /backup/all.sql


停止mysqld服务器：

[root@node1 ~]# service mysqld stop
Shutting down MySQL..                                      [  OK  ]


2.LVM逻辑卷构建

格式化磁盘：

[root@node1 ~]# echo -n -e "n\np\n3\n\n+10G\nt\n3\n8e\n\nw\n" |fdisk /dev/sda
[root@node1 ~]# partx -a /dev/sda
BLKPG: Device or resource busy
error adding partition 1
BLKPG: Device or resource busy
error adding partition 2
BLKPG: Device or resource busy
error adding partition 3
物理卷创建：
[root@node1 ~]# pvcreate /dev/sda3
  Physical volume "/dev/sda3" successfully created
卷组创建：
[root@node1 ~]# vgcreate myvg /dev/sda3 
  Volume group "myvg" successfully created
逻辑卷创建：
[root@node1 ~]# lvcreate -L 5G -n mylv myvg
  Logical volume "mylv" created
[root@node1 ~]# lvs
  LV   VG   Attr       LSize  Pool Origin Data%  Meta%  Move Log Cpy%Sync Convert
  mylv myvg -wi-a-----  5.00g                                                    
  root vg0  -wi-ao---- 20.00g                                                    
  swap vg0  -wi-ao----  2.00g                                                    
  usr  vg0  -wi-ao---- 10.00g                                                    
  var  vg0  -wi-ao---- 20.00g


格式化逻辑卷为ext4文件系统：

[root@node1 ~]# mke2fs -t ext4 /dev/myvg/mylv


挂载逻辑卷到数据库数据目录：

[root@node1 ~]# mount /dev/myvg/mylv  /mydata/data/


mysql访问此目录需要权限，属主属组更改为mysql：

[root@node1 ~]# chown -R mysql.mysql /mydata/data/


3.初始化mysql

由于我们挂载在/mydata/data上了，原来的mysql数据文件都被隐藏了，我们需要初始化mysql；

[root@node1 root]# cd /usr/local/mysql
[root@node1 mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data


启动mysqld服务器：

[root@node1 mysql]# service mysqld start
Starting MySQL.                                            [  OK  ]


恢复我们刚才备份的数据库：

[root@node1 mysql]# mysql -u root -p < /backup/all.sql  
Enter password: 
[root@node1 mysql]# 
是空密码；

登录数据库是拒绝的：

[root@node1 mysql]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

初始化安装后我们root目录下.my.cnf中定义的数据库root用户的密码无法生效了，我们需要使用空密码登录数据库，执行权限刷新操作，如下命令：

[root@node1 mysql]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.0.13-MariaDB-log Source distribution
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> \q
Bye

下面我们就能登录数据库了，我们查看恢复的情况：

 

[root@node1 mysql]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.0.13-MariaDB-log Source distribution
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use hellodb;
Database changed
MariaDB [hellodb]> select * from tb1;
+------+
| id   |
+------+
|    1 |
|    2 |
|   22 |
|    9 |
|   20 |
+------+
5 rows in set (0.00 sec)
MariaDB [hellodb]> \q
Bye

跟上篇博客的数据是一致的，前提准备完成；



二.实现阶段

假设现在数据库是线上数据库服务器，有许多用户连接到数据库执行读写操作，我们备份操作需要考虑的方面：

1.如果是备份单个库，那么要确定这个库的引擎是InnoDB，且必须是每表的存放都是单个表空间的（innodb_file_per_table)；否则，备份必须执行全库备份。

MariaDB [(none)]> show  global variables like 'innodb_file_per%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)


我们可以查看hellodb数据库中的文件：

[root@node1 ~]# ls /mydata/data/hellodb/
classes.frm  coc.frm  courses.frm  db.opt            myisam_table.MYD  scores.frm  students.frm  tb1.frm  teachers.frm  toc.frm  v1.frm
classes.ibd  coc.ibd  courses.ibd  myisam_table.frm  myisam_table.MYI  scores.ibd  students.ibd  tb1.ibd  teachers.ibd  toc.ibd


我们备份hellodb这个数据库就只需要复制这个目录里的所有表文件即可，但是可能会出问题，大部分环境是可以的。


2.备份之前需要锁定表；

在生产环境中你加这个锁可能耗时很长，因为生产环境中有很多用户在访问数据库进行读写操作，必须等用户操作完成后才能加上锁；

[root@node1 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.0.13-MariaDB-log Source distribution
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#请求锁定表，
MariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
#滚动二进制日志
MariaDB [(none)]> flush logs;
Query OK, 0 rows affected (0.25 sec)
#查看现在使用的哪个二进制文件的哪个位置；我们可以将这个信息保存下来，以后做增量备份时就需要充这个二进制日志文件的这个位置进行。
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000009 |      365 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
#查看二进制日志
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     67334 |
| mysql-bin.000002 |    977605 |
| mysql-bin.000003 |       345 |
| mysql-bin.000004 |       345 |
| mysql-bin.000005 |       345 |
| mysql-bin.000006 |       345 |
| mysql-bin.000007 |       345 |
| mysql-bin.000008 |    542426 |
| mysql-bin.000009 |       365 |
+------------------+-----------+
9 rows in set (0.00 sec)


不要退出，不然就释放了。



3.进行LVM2快照创建

实际环境中创建快照时我们需要考虑这段时间内数据的变化量大小；

我们重新开启一个进程进行快照的创建：

[root@node1 mysql]# lvcreate -L 200M -n mydata-snap /dev/myvg/mylv -s -p r
  Logical volume "mydata-snap" created


4.创建完成快照后立即解锁

接上面锁表的操作：
#进行解锁；
MariaDB [(none)]> unlock tables;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> \q
Bye

至此，用户又可以读写操作数据库了；


5.现在我们就能备份了

创建目录：
[root@node1 ~]# mkdir /snap
挂载快照卷到此目录：
[root@node1 ~]# mount /dev/myvg/mydata-snap /snap/
mount: block device /dev/mapper/myvg-mydata--snap is write-protected, mounting read-only
挂载完成后查看目录里的文件：
[root@node1 ~]# ls /snap/
aria_log.00000001  mysql             mysql-bin.000007
aria_log_control   mysql-bin.000001  mysql-bin.000008
hellodb            mysql-bin.000002  mysql-bin.000009
ibdata1            mysql-bin.000003  mysql-bin.index
ib_logfile0        mysql-bin.000004  node1.stu31.com.pid
ib_logfile1        mysql-bin.000005  performance_schema
multi-master.info  mysql-bin.000006  test

所有的数据库都在这里了。

下面我们就能实现将备份好的文件同步推送到其他节点的服务器上或者同步到其他目录：

我这里为了演示就只推送到/backup目录下了，并且我只推送了一个数据库hellodb：

[root@node1 ~]# cd /snap/
[root@node1 snap]# ls
aria_log.00000001  mysql             mysql-bin.000007
aria_log_control   mysql-bin.000001  mysql-bin.000008
hellodb            mysql-bin.000002  mysql-bin.000009
ibdata1            mysql-bin.000003  mysql-bin.index
ib_logfile0        mysql-bin.000004  node1.stu31.com.pid
ib_logfile1        mysql-bin.000005  performance_schema
multi-master.info  mysql-bin.000006  test
[root@node1 snap]# rm -rf /backup/hellodb-2015-01-22-19-34-04/
[root@node1 snap]# rsync -a hellodb /backup/hellodb-`date +%F-%H-%M-%S`
[root@node1 snap]# ls /backup/hellodb-2015-01-22-20-21-48/
hellodb
[root@node1 snap]# ls /backup/hellodb-2015-01-22-20-21-48/hellodb/
classes.frm  courses.ibd       scores.frm    tb1.ibd       v1.frm
classes.ibd  db.opt            scores.ibd    teachers.frm
coc.frm      myisam_table.frm  students.frm  teachers.ibd
coc.ibd      myisam_table.MYD  students.ibd  toc.frm
courses.frm  myisam_table.MYI  tb1.frm       toc.ibd


备份完成了。

考虑一个问题：

如果我们备份的只是单个数据库，而且基于二进制日志文件去做增量备份会出现将所有库的事件都记录下来了，我们需要使用文本分析工具对备份的二进制文件进行筛选，筛选出属于那个指定数据库的事件进行导入。


6.我们进行删除操作；

[root@node1 mysql]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.0.13-MariaDB-log Source distribution
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use hellodb
Database changed
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| myisam_table      |
| scores            |
| students          |
| tb1               |
| teachers          |
| toc               |
| v1                |
+-------------------+
10 rows in set (0.00 sec)
#我们删除其中一个表；删除后我们的快照空间的文件还是包含有此表的文件。
MariaDB [hellodb]> drop table myisam_table;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> \q
Bye


对比查看数据库和快照卷的文件可以发现：

[root@node1 mysql]# ls /mydata/data/hellodb/
classes.frm  courses.frm  scores.ibd    tb1.ibd       toc.ibd
classes.ibd  courses.ibd  students.frm  teachers.frm  v1.frm
coc.frm      db.opt       students.ibd  teachers.ibd
coc.ibd      scores.frm   tb1.frm       toc.frm
[root@node1 mysql]# ls /snap/hellodb/
classes.frm  courses.ibd       scores.frm    tb1.ibd       v1.frm
classes.ibd  db.opt            scores.ibd    teachers.frm
coc.frm      myisam_table.frm  students.frm  teachers.ibd
coc.ibd      myisam_table.MYD  students.ibd  toc.frm
courses.frm  myisam_table.MYI  tb1.frm       toc.ibd

快照卷还包含有myisam_table这个表的数据文件。


7.我们将快照中的文件拷贝到/backup目录中：

[root@node1 mysql]# cp -a /snap /backup
[root@node1 mysql]# ls /backup/
20150122.sql.tgz  daily                        increment-2015-01-22.sql
all.sql           hellodb-2015-01-22-20-21-48  snap
bak.log           hellodb-2015-01-22.sql
[root@node1 mysql]# ls /backup/snap/
aria_log.00000001  mysql             mysql-bin.000007
aria_log_control   mysql-bin.000001  mysql-bin.000008
hellodb            mysql-bin.000002  mysql-bin.000009
ibdata1            mysql-bin.000003  mysql-bin.index
ib_logfile0        mysql-bin.000004  node1.stu31.com.pid
ib_logfile1        mysql-bin.000005  performance_schema
multi-master.info  mysql-bin.000006  test

所有数据库的文件都在这里了，包含二进制文件；



8.假如我们继续操作将数据库hellodb误删除。

[root@node1 snap]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.0.13-MariaDB-log Source distribution
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#删除hellodb数据库
MariaDB [(none)]> drop database hellodb;
Query OK, 9 rows affected (1.83 sec)
MariaDB [(none)]> \q
Bye


9.备份数据库的二进制日志文件

由于我们备份快照后，其后的修改操作我们并没有备份，我们需要读取二进制日志文件，找出drop数据库的时间点，导出删除数据库之前的所有二进制日志。

[root@node1 ~]# mysqlbinlog /mydata/data/mysql-bin.000009
略…
# at 558
#150122 20:29:08 server id 1  end_log_pos 645   Query   thread_id=12    exec_time=2      error_code=0
SET TIMESTAMP=1421929748/*!*/;
drop database hellodb
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;


可以发现时间点是558，我们将时间点为558之前的所有操作日志都导出到一个文件中：

[root@node1 ~]# mysqlbinlog --stop-position=558 /mydata/data/mysql-bin.000009 >/tmp/b.sql
[root@node1 ~]# ls /tmp
all1.sql.xz  hotbackup.sql         mysql.sock
a.sql        ks-script-t3nzeD      warmbackup.sql
b.sql        ks-script-t3nzeD.log  yum.log


10.全库恢复操作

假如生产环境中遇到重大故障后，我们需要赶快停止数据库，进行数据库全库恢复操作。

停止数据库服务器：

[root@node1 ~]# service mysqld stop
Shutting down MySQL..                                      [  OK  ]
删除损坏的数据库数据文件：
[root@node1 ~]# rm -rf /mydata/data/*
将快照的备份文件导入数据库数据目录：
[root@node1 ~]# cp -a /backup/snap/* /mydata/data/
必须保证恢复回来的数据的属主属组无改变：
[root@node1 ~]# ls -l /mydata/data/
total 112216
-rw-rw---- 1 mysql mysql    16384 Jan 22 20:14 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Jan 22 20:14 aria_log_control
drwx------ 2 mysql mysql     4096 Jan 22 20:16 hellodb
-rw-rw---- 1 mysql mysql 12582912 Jan 22 20:16 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Jan 22 20:16 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Jan 22 20:13 ib_logfile1
-rw-rw---- 1 mysql mysql        0 Jan 22 20:14 multi-master.info
drwx------ 2 mysql root      4096 Jan 22 20:16 mysql
-rw-rw---- 1 mysql mysql    67334 Jan 22 20:13 mysql-bin.000001
-rw-rw---- 1 mysql mysql   977605 Jan 22 20:13 mysql-bin.000002
-rw-rw---- 1 mysql mysql      345 Jan 22 20:13 mysql-bin.000003
-rw-rw---- 1 mysql mysql      345 Jan 22 20:13 mysql-bin.000004
-rw-rw---- 1 mysql mysql      345 Jan 22 20:13 mysql-bin.000005
-rw-rw---- 1 mysql mysql      345 Jan 22 20:13 mysql-bin.000006
-rw-rw---- 1 mysql mysql      345 Jan 22 20:14 mysql-bin.000007
-rw-rw---- 1 mysql mysql   542426 Jan 22 20:17 mysql-bin.000008
-rw-rw---- 1 mysql mysql      365 Jan 22 20:17 mysql-bin.000009
-rw-rw---- 1 mysql mysql      171 Jan 22 20:17 mysql-bin.index
-rw-rw---- 1 mysql mysql        5 Jan 22 20:14 node1.stu31.com.pid
drwx------ 2 mysql mysql     4096 Jan 22 20:13 performance_schema
drwx------ 2 mysql root      4096 Jan 22 20:16 test
恢复完成后启动mysqld数据库服务：
[root@node1 ~]# service mysqld start
Starting MySQL.                                            [  OK  ]
[root@node1 ~]#


我们连入mysql将后续修改的数据导入进数据库：

注意：

mysqldump做的备份，数据还原时，会产生二进制日志，如果针对大数据量的数据库，这种二进制日志文件是无用的，可以不记录，我们就需要在导入文件是临时关闭二进制日志记录。

[root@node1 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.0.13-MariaDB-log Source distribution
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use hellodb;
Database changed
#我们原来是删除了myisam_table 表的。
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| myisam_table      |
| scores            |
| students          |
| tb1               |
| teachers          |
| toc               |
| v1                |
+-------------------+
10 rows in set (0.00 sec)
#临时关闭记录二进制日志文件，
MariaDB [hellodb]> set session sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
#恢复从备份快照后到删除hellodb数据库之间的操作；
MariaDB [hellodb]> source /tmp/b.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)
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, 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)
#开启二进制记录文件；
MariaDB [hellodb]> set session sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
#可以发现myisqm_table表被删除了，还原成功；
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| tb1               |
| teachers          |
| toc               |
| v1                |
+-------------------+
9 rows in set (0.00 sec)
MariaDB [hellodb]> \q
Bye


至此，通过LVM逻辑卷进行数据库备份恢复的实验就完成了。