MySQL的备份与还原

2021-11-14 13:33:02

Backup Tools Overview
1:常用的备份工具
Backup Method    Storage Engines   Impact   Backup Speed Recovery Speed   Recovery Granularity
---------------------------------------------------------------------------------------------
mysqldump          ALL              WARM      MEDIUM        SLOWEST          MOST FLEXIBLE
---------------------------------------------------------------------------------------------
mysqldump           InnoDB          HOT       MEDIUM        SLOWEST          MOST FLEXIBLE
---------------------------------------------------------------------------------------------
SELECT INTO
OUTFILE             ALL             WARM      SLOW          SLOW             MOST FLEXIBLE
---------------------------------------------------------------------------------------------
mk-parallel-backup ALL             WARM      MEDIUM        MEDIUM           FLEXIBLE
---------------------------------------------------------------------------------------------
ibbackup            InnoDB          HOT       FAST          FAST             FLEXIBLE
---------------------------------------------------------------------------------------------
ibbackup            ALL             HOT       FAST          FAST             FLEXIBLE
---------------------------------------------------------------------------------------------
BACKUP command
in mysql            ALL             HOT       FAST          FAST             FLEXIBLE
---------------------------------------------------------------------------------------------
Filesystem
(copyfiles)         ALL             COLD      FASTEST       FASTEST          NOT FLEXIBLE
---------------------------------------------------------------------------------------------
Snapshot(that is,
with LVM,ZFS)       ALL             ALMOST HOT FAST        FAST             LEAST FLEXIBLE
---------------------------------------------------------------------------------------------
mysqlhotcopy        MyISAM          MOSTLY COLD FAST        FAST             FLEXIBLE
---------------------------------------------------------------------------------------------

说明：
   FLEXIBLE     翻译为：弹性
   逻辑备份出来的数据是ASCII的形式
   物理备份速度较快
   mysqldump    逻辑备份工具
   SELECT INTO OUTFILE 是mysql的一个命令，逻辑备份工具，可以备份某些表的某些行。
   mk-parallel-backup 是一个脚本，不属于mysql，逻辑备份，提供了在具有多颗CPU的主机上实现并行备份的能力
   ibbackup 是InnoDB公司所研发的，专门为InnoDB的一个热备份工具；物理备份，备份的速度很快。
   Filesystem 是物理备份，用cp命令将数据拷贝出去既可；冷备速度很快，但要把服务器服务关闭
   Snapshot 是物理备份；不能对多个逻辑卷，做同一个时间点的快照
   mysqlhotcopy 是仅能用于MyISAM 存储引擎，物理备份
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
2：mysqldump工具的讲解：
   逻辑备份工具
(1)mysqldump命令的选项
   db_name[tb1_name]
      备份：
      # mysqldump db_name tb_name > /root/tb1.sql
      @@备份时用输出重定向的方式，把数据输出到指定的文件内，一般文件名是.sql命名的
      还原：
      # mysql < /root/tb1.sql        @@命令行界面
      # mysql> SOURCE /root/tb1.sql @@登录mysql里
---------------------------------------------------------------------------------
   一次备份多个数据库：
   --databases db1 db2 db3.....
---------------------------------------------------------------------------------
   一次备份数据库服务器的所有数据库:
   --databases
---------------------------------------------------------------------------------
   创建事件的指令：
   --events
---------------------------------------------------------------------------------
   备份储存过程和存储函数：
   --routines
---------------------------------------------------------------------------------
   备份触发器：
   --triggers
---------------------------------------------------------------------------------
   扩展的insert的语句：在还原时，所有的插入数据，用一个insert语句来实现。默认启用了。
   --extended-insert       @@ --skip-extended-insert 选项来控制
---------------------------------------------------------------------------------
   在备份前对所有表加锁,如果没有手动加锁，此选项是必须的
   --lock-all-tables
   - ------------------------------------------------------------------------------
   刷新日志到磁盘数据文件中的，为了保证一致性备份的，刷新保存到二进制文件中，但没保存到磁盘中
   --flush-logs
   --------------------------------------------------------------------------------
   用在复制中，n的值有1,2,3 ；复制时用2.
   --master-data=n
   --------------------------------------------------------------------------------
   用于备份数据库，表和视图的定义，但不备份数据
   --no-data
   --------------------------------------------------------------------------------
   只备份某个数据库的某个表的某些数据
   --where "WHERE clause"
   --------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
