标签：mysql

Vagrant 中安装 Mysql 如何从外边链接

在 Vagrant 中安装 Mysql 后从外部链接需要三步

  • 设置私有 ip
  • 去掉绑定 127.0.0.1
  • 对所有 ip 开放

设置私有 ip

修改 Vagrantfile 添加 private_network，这样外部可以通过该 ip 链接

config.vm.network "private_network", ip: "192.168.33.10"

这步需要放在第一步来完成，随后重新加载配置

$ vagrant reload

去掉绑定 127.0.0.1

如果你是使用 rpm 来安装的话，修改 /etc/mysql/mysql.conf.d/mysql.cnf，将绑定 127.0.0.1 这一行注释掉

# bind-address            = 127.0.0.1

对所有 ip 开放

登陆 Mysql 并对所有外网 ip 开放权限

$ mysql -uroot -p
> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'your_mysql_password' WITH GRANT OPTION;

这样从外部通过如下命令就可以访问了

$ mysql -uroot -p -h 192.168.33.10

注意

如果在生产环境上的话建议只对指定 ip 开放权限

> GRANT ALL PRIVILEGES ON *.* TO 'root'@'213.1.2.1' IDENTIFIED BY 'your_mysql_password' WITH GRANT OPTION;

亲测Mysql表结构为InnoDB类型从ibd文件恢复数据

客户的机器系统异常关机，重启后mysql数据库不能正常启动，重装系统后发现数据库文件损坏，悲催的是客户数据库没有进行及时备份，只能想办法从数据库文件当中恢复，查找资料，试验各种方法，确认下面步骤可行：

一、找回表结构，如果表结构没有丢失直接到下一步

