mysql学习笔记三 —— 数据恢复与备份

要点：

1、存储引擎
2、导入导出
3、备份与恢复

查看当前数据库中的所有表
use db1；
show tables；

1、存储引擎
不同的发动机（引擎）适用的汽车类型不一样。
存储和处理的不同方式。不同的存储引擎适用的应用场景也不同。
MySQL 插件式存储引擎是MySQL独有的设计，主要引擎有以下两种：
myisam
InnoDB

1.1 myisam存储引擎
MySQL5.5版本之前的默认存储引擎。创建表对象时，如果没有显式指定表的存储引擎，那么这个表就是myisam表。
创建一个myisam表：
mysql> create table t1(id int not null auto_increment primary key unique , name char(5),xingbie enum('M','F'));(默认)

一个myisam表在文件系统上对应三个文件：
*.frm 表结构文件
*.MYD 存放数据
*.MYI 存放索引

myisam表有特点：
支持锁机制 表锁 缺点：并发读没什么影响，但是如果有insert 、update操作那么会影响读操作，适用于读多写少的表 查询速度快 读取速度快，写入速度也快。写入速度块，因为写入到缓存，按照操作系统的机制达到一定条件后刷新到磁盘，不能够保证数据高可靠。
最大存储能力 256T ext4 xfs

1.2 innodb
5.5版本之后的默认存储引擎
mysql> create table t2(id int not null auto_increment primary key unique , name char(5),xingbie enum('M','F')) engine=innodb;   //指定存储引擎
mysql> show create table t2;

修改MySQL默认的存储引擎：
方法一：修改配置文件
default-storage-engine=innodb（之后新建库的表默认是innodb）

方法二：修改全局和会话参数
mysql> set global storage_engine=myisam;

mysql> set session storage_engine=myisam;

innodb表的文件：
*.frm 表结构文件
ibdata1 innodb表系统表空间文件，所有InnoDB表的数据、undo（回滚）、索引等 ，数据目录下(/var/lib/mysql)
当删除数据后，InnoDB的系统表空间不会降低，即使用truncate删除也是如此。

InnoDB表的特性：
InnoDB支持事物：
事物的特性ACID：
原子性：不能再往下分割，事物是最小的一个单位。事物提交后，要么成功提交，要回回滚会原来的状态。
一致性：指数据完整性的约束。事物从一种一致性的状态转换成另外一种一致性的状态。name字段 unique
隔离性：两个不同的事物不能看到对方的操作。
持久性：提交过的数据就会保存起来，不会丢失，InnoDB独有日志，redo log（重做日志）；硬件：raid 、磁盘。数据高可靠，高可用。
日志文件组：默认两个文件（ib_logfile0 ib_logfile1），每个5M，循环使用 建议不要太大，否则故障恢复时间过长。

例，开启一个事物：
默认情况下，MySQL隐式提交，所有修改操作自动提交。
mysql> set autocommit=off; 关闭自动提交功能，当commit才会提交修改操作
mysql> start transaction; 开启一个事物，或者用begin；来开启一个事物
mysql> roolback; 回滚到之前的状态
mysql> commit; 提交事物
锁机制：row level行级锁 写并发大，读并发大。适用于读写都多的情况。缺点：相对慢

两者区别对比：

1、MyISAM：默认表类型，它是基于传统的ISAM类型，ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写，它是存储记录和文件的标准方法。不是事务安全的，而且不支持外键，如果执行大量的select，insert MyISAM比较适合。

2、InnoDB：支持事务安全的引擎，支持外键、行锁、事务是他的最大特点。如果有大量的update和insert，建议使用InnoDB，特别是针对多个并发和QPS较高的情况。

2.导入导出
2.1 mysqlimport命令行工具导入数据     生产常用工具导入，如mysql常用Navicat连接数据库操作，导入数据时直接使用其自带工具导入功能。

使用Navicat导入实例：

步骤一：做好excel表数据内容，其他文档也行如txt.

excel表数据（表列名为数据库xxx表的列名）

步骤二：打开Navicat，连接数据库，选择需要导入数据的表名，右键选择“导入向导”，并选择导入文件类型。

步骤三：依次按要求下一步

自动识别分隔符，默认即可。

自动对比导入表格与数据库表格列名，也可以手动选择

选择导入模式，此处选择添加

直接下一步，点击开始即可完成数据导入。

使用mysqlimport导入

mysqlimport -uroot -p123456 -S /var/lib/mysql/mysql.sock DB1 --fields-terminated-by=',' --default-character-set=utf8 /tmp/loaddata.txt(此文件要和库中表结构相同才能载入)

--fields-terminated-by=',' 指定列的分隔符，默认“tab”
--default-character-set=utf8 链接字符集 需要注意要修改服务器的所有字符集为utf8

配置文件：(重要提示 ：建议开启mysql前就设置，否则新建库中表文件的格式很难改过来)
[mysqld]
character_set_server=utf8 //服务字符集，全局

[mysqld_safe]
character_set_database=utf8