3:mysqldump工具的实例：
（1）备份数据库hellodb中表t1及还原：
# mysqldump -uroot -p hellodb t1 > /root/t1.sql
# cat !$   @@可以查看此文件
# mysql -e 'DROP TABLE hellodb.t1' @@命令行界面删除表t1;可以连进数据库查看下，是否表存在
# mysql -uroot -p hellodb < /root/t1.sql @@因为是表级别的，所以要制定数据库
@@可以连进数据库查看下，是否表存在
----------------------------------------------------------------------------------------
（2）备份整个数据库hellodb，其中含有表t1,t2:
# mysqldump hellodb > /root/hellodb.sql
# mysql -e 'DROP DATABASE hellodb'
# mysql -e 'SHOW DATABASES' @@查看下数据库hellodb是否被删除
@@对于单个数据库来说，需要先创建数据库hellodb
# mysql -e 'CREATE DATABASE hellodb'
# mysql hellodb < /root/hellodb.sql
-----------------------------------------------------------------------------------
另外一种的备份方法：@@用此种方法备份，其还原时会自动创建数据库hellodb，可以查看备份文件，看是否有CREATE DATABASE命令没，就OK了。
# mysqldump --database hellodb > /root/hellodb.sql
# mysql -e 'DROP DATABASE hellodb'
# mysql < /root/hellodb.sql   @@此时不用指定数据库hellodb了。
# mysql -e 'SHOW DATABASES'
---------------------------------------------------------------------------------------
工作应用中，应该对表加锁后，进行备份：
# mysqldump --lock-all-tables --databases hellodb > /root/hellodb.sql
@@此选项是备份前为所有的表加锁，并且备份完成自后，自动释放锁的功能：--lock-all-tables
---------------------------------------------------------------------------------------
另一种加锁的方法：
# mysql -e 'FLUSH TABLES' @@此命令实现刷新一下表，让那些没有保存到表中的数据保存到表中。
# mysql -e 'FLUSH TABLES WITH READ LOCK' @@对于本题来说，可以用此命令实现对表加读锁
# mysqldump --database hellodb > /root/hellodb.sql
# mysql -e 'UNLOCK TABLES'
------------------------------------------------------------------------------------------
如果有触发器的话，就加选项 --triggers
# mysqldump --triggers --lock-all-tables --databases hellod > /root/hellodb.sql
------------------------------------------------------------------------------------------
如果存储引擎是InnoDB，数据库及表的引擎都是InnoDB，此时加上选项 --single-transaction 就不用
加锁，也可以保证数据备份的一致性。要确保备份的是支持事务的引擎，这也就是热备份。
# mysqldump --single-transaction --databases hellodb > /root/hellodb.sql
------------------------------------------------------------------------------------------
@@说明mysqldump此种备份的都是文本文件，可以用sed，awk命令对其进行删除，处理，保留你想要的数据进行还原。
@@对于备份出来的结果可以用命令# gzip -9 -c /root/hellodb.sql > /root/hellodb.sql.gz 的命令对其进行压缩
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
SELECT INTO OUTFILE 备份工具的用法
@@用于备份某些表中的某些数据
1：语法结构：
备份格式：
SELECT * INTO OUTFILE '/tmp/t1.txt' FROM t1;
@@保存文件的目录为/tmp或其它目录，确保其目录对于mysqld这个用户具有写权限。
还原格式：
LOAD DATA INFILE '/tmp/t1.txt' INTO TABLE t1;
----------------------------------------------------------------------------------------
2:实例演习：
备份数据库hellodb中表t2中以h开头的行：
# mysql
mysql> use hellodb;
mysql> SELECT * FROM t2 WHERE CNAME LIKE 'h%';
mysql> SELECT * INTO OUTFILE '/tmp/t2.txt' FROM t2 WHERE CNAME LIKE 'h%'; @@备份数据
mysql> exit
# cat /tmp/t2.sql @@可以查看一下其数据
@@发现其仅含有表中的数据，没有表的结构等信息，要想还原过去，如果表不存在，需要创建此表
假设想把此数据还原到另一个表中如：t3
# mysql
mysql> use hellodb;
mysql> CREATE TABLE t3 LIKE t2;
mysql> DESC t3;
mysql> LOAD DATA INFILE '/tmp/t2.sql' INTO TABLE t3;
mysql> SELECT * FROM t3;
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
Snapshot基于LVM的快照做物理备份：
@@几乎是热备
1：其要求:
(1):保证数据文件在逻辑卷LV上
(2):对数据库中的表施加读锁
(3):立即对数据在的LV做快照
(4):释放数据库的读锁
(5):挂载快照卷，备份数据文件（如果是InnoDB的引擎，还需备份事务日志）
(6):删除快照卷
----------------------------------------------------------------------------------
2：@@如果是InnoDB引擎，一定要把数据文件和事务日志文件放到同一个逻辑卷上
# df -lh
# mysql
mysql> SHOW VARIABLES LIKE 'datadir';
mysql> SHOW VARIABLES LIKE 'log_bin'; @@对于InnoDB引擎有几个非常重要的选项，对于log_bin是二进制日志要处于开启状态，而且要有对应的日志文件
mysql> SHOW VARIABLES LIKE 'sync_binlog'; @@同步日志文件，其值=1
mysql> SHOW VARIABLES LIKE 'innodb_support_xa'; @@InnoDB的刷新日志要ON起来。
-----------------------------------------------------------------------------------
3:如果数据目录没在逻辑卷上，把它移到上去的方法：
# mysqldump --all-databases > /root/all.sql
# service mysqld stop
# df -lh
# cd /mydata
# ls
# rm -rf data
# mkdir data
# chown -R mysql:mysql data/
# vim /etc/my.cnf
datadir = /mydata/data
# cd /usr/local/mysql
# chown mysql:mysql . -R
# scripts/mysql_innodb_db --datadir=/mydata/data --user=mysql
# chown root . -R
# service mysqld start
# mysql -uroot -p < /root/all.sql
# mysql @@不行试下# mysql -uroot -p
mysql> SHOW DATABASES; @@查看下其正确性
mysql> SHOW VARIABLES LIKE 'datadir';
mysql> use hellodb;
mysql> SHOW TABLE STATUS\G; @@可以看下其表的存储引擎都是InnoDB的。
mysql> \q
---------------------------------------------------------------------------------
4：施加读锁：
# mysql
mysql> FLUSH TABLES WITH READ LOCK;
mysql>\q
----------------------------------------------------------------------------------
5:创建快照卷：
# cd
# lvcreate -L 50M -n mysnap -s -p r /dev/myvg/mydata @@-p r表示只读卷
-------------------------------------------------------------------------------
6:解锁：
# mysql
mysql> UNLOCK TABLES;
mysql>\q
---------------------------------------------------------------------------------
7:挂载快照：
# mount /dev/myvg/mysnap /mnt
# cd /mnt
# ls
# cd data
# ls ##查看其所有数据是否在这
# lvs @@可以查看一下是否有快照卷mysnap
------------------------------------------------------------------------------------
8：物理备份：
# cd /mnt/data/hellodb   @@会发现只有表定义的结构文件，而数据文件放在/mnt/data/ibdata1中
# cd .. @@如果想备份所有数据库，则就备份整个/mnt/data这个目录
@@如果仅想备份数据库hellodb,则就备份hellodb，和对应的表空间文件就可以了。
# tar -jcf /root/mysql.tar.bz2   hellodb/ ibdata1 ib_logfile* mysql-bin.* @@备份
--------------------------------------------------------------------------------------
9：删除快照卷：此阶段代表备份的完成
# umount /mnt
# lvremove --force /dev/myvg/mysnap
------------------------------------------------------------------------------------
10:还原的过程：
# service mysqld stop
# rm -rf /mydata/data/*
# cd /usr/local/mysql
# chown mysql:mysql . -R
# scripts/mysql_install_db --user=mysql --datadir=/mydata/data
# chown root . -R
# service mysqld start
# service mysqld stop
# cd /mydata/data
# tar xf /root/mysql.tar.bz2 -C .
# service mysqld start
# mysql @@一以下都是验证信息
mysql> SHOW DATABASES;
mysql> use hellodb;
mysql> SHOW　TABLES;
mysql> SELECT * FROM t2;
-------------------------------------------------------------------------------------
二进制日志可以做时间点恢复
@@可以明确指定从几点到几点的恢复的
1：如何查看二进制日志文件的内容：
@@查看其文件需要用特殊的命令：mysqlbinlog
# cd /mydata/data @@二进制文件在其目录下
# mysqlbinlog mysql-bin.000004
@@此命令可以把二进制文件转化为文本文件的形式显示
# mysqlbinlog mysql-bin.000003
@@可以查看其所有操作
@@其中都是按事件的形式记录的
    # at 478014
    ............
    ............
    # at 478127
