MySQL的恢复

什么时候需要恢复数据

  • 硬件故障(如磁盘损坏、raid丢失)
  • 人为删除(如误删数据、黑客入侵)
  • 业务回滚(如游戏bug需要回滚)
  • 正常需求(部署镜像库、查看历史某时刻数据)

恢复条件

  • 有效备份
  • 完整日志
  • row格式的binlog(反转SQL)

恢复工具与命令

  • source
  • innodbackupex
  • mysql

案例

恢复某几条误删数据
恢复误删除表、库
将数据库恢复到指定时间点

MySQL的备份

基本知识

  1. 数据灾备
    应对硬件故障、数据丢失
    应对人为或者程序bug导致的数据误删除
  2. 制作数据库以供服务
    需要将数据库迁移、统计分析等用处;
    需要为线上数据建立一个镜像;

备份内容

  1. 数据
    数据文件或者文本格式数据
  2. 操作日志(binlog)
    数据库变更日志

类型

  1. 冷备份
    关闭数据库服务,完整拷贝数据文件
  2. 热备份
    在不影响数据库读写服务的情况下备份数据库
  3. 物理备份
    以数据页形式拷贝数据文件,速度快
  4. 逻辑备份
    导出为裸数据或者SQL(insert)语句,恢复快
  5. 本地备份
    在数据库服务器本地进行备份
  6. 远程连接数据库进行备份
  7. 全量备份
    备份完整的数据库
  8. 增量备份
    只备份上一次备份发生之后的数据;

备份周期

考虑因素:
+ 数据库的大小
+ 恢复速度要求(快速或者慢速)
+ 备份方式(全量or增量)

常用工具

mysqldump——逻辑备份,单线程,热备
xtrabackup——物理备份,热备
snapshot
mysqldumper
cp

MySQLdump的使用

sujx@x200:~$ mysqldump -help
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

备份整个数据库

mysqldump -usujx -p12345 sujx > sujx.sql

备份数据库中某个表

mysqldump -usujx -p12345 sujx food > food.sql

备份远程数据库

mysqldump -usujx -p12345 -h192.168.31.25 sujx food > food.bak.sql

xtrabackup的使用

安装
sudo apt install xtrabackup -y
OR
download:http://www.percona.com/downloads/xtrabackup

特点

  • 开源,可以在线备份InnoDB表;
  • 支持限速备份,避免对业务造成影响;
  • 支持流备;
  • 支持增量备份;
  • 支持备份文件压缩与加密;
  • 支持并行备份与恢复,速度快

原理

  • 基于innodb的crash-recovery功能;
  • 备份期间允许用户读写,写请求产生redo日志;
  • 从磁盘上拷贝数据文件;
  • 从InnoDB redo log file实时拷贝走备份期间产生的所有redo日志;
  • 恢复数据的时候,数据文件+redo日志=一致性数据

实用脚本

innobackupex

如何制定备份策略

考虑因素
+ 数据库是不是都是InnoDB引擎表
+ 数据量的大小
+ 数据库本地空间是否充足;
+ 需要多快恢复;

MySQL性能优化

性能优化是通过某些有效的方法提高MySQL数据库的性能。性能优化的目的是为了使MySQL数据库运行速度更快、占用的磁盘空间更小。主要包括优化查询速度、优化更新速度和优化MySQL服务器等。

数据库管理员可以使用'show status like 'value';'来查看数据库的性能:

mysql> show status like 'Connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 6     |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 19052 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 71    |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'com_insert';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert    | 1     |
+---------------+-------+
1 row in set (0.01 sec)

优化查询

通过对查询语句的分析,可以了解查询语句的执行情况。MySQL中,可使用explain和edscribe来分析语句。

mysql> explain select * from employee\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employee
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql> create index index_name on employee(name);
Query OK, 0 rows affected (0.02 sec)
mysql> explain select name from employee where name='tom'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employee
   partitions: NULL
         type: ref
possible_keys: index_name
          key: index_name
      key_len: 62
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

使用索引查询时,一定要注意索引的使用情况。例如,like关键字配置的字符创不能以’%‘开头;使用多列索引时,查询条件必须要使用这个索引的第一个字段;使用or关键字时,or关键字所链接的所有条件都必须使用索引。