[mysql]
default-character-set=utf8 //连接字符集

重启mysql

如果出现乱码，修改表、列字符集：
mysql> alter table DB1.loaddata CHARACTER SET utf8;
mysql> alter table loaddata modify city char(5) CHARACTER SET utf8;

修改了源文件：
[root@localhost tmp]# cat /tmp/loaddata.txt
100001#胡一#北京#[e-mail]
100002#胡二#上海#[e-mail]
100003#胡三#广州#[e-mail]
100004#胡四#深圳#[e-mail]
100005#胡五#杭州#[e-mail]

mysql> create table t7 like loaddata; //创建t7表，表结构同loaddata表

导入：
[root@localhost tmp]# mysqlimport -uroot -p -S /var/lib/mysql/mysql.sock --default-character-set=utf8 --fields-terminated-by="#" DB1 /tmp/t7.txt
Enter password:

修改原文件：
[root@localhost tmp]# mv /tmp/t7.txt /tmp/t8.txt
[root@localhost tmp]# cat /tmp/t8.txt
"100001"#"胡#一"#"北#京"#"[e-mail]"

创建对应的表：（名称相同）
mysql> create table t8 like loaddata;

导入：
[root@localhost tmp]# mysqlimport -uroot -p -S /var/lib/mysql/mysql.sock --default-character-set=utf8 --fields-terminated-by="#" --fields-enclosed-by=\" DB1 /tmp/t8.txt
mysqlimport -uroot -p123 -S /var/lib/mysql/mysql.sock db1 --default-character-set=utf8 --fields-terminated-by=',' /tmp/t3.txt

--fields-enclosed-by=\" 指定列值的包括符 ，即在"" 之内的才是一个具体的列值

--lines-terminated-by 指定行分隔符（换行符），\n

 

2.2 SQL语句导入数据 load data infile
mysql> load data infile '/tmp/loaddata.txt' into table DB1.t10 character set utf8 fields terminated by ',' ;
character set utf8 指定链接字符集
fields terminated by ',' 指定字段分隔符

[root@localhost tmp]# cat loaddata.txt
100001#"胡#一"#"北#京"#"[e-mail]"

mysql> load data infile "/tmp/loaddata.txt" into table DB1.t11 character set utf8 fields terminated by "#" enclosed by "\"" ;

enclosed by "\"" 等于mysqlimport中的--fields-enclosed-by 指定的是列值包括符号（字段包括符号）

2.3 SQL导出数据
select

mysql> select * from t10 into outfile "/tmp/xxdddd.txt" character set utf8 fields terminated by ',';

3、备份与恢复
分情况 myisam InnoDB

冷备、热备
增量备份与恢复
按时间点、binlog位置恢复
mysql ab复制

3.1 冷备份
实现方式：先关闭服务 拷贝相关文件
备份时要考虑你的mysql中表都是什么表，如果全是myisam表，可以实现全备、备份部分库、备份部分表；但是如果全是InnoDB表，只能全库备份。

