MYSQL

最新推荐文章于 2024-04-03 09:15:32 发布

柒月柒日

最新推荐文章于 2024-04-03 09:15:32 发布

阅读量1.6k

点赞数

文章标签： mysql 数据库 sql linux

本文链接：https://blog.csdn.net/weixin_47444128/article/details/120307509

版权

MYSQL

一、MySQL数据库介绍

什么是数据?
数据(data)是事实或观察的结果，是对客观事物的逻辑归纳，是用于表示客观事物的未经加工的的原始素材。数据是信息的表现形式和载体，可以是符号、文字、数字、语音、图像、视频等。数据和信息是不可分离的，数据是信息的表达，信息是数据的内涵。数据本身没有意义，数据只有对实体行为产生影响时才成为信息。数据可以是连续的值，比如声音、图像，称为模拟数据。也可以是离散的，如符号、文字，称为数字数据。在计算机系统中，数据以二进制信息单元0,1的形式表示。

数据存储的发展史:

什么是数据库?
数据库（Database）:是按照数据结构来组织、存储和管理数据的仓库

什么是数据库管理系统(DBMS)?
数据库管理系统:是由数据库及其管理软件组成的系统
1.提供数据持久性的存储,备份,恢复
2.支持事务管理
3.数据操作的并发
4.支持独立的管理语言(sql语句)

使用数据库的优势:
1.数据的独立性:
元数据的存储,应用程序程序不需数据的格式问题
2.数据的冗余:
最小化数据冗余,保障数据的统一性和一致性
3.数据的安全:
不同的用户可以看到独立的数据
4.数据的利用率:
使用结构化的数据管理语言(sql语句)
5.简单的数据备份和恢复:
提供多种数据备份恢复方式,使数据的安全性更高

数据库分类:
1.关系型数据库(MySQL,Oracle,db2,SQL server)
2.非关系型数据库(MongoDB Hbase Cassandra)

什么是关系型数据库
关系数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。关系模型是由埃德加·科德于1970年首先提出的，并配合“科德十二定律”。现如今虽然对此模型有一些批评意见，但它还是数据存储的传统标准。标准数据查询语言SQL就是一种基于关系数据库的语言，这种语言执行对关系数据库中数据的检索和操作。关系模型由关系数据结构、关系操作集合、关系完整性约束三部分组成。

mysql数据库的发展史(该文章来自于互联网)
MySQL的历史最早可以追溯到1979年，有一个人叫Monty Widenius, 为一个叫TcX的小公司打工，并用BASIC设计了一个报表工具，可以在4M主频和16KB内在的计算机上运行。过了不久，又将此工具，使用C语言重写，移植到Unix平台，当时，它只是一个很底层的面向报表的存储引擎.

1985 年，瑞典的几位志同道合小伙子（以David Axmark 为首）成立了一家公司，这就是MySQL AB 的前身。这个公司最初并不是为了开发数据库产品，而是在实现他们想法的过程中，需要一个数据库。他们希望能够使用开源的产品。但在当时并没有一个合适的选择，没办法，那就自己开发吧。

在最初，他们只是自己设计了一个利用索引顺序存取数据的方法，也就是I S A M（Indexed Sequential Access Method）存储引擎核心算法的前身，利用ISAM 结合mSQL 来实现他们的应用需求。在早期，他们主要是为瑞典的一些大型零售商提供数据仓库服务。在系统使用过程中，随着数据量越来越大，系统复杂度越来越高，ISAM 和mSQL 的组合逐渐不堪重负。在分析性能瓶颈之后，他们发现问题出在mSQL 上面。不得已，他们抛弃了mSQL，重新开发了一套功能类似的数据存储引擎，这就是ISAM 存储引擎。大家可能已经注意到他们当时的主要客户是数据仓库，应该也容易理解为什么直至现在，MySQL 最擅长的是查询性能，而不是事务处理（需要借助第三方存储引擎）。

1990年，TcX的customer 中开始有人要求要为它的API提供SQL支持，当时，有人想到了直接使用商用数据库算了，但是Monty觉得商用数据库的速度难令人满意。于是，他直接借助于mSQL的代码，将它集成到自己的存储引擎中。但不巧的是，效果并不太好。于是,Monty雄心大起，决心自己重写一个SQL支持。

1996年，MySQL 1.0发布, 在小范围内使用。到了96年10月，MySQL 3.11.1发布了，没有2.x版本。最开始，只提供了Solaris下的二进制版本。一个月后，Linux版本出现了。此时的MySQL还非常简陋，除了在一个表上做一些Insert，Update，Delete和Select 操作职位，没有其他更多的功能。

紧接下来的两年里，MySQL依次移植到各个平台下。它发布时，采用的许可策略，有些与众不同：允许免费商用，但是不能将MySQL与自己的产品绑定在一起发布。如果想一起发布，就必须使用特殊许可，意味着要花银子。当然，商业支持也是需要花银子的。其它的，随用户怎么用都可以。这种特殊许可为MySQL带来了一些收入，从而为它的持续发展打下了良好的基础。

1999-2000年，有一家公司在瑞典成立了，叫MySQL AB。雇了几个人,与Sleepycat合作，开发出了 Berkeley DB引擎, 因为BDB支持事务处理，所以，MySQL从此开始支持事务处理了。

在2000 年的时候，MySQL 公布了自己的源代码，并采用GPL（GNU General Public License）许可协议，正式进入开源世界。

2000年4月，MySQL对旧的存储引擎进行了整理，命名为MyISAM。

2001年，Heikiki Tuuri向MySQL提出建议，希望能集成他们的存储引擎InnoDB，这个引擎同样支持事务处理，还支持行级锁。所以在2001年发布的3.23 版本的时候，该版本已经支持大多数的基本的SQL 操作，而且还集成了MyISAM和InnoDB 存储引擎。MySQL与InnoDB的正式结合版本是4.0。

2004年10月，发布了经典的4.1版本。 2005年10月，有发布了里程碑的一个版本，MySQL 5.0. 在5.0中加入了游标，存储过程，触发器，视图和事务的支持。在5.0 之后的版本里，MySQL明确地表现出迈向高性能数据库的发展步伐。

2008年1月16号MySQL被Sun公司收购。

2009年04月20日Oracle收购Sun 公司，MySQL 转入Oracle 门下。

2010年04月22 发布MySQL 5.5, MySQLcluster 7.1.

现在官网可以下到的MySQL 版本是：5.5.18. Oracle 对MySQL版本重新进行了划分，分成了社区版和企业版，企业版是需要收费的，当然收费的就会提供更多的功能。

yum -y install mysql-community* --skip-broken

二、MySQL数据库安装

1.rpm包安装

CentOS7自带有MariaDB而不是MySQL，MariaDB和MySQL一样也是开元的数据库

解决方案：如果必须要安装MySQL，首先必须添加mysql社区repo通过输入命令：sudo rpm -Uvh http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm

yum install mysql-server
yum install mysql-devel

下载rpm包进行解压安装

