MySQL备份和还原操作小结

MySQL备份和还原操作

目标

备份的概念
Mysqldump备份操作
MySQL还原操作
MySQL双机热备份

概述

在数据库表丢失或损坏的情况下，备份数据库是很重要的。如果发生系统崩溃，能够将表尽可能丢失最少的数据恢复到崩溃发生时的状态。

备份数据库两个主要方法是用mysqldump程序或直接拷贝数据库文件

Mysqldump备份

导出库表（mysqldump）
mysqldump -u用户名 -p密码 -h主机 数据库 a -w “sql条件” –lock-all-tables > 路径
mysqldump -hhostname -uusername -p dbname tbname>xxxx.sql
** 按指定条件导出数据库表内容。(-w选项 –where）
mysqldump -hhostname -uusername-p dbname tbname -w’id >= 1 and id<= 10000’–skip-lock-tables > xxxx.sql
或
mysqldump -hhostname -uusername -p dbname tbname --where=‘unit_id >= 1 and unit_id <= 10000’> ~/xxxx.sql

Mysqldump备份库表详细举例

导出整个数据库
mysqldump -u 用户名 -p数据库名 > 导出的文件名
mysqldump -u breezelark-p mydb > mydb.sql

导出一个表（包括数据结构及数据）
mysqldump -u 用户名 -p数据库名 表名> 导出的文件名
mysqldump -u lingxi -p mydb mytb> mytb.sql

导出一个数据库结构（无数据只有结构）
mysqldump -u lingxi -p -d --add-drop-table mydb >mydb.sql
-d 没有数据–add-drop-table 在每个create语句之前增加一个drop table

数据库备份

完全备份数据库
增量备份

完全备份数据库

mysqldump --single-transaction --flush-logs --master-data=2 --all-databases >fullbackup_sunday_11_PM.sql
在sql文件中我们会看到两行：
– Position to start replication or point-in-time recovery from
– CHANGE MASTER TO MASTER_LOG_FILE=’bin-log.000002′， MASTER_LOG_POS=107;
第二行包含了我们需要的信息，是指备份后所有的更改将会保存到bin-log.000002二进制文件中。

增量 备份

然后在星期一下午11点我们来做一次增量备份：
mysqladmin flush-logs
这时将会产生一个新的二进制日志文件bin-log.000003，bin-log.000002则保存了自星期天下午11点到现在的所有更
改，我们只需要把这个文件备份到安全的地方就行了。然后星期二我们又做增量备份，还是执行同样的命令，这时我们
保存bin-log.000003文件。
如果mysqld在运行则停止，然后用–log-bin[=file_name]选项来启动。二进制日志文件中提供了执行mysqldump之后对数据库的更改进行复制所需要的信息。
对于InnoDB表，可以进行在线备份，不需要对表进行锁定
MySQL支持增量备份:需要用–log-bin选项来启动服务器以便启用二进制日志“二进制日志”。当想要进行增量备份时(包含上一次完全备份或增量备份之后的所有更改)，应使用FLUSH LOGS回滚二进制日志。然后，你需要将从最后的完全或增量备份的某个时刻到最后某个点的所有二进制日志复制到备份位置。这些二进制日志为增量备份；恢复时，按照下面的解释应用。下次进行完全备份时，还应使用FLUSH LOGS–flushlogs回滚二进制日志。
“mysqldump：数据库备份程序”，

数据库还原

备份的程序，在出现崩溃后，可以恢复数据：
· 操作系统崩溃
· 电源故障
· 文件系统崩溃
· 硬件问题(硬盘、母板等等)
该命令不包括mysqldump和mysql程序的–user和—password等选项。应包括必要的选项让MySQL服务器允许你连接它。
我们假定数据保存在MySQL的InnoDB存储引擎中，支持事务和自动崩溃恢复。我们假定崩溃时MySQL服务器带负载。如果不带负载，则不需要恢复。
出现操作系统崩溃或电源故障时，我们可以假定重启后硬盘上的MySQL数据仍可用。由于崩溃，InnoDB数据文件中的数据可能不再保持一致性，但InnoDB读取它的日志并会查到挂起的提交的和未提交的事务清单，它们没有清空到数据文件中。InnoDB自动卷回未提交的事务，并清空到它的数据文件中。通过MySQL错误日志将该恢复过程相关信息传达给用户

数据恢复

Mysql -u用户名 –p密码 数据库名 < 导出的文件名

恢复备份

比如星期三中午12点出现了故障，这时需要恢复，我们首先导入星期天的完整备份：
mysql < fullbackup_sunday_3_AM.sql
接着我们导入星期一和星期二的增量备份：
mysqlbinlog bin-log.000002 bin-log.000003 | mysql
注意： “mysqlbinlog：用于处理二进制日志文件的实用工具”

备份策略

对于中等级别业务量的系统来说，备份策略可以这么定：第一次全量备份，每天一次增量备份，每周再做一次全量备份，如此一直重复。而对于重要的且繁忙的系统来说，则可能需要每天一次全量备份，每小时一次增量备份，甚至更频繁。为了不影响线上业务，实现在线备份，并且能增量备份，最好的办法就是采用主从复制机制(replication)，在 slave 机器上做备份
出现操作系统崩溃或电源故障时，InnoDB自己可以完成所有数据恢复工作。应遵从下面的指导：
· 一定用–log-bin或甚至–log-bin=log_name选项运行MySQL服务器，其中日志文件名位于某个安全媒介上，不同于数据目录所在驱动器。如果你有这样的安全媒介，最好进行硬盘负载均衡(这样能够提高性能)。
· 定期进行完全备份，使用mysqldump命令进行在线非块备份。
· 用FLUSH LOGS或mysqladmin flush-logs清空日志进行定期增量备份。

备份策略

对于中等级别业务量的系统来说，备份策略可以这么定：第一次全量备份，每天一次增量备份，每周再做一次全量备份，如此一直重复。而对于重要的且繁忙的系统来说，则可能需要每天一次全量备份，每小时一次增量备份，甚至更频繁。为了不影响线上业务，实现在线备份，并且能增量备份，最好的办法就是采用主从复制机制(replication)，在 slave 机器上做备份
出现操作系统崩溃或电源故障时，InnoDB自己可以完成所有数据恢复工作。应遵从下面的指导：
· 一定用–log-bin或甚至–log-bin=log_name选项运行MySQL服务器，其中日志文件名位于某个安全媒介上，不同于数据目录所在驱动器。如果你有这样的安全媒介，最好进行硬盘负载均衡(这样能够提高性能)。
· 定期进行完全备份，使用mysqldump命令进行在线非块备份。
· 用FLUSH LOGS或mysqladmin flush-logs清空日志进行定期增量备份。

MySQL双机热备份

双机热备份通过日志文件来传输入服务器上数据的变化。主服务器进程在数据被更新时触发。并将相应的日志文件发送到从服务器。从服务器进程接收到主服务器发送的日志文件，做出相应的更改操作

双机热备份的工作原理

主服务器的配置

找到配置文件my.ini
编辑my.ini文件,定义到mysqld，加入内容
Server-id=1
Log-bin=c:/log-bin.log
Binlog-do-db=dbname
创建dbname数据库
进入mysql操作，为从服务器授权访问数据库的用户名和密码
grant replication slave on . to ‘root’@从服务器IP identified by ‘密码’
重启MySQL服务器，使更改生效
重新进入到MySQL的命令，执行命令检测配置是否生效:
Show master status\G

从服务器的配置

在从服务器中创建与主服务器中相同的数据库
找到从服务器中的my.ini文件，定位到[mysqld],加入以下内容：
Server-id=2
Master-host=主服务器IP
Master-port=3306(主服务器端口号)
Master-user=root(主服务器用户名)
Master-password=密码(主服务器密码)
Master-connect-retry=60(等待尝试重新连接的秒数)
重新启动MySQL服务器，使配置生效
进入到从服务器MySQL操作,执行启动进程
Slave start

到此这篇关于MySQL备份和还原操作的文章就介绍到这了,更多相关MySQL备份和还原内容请搜索编程客栈(www.lsjlt.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网(www.lsjlt.com)！