优化数据库结构

  1. 将字段分解为多个表;
  2. 增加中间表;
  3. 增加冗余字段;
  4. 优化插入记录的速度;
  5. 分析表、检查表和优化表

优化服务器

  1. 优化服务器硬件;
  2. 优化MySQL的参数。

存储过程与函数

存储过程和函数实在数据库中年定义一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且,存储过程和函数实在MySQL服务器中存储和执行的,可以减少客户端和服务器端的数据传输。

创建存储过程和函数

创建存储过程和函数是指将经常使用的一组SQL语句组合在一起,并将这些SQL语句当做一个整体存储在MySQL服务器中。

创建示例表

idnamecompanypriceproduce_timevalidity_timeaddress
1AA饼干AA饼干厂2.520083北京
2CC牛奶CC牛奶厂3.520091河北
3EE果冻EE果冻厂1.520072北京
4FF咖啡FF咖啡厂2020025天津
5GG奶糖GG奶糖厂1420033广东
#创建表
mysql> create table food
(id int(10) not null unique primary key auto_increment,
name varchar(20) not null, 
company varchar(30) not null, 
price float, 
produce_time YEAR, 
validity_time int(4), 
address varchar(50)) 
default charset=utf8;
#插入数据
mysql> insert into food values
(1,'AA饼干','AA饼干厂',2.5,'2008',3,'北京'),
(2,'CC牛奶','CC牛奶厂',3.5,'2009',1,'河北'),  
(3,'EE果冻','EE果冻厂',1.5,'2007',2,'北京'),  
(4,'FF咖啡','FF咖啡厂',20,'2002',5,'天津'),  
(5,'GG奶糖','GG奶糖厂',14,'2003',3,'广东');
#创建存储过程
mysql> delimiter &&
mysql> create procedure food_price_count(in price_info1 float,in price_info2 float,out  count int)
    -> reads sql data
    -> begin
     -> declare temp float;
    -> declare match_price cursor for select price from food;
    -> declare exit handler for not found close match_price;
    -> set @sum=0;
    -> select count(*) into count from food
    -> where price>price_info1 and price_info2;
    -> open match_price;
    -> repeat
    -> fetch match_price into temp;
    -> if temp>price_info1 and price_info2
    -> then set @sum=@sum+temp;
    -> end if;
    -> until 0 end repeat;
    -> close match_price;
    -> end&&
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call food_price_count(2,18,@count);
Query OK, 0 rows affected (0.00 sec)
mysql> select @count,@sum;
+--------+------+
| @count | @sum |
+--------+------+
|      4 |   40 |
+--------+------+
1 row in set (0.00 sec)
#查看存储过程状态
mysql> show create procedure food_price_count\G
#删除存储过程
mysql> drop procedure food_price_count;

MySQL函数

MySQL数据库中提供了丰富的函数。通过这些函数可以简化用户的操作。

数学函数

数学函数是MySQL中常用的一类函数,主要用欧冠与处理数字,包括整型、浮点数等。数学好书包括绝对值函数、正弦函数、余弦函数和获取随机数的函数等。

字符串函数

字符串函数主要用于处理表中的字符串。包括求字符串长度、合并字符串、在字符串中插入子串和大小写字母切换等函数。

时间和日期函数

日期和时间函数主要用于处理表中的日期和时间数据。日期和时间函数包括获取当日日期的函数、获取当前时间的函数、计算日期的函数、计算时间的函数等。

条件判断函数

条件判断函数用来在SQL语句中进行条件判断。根据是否满足判断条件,SQL语句执行不同的分支。

系统信息函数

系统信息函数用来查询MySQL数据库的系统信息,例如数据库版本、当前用户等。

mysql> select database(),schema(),version(),session_user();
+------------+----------+-------------------------+----------------+
| database() | schema() | version()               | session_user() |
+------------+----------+-------------------------+----------------+
| sujx       | sujx     | 5.7.19-0ubuntu0.16.04.1 | sujx@localhost |
+------------+----------+-------------------------+----------------+
1 row in set (0.00 sec)

加密函数

因为数据库总有些敏感信息不希望被其他人看到,就应该通过加密的方式来使得这些数据变成看似乱码的数据。例如用户密码。

其他函数