MySQL备份与复原
发布时间：2022-04-06 11:12:32 所属栏目：MySql教程 来源：互联网
导读：通过LVM逻辑卷实现MySQL备份及还原（几乎热备）： 前提： 1、数据文件要在逻辑卷上； 2、此逻辑卷所在卷组必须有足够的空间使用快照卷； 3、数据文件和事务日志要在同一个逻辑卷上； 步骤： 1、打开会话，施加读锁，锁定所有表； mysql FLUSH TABLES WITH
|
通过LVM逻辑卷实现MySQL备份及还原（几乎热备）： 前提： 1、数据文件要在逻辑卷上； 2、此逻辑卷所在卷组必须有足够的空间使用快照卷； 3、数据文件和事务日志要在同一个逻辑卷上； 步骤： 1、打开会话，施加读锁，锁定所有表； mysql> FLUSH TABLES WITH READ LOCK; #刷新表并对表施加读锁 mysql> FLUSH LOGS; #滚动日志 2、通过另一个终端，保存二进制日志文件及相关位置信息； [root@lamp ~]# mysql -uroot -p -e 'SHOW MASTER STATUSG' > /path/to/master.info #不登录mysql客户端直接查看位置状态，并保存位置信息到相应目录 3、创建快照卷 [root@lamp ~]# lvcreate -L SIZE -s -p r -n LV_NAME /path/to/source_lv #创建快照卷，-s:指定为所创建的是快照卷s=snapshot快照; -p:指定权限p=permission许可； r:读权限;-n:指定快照名称；LV_NAME:快照名称;/path/to/source_lv:针对哪个逻辑卷目录 路径做快照;-L:指定快照卷大小。 4、释放锁 mysql> UNLOCK TABLES; #释放锁 5、挂载快照卷，并备份； mount /dev/myvg/mydata-snap /mnt -o ro #挂载快照卷至/mnt目录，只读挂载 cp -a ./* /backup/full-backup-2017-06-06/ #挂载后把数据复制到备份目录， -a:表示 复制文件的所有属性及内容，保留源文件的所有属性及权限 6、删除快照卷 umount /mnt #备份完后卸载快照卷挂载的目录 lvremove --force /dev/myvg/mydata-snap #强制移除快照卷 rm -rf mysql-bin.* #把备份目录中的日志文件删除掉，节省空间 7、增量备份二进制日志 mysqlbinlog --start-datetime='2017-06-06 10:11:02' mysql-bin.000005 mysql-bin.000006 > /backup/incremental-`date +%F-%H-%M-%S`.sql #备份二进制日志，如果增量备份的二进制日志 含有2个或以上的日志文件，需要通过指定开始时间点来备份。 实例：通过实际操作，实现lvm逻辑卷快照备份及恢复mysql操作； 首先打开mysql客户端： [root@lamp ~]# mysql #打开mysql客户端 Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 11 Server version: 5.5.28-log Source distribution Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> FLUSH TABLES WITH READ LOCK; #首先刷新表并施加读锁 Query OK, 0 rows affected (0.00 sec) mysql> FLUSH LOGS; #滚动日志 Query OK, 0 rows affected (0.06 sec) mysql> SHOW MASTER STATUS; #查看二进制日志位置状态 +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000005 | 107 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) 不要退出或关闭此mysql客户端，重新打开一个服务器终端，执行位置信息的保存； [root@lamp ~]# mkdir /backup #新建一个备份目录 [root@lamp ~]# mysql -e 'SHOW MASTER STATUSG;' > /backup/master-`date +%F`.info #不登录mysql客户端，直接通过mysql -e直接编辑数据库，把查看二进制日志位置状态保存至备份目录下 [root@lamp ~]# ls /backup/ master-2017-06-06.info 对mysql数据目录执行快照（所以说通过逻辑卷备份数据库，前提必须是数据文件存放在逻辑卷上） [root@lamp ~]# lvcreate -L 50M -s -p r -n mydata-snap /dev/myvg/mydata #对/dev/myvg/mydata逻辑卷做快照，大小为50M，-s:表示snapshot快照，-p:指定权限permission r:读的权限read,-n:指定快照卷的名称。 Rounding up size to full physical extent 52.00 MiB Logical volume "mydata-snap" created [root@lamp ~]# lvs #查看逻辑卷，刚新建的快照逻辑卷 LV VG Attr LSize Origin Snap% Move Log Copy% Convert mydata myvg owi-ao 10.00g mydata-snap myvg sri-a- 52.00m mydata 0.02 [root@lamp ~]# mount #查看挂载相关信息 /dev/sda7 on / type ext4 (rw) proc on /proc type proc (rw) sysfs on /sys type sysfs (rw) devpts on /dev/pts type devpts (rw,gid=5,mode=620) tmpfs on /dev/shm type tmpfs (rw,rootcontext="system_u:object_r:tmpfs_t:s0") /dev/sda1 on /boot type ext4 (rw) /dev/sda3 on /home type ext4 (rw) /dev/sda5 on /tmp type ext4 (rw) /dev/sda2 on /usr/local type ext4 (rw) /dev/mapper/myvg-mydata on /mydata type ext4 (rw) none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw) sunrpc on /var/lib/nfs/rpc_pipefs type rpc_pipefs (rw) 执行完快照后，即可解锁表； mysql> UNLOCK TABLES; #解锁 Query OK, 0 rows affected (0.00 sec) 对快照进行挂载并备份； [root@lamp ~]# mount /dev/myvg/mydata-snap /mnt -o ro #以只读方式挂载刚做的快照卷 [root@lamp ~]# cd /mnt #进入挂载目录 [root@lamp mnt]# ls data lost+found [root@lamp mnt]# cd data/ [root@lamp data]# ls hellodb ib_logfile1 lamp.pid mysql-bin.000001 mysql-bin.000004 performance_schema testdb ibdata1 jiaowu mydb mysql-bin.000002 mysql-bin.000005 stu ib_logfile0 lamp.err mysql mysql-bin.000003 mysql-bin.index test [root@lamp data]# mkdir /backup/full-backup-`date +%F` #创建备份目录并以时间格式命名 [root@lamp data]# cp -a ./* /backup/full-backup-2017-06-06/ #-a:复制当前目录的所有内容及其权限属性到备份的目录 [root@lamp data]# cd [root@lamp ~]# umount /mnt #卸载挂载的目录/mnt [root@lamp ~]# lvremove --force /dev/myvg/mydata-snap #移除刚才创建的快照卷--force强制性 Logical volume "mydata-snap" successfully removed [root@lamp ~]# cd /backup/full-backup-2017-06-06/ [root@lamp full-backup-2017-06-06]# ls hellodb ib_logfile1 lamp.pid mysql-bin.000001 mysql-bin.000004 performance_schema testdb ibdata1 jiaowu mydb mysql-bin.000002 mysql-bin.000005 stu ib_logfile0 lamp.err mysql mysql-bin.000003 mysql-bin.index test [root@lamp full-backup-2017-06-06]# rm -rf mysql-bin.* #删除相关的二进制日志文件以便节省空间 [root@lamp full-backup-2017-06-06]# ls hellodb ib_logfile0 jiaowu lamp.pid mysql stu testdb ibdata1 ib_logfile1 lamp.err mydb performance_schema test root@lamp ~]# cd /mydata/data [root@lamp data]# ls hellodb ib_logfile1 lamp.pid mysql-bin.000001 mysql-bin.000004 performance_schema testdb ibdata1 jiaowu mydb mysql-bin.000002 mysql-bin.000005 stu ib_logfile0 lamp.err mysql mysql-bin.000003 mysql-bin.index test [root@lamp data]# cat /backup/master-2017-06-06.info *************************** 1. row *************************** File: mysql-bin.000005 #记录的二进制日志文件 Position: 107 #二进制日志的位置 Binlog_Do_DB: Binlog_Ignore_DB: mysql> FLUSH LOGS; #滚动日志 Query OK, 0 rows affected (0.01 sec) mysql> USE jiaowu Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> INSERT INTO tutors(Tname) VALUES ('stu0003'); #往表tutors中插入数据,字段Tname 值stu0003 Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO tutors(Tname) VALUES ('stu0004'); #往表tutors中插入数据,字段Tname 值stu0004 Query OK, 1 row affected (0.00 sec) mysql> SHOW MASTER STATUS; #查看此时二进制日志的位置状态信息 +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000006 | 575 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> q Bye [root@lamp data]# mysqlbinlog --start-datetime='2017-06-06 10:11:02' mysql-bin.000005 mysql-bin.000006 > /backup/incremental-`date +%F-%H-%M-%S`.sql #导出二进制日志文件bin.000005和bin.000006时间点从2017-06-06 10:11:02开始后的内容 [root@lamp data]# ls /backup/incremental-2017-06-06-17-01-41.sql /backup/incremental-2017-06-06-17-01-41.sql [root@lamp data]# service mysqld stop #停止mysqld进程 Shutting down MySQL.. [ OK ] [root@lamp data]# rm -rf ./* #模拟mysql数据库数据目录内容丢失（手动删除） [root@lamp data]# ls #目录中的内容全部删除，查看显示为空 [root@lamp data]# cp -a /backup/full-backup-2017-06-06/* ./ #复制之前快照卷备份的内容至当前数据目录，-a:复制文件的内容及权限属性。 [root@lamp data]# ll #确保所有文件的属主为mysql用户 total 28712 drwx------. 2 mysql mysql 4096 Jun 2 15:30 hellodb -rw-rw----. 1 mysql mysql 18874368 Jun 5 14:00 ibdata1 -rw-rw----. 1 mysql mysql 5242880 Jun 5 14:00 ib_logfile0 -rw-rw----. 1 mysql mysql 5242880 Jun 2 15:28 ib_logfile1 drwx------. 2 mysql mysql 4096 Jun 5 14:00 jiaowu -rw-rw----. 1 mysql root 1853 Jun 2 15:28 lamp.err -rw-rw----. 1 mysql mysql 6 Jun 2 15:28 lamp.pid drwx------. 2 mysql mysql 4096 Jun 2 15:30 mydb drwx------. 2 mysql root 4096 Jun 2 15:30 mysql drwx------. 2 mysql mysql 4096 Jun 2 15:28 performance_schema drwx------. 2 mysql mysql 4096 Jun 2 15:30 stu drwx------. 2 mysql root 4096 Jun 2 15:28 test drwx------. 2 mysql mysql 4096 Jun 2 15:30 testdb [root@lamp data]# service mysqld start #开启mysqld进程 Starting MySQL [ OK ] [root@lamp data]# mysql -uroot -p #登录mysql客户端 Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 3 Server version: 5.5.28-log Source distribution Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> USE jiaowu Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SELECT * FROM tutors; #查询tutors表的内容（此时后插入的2行数据没有） +-----+--------------+--------+------+ | TID | Tname | Gender | Age | +-----+--------------+--------+------+ | 1 | HongQigong | M | 93 | | 2 | HuangYaoshi | M | 63 | | 3 | Miejueshitai | F | 72 | | 4 | OuYangfeng | M | 76 | | 5 | YiDeng | M | 90 | | 6 | YuCanghai | M | 56 | | 7 | Jinlunfawang | M | 67 | | 8 | HuYidao | M | 42 | | 9 | NingZhongze | F | 49 | +-----+--------------+--------+------+ 9 rows in set (0.00 sec) mysql> SET sql_log_bin=0; #导入二进制日志前，先关闭日志记录功能0为关闭，1为打开 Query OK, 0 rows affected (0.00 sec) mysql> SOURCE /backup/incremental-2017-06-06-17-01-41.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) mysql> SELECT * FROM tutors; #再次查询tutors表的内容，此时后插入的2行数据已经生成 +-----+--------------+--------+------+ | TID | Tname | Gender | Age | +-----+--------------+--------+------+ | 1 | HongQigong | M | 93 | | 2 | HuangYaoshi | M | 63 | | 3 | Miejueshitai | F | 72 | | 4 | OuYangfeng | M | 76 | | 5 | YiDeng | M | 90 | | 6 | YuCanghai | M | 56 | | 7 | Jinlunfawang | M | 67 | | 8 | HuYidao | M | 42 | | 9 | NingZhongze | F | 49 | | 10 | stu0003 | M | NULL | | 11 | stu0004 | M | NULL | +-----+--------------+--------+------+ 11 rows in set (0.00 sec) mysql> SET sql_log_bin=1; #二进制日志导入后再打开二进制日志记录功能 Query OK, 0 rows affected (0.00 sec) mysql> SHOW MASTER STATUS; #查看此时的二进制日志位置状态信息 +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 107 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) 至此通过lvm逻辑卷和二进制cp功能实现了mysql数据库备份和还原。 （编辑：海南站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