1、为什么要备份
-
备份:能够防止由于机械故障以及人为误操作带来的数据丢失,例如将数据库文件保存在了其它地方。
-
冗余: 数据有多份冗余,但不等于备份,只能防止机械故障带来的数据丢失,例如主备模式、数据库集群。
2.MySQL数据备份需要重视的内容
备份内容 databases Binlog my.conf
所有备份数据都应放在非数据库本地,而且建议有多份副本。
测试环境中做日常恢复演练,恢复比备份更为重要。
备份过程中必须考虑因素:
1. 数据的一致性
2. 服务的可用性
3、MySQL 备份类型
1.物理备份: 直接复制数据库文件,适用于大型数据库环境,不受存储引擎的限制,但不能恢复到不同的MySQL版本。
####1、热备(hot backup)
在线备份,数据库处于运行状态,这种备份方法依赖于数据库的日志文件
- 对应用基本无影响(但是性能还是会有下降,所以尽量不要在主上做备份,在从库上做)
####2、冷备(cold backup)
备份数据文件,需要停机,是在关闭数据库的时候进行的
- 备份 datadir 目录下的所有文件
####3、温备(warm backup)
- 针对myisam的备份(myisam不支持热备),备份时候实例只读不可写,数据库锁定表格(不可写入但可读)的状态下进行的
- 对应用影响很大
- 通常加一个读锁2.逻辑备份: 备份的是建表、建库、插入等操作所执行SQL语句(DDL DML DCL),适用于中小型数据库,效率相对较低。 (相当于bin
5、MySQL 备份工具
1、ibbackup
- 官方备份工具
- 收费
- 物理备份
2、xtrabackup
- 开源社区备份工具
- 开源免费,上面的免费版本(老版本有问题,备份出来的数据可能有问题)
- 物理备份
3、mysqldump
- 官方自带备份工具 开源免费
- 逻辑备份(速度慢)
4、mysqlbackup
- mysql 官方备份工具
- innodb 引擎的表mysqlbackup可以进行热备
- 非innodb表mysqlbackup就只能温备
- 物理备份,备份还原速度快
- 适合大规模数据使用
2.物理备份的方式
1.完全备份-----完整备份: 每次对数据进行完整的备份,即对整个数据库的备份、数据库结构和文件结构的备份,保存的是备份完成时刻的数据库,是差异备份与增量备份的基础。 优点:备份与恢复操作简单方便,恢复时一次恢复到位,恢复速度快 缺点:占用空间大,备份速度慢
2.增量备份: 每次备份上一次备份到现在产生的新数据
只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次的增量备份的时间为时间点,仅备份这之间的数据变化.
特点:因而备份的数据量小,占用空间小,备份速度快。但恢复时,需要从上一次的完整备份起按备份时间顺序,逐个备份版本进行恢复,恢复时间长,如中间某次的备份数据损坏,将导致数据的丢失。
3.差异备份:只备份跟完整备份不一样的
备份那些自从第一次次完全备份之后被修改过的所有文件,备份的时间起点是从第一次次完整备份起,且以后每次备份都是和第一次完整备份进行比较(注意是第一次,不是上一次),备份自第一次完整备份以来所有的修改过的文件。备份数据量会越来越大。
特点:占用空间比增量备份大,比完整备份小,恢复时仅需要恢复第一个完整版本和最后一次的差异版本,恢复速度介于完整备份和增量备份之间。
简单的讲,完整备份就是不管三七二十一,每次都把指定的备份目录完整的复制一遍,不管目录下的文件有没有变化;增量备份就是每次将之前(第一次、第二次、直到前一次)做过备份之后有变化的文件进行备份;差异备份就是每次都将第一次完整备份以来有变化的文件进行备份。
4.mysqldump逻辑备份 ---- 推荐优先使用
mysqldump 是 MySQL 自带的逻辑备份工具。可以保证数据的一致性和服务的可用性。
如何保证数据一致?在备份的时候进行锁表会自动锁表。锁住之后在备份。
本身为客户端工具:
远程备份语法: # mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql
本地备份语法: # mysqldump -u用户名 -p密码 数据库名 > 备份文件.sql
1.常用备份选项
-A, --all-databases #备份所有库
-B, --databases #备份多个数据库
-F, --flush-logs #备份之前刷新binlog日志
--default-character-set #指定导出数据时采用何种字符集,如果数据表不是采用默认的latin1字符集的话,那么导出时必须指定该选项,否则再次导入数据后将产生乱码问题。
--no-data,-d #不导出任何数据,只导出数据库表结构。
--lock-tables #备份前,锁定所有数据库表
--single-transaction #保证数据的一致性和服务的可用性
-f, --force #即使在一个表导出期间得到一个SQL错误,继续。
注意
使用 mysqldump 备份数据库时避免锁表:
对一个正在运行的数据库进行备份请慎重!! 如果一定要 在服务运行期间备份,可以选择添加 --single-transaction选项,类似执行: mysqldump --single-transaction -u root -p123456 dbname > mysql.sql
2.备份表
语法: # mysqldump -u root -p1 db1 t1 > /db1.t1.bak
[root@mysql-server ~]# mkdir /home/back #创建备份目录
[root@mysql-server ~]# mysqldump -uroot -p'qf123' company employee5 > /home/back/company.employee5.bak
备份多个表:
语法: mysqldump -u root -p1 db1 t1 t2 > /db1.t1_t2.bak
[root@mysql-server ~]# mysqldump -uroot -p'qf123' company new_t1 new_t2 > /home/back/company.new_t1_t2.bak
3、备份库
备份一个库:相当于将这个库里面的所有表全部备份。 语法: # mysqldump -u root -p1 db1 > /db1.bak [root@mysql-server ~]# mysqldump -uroot -p'qf123' company > /home/back/company.bak 备份多个库: 语法:mysqldump -u root -p1 -B db1 db2 db3 > /db123.bak [root@mysql-server ~]# mysqldump -uroot -p'qf123' -B company testdb > /home/back/company_testdb.bak 备份所有的库: 语法:# mysqldump -u root -p1 -A > /alldb.bak [root@mysql-server ~]# mysqldump -uroot -p'qf123' -A > /home/back/allbase.bak
到目录下面查看一下:
4、恢复数据库和表
为保证数据一致性,应在恢复数据之前停止数据库对外的服务,停止binlog日志 因为binlog使用binlog日志恢复数据时也会产生binlog日志。
为实验效果先将刚才备份的数据库和表删除了。登陆数据库:
[root@mysql-server ~]# mysql -uroot -pqf123
mysql> show databases;
mysql> drop database company;
mysql> \q
5、恢复库
登陆mysql创建一个库
mysql> create database company;
恢复:
[root@mysql-server ~]# mysql -uroot -p'qf123' company < /home/back/company.bak
6、恢复表
登陆到刚才恢复的库中将其中的一个表删除掉 mysql> show databases; mysql> use company mysql> show tables; +-------------------+ | Tables_in_company | +-------------------+ | employee5 | | new_t1 | | new_t2 | +-------------------+ mysql> drop table employee5; 开始恢复: mysql> set sql_log_bin=0; #停止binlog日志 Query OK, 0 rows affected (0.00 sec) mysql> source /home/back/company.employee5.bak; -------加路径和备份的文件 恢复方式二: # mysql -u root -p1 db1 < db1.t1.bak 库名 备份的文件路径
7、备份及恢复表结构
1.备份表结构:
语法:mysqldump -uroot -p123456 -d database table > dump.sql
[root@mysql-server ~]# mysqldump -uroot -p'qf123' -d company employee5 > /home/back/emp.bak
恢复表结构:
登陆数据库创建一个库
mysql> create database t1;
语法:# mysql -u root -p1 -D db1 < db1.t1.bak
[root@mysql-server ~]# mysql -uroot -p'qf123' -D t1 < /home/back/emp.bak
登陆数据查看:
8、数据的导入导出,没有表结构。
表的导出和导入只备份表内记录,不会备份表结构,需要通过mysqldump备份表结构,恢复时先恢复表结构,再导入数据。
mysql> show variables like "secure_file_priv"; ----查询导入导出的目录。
修改安全文件目录:
1.创建一个目录:mkdir 路径目录
[root@mysql-server ~]# mkdir /sql
2.修改权限
[root@mysql-server ~]# chown mysql.mysql /sql
3.编辑配置文件:
vim /etc/my.cnf
在[mysqld]里追加
secure_file_priv=/sql
4.重新启动mysql.
1.导出数据
登陆数据查看数据
mysql> show databases; #找到test库
mysql> use test #进入test库
mysql> show tables; #找到它t3表
mysql> select * from t3 into outfile '/sql/test.t3.bak';
2.数据的导入
先将原来表里面的数据清除掉,只保留表结构
mysql> delete from t3;
mysql> load data infile '/sql/test.t3.bak' into table t3;
如果将数据导入别的表,需要创建这个表并创建相应的表结构。
5、通过binlog恢复
开启binlog日志:
[root@mysql-server ~]# vim /etc/my.cnf
log-bin=/var/log/sql-bin/mylog
server-id=1
创建目录并修改权限
[root@mysql-server ~]# mkdir /var/log/sql-bin
[root@mysql-server ~]# chown mysql.mysql /var/log/sql-bin
[root@mysql-server ~]# systemctl restart mysqld
mysql> flush logs; #刷新binlog日志会截断产生新的日志文件
mysql> create table testdb.t3(id int); #创建一个表
根据位置恢复
找到要恢复的sql语句的起始位置、结束位置
[root@mysql-server sql-bin]# mysqlbinlog mylog.000002
测试
[root@mysql-server ~]# mysql -uroot -p'qf123'
mysql> drop table testdb.t3; #将这个表删除
Query OK, 0 rows affected (0.01 sec)
恢复:
[root@mysql-server ~]# cd /var/log/sql-bin/
[root@mysql-server sql-bin]# mysqlbinlog --start-position 219 --stop-position 321 mylog.000002 | mysql -uroot -p'qf123'
mysql: [Warning] Using a password on the command line interface can be insecure.