-----------------------------------------------------------------------------------------------------
2:mysqlbinlog命令的选项：
start-position @@从哪个位置开始
end-position   @@到那个位置结束

start-datatime @@从那个时间点开始
end-datatime   @@到那个时间点结束
-------------------------------------------------------------------------------------------------------
3：查看当前服务器所使用的事务文件
# mysql -e 'SHOW MASTER STATUS' @@假设是mysql-bin.000005
# mysql -e 'FLUSH LOGS' @@滚动日志，群殴所使用费的二进制文件会加1 ,此时会变成 mysql-bin.000006
# mysql -e 'SHOW MASTER STATUS'
@@二进制日志只记录写的内容
-----------------------------------------------------------------------------------------------------
4：一次完整的备份加差异备份
# mysql
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
mysql> \q
# lvcreate -L 50M -n mysnap -s -p r /dev/myvg/mydata
# mysql
mysql> UNLOCK TABLES;
mysql> \q
# cd /mydata/data
# ls
# tar -jcf /root/mysql.tar.bz2   hellodb/ ibdata1 ib_logfile* @@此时就不需要备份二进制文件，因为FLUSH LOGS

# mysql
mysql> use hellodb;
mysql> insert INTO t2 VALUES ('HHELLO'),('dsjkdf'); @@备份以后插入的数据
mysql> \q
# mysqlbinlog mysql-bin.000006 @@查看你写入事件的位置
# mysqlbinlog --start-position 301 mysql-bin.000006 > /root/mysql2.inrc.sql
@@假如有此时你的数据库服务器坏了
# service mysqld stop
# rm -rf /mydata/data
# cd /usr/local/mysql
# chown mysql:mysql . -R
# scripts/mysql_install_db --user=mysql --datadir=/mydata/data
# chown root . -R
# service mysqld start
# service mysqld stop
# cd /mydata/data
# tar xf /root/mysql2.tar.bz2 -C .
# service mysqld start
# mysql
mysql> USE hellodb;
mysql> SELECT * FROM t2; @@此时你会发现你备份后插入的数据没有
mysql> \q