InnoDB表的全库备份--冷备份：
3.1.1 关库
service mysqld stop
3.1.2 cp到备份集存放路径
mkdir /opt/all.bak/
cp -pr /var/lib/mysql/* /opt/all.bak/

myisam表备份：
全备份
部分库
部分表

全库备份恢复：关库
恢复部分库、部分表：不需要关库

冷备份：
需要关库-->影响线上业务

热备份：
不需要关库备份，一定程度上不影响线上业务。
mysqldump命令行工具
逻辑备份的一种，备份的是SQL语句（insert、update）

myisam表可以实现全库备份、部分库备份、部分表备份；
InnoDB表只能实现全库备份
热备时加锁，能够实现备份集与数据库中的数据一致。
myisam和InnoDB加锁的选项不同
myisam表 -x
innodb表 --single-transaction 事物表

用mysqldump实现myisam表的全库备份、部分库备份、部分表备份：
[root@localhost db1]# mysqldump -uroot -p -x --all-databases > /opt/all.sql
注意：由于mysql在全量导出时不导出event事件表,故需要在全量导出时忽略事件表，可能会报mysql.event的错误，解决如下：

mysqldump -uroot -p -x -A --events ignore-tables=mysql.events --all-databases > /opt/all.sql (经过测试--all-databases没有-A好用，实例如下)

[root@localhost db1]# mysqldump -u root -p -x --databases db1 > /opt/db1.sql
Enter password:
[root@localhost db1]# mysqldump -uroot -p -x db2 t1 > /opt/db2_t1.sql

选项：
-x;--lock-all-tables:锁定所有库中的所有表（MyISAM表）
-A；--all-databases：备份所有库中所有的表（MyISAM表和InNODB表）
--single-transaction：加事务锁（适合应用于InNODB引擎表和MyISAM表）

MyISAM：

全库备份：
[root@localhost ~]# mysqldump -usystem -p -x -A > /opt/all.sql

备份部分库：
[root@localhost ~]# mysqldump -usystem -p -x --databases db1 > /opt/db1.sql

备份部分表：
[root@localhost ~]# mysqldump -usystem -p -x db1 t1 > /opt/t1.sql

恢复：
[root@localhost mysql]# mysql < /opt/all.sql
[root@localhost DB1]# mysql -uroot -p123456 -S /var/lib/mysql/mysql.sock < /opt/db1.sql
[root@localhost DB1]# mysql -uroot -p123456 -S /var/lib/mysql/mysql.sock db2 < /opt/db2_t1.sql

Innodb:(只能全库备份)

[root@localhost ~]# mysqldump -usystem -p --single-transaction -A > /opt/all.sql     //全数据库备份

[root@localhost ~]# mysqldump -usystem -p --single-transaction --databases  test > /opt/all.sql    //test全库备份

恢复：
[root@localhost mysql]# mysql < /opt/all.sql
[root@localhost DB1]# mysql -uroot -p123456 -S /var/lib/mysql/mysql.sock < /opt/all.sql

生产实例：
备份mysql：
mysqldump -u$User -p$Passwd -R --opt --flush-logs --databases test | gzip > $Backup_mysql_path/$Backup_date.mysql_backup.gz      //实验加上--opt执行一次会产生2个binlog日志
备份生产test数据库全库并通过gzip压缩，按日期命令压缩包名（--opt可实现加锁,等同于调用add-locks、lock-tables等参数）
还原mysql：
mysql -uroot -p123456 -S /var/lib/mysql/mysql.sock < mysql_backup //Linux系统中后缀名不重要，该mysql_backup是通过mysqldump备份test这个数据库的全库备份sql文件。也可命令为mysq_backup.sql

推荐备份参数添加

-R --single-transaction --quick --flush-logs --databases test

解释：

--opt：此mysqldump命令参数是可选的，如果带上这个选项代表激活了mysqldump命令的 quick，add-drop-table，add-locks，extended-insert，lock-tables参数，也就是通--opt参数在使用Mysqldump导出Mysql数据库信息时不需要再附加上述这些参数。

　　–quick：代表忽略缓冲输出，mysqldump命令直接将数据导出到指定的SQL文件。
　　–add-drop-table：顾名思义，就是在每个CREATE TABEL命令之前增加DROP-TABLE IF EXISTS语句，防止数据表重名。
　　–add-locks：表示在INSERT数据之前和之后锁定和解锁具体的数据表，你可以打开mysqldump导出的SQL文件，在INSERT之前会出现LOCK TABLES和UNLOCK TABLES语句,防止在这些记录被再次导入数据库时其他用户对表进行的操作。
　　–extended-insert (-e)：此参数表示可以多行插入。

增量备份
结合全库备份，每周一全库备份，周二至周日增量备份 1 2 3

配置文件中开启binlog
log_bin=binlog //名称
log_bin_index=binlog.index //设置binlog索引

重启mysql
[root@localhost mysql]# ls
binlog.000001 db1 db3 ib_logfile0 mysql
binlog.index db2 ibdata1 ib_logfile1 mysql.sock

为了便于管理，可以将binlog日志独立出来，如:一天产生一个binlog文件,binlog.000001、binlog.000002....

怎样实现增量备份：
通过mysql的binlog日志来实现增量备份。
binlog：记录的是SQL语句，记录insert、update、drop、delete的SQL语句；二进制日志，mysqlbinlog可以查看binlog日志内容

刷新binlog的方式：
service mysqld restart
之后将产生binlog.000002，这时就可以将binlog.000001当作昨天的备份了（但是此方法需要重启mysql，一般不用，用下面的方式刷新）
flush logs； mysql运行时执行SQL语句
mysql -uroot -p123 -e "flush logs;"

增量备份实现：
周二至周日每天0点0分 执行flush logs 然后将之前的那个binlog拷贝到备份集目录 --crontab计划任务

1 2 3 10点 根据时间点恢复、根据binlog位置（postion）

查看刷新出来的binlog日志信息 mysqlbinlog binlog.0000003

（以下恢复需要关闭mysql服务）
先恢复全库备份
恢复前，先关闭binlog（修改配置文件），否则所有的操作又记录到binlog中
[root@localhost mysql]# mysql < /tmp/all.sql
恢复增量备份000003
[root@localhost mysql]# mysqlbinlog /opt/binlog/binlog.000003 |mysql -uroot -p123456

先查看binlog中的信息：mysqlbinlog /var/lib/mysql/binlog.000004
再去恢复增量备份000004 按照时间点、位置来恢复
#170816 16:27:04
#170816 16:27:42
# at 286
end_log_pos 379

[root@localhost mysql]# mysqlbinlog --start-datetime='2017-08-16 16:27:04' --stop-datetime='2017-08-16 16:27:42' /opt/binlog/binlog.000004 |mysql -uroot -p123456

[root@localhost mysql]# mysqlbinlog --start-position=286 --stop-position=379 /opt/binlog/binlog.000004 |mysql -uroot -p123456