a、先创建一个数据库，这个数据库必须是没有表和任何操作的。
b、创建一个表结构，和要恢复的表名是一样的。表里的字段无所谓。一定要是innodb引擎的。CREATE TABLE `test`(  `testID` bigint(20)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
c、关闭mysql， service mysqld stop；
d、用需要恢复的frm文件覆盖刚新建的frm文件；  
e、修改my.ini 里  innodb_force_recovery=1 ， 如果不成修改为 2,3,4,5,6。
f、 启动mysql，service mysqld start；show create table test就能够看到表结构信息了。

二、找回数据

a、建立一个数据库，根据上面导出的创建表的sql执行创建表。
b、找到记录点。先要把当前数据库的表空间废弃掉，使当前ibd的数据文件和frm分离。  ALTER TABLE test DISCARD TABLESPACE；
c、把之前要恢复的 .ibd文件复制到新的表结构文件夹下。 使当前的ibd 和frm发生关系。ALTER TABLE test  IMPORT TABLESPACE;

d、将恢复好的数据导出就行了

Django+Ngnix+Gunicorn+Mysql部署Centos的坑

环境配置

  • Centos 7
  • Python 3.6.5
  • Virtualenvwrapper 4.8.2
  • Django 2.0.5
  • Mysql 5.7.22
  • Ngnix 1.12.2
  • Gunicorn 19.8.1

注：这并不是什么教程

python虚拟环境配置

Virtualenvwrapper安装失败

试试这条命令 :-p

sudo pip install virtualenvwrapper --upgrade --ignore-installed six

简答解释就是包six版本有点低，详细解释点此

找不到virtualenvwrapper.sh文件

按照官方文档上说是安在 /usr/local/bin/virtualenvwrapper.sh，但是并没有，每个系统的情况不一样，使用如下命令

pip uninstall virtualenvwrapper

不用担心，这并不会立刻移除virtualenvwrapper，会输出以下内容，再输入n取消安装

Uninstalling virtualenvwrapper-4.8.2:
  /usr/bin/virtualenvwrapper.sh
  /usr/bin/virtualenvwrapper_lazy.sh
  /usr/lib/python2.7/site-packages/virtualenvwrapper-4.8.2-py2.7-nspkg.pth
  /usr/lib/python2.7/site-packages/virtualenvwrapper-4.8.2.dist-info/DESCRIPTION.rst
  /usr/lib/python2.7/site-packages/virtualenvwrapper-4.8.2.dist-info/INSTALLER
  /usr/lib/python2.7/site-packages/virtualenvwrapper-4.8.2.dist-info/METADATA
  ......
  Proceed (y/n)?

看第一行，这下知道在哪里的吧，对于我的centos7来说是在 /usr/bin/virtualenvwrapper.sh 下

具体见此https://stackoverflow.com/questions/12647266/where-is-virtualenvwrapper-sh-after-pip-install/41676706#41676706

Mysql配置

安装教程https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-centos-7 ，比较靠谱

中文乱码

问题可能出现在两个地方，一个是Django的配置文件中，还有一个就是Mysql的配置出问题

Django数据配置

找到project的settings.py，修改这两个地方，注意，对于较新版本的django，把zh-cn改成了zh-Hans

LANGUAGE_CODE = 'zh-Hans'

TIME_ZONE = 'Asia/Shanghai'

Mysql数据配置

先登录到你的mysql中，执行如下命令

SHOW VARIABLES LIKE 'character_set_%';

发现大多数行的值都不是utf-8，而是latin1

那么，找到mysql的配置文件my.cnf

对于 MySQL 5.7+，执行以下命令，别的情况见此

mysqladmin --help | grep 'Default options' -A 2

输出的第二行就是文件的所在

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

打开此文件 /etc/my.cnf

添加如下内容如下，别的地方别修改

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

...

[mysql]
default-character-set=utf8

改完之后记得重启mysql服务

sudo systemctl restart mysqld

gunicorn找不到 django.core.xxx

找到gunicorn的所在地

which gunicorn

应为是在python虚拟环境下安装的，所以一般在//.virtualenvs//bin/gunicorn，如果不是这样的话终极解决方案是把要输入 gunicorn 的地方全改成 //.virtualenvs//bin/gunicorn

Django 静态文件404

在production模式下Django并不支持静态文件的处理，这一切都要交给一个服务器处理，比如此处的Nginx，千万要记住！！！！！！

Nginx无法正常启动

(ps1：centos7安装Nginx的教程见此，比较靠谱)

(ps2：默认相关防火墙已开启)

在输入

sudo systemctl start nginx
sudo systemctl status nginx

发现其启动失败，则多半是你配置文件写错的结果，输入一下命令排错

sudo nginx -t

修改完之后记得刷新哦

sudo service nginx restart
#或者
sudo service nginx reload

LNMP架构 (1) 之 架构介绍、MySQL安装、PHP安装、Nginx介绍

1. LNMP架构介绍

LNMP代表的就是：Linux系统下Nginx+MySQL+PHP这种网站服务器架构。

未分类

  • 和LAMP不同的是，提供web服务的是Nginx
  • 并且php是作为一个独立服务存在的，这个服务叫做php-fpm
  • Nginx直接处理静态请求，动态请求会转发给php-fpm。

2. MySQL安装

2.1 卸载二进制包安装的MySQL

确认MySQL服务运行状态，并停止

[root@host ~]# ps -ef | grep mysql
[root@host ~]# /etc/init.d/mysql.server status
[root@host ~]# /etc/init.d/mysql.server stop

删除MySQL安装时的相关文件

[root@host ~]# rm -rf /usr/local/mysql 
[root@host ~]# rm -rf /etc/init.d/mysqld 
[root@host ~]# rm -rf /data/mysql

2.2 安装MySQL

和之前LAMP环境安装MySQL的方法一样 可以参考前面的文章 LAMP架构及安装配置(https://my.oschina.net/zhouyuntai/blog/1647058) 中的Mysql安装。

3. PHP安装

和LAMP安装PHP有区别，需要开启php-fpm服务。

3.1 准备PHP的包和用户

[root@host ~]# cd /usr/local/src/
[root@host src]# wget http://cn2.php.net/distributions/php-5.6.30.tar.gz   //下载php二进制包
[root@host src]# tar zxvf php-5.6.30.tar.gz   //解压缩
[root@host  src]# useradd -s /sbin/nologin php-fpm   //创建专门账号用来运行php-fpm服务，因为在LNMP环境中，PHP是以一种服务的形式独立存在的

3.2 卸载之前编译安装的PHP（如果之前有安装的话）

[root@host ~]# cd /usr/local/src
[root@host src]# ls
[root@host src]# cd php-5.6.30
[root@host php-5.6.30]# ls
[root@host php-5.6.30]# make clean

3.3 安装PHP

[root@host php-5.6.30]# cd php-5.6.30
[root@host php-5.6.30]# ./configure --prefix=/usr/local/php-fpm --with-config-file-path=/usr/local/php-fpm/etc --enable-fpm --with-fpm-user=php-fpm --with-fpm-group=php-fpm --with-mysql=/usr/local/mysql --with-mysqli=/usr/local/mysql/bin/mysql_config --with-pdo-mysql=/usr/local/mysql --with-mysql-sock=/tmp/mysql.sock --with-libxml-dir --with-gd --with-jpeg-dir --with-png-dir --with-freetype-dir --with-iconv-dir --with-zlib-dir --with-mcrypt --enable-soap --enable-gd-native-ttf --enable-ftp --enable-mbstring --enable-exif --with-pear --with-curl  --with-openssl   //初始化

3.4 编译过程中的出错排查

错误1：

onfigure: error: xml2-config not found. Please check your libxml2 installation.

解决办法1：

[root@host php-5.6.30]# yum list |grep libxml2
libxml2.x86_64                          2.9.1-6.el7_2.3                @anaconda
libxml2.i686                            2.9.1-6.el7_2.3                base    
libxml2-devel.i686                      2.9.1-6.el7_2.3                base    
libxml2-devel.x86_64                    2.9.1-6.el7_2.3                base    
libxml2-python.x86_64                  2.9.1-6.el7_2.3                base    
libxml2-static.i686                    2.9.1-6.el7_2.3                base    
libxml2-static.x86_64                  2.9.1-6.el7_2.3                base    

[root@host php-5.6.30]# yum install -y libxml2 libxml2-devel     //一般只需要安裝devel的庫文件就好了

错误2：

configure: error: Cannot find OpenSSL's <evp.h>

解决办法2：

[root@host php-5.6.30]# yum install -y openssl openssl-devel

错误3：

configure: error: Please reinstall the libcurl distribution -
    easy.h should be in <curl-dir>/include/curl/

解决办法3：

[root@host php-5.6.30]# yum install -y libcurl libcurl-devel

错误4：

configure: error: jpeglib.h not found.

解决办法4：

[root@host php-5.6.30]# yum install -y libjpeg libjpeg-turbo-devel

错误5：

configure: error: png.h not found.

解决办法5：

[root@host php-5.6.30]# yum install -y libpng libpng-devel

错误6：

configure: error: freetype-config not found.

解决办法6：

[root@host php-5.6.30]# yum install -y freetype freetype-devel

错误7：

configure: error: mcrypt.h not found. Please reinstall libmcrypt.

解决办法7：

[root@host php-5.6.30]# yum install -y libmcrypt libmcrypt-devel

错误8：

configure: error: mcrypt.h not found. Please reinstall libmcrypt.
(centos源不能安装libmcrypt-devel，由于版权的原因没有自带mcrypt的包rpm -qa|grep limcrypt limcrypt-devel,此源为rethot社区版的源)

解决办法8：安装第三方yum源

wget http://www.atomicorp.com/installers/atomic
sh ./atomic

yum  install  php-mcrypt  libmcrypt  libmcrypt-devel

检测、编译和安装

[root@host php-5.6.30]# echo $?
0

[root@host php-5.6.30]# make 

[root@host php-5.6.30]# make install

3.5 配置PHP

添加配置文件

[root@host php-5.6.30]# cp php.ini-production /usr/local/php-fpm/etc/php.ini

配置文件编辑

[root@host php-5.6.30]# vi /usr/local/php/etc/php-fpm.conf   //写入如下内容

[global]
#定义全局参数
pid = /usr/local/php-fpm/var/run/php-fpm.pid
error_log = /usr/local/php-fpm/var/log/php-fpm.log
[www]
listen = /tmp/php-fcgi.sock
#监听地址，也可以写：listen = 127.0.0.1：:9000，本地监听，也可以监听其他IP：port
#此处格式会影响配置Nginx和PHP结合时Nginx寻址PHP的路径
listen.mode = 666
#当监听的为socket文件时该部分才生效，用于指定.sock文件的权限
user = php-fpm
group = php-fpm
#定义php-fpm服务的用户
pm = dynamic
pm.max_children = 50
pm.start_servers = 20
pm.min_spare_servers = 5
pm.max_spare_servers = 35
pm.max_requests = 500
rlimit_files = 1024
#以上部分为进程相关信息

配置启动脚本

[root@host etc]# cd /usr/local/src/php-5.6.30  //进入源码目录下来

[root@host php-5.6.30]#  cp sapi/fpm/init.d.php-fpm /etc/init.d/php-fpm  //把启动脚本放到到系统配置

[root@host php-5.6.30]# chmod 755 /etc/init.d/php-fpm  //修改权限

[root@host php-5.6.30]# chkconfig --add php-fpm  //添加到开机启动项

[root@host php-5.6.30]# chkconfig php-fpm on  //设置开机启动

[root@host php-5.6.30]# service php-fpm start  //启动php-fpm服务
Starting php-fpm  done

[root@host php-5.6.30]# ps aux |grep php-fpm  //查看后台www的pool是否启动

4. Nginx介绍

Nginx是一款轻量级的Web 服务器/反向代理服务器及电子邮件（IMAP/POP3）代理服务器，并在一个BSD-like 协议下发行。其特点是占有内存少，并发能力强，事实上nginx的并发能力确实在同类型的网页服务器中表现较好，中国大陆使用nginx网站用户有：百度、京东、新浪、网易、腾讯、淘宝等。

  • Nginx官网 nginx.org，最新版1.13，最新稳定版1.12
  • Nginx应用场景：web服务、反向代理、负载均衡
  • Nginx著名分支，淘宝基于Nginx开发的Tengine，使用上和Nginx一致，服务名，配置文件名都一样，和Nginx的最大区别在于Tenging增加了一些定制化模块，在安全限速方面表现突出，另外它支持对js，css合并
  • Nginx核心+lua相关的组件和模块组成了一个支持lua的高性能web容器openresty。

1、Nginx优点 Nginx设计为一个主进程多个工作进程的工作模式，每个进程是单线程来处理多个连接，而且每个工作进程采用了非阻塞I/O来处理多个连接，从而减少了线程上下文切换，从而实现了公认的高性能、高并发；因此在生成环境中会通过把CPU绑定给Nginx工作进程从而提升其性能；另外因为单线程工作模式的特点，内存占用就非常少了。 Nginx更改配置重启速度非常快，可以毫秒级，而且支持不停止Nginx进行升级Nginx版本、动态重载Nginx配置。 Nginx模块也是非常多，功能也很强劲，不仅可以作为http负载均衡，Nginx发布1.9.0版本还支持TCP负载均衡，还可以很容易的实现内容缓存、web服务器、反向代理、访问控制等功能。

2、Lua的优点 Lua是一种轻量级、可嵌入式的脚本语言，这样可以非常容易的嵌入到其他语言中使用。另外Lua提供了协程并发，即以同步调用的方式进行异步执行，从而实现并发，比起回调机制的并发来说代码更容易编写和理解，排查问题也会容易。Lua还提供了闭包机制，函数可以作为First Class Value 进行参数传递，另外其实现了标记清除垃圾收集。 因为Lua的小巧轻量级，可以在Nginx中嵌入Lua VM，请求的时候创建一个VM，请求结束的时候回收VM。

3、什么是ngx_lua ngx_lua是Nginx的一个模块，将Lua嵌入到Nginx中，从而可以使用Lua来编写脚本，这样就可以使用Lua编写应用脚本，部署到Nginx中运行，即Nginx变成了一个Web容器；这样开发人员就可以使用Lua语言开发高性能Web应用了。 ngx_lua提供了与Nginx交互的很多的API，对于开发人员来说只需要学习这些API就可以进行功能开发，而对于开发web应用来说，如果接触过Servlet的话，其开发和Servlet类似，无外乎就是知道接收请求、参数解析、功能处理、返回响应这几步的API是什么样子的。

4、开发环境 我们可以使用OpenResty来搭建开发环境，OpenResty将Nginx核心、LuaJIT、许多有用的Lua库和Nginx第三方模块打包在一起；这样开发人员只需要安装OpenResty，不需要了解Nginx核心和写复杂的C/C++模块就可以，只需要使用Lua语言进行Web应用开发了。

深入理解MySQL――锁、事务与并发控制

本文对锁、事务、并发控制做一个总结，看了网上很多文章，描述非常不准确。如有与您观点不一致，欢迎有理有据的拍砖！

本文对锁、事务、并发控制做一个总结，看了网上很多文章，描述非常不准确。如有与您观点不一致，欢迎有理有据的拍砖！

mysql服务器逻辑架构

未分类

每个连接都会在mysql服务端产生一个线程（内部通过线程池管理线程），比如一个select语句进入，mysql首先会在查询缓存中查找是否缓存了这个select的结果集，如果没有则继续执行 解析、优化、执行的过程；否则会之间从缓存中获取结果集。

mysql并发控制——共享锁、排他锁

共享锁

共享锁也称为读锁，读锁允许多个连接可以同一时刻并发的读取同一资源,互不干扰；

排他锁

排他锁也称为写锁，一个写锁会阻塞其他的写锁或读锁，保证同一时刻只有一个连接可以写入数据，同时防止其他用户对这个数据的读写。

锁策略

锁的开销是较为昂贵的，锁策略其实就是保证了线程安全的同时获取最大的性能之间的平衡策略。

mysql锁策略：talbe lock(表锁)

表锁是mysql最基本的锁策略，也是开销最小的锁，它会锁定整个表；

具体情况是：若一个用户正在执行写操作，会获取排他的“写锁”，这可能会锁定整个表，阻塞其他用户的读、写操作；

若一个用户正在执行读操作，会先获取共享锁“读锁”，这个锁运行其他读锁并发的对这个表进行读取，互不干扰。只要没有写锁的进入，读锁可以是并发读取统一资源的。

通常发生在DDL语句DML不走索引的语句中，比如这个DML update table set columnA=”A” where columnB=“B”.
如果columnB字段不存在索引（或者不是组合索引前缀），会锁住所有记录也就是锁表。如果语句的执行能够执行一个columnB字段的索引，那么会锁住满足where的行(行锁)。

mysql锁策略：row lock(行锁)

行锁可以最大限度的支持并发处理，当然也带来了最大开销，顾名思义，行锁的粒度实在每一条行数据。

事务

事务就是一组原子性的sql，或者说一个独立的工作单元。
事务就是说，要么mysql引擎会全部执行这一组sql语句，要么全部都不执行（比如其中一条语句失败的话）。

比如，tim要给bill转账100块钱：

  1. 检查tim的账户余额是否大于100块；
  2. tim的账户减少100块；
  3. bill的账户增加100块；

这三个操作就是一个事务，必须打包执行，要么全部成功，要么全部不执行，其中任何一个操作的失败都会导致所有三个操作“不执行”——回滚。

CREATE DATABASE IF NOT EXISTS employees; 
USE employees; 

CREATE TABLE `employees`.`account` ( 
 `id` BIGINT (11) NOT NULL AUTO_INCREMENT, 
 `p_name` VARCHAR (4), 
 `p_money` DECIMAL (10, 2) NOT NULL DEFAULT 0, 
 PRIMARY KEY (`id`) 
) ; 
INSERT INTO `employees`.`account` (`id`, `p_name`, `p_money`) VALUES ('1', 'tim', '200');  
INSERT INTO `employees`.`account` (`id`, `p_name`, `p_money`) VALUES ('2', 'bill', '200');  

START TRANSACTION; 
SELECT p_money FROM account WHERE p_name="tim";-- step1 
UPDATE account SET p_money=p_money-100 WHERE p_name="tim";-- step2 
UPDATE account SET p_money=p_money+100 WHERE p_name="bill";-- step3 
COMMIT;

一个良好的事务系统，必须满足ACID特点：

事务的ACID

A:atomiciy原子性 一个事务必须保证其中的操作要么全部执行，要么全部回滚，不可能存在只执行了一部分这种情况出现。

C:consistency一致性 数据必须保证从一种一致性的状态转换为另一种一致性状态。 比如上一个事务中执行了第二步时系统崩溃了，数据也不会出现bill的账户少了100块，但是tim的账户没变的情况。要么维持原装（全部回滚），要么bill少了100块同时tim多了100块，只有这两种一致性状态的

I：isolation隔离性 在一个事务未执行完毕时，通常会保证其他Session 无法看到这个事务的执行结果

D:durability持久性 事务一旦commit，则数据就会保存下来，即使提交完之后系统崩溃，数据也不会丢失。

隔离级别

未分类

查看系统隔离级别： select @@global.tx_isolation; 查看当前会话隔离级别 select @@tx_isolation; 设置当前会话隔离级别 SET session TRANSACTION ISOLATION LEVEL serializable; 设置全局系统隔离级别 SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

READ UNCOMMITTED(未提交读,可脏读)

事务中的修改，即使没有提交，对其他会话也是可见的。
可以读取未提交的数据——脏读。脏读会导致很多问题，一般不适用这个隔离级别。
实例：

-- ------------------------- read-uncommitted实例 ------------------------------ 
-- 设置全局系统隔离级别 
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
-- Session A 
START TRANSACTION; 
SELECT * FROM USER; 
UPDATE USER SET NAME="READ UNCOMMITTED"; 
-- commit; 

-- Session B 
SELECT * FROM USER; 

//SessionB Console 可以看到Session A未提交的事物处理，在另一个Session 中也看到了，这就是所谓的脏读 
id  name 
2   READ UNCOMMITTED 
34  READ UNCOMMITTED

READ COMMITTED(提交读或不可重复读，幻读)

一般数据库都默认使用这个隔离级别（mysql不是），这个隔离级别保证了一个事务如果没有完全成功（commit执行完），事务中的操作对其他会话是不可见的。

-- ------------------------- read-cmmitted实例 ------------------------------ 
-- 设置全局系统隔离级别 
SET GLOBAL TRANSACTION ISOLATION LEVEL READ  COMMITTED; 
-- Session A 
START TRANSACTION; 
SELECT * FROM USER; 
UPDATE USER SET NAME="READ COMMITTED"; 
-- COMMIT; 

-- Session B 
SELECT * FROM USER; 

//Console OUTPUT: 
id  name 
2   READ UNCOMMITTED 
34  READ UNCOMMITTED 


--------------------------------------------------- 
-- 当 Session  A执行了commit，Session B得到如下结果： 
id  name 
2   READ COMMITTED 
34  READ COMMITTED

也就验证了read committed级别在事物未完成commit操作之前修改的数据对其他Session 不可见，执行了commit之后才会对其他Session 可见。
我们可以看到Session B两次查询得到了不同的数据。

read committed隔离级别解决了脏读的问题，但是会对其他Session 产生两次不一致的读取结果（因为另一个Session 执行了事务，一致性变化）。

REPEATABLE READ(可重复读)

一个事务中多次执行统一读SQL,返回结果一样。
这个隔离级别解决了脏读的问题，幻读问题。这里指的是innodb的rr级别，innodb中使用next-key锁对”当前读”进行加锁，锁住行以及可能产生幻读的插入位置，阻止新的数据插入产生幻行。
下文中详细分析。

具体请参考mysql手册

https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html

SERIALIZABLE(可串行化)

最强的隔离级别，通过给事务中每次读取的行加锁，写加写锁，保证不产生幻读问题，但是会导致大量超时以及锁争用问题。

多版本并发控制-MVCC

MVCC(multiple-version-concurrency-control）是个行级锁的变种，它在普通读情况下避免了加锁操作，因此开销更低。
虽然实现不同，但通常都是实现非阻塞读，对于写操作只锁定必要的行。

一致性读 （就是读取快照） select * from table ….;

当前读(就是读取实际的持久化的数据) 特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁。 select * from table where ? lock in share mode; select * from table where ? for update; insert; update ; delete;

注意：select …… from where…… （没有额外加锁后缀）使用MVCC，保证了读快照(mysql称为consistent read)，所谓一致性读或者读快照就是读取当前事务开始之前的数据快照，在这个事务开始之后的更新不会被读到。详细情况下文select的详述。

对于加锁读SELECT with FOR UPDATE(排他锁) or LOCK IN SHARE MODE(共享锁)、update、delete语句，要考虑是否是唯一索引的等值查询。

写锁-recordLock,gapLock,next key lock

对于使用到唯一索引 等值查询：比如，where columnA=”…” ，如果columnA上的索引被使用到，
那么会在满足where的记录上加行锁(for update是排他锁,lock in shared 是共享锁，其他写操作加排他锁)。这里是行级锁，record lock。

对于范围查询（使用非唯一的索引）：
比如(做范围查询)：where columnA between 10 and 30 ,会导致其他会话中10以后的数据都无法插入(next key lock),从而解决了幻读问题。

这里是next key lock 会包括涉及到的所有行。
next key lock=recordLock+gapLock，不仅锁住相关数据，而且锁住边界，从而彻底避免幻读。

对于没有索引
锁表
通常发生在DDL语句DML不走索引的语句中，比如这个DML update table set columnA=”A” where columnB=“B”.
如果columnB字段不存在索引（或者不是组合索引前缀），会锁住所有记录也就是锁表。如果语句的执行能够执行一个columnB字段的索引，那么会锁住满足where的行(行锁)。

INNODB的MVCC通常是通过在每行数据后边保存两个隐藏的列来实现(其实是三列，第三列是用于事务回滚，此处略去)，
一个保存了行的创建版本号，另一个保存了行的更新版本号（上一次被更新数据的版本号）
这个版本号是每个事务的版本号，递增的。

这样保证了innodb对读操作不需要加锁也能保证正确读取数据。

MVCC select无锁操作 与 维护版本号

下边在mysql默认的Repeatable Read隔离级别下，具体看看MVCC操作：

Select（快照读，所谓读快照就是读取当前事务之前的数据。）： a.InnoDB只select查找版本号早于当前版本号的数据行，这样保证了读取的数据要么是在这个事务开始之前就已经commit了的（早于当前版本号），要么是在这个事务自身中执行创建操作的数据（等于当前版本号）。 b.查找行的更新版本号要么未定义，要么大于当前的版本号(为了保证事务可以读到老数据)，这样保证了事务读取到在当前事务开始之后未被更新的数据。 注意： 这里的select不能有for update、lock in share 语句。 总之要只返回满足以下条件的行数据，达到了快照读的效果：

(行创建版本号< =当前版本号 && (行更新版本号==null or 行更新版本号>当前版本号 ) )

Insert InnoDB为这个事务中新插入的行，保存当前事务版本号的行作为行的行创建版本号。

Delete InnoDB为每一个删除的行保存当前事务版本号，作为行的删除标记。

Update 将存在两条数据，保持当前版本号作为更新后的数据的新增版本号，同时保存当前版本号作为老数据行的更新版本号。

当前版本号—写—>新数据行创建版本号 && 当前版本号—写—>老数据更新版本号();

脏读 vs 幻读 vs 不可重复读

脏读：一事务未提交的中间状态的更新数据 被其他会话读取到。 当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有 提交到数据库中(commit未执行)，这时，另外会话也访问这个数据，因为这个数据是还没有提交， 那么另外一个会话读到的这个数据是脏数据，依据脏数据所做的操作也可能是不正确的。

不可重复读：简单来说就是在一个事务中读取的数据可能产生变化，ReadCommitted也称为不可重复读。

在同一事务中，多次读取同一数据返回的结果有所不同。换句话说就是，后续读取可以读到另一会话事务已提交的更新数据。 相反，“可重复读”在同一事务中多次读取数据时，能够保证所读数据一样，也就是，后续读取不能读到另一会话事务已提交的更新数据。

幻读：会话T1事务中执行一次查询，然后会话T2新插入一行记录，这行记录恰好可以满足T1所使用的查询的条件。然后T1又使用相同 的查询再次对表进行检索，但是此时却看到了事务T2刚才插入的新行。这个新行就称为“幻像”，因为对T1来说这一行就像突然 出现的一样。
innoDB的RR级别无法做到完全避免幻读，下文详细分析。

----------------------------------前置准备---------------------------------------- 
prerequisite： 
-- 创建表 
mysql> 
CREATE TABLE `t_bitfly` ( 
  `id` bigint(20) NOT NULL DEFAULT '0', 
  `value` varchar(32) DEFAULT NULL, 
  PRIMARY KEY (`id`) 
) 

-- 确保当前隔离级别为默认的RR级别 

mysql> select @@global.tx_isolation, @@tx_isolation; 
+-----------------------+-----------------+ 
| @@global.tx_isolation | @@tx_isolation  | 
+-----------------------+-----------------+ 
| REPEATABLE-READ       | REPEATABLE-READ | 
+-----------------------+-----------------+ 
1 row in set (0.00 sec) 
---------------------------------------开始---------------------------------------------  


session A                                           |   session B 
                                                   | 
                                                   | 
mysql> START TRANSACTION;                           |   mysql> START TRANSACTION; 
Query OK, 0 rows affected (0.00 sec)                |   Query OK, 0 rows affected (0.00 sec)                                         
                                                    |    
                                                   | 
mysql> SELECT * FROM test.t_bitfly;                 |   mysql> SELECT * FROM test.t_bitfly;  
Empty set (0.00 sec)                                |   Empty set (0.00 sec) 
                                                   | 
                                                   |   mysql> INSERT INTO t_bitfly VALUES (1, 'test'); 
                                                    |   Query OK, 1 row affected (0.00 sec) 
                                                   | 
                                                   | 
mysql> SELECT * FROM test.t_bitfly;                 | 
Empty set (0.00 sec)                                | 
                                                   | 
                                                   |   mysql> commit; 
                                                   |   Query OK, 0 rows affected (0.01 sec)                                                 
mysql> SELECT * FROM test.t_bitfly;                 | 
Empty set (0.00 sec)                                | 
-- 可以看到虽然两次执行结果返回的数据一致，         | 
-- 但是不能说明没有幻读。接着看：                   | 
                                                   | 
mysql> INSERT INTO t_bitfly VALUES (1, 'test');     | 
ERROR 1062 (23000):                                 | 
Duplicate entry '1' for key 'PRIMARY'               | 
                                                   | 
-- 明明为空的表，为什么说主键重复？——幻读出现 ！！！       |

如何保证rr级别绝对不产生幻读？

在使用的select …where语句中加入 for update(排他锁) 或者 lock in share mode(共享锁)语句来实现。其实就是锁住了可能造成幻读的数据，阻止数据的写入操作。

其实是因为数据的写入操作(insert 、update)需要先获取写锁，由于可能产生幻读的部分，已经获取到了某种锁，所以要在另外一个会话中获取写锁的前提是当前会话中释放所有因加锁语句产生的锁。

mysql死锁问题

死锁，就是产生了循环等待链条，我等待你的资源，你却等待我的资源，我们都相互等待，谁也不释放自己占有的资源，导致无线等待下去。
比如：

//Session A 
START TRANSACTION; 
UPDATE account SET p_money=p_money-100 WHERE p_name="tim"; 
UPDATE account SET p_money=p_money+100 WHERE p_name="bill"; 
COMMIT; 
//Thread B 
START TRANSACTION; 
UPDATE account SET p_money=p_money+100 WHERE p_name="bill"; 
UPDATE account SET p_money=p_money-100 WHERE p_name="tim"; 
COMMIT;

当线程A执行到第一条语句UPDATE account SET p_money=p_money-100 WHERE p_name=”tim”;锁定了p_name=”tim”的行数据；并且试图获取p_name=”bill”的数据；

，此时，恰好，线程B也执行到第一条语句：UPDATE account SET p_money=p_money+100 WHERE p_name=”bill”;

锁定了 p_name=”bill”的数据，同时试图获取p_name=”tim”的数据；
此时，两个线程就进入了死锁，谁也无法获取自己想要获取的资源，进入无线等待中，直到超时！

innodb_lock_wait_timeout 等待锁超时回滚事务：
直观方法是在两个事务相互等待时，当一个等待时间超过设置的某一阀值时，对其中一个事务进行回滚，另一个事务就能继续执行。这种方法简单有效，在innodb中，参数innodb_lock_wait_timeout用来设置超时时间。

wait-for graph算法来主动进行死锁检测：
innodb还提供了wait-for graph算法来主动进行死锁检测，每当加锁请求无法立即满足需要并进入等待时，wait-for graph算法都会被触发。

如何尽可能避免死锁

1）以固定的顺序访问表和行。比如两个更新数据的事务，事务A 更新数据的顺序 为1，2；事务B更新数据的顺序为2，1。这样更可能会造成死锁。

2）大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。

3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。

4）降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。

5）为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。

显式锁 与 隐式锁
隐式锁:我们上文说的锁都属于不需要额外语句加锁的隐式锁。
显示锁：

SELECT … LOCK IN SHARE MODE(加共享锁); SELECT … FOR UPDATE(加排他锁);

详情上文已经说过。

通过如下sql可以查看等待锁的情况

select * from information_schema.innodb_trx where trx_state="lock wait";

或

show engine innodb status;

mysql中的事务

show variables like "autocommit"; 

set autocommit=0; //0表示AutoCommit关闭 
set autocommit=1; //1表示AutoCommit开启

自动提交（AutoCommit，mysql默认）

mysql默认采用AutoCommit模式，也就是每个sql都是一个事务，并不需要显示的执行事务。

如果autoCommit关闭，那么每个sql都默认开启一个事务，只有显式的执行“commit”后这个事务才会被提交。

Mysql设置编码方式及基本操作

介绍

Mysql 默认安装后的编码方式默认一般是Latin, 在插入汉字数据或读取的时候，存在乱码或报错。这时候，只需要修改编码方式为UTF8，统一数据库和数据表的编码方式。

如何修改

1、 复制MySQL数据库中的配置文件，然后粘贴重命名为my.ini

2、 在文件中添加以下语句。

[mysqld]
Character-set-server = utf8
[client]
Default-character-set = utf8
[mysql]
Default-character-set = utf8

3、 重启查看

Net stop mysql;
Net start mysql;
Mysql -u root -p 
show variables like "% character %";

基本操作

1、 查看编码格式

show variables like 'character_set_database';
show create table <表名>;

2、 指定编码格式

create database <数据库名> character set utf8;
create table tb_books (
    name varchar(45) not null,
    price double not null,
    bookCount int not null,
    author varchar(45) not null ) default charset = utf8;

3、 修改编码格式

alter database <数据库名> character set utf8;
alter table <表名> character set utf8;
alter table <表名> change <字段名> <字段名> <类型> character set utf8;

4、 外键操作

alter table <表名> add constraint <外键名> foreign key<字段名> REFERENCES <外表表名><字段名>;
alter table <表名> drop foreign key <外键名>;

5、 增删改查

Select * from user where name = ‘Jachin’;
Delete  from user where name = ‘Jachin’;
Update user set name = ‘Jachin’ where name = ‘Wang’;
Insert into user (name) values (‘Jachin’);

Linux下配置mysql数据库主从自动备份

当网站内容越来越多的时候，数据库也变得越来越大了。如果不小心误删了，或者被攻击了，那就什么都没有了，一切归零。到现在博主也认识到了数据库的重要性，于是决定研究研究数据库的自动备份。如果你是土豪那可以直接买各种云服务商的数据库，自带容灾备份的，安全性比较好。

本文仅讨论数据库放在本地的情况，备份方式为主从：即一台服务器作为主服务器，另外一台服务器作为备份服务器，当主服务器故障时，可以通过备份服务器来接管/恢复。

先来看看数据库主备备份的原理：

MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步的

MySQL主从是基于binlog的，主上须开启binlog才能进行主从。 主从过程大致有3个步骤 

1）主将更改操作记录到binlog里 

2）从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里 

3）从根据relaylog里面的sql语句按顺序执行

主上有一个log dump线程，用来和从的I/O线程传递binlog

从上有两个线程，其中I/O线程用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的sql语句执行一遍

两种情况：一种是做备份用，一种是作为读用

简单的说就是master将数据库的改变写入二进制日志，slave同步这些二进制日志，并根据这些二进制日志进行数据操作。

环境介绍：

本次配置的环境为：centos7，使用LNMP1.4安装的Mysql5.5，主服务器为AWS服务器（下面简称主服务器），从服务器为京东云服务器（下面简称从服务器）,现在需要将主服务器中名为img的数据库做主从同步，自动同步到从服务器上。

准备工具：xshell

一、复制数据库

1、首先要将主服务器的数据库复制到从服务器中。

用xshell连接主服务器，导出数据库：

mysqldump -uroot -p img >/root/img.sql
---输入数据库密码---

然后ls看下，可以看到一个 img.sql的文件。

2、用xshell连接从服务器，使用sftp命令远程到主服务器（有提示输入yes）：

sftp root@主服务器IP地址
---输入ssh密码---
cd /root

然后ls看下，可以看到主服务器的目录，里面有个img.sql的文件，执行：

get img.sql

这样数据库文件就被复制到从服务器上了。 exit 退出sftp。

3、接下来在从服务器上建立一个名为img的数据库，排序规则设为uft8

mysql -uroot -p
---输入数据库密码---
create database img;
use img;
set names utf8;
source img.sql

这样就成功导入了，exit退出回到linux命令行。

二、主服务器配置

1、切回主服务器，用vim 编辑 /etc下的my.cnf，如图

未分类

这里主要配置的是server-id和log-bin。server-id用于区分不同主机上的数据库，log-bin是二进制文件的名字（可以随意命名）。同时，为了保证只同步需要的数据库，我们需要加上：

binlog-do-db=img
binlog-ignore-db=mysql

配置完成如图：

未分类

然后 :wq 保存退出，重启mysql：

service mysql restart

未分类

2、连接数据库进行如下操作：

mysql -uroot -p img
---输入数据库密码---
#以下为修改数据库连接权限
grant all privileges on *.* to 'root'@'%' identified by '自定义密码';
flush privileges;
#下面为显示bin-log文件名和位置
show master status;

执行完最后一条后我们可以看到：

未分类

记下红框处的文件名和数字。

三、从服务器配置

1、连接到从服务器，用vim修改/etc下的my.cnf文件，把server-id改为2。

重启mysql服务：

service mysql restart

2、连接数据库，配置从数据库：

mysql -uroot -p
---输入数据库密码---
stop slave;
change master to
master_user='root',
master_password='你设置的自定义密码',
master_host='主服务器地址'，
master_log_file='mysql-bin.000011',   #记下的上图红框的文件名
master_log_pos=255;   #记下的上图红框的位置
start slave;
show slave statusG   #查看是否安装成功

如图，这两个是YES就成功了

未分类

四、其他说明

现在只要主服务器上的数据库有变动，从服务器上也会时时变动，达到了数据库异地容灾备份的功能。如果需要多服务器备份，那么可以把多个服务器中my.cnf中的server_id修改为不同，并按以上教程重新配置一遍，每个服务器的数据库可做主服务器也可以做从服务器。

如要同步多个数据库，可在主服务器的my.cnf 中，添加多条 binlog-do-db，指向多个数据库。

MariaDB/MySQL中的变量

在MySQL/MariaDB中有好几种变量类型：用户自定义变量、系统变量、一般的临时变量(即本地变量，或称为局部变量)。

1. 用户变量

用户变量是基于会话的，也是基于用户的，所以我觉得称之为会话变量更合适，但会话变量一般用来表示系统会话变量(后面会说明)，所以还是称之为用户变量好了。

只有本用户才能引用自身的用户变量，其他用户无法引用，且当用户退出会话时，用户变量自动销毁。

用户变量使用”@”开头，用户变量可以直接赋值，无需事先声明。在引用未赋值的用户变量时，该变量值为null。

有以下三种方式设置用户变量：

  • set语句，此时可以使用”=”或者”:=”操作符;
  • select语句，此时只能使用”:=”格式赋值，因为除了set语句中，”=”都会被视为比较操作符。;
  • select … into var_name from TABLE语句，此时要求select语句只能返回标量值，即单行数据。因此为了保险，select into var_name的时候，应尽量结合limit语句限制输出。
set @a1=1,@a2=3,@a3:=2;
select @a4:=@a1+@a3;
select 33,'abc' into @a5,@a6 from dual;

查看变量值可以使用select语句。

MariaDB [test]> select @a1,@a2,@a3,@a4,@a5,@a6,@a7;
+------+------+------+------+------+------+------+
| @a1  | @a2  | @a3  | @a4  | @a5  | @a6  | @a7  |
+------+------+------+------+------+------+------+
|    1 |    2 |    3 |    4 |    5 | abc  | NULL |
+------+------+------+------+------+------+------+

在mariadb 10.2.6中，引入了一张系统架构表information_schema.USER_VARIABLES，该表中记录了当前用户当前会话定义的用户变量信息。该信息架构表在mysql中没有。

MariaDB [test]> SELECT * FROM information_schema.USER_VARIABLES;           
+---------------+----------------+---------------+--------------------+
| VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_TYPE | CHARACTER_SET_NAME |
+---------------+----------------+---------------+--------------------+
| a6            | abc            | VARCHAR       | utf8               |
| i             | 2              | INT           | utf8               |
| a5            | 33             | INT           | utf8               |
| a1            | 1              | INT           | utf8               |
| a4            | 3              | INT           | utf8               |
| a2            | 3              | INT           | utf8               |
| a3            | 2              | INT           | utf8               |
+---------------+----------------+---------------+--------------------+

2. 系统变量

在MySQL/mariadb中维护两种系统变量：全局系统变量和会话系统变量。系统变量是用来设置MySQL服务运行属性和状态的。

全局系统变量使用global或者”@@global.”关键字来设置。会话系统变量使用session或者”@@session.”关键字来设置，其中session可以替换为Local，它们是同义词。如果省略这些关键字，则默认为session系统变量。设置global系统变量要求具有super权限。

-- 设置全局系统变量
set global sort_buffer_size=32M;
set @@global.sort_buffer_size=32M;
-- 设置会话系统变量
set session sort_buffer_size=32M;
set @@session.sort_buffer_size=32M;
set sort_buffer_size=32M;
-- 查看全局系统变量值
select @@global.sort_buffer_size;
show global variables like "sort_buffer%";
-- 查看会话系统变量，不能使用select sort_buffer_size
select @@session.sort_buffer_size;
select @@sort_buffer_size;
show [session] variables like "sort_buffer%";
-- 一次性设置多个变量，包括会话变量、全局变量以及用户变量
SET @x = 1, SESSION sql_mode = '';
SET GLOBAL sort_buffer_size = 1000000, SESSION sort_buffer_size = 1000000;
SET @@global.sort_buffer_size = 1000000, @@local.sort_buffer_size = 1000000;
SET GLOBAL max_connections = 1000, sort_buffer_size = 1000000;

全局系统变量对全局有效，当有新的会话打开时，新会话会继承全局系统变量的值，所以设置全局系统变量之后新打开的会话都会继承设置后的值。设置全局系统变量对已经打开的连接无效，但是其他已经打开的连接可以查看到设置后的全局系统变量值。

系统变量按照是否允许在运行时修改，还分为动态变量和静态变量。能在运行过程中修改的变量称为动态变量，只能在数据库实例关闭状态下修改的变量称为静态变量或只读变量。动态变量使用set修改。如果在数据库实例运行状态下修改静态变量，则会给出错误。如：

set @@innodb_undo_tablespaces=3;
ERROR 1238 (HY000): Variable 'innodb_undo_tablespaces' is a read only variable

系统变量除了可以在运行中的环境下设置，还可以在配置文件中或者mysqld/mysqld_safe这样的命令行中设置，甚至mysql客户端命令行也可以传递。在配置文件中设置系统变量时，下划线或者短横线都允许，它们表示同一个意思。例如下面的两行配置是等价的：

innodb_file_per_table=1
innodb-file-per-table=1

3. 局部变量

局部变量也称为本地变量，只能在begin…and语句块中生效。它不像用户变量，本地变量必须使用declare事先声明，所以declare也必须在begin…end中使用。

局部变量无论是声明还是调用的时候都不需要任何多余的符号(即不需要@符号)，直接使用其名称var_name即可。

使用declare声明变量，可以一次性声明多个同类型的变量，需要时可有直接为其指定默认值，不指定时默认为null。

decalre var_name,... type [default value];

使用set为变量赋值。MySQL/mariadb中set支持一次性赋值多个变量。

在begin…end中的set是一般set语句的扩展版本，它既可以设置系统变量、用户变量，也可以设置此处的本地变量。

set var_name=expr,[var_name=expr1,...]

或者使用select…into语句从表中获取值来赋值给变量，但是这样的赋值行为要求表的返回结果必须是单列且单行的标量结果。例如下面的语句将col的列值赋值给var_name变量。

select col into var_name from table_name;

因为局部变量只能在begin…end中使用，所以此处使用存储过程的例子来演示。

DROP PROCEDURE IF EXISTS haha;
DELIMITER $$
CREATE PROCEDURE haha()
BEGIN
    DECLARE a INT;
    SET a=1;
    SET @i:=2;
    SELECT a,@i;
END$$
DELIMITER ;

CALL haha();
     a        @i
------  --------
     1         2

在MySQL中，begin…end只能定义在存储程序中，所以declare也只能定义在存储程序内。但在mariadb中，begin…end是允许定义在存储程序(存储函数，存储过程，触发器，事件)之外的，所以decalre也算是能够定义在存储程序之外吧。需要定义在存储程序之外时，使用 begin not atomic 关键字即可。例如：

delimiter $$ 
begin not atomic
    declare a int;
    set a=3;
    select a;
end$$

3.1 declare锚定其他对象的数据类型

在mariadb 10.3中(注意版本号，目前10.3版本还在测试中)，declare语句允许在存储程序中使用TYPE OF和ROW TYPE OF 关键字基于表或游标来锚定数据类型。在mysql中不支持数据类型的锚定功能。

例如：

DECLARE tmp TYPE OF t1.a; -- 基于表t1中的a列获取数据类型
DECLARE rec1 ROW TYPE OF t1; -- 锚定表t1中行数据类型
DECLARE rec2 ROW TYPE OF cur1; -- 基于游标cur1获取行数据类型

通过其他对象来锚定本地变量的数据类型时，如果对象的数据类型改变，则本地数据类型也随之改变。这在某些时候非常有利于维护存储程序。

在定义存储程序时，不会检查declare锚定的对象是否存在。但在调用存储程序时，会先检查锚定对象是否存在。

当declare语句的锚定是基于表对象(不是游标)时，在调用存储程序的瞬间就会检查锚定的表是否存在，然后立刻声明该变量。因此:

  • (1).带有锚定功能的decalre语句可以定义在存储程序的任意位置；
  • (2).在存储程序中删除锚定的表对象，或者修改了锚定的表结构，都不会改变存储程序调用时声明的变量类型；
  • (3).所有带锚定功能的declare都是在存储程序调用之初被赋值的。

当declare语句的锚定是基于游标对象时，变量的数据类型是在执行变量声明语句时才获取到的。数据类型仅只锚定一次，之后不再改变。如果游标中的ROW TYPE OF变量是定义在一个循环之中，则数据类型在循环的开头就已经获取，且之后的循环不再改变。

示例：

create table t1(a int,b char(20));
drop procedure if exists haha;
delimiter $$
create procedure haha()
begin
    declare x type of t1.a;
    set x=1;
    select x;
end$$
delimiter ;

call haha();

如何在Ubuntu上安装MySQL / MariaDB

本教程面向Ubuntu服务器，适用于Ubuntu的任何LTS版本，包括Ubuntu 14.04，Ubuntu 16.04，Ubuntu 18.04，甚至非LTS版本（如Ubuntu 17.10和其他基于Ubuntu的发行版）。我们在Ubuntu 16.04服务器上测试了这个。

在我们开始安装MySQL / MariaDB之前

一些要求和其他说明：

当涉及LAMP堆栈中的基本用法时，MySQL和MariaDB几乎完全相同。大多数命令是相同的，即使安装也是如此。选择一个并将其安装到您的LAMP堆栈中，我们将包含两者的说明。

你需要一个Ubuntu服务器来运行MySQL/MariaDB。

您需要root用户或具有sudo访问权限的用户访问服务器。以下命令全部由root用户执行，所以我们不必为每个命令添加’sudo’。如果您使用非root用户，则可能必须执行此操作。
如果您使用的是Windows，则需要启用SSH，如果您使用Ubuntu或MobaXterm等SSH客户端。

MySQL / MariaDB可能已经安装在您的服务器上。您可以通过输入“mysql”或“mariadb”来检查它们是否已安装，并且您应该根据输出结果来了解它们。

现在就是这样。 我们来看看我们的教程。

如何在Ubuntu上安装MySQL

我们将从MySQL开始。 如果您想安装MariaDB，请跳至MariaDB说明。

更新Ubuntu
首先，像往常一样，在你做任何事之前，通过运行以下命令来更新你的Ubuntu服务器：

apt-get update && apt-get upgrade

安装MySQL
然后，通过运行以下命令安装MySQL：

apt-get install mysql-server

该命令将安装MySQL服务器和客户端。 你会得到一个提示，为你的root用户输入一个密码。

而已。 MySQL已安装。 现在，您需要保护并配置它。

安全的MySQL
你应该运行mysql_secure_installation脚本来帮助你保护你的MySQL。

使用以下命令启动脚本：

mysql_secure_installation

并回应提示。 您可以使用每个提示的默认响应。

优化MySQL（仅限高级用户）
要优化你的MySQL，你可以使用MySQLTuner脚本。 它不会为你做所有的工作。 该脚本仅向您提供如何改进和优化MySQL的建议。

使用以下命令下载并运行脚本：

curl -L http://mysqltuner.pl/ | perl

并检查建议。 做一些研究和使用谷歌每个建议。 如果你不知道自己在做什么，请联系其他人，让他们为你做，或者跳过这个。

你也可以使用mysqlcheck来修复你的数据库。 您可以使用单个命令修复所有数据库：

mysqlcheck -A –auto-repair -u root -p

还有其他的优化可以在你的服务器和数据库上完成，如果你想进一步优化MySQL，你也可以自己做一些研究。

如何在Ubuntu上安装MariaDB

现在来看我们的MariaDB安装说明。

更新Ubuntu
首先，更新你的Ubuntu服务器：

apt-get update && apt-get upgrade

添加MariaDB存储库
在您可以安装MariaDB之前，您需要添加MariaDB存储库。

根据您的Ubuntu发行版，您可能需要运行不同的命令，因此请前往官方的MariaDB存储库页面，选择您的发行版并选择离您的服务器位置最近的镜像。 然后，复制你在页面上得到的命令。 我们使用Ubuntu 16.04.1并选择了美国镜像，因此我们将运行以下命令来添加存储库：

apt-get install software-properties-common
apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
add-apt-repository 'deb [arch=amd64,i386,ppc64el] https://mirrors.evowise.com/mariadb/repo/10.1/ubuntu xenial main'

接下来，您需要再次更新您的软件包列表：

apt-get update

并转到安装MariaDB。

安装MariaDB
添加MariaDB存储库后，可以通过运行以下命令来安装它：

apt-get install mariadb-server

就是这样。 你已经在服务器上安装了MariaDB。

保护MariaDB
这与MySQL的过程相同。 使用以下命令运行安全脚本：

mysql_secure_installation

并按照提示进行操作。 您可以输入每个提示的默认值。 当然，使用强密码。

优化MariaDB（仅限高级用户）
同样，与MySQL相同，您可以使用MySQLTuner检查您的MariaDB并获取有关如何改进它的建议。 它不会为你做所有的工作。 该脚本仅向您提供如何改进和优化MariaDB的建议。

运行脚本：

curl -L http://mysqltuner.pl/ | perl

并检查建议。 做一些研究和使用谷歌每个建议。 如果你不知道自己在做什么，请联系其他人，让他们为你做，或者跳过这个。

Mysqlcheck也适用于MariaDB，因此要一次优化所有MariaDB数据库，请运行以下命令：

mysqlcheck -A --auto-repair -u root -p

您可以在服务器和数据库上进行其他优化，因此如果您想进一步优化MariaDB，请自行研究.

Mysql/Mariadb备份(xtrabackup)还原实战

一、概述

之前的文章说到mysql的安装与mysql的基本使用;本文是后续补充,主要说明针对mysql或mariadb的备份与还原;众所周知,数据是重中之重,因此平时对企业数据需要做备份,当数据系统崩溃,数据丢失异常时,才能依据备份文件进行恢复!

本次的环境:
CentOS7.4_x64 , mysql5.7.21, xtrabackup

mysql的安装配置可参考之前系列文章;只补充相关配置项的开启;以及xtrabackup安装使用;
用到的演示数据导入mysql数据库

[root@db ~]# mysql -uroot -predhat < testdb.sql
或
mysql> source   testdb.sql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| example            |
| mysql              |
| performance_schema |
| study              |
| sys                |
以上study即为测试数据库包含以下测试表

mysql> show tables;
+-----------------+
| Tables_in_study |
+-----------------+
| class           |
| course          |
| part            |
| score           |
| student         |
| tb31            |
| tb32            |
| teacher         |
| test1           |
| test2           |
| user_info       |
+-----------------+

测试数据库及数据表准备完成,在进行数据的备份与恢复前,我们先简单了解下数据库备份与恢复的相关概念原理;

关于数据库的备份与还原

为什么备份？
主要是为了灾难恢复如:硬件故障（冗余）、软件故障（bug）、自然灾害、黑客攻击、误操作、以及测试需要导出数据等;
还原或叫恢复时即基于以往的备份文件;

备份类型

全量备份、增量备份、差异备份：
完全备份: 备份数据的副本(某时间点);
增量备份：仅备份自上一次完全备份或 增量备份以来变量的那部数据；
差异备份：仅备份自上一次完全备份以来变量的那部数据；

物理备份、逻辑备份：
物理备份：复制数据文件进行的备份；
逻辑备份：从数据库导出数据另存在一个或多个文件中；

根据数据服务是否在线：
热备：读写操作均可进行的状态下所做的备份；
温备：可读但不可写状态下进行的备份；
冷备：读写操作均不可进行的状态下所做的备份
以上的各备份类型备份执行时只能备份数据在备份时的状态,如想要恢复数据库崩溃那一刻的状态,需要打开binary log功能,需要基于备份的数据+binary log来恢复到数据崩溃前一刻的状态;
备份的工具有mysqldump(温备,不适合大型数据的在线备份),xtrabackup(支持对InnoDB热备,开源专业的备份数据,支持mysql/mariadb)本文将通过mysqldump与xtrabackup来说明数据的备份与恢复(异地);

无论那种工具备份,在恢复时均要binary log才能恢复到崩溃前的状态;因此需要配置数据库开启binary log功能;以下能mysql5.7.21

#cat /usr/local/mysql/etc/my.cnf
server-id       = 1
log_bin         = /data1/mysqldb/mysql-bin.log

二、mysqldump备份与恢复

mysqldump使用说明

单进程逻辑备份、完全备份、部分备份；

Usage: 
mysqldump [OPTIONS] database [tables]
         OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
         OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

mysqldump mydb：表级别备份(还原时库需要存在)
mysqldump --databases mydb：库级别备份(库不在会自行创建库)

MyISAM存储引擎：支持温备，备份时要锁定表；
         -x, --lock-all-tables：锁定所有库的所有表，读锁；
         -l, --lock-tables：锁定指定库所有表；

InnoDB存储引擎：支持温备和热备；
         -x, --lock-all-tables：锁定所有库的所有表，读锁；
         -l, --lock-tables：锁定指定库所有表；
        --single-transaction：创建一个事务，基于此快照执行备份；
                -R, --routines：存储过程和存储函数；
                --triggers      触发器
                -E, --events      事件

                 --master-data[=#]
                        1：记录为CHANGE MASTER TO语句，此语句不被注释；
                        2：记录为CHANGE MASTER TO语句，此语句被注释；

                --flush-logs：锁定表完成后，即进行日志刷新操作(重新生成binlog日志)；

基于mysqldump备份study数据库

热备,备份存储过程和存储函数,事件,并记得下事件位置;(便于从binlog中的位置开始恢复到故障前)
#mysqldump -uroot -predhat --single-transaction  -R -E --triggers --master-data=2 --databases study >/home/san/studydb.sql

说明:

less studydb.sql

会看到以下内容

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=154;

这就是–master-data=2 选项作用,注释了,binary log 点在154

模拟备份后数据修改操作

修改前的:
mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name  | age  | gender | part_nid |
+-----+-------+------+--------+----------+
|   1 | san   |   20 | 男     |        1 |
|   2 | dong  |   29 | 男     |        2 |
|   4 | Ling  |   28 | 男     |        4 |
|   5 | ling  |   28 | 男     |        3 |
|   6 | dong  |   30 | 男     |        1 |
|   7 | b     |   11 | 女     |        1 |
|   8 | c     |   12 | 女     |        1 |
|   9 | d     |   18 | 女     |        4 |
|  10 | e     |   22 | 男     |        3 |
|  11 | f     |   23 | 男     |        2 |
|  12 | dongy |   22 | 男     |        1 |
+-----+-------+------+--------+----------+
11 rows in set (0.00 sec)
增加一条:
mysql> insert into user_info values(13,'hi',18,'男',4);
Query OK, 1 row affected (0.03 sec)
删除一条:
mysql> delete  from user_info where nid=1;
Query OK, 1 row affected (0.01 sec)
最终在上次备份后user_info数据如下:
mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name  | age  | gender | part_nid |
+-----+-------+------+--------+----------+
|   2 | dong  |   29 | 男     |        2 |
|   4 | Ling  |   28 | 男     |        4 |
|   5 | ling  |   28 | 男     |        3 |
|   6 | dong  |   30 | 男     |        1 |
|   7 | b     |   11 | 女     |        1 |
|   8 | c     |   12 | 女     |        1 |
|   9 | d     |   18 | 女     |        4 |
|  10 | e     |   22 | 男     |        3 |
|  11 | f     |   23 | 男     |        2 |
|  12 | dongy |   22 | 男     |        1 |
|  13 | hi    |   18 | 男     |        4 |
+-----+-------+------+--------+----------+
11 rows in set (0.00 sec)
可以看出少了一条,加了一条;

模拟数据库损坏并恢复study数据库

关闭mysql并到数据目录删除study数据库;

假设发现study数据已经丢失了;

数据库运行正常;查看binlog位置
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       815 |
| mysql-bin.000002 |       177 |
| mysql-bin.000003 |       177 |
| mysql-bin.000004 |   1890875 |
| mysql-bin.000005 |       725 |
+------------------+-----------+
记住这里最后一个binlog文件及位置是mysql-bin.000005   725
结合上面备份文件中的-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=154; 可以分析出备份时位置是154而数据库丢失前是725
因此我们恢复study数据库里需要恢复上次的全备+加mysql-bin.000005中的154-725内容;

模拟study丢失过程(传说溃的删库路)
[root@db mysqldb]# service stop mysqld
[root@db mysqldb]# pwd
/data1/mysqldb
[root@db mysqldb]# rm -rf  study/
启动数据库
[root@db mysqldb]# service stop mysqld
登录数据库并查看发现study数据库已经丢失了

还原数据库

mysql -uroot -predhat < studydb.sql
mysql> show databases;
可发现已经恢复;但是之前完整备份的到崩溃前的修改不见了;如下:
mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name  | age  | gender | part_nid |
+-----+-------+------+--------+----------+
|   1 | san   |   20 | 男     |        1 |
|   2 | dong  |   29 | 男     |        2 |
|   4 | Ling  |   28 | 男     |        4 |
|   5 | ling  |   28 | 男     |        3 |
|   6 | dong  |   30 | 男     |        1 |
|   7 | b     |   11 | 女     |        1 |
|   8 | c     |   12 | 女     |        1 |
|   9 | d     |   18 | 女     |        4 |
|  10 | e     |   22 | 男     |        3 |
|  11 | f     |   23 | 男     |        2 |
|  12 | dongy |   22 | 男     |        1 |
+-----+-------+------+--------+----------+
11 rows in set (0.00 sec)

结合binlog恢复:

从binlog上导出sql文件
[root@db mysqldb]# mysqlbinlog mysql-bin.000005 >/root/binlog.sql
登录mysql恢复
恢复过程中临时关闭binlog记录
mysql> set @@session.sql_log_bin=OFF;
mysql> source binlog.sql;
Query OK, 0 rows affected (0.00 sec

mysql> set @@session.sql_log_bin=ON;
mysql> use study; 
mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name  | age  | gender | part_nid |
+-----+-------+------+--------+----------+
|   2 | dong  |   29 | 男     |        2 |
|   4 | Ling  |   28 | 男     |        4 |
|   5 | ling  |   28 | 男     |        3 |
|   6 | dong  |   30 | 男     |        1 |
|   7 | b     |   11 | 女     |        1 |
|   8 | c     |   12 | 女     |        1 |
|   9 | d     |   18 | 女     |        4 |
|  10 | e     |   22 | 男     |        3 |
|  11 | f     |   23 | 男     |        2 |
|  12 | dongy |   22 | 男     |        1 |
|  13 | hi    |   18 | 男     |        4 |
+-----+-------+------+--------+----------+
11 rows in set (0.00 sec)

可以看出study数据库已经恢复到崩溃损坏前的状态;另外完全 可以新准备一台数据库服务器;把sql转移到新机器上恢复;前提数据配置参数需要一样;

三、xtrabackup备份与恢复

xtrabackup简介

xtrabackup是Percona一款开源工具,支持innodb,Xtradb(mariadb)引擎数据库的热备;
对MyISAM：温备，不支持增量备份;InnoDB：热备，增量；
物理备份，速率快、可靠；备份完成后自动校验备份结果集是否可用；还原速度快
功能介绍与Innobackup(mysql企业版收费)对比参考官网
所数据库引擎请使用innodb引擎

xtrabackup安装与使用说明

安装
[官方下载地址](https://www.percona.com/downloads/XtraBackup/LATEST/)
本次使用percona-xtrabackup-24-2.4.8-1
[root@db ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.8/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.8-1.el7.x86_64.rpm
[root@db ~]# yum install ./percona-xtrabackup-24-2.4.8-1.el7.x86_64.rpm -y

**获取帮助与使用:**
可以通过man  xtrabackup 获取详细使用说明与实例
Usage: 
 innobackupex [--defaults-file=#] --backup | innobackupex [--defaults-file=#] --prepare] [OPTIONS]
 备份用到的主要选项:
    --defaults-file=     #mysql或mariadb配置文件
        --user=                  #备份时使用的用户(对备份的数据库有备份权限)
        --password=         #备份用户密码
        -H | --host=            #localhost或远程主机

**恢复时到的主要选项:**
        --apply-log         #分析获取binary log文件生成backup_binlog_info文件
        ---copy-back      #基于backup_binlog_info等文件恢复
注:innobackupex是xtrabackup的软件链接;

xtrabackup全备与恢复:

注意:备份时数据库是在线状态;恢复时需要离线并且mysql数据目录为空;
备份:

创建备份目录 
mkdir -pv /data/backup
创建备份授权账号root(可以是其他用户最小权限)
mysql> GRANT ALL ON *.* TO 'root'@'127.0.0.1' identified by "redhat";
Query OK, 0 rows affected, 1 warning (0.00 sec)

[root@db mysqldb]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --user=root --password=redhat  --host=127.0.0.1 /data/backup
看到类似如下信息表示备份成功:
xtrabackup: Transaction log of lsn (7701576) to (7701585) was copied.
180401 11:52:35 completed OK!
同时在/data/backup目录中产生以时间为目录的备份目录
[root@db backup]# ll /data/backup/
drwxr-x--- 14 root root 4096 4月   1 11:52 2018-04-01_11-52-29

备份后对数据库study 中的表进行修改

删除student表
mysql> drop table student;
Query OK, 0 rows affected (0.04 sec)
往user_info表中插入两行
mysql> insert into user_info values(1,"san",18,"男",4),(14,"Hello",28,"女",2);
Query OK, 1 row affected (0.00 sec)

模拟数据库崩溃

注意binlog文件备份好;如果binglog和数据目录在一起
[root@db backup]# service mysqld stop
[root@db backup]# rm -rf /data1/mysqldb/*

恢复数据:

切换到备份数据目录
[root@db backup]# cd /data/backup/2018-04-01_11-52-29
事务回滚不提交
[root@db 2018-04-01_11-52-29]# innobackupex  --defaults-file=/usr/local/mysql/etc/my.cnf --apply-log ./
类似以下提示表示完成:
InnoDB: Shutdown completed; log sequence number 7702056
180401 12:13:40 completed OK!

数据还原

由于centos7默认有/etc/my.cnf文件
因此需要重命名my.cnf或移除以免影响恢复;
[root@db 2018-04-01_11-52-29]# innobackupex  --defaults-file=/usr/local/mysql/etc/my.cnf --copy-back ./
类似以下提示表示恢复完成:
180401 12:16:15 [01]        ...done
180401 12:16:15 completed OK!

恢复binlog中信息

查看全备中的binlog信息(文件和位置)

[root@db backup]# cat /data/backup/2018-04-01_11-52-29/xtrabackup_binlog_info
mysql-bin.000008        14775
由引可知在上次全备时的binglog文件是mysql-bin.000008位置为14775
获取binlog信息
[root@db backup]# mysqlbinlog -j 14775 mysql-bin.000008 >/data/backup/binlog.sql
还原binlog中的内容(全备后的修改数据内容)
切换到mysql数据目录(/data1/mysqldb)并修改权限
[root@db mysqldb]# cd /data1/mysqldb
[root@db mysqldb]# chown mysql.mysql *  -R
启动mysql
[root@db mysqldb]# service mysqld start

登录数据库并导入binlog.sql

mysql> source /data/backup/binlog.sql
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name  | age  | gender | part_nid |
+-----+-------+------+--------+----------+
|   1 | san   |   18 | 男     |        4 |
|   2 | dong  |   29 | 男     |        2 |
|   4 | Ling  |   28 | 男     |        4 |
|   5 | ling  |   28 | 男     |        3 |
|   6 | dong  |   30 | 男     |        1 |
|   7 | b     |   11 | 女     |        1 |
|   8 | c     |   12 | 女     |        1 |
|   9 | d     |   18 | 女     |        4 |
|  10 | e     |   22 | 男     |        3 |
|  11 | f     |   23 | 男     |        2 |
|  12 | dongy |   22 | 男     |        1 |
|  13 | hi    |   18 | 男     |        4 |
|  14 | Hello |   28 | 女     |        2 |
+-----+-------+------+--------+----------+
13 rows in set (0.00 sec)

xtrabackup 增量备份与恢复

备份流程:
首次增量备份是基于完整备份后做的增量备份 ,后面的增量备份将基于前一次增量备份;
恢复流程:
合并完整备份事务 –>再合并第一次增量的事务–>….最后一次增量备份 +binlog日志

完整备份:

[root@db ~# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --user=root --password=redhat  --host=127.0.0.1 /data/backup
提示类似如下信息完成 :
xtrabackup: Transaction log of lsn (7802468) to (7802477) was copied.
180401 13:13:13 completed OK!
[root@db ~# ll /data/backup
2018-04-01_13-13-10    ######完整备份目录

模拟数据库的修改操作

删除第10行并新增一行
mysql> delete from user_info where nid=10;
Query OK, 1 row affected (0.01 sec)

mysql> insert into user_info value(15,'hehe',22,'男',1);
Query OK, 1 row affected (0.01 sec)

第一次增量备份

[root@db ~# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --incremental --user=root --password=redhat  --host=127.0.0.1 /data/backup/ --incremental-basedir=/data/backup/2018-04-01_13-13-10/
提示类似如下信息完成 :
xtrabackup: Transaction log of lsn (7803424) to (7803433) was copied.
180401 13:17:26 completed OK!

再次模拟数据库的修改操作

mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name  | age  | gender | part_nid |
+-----+-------+------+--------+----------+
|   1 | san   |   18 | 男     |        4 |
|   2 | dong  |   29 | 男     |        2 |
|   4 | Ling  |   28 | 男     |        4 |
|   5 | ling  |   28 | 男     |        3 |
|   6 | dong  |   30 | 男     |        1 |
|   7 | b     |   11 | 女     |        1 |
|   8 | c     |   12 | 女     |        1 |
|   9 | d     |   18 | 女     |        4 |
|  11 | f     |   23 | 男     |        2 |
|  12 | dongy |   22 | 男     |        1 |
|  13 | hi    |   18 | 男     |        4 |
|  14 | Hello |   28 | 女     |        2 |
|  15 | hehe  |   22 | 男     |        1 |
+-----+-------+------+--------+----------+
13 rows in set (0.01 sec)
插入一行再删除一行
mysql> insert into user_info value(16,'haha',21,'女',3);
Query OK, 1 row affected (0.01 sec)

mysql> delete from user_info where nid=2;
Query OK, 1 row affected (0.01 sec)
mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name  | age  | gender | part_nid |
+-----+-------+------+--------+----------+
|   1 | san   |   18 | 男     |        4 |
|   4 | Ling  |   28 | 男     |        4 |
|   5 | ling  |   28 | 男     |        3 |
|   6 | dong  |   30 | 男     |        1 |
|   7 | b     |   11 | 女     |        1 |
|   8 | c     |   12 | 女     |        1 |
|   9 | d     |   18 | 女     |        4 |
|  11 | f     |   23 | 男     |        2 |
|  12 | dongy |   22 | 男     |        1 |
|  13 | hi    |   18 | 男     |        4 |
|  14 | Hello |   28 | 女     |        2 |
|  15 | hehe  |   22 | 男     |        1 |
|  16 | haha  |   21 | 女     |        3 |
+-----+-------+------+--------+----------+
13 rows in set (0.00 sec)

第二次增量备份:

[root@db backup]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --incremental --user=root --password=redhat  --host=127.0.0.1 /data/backup/ --incremental-basedir=/data/backup/2018-04-01_13-17-21/
注意:这里的 --incremental-basedir=/data/backup/2018-04-01_13-17-21/ 是上一次增量备份 产生的备份 目录 
如果基于第一次完整备份 则成为差异备份

找出最近一次增量备份的binlog文件及信息

cd /data/backup/2018-04-01_13-21-56
[root@db 2018-04-01_13-21-56]# cat xtrabackup_binlog_info 
mysql-bin.000001    17452
备份 mysql-bin.000001 到/data/backup中
[root@db backup]# cd /data/backup
[root@db backup]# cp /data1/mysqldb/mysql-bin.000001 .
[root@db backup]# mysqlbinlog mysql-bin.000001 >binlog.sql

模拟数据库崩溃数据丢失

[root@db backup]# service mysqld stop
[root@db backup]# rm -rf /data1/mysqldb/*

数据恢复

[root@n1 backup]# ls
2018-04-01_13-13-10     2018-04-01_13-17-21    2018-04-01_13-21-56     binlog.sql    mysql-bin.000001

依次是完全整备份 ,第一次和第二次增量备份 目录 ,以及备份出来的binlog文件与binlog.sql
恢复过程:
首先对第1个(完整备份)合并只提交事务不回滚 再把第2个目录合并提交事务不回滚到第一个,再把第3个合并到第1个中;最后做一次回滚，再做统一事务提交;最后再加binlog恢复

完整备份 的事务合并
[root@db backup]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --apply-log --redo-only 2018-04-01_13-13-10/
合并第一次增量事务
[root@db backup]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --apply-log --redo-only 2018-04-01_13-13-10/ --incremental-dir=2018-04-01_13-17-21/
合并第二次增量事务
[root@db backup]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --apply-log --redo-only 2018-04-01_13-13-10/ --incremental-dir=2018-04-01_13-21-56/
合并所有的事务
[root@db backup]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf  --apply-log 2018-04-01_13-13-10/
提交还原事务
[root@db backup] innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf  --copy-back 2018-04-01_13-13-10/

修改还原数据权限与启动数据库:

[root@db backup]chown mysq.mysql /data1/mysqldb -R
[root@db backup] systemctl start mysqld

binlog事务恢复

mysql> source /data/backup/binlog.sql
mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name | age | gender | part_nid |
+-----+-------+------+--------+----------+
| 1 | san | 18 | 男 | 4 |
| 4 | Ling | 28 | 男 | 4 |
| 5 | ling | 28 | 男 | 3 |
| 6 | dong | 30 | 男 | 1 |
| 7 | b | 11 | 女 | 1 |
| 8 | c | 12 | 女 | 1 |
| 9 | d | 18 | 女 | 4 |
| 11 | f | 23 | 男 | 2 |
| 12 | dongy | 22 | 男 | 1 |
| 13 | hi | 18 | 男 | 4 |
| 14 | Hello | 28 | 女 | 2 |
| 15 | hehe | 22 | 男 | 1 |
| 16 | haha | 21 | 女 | 3 |
+-----+-------+------+--------+----------+
13 rows in set (0.00 sec)

到此增量备份 与恢复 已经 完成!

总结

日常数据库的备份是十分有必要的,而且不管用什么方法恢复,开启binary log十分重要,否则恢复不完整;binary log最好不要和数据目录一起,另外建议数据目录和binary log所在目录不要放在同一块物理磁盘;同时需要计划备份并实现异地备份;这样出现删库跑或崩溃数据丢失时就不怕了!本文很多步骤,可能存在遗漏之处,如有错误之处,欢迎指点。