@@幸运的是我们备份了二进制文件：
# mysql < /root/mysql2.incr.sql
# mysql
mysql> USE hellodb;
mysql> SELECT * FROM t2; @@此时你会发现有了。
mysql> \q

@@mysqlbinlog 在做完完全备份后，应经常定期的做备份。帮你还原做完全备份后数据发生变化的备份。
@@时间格式：
# mysqlbinlog mysql-bin.000003 --start-datatime="2011-03-05 17:34:40" --end-datatime="2011-03-05 17:34:56" > hdkd.sql

----------------------------------------------------------------------------------------------------------------------------
你想备份某一个二进制文件之前的所有二进制文件：
# mysql
mysql> FLUSH LOGS;
mysql> SHOW MASTER STATUS;
mysql> \q
# cd /mydata/data @@把对应从show master status 中查看到的file文件之前的二进制文件拷贝出来即可，也是实现所谓的物理备份。
----------------------------------------------------------------------------------------------------------------------------

@@幸运的是我们备份了二进制文件：
# mysql < /root/mysql2.incr.sql
# mysql
mysql> USE hellodb;
mysql> SELECT * FROM t2; @@此时你会发现有了。
mysql> \q

本文转自 kuchuli 51CTO博客，原文链接：http://blog.51cto.com/lgdvsehome/666108，如需转载请自行联系原作者

码农公寓

相关文章