mysql5.7mysql5.7的默认密码
systemctl restart mysqld
cat /var/log/mysqld.log
2019-04-18T09:22:59.813319Z 1 [Note] A temporary password is generated for root@localhost: (+(FyeLZt8qs

修改密码
mysql> set password=password(“Robin_123”);

rpm包mysql基本信息
配置文件目录:
/etc/my.cnf
/etc/my.cnf.d/
数据主目录:/var/lib/mysql
端口: 3306
日志文件:/var/log/mysqld.log
启动服务: systemctl restart mysqld

基本操作:
mysql> show databases; 查看所有数据库
mysql> create database mydb; 创建数据库
mysql> use mydb; 切换数据库
mysql> create table user_info(id int,name char(10)); 创建表
mysql> show tables; 显示表
mysql> desc user_info; 描述表(看属性)
mysql> insert into user_info values(1,‘robin’); 插入数据
mysql> select * from user_info; 查看数据
mysql> drop table user_info; 删除表
mysql> drop database mydb; 删除库

密码不正确:
[root@localhost ftp]# systemctl stop mysqld
[root@localhost ftp]# rm -rf /var/lib/mysql/*
[root@localhost ftp]# > /var/log/mysqld.log
[root@localhost ftp]# mysqld --initialize --datadir=/var/lib/mysql
[root@localhost ftp]# chown -R mysql.mysql /var/lib/mysql
[root@localhost ftp]# cat /var/log/mysqld.log | grep password
[root@localhost ftp]# systemctl restart mysqld
[root@localhost ftp]# mysql -u root -pKTdr2+yppDsq

mysql-rpm
1.配置/etc/my.cnf
/etc/my.cnf.d/ 子配置文件目录

2.数据目录: /var/lib/mysql
3.mysql启动后套接字:/var/lib/mysql/mysql.sock
4.mysql的命令 /usr/bin/

mysql-src
1.配置/etc/my.cnf
/etc/my.cnf.d/ 子配置文件目录
2.数据目录: /usr/local/mysql/data
3.根据编译参数产生套接字文件:
-DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock
4.mysql命令/usr/local/mysql/bin/

2.源码编译

[root@localhost ftp]# tar -xvf mysql-5.7.18.tar.gz -C /usr/local/src/
[root@localhost mysql-5.7.18]# yum install cmake
[root@localhost src]# cd /usr/local/src/
[root@localhost src]# cd /usr/local/src/mysql-5.7.18/

yum -y install gcc gcc-c++ cmake bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel   ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel perl-Data-Dumper

[root@localhost mysql-5.7.18]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DSYSCONFDIR=/etc -DWITH_READLINE=1 -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock -DENABLED_LOCAL_INFILE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost

[root@localhost mysql-5.7.18]# make
[root@localhost mysql-5.7.18]# make install

系统中需要有mysql帐号
useradd -u 27 -d /var/lib/mysql -s /bin/false mysql

创建数据目录
[root@localhost mysql]# mkdir /usr/local/mysql/data

不使用rpm产生的配置文件
[root@localhost mysql]# mv /etc/my.cnf /etc/my.cnf.bak

初始化
[root@localhost mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data

修改权限
[root@localhost mysql]# chown -R mysql.mysql /usr/local/mysql/

启动mysql
[root@localhost mysql]# /usr/local/mysql/bin/mysqld_safe --user=mysql &

检查端口:
[root@localhost mysql]# netstat -anplt | grep :3306
tcp6 0 0 :::3306 ::: LISTEN 60187/mysqld

登录
[root@localhost mysql]# /usr/local/mysql/bin/mysql -uroot -p"Rkadj:2opk#5"
修改密码
mysql> set password=password(‘Robin_123’);

修改源码mysql的启动方式
[root@localhost support-files]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqldd
[root@localhost support-files]# service mysqldd restart
开机自启动
[root@localhost support-files]# chkconfig --add mysqldd
[root@localhost support-files]# chkconfig mysqldd on
也可以
[root@localhost support-files]# systemctl restart mysqldd

修改源码客户端
[root@localhost support-files]# vim /etc/profile
PATH=/usr/local/mysql/bin:$PATH
export PATH

[root@localhost support-files]# source /etc/profile
测试登录
[root@localhost support-files]# mysql -u root -pRobin_123

错误1:
CMake Error at cmake/boost.cmake:81 (MESSAGE):
You can download it with -DDOWNLOAD_BOOST=1 -DWITH_BOOST=

This CMake script will look for boost in . If it is not there,
it will download and unpack it (in that directory) for you.

If you are inside a firewall, you may need to use an http proxy:

export http_proxy=http://example.com:80

删除缓存文件
[root@localhost mysql-5.7.18]# rm -rf CMakeCache.txt

解决:
boost 版本过低

1.在/usr/local下创建一个名为boost的文件夹
mkdir -p /usr/local/boost
2.进入这个新创建的文件夹然后下载boost
wget http://www.sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz
3.解压
tar -xvzf boost_1_59_0.tar.gz
4.继续cmake，添加上红色部分
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DSYSCONFDIR=/etc -DWITH_READLINE=1 -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock -DENABLED_LOCAL_INFILE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost

错误2:
CMake Error at cmake/readline.cmake:64 (MESSAGE):
Curses library not found. Please install appropriate package,

remove CMakeCache.txt and rerun cmake.On Debian/Ubuntu, package name is libncurses5-dev, on Redhat and derivates it is ncurses-devel.
Call Stack (most recent call first):
cmake/readline.cmake:107 (FIND_CURSES)
cmake/readline.cmake:197 (MYSQL_USE_BUNDLED_EDITLINE)
CMakeLists.txt:488 (MYSQL_CHECK_EDITLINE)

– Configuring incomplete, errors occurred!

解决:
yum install ncurses-devel

[root@localhost [root@localhost mysql-5.7.18]# rm -rf CMakeCache.txt

错误3:
CMake Error: your C compiler: “CMAKE_C_COMPILER-NOTFOUND” was not found. Please set CMAKE_C_COMPILER to a valid compiler path or name.
CMake Error: your CXX compiler: “CMAKE_CXX_COMPILER-NOTFOUND” was not found. Please set CMAKE_CXX_COMPILER to a valid compiler path or name.
– CMAKE_GENERATOR: Unix Makefiles
CMake Error at cmake/os/Linux.cmake:41 (MESSAGE):
Unsupported compiler!
Call Stack (most recent call first):
CMakeLists.txt:169 (INCLUDE)

解决:
yum install gcc gcc-c++

错误:
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)

情况1: socket文件没有在 /var/lib/mysql/mysql.sock

情况2: mysqld服务未启动

解决:
1.查看mysld服务端口 netstat -anplt | grep :3306

a.没端口服务未启动
b.有端口,文件位置不正确
cat /etc/my.cnf
socket=/var/lib/mysql/mysql.sock

#!/bin/bash
wget -P /tmp ftp://10.10.11.222/mysql.tar.gz  #下载mysql
tar -xvf /tmp/mysql.tar.gz -C /usr/local/   #解压mysql
useradd -u 27 -M -s /sbin/nologin mysql     #创建mysql用户
mkdir /usr/local/mysql/data                 #创建数据目录
chown -R mysql.mysql /usr/local/mysql/      #修改目录的拥有者和所属组
/usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data >/tmp/mysql.passwd 2>&1  #初始化

rm -rf /etc/my.cnf                  #删除my.cnf
mkdir /var/lib/mysql                
chown -R mysql.mysql /var/lib/mysql    #修改拥有者和所属组

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqldd 
chkconfig --add mysqldd
systemctl restart mysqldd             #重启mysql

echo "PATH=/usr/local/mysql/bin:$PATH" >> /etc/bashrc  #修改路径
echo "export PATH" >> /etc/bashrc     #设置为全局环境变量
source  /etc/bashrc

password=`cat /tmp/mysql.passwd | tail -1 | awk '{print $NF}'` #获取密码
/usr/local/mysql/bin/mysqladmin -u root -p"$password" password '123'
echo "source password:" $password
echo "new password: 123"                   #修改密码

三、MySQL架构

所谓MySQL的架构指的是MySQL的逻辑架构，了解MySQL的逻辑架构可以帮助我们更好的理解MySQL是如何工作的，以及在MySQL工作过程当中我们需要注意的一些地方，并且理解一些有关于数据库的的基本概念和知识。

在这里插入图片描述

四、SQL语句

接下来的一些内容，我们需要提前学一些简单的sql语句，方便大家理解接下来的知识。

DDL—数据定义语言(Create，Alter，Drop，DECLARE)
DML—数据操纵语言(Select，Delete，Update，Insert)
DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)
DQL–数据查询语言(select)

DML（data manipulation language）：
它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言
DDL（data definition language）：
DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用
DCL（Data Control Language）：
是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

-----------------------------------------
接下来我们逐步学习SQL语句,在学习之前我们先注意一下SQL语句的注意事项.
1.每条SQL语句结束时要以;做为结束符.(除了use命令)
2.SQL语句的关键字不区分大小写(除了库名字和表名字)
3.在查询数据库信息或者表信息时,可以以\G做为结束符,表示以文本模式输出
4.当你不需要一条语句输出的结果以\c结束,不可以使用ctrl+c,否则登出mysql.
5.我们可以在命令行执行sql语句,要通过mysql -e参数
mysql -e “show databases \G” 显示到shell上
6.如果需要获取SQL语句的帮助可以用help命令
如:help create
如果需要进一步获取帮助,可以继续使用help命令
如:help create database

---------------------------------------------------

1.DDL数据库定义语句

建立数据库以及查询
create database db;
create database db CHARACTER SET = ‘utf8’
show databases;
show create database db;
alter database db CHARACTER SET = ‘latin1’;
修改库名只需要改数据库目录名称
drop database db;

练习:
1.创建数据库字符集为latin1
2.修改数据库字符集为utf8
3.删除数据库

2.建立表，查询表以及数据类型

数据类型
数值类型
整数类型字节范围（有符号）范围（无符号）用途
TINYINT 1字节 (-128，127) (0，255) 小整数值
SMALLINT 2字节 (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值
INT或INTEGER 4字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4字节 (-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度浮点数值 (7个有效位)
DOUBLE 8字节 (1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度浮点数值 (15个有效位)
DECIMAL 不存在精度损失，常用于银行帐目计算。（28个有效位）

float类型出现不精确情况
mysql> create table t1(money float(10,2));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 values(697302.68);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+-----------+
| money     |
+-----------+
| 697302.69 |
+-----------+
1 row in set (0.01 sec)

超出数值范围
mysql> create table t1(age tinyint);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t1 values(500);
ERROR 1264 (22003): Out of range value for column 'age' at row 1

mysql> select @@sql_mode
    -> ;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql5.0以上版本支持三种sql_mode模式：ANSI、TRADITIONAL和STRICT_TRANS_TABLES。
ANSI模式：宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。
TRADITIONAL模式：严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事物时，会进行事物的回滚。
STRICT_TRANS_TABLES模式：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。

临时修改模式:可以插入数据,报warning
mysql> set @@sql_mode=ANSI;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(500);
Query OK, 1 row affected, 1 warning (0.01 sec)

字符串类型
create table t1(id int(6),name char(10));
create table t1(id int(6),name varchar(10));
日期时间类型
date类型
create table t4(aa date);
insert into t4 values('2010-04-01'),(20100401);
select * from t4;
+------------+
| aa         |
+------------+
| 2010-04-01 |
| 2010-04-01 |
+------------+
time类型
create table t5(showttime time);
insert into t5 values ('11:11:11'),('11:11'),('111111');
select * from t5;
+-----------+
| showttime |
+-----------+
| 11:11:11  |
| 11:11:00  |
| 11:11:11  |
+-----------+
出现的问题
create table t6 (a_data data,a_time time);
insert into t6 values('1978-4-6',123412),(651212,'3:5:6');
select * from t6;
+------------+----------+
| a_date     | a_time   |
+------------+----------+
| 1978-04-06 | 12:34:12 |
| 2065-12-12 | 03:05:06 |
+------------+----------+
年份的范围00-69为2000-2069&&70-99为1970-1999	
year类型
create table t7 (year year);
insert into t7 values(2003),(04),(53),(89),(90);
select * from t7;
+------+
| year |
+------+
| 2003 |
| 2004 |
| 2053 |
| 1989 |
| 1990 |
+------+
datetime和timestamp类型
create table t8(f_datetime datetime,f_timestamp timestamp);
insert into t8 values('1999-11-11 11:11:11','2002-11-111:11:11');
insert into t8 values(19991111111111,20021111111111);
insert into t8 values(now(),null);
select * from t8;
+---------------------+---------------------+
| f_datetime          | f_timestamp         |
+---------------------+---------------------+
| 1999-11-11 11:11:11 | 2002-11-11 11:11:11 |
| 1999-11-11 11:11:11 | 2002-11-11 11:11:11 |
| 2012-03-21 21:05:21 | 2012-03-21 21:05:21 |
+---------------------+---------------------+
ENUM和SET类型
create table t10(gerder ENUM('M','F'));	
insert into t10 values('M'),('m'),('F'),('aa'),(null);
select * from t10;
+------+
| gender  |
+------+
| M    |
| M    |
| F    |
|      |
| NULL |
+------+
create table t11 (type SET('a','b','c','d','e'));
insert into t11 values(a);
insert into t11 values('b,c');
insert into t11 values('J');
select * from t11;
+------+
| type |
+------+
| a    |
| b,c  |
|      |
+------+
insert into t11 values('b,c,e,f');既有合法字符又有非法字符
select * from t11;                                                  
+-------+
| type  |
+-------+
| a     |
| b,c   |
|       |
| b,c,e |
+-------+

练习:
创建表test  id
 name money gender hobby email qq  idcard jointime 



create table test(id tinyint,name char(10),money float(10,2),gender enum('M','F'),hobby set('a','b','c'),email varchar(50),qq char(15),shenfenzheng char(18),jointime datetime);

mysql> rename table test to newtest;
mysql> alter table test change id uid smallint;
mysql> alter table test modify id smallint;

------------------------------------------------------

3.修饰符(约束)

无符号 unsigned
用0补齐 zerofill

desc t11;
+-------+--------------------------+------+-----+--------+-------+
| Field | Type                     | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| type  | set('a','b','c','d','e') | YES  |     | NULL    |       |
+-------+--------------------------+------+-----+---------+-------+

not null约束  非空
create table t12 (id int,gender enum('M','W') NOT NULL );
 desc t12;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| id    | int(10) unsigned zerofill | YES  |     | NULL    |       |
| gender   | enum('M','W')             | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
insert into t12(id) values(1);
Query OK, 1 row affected (0.00 sec)
 select * from t12;
+---+-----+
|id | gender |
+---+-----+
| 1 | NULL|
+---+-----+

DEFAULT约束  默认值
create table t13 (id int ,gender enum('M','W') NOT NULL default 'M' );
desc t13;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| gender| enum('M','W') | YES  |     | M       |       |
+-------+---------------+------+-----+---------+-------+
insert into t13(id) values(3);
select * from t13;
+------+------+
| id   | gender  |
+------+------+
|    2 | M    |
|    3 | M    |
+------+------+

fv
insert into t14(name) values('haha');
select * from t14;
+----+------+
| id | name |
+----+------+
|  1 | zhb  |
|  2 | haha |
+----+------+

4.索引

索引的作用：索引可以大大提高MySQL的检索速度。
索引建立

create table t15(id int not null ,name char(10),index(id));
desc t15;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | MUL | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
向已有表添加索引
create table t16(id int not null ,name char(10));
 desc t16;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
create index id on t16 (id);
alter table t17 add index(id);

desc t16;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | MUL | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
删除索引
drop index id on t16;
desc t16;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
查询索引
show index from t16;



UNIQUE索引(允许空值，但不能重复（唯一索引）)   

create table t17(id int ,name char(10),unique(id));
desc t17;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  | UNI | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
insert into t17 values(null,'zhb');
 select * from t17;
+------+------+
| id   | name |
+------+------+
| NULL | zhb  |
+------+------+

添加unique索引

create unique index email on t16(email);

alter table t1 add unique(name);




联合索引
联合索引是指对表上的多个列进行索引

最左匹配原则：Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b | a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

优化：在联合索引中将选择性最高的列放在索引最前面。

如果where条件中是OR关系，加索引不起作用
数据量少的字段不需要加索引

5.主键

PRIMARY KEY(主键约束 值唯一 uniq和not null的结合)
create table t18(id int,name char(10),primary key(id));
 desc t18;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | 0       |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
insert into t18 values(1,'zhb');
select * from t18;
+----+------+
| id | name |
+----+------+
|  1 | zhb  |
+----+------+
insert into t18 values(1,'zhb');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' 不允许重复

删除主键
mysql> alter table t19 drop primary key;
向已有表添加主键
mysql> alter table t19 add primary key(id);

在多个列上建立主键  （多列同时重复才报错）
create table t19(id int,name char(10),primary key(id,name));
desc t19;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | 0       |       |
| name  | char(10) | NO   | PRI |         |       |
+-------+----------+------+-----+---------+-------+
 insert into t19 values(1,'zhb');
 insert into t19 values(1,'zorro');
select * from t19;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhb   |
|  1 | zorro |
+----+-------+

6.外键

外键myisam引擎不支持只能用innodb引擎
create table dpmnt(id int not null,name char(10) not null,primary key(id)) type = INNODB;
desc dpmnt;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(10) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
建立外键
create table emp (id int not null, name char(10) not null,fk_dpmnt int not null ,primary key(id),index (fk_dpmnt),foreign key (fk_dpmnt) references dpmnt(id)) type=innodb;
desc emp;         
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| id       | int(11)  | NO   | PRI | NULL    |       |
| name     | char(10) | NO   |     | NULL    |       |
| fk_dpmnt | int(11)  | NO   | MUL | NULL    |       |
+----------+----------+------+-----+---------+-------+
 insert into dpmnt  values(1,hr);
 insert into dpmnt  values(2,'yw');
 insert into emp values(10,'zhb',3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`fk_dpmnt`) REFERENCES `dpmnt` (`id`))

fk_dpmnt字段的数据必须得是dpmnt表里有的不然报错...

即使表存在外键约束，MySQL还允许我们删除表，并且不会产生错误。这是删除外键的方法。
alter table emp drop foreign key  emp_ibfk_1;
删除外键


全文本索引
create table t22(id int,name char(10),fulltext(id));

7.练习

创建一张表 id name两列 ，创建的同时在id列建立索引

mysql> create table test1(id int, name char(10), index(id));

查询索引

mysql> show index from test1;

mysql> show index from test1\G

删除索引

mysql> drop index id on test1;

再添加索引

mysql> alter table test1 add index(name);



创建一张表 id name两列 ，创建的同时在id列建立唯一索引

mysql> create table t1(id int, name char(10), unique(id));

查询唯一索引

mysql> show index from t1;

mysql> show index from t1\G

删除唯一索引

mysql> drop index id on t1;

再添加唯一索引

mysql> alter table t1 add unique(name);



创建一张表 id name两列 ，创建的同时在id列建立主键

mysql> create table t2(id int, name char(10), primary key(id)); 

查询主键

mysql> desc t2;

mysql> show index from t2；

mysql> show index from t2\G

删除主键

mysql> alter table t2 drop primary key;

再添加主键

mysql> alter table t2 add primary key(name);



练习:
创建表test  id name money gender hobby email qq shenfezheng jointime 

create table test(id tinyint,name char(10),money float(10,2),gender enum('M','F'),hobby set('a','b','c'),email varchar(50),qq char(15),shenfenzheng char(18),jointime datetime);

1.添加约束
create table test1 ( id int primary key auto_increment, name char(20) not null, money float(10,2) not null, gender enum('M','F') not null default 'M', hobby set('a','b','c') not null default 'a', qq char(15) unique, email char(50), jointime datetime,index(email));
2.删除掉所有的约束
alter table test1 modify id int;  删除auto_increment
alter table test1 drop primary key; 删除primary key
alter table test1 modify id int;  删除 not null

alter table test1 modify name char(10);
alter table test1 modify money float(10,2);
alter table test1 modify gender enum('M','F');
alter table test1 modify hobby set('a','b','c');
drop index qq on test1;
drop index email on test1;

3.在添加约束
alter table test1 add primary key(id);
alter table test1 modify id int auto_increment;

show tables;
show create table t1;

ALTER table t2 RENAME t1;
alter table t2 MODIFY a tinyint  not null,CHANGE b c char(20);
create table members ( id int(11),name char(10),tel char(15));
alter table members ADD qq int;
alter table members drop qq;
alter table members add qq int after name ;
alter table members add phone first;
alter table test1 modify qq char(15) after id;

drop table t1;

create table user(
id int primary key auto_increment,
name varchar(30) not null,
money float(10,2),
gender enum('M','F') not null,
hobby set('a','b','c','d'),
email varchar(50) unique,
qq char(15) unique,
idcard char(18) unique,
jointime datetime not null,
index(name));

8.DML 数据库操作语句

insert 
mysql> INSERT INTO members ( member_id,fname,lname,tel,email) VALUES ( NULL,'john','Doe','1234567','jdoe@163.com');
mysql> INSERT INTO members VALUES ( NULL,'kyo','oyk','7654321','kyo@163.com');
mysql> INSERT INTO members (fname,lname,email,tel,member_id) VALUES ('bob','kk','bob@163.com','22334455',NULL);

update
mysql> UPDATE members SET email = 'kyo@163.com' WHERE member_id = 3;
mysql> UPDATE members SET email = 'hoho@163.com',lname = 'ho' WHERE member_id = 2;

delete	
mysql> DELETE FROM members;
mysql> DELETE FROM members WHERE member_id = 1;

sql语句使用

连接数据库 
mysql -u root -p123 -h localhost
查看服务器状态
show staus;
显示所有库名
show databases;
使用数据库
use db;
显示当前数据库中的所有表
show tables;
查看表结构
desc tables;

select查询语句

select name from tables; 从表中查询指定列
select id,name,sal from tables; 指定多个列名
select * from tables;查询所有的列

select distinct id from tables; 去掉重复行 
select name from tables limit 5; 显示前5行
select name from tables limit 5,5;显示从第5行开始的后5行即6-10行

select name from db.t1;没有使用use进入db库时查询db库的t1表
select t1.name from db.t1; 指定库的表 指定表的列


显示mysql中第一个用户名字?

在shell命令行显示用户的名字和密码
显示mysql中的前3个用户
\-------------------------------------
修改root帐号密码为456
1.停止服务
killall mysqld
2.跳过授权表启动服务
[root@robin mysql]# /usr/local/mysql/bin/mysqld_safe --user=mysql --skip-grant-tables &
3.修改密码
mysql> update mysql.user set password=password('123') where user='root' and host='localhost';

mysql.5.7
update mysql.user set authentication_string=password('123') where user='root';
4.关闭服务
5.正常启动服务,登录


排序检索语句
select id,name from t1 order by id; 按id排序
select id,name from t1 order by id,name;先按id排序id相同在按name排序
select id,name from t1 order by id desc; 按id反向排序（降序）
select id,name from t1 order by id desc,name; 先按id反向排序再按名字排序
select id,name,sal from t1 order by sal desc limit 1;查找工资最高的人


where子句
select id,name,sal from t1 where name='tom'; 查找tom的信息
where 子句的操作符
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between 5 and 10 在两个值之间
is null 空值
is not null 非空值

select id,name from t1 where id>5 and name='tom'; and操作符表示两个条件都要满足 与操作
select id,name from t1 where id=10 or name='tom';or操作符表示满足任意条件  或操作

select id,name,sal from t1 where id=10 or id=20 and sal > 5000; id为10的 或者id为20并且薪水大于5000的;and优先执行
select id,name,sal from t1 where (id=10 or id=20) and sal > 5000;id为10或者20  并且薪水大于5000的
 	
select id,name,sal from t1 where id in (10,20,30);id在 10 20 30 中的记录
这条语句用or可以做到相同的结果,那in的好处
1.in的语法更加直观
2.in的计算次序更容易管理(操作符少)
3.in 一般比or执行的更快
4.in的最大优点可以包含其他子句 or不行

取最高薪水的人
select * from test2 where money=(select money from test2 order by money desc limit 1);

select id,name,sal from t1 where id not in (10,20,30); id不在10 20 30的记录 not找到不匹配的记录更简单

通配符%匹配多个字符_匹配一个字符
select id,name from t1 where name like 'jer%';模糊查询名字为jer开头的记录
select id,name from t1 where name like 'j%y'; 匹配j开头y结尾的
select id,name from t1 where name like '_err%' 匹配e前边有一个字符的记录	

原则:
尽量少使用通配符,如果其他操作符能做到就不要使用通配符
在确实需要通配符时,尽量不要使用%erry 这种用法搜索起来会更慢	
至于使用位置,使用错了得不到想要的结果


正则表达式的使用regexp
select id,name from t1 where name regexp 'je*';调用正则匹配je开头
select id,name from t1 where name regexp 'y$' ;


语句的拼接
select concat(id ,'(',name,')') from t1 将id和name 拼接为1列 oracle用||
select concat(id ,'(',name,')') all_name from t1 别名也可以使用as
select sal*12 from t1 计算全年薪水  + - * /

函数使用
select upper(name) as new_name from t1; 将名字转换为大写 lower 小写
group by 分组 必须在where之后 分组前过滤  having 可以分组后过滤

sum  max min avg count year month day hour minute second







insert into new values(1,'robin',10000,'M',"a,b",'12345678','123456789012345678',"2010-10-10 10:10:10","robin@robin.com");
insert into new values(2,'zorro',20000,'M',"b,c",'123456789','123456789012345679',"2011-11-10 10:10:10","zorro@zorro.com");
insert into new values(3,'jack',15000,'M',"a,b,c",'1234569','123456789012345698',"2009-12-10 10:10:10","jack@163.com");




练习:
cpu
prod_name prod_pris  vender_name  prod_date 
cpu1             1200         inter                2018-10-10
cpu2             800           amd                2017-10-10

1.求各厂商最贵的cpu价钱
2.求各厂商最贵的cpu名字
 select prod_name,prod_pris,cpu.vender_name,vender_date from cpu,(select vender_name,max(prod_pris) as new_pris from cpu group by vender_name) as new where cpu.vender_name = new.vender_name and cpu.prod_pris = new.new_pris;

1.作业1
修改库名

2.bank系统 (mysql数据库)

五、MySQL引擎

1.mysql引擎类型

存储引擎是什么?

MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。
例如，如果你在研究大量的临时数据，你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据

MyISAM
mysql> create table t4(id int,name char(10)) engine=myisam;

InnoDB

MRG_MYISAM

MEMORY
创建memory引擎表
mysql> create table t3(id int,name char(10)) engine=memory;




MyISAM
MyISAM是默认存储引擎。它基于更老的ISAM代码，但有很多有用的扩展。（注意MySQL 5.1不支持ISAM）。
每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名 为.MYD （MYData）。索引文件的扩展名是.MYI （MYIndex）。 MyISAM文件的格式是平台无关的，这意味着你可以将数据和索引文件从一个intel服务器上拷贝到一台PowerPC或者Sun SPARC上，而不会出任何问题。

主要区别：

　　MyISAM是非事务安全型的，而InnoDB是事务安全型的。

　　MyISAM锁的粒度是表级，而InnoDB支持行级锁定。

　　MyISAM支持全文类型索引，而InnoDB不支持全文索引。

　　MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。

　　MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。

　　InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表(alter table tablename engine=innodb)。

mysql的配置文件:/etc/my.cnf  /usr/my.cnf /usr/local/mysql/my.cnf
独占表空间和共享表空间
innodb_file_per_table=1  独立
innodb_file_per_table=0  共享


mysql引擎类型：
mysql> show engines\G
*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
9 rows in set (0.00 sec)



mrg_myisam实验
mysql> create table a1(id int, name char(10)) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> create table a2(id int, name char(10)) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into a1 values(1,'robin');
Query OK, 1 row affected (0.00 sec)

mysql> insert into a2 values(2,'zorro');
Query OK, 1 row affected (0.01 sec)

mysql> select * from a1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | robin |
+------+-------+
1 row in set (0.01 sec)

mysql> select * from a2;
+------+-------+
| id   | name  |
+------+-------+
|    2 | zorro |
+------+-------+
1 row in set (0.00 sec)

mysql> create table a3(id int, name char(10)) union=(a1,a2) INSERT_METHOD=first engine=mrg_myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from a3;
+------+-------+
| id   | name  |
+------+-------+
|    1 | robin |
|    2 | zorro |
+------+-------+
2 rows in set (0.01 sec)

mysql> insert into a3 values(3,'tom');
Query OK, 1 row affected (0.00 sec)

mysql> select * from a3;
+------+-------+
| id   | name  |
+------+-------+
|    1 | robin |
|    3 | tom   |
|    2 | zorro |
+------+-------+
3 rows in set (0.00 sec)

#INSERT_METHOD=first/last 表示可以在第一个或者最后一个表里插入数据，如果不写则无法插入数据


create table t10(id int,name char(10)) engine=myisam;
create table t20(id int,name char(10)) engine=myisam;
create table t30(id int,name char(10)) union=(t10,t20) engine=mrg_myisam; 
create table t40(id int,name char(10)) union=(t10,t20) INSERT_METHOD=first engine=mrg_myisam;

2.mysql视图

单表视图
create view new_emp as select * from emp;
create view new_emp1 as select uid,name,money from emp;
修改视图=修改源表

多表视图
create table tt1(id int,name char(10),did int);
create table tt2(did int,dname char(10));
多表查询
select tt1.id,tt1.name,tt2.did,tt2.dname from tt1,tt2 where tt1.did=tt2.did;
create view kk as select tt1.id,tt1.name,tt2.did,tt2.dname from tt1,tt2 where tt1.did=tt2.did;
create view kkk as select tt1.name,tt2.dname from tt1,tt2 where tt1.did=tt2.did;

3.innodb

1.外键索引
外键myisam引擎不支持只能用innodb引擎

先有主键

create table dpmnt(id int,name char(10) not null,primary key(id)) type = INNODB;
desc dpmnt;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(10) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
建立外键
create table emp (id int not null, name char(10) not null,fk_dpmnt int not null ,primary key(id),index (fk_dpmnt),foreign key (fk_dpmnt) references dpmnt(id)) type=innodb;
desc emp;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| id       | int(11)  | NO   | PRI | NULL    |       |
| name     | char(10) | NO   |     | NULL    |       |
| fk_dpmnt | int(11)  | NO   | MUL | NULL    |       |
+----------+----------+------+-----+---------+-------+
 insert into dpmnt  values(1,hr);
 insert into dpmnt  values(2,'yw');
 insert into emp values(10,'zhb',3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`fk_dpmnt`) REFERENCES `dpmnt` (`id`))

fk_dpmnt字段的数据必须得是dpmnt表里有的不然报错...

即使表存在外键约束，MySQL还允许我们删除表，并且不会产生错误。这是删除外键的方法。
alter table emp drop foreign key  emp_ibfk_1;
删除外键
添加外键
alter table emp add CONSTRAINT fk_1 foreign key(did) references dpt(did);



create table dpt(did int primary key,dname char(10))
create table emp(id int,name char(10),did int,foreign key (did) references dpt(did) );

insert into emp values(1,'robin',10);
insert into emp values(2,'zorro',30);  

alter table emp drop foreign key emp_ibfk_1;
alter table emp add foreign key (did) references dpt(did);
alter table emp add CONSTRAINT emp_dpt_fk foreign key (did) references dpt(did);

级联删除,级联更新
mysql> create table emp(id int,name char(10),did int,foreign key (did) references dpt(did) ON DELETE CASCADE on update cascade);




2.表级锁和行级锁
行级锁：对正在被修改的行进行锁定，其它的用户可以访问被锁定的行以外的行。表级锁：锁定整个表，限其他用户对表的访问

3.事物的支持
事务处理是一种机制,用来管理成批执行的sql语句,以保证数据库不包含不完整的操作结果,他们或者为整体执行完成,或者完全不执行(如果没有错误发生整组语句提交到数据库,如果发生错误,则进行回退,以保证数据的安全)

事务 transaction 指定一组sql语句
回退 rollback 撤销指定的sql语句(只能回退insert delete update语句)
提交 commit 提交未存储的sql语句
保留点 savepoint 事务处理中设置的临时占位符 你可以对它发布回退(与整个事务回退不同)

如果用drop ，create，alter 则强行关闭并保存



create table t11(id int,name char(10)) engine=innodb;
start transaction;
delete from t11;

select * from t11;
Empty set (0.00 sec)

rollback;

select * from t11;
+------+-------+
| id   | name  |
+------+-------+
|    1 | zorro |
|    2 | zorro |
|    2 | zorro |
+------+-------+

提交
start transaction;
insert into t11 values(3,'tom');
select * from t11;
commit;

保留点
start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t11 values(5,'haha');
Query OK, 1 row affected (0.01 sec)

mysql> savepoint insert1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t11;
+------+-------+
| id   | name  |
+------+-------+
|    1 | tom   |
|    2 | jerry |
|    3 | zorro |
|    4 | seker |
|    5 | haha  |
+------+-------+
5 rows in set (0.00 sec)

mysql> delete from t11 where id=4;
Query OK, 1 row affected (0.00 sec)

mysql> savepoint delete1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t11;
+------+-------+
| id   | name  |
+------+-------+
|    1 | tom   |
|    2 | jerry |
|    3 | zorro |
|    5 | haha  |
+------+-------+
4 rows in set (0.00 sec)

mysql> delete from t11 where id=1;
Query OK, 1 row affected (0.00 sec)

mysql> savepoint delete2;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t11;
+------+-------+
| id   | name  |
+------+-------+
|    2 | jerry |
|    3 | zorro |
|    5 | haha  |
+------+-------+
3 rows in set (0.01 sec)

mysql> rollback to delete1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t11;
+------+-------+
| id   | name  |
+------+-------+
|    1 | tom   |
|    2 | jerry |
|    3 | zorro |
|    5 | haha  |
+------+-------+
4 rows in set (0.00 sec)







create table dpt(did int primary key,dname char(10)) engine=innodb;

create table emp(id int primary key,name char(30),did int,foreign key(did) references dpt(did)) engine=innodb;

alter table emp drop foreign key  emp_ibfk_1;
删除外键
添加外键
alter table emp add CONSTRAINT fk_1 foreign key(did) references dpt(did);

六、用户权限和访问控制

1.用户创建及密码修改

创建用户并设置密码
create user zorro identified by '123';
create user zorro@localhost identified by '123';
create user zorro@'%' identified by '123';
查询
select user from mysql.user;
修改用户名
rename user zorro to robin;
select user from mysql.user;
删除	
drop user robin;
修改用户密码
set password for 'zorro'@'%'= password('123'); password()函数加密
set password = password('123');修改当前用户



root密码丢失

方法一：

[root@local ~]# vim /etc/my.cnf

skip-grant-tables=1

mysql> select * from mysql.user\G

重置root口令
mysql> update mysql.user set authentication_string=password('123') where host='localhost' and user='root';

方法二：

[root@local ~]# /usr/local/mysql/bin/mysqld_safe --skip-grant-tables --skip-networking &

重置root口令
mysql> update mysql.user set authentication_string=password('123') where host='localhost' and user='root';



练习:
1.tom   local
2.jerry  %
设置密码 123456
删除

2.权限管理

查询用户权限
show grants for zorro \G
*************************** 1. row ***************************
Grants for zorro@%: GRANT USAGE ON *.* TO 'zorro'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'

USAGE表示没有任何权限
连接测试
 mysql -u zorro -p123
ERROR 1045 (28000): Access denied for user 'zorro'@'localhost' (using password: YES)
失败

权限
MySQL存取控制包含2个阶段：
	阶段1：服务器检查是否允许你连接。 
	阶段2：假定你能连接，服务器检查你发出的每个请求。看你是否有足够的权限实施它。例如，如果你从数据库表中选择(select)行或从数据库删除表，服务器确定你对表有SELECT权限或对数据库有DROP权限。 


授权grant
命令格式
grant 权限 on 库.表 to 用户@主机 [密码]
grant select on hr.* to zorro@'localhost';
show grants for zorro \G
*************************** 1. row ***************************
Grants for zorro@%: GRANT USAGE ON *.* TO 'zorro'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
*************************** 2. row ***************************
Grants for zorro@%: GRANT SELECT ON `hr`.* TO 'zorro'@'%'

移除权限revoke
命令格式
revoke 权限 on 库.表 from 用户@主机;
revoke select on hr.* from zorro'localhost';

远程主机授权
grant all on hr.* to zorro@'192.168.1.129' identified by '123';
grant all on hr.* to zorro@'%' identified by '123';


grant和revoke可在几个层次上控制访问权限
整个服务器 grant all 和 revoke all
整个数据库 on databases.*
grant select,insert on hr.* to robin@'localhost' identified by '123';
特定的表 on database.table;
grant select,insert on hr.tt to tom@'localhost' identified by '123';



其他方法：
mysql> INSERT INTO user (Host,User,Password)  VALUES('localhost','dummy',password());
mysql> FLUSH PRIVILEGES;



练习:
1.创建帐号zorro 允许从本机和任意位置登录
create user zorro@'%';
create user zorro@'%' identified by '123';
create user zorro@'localhost' identified by '123';
2.修改zorro名字为king
rename user zorro@'%' to king@'%';
rename user zorro@'localhost' to king@'localhost';
3.设置king用户的密码位123  
set password for king@'localhost'=password('123');
set password for king@'%'=password('123');
4.以king帐号登录到mysql数据库 设置密码位abc
set password=password('123');


重置root密码
1.停止mysql(pkill mysql)
2./usr/local/mysql/bin/mysqld_safe --user=mysql --skip-grant-tables &
3.update mysql.user set password=password('123') where user='root' and host='localhost';
4.停止mysql   (pkill mysql)
5./usr/local/mysql/bin/mysqld_safe --user=mysql &
6.正常登录

跳过授权
vim /etc/my.cnf
[mysqld]
skip-grant-tables



用户信息mysql.user存储所有用户信息,权限信息分布不同的表中

abc1  abc2 abc3  abc4
grant all on *.* to abc1@localhost identified by '123';
abc1 权限保存在 mysql.user

grant all on robin.* to abc2@localhost identified by '123';
abc2 权限保存在 mysql.db

grant all on db.test20 to abc3@localhost identified by '123';
abc3 权限保存在 mysql.tables_priv

grant select(name) on db.test20 to abc4@localhost identified by '123';
abc4 权限保存在 mysql.columns_priv



----------------------------------------------------------------------

create user robin;				添加帐号
set password for robin=password('123');		设置密码
create user zorro identified by '123';		创建帐号同时设置密码
rename user zorro to newzorro;			修改帐号名字
drop user newzorro;				删除帐号

set password=password('123');			设置当前帐号密码

root密码丢失
实验环境
删除数据目录
重新初始化
管理密码为空(直接登录)

重置root密码
/usr/local/mysql/bin/mysqld_safe --user=mysql --skip-grant-tables &
--skip-grant-tables  跳过授权表不进行验证.
mysql> update mysql.user set password=password('123') where user='root' and host='localhost';  更新密码

pkill mysql
service mysqldd restart

授权
1.是否能连接数据库  localhost   %
2.验证帐号密码

1.能否连接数据库
第一部分 本地来源
第二部分 远程来源
create user zorro@'%' identified by '123';
select user,password,host from mysql.user;

create user zorro@'localhost' identified by '123';

2,授权
grant all on db.* to zorro@'localhost';     db库所有表具有所有权限
grant select,insert on db.t5 to robin@'localhost' identified by '123';  授权同时创建帐号

回收权限revoke all on db.* from zorro@'localhost';

  *.*     mysql.user
  db.*    mysql.db
  db.t5   mysql.tables_priv
  db.t5(id)	  mysql.columns_priv	


grant all on *.* to abc1@localhost identified by '123';
abc1 权限保存在 mysql.user

grant all on db.* to abc2@localhost identified by '123';
abc2 权限保存在 mysql.db

grant all on db.test20 to abc3@localhost identified by '123';
abc3 权限保存在 mysql.tables_priv

grant select(name) on db.test20 to abc4@localhost identified by '123';
abc4 权限保存在 mysql.columns_priv

七、备份恢复

备份一般分为物理备份(物理文件)和逻辑备份(sql语句)
物理备份：只要备份物理文件速度快不跨平台 linux windos
逻辑备份：sql语句的备份速度慢跨平台 linxu sql — > windos

还可以分为离线备份(物理)和在线备份(逻辑)

1.物理备份（了解即可，不用）

1.停掉数据库,cp相应的数据库目录或者表文件
myIsam引擎 备份表拷贝以下文件
tt.frm 表结构
tt.MYD 数据
tt.MYI 索引


物理备份
关闭mysql
cp /usr/local/mysql/data/db/{t4.frm,t4.ibd} /backup/
cp /usr/local/mysql/data/ib* /backup/
drop table t4
恢复t4
cp /backup/t4.* /usr/local/mysql/data/db
chown mysql.mysql /usr/local/mysql/data/db/t4.*
cp /backup/ib* /usr/local/mysql/data/
/bin/cp /backup/ib* /usr/local/mysql/data/   覆盖掉
启动mysql
select * from db.t4;
              
ibdata1 数据
ib_logfile0\ 
	    > 事务信息
ib_logfile1/

物理在线备份 myisam
1.锁定表 lock table t1 read;
2.cp 物理文件 备份
3.unlock tables;

工具mysqlhotcopy 适用于myIsam引擎 需要perl-DBD-MySQL包
/usr/local/mysql/bin/mysqlhotcopy -u root -p 123 -S /tmp/mysql.sock db /tmp/

ls /tmp/hr

vim /etc/my.cnf
[mysqlhotcopy]
socket=/tmp/mysql.sock
user=root
password=123

[mysql]
user=root
password=123

2.逻辑备份

逻辑备份 不区分引擎
mysqldump -u root -p123 --all-databases > /all.sql 全备

删掉data目录
初始化数据库(修改权限)
mysql -u root < all.sql
mysqldump -u root -p123 --databases hr >> hr.sql 备一个库
mysql -u root < hr.sql
mysqldump -u root -p123 --databases hr db > hr.db.sql备多个库
mysql -u root <hr.db.sql
mysqldump -u root -p123 hr emp > emp.sql 备份表
mysql -u root -p123 hr < emp.sql 恢复



实验过程:
先做完全备份
模拟损坏:
rm -rf /usr/local/mysql/data

修复:
1.杀掉进程
pkill mysqld

2.创建目录data 修改拥有者和所属组 并且初始化
mkdir /usr/local/mysql/data
chown -R mysql.mysql /usr/local/mysql/
初始化：
源码包的
/usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data
rpm包的  (rpm包的都在/usr/bin下或者/usr/sbin下)
mysqld --initialize --user=mysql --datadir=/var/lib/mysql

3.启动mysql
systemctl restart mysqldd

4.修改密码
mysql -u root -p'密码'
set password=password('Robin_123');
5.恢复数据
mysql -uroot -p123 < /backup/all.sql

3.导入导出

导入导出
show global variables like '%secure%';

• 当secure_file_priv的值为null ，表示限制mysqld 不允许导入|导出
• 当secure_file_priv的值为/tmp/ ，表示限制mysqld 的导入|导出只能发生在/tmp/目录下
• 当secure_file_priv的值没有具体值时，表示不对mysqld 的导入|导出做限制

/etc/my.cnf
secure_file_priv =  



表的导出和导入
select * from t9;
+------+------+
| id   | name |
+------+------+
|    1 | aa   |
|    1 | aa   |
+------+------+
mysql> select * from t9 into outfile '/backup/a.txt';
shell> cat a.txt
1       aa
1       aa
添加
2	bb
3	cc
mysql> load data infile '/backup/a.txt' into table t9 fields terminated by '\t' lines terminated by '\n' ;
mysql> select * from t9;
+------+------+
| id   | name |
+------+------+
|    1 | aa   |
|    1 | aa   |
|    1 | aa   |
|    1 | aa   |
|    2 | bb   |
|    3 | cc   |
+------+------+

指定列分隔符和行分隔符
select * from emp into outfile '/backup/c.txt' fields terminated by ',' lines terminated by '\n';

cat c.txt
1,aa
1,aa
1,aa
1,aa
2,bb
3,cc

4.lvm的快照(mysql备份)

1.将lv挂载mysql数据目录data
停止mysqld
lvm格式化成ext4格式
挂载lvm
初始化mysql
启动mysqld

2.保证数据完整(全备 完全恢复)
进行数据恢复

3.锁库
mysql> FLUSH TABLES WITH READ LOCK;
4.创建快照
[root@robin data]# lvcreate -L 300M -s -n /dev/vgmysql/lvmysql-snampshot  /dev/vgmysql/lvmysql
5.解锁
mysql> unlock tables;
6.备份

mount /dev/vgmysql/lvmysql-snampshot /opt
[root@robin opt]# tar -cvf /backup/mysql.bak.tar /opt/*
7.删除快照
[root@robin /]# umount /opt/
[root@robin /]# lvremove /dev/vgmysql/lvmysql-snampshot

恢复
1.确定mysql进程结束
[root@robin backup]# killall -9 mysqld
2.解压恢复
[root@robin backup]# tar -xvf /backup/mysql.bak.tar -C /usr/local/mysql/data/

问题:
1.锁表(时间不好估算)
2.快照大小



脚本实现：
先挂载好lv，初始化好mysql

#!/bin/bash
mysql -u root -p123 -e 'FLUSH TABLES WITH READ LOCK;system 
lvcreate -L 300M -s -n /dev/vg-mysql/lv-mysql-snampshot  /dev/vg-mysql/lv-mysql;unlock tables;'
mount /dev/vg-mysql/lv-mysql-snampshot /opt
cd /opt
tar -cvf /backup/mysql.bak.tar ./*
cd /root
umount /opt/
lvremove -y /dev/vg-mysql/lv-mysql-snampshot

5.二进制日志的使用log-bin

binlog日志是把数据库的每一个变化都记载到一个专用的文件里,这种文件叫日志文件,mysql默认只打开错误日志文件,因为过多的日志会影响系统的处理性能
启用方法
1.mysqld_safe --log-bin --user=mysql&
2.vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=master 日志名称     #可以指定绝对路径，不写则默认在mysql数据目录下
log-bin-index=master
max-binlog-size=防止binlog大小

查看是否启动了binlog日志
mysql> set binlog_rows_query_log_events=1;

查看binlog文件内容
mysqlbinlog -v /usr/local/mysql/var/master.000001

切换日志 (重新启动会产生新日志 mysqladmin flush-logs)
mysql>flush logs
或者
[root@local ~]# mysqladmin -u root -p123 flush-logs

使用binlog-do-db可以指定记录的数据库；
使用binlog-ignore-db可以指定不记录的数据库。
注意的是：binlog-do-db和binlog-ignore-db一次只指定一个数据库，指定多个数据库需要多个语句。而且，MySQL会将所有的数据库名称改成小写，在指定数据库时必须全部使用小写名字，否则不会起作用。

1、Statement Level模式（早期默认）
    简介：每一条会修改数据的sql都会记录到master的bin-log中。Slave在复制的时候sql线程会解析成和原来master端执行过的相同语句来执行。
优点：不需要记录每一行数据的变化，减少bin-log的日志量，节约IO，提高性能。因为他只记录在master上所执行语句的细节，以及执行语句时候的上下文的信息。
缺点：很多新功能的加入在复制的时候容易导致出现问题。
delete from t1;

日志中直接显示语句
mysql> show binlog events\G
[root@localhost data]# mysqlbinlog /usr/local/mysql/data/mysql.000001


       
2、Row Level 模式：(默认)
简介:日志中会记录成每一行数据被修改的模式，然后再slave 端在对相同的数据进行修改.
 优点：在row level模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息。仅仅只需要记录那一条记录被修改了。所以row level的日志内容会非常清楚记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特点情况下的存储过程，或function 以及triggeer的调用和触发无法被正确复制的问题.

缺点：所有执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，可能会产生大量的日志呢。
delete from t1;
mysql> show global variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

系统查看日志信息
[root@localhost data]# mysqlbinlog -v /usr/local/mysql/data/mysql.000001
mysql中查看语句
mysql> set binlog_rows_query_log_events=1;
mysql> show binlog events\G
*************************** 10. row ***************************
   Log_name: mysql.000001
        Pos: 574
 Event_type: Rows_query
  Server_id: 1
End_log_pos: 653
       Info: # insert into t10 values(1,'robin'),(2,'zorro'),(3,'tom')
*************************** 11. row ***************************

3、Mixed （前两种的混合模式）：根据执行的每一条具体的sql语句来区分对待记录日志的形式；

如何设置模式:
[mysqld]
innodb_file_per_table=1
secure_file_priv =
server-id=1
log_bin=mysql
log_bin_index=mysql
binlog_format={statement|row|mixed}


binlog日志的切换
情况1:
mysqld服务重新启动
情况2:
mysql> flush logs;
情况3:
[root@localhost data]# mysqladmin -u root -p123 'flush-logs'


查看其他日志
mysql> show binlog events in "mysql.000003"\G


模拟恢复:
日志和数据分离
[mysqld]
innodb_file_per_table=1
secure_file_priv =
server-id=1
log_bin=/mysql-log/master
log_bin_index=/mysql-log/master
binlog_format=row
log-error=/mysql-log/localhost.err

[root@localhost data]# mkdir /mysql_log
[root@localhost data]# chown -R mysql.mysql /mysql_log


情况1:物理删除
1.完全备份做好
2.开启binglog日志
   创建数据库zorro
   创建表t1
   插入数据3条
   flush logs;
   插入数据2条
   flush logs;

模拟错误:
rm -rf /usr/local/mysql/data/*

恢复:
1.恢复全备

停止mysql进程 
关闭binlog日志
[root@localhost data]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data
登录并修改秘密(默认密码来自日志文件)
mysql> set password=password("Robin_123");

恢复all.sql
[root@localhost data]# mysql -u root -pRobin_123 < /backup/all.sql
[root@localhost data]# systemctl restart mysqldd

binlog日志恢复数据
[root@localhost binlog]# mysqlbinlog -v /binlog/master.000001 | mysql -u root -p123 
[root@localhost binlog]# mysqlbinlog -v /binlog/master.000002 | mysql -u root -p123 
[root@localhost binlog]# mysqlbinlog -v /binlog/master.000003 | mysql -u root -p123 

确认恢复成功
打开binlog,正常运行



逻辑删除
1.完全备份做好
2.开启binglog日志
   创建数据库hello
   创建表world
   插入数据3条
   删除2条数据
   flush logs;
   删除所有数据
   插入数据2条
   删除1条数据 
   flush logs;
   插入2条数据
   
恢复所有插入数据

完全恢复:

停止mysql进程 
关闭binlog日志
[root@localhost data]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data
登录并修改秘密(默认密码来自日志文件)
mysql> set password=password("Robin_123");

恢复all.sql
[root@localhost data]# mysql -u root -pRobin_123 < /backup/all.sql
[root@localhost data]# systemctl restart mysqldd

恢复数据
[root@localhost binlog]# mysqlbinlog --stop-position=1486 master.000001  | mysql -u root -p123
[root@localhost binlog]# mysqlbinlog --start-datetime="2018-04-24 14:52:28" master.000002  | mysql -u root -p123
[root@localhost binlog]# mysqlbinlog master.000003  | mysql -u root -p123



实验
--------------------------------------------------------------
数据库全备
1.开启binlog日志  将binlog和binglog索引文件  放到/backup下
2.创建库 创建表
3.刷新日志 flush logs
4.插入数据 
5.物理删除库
 
恢复
1.杀mysql进程(关闭2进制binlog)
2.初始化数据库，重启数据库（先关闭binlog）
3.恢复全备
4.恢复binlog日志,删除已有日志
5.打开binlog
---------------------------------------------------------------

八、MySQL AB复制

1.MySQL AB复制

首先我们先介绍什么是MySQL AB复制。
AB复制又称主从复制，实现的是数据同步。如果要做MySQL AB复制，数据库版本尽量保持一致。如果版本不一致，从服务器版本高于主服务器，但是版本不一致不能做双向复制。MySQL AB复制有什么好处呢？有两点，第一是解决宕机带来的数据不一致，因为MySQL AB复制可以实时备份数据；第二点是减轻数据库服务器压力，这点很容易想到，多台服务器的性能一般比单台要好。但是MySQL AB复制不适用于大数据量，如果是大数据环境，推荐使用集群。

Mysql复制（replication）是一个异步的复制，从一个Mysql 实例（Master）复制到另一个Mysql 实例（Slave）。实现整个主从复制，需要由Master服务器上的IO进程，和Slave服务器上的Sql进程和IO进程共从完成。要实现主从复制，首先必须打开Master端的binary log（bin-log）功能，因为整个 MySQL 复制过程实际上就是Slave从Master端获取相应的二进制日志，然后再在自己slave端完全顺序的执行日志中所记录的各种操作。（二进制日志几乎记录了除select以外的所有针对数据库的sql操作语句）
主从复制的基本过程如下：
1)、Mysql Slave端的IO进程连接上Master，向Master请求指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；
2)、Master接收到来自Slave的IO进程的请求后，负责复制的IO进程根据Slave的请求信息，读取相应日志内容，返回给Slave 的IO进程。并将本次请求读取的bin-log文件名及位置一起返回给Slave端。
3)、Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”；
4)、Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行。

1.设置主机名，彼此解析主机名
2.关闭防火墙
3.selinux关闭
4.设置时间同步

主服务器配置
A ip  192.168.33.132
1.A全备份
mysqldump -u root -S /var/lib/mysql/mysql.sock --all-databases >> /tmp/all.sql

-s指定套接字文件

[root@master ~]# mysqldump -u root -p123 --all-databases > /backup/all2.sql

将全备拷贝到从机,并且从机恢复数据

2.开启binlog日志
vim /etc/my.cnf
[mysqld]
server-id=1
log_bin=/backup/master
log_bin_index=/backup/master
[root@master backup]# systemctl restart mysqld

3.授权B服务器 可以传输binlog日志
mysql> grant replication slave on *.* to slave@'192.168.33.137' identified by 'Robin_123';



从服务器配置  
B   IP   192.168.33.137
groupadd -g 27 mysql
useradd -g 27 -u 27 -s /sbin/nologin mysql
mkdir /var/lib/mysql
chown -R mysql.mysql  /var/lib/mysql

1.测试slave是否能正常链接
[root@slave ~]# mysql -u slave -p'Robin_123' -h 192.168.33.132;

2.拷贝A服务器的全备,并且恢复
[root@master backup]# scp /backup/all2.sql root@192.168.33.137:/root/
[root@slave ~]# mysql -u root -p'Robin_123' < /root/all2.sql 

3.配置A服务器连接地址及用户信息
修改配置文件
vim /etc/my.cnf
[mysqld]
server-id=2

[root@slave ~]# mysql -u root -p123
mysql> stop slave;
mysql> change master to
-> master_host='192.168.33.132',
-> master_user='slave',
-> master_password='123',
-> master_port=3306,
-> master_log_file='master.000001',
-> master_log_pos=748;   #在主服务器上查看show master status\G
mysql> start slave;


测试
A服务器
mysql> show master status\G

B服务器
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

A服务器做出数据改动 查看从服务器是否有同步的变化



练习:
主----从(主)------从
log-slave-updates 中间这台作用:中继日志内容写入到binlog日志

Binlog_Do_DB=robin    #复制robin库
Binlog_Ignore_DB=db   #忽略db库
binlog_ignore_db=test

2.M-M互为主从

主服务器
开启binlog日志 
[mysqld]
log-bin=master
log-bin-index=master
server-id=1
1.全备:
[root@localhost data]# mysqldump -u root -p123 --all-databases > /tmp/all.sql

2.将全备拷贝给从机，并在从机完全恢复，保证主从数据的一致性

3.授权从机可以从主机复制数据
mysql> grant replication slave on *.* to slave@'192.168.10.201' identified by '123';

4.查看主服务器日志状态：
mysql> show master status\G
*************************** 1. row ***************************
             File: master.000001
         Position: 120
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

从服务器
1.完全恢复
[root@localhost mysql]# mysql -u root < /root/all.sql

2.测试登陆主服务器
[root@localhost mysql]# mysql -u slave -p123 -h 192.168.10.200

3.配置文件
[root@localhost data]# cat /etc/my.cnf 
[mysqld]
server-id=2

4.配置连接服务器
mysql> change master to
    -> master_host='192.168.10.200',
    -> master_user='slave',
    -> master_password='123',
    -> master_log_file='master.000001',
    -> master_log_pos=120;
mysql> start slave；
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

调换两台主机的身份再做一次
从服务器：
开启binlog日志：
[root@localhost data]# cat /etc/my.cnf 
[mysqld]
server-id=2
log-bin=slave
log-bin-index=slave

给主服务器授权
mysql> grant replication slave on *.* to master@'192.168.33.132' identified by 'Robin_123';


主服务器：
mysql> change master to 
    -> master_host='192.168.33.137',
    -> master_user='master',
    -> master_password='Robin_123',
    -> master_log_file='slave.000001',
    -> master_log_pos=452;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;

mysql> show slave status\G

测试：


change master to master_host='172.16.60.246',master_user='slave',master_password='123',master_port=3306,master_log_file='master.000003',master_log_pos=312;

mysql> reset slave all;  #清空配置

3.keepalived+mysql

keepalived是什么
keepalived是集群管理中保证集群高可用的一个服务软件，其功能类似于heartbeat，用来防止单点故障。(HA)

keepalived工作原理

keepalived是以VRRP协议为实现基础的，VRRP全称Virtual Router Redundancy Protocol，即虚拟路由冗余协议。
虚拟路由冗余协议，可以认为是实现路由器高可用的协议，即将N台提供相同功能的路由器组成一个路由器组，这个组里面有一个master和多个backup，master上面有一个对外提供服务的vip（该路由器所在局域网内其他机器的默认路由为该vip），master会发组播，当backup收不到vrrp包时就认为master宕掉了，这时就需要根据VRRP的优先级来选举一个backup当master。这样的话就可以保证路由器的高可用了。
keepalived主要有三个模块，分别是core、check和vrrp。core模块为keepalived的核心，负责主进程的启动、维护以及全局配置文件的加载和解析。check负责健康检查，包括常见的各种检查方式。vrrp模块是来实现VRRP协议的。

在mysql互主的基础上配置keepalived(两台mysql都要安装)

root@localhost smb]# tar -xvf keepalived-1.2.24.tar.gz -C /usr/local/src/
[root@localhost ~]# cd /usr/local/src/keepalived-1.2.24/
[root@localhost keepalived-1.2.24]#./configure --prefix=/ --mandir=/usr/local/share/man/
[root@localhost keepalived-1.2.24]# make
[root@localhost keepalived-1.2.24]# make install
[root@localhost keepalived-1.2.24]# cd /etc/keepalived/
! Configuration File for keepalived

global_defs {                                           #全局定义主要设置 keepalived 的通知机制和标识
   notification_email {
        root@localhost
   }
   notification_email_from keepalived@localhost
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id test
}

vrrp_instance VI_1 {                    #VRRP(虚拟路由冗余协议)实例配置
    state MASTER                            #另一个 Director 标记为 BACKUP！！！
    interface eth0                              #实例绑定的网卡
    virtual_router_id 51                    #VRID 虚拟路由标识 00-00-5e-00-01-{VRID}
    priority 150                                    #优先级高为master，master 至少要高于 backup 50 ！！！
    advert_int 1                                    #检查间隔
    authentication {
        auth_type PASS                          验证:主备之间做身份验证  主备之间一定一致
        auth_pass 1111
    }
    virtual_ipaddress {                         浮动ip
        192.168.10.222/24
    }
}

[root@localhost keepalived]# scp keepalived.conf 192.168.10.201:/etc/keepalived/
拷贝后,修改配置文件
	state BACKUP
	priority 100

2台mysql上，启动Keepalived服务
[root@localhost keepalived]# service keepalived start


测试2台mysql的故障转移

4.MySQL AB复制的半同步

Mysql 主从复制（半同步）
1.半同步复制
在说明半同步复制之前我们先来了解一下，什么是同步复制？同步复制：同步复制可以定义为数据在同一时刻被提交到一台或多台机器，通常这是通过众所周知的“两阶段提交”做到的。虽然这确实给你在多系统中保持一致性，但也由于增加了额外的消息交换而造成性能下降。使用MyISAM或者InnoDB存储引擎的MySQL本身并不支持同步复制，然而有些技术，例如分布式复制块设备（简称DRBD），可以在下层的文件系统提供同步复制，允许第二个MySQL服务器在主服务器丢失的情况下接管（使用第二服务器的复本）。了解了同步复制我们正下面来说一下，什么是半同步复制？
MYSQL 5.5开始，支持半自动复制。之前版本的MySQL Replication都是异步（asynchronous）的，主库在执行完一些事务后，是不会管备库的进度的。如果备库不幸落后，而更不幸的是主库此时又出现Crash（例如宕机），这时备库中的数据就是不完整的。简而言之，在主库发生故障的时候，我们无法使用备库来继续提供数据一致的服务了。Semisynchronous Replication(半同步复制)则一定程度上保证提交的事务已经传给了至少一个备库。Semi synchronous中，仅仅保证事务的已经传递到备库上，但是并不确保已经在备库上执行完成了。
此外，还有一种情况会导致主备数据不一致。在某个session中，主库上提交一个事务后，会等待事务传递给至少一个备库，如果在这个等待过程中主库Crash，那么也可能备库和主库不一致，这是很致命的。如果主备网络故障或者备库挂了，主库在事务提交后等待10秒（rpl_semi_sync_master_timeout的默认值）后，就会继续。这时，主库就会变回原来的异步状态。
MySQL在加载并开启Semi-sync插件后，每一个事务需等待备库接收日志后才返回给客户端。如果做的是小事务，两台主机的延迟又较小，则Semi-sync可以实现在性能很小损失的情况下的零数据丢失。
2.异步与半同步异同
默认情况下MySQL的复制是异步的，Master上所有的更新操作写入Binlog之后并不确保所有的更新都被复制到Slave之上。异步操作虽然效率高，但是在Master/Slave出现问题的时候，存在很高数据不同步的风险，甚至可能丢失数据。
MySQL5.5引入半同步复制功能的目的是为了保证在master出问题的时候，至少有一台Slave的数据是完整的。在超时的情况下也可以临时转入异步复制，保障业务的正常使用，直到一台slave追赶上之后，继续切换到半同步模式。

注：mysql5.5半同步插件是由谷歌提供，具体位置/usr/local/mysql/lib/plugin/下，一个是master用的semisync_master.so，一个是slave用的semisync_slave.so，下面我们就来具体配置一下。

配置：（先做AB异步复制,再改成半同步）
master端：
安装半同步master插件
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
开启半同步插件
mysql> set global rpl_semi_sync_master_enabled=1;
设置同步超时时间
mysql> set global rpl_semi_sync_master_timeout=1000;

修改配置文件
[root@localhost data]# cat /etc/my.cnf
[mysqld]
server-id=1
log-bin=master
log-bin-index=master
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000

启动服务：
service mysqld restart


slave端配置：
安装半同步slave插件：
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
开启半同步插件
mysql> set global rpl_semi_sync_slave_enabled=1;



修改配置文件：
[root@localhost data]# cat /etc/my.cnf 
[mysqld]
server-id=2
rpl_semi_sync_slave_enabled=1

重新启动服务：
service  mysqld restart


查询状态
master
mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%'; 
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+



slave：
mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%'; 
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | OFF   |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
| Rpl_semi_sync_slave_status                 | ON    |
+--------------------------------------------+-------+

测试：
master：
mysql> create table test(id int);
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+--------------+
| Tables_in_db |
+--------------+
| t1           |
| test         |
+--------------+
2 rows in set (0.00 sec)

mysql> insert into test values(1),(2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0


模拟错误
slave：

mysql> STOP SLAVE IO_THREAD; 
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%'; 
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | OFF   |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
| Rpl_semi_sync_slave_status                 | OFF   |
+--------------------------------------------+-------+
15 rows in set (0.00 sec)

master：
mysql> create table user1 (id int(10)); 
Query OK, 0 rows affected (1.02 sec)
创建时间会变长

5.GTID复制

一、GTID概念介绍
GTID即全局事务ID (global transaction identifier), 其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的ID。GTID最初由google实现，官方MySQL在5.6才加入该功能。mysql主从结构在一主一从情况下对于GTID来说就没有优势了，而对于2台主以上的结构优势异常明显，可以在数据不丢失的情况下切换新主。使用GTID需要注意: 在构建主从复制之前，在一台将成为主的实例上进行一些操作（如数据清理等），通过GTID复制，这些在主从成立之前的操作也会被复制到从服务器上，引起复制失败。也就是说通过GTID复制都是从最先开始的事务日志开始，即使这些操作在复制之前执行。比如在server1上执行一些drop、delete的清理操作，接着在server2上执行change的操作，会使得server2也进行server1的清理操作。
GTID实际上是由UUID+TID (即transactionId)组成的。其中UUID(即server_uuid) 产生于auto.conf文件(cat /data/mysql/data/auto.cnf)，是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增，所以GTID能够保证每个MySQL实例事务的执行（不会重复执行同一个事务，并且会补全没有执行的事务）。GTID在一组复制中，全局唯一。

GTID的组成

GTID = source_id:transaction_id
source_id，用于鉴别原服务器，即mysql服务器唯一的的server_uuid，由于GTID会传递到slave，所以也可以理解为源ID。
transaction_id，为当前服务器上已提交事务的一个序列号，通常从1开始自增长的序列，一个数值对应一个事务。
示例：
3E11FA47-71CA-11E1-9E33-C80AA9429562:23
前面的一串为服务器的server_uuid，即3E11FA47-71CA-11E1-9E33-C80AA9429562，后面的23为transaction_id

GTID的工作原理

1、当一个事务在主库端执行并提交时，产生GTID，一同记录到binlog日志中。
2、binlog传输到slave,并存储到slave的relaylog后，读取这个GTID的这个值设置gtid_next变量，即告诉Slave，下一个要执行的GTID值。
3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有该GTID。
4、如果有记录，说明该GTID的事务已经执行，slave会忽略。
5、如果没有记录，slave就会执行该GTID事务，并记录该GTID到自身的binlog，
在读取执行事务前会先检查其他session持有该GTID，确保不被重复执行。
6、在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。

配置
1、主： 
[mysqld] 
#GTID:
server_id=135                #服务器id
gtid_mode=on                 #开启gtid模式
enforce_gtid_consistency=on  #强制gtid一致性，开启后对于特定create table不被支持
#binlog 
log_bin=master-binlog 
log-slave-updates=1 
binlog_format=row            #强烈建议，其他格式可能造成数据不一致*

从： 
[mysqld] #GTID: 
gtid_mode=on 
enforce_gtid_consistency=on 
server_id=143 
#binlog
log-bin=slave-binlog 
log-slave-updates=1
binlog_format=row      #强烈建议，其他格式可能造成数据不一致 
#relay log
skip_slave_start=1

九、多源复制

使用多源复制的考虑：
1、灾备作用：将各个库汇总在一起，就算是其他库都挂了（整个机房都无法连接了），还有最后一个救命稻草；
2、备份：直接在这个从库中做备份，不影响线上的数据库；
3、减少成本：不需要每个库都做一个实例，也减少了DBA的维护成本；
4、数据统计：后期的一些数据统计，需要将所有的库汇总在一起。

实验环境：
1.关闭防火墙
2.关闭selinux
3.设置和解析主机名
192.168.0.13 master1.com
192.168.0.14 master2.com
192.168.0.11 slave.com


分别安装mysql5.7(略)

分别在master1 master2上建立不同的数据库已做测试
master1
mysql> create database test;
mysql> use test
mysql> create table test(name char(10));
mysql> insert into test values('robin');
mysql> insert into test values('zorro');

master2
mysql> create database db;
mysql> use db
mysql> create table t1(id int);
mysql> insert into t1 values(1);
mysql> insert into t1 values(2);

分别在master1  master2导出需要同步的数据库
master1
mysqldump -u root -pRobin_123 --databases test > /tmp/test.sql

master2
mysqldump -u root -pRobin_123 --databases db > /tmp/db.sql



分别在master1 master2上创建复制账号和密码
master1
[root@master1 backup]# vim /etc/my.cnf
server-id=1
log_bin=/backup/master1
log_bin_index=/backup/master1

mysql>grant replication slave on *.* to slave1@192.168.0.11 identified by "Slave_123";

master2
[root@master2 backup]# vim /etc/my.cnf
server-id=2
log_bin=/backup/master2
log_bin_index=/backup/master2

mysql>grant replication slave on *.* to slave2@192.168.0.11 identified by "Slave_123";

在slave节点测试
[root@web1 conf]# mysql -u slave1 -pSlave_123 -h 192.168.0.13
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

[root@web1 conf]# mysql -u slave2 -pSlave_123 -h 192.168.0.14
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.7.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 


修改（在slave中添加）
master_info_repository=TABLE
relay_log_info_repository=TABLE

master_info_repository
开启MTS功能后，务必将参数master_info_repostitory设置为TABLE，这样性能可以有50%~80%的提升。这是因为并行复制开启后对于元master.info这个文件的更新将会大幅提升，资源的竞争也会变大。在之前InnoSQL的版本中，添加了参数来控制刷新master.info这个文件的频率，甚至可以不刷新这个文件。因为刷新这个文件是没有必要的，即根据master-info.log这个文件恢复本身就是不可靠的。在
MySQL 5.7中，推荐master_info_repository设置为TABLE，来减少这部分的开销。
relay_log_info_repository 同理

vim /etc/my.cnf
master_info_repository=TABLE
relay_log_info_repository=TABLE

也可以在线修改
mysql> stop slave;
mysql> SET GLOBAL master_info_repository = 'TABLE';
mysql> SET GLOBAL relay_log_info_repository = 'TABLE';


将master1 master2备份的sql语句拷贝并导入slave数据库
拷贝
[root@web1 conf]# scp 192.168.0.13:/tmp/test.sql /tmp/
test.sql                                                   100% 1937     1.9KB/s   00:00    
[root@web1 conf]# scp 192.168.0.14:/tmp/db.sql /tmp/
db.sql                                                     100% 1898     1.9KB/s   00:00  

恢复
[root@web1 conf]# mysql -u root -pRobin_123 < /tmp/test.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@web1 conf]# mysql -u root -pRobin_123 < /tmp/db.sql 
mysql: [Warning] Using a password on the command line interface can be insecure


分别找出master1 和master2的pos位置
master1
mysql> show master status\G
*************************** 1. row ***************************
             File: master1.000001
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)


master2
mysql> show master status\G
*************************** 1. row ***************************
             File: master2.000001
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)


slave 
登录Slave进行同步操作，分别change master到两台Master服务器，后面以FOR CHANNEL 'CHANNEL_NAME'区分

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.13',MASTER_USER='slave1', MASTER_PASSWORD='Slave_123',MASTER_LOG_FILE='master1.000001',MASTER_LOG_POS=154 FOR CHANNEL 'master1';
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.14',MASTER_USER='slave2', MASTER_PASSWORD='Slave_123',MASTER_LOG_FILE='master2.000001',MASTER_LOG_POS=154 FOR CHANNEL 'master2';
Query OK, 0 rows affected, 2 warnings (0.02 sec)


正常启动后，可以查看同步的状态：执行SHOW SLAVE STATUS FOR CHANNEL 'channel_name'\G



验证数据是否同步
master1
insert into test values('king')；

master2
insert into t1 values('10000');

slave
mysql> select * from test.test;
+-------+
| name  |
+-------+
| robin |
| zorro |
| king  |
+-------+
3 rows in set (0.01 sec)

mysql> select * from db.t1;
+-------+
| id    |
+-------+
|   100 |
|   200 |
| 10000 |
+-------+
3 rows in set (0.00 sec)

十、Mycat

1.Mycat实现读写分离

准备三台虚拟机
1.关闭防火墙
2.关闭selinux
3.设置主机名和ip地址
vm-1   mycat.com    192.168.0.11
vm-2   mysql-a.com 192.168.0.12
vm-3   mysql-b.com 192.168.0.13

在mysql-a 和 mysql-b 主机上安装好mysql并创建好测试读写分离的库和表（略）



一、下载mycat   

mycat的官网网址 http://www.mycat.org.cn/,我使用的是mycat的1.6的[Linux](http://javascript:;)安装包
下载地址
https://github.com/MyCATApache/Mycat-download-new
下载mycat的安装包到 /tmp 目录下
[root@mycat ~]# cd /tmp
[root@mycat ~]# tar -zxvf /tmp/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/
[root@mycat ~]# ls -d /usr/local/mycat/
/usr/local/mycat/
[root@mycat ~]# ls /usr/bin/mycat
bin catlet conf lib logs version.txt



二、安装JDK并且配置环境变量
下载tmp/jdk-8u131-linux-x64.tar.gz

[root@mycat ~]# tar -xvf /tmp/jdk-8u131-linux-x64.tar.gz -C /usr/local/
[root@mycat ~]# ln -s /usr/local/jdk1.8.0_131/ /usr/local/java

[root@mycat ~]# vim /etc/profile
JAVA_HOME=/usr/local/java
PATH=$JAVA_HOME/bin:$PATH
export JAVA_HOME PATH

[root@mycat ~]# source /etc/profile
[root@mycat ~]# env |grep JAVA
JAVA_HOME=/usr/local/java



三、修改mycat配置文件
接下来修改mycat的配置文件
\#cd /usr/local/mycat/conf
\#ls
autopartition-long.txt    partition-hash-int.txt  sequence_db_conf.properties
cacheservice.properties   partition-range-mod.txt  sequence_time_conf.properties
dnindex.properties      router.xml        server.xml
ehcache.xml         rule.xml         wrapper.conf
index_to_charset.properties schema.xml
log4j.xml          sequence_conf.properties

配置文件非常多,配置项也远比amoeba复杂.如果只是简单配置在不同的服务器上进行读写分离只需要配置两个文件[server](http://javascript:;).xml 和 schema.xml



（一）先配置server.xml

[root@mycat ~]# vim /usr/local/mycat/conf/server.xml 
找到这一段
</system>
        <user name="test">
                <property name="password">test</property>
                <property name="schemas">TESTDB</property>
        </user>
 
        <user name="user">
                <property name="password">user</property>
                <property name="schemas">TESTDB</property>
                <property name="readOnly">true</property>
        </user>
 
这里配置了两个可以来连接的用户
用户1  test   密码test   给予了此用户TESTDB数据库的权限
用户2  user   密码user   给予了此用户TESTDB数据库的只读权限
 
注意这里的testdb 不一定是你数据库上的真实库名.可以任意指定.只要和接下来的schema.xml的配置文件中的库名统一即可



（二）接下来配置schema.xml

备份源schema.xml文件
[root@localhost ~]# cp /usr/local/mycat/conf/schema.xml /usr/local/mycat/conf/schema.xml.bak

配置
[root@mycat ~]# vim /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="[">http://io.mycat/">](http://io.mycat/)


        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        </schema>
        <dataNode name="dn1" dataHost="localhost1" database="db" />
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.0.12:3306" user="Mycat" password="Mycat_123">
                <readHost host="hostS2" url="192.168.0.13:3306" user="Mycat" password="Mycat_123" />
                </writeHost>
        </dataHost>
</mycat:schema >



（三）配置文件解释

1）<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
这里TESTDB 就是我们对外声称的我们有数据库的名称 必须和server.xml中的用户指定的数据库名称一致
添加一个dataNode="dn1" 是指定了我们这个库只在dn1上.没有进行分库

2） <dataNode name="dn1" dataHost="localhost1" database="db1" />
这里只需要改database的名字 db1 就是你真实数据库服务上的数据库名 .
根据你自己的数据库名进行修改.

3）<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

这里只需要配置三个地方  balance="1"与writeType="0" ,switchType=”1”
a. balance 属性负载均衡类型，目前的取值有 4 种：
1. balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。
2. balance="1"，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1 ->S1 ， M2->S2，并且 M1 与 M2 互为主备)，正常情况下， M2,S1,S2 都参与 select 语句的负载均衡。
3. balance="2"，所有读操作都随机的在 writeHost、 readhost 上分发。
4. balance="3"， 所有读请求随机的分发到 wiriterHost 对应的 readhost 执行,writerHost 不负担读压力，注意 balance=3 只在 1.4 及其以后版本有， 1.3 没有。

b. writeType 属性
负载均衡类型，目前的取值有 3 种：
1. writeType="0", 所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个
writeHost，重新启动后已切换后的为准，切换[记录](http://javascript:;)在配置文件中:dnindex.properties .
2. writeType="1"，所有写操作都随机的发送到配置的 writeHost。1.5后废弃，不推荐

c. switchType 属性
- -1 表示不自动切换
- 1 默认值，自动切换
- 2 基于MySQL 主从同步的状态决定是否切换

slaveThreshold="100"
开始支持 MySQL主从复制状态绑定的读写分离机制，让读更加安全可靠，配置如下： 
MyCAT心跳检查语句配置为 show slave status ，dataHost 上定义两个新属性： switchType="2" 与  
slaveThreshold="100"，此时意味着开启MySQL主从复制状态绑定的读写分离与切换机制，Mycat心跳机
制通过检测 show slave status 中的 "Seconds_Behind_Master", "Slave_IO_Running", 
"Slave_SQL_Running" 三个字段来确定当前主从同步的状态以及Seconds_Behind_Master 主从复制时延， 
当Seconds_Behind_Master>slaveThreshold 时，读写分离筛选器会过滤掉此Slave机器，防止读到很久之
前的旧数据，而当主节点宕机后，切换逻辑会检查Slave上的Seconds_Behind_Master是否为0，为0时则
表示主从同步，可以安全切换，否则不会切换。 


4）<heartbeat>select user()</heartbeat>
心跳检查语句

5）<writeHost host="hostM1" url="192.168.1.100:3306" user="root" password="Mycat_123" />
<readHost host="hostS1" url="192.168.1.101:3306" user="root" password="Mycat_123" />
</writeHost>
这里是配置的我们的两台读写服务器IP地址访问端口和 访问用户的用户名和密码



（四）启动mycat
/usr/local/mycat/bin/mycat start

测试：
使用test用户
[root@localhost ~]# mysql -u test -ptest -h 192.168.0.11 -P 8066
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.01 sec)

mysql> use TESTDB

mysql> select * from t1;
Empty set (0.01 sec)

mysql> insert into t1 values(1,'robin');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | robin |
+------+-------+
1 row in set (0.00 sec)

使用user用户
mysql> use TESTDB
mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | robin |
+------+-------+
1 row in set (0.01 sec)

mysql> insert into t1 values(2,'zorro');
ERROR 1495 (HY000): User readonly

也就是说test用户可读可写   user只读但是都在mysql-a这个库上

修改参数使读写可以分离



[root@localhost ~]# mysql -u test -ptest -h 192.168.0.11 -P 8066
mysql> use TESTDB
mysql> select * from t1;
Empty set (0.03 sec)

mysql> insert into t1 values(2,'zorro');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
Empty set (0.01 sec)



到mysql-a上验证
[root@mysql-a ~]# mysql -u root -p"Robin_123"
mysql> use db

mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | robin |
|    2 | zorro |
+------+-------+
2 rows in set (0.00 sec)




错误
通过mycat start启动失败，wrapper.log有如下报错信息：
STATUS | wrapper  | 2016/06/17 15:26:16 | Launching a JVM...
ERROR  | wrapper  | 2016/06/17 15:26:46 | Startup failed: Timed out waiting for a signal from the JVM.
ERROR  | wrapper  | 2016/06/17 15:26:46 | JVM did not exit on request, terminated
INFO   | wrapper  | 2016/06/17 15:26:46 | JVM exited on its own while waiting to kill the application.
STATUS | wrapper  | 2016/06/17 15:26:46 | JVM exited in response to signal SIGKILL (9).

后来通过startup_nowrap.sh，发现可以启动成功。
可修改conf/wrapper.conf中设置如下参数：

#设置超时时间为2小时wrapper.startup.timeout=7200

#设置心跳超时时间为1小时wrapper.ping.timeout=3600
重新通过mycat start启动，启动成功

2.Mycat垂直切分

垂直切分(分库)

一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面

在这里插入图片描述

环境根据上个实验
在mysql-a 和 mysql-b 上创建相同的库 但是存在着不同的表

master-a
[root@mysql-a ~]# mysql -u root -p"Robin_123"
mysql> create database test;
mysql> use test
mysql> create table emp(id int,name char(10));
mysql> insert into emp values(1,'robin');
mysql> select * from emp;
+------+-------+
| id   | name  |
+------+-------+
|    1 | robin |
+------+-------+
1 row in set (0.00 sec)


master-b
[root@mysql-b ~]# mysql -u root -p"Robin_123"
mysql> create database test;
mysql> use test
mysql> create table dpt(did int,d_name char(10));
mysql> insert into dpt values(100,'hr');
mysql> select * from dpt;
+------+--------+
| did  | d_name |
+------+--------+
|  100 | hr     |
+------+--------+

修改mycat配置文件：

[root@mycat ~]# vim /usr/local/mycat/conf/server.xml
<user name="robin">
                <property name="password">robin</property>
                <property name="schemas">test</property>
</user>


[root@mycat ~]# vim /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">


        <schema name="test" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
                <table name="emp" primaryKey="id" type="global" dataNode="dn1" />
                <table name="dpt" primaryKey="did" type="global" dataNode="dn2" />
        </schema>
        <dataNode name="dn1" dataHost="localhost1" database="test" />
        <dataNode name="dn2" dataHost="localhost2" database="test" />
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="dn1" url="192.168.0.12:3306" user="robin" password="Mycat_123">
                </writeHost>
        </dataHost>
        <dataHost name="localhost2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="dn2" url="192.168.0.13:3306" user="robin" password="Mycat_123">
                </writeHost>
        </dataHost>

</mycat:schema>


测试：
[root@localhost ~]# mysql -u robin -probin -h 192.168.0.11 -P 8066
mysql> show databases;
+----------+
| DATABASE |
+----------+
| test     |
+----------+
1 row in set (0.00 sec)

mysql> use test;

mysql> select * from emp;
+------+-------+
| id   | name  |
+------+-------+
|    1 | robin |
+------+-------+
1 row in set (0.00 sec)

mysql> select * from dpt;
+------+--------+
| did  | d_name |
+------+--------+
|  100 | hr     |
+------+--------+
1 row in set (0.00 sec)

mysql>

3.Mycat水平切分

水平切分相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分到一个数据库，而另外的某些行又切分到其他的数据库中

在这里插入图片描述

环境根据上个实验
在mysql-a 和 mysql-b 上创建相同的库 但是存在着相同的表

mysql> create database redhat;
mysql> use redhat
mysql> create table t1(id int primary key auto_increment,name char(10));
mysql> grant all privileges on redhat.* to robin@'192.168.0.11' identified by 'Mycat_123';

修改配置文件
[root@mycat ~]# vim /usr/local/mycat/conf/server.xml 
<user name="robin">
                <property name="password">robin</property>
                <property name="schemas">test</property>
</user>



[root@mycat ~]# vim /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="test" checkSQLschema="false" sqlMaxLimit="100">
                <table name="t1" primaryKey="id" dataNode="dn1,dn2" rule="rule1" />
        </schema>
        <dataNode name="dn1" dataHost="localhost1" database="redhat" />
        <dataNode name="dn2" dataHost="localhost2" database="redhat" />
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="dn1" url="192.168.0.12:3306" user="robin" password="Mycat_123">
                </writeHost>
        </dataHost>
        <dataHost name="localhost2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="dn2" url="192.168.0.13:3306" user="robin" password="Mycat_123">
                </writeHost>
        </dataHost>
</mycat:schema>


修改rule：
[root@mycat ~]# vim /usr/local/mycat/conf/rule.xml 
<tableRule name="rule1">
                <rule>
                        <columns>id</columns>
                        <algorithm>func1</algorithm>
                </rule>
</tableRule>

<!--安装id规则，将id除于1024然后取余，如果 余数落在0~512就将数据写到第一个数据库，如果是在512~1024就放到第二个数据库-->
<function name="func1" class="io.mycat.route.function.PartitionByLong">
                <property name="partitionCount">2</property>
                <property name="partitionLength">512</property>
</function>
<!--分成2片，每片的区间是512，必须要俩个相乘是1024-->


测试:
[root@localhost ~]# mysql -u robin -probin -h 192.168.0.11 -P 8066
mysql> show databases;
+----------+
| DATABASE |
+----------+
| test     |
+----------+
1 row in set (0.01 sec)

mysql> use test
mysql> 
mysql> show tables;
+----------------+
| Tables in test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

mysql> insert into t1 values(10,"aa");
ERROR 1064 (HY000): partition table, insert must provide ColumnList

mysql> insert into t1(id,name) values(10,"aa");

mysql> insert into t1(id,name) values(511,"aa");

mysql> insert into t1(id,name) values(512,"aa");

mysql> insert into t1(id,name) values(1024,"aa");

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|   10 | aa   |
|  511 | aa   |
| 1024 | aa   |
|  512 | aa   |
+------+------+
4 rows in set (0.11 sec)

master-a
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|   10 | aa   |
|  511 | aa   |
| 1024 | aa   |
+------+------+


master-b
mysql> select * from t1;
+-----+------+
| id  | name |
+-----+------+
| 512 | aa   |
+-----+------+

4.实验

在这里插入图片描述

先准备好环境（Mycat和mysql），对db1中的user1表做分库，对db2和db3中的user2和user3表做分表，再开3台虚拟机做读写分离，然后做主从（这3台可以用一台）

准备数据库：
mysql> create database test;
mysql> use test
db1中：
mysql> create table user1(id int, name char(10));
db2和db3中：
mysql> create table user2(id int, name char(10));
mysql> create table user3(id int, name char(10));

准备mycat：
参照之前Mycat环境的布置
配置server.xml
[root@mycat ~]# vim /usr/local/mycat/conf/server.xml 
找到这一段
</system>
        <user name="test">
                <property name="password">test</property>
                <property name="schemas">TESTDB</property>
        </user>
 
        <user name="user">
                <property name="password">user</property>
                <property name="schemas">TESTDB</property>
                <property name="readOnly">true</property>
        </user>


配置schema.xml
[root@mycat ~]# vim /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100">
                <table name="user1" primaryKey="id" type="global" dataNode="dn1" />
                <table name="user2" primaryKey="id"  dataNode="dn2,dn3" rule="rule1" />
                <table name="user3" primaryKey="id"  dataNode="dn2,dn3" rule="rule1" />
        </schema>
        <dataNode name="dn1" dataHost="localhost1" database="test" />
        <dataNode name="dn2" dataHost="localhost2" database="test" />
        <dataNode name="dn3" dataHost="localhost3" database="test" />
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="dn1" url="192.168.33.140:3306" user="Mycat" password="Mycat_123">
                <readHost host="hostS2" url="192.168.33.143:3306" user="Mycat" password="Mycat_123" />
                </writeHost>
        </dataHost>
        <dataHost name="localhost2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="dn2" url="192.168.33.141:3306" user="Mycat" password="Mycat_123">
                <readHost host="hostS2" url="192.168.33.143:3306" user="Mycat" password="Mycat_123" />
                </writeHost>
        </dataHost>
        <dataHost name="localhost3" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="dn3" url="192.168.33.142:3306" user="Mycat" password="Mycat_123">
                <readHost host="hostS2" url="192.168.33.143:3306" user="Mycat" password="Mycat_123" />
                </writeHost>
        </dataHost>
</mycat:schema>

进行测试：和之前3个实验相同

做主从，进行多源复制
先将3主的库备份到从服务器中
在4台mysql服务器之间做主从
3主：
[root@master1 ~]# vim /etc/my.cnf
server-id=1      #注意要设置不一样
log_bin=/backup/master1
log_bin_index=/backup/master1
重启mysql
授权
mysql>grant replication slave on *.* to master1@192.168.0.11 identified by "Master_123";
在slave节点测试
[root@web1 conf]# mysql -u master1 -pMaster_123 -h 192.168.0.13


1从：
[root@master1 ~]# vim /etc/my.cnf
server-id=1      #注意要和其他3台不一样
master_info_repository=TABLE
relay_log_info_repository=TABLE

mysql> CHANGE MASTER TO MASTER_HOST='192.168.33.140', MASTER_USER='master1', MASTER_PASSWORD='Master_123', MASTER_LOG_FILE='master1.000001',MASTER_LOG_POS=453 FOR CHANNEL 'master1';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> CHANGE MASTER TO MASTER_HOST='192.168.33.141', MASTER_USER='master2', MASTER_PASSWORD='Master_123', MASTER_LOG_FILE='master2.000001',MASTER_LOG_POS=453 FOR CHANNEL 'master2';
Query OK, 0 rows affected, 2 warnings (0.10 sec)

mysql> CHANGE MASTER TO MASTER_HOST='192.168.33.142', MASTER_USER='master3', MASTER_PASSWORD='Master_123', MASTER_LOG_FILE='master3.000001',MASTER_LOG_POS=453 FOR CHANNEL 'master3';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

十一、LAMP

一.apache  php
yum install httpd php php-mysql

vim /etc/httpd/conf.modules.d/10-php.conf
<IfModule prefork.c>
  LoadModule php5_module modules/libphp5.so
</IfModule>
[root@mycat ~]# ls /etc/httpd/modules/libphp5.so
/etc/httpd/modules/libphp5.so

启动服务
systemctl restart httpd

测试页
vim /var/www/html/index.php
<?php
        phpinfo()
?>

二.安装mysql
略(源码  rpm)

十二、MySQL事务

1、事务

事务可以是一个非常简单的SQL构成，也可以是一组复杂的SQL语句构成。事务是访问并且更新数据库中数据的一个单元，在事务中的操作，要么都修改，要么都不做修改，这就是事务的目的，也是事务模型区别于其他模型的重要特征之一。

事物的支持
事务处理是一种机制,用来管理成批执行的sql语句,以保证数据库不包含不完整的操作结果,他们或者为整体执行完成,或者完全不执行(如果没有错误发生整组语句提交到数据库,如果发生错误,则进行回退,以保证数据的安全)

事务 transaction 指定一组sql语句
回退 rollback 撤销指定的sql语句(只能回退insert delete update语句)
提交 commit 提交未存储的sql语句
保留点 savepoint 事务处理中设置的临时占位符你可以对它发布回退(与整个事务回退不同)

如果用drop ，create，alter 则强行关闭并保存

事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同
事务的实现

重做日志（redo）,用来实现事务的持久性，有两部分组成，一是内存中的重做日志，一个是硬盘上的重做日志文件。 innodb是支持事务的存储引擎，通过日志先行WAL，来实现数据库的事务的特性，在一个事务提交的时候，并不是直接对内存的脏数据进行落盘，而是先把重做日志缓冲中的日志写入文件，然后再提交成功。这样保证了即使在断电的情况下，依然可以依靠redo log进行数据的恢复与重做。只要是提交的事务，在redo中就会有记录，数据库断电后重启，数据库会对提交的事务，但是没有写入硬盘的脏数据，利用redo来进行重做。

还要一个保证事务的是undo，undo有两个作用：实现事务的回滚、实现mvcc的快照读取

redo是物理日志，计算页的物理修改操作

undo是逻辑记录，记录了行的操作内容
MySQL设置存储引擎的3种方法
- 在启动配置文件中设置服务器存储引擎
  [mysqld]
  default-storage-engine=
- 使用set命令为当前客户机回话设置
  SET @@storage_engine=;
- 使用create table语句指定
  CREATE TABLE t (i INT) ENGINE = ;
  
  将现有表转换为InnoDB
  使用 ALTER TABLE 更改存储引擎
  ALTER TABLE t ENGINE = InnoDB;

MySQL事务使用

create table t11(id int,name char(10)) engine=innodb;
start transaction;
delete from t11;

select * from t11;
Empty set (0.00 sec)

rollback;

select * from t11;
+------+-------+
| id   | name  |
+------+-------+
|    1 | zorro |
|    2 | zorro |
|    2 | zorro |
+------+-------+

提交
start transaction;
insert into t11 values(3,'tom');
select * from t11;
commit;

保留点
start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t11 values(5,'haha');
Query OK, 1 row affected (0.01 sec)

mysql> savepoint insert1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t11;
+------+-------+
| id   | name  |
+------+-------+
|    1 | tom   |
|    2 | jerry |
|    3 | zorro |
|    4 | seker |
|    5 | haha  |
+------+-------+
5 rows in set (0.00 sec)

mysql> delete from t11 where id=4;
Query OK, 1 row affected (0.00 sec)

mysql> savepoint delete1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t11;
+------+-------+
| id   | name  |
+------+-------+
|    1 | tom   |
|    2 | jerry |
|    3 | zorro |
|    5 | haha  |
+------+-------+
4 rows in set (0.00 sec)

mysql> delete from t11 where id=1;
Query OK, 1 row affected (0.00 sec)

mysql> savepoint delete2;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t11;
+------+-------+
| id   | name  |
+------+-------+
|    2 | jerry |
|    3 | zorro |
|    5 | haha  |
+------+-------+
3 rows in set (0.01 sec)

mysql> rollback to delete1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t11;
+------+-------+
| id   | name  |
+------+-------+
|    1 | tom   |
|    2 | jerry |
|    3 | zorro |
|    5 | haha  |
+------+-------+
4 rows in set (0.00 sec)

2、事务的四种特性

原子性（Atomic）

原子性是指事务是不可分割的一部分，一个事务内的任务要么全部执行成功，要么全部不执行，不存在执行一部分的情况。

可以将整个取款流程当做原子操作，要么取款成功，要么取款失败：
1. 我们可以使用取钱的例子，来讲解这一特性
2. 登录ATM机器
3. 从远程银行的数据库中，获取账户的信息
4. 用户在ATM上输入欲取出的金额
5. 从远程银行的数据库中，更新账户信息
6. ATM机器出款
7. 用户取钱
整个取钱操作，应该视为原子操作，要么都做，要么都不做，不能用户钱还没出来，但是银行卡的钱已经被扣除了。使用事务模型可以保证该操作的一致性。
一致性（Consistency）

是指事务的完整性约束没有被破坏，如果遇到了违反约束的情况，数据库会被自动回滚。一致性是指数据库从一种状态转变为另一种状态。在开始事务和结束事务后，数据库的约束性没有被破坏。例如，数据库表中的用户ID列为唯一性约束，即在表中姓名不能重复。

事务可以不同程度的一致性：
强一致性：读操作可以立即读到提交的更新操作。
弱一致性：提交的更新操作，不一定立即会被读操作读到，此种情况会存在一个不一致窗口，指的是读操作可以读到最新值的一段时间。
最终一致性：是弱一致性的特例。事务更新一份数据，最终一致性保证在没有其他事务更新同样的值的话，最终所有的事务都会读到之前事务更新的最新值。如果没有错误发生，不一致窗口的大小依赖于：通信延迟，系统负载等。
其他一致性变体还有：
单调一致性：如果一个进程已经读到一个值，那么后续不会读到更早的值。
会话一致性：保证客户端和服务器交互的会话过程中，读操作可以读到更新操作后的最新值

隔离性（Isolation）

事物的隔离性要求每个事务的操作，不会受到另一个事务的影响。

并发事务之间互相影响的程度，比如一个事务会不会读取到另一个未提交的事务修改的数据。在事务并发操作时，可能出现的问题有：

  脏读：事务A修改了一个数据，但未提交，事务B读到了事务A未提交的更新结果，如果事务A提交失败，事务B读到的就是脏数据。
  不可重复读：在同一个事务中，对于同一份数据读取到的结果不一致。比如，事务B在事务A提交前读到的结果，和提交后读到的结果可能不同。 不可重复读出现的原因就是事务并发修改记录，要避免这种情况，最简单的方法就是对要修改的记录加锁，这回导致锁竞争加剧，影响性能。另一种方法是通过MVCC可以在无锁的情况下，避免不可重复读。
  幻读：在同一个事务中，同一个查询多次返回的结果不一致。事务A新增了一条记录，事务B在事务A提交前后各执行了一次查询操作，发现后一次比前一次多了一条记录。 幻读是由于并发事务增加记录导致的，这个不能像不可重复读通过记录加锁解决，因为对于新增的记录根本无法加锁。需要将事务串行化，才能避免幻读。
 事务的隔离级别从低到高有：
  Read Uncommitted：最低的隔离级别，什么都不需要做，一个事务可以读到另一个事务未提交的结果。所有的并发事务问题都会发生。
  Read Committed：只有在事务提交后，其更新结果才会被其他事务看见。 可以解决脏读问题。
  Repeated Read：在一个事务中，对于同一份数据的读取结果总是相同的，无论是否有其他事务对这份数据进行操作，以及这个事务是否提交。 可以解决脏读、不可重复读。
 Serialization：事务串行化执行，隔离级别最高，牺牲了系统的并发性。 可以解决并发事务的所有问题。
 通常，在工程实践中，为了性能的考虑会对隔离性进行折中。

持久性（Durability）

事务一旦提交，那么结果就是持久性的。即使发生宕机，数据库也可以做数据恢复。

隔离性通过锁实现，原子性、一致性、持久性通过数据库的redo和undo来完成，其中另外三个都是为了一致性服务的，undo是为了保证原子性，redo是为了保证持久性

支持事务：InnoDB、NDBCluster、TokuDB

不支持事务：MyISAM、MEMORY

3、redo

redo log重做日志

Redo日志记录某数据块被修改后的值，可以用来恢复未写入data file的已成功事务更新的数据。

Redo Log应首先持久化在磁盘上，然后事务的操作结果才写入db buffer，（此时，内存中的数据和data file对应的数据不同，我们认为内存中的数据是脏数据），db buffer再选择合适的时机将数据持久化到data file中。这种顺序可以保证在需要故障恢复时恢复最后的修改操作。先持久化日志的策略叫做Write Ahead Log，即预写日志。

LSN（log sequence number）日志序列号

LSN实际上对应日志文件的偏移量，新的LSN＝旧的LSN + 写入的日志大小

日志文件刷新后，LSN不会进行重置

Log sequence number 1626028

Log flushed up to 1626028

Pages flushed up to 1626028

Last checkpoint at 1626028

Log sequence number:当前系统LSN最大值,新的事务日志LSN将在此基础上生成(LSN1+新日志的大小）

Log flushed up to:当前已经写入日志文件的LSN

Pages flushed up to:当前最旧的脏页数据对应的LSN,写Checkpoint的时候直接将此LSN写入到日志文件

Last checkpoint at:当前已经写入Checkpoint的LSN
1. DML操作导致的页面变化，均需要记录Redo日志（物理日志）
2. 在页面修改完成之后，在脏页刷出磁盘之前，写入Redo日志;
3. 日志先行（WAL），日志一定比数据页先写回磁盘;
4. 聚簇索引/二级索引/Undo页面修改，均需要记录Redo日志; 为了管理脏页，在 Buffer Pool 的每个instance上都维持了一个flush list，flush list 上的 page 按照修改这些 page 的LSN号进行排序。因此定期做redo checkpoint点时，选择的 LSN 总是所有 bp instance 的 flush list 上最老的那个page（拥有最小的LSN）。由于采用WAL的策略，每次事务提交时需要持久化 redo log 才能保证事务不丢。而延迟刷脏页则起到了合并多次修改的效果，避免频繁写数据文件造成的性能问题。
Redo的作用

提高性能
1. 日志用来记录buffer pool中页的page修改的，每次数据提交只要写redo日志就可以，不需要每次都写脏页
2. 通常一个数据页是16KB，如果不写日志，每次的写入还是16kb，即使修改很少数据，仍然要全部落盘，性能影响非常严重。
3. 如果没有日志，每次都会刷脏页，脏页的位置导致的IO是随机IO，而redo的数据页的大小是512字节，这样非常契合硬盘的块大小，可以进行顺序IO，这样可以保证顺序IO，同时可以大大提高IOPS
做crash recovery
1. 数据库重启后，利用redo log进行数据库恢复工作，比对redolog LSN和数据页的LSN，如果数据页LSN低于 REDO LOG LSN就会进行数据页实例恢复

检查点checkpoint

checkpoint是为了定期将db buffer的内容刷新到data file。当遇到内存不足、db buffer已满等情况时，需要将db buffer中的内容/部分内容（特别是脏数据）转储到data file中。在转储时，会记录checkpoint发生的”时刻“。在故障回复时候，只需要redo/undo最近的一次checkpoint之后的操作。

4、undo

undo log 回滚日志

Undo日志记录某数据被修改前的值，可以用来在事务失败时进行rollback；

Undo Log 是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中，还用Undo Log来实现多版本并发控制(简称：MVCC)。

Undo Log的原理很简单，为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了 ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。除了可以保证事务的原子性，Undo Log也可以用来辅助完成事务的持久化。

Undo+Redo事务的简化过程

假设有A、B两个数据，值分别为1,2

A.事务开始.
B.记录A=1到undo log.
C.修改A=3.
D.记录A=3到redo log.
E.记录B=2到undo log.
F.修改B=4.
G.记录B=4到redo log.
H.将redo log写入磁盘。
I.事务提交

Undo+Redo事务特点

A. 为了保证持久性，必须在事务提交前将Redo Log持久化。
B. 数据不需要在事务提交前写入磁盘，而是缓存在内存中。
C. Redo Log 保证事务的持久性。
D. Undo Log 保证事务的原子性。
E. 有一个隐含的特点，数据必须要晚于redo log写入持久存储。

Undo作用

undo作用

用于回滚事务
保证mvcc多版本高并发控制

innodb把undo分为两类

新增undo

代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃。
修改undo

事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除。

分类依据就是是否需要做purge操作 insert在事务执行完成后，回滚记录就可以丢掉了。但是对于更新和删除操作而言，在完成事务后，还需要为MVCC 提供服务，这些日志就被放到一个history list，用于MVCC以及等待purge。

   在并发读写数据库时，读操作可能会不一致的数据（脏读）。为了避免这种情况，需要实现数据库的并发访问控制，最简单的方式就是加锁访问。由于，加锁会将读写操作串行化，所以不会出现不一致的状态。但是，读操作会被写操作阻塞，大幅降低读性能。在Java concurrent包中，有copyonwrite系列的类，专门用于优化读远大于写的情况。而其优化的手段就是，在进行写操作时，将数据copy一份，不会影响原有数据，然后进行修改，修改完成后原子替换掉旧的数据，而读操作只会读取原有数据。通过这种方式实现写操作不会阻塞读操作，从而优化读效率。而写操作之间是要互斥的，并且每次写操作都会有一次copy，所以只适合读大于写的情况。
   MVCC的原理与copyonwrite类似，在MVCC协议下，每个读操作会看到一个一致性的snapshot，并且可以实现非阻塞的读。MVCC允许数据具有多个版本，这个版本可以是时间戳或者是全局递增的事务ID，在同一个时间点，不同的事务看到的数据是不同的。
   MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。MVCC是通过保存数据在某个时间点的快照来进行控制的。使用MVCC就是允许同一个数据记录拥有多个不同的版本。然后在查询时通过添加相对应的约束条件，就可以获取用户想要的对应版本的数据。


MySQL的innodb引擎是如何实现MVCC的。innodb会为每一行添加两个字段，分别表示该行创建的版本和删除的版本，填入的是事务的版本号，这个版本号随着事务的创建不断递增。在repeated read的隔离级别下，具体各种数据库操作的实现：

select：满足以下两个条件innodb会返回该行数据：（1）该行的创建版本号小于等于当前版本号，用于保证在select操作之前所有的操作已经执行落地。（2）该行的删除版本号大于当前版本或者为空。删除版本号大于当前版本意味着有一个并发事务将该行删除了。
insert：将新插入的行的创建版本号设置为当前系统的版本号。
delete：将要删除的行的删除版本号设置为当前系统的版本号。
update：不执行原地update，而是转换成insert + delete。将旧行的删除版本号设置为当前版本号，并将新行insert同时设置创建版本号为当前版本号。
其中，写操作（insert、delete和update）执行时，需要将系统版本号递增。

由于旧数据并不真正的删除，所以必须对这些数据进行清理，innodb会开启一个后台线程执行清理工作，具体的规则是将删除版本号小于当前系统版本的行删除，这个过程叫做purge。

通过MVCC很好的实现了事务的隔离性，可以达到repeated read级别，要实现serializable还必须加锁。

undo日志的正确性是通过redo来保证的，所以在数据库恢复的时候，需要先恢复redo，在所有数据块都保证一致性的情况下，再进行undo的逻辑操作。

5、检查点checkpoint

检查点解决的问题

缩短数据库恢复时间
缓冲池不够用的时候，刷新脏页到磁盘
重做日志不够用的时候，刷新脏页

当数据库发生宕机的时候，数据库不需要恢复所有的页面，因为检查点之前的页面都已经刷新回磁盘了。故数据库只需要对检查点以后的日志进行恢复，这就大大减少了恢复时间。

检查点的类型

检查点分为两种类型，一种是sharp检查点，一种是fuzzy检查点

sharp checkpoint落盘条件：

关闭数据库的时候设置 innodb_fast_shutdown=1，在关闭数据库的时候，会刷新所有脏页到数据库内。 fuzzy checkpoint在数据库运行的时候，进行页面的落盘操作，不过这种模式下，不是全部落盘，而是落盘一部分数据。

Fuzzy落盘的条件：

master thread checkpoint： master每一秒或者十秒落盘
sync check point： redo 不可用的时候，这时候刷新到磁盘是从脏页链表中刷新的。
Flush_lru_list check point : 刷新flush list的时候。

落盘的操作是异步的，因此不会阻塞其他事务执行。

检查点的作用：

缩短数据库的恢复时间
缓冲池不够用的时候，将脏页刷新到磁盘
重做日志不可用的时候，刷新脏页（循环使用redo文件，当旧的redo要被覆盖的时候，需要刷新脏页，造成检查点）

6、MySQL两阶段提交（内部XA）

在这里插入图片描述

两阶段提交发生在数据变更期间(更新、删除、新增等)，两阶段提交过程中涉及到了 MySQL 数据库中的两个日志系统：redo 日志和 binlog 文件。

MySQL二阶段提交流程：

事务的提交主要分三个主要步骤：

Storage Engine（InnoDB） transaction prepare阶段：

此时SQL已经成功执行，并生成xid信息及redo和undo的内存日志。
Binary log日志提交：

write()将binary log内存日志数据写入文件系统缓存。

fsync()将binary log文件系统缓存日志数据永久写入磁盘。
Storage Engine（InnoDB）内部提交：

修改内存中事务对应的信息，并且将日志写入重做日志缓冲。

调用fsync将确保日志都从重做日志缓冲写入磁盘。

一旦步骤2中的操作完成，就确保了事务的提交，即使在执行步骤3时数据库发生了宕机。此外需要注意的是，每个步骤都需要进行一次fsync操作才能保证上下两层数据的一致性。步骤2的fsync参数由sync_binlog控制，步骤2的 fsync由参数innodb_flush_log_at_trx_commit控制。

ROLLBACK过程：

当事务在binlog阶段crash，此时日志还没有成功写入到磁盘中，启动时会rollback此事务。

当事务在binlog日志已经fsync()到磁盘后crash，但是InnoDB没有来得及commit，此时MySQL数据库recovery的时候将会从二进制日志的Xid（MySQL数据库内部分布式事务XA）中获取提交的信息重新将该事务重做并commit使存储引擎和二进制日志始终保持一致。

总结起来说就是如果一个事物在prepare log阶段中落盘成功，并在MySQL Server层中的binlog也写入成功，那这个事务必定commit成功

举例说明：
更新语句如下：
mysql> update T set c=c+1 where id=2;
未更新前id=2这行数据c的值为0

在 InnoDB 存储引擎下，一条 update 语句在 MySQL 内部执行大概会经历下面五个步骤：
1、执行器先找引擎取 id=2 这一行数据，如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

2、执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

3、引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

4、执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

5、执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交(commit)状态，更新完成。

在这五步中，第3步和第5步中，redo 日志被分割成 prepare 和 commit 两个阶段提交，这个过程称为两阶段提交

7、事务隔离级别

隔离级别	中文事务	可能造成的问题	缩写
READ UNCOMMITED	未提交读	脏读	RU
READ COMMITED	提交读	幻读	RC
REPEATEABLE READ	可重复读	-	RR
SERIALIZABLE	序列化	-	-

在READ UNCOMMITED下，会造成脏读，脏读的概念就是数据库读取了，在内存中被修改，但是没有提交的数据（没有刷新日志，没有刷新到磁盘），这个时候，就读取了脏的数据，别的事务没有提交，造成了事务的隔离性无法被保障。

在READ COMMITED下，未被提交的事务不会被读到，只有被提交的事务的数据，才会被读取到。（执行两次相同的SQL得到了不同的结果，。第二次的SQL得到不同的结果）

在 REPEATEABLE READ下，其他事务对于数据的修改（update，delete）不会影响本事务对于数据的读取，会避免幻读的产生，幻读就是在一个事务内，读取到了不同的数据行数结果。

数据越安全，相对来说，数据库的并发能力越弱（并不代表总体性能越弱）

脏读(Drity Read)：事务T1修改了一行数据，事务T2在事务T1提交之前读到了该行数据。

不可重复读(Non-repeatable read): 事务T1读取了一行数据。事务T2接着修改或者删除了改行数据，当T1再次读取同一行数据的时候，读到的数据时修改之后的或者发现已经被删除。

幻读(Phantom Read): 事务T1读取了满足某条件的一个数据集，事务T2插入了一行或者多行数据满足了T1的选择条件，导致事务T1再次使用同样的选择条件读取的时候，得到了比第一次读取更多的数据集。

幻读和可重复读的区别

幻读更多的是针对于insert来说，即在一个事务之中，先后的两次select查询到了新的数据，新的数据来自于另一个事务的insert，一般称之为幻读，通过gap_lock来防止产生幻读（虽然record可以避免数据行被修改，但是却无法阻止 insert，gap_lock锁定索引间隙，防止了在事务查询的范围内的insert情况）而可重复读，一般是针对于update和delete来说，可重复读采用了mvcc多版本控制来实现数据查询结果本身的不变。

8、设置MySQL的事务隔离级别

查看当前MySQL SESSION的配置的隔离级别

mysql> show variables like '%iso%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+

查看全局的MySQL GLOBAL的配置的隔离级别

global参数设置后，需要新开启session才能生效

mysql> show global variables like '%iso%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+

MySQL自动提交的开启与关闭

在MySQL中，默认情况下，事务是自动提交的，也就是说，只要执行一条DML语句就开启了事物，并且提交了事务，当使用innodb引擎时执行了START TRANSACTION或BEGIN命令后将不会自动提交了，只有明确执行了COMMIT命令后才会被提交，在这之前可以执行ROLLBACK 命令回滚更新操作。
```
SHOW VARIABLES LIKE 'autocommit';查看自动提交状态
SET AUTOCOMMIT=0 禁止自动提交 OFF
SET AUTOCOMMIT=1 开启自动提交 ON
```
设置MySQL事务的隔离级别

可设置的级别有READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE
- 可以在my.ini文件中使用transaction-isolation选项来设置服务器的缺省事务隔离级别
  
  [mysqld]
  
  transaction-isolation = READ-COMMITTED
- 通过命令动态设置隔离级别
  - 设置全局级别参数
```
set global transaction isolation level read uncommitted;
```
  - 设置会话级别参数
```
set session transaction isolation level repeatable read;
```

9、隔离级别特性实验

RU隔离级别下脏读的验证

session1	session2
set autocommit=0; set session transaction isolation level read uncommitted;	set autocommit=0; set session transaction isolation level read uncommitted;
begin；
select * from t1；发现没有数据
	begin；
	insert into t1 values(5,‘yyyy’);
select * from t1；此时session2的事务还没有提交，但是已经发现被修改的脏块被读取到，所以造成了脏读（session2的事务对session1的事务造成了影响，违反了事务的隔离性，造成了脏读）
	commit；
commit；

RC隔离级别下不会造成脏读的验证

session1	session2
set autocommit=0; set session transaction isolation level read committed;	set autocommit=0; set session transaction isolation level read committed;
begin;
select * from t1;
	begin；
	insert into t1 values(6,‘liuliuliu’);
select * from t2; 此时session2的事务还没有提交，发现被修改的脏块未被读取到，未造成了脏读
	commit；
commit；

RC模式下的幻读影响

session1	session2
set session transaction isolation level read committed;	set session transaction isolation level read committed;
begin;
select * from t1;
	begin;
	insert into t1 values(7,‘oooo’);
	commit;
select * from t1; 会发现虽然session1是在一个事务之中，但是session2的事务的提交，对 session1的事务的查询结果造成了影响，即session2提交后的数据，在 session1的同一个事务中，读取到了提交前后的两个结果。
commit;

RR模式下不会造成幻读

session1	session2
set session transaction isolation level repeatable read;	set session transaction isolation level repeatable read;
begin;
select * from t1;
	begin;
	insert into t1 values(8,‘pppp’);
	commit;
select * from t1; 在 session2提交后，session1在该事务中，并没有读取到session2插入的t1数据
commit;
begin;
select * from t1; 新开启事务后，会发现，已经读取到了之前提交后的数据
commit;

10、MVCC的简单实现

MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control) (注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)。

MVCC的好处

读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，这也是为什么现阶段，几乎所有的RDBMS，都支持了MVCC。
读的类型

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)
- 快照读
  
  读取的是记录的可见版本 (有可能是历史版本)，不用加锁。
  
  例如 select 就为快照读
- 当前读
  
  读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。
  
  例如删除/更新/删除操作

在MVCC读取数据的过程中，会先对目前的事务和数据行的事务号进行对比，如果发现事务行的事务版本号，已经增长，则说明该行数据已经被其他事务修改，那么就需要根据undo，读取undo内的历史版本的相关逻辑操作信息，然后根据逻辑操作信息构建符合当前查询的数据版本，然后返回结果集（在RC和RR模式下，对于UNDO构建数据块的版本选择不同）

通过MVCC，虽然每行记录都需要额外的存储空间，更多的行检查工作以及一些额外的维护工作，但可以减少锁的使用，大多数读操作都不用加锁，读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行，也只锁住必要行。 innodb的锁的粒度

十三、MySQL锁

1、锁

MySQL中锁的概念可以等同于：并发控制、序列化、隔离性

这种用隔离性来描述锁，就是因为是事务ACID特性中的I，而锁就是用来实现事务一致性和隔离性的一种常用技术。

当数据库事务并发各自运行的时候，每个事务的运行不受到其他事务的影响。

简单的加锁技术就是对对象加上一个锁，若访问该事务的时候，发现已经有锁，则等待该事务锁的释放。

通过多粒度锁定，保证了数据库中事务的并发性。

innodb锁的类型

对于行锁，根据其作用类型，可以分为两类：

共享锁(S lock)：允许读取一个数据，同时允许其他事务对该事务进行更改。

排他锁(X lock)：允许删除或者更新一条数据，同时不允许其他事务对该事务进行操作。

InnoDB 行锁是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB 将通过隐藏的聚簇索引来对记录加锁。
注意：行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。两个事务不能锁同一个索引。

当一行获取S锁的时候，也可以获取另一个事务的S锁，这称之为锁兼容。

x s
x 不兼容不兼容
s 不兼容兼容
查看innodb中锁的情况
```
show full processlist
show engine innodb status;
查看当前锁信息
select * from information_schema.INNODB_LOCKS\G;
查看锁等待信息
select * from information_schema.INNODB_LOCK_WAITS\G;
```

	x	s
x	不兼容	不兼容
s	不兼容	兼容

在mysql的设计中，在写操作的时候会自动给选定行加上x锁，也就是所谓独占锁，即在没有释放x锁之前，任何会话都不能访问锁定行，我相信这个是没有问题的；但是这样会有问题，别的会话去读加了x锁的行时在正常逻辑下，为了保证数据一致性，我们在读的时候应该需要等待x锁释放再去读，从而可以得到最新最准确的数据，但是这样会严重降低数据库的性能，在数据库有写操作的情况下，读操作都需要等待；那到底在有写操作的时候，读操作要不要去等待呢？欣慰的是：mysql把这个选择权交给了我们，当有写（x锁）操作的情况下，希望读操作等待（加s锁）使用一致性锁定读，希望读操作不等待(不加s锁)使用非锁定读。

关于锁的3张表

##查看锁的情况
mysql> show status like 'innodb_row_lock_%';
## 当前运行的所有事务
mysql> select * from information_schema.innodb_trx\G;
## 当前出现的锁
mysql> select * from information_schema.innodb_locks\G;
## 锁等待的对应关系 
mysql> select * from information_schema.innodb_lock_waits\G;

杀死进程

# 1.查询进程
mysql> show processlist\G 
# 2.杀死对应进程ID 
mysql> kill 140;

2、一致性非锁定读

一致性的非锁定读就是INNODB存储引擎通过行多版本控制的方式来读取当前执行时间数据库中的数据。

如果读取的行正在执行DELETE或者UPDATE，这个时候独缺操作不会等待所在行的锁的释放。 INNODB这个时候会读取一个快照数据。

一致性非锁定读取的原理是这样的：Innodb通过隐藏的回滚指针保存前一个版本的undo日志，通过当前记录加上undo日志可以构造出记录的前一个版本，从而实现同一记录的多个版本。

其实一致性非锁定读的原理很简单，就是采用MVCC；也就是一个数据是有版本的，也就是历史数据，对于显示哪个历史版本的数据，这个和隔离级别有关

快照数据就是当前数据行的历史版本，每个行记录可能有多个版本

在RC模式下，MVCC会一直读取最新的快照数据

在RR模式下，MVCC会读取本事务开始时候的快照数据

对于一致性非锁定读取，即使被读取的行已经SELECT … FOR UPDATE，也是可以进行读取的。

一致性非锁定读RR实验模式实验

session1	session2	session3
set session transaction isolation level repeatable read; Begin;	set session transaction isolation level repeatable read;	set session transaction isolation level repeatable read;
select * from t2;可以看到t2表中的初始数据id=1，name=cc
	begin; update t2 set name=‘aa’ where id=1; commit;
		begin; update t2 set name=‘dd’ where id=1;
select * from t2; 可以看到表中的数据还是id=1，name=cc

在RR模式下，session1在隔离级别中，始终读取的是最初的数据，但是在最后一次读取中，session3已经对数据进行加锁，所以该情况下，读取的是数据name=’cc‘的undo快照，该快照是读取的最初的快照版本。

一致性非锁定读RC模式实验

session1	session2	session3
set session transaction isolation level read committed;	set session transaction isolation level read committed;	set session transaction isolation level read committed;
begin; select * from t2; 查询到的数据为id=1，name=ee
	begin; update t2 set name=‘oo’ where id=1; commit;
		begin; update t2 set name=‘pp’ where id=1;
select * from t2; 查询到的数据为id=1，name=oo

在RC模式下，RC读取的是最近的快照。这样就造成了在session1一个事务中，select产生两种情况的幻读情况。

在RR模式下GAP_LOCK是默认开启的

innodb_locks_unsafe_for_binlog，这个参数默认值是OFF，也就是启用间隙锁

3、一致性锁定读取

有些情况下用户需要采用锁定读取的方式来进行读取保证数据的一致性这时需要手动在查询中添加锁

查询中使用S锁
select * from test1.t1 where id=1 lock in share mode;
查询中添加X锁
select * from test1.t1 where id=1 for update;

一致性锁定读：对数据一致性，实时性比较高的情况下（会破坏事务隔离性，比较慢，性能低）
一致性非锁定读: 对数据一致性，实时性不高的情况下（数据可能不是最新的，不会破坏事务隔离性）

可重复读隔离级别总结
在可重复读隔离级别中（一个事务中），在使用一致性非锁定读时（默认不加锁），读取的总是事务开始的那个版本数据，数据可能不是最新的；在使用一致性锁定读时（加x锁或者s锁），读取的总是当前最新的数据；也就是说在可重复读隔离级别中一致性锁定读与一致性非锁定读读取到的数据不一定一样（在开启事务后，有其他事务修改了数据时不一致，否则一致）

session1	session2
set autocommit=0; set session transaction isolation level repeatable read; begin;	set autocommit=0; set session transaction isolation level repeatable read; begin;
select * from t2 where id=1; 查询到id=1，name=gg
	select * from t2 where id=1; 查询到id=1，name=gg
update t2 set name=‘oo’ where id=1;
	select * from t2 where id=1; 查询到id=1，name=gg
	select * from t2 where id=1 lock in share mode; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
commit;
	select * from t2 where id=1 lock in share mode; 查询到id=1，name=oo数据是最新数据

已提交隔离级别总结
在已提交隔离级别中（一个事务中），一致性锁定读与一致性非锁定读读取到的数据总是一样的。因为即使在开启事务后，有其他的事务对数据进行了修改，一致性非锁定读总是读取最新的那个快照，而一致性锁定读本身就是读取最新的那个快照。

锁等待

关于锁等待的参数

参数支持范围为Session和Global,并且支持动态修改

事务等待获取资源等待的最长时间，超过这个时间还未分配到资源则会返回应用失败

参数的时间单位是秒
```
innodb_lock_wait_timeout 30
```

锁等待实验

session1	session2
set autocommit=0; begin;	set autocommit=0; begin;
update t2 set name=‘xx’ where id=1;
	update t2 set name=‘yy’ where id=1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

4、行锁的三种算法

Record lock：记录锁，单个行记录上的锁
锁住具体的索引项，当SQL执行按照唯一性索引进行数据的检索时，查询条件等值匹配且查询的数据是存在，这时SQL语句加上的锁即为记录锁Record locks，锁住具体的索引项
Gap lock：间隙锁，锁定一个范围，但不包含记录本身
- 当sql执行按照索引进行数据的检索时，查询条件的数据不存在，这时SQL语句加上的锁即为 Gap locks，锁住索引不存在的区间（左开右开的区间）
- Gap只在RR事务隔离级别/repeatable read存在
Next-key lock：record+gap，临建锁，锁定一个范围，且包含记录本身

锁住记录+区间（左开右闭的区间）
当sql执行按照索引进行数据的检索时,查询条件为范围查找（between and、<、>等）并有数据命中则此时SQL语句加上的锁为Next-key locks，锁住索引的记录+区间（左开右闭的区间）
在innodb中，使用Next_Key Lock算法来避免幻读问题 Next-key lock由record_lock和gap_lock组成，在Next_Key Lock算法中，不仅仅锁定住所秒到的索引，而且还锁定住这些索引覆盖的范围。因此在这个范围内的插入都是不允许的。这样就避免了在这个范围内插入数据导致的幻读问题。因此，在innodb默认的隔离级别是RR，在 RR模式下使用Next_Lock算法，避免了不可重复读的现象。不过可以使用相关参数在RR模式下避免使用next-key lock，因为next-key算法会让加锁范围增加，对业务本身的性能影响增加,这个参数默认是OFF，也就是开启了GAP_lock，可以选择关闭。（详情请看后面关于该参数的实验）
```
innodb_locks_unsafe_for_binlog OFF
```

RR开启gap_lock锁后的测试

参数innodb_locks_unsafe_for_binlog在RR模式下的使用该参数默认关闭，也就是开启gap lock

session1	session2
begin; select * from t3 where id2=2 for update;	begin;
	insert into t3 values(3,3); 插入被阻塞，说明选定了全表，而且加了gap lock
	update t3 set id2=10 where id1=2; 同样也被阻塞，但是阻塞该操作的为record锁

RR关闭gap_lock后的测试

innodb_locks_unsafe_for_binlog=1

session1	session2
begin; update test.t3 set id1=40 where id>1;	begin;
	insert into test.t3 values(3,60); 没有被阻塞，插入成功 commit;
update test.t3 set id1=66 where id>1; commit;
	select * from test.t3 where id>1; 新插入的记录也被修改了
begin;	begin;
select * from test.t3 where id>1;
	insert into test.t3 values(4,88); commit;
select * from test.t3 where id>1; 查到的记录没有改变

5、乐观锁和悲观锁

悲观锁

其实就是数据库的表锁，行锁对数据库进行锁定

正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。
乐观锁

通过mvcc实现了非一致性锁定读（undo）

相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。

而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

要说明的是，MVCC的实现没有固定的规范，每个数据库都会有不同的实现方式，这里讨论的是InnoDB的MVCC。

死锁

如果程序是串行的，那么不可能发生死锁，死锁只存在于并发的情况之中，死锁是在两个事务在执行过程中，因为争夺资源而造成的一种互相等待的现象

session1	session2
begin;	begin;
select * from test.t3 where id=2 for update;	select * from test.t3 where id=3 for update;
select * from test.t3 where id=3 for update;
	select * from test.t3 where id=2 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

6、一条SQL加锁的判断

delete from t1 where id = 10;

组合一：id列是主键，RC隔离级别

id = 10的记录加上X锁
组合二：id列是二级唯一索引，RC隔离级别

若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的[name=’d’,id=10]的记录。
组合三：id列是二级非唯一索引，RC隔离级别

id列索引上，满足id = 10查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。与组合二唯一的区别在于，组合二最多只有一个满足等值查询的记录，而组合三会将所有满足查询条件的记录都加锁。
组合四：id列上没有索引，RC隔离级别

若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。
组合五：id列是主键，RR隔离级别

与组合一相同
组合六：id列是二级唯一索引，RR隔离级别

与组合五类似，组合六的加锁，与组合二：[id唯一索引，Read Committed]一致。两个X锁，id唯一索引满足条件的记录上一个，对应的聚簇索引上的记录一个。
组合七：id列是二级非唯一索引，RR隔离级别

其实这个多出来的GAP锁，就是RR隔离级别，相对于RC隔离级别，不会出现幻读的关键。确实，GAP锁锁住的位置，也不是记录本身，而是两条记录之间的GAP。所谓幻读，就是同一个事务，连续做两次当前读 (例如：select * from t1 where id = 10 for update;)，那么这两次当前读返回的是完全相同的记录 (记录数量一致，记录本身也一致)，第二次的当前读，不会比第一次返回更多的记录 (幻象)。
组合八：id列上没有索引，RR隔离级别

Repeatable Read隔离级别下的最后一种情况，id列上没有索引。此时SQL：delete from t1 where id = 10; 没有其他的路径可以选择，只能进行全表扫描
组合九：Serializable隔离级别

自增锁

自增锁是INNODB比较特殊的锁，因为大多数锁都是在事务完成后自动解除锁定，但是如果自增锁也这样做的话，会大大影响数据库自增的插入性能。因此innodb存储引擎的自增锁是在插入SQL语句完成后立即释放。

自增锁之间是不兼容的，就是一个SQL在进行自增插入的时候，另一条SQL需要等待。

每个表的自增长值不会进行持久化，而是每次INNODB 重启后去表中查询最大的自增主键值后，将得到的值保存在内存中。

十四、MySQL故障解决思路及优化

1、故障解决思路

为了让数据库或者服务器故障在最早被发现，被解决，数据库系统应该拥有完善的监控措施，不管是使⽤zabbix或者天兔等开源的监控软件，还是企业内部⾃⼰开发的监控软件，都应该有⼀套可以及时报警的监控系统。

其次就是应该有合理的备份计划，每天做好备份，防患于未然。

解决故障第⼀步查看是否硬件和系统层⾯

数据库的问题，很可能是底层硬件或者操作系统造成的问题

硬盘问题

查看是否是硬盘报满（如果硬盘满了，应该及时清空部分⽆⽤数据或者空间，否则数据库重启⽆法启动，操作系统也容易出问题，数据盘尽量与系统根⽬录分别挂载，防⽌数据盘满后，造成操作系统故障）

查看硬盘读写是否满速iostat

iostat 使⽤

参数 -d 表示，显示设备（磁盘）使⽤状态；

-k某些使⽤block为单位的列强制使⽤Kilobytes为单位；

2 5表示，数据显示每隔2秒刷新⼀次，共显示5次。

[root@localhost ~]# iostat -d -k 2 5
Linux 3.10.0-1062.el7.x86_64 (localhost.localdomain) 	08/16/2021 	_x86_64_	(1 CPU)

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
sda               0.58        12.21         3.52     507301     146217
dm-0              0.50        11.92         3.46     495146     143861
dm-1              0.00         0.06         0.01       2324        308

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
sda               0.50         4.00         0.00          8          0
dm-0              0.50         4.00         0.00          8          0
dm-1              0.00         0.00         0.00          0          0

tps:该设备每秒的传输次数，⼀次传输的意思是“⼀次I/O请求”
kB_read/s：每秒从设备读取的数据量
kB_wrtn/s ：每秒向设备写⼊的数据量
kB_read ：读取的总数据量
kB_wrtn ：写⼊的总数量数据量

[root@localhost ~]# iostat -d -x -k 2 5
Linux 3.10.0-1062.el7.x86_64 (localhost.localdomain) 	08/16/2021 	_x86_64_	(1 CPU)

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00     0.02    0.36    0.18    11.57     3.35    54.42     0.01   10.35   14.37    2.44   3.89   0.21
dm-0              0.00     0.00    0.32    0.16    11.29     3.30    61.48     0.01   12.01   16.40    3.08   4.46   0.21
dm-1              0.00     0.00    0.00    0.00     0.05     0.01    26.99     0.00    3.29    2.18    5.00   0.65   0.00

-x：输出扩展信息
rrqm/s：每秒对该设备的读请求被合并次数，文件系统会对读取同块(block)的请求进行合并；
wrqm/s：每秒对该设备的写请求被合并次数。
rsec/s：每秒完成的读次数；
wsec/：每秒完成的写次数。
rKB/s：每秒读数据量(kB为单位)；
wKB/s：每秒写数据量(kB为单位)；
avgrq-sz：平均每次IO操作的数据量(扇区数为单位)
avgqu-sz：平均等待处理的IO请求队列长度，队列长度越短越好。
await：每一个IO请求的处理的平均时间（单位是微秒毫秒）。这里可以理解为IO的
响应时间，一般 地系统IO响应时间应该低于5ms，如果大于10ms就比较大 了。这个时间包括了队列时间和服务时间，也就是说，一般情况下，await大于svctm，它们的差值越小，则说明队列时间越短， 反之差值越大，队列时间越长，说明系统出了问题。
svctm：表示平均每次设备I/O操作的服务时间（以毫秒为单位）。如果svctm的值与await很接近，表 示几乎没有I/O等待，磁盘性能很好，如果await的值远高于svctm的值，则表示I/O队列等待太 长，系统上运行的应用程序将变慢。
%util： 在统计时间内所有处理IO时间，除以总共统计时间。例如，如果统计间隔1秒，该设备有0.8 秒在处理IO，而0.2秒闲置，那么该设备的%util = 0.8/1 = 80%，所以该参数暗示了设备的繁 忙程度。一般地，如果该参数是100%表示设备已经接近满负荷运行了（当然如果是多磁盘，即使%util是100%，因为磁盘的并发能力，所以磁盘使用未必就到了瓶颈）。

内存问题

查看内存是否⽤尽(OOM内存溢出)

Free -m查看
[root@localhost ~]# free -m
              total        used        free      shared  buff/cache   available
Mem:            972         619          68          10         284         186
Swap:          1023           0        1023

SWAP的使⽤策略与mysql的落盘策略

使⽤mysqldump导出⼀个很⼤的数据库的时候，⽂件系统往往会向Linux申请⼤量的内存作为cache，⼀不⼩⼼就会导致过度使⽤swap或者oom(out of memory内存用完了（内存泄漏、内存溢出）)的发⽣。

/proc/sys/vm/swappiness的内容改成0(这个参数范围是0-100，数值越⼤，越倾向于使⽤swap)
/etc/sysctl.conf上添加vm.swappiness=0
swappiness=0表示最⼤限度使⽤物理内存，然后才是swap分区。

修改MySQL的配置参数innodb_flush_method，开启O_DIRECT模式。
这种情况下，InnoDB的buffer pool会直接绕过⽂件系统cache来访问磁盘

CPU占⽤是否过高

使用TOP命令查看

如果是硬件故障，都需要联系运维人员进行硬件的更换或者升级

解决故障第二步，查看数据库层面

确定问题出现在数据库上⾯后，⾸先要做的就是查看数据库⽇志

Show engine innodb status(查看数据近期情况)
Slow log（查看⽇志）
Show global status（查看数据库状态）
Show processlist（查看链接，锁信息）
error-log（最重要，数据库很多问题可以根据error log来判断）

2、硬件选型优化

企业选购数据库硬件需要注意的问题

云平台（阿⾥云亚⻢逊云⾦⼭云RDS）

⼀般云平台已经进⾏了合理的备份措施，这些都不需要你去担⼼

云平台⼀般会进⾏数据库实例的集成的备份，监控，数据查询状态

在选购云平台的时候，是根据硬件规格选择的，⼏cpu，⼏内存，然后进⾏判断。

选择稳定的云平台（阿⾥云，亚⻢逊云）
选择该提供服务所在区域的云平台的机房
尽量选择SSD（本身⽹络环境 IO环境不稳定，测试经常出问题，所以需要⽤⾼⼀点的配置）

云服务器（阿⾥云亚⻢逊云（AWS EC2）⾦⼭云微软azure）

国内，做的最稳定的阿⾥云，但是最贵的阿⾥云

优点：

扩展性好，可以动态扩展我们的计算，存储资源（可以直接在线购买，然后扩容）
成本低，不需要租赁或者搭建⾃⼰的机房，然后不需要购买服务器，只要购买服务就好了
⾼可⽤等，有云服务⾃带（⽐如我们购买ali ⾦⼭，后台有⾃带的主从，买实例的话，等于买了⼀对如果你当前使⽤的实例挂掉，从库会⾃动的对外提供服务）
容易搭建整套对外提供服务的环境

缺点：

购买RDS的话，⽆法操作系统底层，部分MySQL参数⽆法调节，只能调节云服务商暴露的参数
RDS备份策略，只能选择云服务商提供的备份策略，⽐如有⼀些特殊的备份需求，⽆法满⾜
RDS架构⽐较单⼀，⽆法定制
云服务商的服务质量，参差不⻬，⽹络和硬盘的相关性能，波动⽐较⼤，并且容易遭受⽹络攻击
公司的敏感数据，其实都要暴露在云服务商的系统中，数据安全性⽐较低
只能选择云服务商，所提供的机房

实体服务器的优缺点：

优点：

都在本地，能保证服务的稳定性
多机房部署，可以实现灾备，以及多地区快速相应服务
保证数据安全性，数据都落在公司内部服务器
MySQL架构可定制性⽐较⾼，保证业务的7*24的⽆间断运⾏
运⾏稳定，保证应⽤和数据库的之间的正常的数据交流

缺点：

价格贵，成本⾼
部署，搭建需要专⻔⼈员
如果没有私有云，没有做弹性计算，可能临时的扩展性，差⼀些

私有云：公司内部⾃⼰的云环境

公有云：阿⾥云亚⻢逊这种对外提供服务的云环境。

本地服务器

业务类型与MySQL硬件选择的关系

	OLTP	OLAP
系统功能	日常交易处理	统计、分析、报表
DB设计	面向实时交易类应用	面向统计分析类应用
数据处理	当前的，最新的细节的，二维的分立的	历史的，聚集的，多维的集成的，统一的
实时性	实时读写要求高	实时读写要求低
事务	强一致性	弱事务
分析要求	低，简单	高，复杂

数据库：是OLTP（联机事务处理）应用的场景，其存储的主要是与业务直接相关的数据，强调准确、低时延、高并发，如果没有特别强调，基本上数据库里只会去存储与业务相关的数据。

数据仓库：OLAP（联机分析处理）是数据仓库系统的主要应用，其支持的对象只要是面向分析场景的应用，提供结构化的、主题化的数据提供给运营，做业务反馈和辅助决策用，同时，有些场景下，也可以由数据仓库对业务进行支持。

OLTP 这是典型的互联⽹应⽤场景（IO密集型）

⾼并发（⼤量的数据写⼊写出，qps⽐较⾼，）
计算量⼩（单条SQL的计算量不⼤）
快速响应（需要快速返回结果）

硬件⽅案

内存⼤（满⾜⾼并发的多线程内存开销，缓存池的内存开销，提供内存命中率）
硬盘向应快（HDD 100MB+/S，SSD 600MB/S，PCIE 3GB/S 减少IO瓶颈）
⼀般硬盘需要做raid，ssd尤甚，因为ssd坏掉后⽆法修复，防⽌数据丢失
数据库尽量同机房
cpu选型，尽量选择⾼主频cpu，其次选择多核⼼（MySQL为单进程多线程架构，应该优先考虑cpu主频，cpu主频对于多表连接查询计算等，都有直接的好处）

OLAP这是典型的数据仓库，BI系统的计算特点（CPU密集型）

单⼀事务计算量⽐较⼤
单⼀事务的时间⻓
不要求快速响应（内存够⽤，⼀般硬盘但是容量要够⼤）

硬件⽅案

购买普通的⾜够容量的硬盘（每天涌⼊⼤量的⽇志，防⽌硬盘被撑满，另外需要单独建⽴挂载点，不要放置在根⽬录下）
内存够⽤
CPU较好

cpu优先考虑⾼主频 1. 如果此数据库仅仅是⽤来存放历史数据，那么可以使⽤⼤硬盘的廉价机器（⼀般互联⽹公司资⾦有限）

多机房考虑
1. 尽量减少数据的多机房传输（除⾮有专属的光纤通道，较少返回的数据延迟（同机房在⼏毫秒，夸机房的话数据会在⼏⼗毫秒，⼤数据量下，应⽤和数据库的数据返回开销很⼤）
2. 减少跨机房产⽣的物理损坏问题
3. 数据备份，是可以做多机房甚⾄异地，防⽌某机房坏掉后，数据彻底丢失
清除系统⽂件缓存

sync

sync命令则可⽤来强制将内存缓冲区中的数据⽴即写⼊磁盘中

echo 1 > /proc/sys/vm/drop_caches

0 – 不释放

1 – 释放⻚缓存

2 – 释放⽂件节点缓存和⽬录项缓存

3 – 释放所有缓存

3、MySQL配置参数优化

per thread 参数

per_thread_buffer优化

read_buffer_size

顺序扫描为每个线程分配的缓冲区⼤⼩，默认128KB，设置为128-256KB即可

read_rnd_buffer_size

随机扫描buffer，默认128kb，⼀般也为128-256KB

sort_buffer_size

排序缓冲，在进⾏⽂件排序过程中，为了提⾼性能，会使⽤该缓冲，默认为2M

join_buffer_size

表连接查询的buffer，可以多次分配，默认是128KB，建议设置为128-256KB

binlog_cache_size

如果不是事务⾮常频繁，那么设置1-2MB可以满⾜需求

全局参数优化

innodb_buffer_size_pool

⼀般为服务器的70%左右内存上限，同时还有关注是否有其他应⽤在跑

innodb_buffer_pool_instances

⾼并发下可以设置多个buffer pool 来减少LRU队列锁的征⽤

max_allowed_packet = 32M

在经常有⼤SQL跑动的服务上，可以将该参数设置⼤⼀些，防⽌数据包过⼤被服务器拒绝

Max_connections

建议设置为512-1024之间，防⽌链接过多造成的MySQL⽆法链接

max_connect_errors

如果链接报错这个参数，那么会拒绝该主机访问，防⽌进⾏撞库攻击如果进⾏解禁，则需要使⽤flush hosts

⽂件/etc/security/limits.conf新增如下⾏：

mysql soft nofile 65535

mysql hard nofile 65535

修改MySQL参数

open_files_limit = 65535 

innodb_open_files=65535

interactive_timeout

针对交互式连接，通过mysql客户端连接数据库是交互式连接，单位S。

wait_timeout

针对⾮交互式连接，通过jdbc连接数据库是⾮交互式连接，单位S。⼀般针对程序，调节该参数即可。

general_log

对于性能影响较⼤，可以选择关闭

4、SQL优化

环境中慢SQL的定位

开启慢查询⽇志功能

slow_query_log=1 slow_query_log_file=/home/mysql3307/logs/slow.log long_query_time=1

定位慢⽇志的时间点

mysqldumpslow命令
```
/usr/local/mysql/bin/mysqldumpslow -s c -t 10 /home/mysql3306/logs/mysql-slow.log
```
这会输出记录次数最多的10条SQL语句，其中：

-s, 是表示按照何种⽅式排序

c、t、l、r分别是按照记录次数、查询时间、锁时间、返回的记录数来排序

ac、at、al、ar，表示相应的倒叙

-t, 是top n的意思，即为返回前⾯多少条的数据

-g, 后边可以写⼀个正则匹配模式，⼤⼩写不敏感的

在OLTP环境中，SQL的优化，⼤多数的情况都是各种SQL中没有索引，或者索引没有良好的使⽤，因为OLTP环境中，⼀般单条SQL逻辑不会太难，⽽且单条SQL中的数据集的数据量不会太⼤，所以⼀般从索引问题⼊⼿，可以较快解决问题。

实例1 索引问题解决的真实案例及思路

⼀条SQL在MySQL中⼤概执⾏过程
1. 客户端发送⼀条查询给服务器
2. 服务器先检查查询缓存，如果命中缓存，则⽴即返回缓存结果，否则进⼊下⼀阶段
3. 服务器端进⾏SQL解析，预处理，再由优化器⽣成对应的执⾏计划
4. Mysql根据优化器⽣成的执⾏计划，调⽤存储引擎的API来执⾏查询
5. 将结果返回给客户端
MySQL的执⾏计划是在服务器层⽣成的，但是统计信息是在存储引擎层⽣成的！

SQL优化需要提供的数据信息
1. SQL语句( select ***)
2. SQL的执⾏计划(exlpain )
3. SQL的元数据(show create table tablename)
4. 表的总数据量
案例⼀索引未使⽤SQL优化实例

索引需要注意的事项：

不要让数据库进⾏隐式转换

返回数据集如果占索引的30%以上，那么优化器不会使⽤索引

Where name like 'xiaohua%'可以使⽤索引 where name like '%xiaohua%'不会使⽤索引

使⽤联合索引但是没有索引第⼀列造成了索引空洞⼀般⼀个MySQL查询中，只能使⽤⼀个索引

jion字段如果连接字段类型不⼀样，那么不能使⽤索引

int类型没有办法和varchar类型进⾏join，（char（20）和varchar（20）可以使⽤索引例外，20，25则不⾏）

如果字符集不同，则不能使⽤索引，UTF8转UTF8MB4后不能使⽤

MySQL在否定列⾥⾯不能使⽤索引 where⾥⾯有<>， not in ，not exist等存在的时候

选择性低的列，可以不创建索引（即使创建，CBO⼀般也不会使⽤）男⼥列，职业列都不能使⽤索引

在使⽤索引的时候，采⽤的是最左匹配原则 如果是单列索引，可以使⽤的功能组合是(a),(a,b)(a,b,c)并且索引是⼀次遍历没有回溯的
```
SQL语句
select * from sys_vol20170424
where NOT EXISTS(
select * from sys_standard20170424
where sys_vol20170424.issue_id =sys_standard20170424.issue_id);
```
内部表和外部表不相同的数据，返回到结果集

  对应表的元数据
  CREATE TABLE `sys_vol20170424` ( `issue_id` varchar(20) NOT NULL DEFAULT '', `journal_id`
  varchar(50) DEFAULT NULL COMMENT '期刊（⺟体）ID', `journal_name` varchar(255) DEFAULT NULL COMMENT
  '期刊（⺟体）名称', `volid` varchar(50) DEFAULT NULL COMMENT '卷期ID', `standard_year` varchar(20)
  DEFAULT NULL, `standard_volume` varchar(20) DEFAULT NULL, `standard_info` varchar(255) DEFAULT
  NULL, `TimeCreate` datetime DEFAULT NULL COMMENT '创建时间', `VERSION` int(11) DEFAULT '0' COMMENT
  '版本', `title` varchar(255) DEFAULT NULL, `status` varchar(255) DEFAULT 'FGF' COMMENT '规范状态
  （FGF：⾮规范；GF：已规范；）', `pj_status` int(11) DEFAULT '0' COMMENT '是否含有篇级信息（0：有；1：⽆）',
  PRIMARY KEY (`issue_id`), KEY `test_1` (`journal_id`) USING BTREE, KEY `test_2`
  (`journal_id`,`standard_year`,`standard_volume`,`standard_info`) USING BTREE) ENGINE=InnoDB DEFAULT
  CHARSET=utf8 COMMENT='卷期信息表'
  
  
  CREATE TABLE `sys_standard20170424` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `VERSION` int(11)
  DEFAULT NULL, `issue_id` bigint(20) DEFAULT NULL, `journal_id` varchar(255) DEFAULT NULL,
  `journal_name` varchar(255) DEFAULT NULL, `standard_create_time` datetime DEFAULT NULL,
  `standard_info` varchar(255) DEFAULT NULL, `standard_status` varchar(255) DEFAULT NULL,
  `standard_update_time` datetime DEFAULT NULL, `standard_volume` varchar(255) DEFAULT NULL,
  `standard_year` varchar(255) DEFAULT NULL, PRIMARY KEY (`ID`)) ENGINE=InnoDB AUTO_INCREMENT=592008
  DEFAULT CHARSET=utf8

需求：

sys_vol20170424表是原数据表

sys_standard20170424是规范后的数据表

在数据表⾥规范了未规范的数据，然后把这些修改完的添加到规范后的表⾥

SQL遇到的问题

是这样的，该SQL跑不出来，就是执⾏后，数据库⻓时间不返回结果

初步判断

⼦查询导致没有使⽤索引
索引没有正确建⽴

思路1

⾸先，在mysql 5.1 5.5等版本中，MySQL对于⼦查询的⽀持并不是很好，⼦查询的效率很低

所以在⽇常的SQL优化中，建议⼤家如果可以的话，⾸先将⼦查询的SQL改写为链接查询（然⽽优化器很多时候也会尝试这么做）。

所以根据习惯，我先优化为表连接查询

select tmpa.* from sys_vol20170424 tmpa
left join sys_standard20170424 tmpb
on tmpa.issue_id=tmpb.issue_id
where tmpb.issue_id is null

改写后发现依然不⾏，索要执⾏计划和表结构

于是查看表结构发现sys_standard20170424这张表中，在表连接查询的关联条件中 sys_vol20170424.issue_id =sys_standard20170424.issue_id 并没有建⽴索引，本身也不是主键，所以问题⾸先尝试给表sys_standard20170424建⽴索引

索引建⽴完成后，再次跑SQL，然后发现SQL⾮常快就可以跑出来，然后再次索要执⾏计划发现已经⾛了索引，问题解决。

案例二分页查询的优化措施

在互联⽹的业务中，⼤多数WEB业务是针对⽹⻚的，那么肯定离不开分⻚查询。

在分⻚查询的过程中，如果数据集中数据量过⼤，可能会造成SQL的压⼒。
```
select id from test limit 10000000,10
```
这条sql中，该SQL的执⾏过程应该是⾸先扫描到10000000⾏，然后从1000w⾏往后取⼗条数据，那么这个过程肯定是全表扫描⼗万⾏，然后取出⼗⾏数据

那么我可以通过where先把数据取出来，再进⾏分⻚
```
select id from big_data where id>5000000 limit 10;
```
这样的话，因为id是主键，可以通过主键索引进⾏获取数据后再进⾏limit取数据，避免全表扫描。

案例三对于OR的优化

如果sql中多列使⽤or的话，则不会⾛索引，此时需要使⽤union all进⾏改写原来sql

select uid from test where name=‘d’ or age=3;

改写为

select uid from test where name=’a'
Union all
select uid from test where age=3

案例四对于order by，group by的优化

根据索引字段排序，利⽤索引取出的数据已经是排好序的，直接返回给客户端；
没有⽤到索引，将取出的数据进⾏⼀次排序操作后返回给客户端。

MySQL的SQL中虽然使⽤了order by但是并没有进⾏排序，因为本身从索引中取出的数据就是排序好的

filesort 并不是说通过磁盘⽂件进⾏排序，⽽只是告诉我们进⾏了⼀个排序操作⽽已

优化措施

将排序放在代码中
使⽤正确的索引规避排序问题

mysql> explain select * from t2 order by id desc;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--
--------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows |
filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--
--------+-------+
| 1 | SIMPLE | t2 | NULL | index | NULL | PRIMARY | 4 | NULL | 6 |
100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--
--------+———+

没有索引列进行排序的过程中，需要使用文件排序

mysql> explain select * from t2 order by name asc;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+------
----+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows |
filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+------
----+----------------+
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 6 |
100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+------
----+----------------+

在使用复合索引的过程中，没有使用索引第一个列造成了索引空洞

mysql> alter table t2 add index idx_na(name,age);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from t2 order by age asc;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+---
-------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows |
filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+---
-------+-----------------------------+
| 1 | SIMPLE | t2 | NULL | index | NULL | idx_na | 19 | NULL | 6 |
100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+---
-------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

使用复合索引规避文件排序

mysql> explain select * from t2 order by name,age asc;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+---
-------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows |
filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+---
-------+-------------+
| 1 | SIMPLE | t2 | NULL | index | NULL | idx_na | 19 | NULL | 6 |
100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+---
-------+-------------+

如果排序顺序不⼀致，同样会使⽤⽂件排序

mysql> explain select * from t2 order by name desc,age asc;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+---
-------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows |
filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+---
-------+-----------------------------+
| 1 | SIMPLE | t2 | NULL | index | NULL | idx_na | 19 | NULL | 6 |
100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+---
-------+-----------------------------+

Group by 的优化和order by的情形类似，需要在group by的列后⾯加上索引

mysql> explain select age from t2 group by age;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+---
-------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows |
filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+---
-------+----------------------------------------------+
| 1 | SIMPLE | t2 | NULL | index | idx_na | idx_na | 19 | NULL | 6 | 
100.00 | Using index; Using temporary; Using filesort |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+---
-------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

使用了索引后，避免了临时表和磁盘排序

mysql> explain select age from t2 group by name,age;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+---
-------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows |
filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+---
-------+-------------+
| 1 | SIMPLE | t2 | NULL | index | idx_na | idx_na | 19 | NULL | 6 | 
100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+---
-------+-------------+

不要使⽤select

Select * 和select 所有字段，其实最终性能差别就是多访问⼀次数据字典，性能差距很⼩

那么为什么还不叫使⽤select 呢
1. 返回结果集如果需求是⼀部分字段，⽽你使⽤*的话，那么会增加⽹络开销（然⽽oltp下，增加⼏列数据，开销很⼩）
2. *偶尔会造成索引选择错误（主要是底层影响了优化器⾃动重写SQL）
3. *和全表扫描任何关系，全表还是索引只由where谓语决定。
4. 主要是表结构解耦，例如select a.*,id from test.t1此时a表没有id列，那么这个sql执⾏是正确的，如果在某次schema改动中，在a表中添加了⼀列id列，那么就该出问题了。⽽且是线上的环境，⾮常麻烦。
```
mysql> select t1.*,id from test.t1,test.t2 where t1.id=t2.id;
ERROR 1052 (23000): Column 'id' in field list is ambiguous
mysql> select t1.*,t2.id from test.t1,test.t2 where t1.id=t2.id;
```
这种情况需要提醒开发，设置合理的开发规范，防止在项目上线后踩坑
两个常⻅的索引使⽤误区

误区⼀：对查询条件的每个字段建⽴单列索引，例如查询条件为：A=？and B=？and C=？。

在表上创建了3个单列查询条件的索引ind_A(A)，ind_B(B)，ind_C©，应该根据条件的过滤性，创建适当的单列索引或者组合索引。

误区⼆：对查询的所有字段建⽴组合索引，例如查询条件为select A,B,C,D,E,F from T where G=？。

在表上创建了ind_A_B_C_D_E_F_G(A,B,C,D,E,F,G)

5、MySQL字符类型优化

主键类型的选择

对于主键类型，因为MySQL是索引组织表，所以尽量在选择主键的时候，尽量短⼩，并且尽量使⽤int类型

录⼊⼿机号，使⽤bigint类型

开发⼈员在录⼊⼿机号的时候，如果仅仅是录⼊13102781234这种⼿机号，那么可以使⽤bigint类型，如果使⽤varchar类型，则会造成因为varchar类型的效率并没有int类型⾼

如果是86-1310271234这种，则可以使⽤varchar 或者拆分到两个字段

使⽤int类型会插⼊错误
mysql> insert into t2 values (1,13102783599);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from t2;
+------+------------+
| id | tel |
+------+------------+
| 1 | 2147483647 |
+------+——————+
t3使⽤了bigint类型
mysql> insert into t3 values (1,13102783599);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t3;
+------+-------------+
| id | tel |
+------+-------------+
| 1 | 13102783599 |
+------+-------------+
1 row in set (0.00 sec)

ip类型使⽤⽆符号int整形

MySQL⾥⾯提供了两个函数inet_aton(),他需要把ip转化成数字，然后进⾏操作。

Inet_ntoa()负责转换成数字，然后进⾏操作。

使⽤⽆符号整形存储IP地址，有符号整形存储会造成溢出问题。

CREATE TABLE `t4` (
 `id` int(11) DEFAULT NULL,
 `ip` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> insert into t4 values(1,inet_aton('192.168.1.1'));
mysql> select * from test.t4;
+------+------------+
| id | ip |
+------+------------+
| 1 | 3232235777 |
+------+------------+
1 row in set (0.00 sec)
mysql> select inet_ntoa(ip) from test.t4；
+---------------+
| inet_ntoa(ip) |
+---------------+
| 192.168.1.1 |
+---------------+

根据需要选择最小的数据类型
1. 可以使⽤tinyint的时候就不使⽤int，可以使⽤int类型，就不使⽤varchar类型
2. 可以减少硬盘往内存载⼊数据的时候的开销
3. 同时返回客户端数据集的时候，也可以在⼤数据量下减少开销
varchar 和char的存储区别及使⽤
1. MySQL中varchar类型的计算，最多存储65536个字节
2. varchar（11)表示的是有11个字符，⽽不是11个字节,
3. varchar占⽤空间的⼤⼩根据字符集不同，varchar占⽤的空间不同
4. ⻓度⼩于255⽤⼀个字节存储⻓度，⼤于255⽤两个字节存储⻓度
5. latin1 ⼀个字符占⽤1字节
6. gbk ⼀个字符占⽤两个字节，最⼤⻓度varchar(32766) ,超过报错(65536/2=32768)
7. utf8 ⼀个字符占⽤三个字节，最⼤⻓度varchar(21844),超过报错（65536/3=21845.3）
8. 更⻓的varchar列，会消耗更多的内存，虽然msyql在磁盘存储是根据字符串⻓度来确定，但是数据载⼊内存后， mysql确是根据固定⼤⼩的内存块来保存内部值的
char类型⾮常适合⽤来存储md5值，⽽且char类型不容易产⽣碎⽚。
使⽤合适的数据库事务隔离级别

使⽤RR级别的话，可以保证数据库没有幻读，但是在该模式下，会增加锁竞争，造成数据库并发能⼒的下降。

在RC模式下，没有next_lock的存在，即使在没有索引的情况下，也很难造成⼤规模的锁表⽽导致的死锁问题，这个问题在OLTP中表现的尤其严重。
关于BOLB和TEXT

blob和text都是为了存储很⼤的数据⽽设计的字符串数据类型，分别采⽤了⼆进制和字符串来存储。

在进⾏blob或者text的排序过程中，MySQL会使⽤特殊的处理⼿段，同时会造成⼤量的磁盘IO，所以这种数据类型的处理相⽐于其他数据类型，处理效率很低下，应该尽量避免使⽤，如果不得已⽽使⽤的话，那么应该将blob或者text放置到每个表的末尾列，放置⻚分裂后，导致查询过程中多余的⻚录⼊。或者将该列拆分到其他表中。
使⽤tinyint类型⽽不是⽤枚举类型
1. 使⽤int类型完全可以满⾜枚举的⽬的，⽐如（girl 1 boy 2），那么直接在数据字典中写明就好。
2. 枚举类型在使⽤过程中，字符串列表是固定的，⽽且修改麻烦，修改字符串必须进⾏alter table操作，可以直接使⽤ tiny类型来保存。
3. 在弱语⾔类型中(⽐如PHP，数据类型可以被忽略的语⾔)，容易造成错误。
表设计中避免太多的列
1. MySQL的存储引擎在⼯作的时候，需要在服务层和存储引擎层不断通过⾏缓冲拷⻉数据，然后在服务器层将缓冲内容解码成各个列，在这个过程中开销是⾮常⾼的，⼀般⼀个MySQL的列数应该控制在40-60上限
2. 同时MySQL的设计是⾏式数据库，⼤量的列的存在，在获取数据的过程中，会造成⼤量的冗余的数据交互

6、MySQL业务与架构优化

范式和反范式设计模式

范式要求数据表中不存在任何的传递函数依赖

反范式允许表中存在传递函数依赖

范式与反范式的比较：
1. 查询记录时，范式模式往往要进行多表连接，而反范式只需在同一张表中查询，当数据量很大的时候，显然反范式的效率会更好。
2. 反范式有很多重复的数据，会占用更多的内存，查询时可能会较多地使用DROUP BY或DISTINCT等耗时耗性能的关键字。
3. 当要修改更新数据时（例如修改Accounting部门的领导为Russell），范式更灵活，而反范式要修改全部的数据，且易出错。
适当使⽤反范式，⽐如在电商的或者交易系统中，⼀般会有下单业务和订单统计业务，那么在如果将下单业务这种多写⼊，修改的表和经常做全表统计的表放在⼀起，那么会很容易造成读写锁冲突，容易在RR模式下造成死锁，这时候的办法是，在程序代码中，插⼊两份数据，⼀份插⼊到下单表，⼀份插⼊到订单统计表，这样虽然数据有了冗余但是可以保证统计业务和下单业务不做冲突。
避免外键

外键约束会造成每次在插⼊数据的时候，都需要检查⼀下被参照表有没有对应的数据，造成数据库的性能严重下降，现在业界⼏乎已经不允许外键的存在。⽽是通过代码去实现数据的完整性。
MySQL链接池的使用

现在普遍在MySQL的使⽤中，会使⽤到链接池，链接池⼀般使⽤在程序端（⽐如c3p0），该程序会⼀直保持和数据库的链接，如果程序需要从数据库查询数据，则不需要再次从数据库获取链接，⽽直接使⽤连接池的链接进⾏操作。这样避免创建链接的开销
MySQL线程池的使用

MySQL线程池，每个MySQL链接到MySQL数据库后，MySQL会创建⼀个对应的线程提供服务，在这个过程中CPU的线程创建需要时间和cpu资源，在OLTP环境中，⼤量的链接涌⼊进来，会造成不⼩的开销，所以在MySQL内部使⽤线程池，可以避免不断的线程创建销毁的消耗，线程池在社区版MySQL中并没有开放，但是在商业版MySQL，mariadb 和 percona中有开放。
```
mariadb开启线程池
默认没有开启线程池
thread_handling one-thread-per-connection
thread_pool_max_threads 1000
在mariadb配置⽂件中开启线程池(只读参数)
thread_handling=pool-of-threads
thread_pool_max_threads 1000
```

日志审计功能

percona 5.6和mariadb 10.x版本后具有了审计功能（原版mysql不具备）

安装审计插件
MariaDB [(none)]> install plugin server_audit soname 'server_audit.so’;
配置⽂件添加
server_audit_logging = ON
server_audit_file_path =/home/mysql3306/logs/server_audit.log

数据拆分

⼀般的公司架构层，数据架构的拆分过程

单库数据库–>数据库读写分离–>缓存技术–>数据的垂直拆分–>数据的⽔平拆分

MySQL再优秀，也是单节点，单节点的处理能⼒是有限的，纵向的扩展能⼒也⾮常有限，那么我可以通过⼈为的数据拆分达到分散压⼒的⽬的

按照时间拆分

可以⽇志数据进⾏按照每天或者每个⽉进⾏拆分，每天形成⼀个表或者每个⽉形成⼀个表，从⽽达到分散压⼒的⽬的。

垂直拆分

⼀个数据库由多个表构成，每个表代表着不同的业务，垂直拆分是为了能够按照业务进⾏分类，分布到不同的数据库上，也就是将不同的压⼒分散到不同的数据库上。

对相同的表中的不同的业务拆分到不同的表。

⼀个系统由不同的模块组成，在架构设计中，各个模块之间的交互点越统⼀，那么耦合度越低，这样的系统进⾏垂直拆分越简单，当数据发展到⼀定规模，负载很⼤的情况，就必须要做分割。

垂直拆分的优点： 规则简单，实施更为⽅便，适合业务耦合度低，业务清晰的系统，拆分规则⽐较清晰。

拆分后的优缺点

优点:
1. 拆分后业务清晰，拆分规则明确。
2. 系统之间整合或扩展容易。
3. 数据维护简单。
缺点:
1. 部分业务表⽆法join，只能通过接⼝⽅式解决，
2. 提⾼了系统复杂度。
3. 受每种业务不同的限制存在单库性能瓶颈，不易数据扩展跟性能提⾼。
4. 事务处理复杂。
水平拆分

于垂直切分相⽐，相对来说稍微复杂⼀些。因为要将同⼀个表中的不同数据拆分到不同的数据库中，对于应⽤程序来说，拆分规则本身就较根据表名来拆分更为复杂，后期的数据维护也会更为复杂⼀些。

相对于垂直拆分，⽔平拆分不是将表做分类，⽽是按照某个字段的某种规则来分散到多个库之中，每个表中包含⼀部分数据。简单来说，我们可以将数据的⽔平切分理解为是按照数据⾏的切分，就是将表中的某些⾏切分到⼀个数据库，⽽另外的某些⾏⼜切分到其他的数据库中

水平拆分就是对相同的业务再进⾏拆分

几种典型的拆分规则：

几种典型的分片规则包括:
1. 按照⽤户ID求模，将数据分散到不同的数据库，具有相同数据⽤户的数据都被分散到⼀个库中。
2. 按照⽇期，将不同⽉甚⾄⽇的数据分散到不同的库中。
3. 按照某个特定的字段求摸，或者根据特定范围段分散到不同的库中。
数据做了拆分优缺点

优点有:
1. 拆分规则抽象好，join操作基本可以数据库做。
2. 不存在单库⼤数据，⾼并发的性能瓶颈。
3. 应⽤端改造较少。提⾼了系统的稳定性跟负载能⼒。
缺点有:
1. 拆分规则难以抽象。
2. 分⽚事务⼀致性难以解决。
3. 数据多次扩展难度跟维护量极⼤。
4. 跨库join性能较差
两种拆分方式共同的特点缺点：
1. 引⼊分布式事务的问题。跨节点Join的问题。
2. 跨节点合并排序分⻚问题。多数据源管理问题
数据源管理思路：
1. 客户端模式，在每个应⽤程序模块中配置管理⾃⼰需要的⼀个(或者多个)数据源，直接访问各个数据库，在模块内完成数据的整合
2. 中间代理层来统⼀管理所有的数据源，后端数据库集群对前端应⽤程序透明
数据库读写分离

在互联⽹业务中，典型的OLTP场景中，读业务（select）是远⼤于写（insert）业务的，那么在这种情况下，很⼤程度上，写业务会对读业务造成影响，也是同时的情况下，读写业锁会互相⼲扰。那么这种情况下，需要对主库做⼀个从库，进⾏读写分离，读写分离后，可以对多从库进⾏负载均衡。