技术分享
mysql备份/恢复策略
2021-07-07
需要考虑的一些因素。
1.确定要备份的表的存储引擎是事务型还是非事务性，两种不同的存储引擎备份方式在处理数据一致性方面是不太一样的。
2.确定使用全备份还是增量备份。全备份的优点是备份保持最新备份恢复的时候可以花费更少的时间缺点是如果数据量大，将会花费很多的时间，并对系统造成较长时间的压力。增量备份则恰恰相反，只需要备份每天的增量日志，备份时间少，对负载压力也小；缺点就是恢复的时候需要全备份加上次备份到故障前的所有日志，恢复时间会长些。
3.可以考虑采取复制的方法来做异地备份，但是记住，复制不能代替备份，它对数据库的误操作也无能为力，要定期做备份，备份的周期要充分考虑系统可以承受的恢复时间。备份要在系统负载较小的时候进行。确保MySQL打开log-bin选项有了BINLOGMySQL才可以在必要的时候做完整恢复或基于时间点的恢复或基于位置的恢复。要经常做备份恢复测试确保备份是有效的并且是可以恢复的
逻辑备份和恢复
在MySQL里面逻辑备份的最大优点是对于各种存储引擎都可以用同样的方法来备份而物理备份则不同不同的存储引擎有着不同的备份方法。因此对于不同存储引擎混合的数据库用逻辑备份会更简单一些。本节将详细介绍逻辑备份以及相应的恢复方法
备份
shell> mysqldump [options] db_name [tables]
备份指定的一个或多个数据库。
shell> mysqldump [options] ---database DB1 [DB2 DB3...]
备份所有数据库。
shell> mysqldump [options] --all-databases
2.备份数据库test [zzx@localhost ~]$ mysqldump –u root -p test > test.sql Enter password
3.备份数据库test下的表emp
[zzx@localhost ~]$ mysqldump -uroot -p test emp > emp.sqlEnter password
4备份数据库test下的表emp和dept
[zzx@localhost ~]$ mysqldump -uroot -p test emp dept > emp_dept.sqlEnter password
5备份数据库test下的所有表为逗号分割的文本备份到/tmp
[root@localhost tmp]# mysqldump -uroot -T /tmp test emp -- fields-terminated-by ','[root@localhost tmp]# more emp.txt 1,z1 2,z2 3,z3 4,z4 1,z
其中mysqldump的选项很多具体可以使用“–help”参数查看 mysqldump --help
需要强调的是，为了保证数据备份的一致性MyISAM存储引擎在备份的时候需要加上-l参数表示将所有表加上读锁在备份期间所有表将只能读而不能进行数据更新。但是对于事务存储引擎InnoDB和BDB来说可以采用更好的选项–single-transaction此选项将使得InnoDB存储引擎得到一个快照Snapshot使得备份的数据能够保证一致性
完全恢复
mysqldump的恢复也很简单将备份作为输入执行即可具体语法如下
mysql –uroot –p dbname < bakfile
注意将备份恢复后数据并不完整还需要将备份后执行的日志进行重做语法如下
[root@localhost mysql]# mysqldump -uroot –p –l –F test >test.dmpEnter password
其中-l参数表示给所有表加读锁-F表示生成一个新的日志文件此时test中emp表的数据如下
mysql> select * from emp order by id; +------+------+| id | name | +------+------+| 1 | z1 | | 2 | z2 | | 3 | z3 | | 4 | z4 | +------+------+4 rows in set (0.00 sec
基于时间点恢复
由于误操作比如误删除了一张表这时使用完全恢复是没有用的因为日志里面还存在误操作的语句我们需要的是恢复到误操作之前的状态然后跳过误操作语句再恢复后面执行的语句完成我们的恢复。这种恢复叫不完全恢复在MySQL中不完全恢复分为基于时间点的恢复和基于位置的恢复。以下是基于时间点恢复的操作步骤：
1如果上午10点发生了误操作可以用以下语句用备份和BINLOG将数据恢复到故障前
shell>mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/bin.123456 | mysql -uroot –pmypw
2跳过故障时的时间点继续执行后面的BINLOG完成恢复
shell>mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456| mysql -uroot -pmypwd
基于位置恢复
和基于时间点的恢复类似但是更精确因为同一个时间点可能有很多条SQL语句同时执行。恢复的操作步骤如下。1在shell下执行如下命令
shell>mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-2010:05:00" /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql
该命令将在/tmp目录创建小的文本文件编辑此文件找到出错语句前后的位置号例如前后位置号分别是368312和368315。2恢复了以前的备份文件后应从命令行输入下面内容:
shell>mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 \ | mysql -u root -pmypwd shell>mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 \ | mysql -u root -pmypwd \
上面的第一行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为mysqlbinlog的输出包括每个SQL语句记录之前的SETTIMESTAMP语句因此恢复的数据和相关MySQL日志将反应事务执行的原时间
物理备份和恢复
物理备份又分为冷备份和热备份两种和逻辑备份相比它的最大优点是备份和恢复的速度更快因为物理备份的原理都是基于文件的cp。本节将介绍MySQL中的物理备份及其恢复的方法
冷备份
冷备份其实就是停掉数据库服务cp数据文件的方法。这种方法对MyISAM和InnoDB存储引擎都适合但是一般很少使用因为很多应用是不允许长时间停机的。进行备份的操作如下停掉MySQL服务在操作系统级别备份MySQL的数据文件和日志文件到备份目录。进行恢复的操作如下首先停掉MySQL服务在操作系统级别恢复MySQL的数据文件然后重启MySQL服务使用mysqlbinlog工具恢复自备份以来的所有BINLOG
热备份
MySQL中对于不同的存储引擎热备份方法也有所不同，下面主要介绍MyISAM和InnoDB两种最常用的存储引擎的热备份方法：
1.MyISAM存储引擎
方法1：使用mysqlhotcopy工具mysqlhotcopy是MySQL自带的一个热备份工具使用方法很简单
shell> mysqlhotcopy db_name [/path/to/new_directory
mysqlhotcopy有很多选项具体可以使用“–help”查看帮助
mysqlhotcopy --help
方法2手工锁表copy
在mysqlhotcopy使用不正常的情况下，可以手工来做热备份操作步骤如下：
首先数据库中所有表加读锁
mysql>flush tables for read
然后cp数据文件到备份目录即可
2.InnoDB存储引擎
ibbackup是Innobase公司www.innodb.com的一个热备份工具专门对InnoDB存储引擎进行物理热备份此工具是收费的但可以免费使用1个月。Innobase公司已经于2005年被Oracle公司所收购。下面简单介绍一下使用ibbackup工具的备份步骤
1编辑用于启动的配置文件my.cnf和用于备份的配置文件backup-my.cnf
my.cnf的例子如下: [mysqld] datadir = /home/heikki/data innodb_data_home_dir = /home/heikki/data innodb_data_file_path=ibdata1:100M;ibdata2:200M;ibdata3:500M:autoextend innodb_log_group_home_dir = /home/heikki/data set-variable = innodb_log_files_in_group=2 set-variable = innodb_log_file_size=20M
2如果想备份到/home/heikki/backup则
backup-my.cnf的例子如下:
mysqld] datadir = /home/heikki/backup innodb_data_home_dir = /home/heikki/backup innodb_data_file_path=ibdata1:100M;ibdata2:200M;ibdata3:500M:autoextend innodb_log_group_home_dir = /home/heikki/backup set-variable = innodb_log_files_in_group=2set-variable = innodb_log_file_size=20M
3.开始备份
注意:
ibbackup工具不会覆盖任何重名的文件因此在新的备份开始之前，需要确保备份目录中没有重名文件否则备份很可能会失败。
4.备份完成后，备份目录下包含有数据文件和日志文件，如下所示
$ ls -lh /home/heikki/backu total 824M-rw-r—– 1 pekka dev 22MJan2117:42ibbackup_logfile-rw-r—– 1 pekka dev 100M Jan 21 17:36 ibdata1-rw-r—– 1 pekka dev 200M Jan 21 17:38 ibdata2-rw-r—– 1 pekka dev 500M Jan 21 17:42 ibdata
因为在cp数据文件时文件内容在不断地变化因此在不同的时间点cp的数据块中的数据很可能是不一致的。因此ibbackup在备份期间用一个日志文件ibbackup_logfile记录了备份期间数据的变化在恢复的时候就可以用此日志文件对备份的数据文件进行日志重做使得备份的数据能够保持完整性和一致性。当主数据库出现故障时我们需要用备份进行恢复恢复的步骤如下。1进行日志重做。如前面所述利用下面的命令对备份数据进行日志重做
shell>ibbackup --apply-log /home/pekka/backup-my.cnf
2恢复后重启数据库服务
shell>./bin/mysqld_saft --defaults-file=/home/pekka/backup-my.cnf &
3服务重启后利用BINLOG日志将备份点与故障点之间的剩余数据进行恢复
mysqlbinlog binlog-file | mysql -u root –p***
ibbackup还有一些其他的功能比如压缩备份、不完全恢复等这里就不再赘述。更详细的使用方法读者可以用“ibbackup –help”命令进行查看或者参阅官方帮助文档http://www.innodb.com/support/documentation/innodb-hot-backup-manual/。对于InnoDB和MyISAM混合的数据库Innobase公司还提供了一个开源的Perl脚本innobackup它可以将两种存储引擎的表一起进行备份具体使用方法读者可以参阅上述链接中的文档
表的导入导出
在数据库的日常维护中表的导入导出是很频繁的一类操作。本节将对MySQL中这类操作进行详细的介绍希望读者能够熟练掌握
导出
在某些情况下为了一些特定的目的经常需要将表里的数据导出为某些符号分割的纯数据文本而不是SQL语句。这些应用可能有以下一些
用来作为EXCEL显示单纯为了节省备份空间
为了快速的加载数据LOAD DATA的加载速度比普通的SQL加载要快20倍以上。
为了满足这些应用可以使用以下两种办法来实现。
方法1使用SELECT …INTO OUTFILE …命令来导出数据具体语法如下:
mysql> SELECT * FROM tablename INTO OUTFILE 'target_file' [option]
其中option参数可以是以下选项
FIELDS TERMINATED BY 'string' 字段分隔符默认为制表符’\t’FIELDS [OPTIONALLY] ENCLOSED BY 'char'(字段引用符如果加OPTIONALLY选项则只用在char、varchar和text等字符型字段上。默认不使用引用符) FIELDS ESCAPED BY 'char' 转义字符默认为’\’LINES STARTING BY 'string' 每行前都加此字符串默认''LINES TERMINATED BY 'string'行结束符默认为’\n’
其中char表示此符号只能是单个字符string表示可以是字符串。例如将emp表中数据导出为数据文本其中字段分隔符为“,”字段引用符为“””双引号记录结束符为回车符具体实现如下
ysql> select * from emp into outfile '/tmp/emp.txt' fields terminated by "," enclosedby '"' ; Query OK, 5 rows affected (0.00 sec) mysql> mysql> system more/tmp/emp.txt"1","z1","aa""2","z1","aa""3","z1","aa""4","z1","aa""1","z1","aa
发现第一列是数值型如果不希望字段两边用引号引起则语句改为
mysql> select * from emp into outfile '/tmp/emp.txt' fields terminated by ","optionally enclosed by '"' ; Query OK, 5 rows affected (0.00 sec) mysql>system more/tmp/emp.txt1,"z1","aa"2,"z1","aa"3,"z1","aa"4,"z1","aa"1,"z1","aa
结果如我们所愿第一列的双引号被去掉。下面来测试一下转义字符。转义字符顾名思义就是由于含义模糊而需要特殊进行转换的字符在不同的情况下需要转义的字符是不一样的。MySQL导出的数据中需要转义的字符主要包括以下3类转义字符本身字段分隔符记录分隔符。在下面的例子中对表emp中的name更新为含“\”字段分隔符记录分隔符的数据然后导出
mysql> update emp set name='\\"##!aa' where id=1; Query OK, 2 rows affected (0.04 sec)Rows matched: 2 Changed: 2 Warnings: 0mysql> system rm /tmp/emp.txt mysql> select * from emp into outfile '/tmp/emp.txt' fields terminated by ","optionally enclosed by '"' Query OK, 5 rows affected (0.00 sec) mysql> system more /tmp/emp.txt1, 1,"\\\"##!aa","aa" 2,"z1","aa" 3,"z1","aa" 4,"z1","aa" 1,"\\\"##!aa","aa" mysql>
以上例子中name中含有转义字符本身“\”域引用符“””因此在输出的数据中我们发现这两种字符前面都加上了转义字符“\”“#”变成了“\#”。继续进行测试将id为1的name更新为含有字段分隔符“,”的字符串
mysql> update emp set name='\\"#,#,!aa' where id=1; Query OK, 2 rows affected (0.04 sec)Rows matched: 2 Changed: 2 Warnings: 0mysql> system rm /tmp/emp.txt mysql> select * from emp into outfile '/tmp/emp.txt' fields terminated by ","optionally enclosed by '"' ; Query OK, 5 rows affected (0.00 sec) mysql> system more/tmp/emp.txt1,1,"\\\"#,#,!aa","aa"2,"z1","aa"3,"z1","aa"4,"z1","aa"1,"\\\"#,#,!aa","aa
注意在MySQL客户端连接成功后如果要执行操作系统的命令可以用“system+操作系统命令”来进行执行。这个时候发现数据中的字符“”并没有被转义这是为什么呢其实仔细想想就明白了因为每个字符串的两边带有引用符“””双引号)所以当MySQL看到数据中的“,”时由于它处在前半个引用分隔符之后后半个引用分隔符之前所以并没有将它作为字段分隔符而只是作为普通的一个数据字符来对待因而不需要转义。继续做测试将输出文件的字段引用符去掉这个时候我们的预期是数据中的“,”将成为转义字符而需要加上“\”
mysql> system rm /tmp/emp.txt mysql> select * from emp into outfile '/tmp/emp.txt' fields terminated by "," ; Query OK, 5 rows affected (0.00 sec) 1,\\"#\,#\,!aa,aa 2,z1,aa 3,z1,aa4, 4,z1,a 1,\\"#\,#\,!aa,aa
果然现在的“,”前面加上了转义字符“\”。而刚才的引用符“””却没有被转义因为它已经没有什么歧义不需要被转义。通过上面的测试可以得出以下结论当导出命令中包含字段引用符时数据中含有转义字符本身和字段引用符的字符需要被转义当导出命令中不包含字段引用符时数据中含有转义字符本身和字段分隔符的字符需要被转义。注意SELECT…INTO OUTFILE…产生的输出文件如果在目标目录下有重名文件将不会创建成功源文件不能被自动覆盖。方法2用mysqldump导出数据为文本
mysqldump –u username –T target_dir dbname tablename [option]
其中option参数可以是以下选项 –fields-terminated-by=name字段分隔符 –fields-enclosed-by=name字段引用符 –fields-optionally-enclosed-by=name字段引用符只用在char、varchar和text等字符型字段上 –fields-escaped-by=name转义字符 –lines-terminated-by=name记录结束符。下面的例子中采用mysqldump生成了指定分隔符分隔的文本
[root@localhost tmp]# mysqldump -uroot -T /tmp test emp --fields-terminated-by ','--fields-optionally-enclosed-by '"'[root@localhost tmp]# more /tmp/emp.txt1,"\\\"#,#,!aa","aa"2,"z1","aa"3,"z1","aa"4,"z1","aa"1,"\\\"#,#,!aa","aa
除了生成数据文件emp.txt之外还生成一个emp.sql文件里面记录了emp表的创建脚本记录的内容如下
[root@localhost tmp]# more emp.sql-- MySQL dump 10.11---- Host: localhost Database: test-- -------------------------------------------------------- Server version 5.0.41-community-log/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' *//*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;---- Table structure for table `emp`--DROP TABLE IF EXISTS `emp`;CREATE TABLE `emp` ( `id` int(11) default NULL, `name` varchar(10) default NULL, `content` text) ENGINE=InnoDB DEFAULT CHARSET=latin1;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */-- Dump completed on 2007-08-11 20:27:32
导入
本节只讨论用SELECT… INTO OUTFILE或者mysqldump导出的纯数据文本的导入方法。和导出类似导入也有两种不同的方法分别是LOAD DATA INFILE…和mysqlimport它们的本质是一样的区别只是在于一个在MySQL内部执行另一个在MySQL外部执行
方法1使用“LOAD DATA INFILE…”命令
mysql > LOAD DATA[LOCAL] INFILE ‘filename’ INTO TABLE tablename [option]
option可以是以下选项 FIELDS TERMINATED BY ‘string’字段分隔符默认为制表符’\t’ FIELDS [OPTIONALLY] ENCLOSED BY ‘char’字段引用符如果加OPTIONALLY选项则只用在char、varchar和text等字符型字段上。默认不使用引用符 FIELDS ESCAPED BY ‘char’转义字符默认为’\’ LINES STARTING BY ‘string’每行前都加此字符串默认” LINES TERMINATED BY ‘string’行结束符默认为’\n’ IGNORE number LINES忽略输入文件中的前n行数据 (col_name_or_user_var,…) 按照列出的字段顺序和字段数量加载数据 SET col_name = expr,…将列做一定的数值转换后再加载。其中char表示此符号只能是单个字符string表示可以是字符串。FILELD和LINES和前面SELECT …INTO OUTFILE…的含义完全相同不同的是多了几个不同的选项下面的例子将文件“/tmp/emp.txt”中的数据加载到表emp中
mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosedby '"' ; Query OK, 4 rows affected (0.03 sec)mysql> select * from emp; +------+------+---------+| id | name | content | +------+------+---------+| 2 | z1 | aa | | 3 | z1 | aa | | 4 | z1 | aa | | 1 | z1 | aa | +------+------+---------+4 rows in set (0.00 sec
如果不希望加载文件中的前两行可以如下操作
mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosedby '"' ignore 2 lines; Query OK, 2 rows affected (0.04 sec)Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 +------+------+---------+| id | name | content | +------+------+---------+| 4 | z1 | aa | | 1 | z1 | aa | +------+------+---------+2 rows in set (0.00 sec)
此时数据只加载了两行
mysql> system more /tmp/emp.txt2,"z1","aa"3,"z1","aa"4,"z1","aa"1,"z1","aa
或者只想加载部分列可以在命令行中加上列的顺序如下例所示
mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosed by '"' ignore 2 lines (id,content,name); Query OK, 2 rows affected (0.05 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 0mysql> select * from emp; +------+------+---------+| id | name | content | +------+------+---------+| 4 | aa | z1 | | 1 | aa | z1 | +------+------+---------+2 rows in set (0.00 sec)
可以发现文件中第二列的内容放到了content里面第三列的内容放到了name里面。如果只想加载第一列字段的列表里面可以只加第一列的名称
mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosedby '"' ignore 2 lines (id); Query OK, 2 rows affected, 2 warnings (0.04 sec)Records: 2 Deleted: 0 Skipped: 0 Warnings: 2mysql> select * from emp; +------+------+---------+| id | name | content | +------+------+---------+| 4 | NULL | NULL | | 1 | NULL | NULL | +------+------+---------+2 rows in set (0.00 sec)
如果希望将id列的内容+10后再加载到表中可以如下操作
mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosedby '"' set id=id+10; Query OK, 4 rows affected (0.03 sec) Records: 4 Deleted: 0 Skipped: 0 Warnings: 0mysql> select * from emp; +------+------+---------+| id | name | content | +------+------+---------+| 12 | z1 | aa | | 13 | z1 | aa | | 14 | z1 | aa | | 11 | z1 | aa | +------+------+---------+4 rows in set (0.00 sec)
方法2用mysqlimport来实现具体命令如下
shell>mysqlimport –u root –p*** [--LOCAL] dbname order_tab.txt [option]
其中option参数可以是以下选项 –fields-terminated-by=name字段分隔符 –fields-enclosed-by=name字段引用符 –fields-optionally-enclosed-by=name字段引用符只用在char、varchar和text等字符型字段上 –fields-escaped-by=name转义字符 –lines-terminated-by=name记录结束符 – ignore-lines=number或略前几行。这与mysqldump的选项几乎完全相同这里不再详细介绍简单来看一个例子
[root@localhost tmp]# mysqlimport -uroot test /tmp/emp.txt --fields-terminated-by=','--fields-enclosed-by='"'test.emp: Records: 4 Deleted: 0 Skipped: 0 Warnings: 0 [root@localhost tmp]# [root@localhost tmp]# mysql -uroot test -e 'select count(10) from emp'+-----------+| count(10) | +-----------+| 4 | +-----------+[root@localhost tmp]# mysql -uroot test -e 'select * from emp'+------+------+---------+| id | name | content | +------+------+---------+| 2 | z1 | aa | | 3 | z1 | aa | | 4 | z1 | aa | | 1 | z1 | aa | +------+------+---------
注意：如果导入和导出是跨平台操作的，Windows和Linux，那么要注意设置参数line-terminated-by，Windows上设置为line-terminated-by=’\r\n’，Linux上设置为line-terminated-by=’\n’
- 标签：
-
技术分享
