mysql数据库深入学习

2022-10-12 19:46:10

mysql 数据库

一、数据库介绍

1、关系型数据库的特点

? 二维表
典型产品Oracle传统企业，MySQL是互联网企业
数据存取是通过SQL
最大特点，数据安全性方面强（ACID）

2、NoSQL：非关系型数据库（Not only SQL）

? 不是否定关系型数据库，做关系型数据库的的补充。

3、web1.0时代

? 企业提供内容，用户浏览，所以关系型数据库够用，并发并不高，所以不需要NoSQL。

4、web2.0时代

? 核心是企业提供平台，用户参与提供内容。这时关系型数据库无法满足需求了。

5、2003 NoSQL出现

? memcached诞生，关注的点是性能，而安全性关注比较低。随着安全性需求不断提升，所以有了redis。

6、redis特点

? 依然高性能该并发，

? 数据持久化的功能

? 支持多数据类型，主从复制和集群

? 管理不再使用SQL了

7、NoSQL特性总览：

? 不是否定关系型数据库，而是作为补充，现在也有部分替代的趋势。

? 关注高性能，高并发，灵活性，忽略和上述无关的功能。

? 现在也在提升安全性和使用功能。

? 典型产品：Redis（持久化缓存，两个半天）、MongoDB（最接近关系型数据的NoSQL）、Memcached。

? 管理不适用SQL管理，而是用一些特殊的API或数据接口。

8、NoSQL的分类、特点、典型产品

? 键值（KV）存储：Memcached、Redis；

? 列存储（column-oriented）：HBASE(新浪，360)、Cassandra（200台服务器集群）

? 文档数据库（document-oriented）：MongoDB（最接近关系型数据库的NoSQL）

? 图形存储（Graph）：Neo4j。

9、mysql发展史

1979年，报表工具Unireg出现。
1985 年，以瑞典David Axmark为首，成立了一家公司（AB前身），IASM引擎出现。
1990年，提供SQL支持。
1999-2000年，MySQLAB公司成立，并公布源码，开源化。
2000年4月BDB引擎出现，支持事务。
2008年1月16号MySQL被Sun公司收购。
2009年04月20日Oracle收购Sun公司，MySQL转入Oracle 门下。

10、mysql特点

开源
社区版免费
简单，使用方便，可靠
稳定、安全
社区活跃

二、mysql安装及启动

1、mysql安装方式介绍

1.RPM、Yum：安装方便、安装速度快，无法定制
2.二进制：不需要安装，解压即可使用，不能定制功能
3.编译安装：可定制，安装慢。
5.5之前：./configure make make install
5.5之后：cmakegmake
4.先编译，然后制作rpm，制作yum库，然后yum安装。
简单、速度快、可定制，比较复杂制作时间长
企业选择安装方式
中小企业：以上方式都可以，运维偏向编译，dba偏向选择二进制。
大型企业：可以选择4

2、mysql安装

一、MySQL5.6.36安装前准备
（1）克隆一个模板机器（使用centos6），克隆完做快照
（2）IP 10.0.0.52 主机名db02
（3）iptables   selinux （关闭）
（4）下载好5.6.36
（5）安装依赖包
     yum  install  -y  ncurses-devel libaio-devel
（6）安装cmake
	yum install cmake –y
（7）创建用户
    useradd -s /sbin/nologin -M mysql
	id mysql
二、MySQL下载安装
（0）创建软件下载目录：
mkdir -p /server/tools
cd /server/tools/
（1）下载并上传到/server/tools
https://www.mysql.com/downloads/
社区版MySQL Community Server
（2）解压：
cd /server/tools
tar xf mysql-5.6.36.tar.gz
（3）安装：
cd mysql-5.6.36
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.36 -DMYSQL_DATADIR=/application/mysql-5.6.36/data -DMYSQL_UNIX_ADDR=/application/mysql-5.6.36/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITH_ZLIB=bundled -DWITH_SSL=bundled -DENABLED_LOCAL_INFILE=1 -DWITH_EMBEDDED_SERVER=1 -DENABLE_DOWNLOADS=1 -DWITH_DEBUG=0

make && make install

三、配置并启动
（1）制作软连接：
[root@centos6-kvm3 mysql-5.6.36]# ln -s /application/mysql-5.6.36/ /application/mysql
（2）拷贝配置文件到/etc:
[root@centos6-kvm3 support-files]# cp my-default.cnf /etc/my.cnf 
（3）初始化数据库：
[root@centos6-kvm3 support-files]# /application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data --user=mysql
（4）创建关键目录并设置权限:
[root@centos6-kvm3 support-files]# mkdir -p /application/mysql/tmp
[root@centos6-kvm3 support-files]# chown -R mysql.mysql /application/mysql/
（5）复制启动脚本到/etc/init.d/mysqld
[root@centos6-kvm3 support-files]# cp mysql.server /etc/init.d/mysqld 
（6）启动数据库
[root@centos6-kvm3 support-files]# /etc/init.d/mysqld start
[root@centos6-kvm3 support-files]# ps -ef | grep mysql
[root@centos6-kvm3 support-files]# netstat -lntp | grep 3306
（7）配置环境变量
[root@centos6-kvm3 support-files]# echo ‘PATH=/application/mysql/bin/:$PATH‘>>/etc/profile
[root@centos6-kvm3 support-files]# tail /etc/profile
[root@centos6-kvm3 support-files]# source /etc/profile
[root@centos6-kvm3 support-files]# echo $PATH
/application/mysql/bin/:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
[root@centos6-kvm3 support-files]# mysql
mysql> select user,host,password from mysql.user;

3、客户端程序连接到MySQL方式

通过网络连接串
mysql -uroot -poldboy123 -h 10.0.0.200
通过套接字文件
mysql -uroot -poldboy123 -S /tmp/mysql.sock

4、mysql 的SQL层处理：

SQL:
	1、接收到连接层送过过来的“SQL”
	2、由专门的模块，会判断SQL语法、语义（SQL语句的类型：DDL、DCL、DML）
	3、将不同类型的语句，送到专门的处理接口（解析器）
	4、解析器，将SQL解析成执行计划
	5、优化器，会选择“它”最优的执行计划交给执行器
	6、执行器，执行 执行计划，得出如何去“磁盘”获取数据的方法
	7、专门线程将获取数据的方法，送给下层（存储引擎层）继续处理。
	8、验证授权，当前用户对 库或表对象有没有操作的权限。
	9、查询高速缓存query_cache。
	10、记录修改操作日志binlog。
	
mysql 表存储
1、数据文件
   页（默认是16k）：是mysql数据库存储的最小单元
   区：连续的多个页组成
   段：一个表（分区表）就是一个段，包含了多个区

5、mysql常用命令

mysql 登录命令
-u   用户名
-p   密码
例子： 
mysql -uroot -poldboy123
-h ip
例子：
[root@centos6-kvm3 support-files]# mysql -uroot -poldboy123 -h 127.0.0.1
[root@centos6-kvm3 support-files]# mysql -uroot -poldboy123 -h localhost
mysql -uroot -poldboy123 -h 10.0.0.52
-P 3308
例子：

mysql -uroot -poldboy123 -h 10.0.0.52 -P 3308
[root@centos6-kvm3 support-files]# mysql -uroot -poldboy123 -P 3306
-S /tmp/mysql.sock
例子：
[root@centos6-kvm3 support-files]# mysql -uroot -poldboy123 -S /application/mysql/tmp/mysql.sock 
mysql -uroot -poldboy123 -S /application/mysql/tmp/mysql.sock
-e "show variables like ‘server_id‘;"
例子：
mysql -uroot -poldboy123  -e "show variables like ‘server_id‘;"
修改密码：
[root@centos6-kvm3 support-files]# mysqladmin -uroot -p password oldboy123
Enter password:

6、清理不常用的数据库

select user,host from mysql.user;
drop user ‘‘@‘db02‘;
drop user ‘‘@‘localhost‘;
drop user ‘root‘@‘db02‘;
drop user ‘root‘@‘::1‘;
select user,host from mysql.user;
drop database test;
show databases;

三、mysql的基本管理

1、数据库关闭启动

数据库启动流程：

关闭：
[root@centos6-kvm3 support-files]# service mysql stop
[root@centos6-kvm3 support-files]# mysqladmin -uroot -poldboy123 shutdown
启动：
[root@centos6-kvm3 support-files]# service mysqld start
[root@centos6-kvm3 support-files]# /application/mysql/bin/mysqld_safe &
野蛮关闭数据库方式：
kill -9 ?
第三种为利用系统进程管理命令关闭MySQL。
kill pid#<==这里的pid为数据库服务对应的进程号。
killall mysqld#<==这里的mysqld是数据库服务对应的进程名字。
pkill mysqld #<==这里的mysqld是数据库服务对应的进程名字。

启动报错案例:
[root@centos6-kvm3 data]# service mysqld start
Starting MySQL. ERROR! The server quit without updating PID file (/application/mysql-5.6.36/data/centos6-kvm3.pid).

查看错误日志：
[root@centos6-kvm3 data]# pwd
/application/mysql/data
[root@centos6-kvm3 data]# cat centos6-kvm3.err 
查看报错部分：
2020-03-19 00:07:20 23029 [ERROR] /application/mysql-5.6.36/bin/mysqld: Can‘t find file: ‘./mysql/user.frm‘ (errno: 13 - Permission denied)

[root@centos6-kvm3 data]#  ll
drwx------ 2 root  root      4096 Mar 18 21:32 mysql
[root@centos6-kvm3 data]# chown -R mysql.mysql mysql

编译启动：
[root@centos6-kvm3 data]# mysqld_safe --socket=/tmp/mysql.sock --port=3307 &
[root@centos6-kvm3 data]# mysql -uroot -poldboy123 -S /tmp/mysql.sock 
[root@centos6-kvm3 data]# ps -ef | grep mysql
mysql启动参数设置：
1、预编译时候设置参数，参数会硬编码到程序中。
2、命令行方式设定启动参数。
3、初始化的配置文件，/etc/my.cnf
启动优先级：2>3>1
影响到什么？
①影响数据库的启动
[mysqld]
[mysqld_safe]
[server]

②影响到数据库的链接
[mysql]
[mysqladmin]
[mysqldump]
[client]

2、mysql配置文件

配置文件案例：
[root@centos6-kvm3 data]# vim /etc/my.cnf 
[mysqld]
basedir=/application/mysql
datadir/application/mysql/data
socket=/application/mysql/tmp/mysql.sock
port=3306
server_id=10
log-error=/var/log/mysql.log #错误日志
log-bin=/application/mysql/data/mysql-bin
binlog_format=row
skip_name_resolve #跳过域名解析
[mysql]
#客户端
socket=/application/mysql/tmp/mysql.sock
 
[root@centos6-kvm3 data]# service mysqld start
Starting MySQL. SUCCESS! 
[root@centos6-kvm3 data]# ps -ef | grep mysql

自定义配置文件启动：
[root@centos6-kvm3 ~]# cat /tmp/aa.txt 
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/application/mysql/tmp/mysql.sock
port=3309
server_id=11
log-error=/var/log/mysql.log
log-bin=/application/mysql/data/mysql-bin
binlog_format=row
skip_name_resolve
[root@centos6-kvm3 data]# mysqld_safe --defaults-file=/tmp/aa.txt
[root@centos6-kvm3 ~]# ps -ef | grep mysql

3、配置mysql多实例

思路:
1、启动多个mysqld进程
2、规划多套数据
3、规划多个端口
4、规划多套日志路径

多实例配置
1、创建多套目录
mkdir -p /data/330{7,8,9}
2、准备多套配置文件
vi /data/3307/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3307
server-id=3307
port=3307
log-bin=/data/3307/mysql-bin
socket=/data/3307/mysql.sock
log-error=/data/3307/mysql.log

vi /data/3308/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3308
server-id=3308
port=3308
log-bin=/data/3308/mysql-bin
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log

vi /data/3309/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3309
server-id=3309
port=3309
log-bin=/data/3309/mysql-bin
socket=/data/3309/mysql.sock
log-error=/data/3309/mysql.log
3、初始化多套数据
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3307 --user=mysql
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3308 --user=mysql
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3309 --user=mysql
4、启动多个实例
mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/my.cnf &
5、查看启动端口
netstat -lnp |grep 330
6、分别连接测试
mysql -S /data/3307/mysql.sock -e "show variables like ‘server_id‘;"
mysql -S /data/3308/mysql.sock -e "show variables like ‘server_id‘;"
mysql -S /data/3309/mysql.sock -e "show variables like ‘server_id‘;"

如果出现闪退，检查是否端口已经被使用：需要关闭在用的端口
mysqladmin -uroot -poldboy123 -S /tmp/mysql.sock shutdown

四、数据库用户管理

1、用户定义

用户定义：
mysql> select user,host,password from mysql.user;
+------+--------------+-------------------------------------------+
| user | host         | password   

==================================

user                     主机范围
使用某个用户              从哪些主机地址可以访问我的数据库

用户的功能：
1、用来登录mysql数据库
2、用来管理数据库对象（库，表）

权限：
	功能：针对不同的用户，设置不同的对象管理能力。
    select updata delete insert creat ...
权限的范围：
*.*  :全局范围
oldboy.* :单库级别
oldboy.t1 :单表级别
创建用户并授权：
grant 	all  on  wordpress.* to workpress@‘10.0.0.%‘ identified by ‘oldboy123‘;
授权命令 权限     权限范围		用于		主机范围 				密码

修改超级管理员用户：root
修改密码：mysqladmin -uroot -p password oldboy123
root@localhost
普通用户：select,updata,delete,insert,create,drop (增删改查)

只针对用户的操作命令：
mysql> create user zabbix@‘10.0.0.%‘ identified by ‘oldboy123‘;
Query OK, 0 rows affected (0.01 sec)
mysql> drop user root@‘127.0.0.1‘;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from mysql.user;
特殊的删除方法:
mysql> delete from mysql.user where user=‘oldboy‘ and host=‘localhost‘; 
Query OK, 1 row affected (0.00 sec)
mysql> flush privileges;

2、用户授权

grant 	all  on  wordpress.* to workpress@‘10.0.0.%‘ identified by ‘oldboy123‘;
授权命令 权限     权限范围		用于		主机范围 

all权限:
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES,
INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, 
REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, 
CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

开发用户权限：（root用户进行授权）
	grant SELECT,INSERT, UPDATE, DELETE, CREATE, DROP on testdb.* to zabbix@‘10.0.0.%‘；
使用zabbix检查：
mysql> create database testdb;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
创建用户并授权：
mysql> grant all on *.* to root@‘10.0.0.%‘ identified by ‘oldboy123‘;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from mysql.user;
查询用户的权限：
mysql> show grants for zabbix@‘10.0.0.%‘;
创建类似管理员：
mysql> show grants for root@‘10.0.0.%‘;
本地超级管理员：有grants权限
mysql> show grants for root@‘localhost‘;
收回权限：
mysql> revoke create,drop  on testdb.* from zabbix@‘10.0.0.%‘;
mysql> show grants for zabbix@‘10.0.0.%‘;

思考：
grant select on *.* to zabbix@‘10.0.0.%‘;
grant INSERT, UPDATE, DELETE, CREATE, DROP on testdb.* to zabbix@‘10.0.0.%‘;
grant update on testdb.t1 to zabbix@‘10.0.0.%‘;

###
mysql> use testdb;
mysql> create table t1(id int);
mysql> show tables;
mysql> insert into t1 values(1);

问：zabbix@‘10.0.0.%‘ 对t1 表到底有什么权限？
如果对某个用户在不同的数据库级别设置了权限，最终权限权限叠加，加起来的最大权限为准。
建议，不要多范围授权。

五、SELECT高级应用

1、课程大纲

? 开发环境准备
? select语句的基本语法格式
? WHERE子句
? ORDER BY子句
? LIMIT子句
? 多表连接查询（join、using） ? 集合操作（union） ? group by与having字句
? 子查询

2、开发环境

? 导入world.sql
? 创建用户，使用sqlyog登录数据库

3、select语法环境

help select;
SELECT
[ALL | DISTINCT | DISTINCTROW ]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[Asc | DEsc], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[Asc | DEsc], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[FOR UPDATE | LOCK IN SHARE MODE]]

4、WHERE子句

? 基本语法

SELECT *|{[DISTINCT] column|select_expr [alias], ...]}  

[FROM [database.]table] 

[WHERE conditions];

? WHERE条件又叫做过滤条件，它从FROM子句的中间结果中去掉所有

条件conditions不为TRUE（而为FALSE或者NULL）的行

? WHERE子句跟在FROM子句后面

? 不能在WHERE子句中使用列别名

例1：where字句的基本使用

SELECT * FROM world.`city` WHERE CountryCode=‘CHN‘; 

or 

SELECT * FROM world.`city` WHERE CountryCode=‘chn‘;

注意：

WHERE中出现的字符串和日期字面量必须使用引号括起来

这里，字符串字面量写成大写或小写结果都一样，即不区分大小写进行查询。

这和ORACLE不同，ORACLE中WHERE条件中的字面量是区分大小写的

例2：where字句中的逻辑操作符：

SELECT * FROM world.`city`  

WHERE CountryCode=‘chn‘ AND district = ‘anhui‘;

? 逻辑操作符介绍：

– and

逻辑与。只有当所有的子条件都为true时，and才返回true。否则返回false或null

– or

逻辑或。只要有一个子条件为true，or就返回true。否则返回false或null

– not

逻辑非。如果子条件为true，则返回false；如果子条件为false，则返回true

– xor

逻辑异或。当一个子条件为true而另一个子条件为false时，其结果为true；

当两个条件都为true或都为false时，结果为false。否则，结果为null

例3 ：where字句中的范围比较：

SELECT * FROM world.`city`  

WHERE  

population BETWEEN 100000 AND 200000 ;

? 例4：where字句中的IN

SELECT * FROM city 

WHERE countrycode IN (‘CHN‘,‘JPN‘);

例5：where字句中的like

– 语法：

like ‘匹配模式字符串’

– 实现模式匹配查询或者模糊查询：

测试一个列值是否匹配给出的模式

– 在‘匹配模式字符串’中，可以有两个具有特殊含义的通配字符：

%：表示0个或者任意多个字符

_：只表示一个任意字符

SELECT * FROM city 

WHERE countrycode LIKE ‘ch%‘;

5、ORDER BY子句

? ORDER BY子句用来排序行

? 如果SELECT语句中没有ORDER BY子句，那么结果集中行的顺序是

不可预料的

? 语法：

SELECT expr 

FROM table 

[WHERE condition(s)] 

[ORDER BY {column, expr, numeric_position} [Asc|DEsc]];

? 其中：

– Asc：执行升序排序。默认值

– DEsc：执行降序排序

– ORDER BY子句一般在SELECT语句的最后面

例1：基本使用

SELECT * FROM city 

ORDER BY population;

? 例2：多个排序条件

SELECT * FROM city 

ORDER BY population,countrycode;

? 例3：以select字句列编号排序

SELECT * FROM city 

ORDER BY 5; 按照第5列进行排序。

? 例4：desc asc

SELECT * FROM city 

ORDER BY 5 desc;

? 例5：NULL值的排序

在MySQL中，把NULL值当做一列值中的最小值对待。因此，升序排序时，它出现在最前面

6、LIMIT子句

MySQL特有的子句。

? 它是SELECT语句中的最后一个子句（在order by后面）。

? 它用来表示从结果集中选取最前面或最后面的几行。

? 偏移量offset的最小值为0。

? 语法：

limit <获取的行数> [OFFSET <跳过的行数>]

或者 limit [<跳过的行数>,] <获取的行数>

SELECT * FROM city 

ORDER BY 5 DEsc 

LIMIT 4;

注：先按照人口数量进行降序排序，然后使用limit从中挑出最前面的4行。

如果没有order by子句，返回的4行就是不可预料的。

7、多表连接查询

传统的连接写法（使用where）

SELECT NAME,ci.countrycode ,cl.language ,ci.population 

FROM city ci , countrylanguage cl 

WHERE ci.`CountryCode`=cl.countrycode;

注意：一旦给表定义了别名，那么原始的表名就不能在出现在该语句

的其它子句中了

? NATURAL JOIN子句

? 自动到两张表中查找所有同名同类型的列拿来做连接列，进行相等连接

SELECT NAME,countrycode ,LANGUAGE ,population 

FROM city NATURAL JOIN countrylanguage 

WHERE population > 1000000 

ORDER BY population;

注意：在select子句只能出现一个连接列

使用using子句

SELECT NAME,countrycode ,LANGUAGE ,population 

FROM city JOIN countrylanguage 

USING(countrycode);

8、集合操作

UNION [DISTINCT]
? UNION ALL
? 语法：
SELECT ... 
UNION [ALL | DISTINCT] 
SELECT ... 
[UNION [ALL | DISTINCT] 
SELECT ...]

? UNION用于把两个或者多个select查询的结果集合并成一个
? 进行合并的两个查询，其SELECT列表必须在数量和对应列的数据类型上保持一致
? 默认会去掉两个查询结果集中的重复行
? 默认结果集不排序
? 最终结果集的列名来自于第一个查询的SELECT列表

9、分组操作及分组处理

“Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个 “小区域”进行数据处理。

? Having与Where的区别

where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分

组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行。

having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚

组函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。

11、子查询

子查询定义

在一个表表达中可以调用另一个表表达式，这个被调用的表表达式叫做子查询（ subquery），我么也称作子选择（subselect）或内嵌选择（inner select）。子查询的结果传递给调用它的表表达式继续处理。
子查询（inner query）先执行，然后执行主查询（outer query）
子查询按对返回结果集的调用方法，可分为：where型子查询，from型子查询及exists 型子查询。

? 使用子查询原则

一个子查询必须放在圆括号中。
将子查询放在比较条件的右边以增加可读性。

子查询不包含 ORDER BY 子句。对一个 SELECT 语句只能用一个 ORDER BY 子句，并且如果指定了它就必须放在主 SELECT 语句的最后。

在子查询中可以使用两种比较条件：单行运算符(>, =, >=, <, <>, <=) 和多行运算符 (IN, ANY, ALL)。

六、mysql数据类型

1、课程大纲

? 数据类型介绍
? 数据类型设置
? 列属性
? 数据类型的字符集用法
? 选择适当的数据类型

2、数据类型介绍

? 四种主要类别：

数值类型
字符类型
时间类型
二进制类型

? 数据类型的 ABC 要素：

Appropriate（适当）
Brief（简洁）
Complete（完整）

? 例 1：列声明

CREATE TABLE people ( 

id INT, 

first_name CHAR(30), 

last_name CHAR(30) );

? 例 2：不允许负值和未知值

CREATE TABLE people ( 

id INT UNSIGNED NOT NULL, 

first_name CHAR(30), 

last_name CHAR(30) );

3、数值数据类型

? 使用数值数据类型时的注意事项：

数据类型所表示的值的范围
列值所需的空间量
列精度和范围（浮点数和定点数）

? 数值数据类型的类：

整数：整数
浮点数：小数
定点数：精确值数值
BIT：位字段值

4、字符串数据类型

? 表示给定字符集中的一个字母数字字符序列

? 用于存储文本或二进制数据

? 几乎在每种编程语言中都有实现

? 支持字符集和整理

? 属于以下其中一类

文本：真实的非结构化字符串数据类型
整数：结构化字符串类型

5、二进制字符串数据类型

? 字节序列

二进制位按八位分组

? 存储二进制值，例如：

编译的计算机程序和应用程序
图像和声音文件

? 字符二进制数据类型的类：

二进制：固定长度和可变长度的二进制字符串
BLOB：二进制数据的可变长度非结构化集合

6、时间数据类型

7、列属性

列属性的类别：

? 数值：适用于数值数据类型（BIT 除外）

? 字符串：适用于非二进制字符串数据类型

? 常规：适用于所有数据类型

8、如何选择数据类型

? 考虑哪些数据类型和字符集可以最大限度地减少存储和磁盘 I/O。

? 使用固定长度数据类型：

如果存储的所有字符串值的长度相同

? 使用可变长度数据类型：

如果存储的字符串值不同
对于多字节字符集

? 对于频繁使用的字符，使用占用空间较少的多字节字符集。

使用基本多文种平面 (Basic Multilingual Plane, BMP) 之外的其他 Unicode 字符集。

七、MySQL 客户端工具及SQL入门

1、课程大纲：

mysql客户端命令介绍；

mysql获取帮助的方法细讲；

DDL语句之管理数据库；

DDL语句之管理表与案例介绍；

DML语句之管理表中的数据；

SELECT 检索数据；

2、mysql接口程序使用及SQL入门

mysql客户端命令介绍：
? mysql： – 用于数据库连接管理
- 将 用户SQL 语句发送到服务器
? mysqladmin： – 命令行管理工具
? mysqldump： – 备份数据库和表的内容

- 用于管理数据库：
	命令接口自带命令
	DDL:数据定义语言（create）
	DCL：数据控制语言(grant,revoke)
	DML：数据操作语言(update,delete,insert)
mysql 接口程序：
mysql -uroot -poldboy123 -e "show variables like ‘%server_id%‘"
mysql>:
1,接口自带的功能
mysql命令：
1.\h或help 或？
显示接口命令帮助命令。
2.\G
将显示的内容格式输出。
3.\T或者tee
日志记录，需要先：tee /tmp/test.log
所有mysql操作及输出都记录在这个文件里。
4.\c 或者CTRL+c
语句后面带\c，前面的命令不在执行。ctrl+c退出
5.\s 或 status
查看当前数据库的基本状态。
6.\. 或 source
用来执行外部的SQL脚本：二进制日志截取，备份出来的sql脚本
7.\ use
use 进入到某个数据库。
2，服务器端命令（SQL结构化的查询语言,mysql接口程序只负责接收SQL)
show 系列命令。

2、服务器端命令（SQL）

（1）SQL:结构化的查询语言，mysql接口程序只负责接收SQL，传送SQL.

（2）SQL种类：

? DDL：数据库对象定义语言(create)

? DCL：数据库控制语言（grant，revoke）

? DML:数据行操作语言（update,delete,insert)

? DQL:数据查询语言（show，select）

DDL操作：

? 对象：f

? 库：

? 定义什么？

? 1、库名字

? 2、库的基本属性

? 如何定义？

? create database lufei;

? create shema lf;

? show databases;

mysql> help create database
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...
创建属性：
create_specification:
字符集：[DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] 
  排序规则：COLLATE [=] collation_name
mysql> create database llf character set utf-8;
mysql> show create database llf;#查看建库语句
drop database llf;删除数据库

help  后面加命令，帮助不熟悉使用的命令。
修改字符集：
ALTER DATABASE [db_name] CHARACTER SET charset_name collation_name
mysql> alter database lf charset utf8mb4;
                          缩写
mysql> show create database lf;

? 表：

? 表数据

? 表属性（元数据）：表明，列名字，列定义（数据类型，约束，特殊列属性）、表的索引信息。

? 定义什么？

? 定义表的属性？

use lufei;
create table t1(id int,name varchar(20));
mysql> use lufei;
mysql> create table t1(id int ,name varchar(20));
mysql> show tables;
mysql> show create table t1;
mysql> desc t1;
mysql> drop table t1;

修改表的定义：
		修改：
		（1）在表中添加一列
		alter table t1 add age int;
		（2）添加多列
		alter table t1 add bridate datetime, add gender enum(‘M‘,‘F‘);
		（3）在指定列后添加一列
		alter table t1 add stu_id int after id;
		（4）在表中最前添加一列
		alter table t1 add sid int first;
		（5）删除列
		 alter table t1 drop sid;
		（6）修改列名
		alter table t1 change name stu_name varchar(20);
		（7）修改列属性
		alter table t1 modify stu_id varchar(20);
		（8）修改表名
		rename table t1 to student;
		alter table student rename  as stu;

DML语句：数据库操作语言

? insert

update

? delete

DML语句：数据操作语言
	insert 
	use  lufei
	create table t1 (id int ,name varchar(20));
	insert into t1 values(1,‘zhang3‘);
	select * from t1;
	insert into t1 values (2,‘li4‘),(3,‘wang5‘),(4,‘ma6‘);
	insert into t1(name) values (‘xyz‘);
	update
	update  t1  set name=‘zhang33‘ ;   ----会更新表中所有行的name字段，比较危险。
	update  t1  set name=‘zhang55‘ where id=1;   ----update在使用时一般都会有where条件去限制。
	delete
	delete from t1 ;  --删除表中所有行，比较危险。一行一行删除表中数据。
	delete from t1   where  id=2;
	DDL
	truncate table t1;  ---在物理上删除表数据，速度比较快。

DQL语句：（数据库查询语句）

DQL:
	select语句：
SELECT USER,PASSWORD ,HOST  FROM mysql.user;

-- select 基本查询

DESC world.city
SELECT  id ,NAME   FROM  world.city;
SELECT * FROM world.`city`;
-- select 条件查询 where
---- 1、查询中国（CHN）所有的城市信息
SELECT * FROM world.`city` WHERE countrycode=‘CHN‘;
---- 2、查询中国（CHN）安徽省所有的城市信息。
SELECT * FROM world.`city` 
WHERE countrycode=‘CHN‘
AND
district=‘anhui‘;

---- 3、查询世界上人口数量在10w-20w城市信息
SELECT * FROM world.`city` 
WHERE 
population BETWEEN 100000 AND 200000 ;

---- 4、中国或者日本的所有城市信息
where字句中的IN
SELECT * FROM world.city
WHERE countrycode IN (‘CHN‘,‘JPN‘);

---- 5、模糊查询
SELECT * FROM world.city
WHERE countrycode LIKE ‘ch%‘;

select 排序并限制

按照人口数量排序输出中国的城市信息（asc（默认升序），desc（降序））

-- select 排序并限制
---- 按照人口数量排序输出中国的城市信息（ASC\DESC）

SELECT * FROM world.`city` WHERE countrycode=‘CHN‘ ORDER BY population ASC;
SELECT * FROM world.`city` WHERE countrycode=‘CHN‘ ORDER BY population DESC;

---- 按照多列排序人口+省排序
SELECT * FROM world.`city` WHERE countrycode=‘CHN‘ ORDER BY id DESC  ;
按照第5列进行降序排序：
SELECT * FROM city
ORDER BY 5 DESC ;

1-20
SELECT * FROM world.`city` WHERE countrycode=‘CHN‘ 
ORDER BY 5 DESC LIMIT 20;

显示11-20行
SELECT * FROM world.`city` WHERE countrycode=‘CHN‘ 
ORDER BY 5 DESC LIMIT 10,10 ;

SELECT * FROM world.`city` WHERE countrycode=‘CHN‘ 
ORDER BY 5 DESC LIMIT 10 OFFSET 10 ;

表连接查询（使用where）

传统的连接写法（使用where）
---- 中国所有城市信息+使用语言
		
SELECT NAME ,countrycode ,population FROM city 
WHERE countrycode =‘CHN‘	
	
SELECT countrycode ,LANGUAGE FROM countrylanguage;

SELECT ci.NAME ,ci.countrycode ,ci.population,cl.language 
FROM 
city AS ci , countrylanguage AS cl
WHERE ci.countrycode =‘CHN‘ 
AND
ci.CountryCode=cl.CountryCode;

SELECT NAME,ci.countrycode ,cl.language ,ci.population
FROM  city ci , countrylanguage cl
WHERE 
ci.countrycode=‘chn‘ AND
ci.`CountryCode`=cl.countrycode;

SELECT NAME,countrycode ,LANGUAGE ,population
FROM  city NATURAL  JOIN  countrylanguage 
WHERE population > 10000000
ORDER BY population;

SELECT NAME,countrycode ,LANGUAGE ,population
FROM  city JOIN  countrylanguage 
USING(countrycode);

---- 查询青岛这个城市，所在的国家具体叫什么名字
DESC city
DESC country

SELECT NAME,countrycode FROM city WHERE NAME=‘qingdao‘;

SELECT NAME FROM country WHERE CODE=‘CHN‘;
--------------------------------
SELECT ci.name ,ci.countrycode,ci.population ,co.name
FROM city AS ci 
JOIN 
country AS co
ON ci.countrycode=co.code
AND
ci.name=‘qingdao‘;

group by +聚合函数（avg(),max(),min(),sum())

group by +聚合函数（avg()、max()、min()、sum()）


SELECT countrycode ,SUM(population) FROM city
WHERE countrycode = ‘chn‘
GROUP BY countrycode;

union 

用来替换  or 、in()

SELECT * FROM world.city
WHERE countrycode IN (‘CHN‘,‘JPN‘);
改写为：

SELECT * FROM world.city
WHERE countrycode =‘CHN‘
union
SELECT * FROM world.city
WHERE countrycode =‘JPN‘;

字符集

字符集：

charset:字符集
UTF8
UTF8mb4

gbk

collation：排序规则

a-z ,A-Z  大小写敏感

aA-zZ     小写不敏感

show charset;
show collation;

数据库：

服务器端字符集：

控制的是，存到mysql中时，字符集控制

客户端字符集

控制的是用户的输入及显示
系统字符集

控制的是系统相关的显示，和一些依赖于操作系统的应用
alter database oldboy CHARACTER SET utf8 collate utf8_general_ci;
alter table t1 CHARACTER SET latin1;

注意：更改字符集时，一定要保证由小往大改，后者必须是前者的严格超集。生产中别随便改。

数据类型及列属性：

数字类型
字符类型
时间类型
列属性
create table student(id int not null primary key  AUTO_INCREMENT)；
create table student1(id int not null primary key  AUTO_INCREMENT,name varchar(20))charset utf8;
create table teacher(id int not null ,name varchar(20) not null);
create table teacher1(id int not null ,name varchar(20) not null,beizhu varchar(20) not null default "ok");

primary key 主键：非空、唯一
unique：唯一

获取元数据：

information_schema :

数据行之外

元数据（定义数据的数据列属性，列名字等，状态）

充当数据库元数据的*系统信息库：

模式和模式对象
服务器统计信息（状态变量，设置，连接）

采用表格式以实现灵活访问

使用任意select 语句

是“虚拟数据库”

表并非“真实”表（基表），而是“系统视图”
根据当前用户的特权动态填充表

mysql> use information_schema

mysql> show tables;

mysql> desc tables;

mysql> select table_name ,table_schema,engine from world;

显示数据库world中表的列的信息：

mysql> select * from columns where table_schema=‘world‘\G;

mysql> select table_schema,table_name from information_schema.tables where table_schema=‘world‘;
+--------------+-----------------+
| table_schema | table_name      |
+--------------+-----------------+
| world        | city            |
| world        | country         |
| world        | countrylanguage |
+--------------+-----------------+
批量拼接语句：
----
mysql> select concat(‘hellow‘);
+------------------+
| concat(‘hellow‘) |
+------------------+
| hellow           |
+------------------+
1 row in set (0.01 sec)
----
实例：
mysql> select concat("mysqldump -uroot -poldboy123 ",table_schema," ",table_name," >>","/backup/",table_schema,"-",table__name,".bak.sql") from information_schema.tables where table_schema=‘world‘;
+-----------------------------------------------------------------------------------------------------------------------------+
| concat("mysqldump -uroot -poldboy123 ",table_schema," ",table_name," >>","/backup/",table_schema,"-",table_name,".bak.sql") |
+-----------------------------------------------------------------------------------------------------------------------------+
| mysqldump -uroot -poldboy123 world city >>/backup/world-city.bak.sql                                                        |
| mysqldump -uroot -poldboy123 world country >>/backup/world-country.bak.sql                                                  |
| mysqldump -uroot -poldboy123 world countrylanguage >>/backup/world-countrylanguage.bak.sql                                  |
+-----------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
实例2：
SELECT CONCAT(‘CREATE TABLE ‘, TABLE_SCHEMA, ‘.‘,
			 TABLE_NAME, ‘_backup LIKE ‘, TABLE_SCHEMA, ‘.‘,
			 TABLE_NAME, ‘;‘) FROM INFORMATION_SCHEMA.TABLES
			 WHERE TABLE_SCHEMA = ‘world’;

linux命令行使用的命令：
[root@centos6-kvm3 data]# mysqlshow -uroot -poldboy123 world
元数据一般查询语句：
show
show databases
show create database oldboy
show tables
show create table t1

SOHW databases：列出所有数据库
SHOW TABLES：列出默认数据库中的表
SHOW TABLES FROM <database_name>：列出指定数据库中的表
SHOW COLUMNS FROM <table_name>：显示表的列结构
SHOW INDEX FROM <table_name>：显示表中有关索引和索引列的信息
SHOW CHARACTER SET：显示可用的字符集及其默认整理
SHOW COLLATION：显示每个字符集的整理
SHOW STATUS：列出当前数据库状态
SHOW VARIABLES：列出数据库中的参数定义值

八、mysql 基础优化-索引管理

1、课程大纲

索引介绍

索引管理

2、执行计划获取及分析

mysql数据库中索引的类型介绍
BTREE:B+数索引 （主要）
HASH:HASH索引
FULLTEXT:全文索引
RTREE:R树索引
------
索引管理：
索引建立的在表的列上（字段）的。
在where后面的列建立索引才会加快查询速度。
索引分类：
- 主键索引
- 普通索引****
- 唯一索引
添加索引方法
alter table test add index index_name(name);
create index index_name on test(name);
查询表是否有索引信息：
DESC stu；看他的key列值
mysql> explain select * from stu;
mysql> explain select * from stu where stu_name=‘zhangsan‘;
查看到的type不同。
------

索引及执行计划
索引基本管理：

创建和删除：
alter table stu add index idx_name(stu_name);
alter table stu drop index idx_name;
或者

create index inx_name on stu(stu_name);
drop index inx_name on stu;

查询索引设置
desc stu;

主键索引： 唯一、非空
走主键索引的查询效率是最高的，我们尽量每个表有一个主键，并且将来查询的时候计量以主键为条件查询

CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE `test1` (
  `id` int(4) NOT NULL,
  `name` char(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
增加自增主键
alter table test1 change id id int(4) primary key not null auto_increment;

前缀索引：
create index index_name on stu(stu_id(8));

联合索引：
where a女生 and b身高165 and c身材好

index(a,b,c)

特点：前缀生效特性。

a,ab,abc，ac   可以走索引。
b bc c 不走索引。

原则：把最常用来作为条件查询的列放在前面。

走索引：
select  * from people where a=‘nv‘ and b>=165 and tizhong<=120;
select  * from people where a=‘nv‘ and b>=165;
select  * from people where a=‘nv‘;
select  * from people where a=‘nv‘ and tizhong<=120;

alter table stu add index minx(gender,age);
唯一性索引：
create unique index index_name on test(name);

3、explain 调取语句的执行计划

主要是判断语句是否走索引

explain select stu_name,gender,age from stu where gender=‘F‘ and age <20;

mysql> explain select name,gender,age from test where gender=‘F‘ and age <20;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | inx_test      | inx_test | 7       | NULL |    1 | Using index condition |

type : 表示MySQL在表中找到所需行的方式，又称“访问类型”，

常见类型如下:

ALL,index, range, ref, eq_ref, const, system, NULL

从左到右，性能从最差到最好

ALL：
Full Table Scan， MySQL将遍历全表以找到匹配的行

如果显示ALL，说明：
查询没有走索引：
	1、语句本身的问题
	2、索引的问题，没建立索引

index：Full Index Scan，index与ALL区别为index类型只遍历索引树
例子：
explain select count(*) from stu ;

range:索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。
显	而易见的索引范围扫描是带有between或者where子句里带有<,>查询。
where 条件中有范围查询或模糊查询时
>  < >= <=   between  and   in ()   or
like ‘xx%‘

当mysql使用索引去查找一系列值时，例如IN()和OR列表，也会显示range（范围扫描）,当然性能上面是有差异的。

ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行

where stu_name=‘xiaoming‘

explain select *  from stu  where stu_name=‘aa‘;

eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，
就是多表连接中使用primary key或者 unique key作为关联条件	

join条件使用的是primary key或者 unique key

const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。
如将主键置于where列表中，MySQL就能将该查询转换为一个常量

 explain  select * from city where id=1;

NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，
	  例如从一个索引列里选取最小值可以通过单独索引查找完成。

mysql> explain select name,population from city;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | city  | ALL  | NULL          | NULL | NULL    | NULL | 4188 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
	  
Extra:

Using temporary
Using filesort
Using join buffer

排序   order by  ,group by ,distinct,排序条件上没有索引
explain select * from city where countrycode=‘CHN‘ order by population;
在join 的条件列上没有建立索引

4、数据库索引的设计原则：

一、数据库索引的设计原则：

	为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。
那么索引设计原则又是怎样的?(尽量使用主键索引和唯一性索引。)

1．选择唯一性索引
	唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。

主键索引和唯一键索引，在查询中使用是效率最高的。

2．为经常需要排序、分组和联合操作的字段建立索引
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。
如果为其建立索引，可以有效地避免排序操作。

3．为常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，
为这样的字段建立索引，可以提高整个表的查询速度。

select count(DISTINCT population ) from city;
select count(*) from city;

4．尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索
会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

------------------------以上的是重点关注的，以下是能保证则保证的--------------------

5．限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

6．尽量使用数据量少的索引
如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR（100）类型的字段进行全文
检索需要的时间肯定要比对CHAR（10）类型的字段需要的时间要多。

7．删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

5、索引的开发规范

不走索引的情况：
重点关注：
1) 没有查询条件，或者查询条件没有建立索引 

select * from tab;   全表扫描。
select  * from tab where 1=1;

在业务数据库中，特别是数据量比较大的表。
是没有全表扫描这种需求。

1、对用户查看是非常痛苦的。
2、对服务器来讲毁灭性的。

（1）select * from tab;

SQL改写成以下语句：
selec  * from tab  order by  price  limit 10      需要在price列上建立索引

（2）
select  * from  tab where name=‘zhangsan‘          name列没有索引

改：
	1、换成有索引的列作为查询条件
	2、将name列建立索引
	

2) 查询结果集是原表中的大部分数据，应该是30％以上。 

查询的结果集，超过了总数行数30%，优化器觉得就没有必要走索引了。

假如：tab表 id，name    id:1-100w  ，id列有索引

select * from tab  where id>500000;


如果业务允许，可以使用limit控制。

怎么改写 ？
结合业务判断，有没有更好的方式。如果没有更好的改写方案
尽量不要在mysql存放这个数据了。放到redis里面。


3) 索引本身失效，统计数据不真实 

索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。


4) 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等) 
例子： 
错误的例子：select * from test where id-1=9; 
正确的例子：select * from test where id=10;


5)隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 
由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给数据库,
这样会导致索引失效. 错误的例子：select * from test where tu_mdn=13333333333; 
正确的例子：select * from test where tu_mdn=‘13333333333‘; 
------------------------
mysql> alter table tab add index inx_tel(telnum);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> desc tab;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| telnum | varchar(20) | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)


mysql> select * from tab where telnum=‘1333333‘;
+------+------+---------+
| id   | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)

mysql> select * from tab where telnum=1333333;
+------+------+---------+
| id   | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)

mysql> explain  select * from tab where telnum=‘1333333‘;
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | tab   | ref  | inx_tel       | inx_tel | 63      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

mysql> explain  select * from tab where telnum=1333333;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab   | ALL  | inx_tel       | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain  select * from tab where telnum=1555555;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab   | ALL  | inx_tel       | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain  select * from tab where telnum=‘1555555‘;
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | tab   | ref  | inx_tel       | inx_tel | 63      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)


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

6) 
<>  ，not in 不走索引

EXPLAIN SELECT * FROM teltab WHERE telnum   <> ‘110‘;
EXPLAIN  SELECT * FROM teltab WHERE telnum  NOT IN (‘110‘,‘119‘);
------------
mysql> select * from tab where telnum <> ‘1555555‘;
+------+------+---------+
| id   | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)

mysql> explain select * from tab where telnum <> ‘1555555‘;
-----
单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit
or或in  尽量改成union

EXPLAIN  SELECT * FROM teltab WHERE telnum   IN (‘110‘,‘119‘);
改写成：

EXPLAIN SELECT * FROM teltab WHERE telnum=‘110‘
UNION ALL
SELECT * FROM teltab WHERE telnum=‘119‘

-----------------------------------
7)   like "%_" 百分号在最前面不走

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE ‘31%‘   走range索引扫描

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE ‘%110‘  不走索引

%linux%类的搜索需求，可以使用elasticsearch

%linux培训%

8) 单独引用复合索引里非第一位置的索引列. 
列子：
复合索引：

DROP TABLE t1
CREATE TABLE t1 (id INT,NAME VARCHAR(20),age INT ,sex ENUM(‘m‘,‘f‘),money INT);

ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex);
DESC t1
SHOW INDEX FROM t1
走索引的情况测试：
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30  AND sex=‘m‘;
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30  ;
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30  AND sex=‘m‘;    ----->部分走索引
不走索引的：
EXPLAIN SELECT  NAME,age,sex,money FROM t1 WHERE  age=20
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE   age=30  AND sex=‘m‘;
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE   sex=‘m‘;

九、mysql 存储引擎

1、课程大纲

存储引擎的介绍

mysql中的存储引擎分类

mysql存储引擎设置

mysql，innodb引擎存储结构

mysql中的事物

mysql中的锁

2、mysql存储引擎介绍

mysql存储引擎介绍
文件系统：
操作系统组织和存取数据的一种机制。
文件系统是一种软件。
类型：ext2,3,4,xfs数据：
不管使用什么文件系统，数据内容不会变化
不同的是，存储空间，大小，速度
mysql引擎：
可以理解为，mysql的“文件系统”，只不过功能更加强大。
mysql引擎功能：
除了可以提供基本的存取功能，还有更多功能事物功能，锁定，备份和恢复，优化以及特殊功能。

3、mysql存储引擎种类

MySQL 提供以下存储引擎：
InnoDB
MyISAM
MEMORY
ARCHIVE	
FEDERATED
EXAMPLE
BLACKHOLE
MERGE
NDBCLUSTER
CSV
还可以使用第三方存储引擎（TokuDB）。

4、数据库的存储引擎

存储引擎查询

存储引擎查询：
show engines;
select @@default_storage_engine;
show create table city;
show table status like ‘city‘\G
use information_schema 
select table_schema,table_name,engine from information_schema.tables where table_schema=‘world‘;
select table_schema,table_name,engine from information_schema.tables where table_schema=‘mysql‘;
select table_schema,table_name,engine from information_schema.tables where engine=‘csv‘;

存储引擎的配置：

查看存储引擎：
show engines;
select @@default_storage_engine;
mysql> show variables like ‘%engine%‘;
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| default_storage_engine     | InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine             | InnoDB |
+----------------------------+--------+
将存储引擎设置在配置文件中，重启mysql生效。

配置存储引擎：

1、在启动配置文件中设置服务器存储引擎：
	[mysqld]
	default-storage-engine=<Storage Engine>
2、使用 SET 命令为当前客户机会话设置：
	SET @@storage_engine=<Storage Engine>;
3、在 CREATE TABLE 语句指定：
	CREATE TABLE t (i INT) ENGINE = <Storage Engine>;

mysql> show variables like ‘%engine%‘;
mysql> use world
mysql> create table test3 (id int ) engine=innodb;
mysql> show create table test3;

5、innodb体系结构：

表空间：

共享表空间：主要存放系统元数据

独立表空间：主要存放用户数据

逻辑概念，数据存储的概念

表空间数据文件：idb文件，在/app/mysql/data/lufei

共享表空间的设置：

默认的配置：
innodb_data_file_path    /application/mysql/data/  ibdata1:12M:autoextend 

共享表空间的设置：
共享表空间设置：

innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

innodb_data_file_path=ibdata1:12M;ibdata2:50M:autoextend    ----错误的配置XXX
在 /application/mysql/data/查看idbdata1实际的大小，然后在增加idbdata2的大小，进行扩展。此命令配置在mysql的配置文件中。
innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend    -----正确的配置

独立表空间：

	除了系统表空间之外，InnoDB 还在数据库目录中创建另外的表空间，用于每个 InnoDB 表的 .ibd 文件。
	InnoDB 创建的每个新表在数据库目录中设置一个 .ibd 文件来搭配表的 .frm 文件。
	可以使用 innodb_file_per_table 选项控制此设置
	更改该设置仅会更改已创建的新表的默认值。
	
注：在mysql5.6开始，默认的配置为：
	| innodb_file_per_table | ON    |
	
独立表空间删除表空间命令：

alter table testtab discard tablespace;

该命令执行后/app/mysql/data/lufei中的testtab的ibd文件就被删除。

6、Innodb引擎-事务

组数据操作执行步骤，这些步骤被视为一个工作单元:
	用于对多个语句进行分组
	可以在多个客户机并发访问同一个表中的数据时使用
所有步骤都成功或都失败:
	如果所有步骤正常，则执行
	如果步骤出现错误或不完整，则取消

事务ACID:
Atomic（原子性）
	所有语句作为一个单元全部成功执行或全部取消。
Consistent（一致性）
	如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。 
Isolated（隔离性）
	事务之间不相互影响。
Durable（持久性）
	事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。

7、mysql事务控制语句

事务中的标准语句：DML语句
insert,update,delete三类事物语句。
如果遇到以上三个命令，都是事物型的操作，都会默认在前面添加begin命令。

START TRANSACTION（或 BEGIN）：显式开始一个新事务
1
2
3
COMMIT：永久记录当前事务所做的更改
BEGIN
1    打标记a
2    打标记b（savepoint时候）
3
ROLLBACK：取消当前事务所做的更改（回滚到所打的标记）

SAVEPOINT：分配事务过程中的一个位置，以供将来引用
ROLLBACK TO SAVEPOINT：取消在 savepoint 之后执行的更改
RELEASE SAVEPOINT：删除 savepoint 标识符
SET AUTOCOMMIT：为当前连接禁用或启用默认 autocommit 模式

保存：commit是自动保存的：

mysql> show variables like "%auto%";
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| auto_increment_increment    | 1     |
| auto_increment_offset       | 1     |
| autocommit                  | ON    |
| automatic_sp_privileges     | ON    |
| innodb_autoextend_increment | 64    |
| innodb_autoinc_lock_mode    | 1     |
| innodb_stats_auto_recalc    | ON    |
| sql_auto_is_null            | OFF   |
+-----------------------------+-------+
8 rows in set (0.00 sec)
需要将自动保存的commit改成关闭：（只修改当前会话）
mysql> set autocommit=0;
查看全局commit配置：
mysql> show global variables like "%auto%";
	在有些业务繁忙企业场景下，这种配置可能会对性能产生很大影响，但对于安全性上有很大提高。
将来，我们需要去权衡我们的业务需求去调整是否自动提交。
我们可以通过以下命令进行修改关闭（0是关闭，1是开启）：
	SET GLOBAL AUTOCOMMIT=0;  -?所有新建会话	
	SET SESSION AUTOCOMMIT=0; -?当前会话
	SELECT @@AUTOCOMMIT;      -?查看设置结果
我们也可以修改配置文件让其永久生效：
	vi /etc/my.cnf	
	[mysqld]
	AUTOCOMMIT=0
		
隐式提交语句（commit）：
用于隐式提交的 SQL 语句：
	START TRANSACTION
	SET AUTOCOMMIT = 1 
导致提交的非事务语句：
	DDL语句：	（ALTER、CREATE 和 DROP）
	DCL语句：	（GRANT、REVOKE 和 SET PASSWORD）
	锁定语句：（LOCK TABLES 和 UNLOCK TABLES）
导致隐式提交的语句示例：
	TRUNCATE TABLE
	LOAD DATA INFILE
	SELECT FOR UPDATE
如：
BEGIN
1
2
3

BGEIN(开始之前就默认commit)
1
2
3

8、事务的日志

Redo是什么？	
	redo,顾名思义“重做日志”，是事务日志的一种。
作用是什么？
	在事务ACID过程中，实现的是“D”持久化的作用。
undo是什么？	
undo,顾名思义“回滚日志”，是事务日志的一种。
作用是什么？
在事务ACID过程中，实现的是“A、C”原子性和一致性的作用。
什么是“锁”？
“锁”顾名思义就是锁定的意思。
“锁”的作用是什么？
在事务ACID过程中，“锁”和“隔离级别”一起来实现“I”隔离性的作用。
锁的粒度：
1、MyIasm：低并发锁——表级锁
2、Innodb：高并发锁——行级锁
四种隔离级别：
	READ UNCOMMITTED		
允许事务查看其他事务所进行的未提交更改
	READ COMMITTED
		允许事务查看其他事务所进行的已提交更改
	REPEATABLE READ******
		确保每个事务的 SELECT 输出一致
		InnoDB 的默认级别
	SERIALIZABLE
		将一个事务的结果与其他事务完全隔离

十、myql日志管理

课程大纲

1、MySQL日志类型简介

2、MySQL错误日志配置及查看

3、MySQL binlog介绍及管理实战

4、MySQL 慢查询日志设置及管理实战

1、日志的类型简介

mysql> show variables like ‘%log_error%‘；在配置文件中指定错误日志位置。

mysql> show variables like ‘%gen%‘; 一般日志查询

二进制日志，记录修改记录。

日志文件	选项	文件名	程序
表名称
错误	--log-error	host_name.err	N/A
常规	--general_log	host_name.log，general_log	N/A

慢速查询	--slow_query_log --long_query_time	host_name-slow. log，slow_log	mysqldumpslow

二进制	--log-bin --expire-logs-days	host_name-bin.000001	mysqlbinlog
审计	--audit_log--audit_log_file...	audit.log	N/A

2、MySQL错误日志配置及查看

错误日志：
配置方法:
[mysqld]
log-error=/data/mysql/mysql.log
查看配置方式：
mysql> show variables like ‘%log%error%‘;
作用：
记录mysql数据库的一般状态信息及报错信息，是我们对于数据库常规报错处理的常用日志。
一般查询日志：
配置方法:
[mysqld]
general_log=on
general_log_file=/data/mysql/server2.log
查看配置方式：
show variables like ‘%gen%‘;
作用：
记录mysql所有执行成功的SQL语句信息，可以做审计用，但是我们很少开启。

3、MySQL binlog介绍及管理实战

1、二进制日志都记录了什么？
	已提交的数据记录，以event的形式记录到二进制文件中
2、二进制记录格式有哪些？
	row：行模式，即数据行的变化过程，上图中Age=19修改成	Age=20的过程事件。(一般都是应用这个模式)
	statement：语句模式，上图中将update语句进行记录。
	mixed：以上两者的混合模式。
3、三总模式有什么优缺点？
4、binlog的作用
	备份恢复、复制

二进制日志管理：

1、开启二进制日志
set sql_log_bin=0    ?在会话级别修改为临时关闭
vi /etc/my.cnf
log-bin=/data/mysql/mysql-bin  ?在全局打开binlog

2、设置二进制日志记录格式（建议是ROW）：
配置文件中修改：
binlog-format=ROW
命令行修改
mysql> SET GLOBAL binlog_format = ‘STATEMENT‘;
mysql> SET GLOBAL binlog_format = ‘ROW‘;
mysql> SET GLOBAL binlog_format = ‘MIXED‘;    
3、查看binlog设置
show variables like ‘%binlog%‘;

查询二进制日志方法：
mysql> show variables like ‘binlog‘;
mysql> show binary logs;
mysql> show master status; 最近一次的，当前正在使用的二进制日志
mysql> show binlog events in ‘mysql-bin.000014‘;
可以通过linux命令行中进程查询：
[root@centos6-kvm3 mysql]# pwd
/application/mysql/data/mysql
[root@centos6-kvm3 data]# mysqlbinlog mysql-bin.000014 #真正二进制日志内容
详细的查询：日志内容分析
[root@centos6-kvm3 data]# mysqlbinlog --help | more
[root@centos6-kvm3 data]# mysqlbinlog --base64-output=decode-rows -v mysql-bin.000014
根据需求截取二进制日志：
[root@centos6-kvm3 data]# mysqlbinlog --start-position=1077  --stop-position=1119 mysql-bin.000014
----
二进制日志

-----

mkdir /data/binlog -p
chown -R mysql.mysql /data/binlog

vim /etc/my.cnf

log-bin=/data/binlog
binlog_format=row
sync_binlog=1

show binary logs;
show binlog events in ‘my-bin.000002‘
show master status;
截取获取二进制日志，进行恢复：
mysqlbinlog  --base64-output=decode-rows -v my-bin.000002
mysqlbinlog --start-position=120 --stop-position=721   my-bin.000002
mysqbinlog --start-position=340 --stop-position=721 my-bin.000002 >/tmp/binlog.sql
source /tmp/binlog.sql
二进制日志管理：
刷新二进制日志
	flush logs
截取二进制日志
	mysqlbinlog --start-position=  --stop-position= >a.sql 
问题：
1、什么是事件？
2、什么是position	?	

1、通过截取binlog恢复损坏数据
mysqbinlog --start-position=340 --stop-position=721 my-bin.000002 >/tmp/binlog.sql
2、二进制日志翻转实现闪回数据（扩展）
source /tmp/binlog.sql

删除二进制：
默认情况下，不会删除旧的日志文件。
根据存在时间删除日志：
SET GLOBAL expire_logs_days = 7;
…或者…
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
根据文件名删除日志：
PURGE BINARY LOGS TO ‘mysql-bin.000010‘;
reset master;#重启启用一个二进制文件。

4、慢日志管理：

功能：
slow-log，记录所有条件内的慢的SQL语句
优化的一种工具日志。帮我们定位问题。
是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件
通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的。

慢日志设置：
long_query_time? ? ：? 设定慢查询的阀值，超出次设定值的SQL即被记录到慢查询日志，缺省值为10s
slow_query_log? ? ? ：? 指定是否开启慢查询日志
slow_query_log_file ：? 指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log
min_examined_row_limit：查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
log_queries_not_using_indexes: 不使用索引的慢查询日志是否记录到索引

配置例子，添加在配置文件中:
slow_query_log=1?
slow_query_log_file=/data/slow/slow.log
long_query_time=0.5
log_queries_not_using_indexes
	
处理慢日志：
mysqldumpslow命令

mysqldumpslow -s c -t 10 /data/slow/slow.log
mysqldumpslow -s at -t 10 /data/slow/slow.log

这会输出记录次数最多的10条SQL语句，
其中：
-s
是表示按照何种方式排序
c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序。
ac、at、al、ar，表示相应的倒叙。
-t
是top n的意思，即为返回前面多少条的数据；

----------自己扩展工具
pt-query-diagest    percona-toolkit
mysqlsla

十一、mysql 备份恢复

课程大纲

1、备份的原因

2、备份的方式

3、备份的工具

4、mysqldump备份工具的详解

5、mysqldump+mysqlbinlog实现增量备份

6、企业级备份策略及恢复案例

7、xtrabackup备份恢复实战

运维工作的核心简单概括就两件事：

第一个是保护公司的数据。

第二个是让网站能7*24小时提供服务(用户体验)。

1、备份的类型

冷备份：关闭数据、停止业务

温备份：枷锁备份

热备份：在线备份，不会影响业务。

2、备份方式

逻辑备份：

基于sql语句的备份：

①mysqldump--》建库，建表，数据插入

②基于二进制日志：数据库的所有变化类的操作。

③基于复制的备份：将二进制日志实时传递到另一台机器并且恢复。

物理备份：

①xtrabackup 进行物理备份

②拷贝数据文件（冷备）

3、备份工具：

①mysqldump

mysql原生自带很好用的逻辑备份工具

②mysqlbinlog（根据始末position位置进行截取备份）

实现binlog备份的原生态命令

③xtrabackup

percona公司开发的性能很高的物理备份工具

mysqldump备份工具优缺点：

有点：逻辑备份工具，都是sql语句，都是文本格式，便于查看和编辑，便于压缩。

缺点：备份较慢，效率低。

mysqldump参数介绍：

-u -p -S -h -P

-A, 全库备份

例子：

[root@centos6-kvm3 ~]# mysqldump -uroot -poldboy123 -A >/backup/mysqlfull.sql

单库备份：

-B,增加建库（create）及’use库‘的语句，在将来恢复时，不需要手工建库和use库。

-B 选项还可以实现，同时备份多个库，备份到同一个文件中。（空格隔开）

[root@centos6-kvm3 ~]# mysqldump -uroot -poldboy123 -B lufei >/backup/mysqllufei.sql

[root@centos6-kvm3 ~]# mysqldump -uroot -poldboy123 lufei >/backup/mysqllufei1.sql

不加-B，恢复时候需要先创建库，use库下再进行恢复。

不加-B ,去备份他库下的一个单表的意思。

生成环境下，也要加的额外参数：

-R:备份存储过程和函数数据。

--triggers :备份触发器数据。

-F，--flush-logs ：刷新binlog日志，为了方便将来二进制日志截取时的起点。

mysqldump -uroot -poldboy -A -F >/backup/mysqlfull.sql

--master-data={1|2} :告诉你备份时刻的binlog位置，一般我们选择2，以注释的方式记录二进制日志的位置。

mysql> show master status;

锁表：适合所有引擎（myisam,innodb）

-x,--lock-all-tables

-l,--lock-tables

--single-transaction 对innodb引擎进行热备

[root@centos6-kvm3 ~]# mysqldump -uroot -poldboy123 -A --master-data=2 --single-transaction >/backup/mysqlfull.sql

通过快照的方式实现热备。

压缩备份：

[root@centos6-kvm3 ~]# mysqldump -uroot -poldboy123 -A -R --triggers --master-data=2 --single-transaction | gzip >/backup/mysqlfull_$(date +%F).sql

4、mysqldump+binlog企业恢复实战

使用source 命令进行恢复
set sql_log_bin=0;（临时关闭二进制日志，防止恢复操作记录到二进制日志中）
source /opt/xxx.sql;
企业实例：
背景环境：
正在运行的网站系统，mysql数据库，数据量25G，日业务增量10-15M。
备份方式：
每天23:00点，计划任务调用mysqldump执行全备脚本。
故障时间点：
上午10点，误删除了一个表。
如何恢复？
思路：
①断开业务，防止对数据库二次伤害，挂出维护页面。
②搭建备用库，恢复全备。
③截取昨天晚上23:00之后到上午10点误删除操作之前的二进制日志。
④恢复到备用库，验证数据可用性和完整性。
⑤两种恢复前端应用。
	5.1备用库导出误删除的表，导入到生产库，开启业务。
	5.2直接将应用切割刀备用库，替代生产库，开启业务。
模拟故障并恢复：
1、原始数据：
mysql> create database oldboy;
mysql> use oldboy
mysql> create table t1 (id int,name varchar(20));
mysql> insert into t1 values (1,‘zhang3‘);
mysql> insert into t1 values (2,‘li4‘);
mysql> insert into t1 values (3,‘wang5‘);
mysql> commit;
2、模拟前一天晚上23:00全备
mysqldump  -A  -R --triggers --master-data=2 --single-transaction |gzip >/backup/all_$(date +%F).sql.gz

3、模拟白天（23:00-10:00）业务对数据的修改
mysql> insert into t1 values (4,‘zhang33‘);
mysql> insert into t1 values (5,‘li44‘);
mysql> insert into t1 values (6，‘wang54‘);
mysql> commit;
4、模拟故障
drop table t1;
5、恢复
（1）准备全备，并获取到备份文件中的binlog的截取起点
gunzip all_2018-04-04.sql.gz
	-- CHANGE MASTER TO MASTER_LOG_FILE=‘my-bin.000004‘, MASTER_LOG_POS=731;
（2）截取二进制日志
mysqlbinlog --start-position=731  --stop-position=1126 /data/binlog/my-bin.000004 >/backup/binlog.sql
-----
 show binlog events in ‘my-bin.000004‘;  ----》drop之前的position为1126 
-----
(3)恢复全备+binlog
set sql_log_Bin=0;
source /backup/all_2018-04-04.sql;
source /backup/binlog.sql

5、xtrabackup 介绍

Xtrabackup物理备份工具
percona公司的备份工具，性能比较高。物理备份工具。
特点：
物理备份工具，在同级数据量基础上，都要比逻辑备份性能要好的多。
特别是在数据量比较大的时候，体现的更加明显。
备份方式：
	1、拷贝数据文件
	2、拷贝数据页
备份原理（innodb）：
	1、对于innodb表，可以实现热备
		（1）在数据还有修改操作的时刻，直接将数据文件中的数据页备份
		此时，备份走的数据对于当前mysql来讲是不一致。
		（2） 将备份过程中的redo和undo一并备走。
		（3）为了恢复的时候，只要保证备份出来的数据页LSN能和redo LSN匹配，
			将来恢复的就是一致的数据。redo应用和undo的应用。
	2、对与myisam表，实现自动锁表拷贝文件。

Xtrabackup软件安装：

1、安装
wget -O /etc/yum.repos.d/epel.repo  http://mirrors.aliyun.com/repo/epel-6.repo
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
yum -y install percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

2、全备备份并恢复
mkdir /server/backup  -p

--user=
--password=
--socket=
--no-timestamp 

全备例子：
innobackupex     /server/backup/

innobackupex     --no-timestamp  /server/backup/full

全备恢复例子：

1、恢复数据前的准备(合并xtabackup_log_file和备份的物理文件)
innobackupex --apply-log --use-memory=32M /server/backup/full/


2、模拟故障
停库:
pkill mysqld
破坏数据：
cd /application/mysql/data
\rm -rf *

3、恢复
cp -a /server/backup/full/*  /application/mysql/data

或者

innobackupex --copy-back  /server/backup/full/   

注意：恢复时，要确认数据路径是空的，并且数据库是停掉的

chown -R mysql.mysql /application/mysql/data

启动：
/etc/init.d/mysqld start

mysql -e "select * from oldboy.t1"

6、xtrabackup 实现增量备份及故障恢复

xtrabackup 实现增量备份及故障恢复

周日全备，周一到周六做增量

1、周日全备：
mkdir /backup/full
innobackupex --user=root --password=123 --no-timestamp /backup/full/
2、模拟数据变化（周一数据变化）
。 
。
。
3、第一增量（周一晚上增量）：
innobackupex --user=root --password=123 --incremental --no-timestamp --incremental-basedir=/backup/full/ /backup/inc1
4、模拟数据变化（周二数据变化）
。
。
。
5、第二次增量（周二晚上增量）：
innobackupex --user=root --password=123 --incremental --no-timestamp --incremental-basedir=/backup/inc1 /backup/inc2
6、模拟数据损坏

n多的操作。。。。。
周三上午10:00时刻,删除t1表


7、恢复数据：
innobackupex --apply-log  --redo-only /backup/full
innobackupex --apply-log  --redo-only --incremental-dir=/backup/inc1 /backup/full
innobackupex --apply-log  --incremental-dir=/backup/inc2 /backup/full
innobackupex --apply-log  /backup/full

7、xtrabackup生产恢复案例：

背景：
1、xtrabackup备份策略每周日，full全备
2、xtrabackup周一到周六，inc1-inic6
3、总数据量200G
4、周三上午10点误删除表t1，数据量1G左右
5、周二晚上inc2备份完成之后到周三上午10点又做了很多操作

如何将数据库恢复到t1表误删除之前状态？

思路：
	1、停业务，挂维护页
	2、找备用库
	3、合并full+inc1+inc2
	4、截取周二晚上inc2备份后到周三上午10点，t1表删除之前的binlog日志
	5、将合并后的full+截取的binlog恢复到备用库
	（ 根据备份日志最后的position，再根据show binlog events in ‘my-bin.000004‘;获取最后的position）
	6、验证数据可用性和完整性
	7、使用备用库替代生产库使用或者将t1表导出并导入回生产库
	8、业务恢复

-----------------
-----------------
思考：以上恢复策略是否可以优化？
为了恢复1G表，需要将整个全备恢复，有必要吗？有什么好的解决办法？
单独恢复一个表：

drop table t1;
create table t1 (id int,name varchar(20));
alter table t1 discard tablespace;

cd /application/mysql/data/oldboy
cp /backup/full/oldboy/t1.ibd  ./

chown  -R mysql.mysql *
alter table t1 import tablespace;

十二、mysql主从复制

1、主从复制原理

1.主从复制的前提：

1.1两台mysql实例（多台物理机，或者多实例）

1.2主库要开启二进制日志

1.3主库要提供复制相关用户，replication slave,一个比较特殊的权限。

grant replication slave on * . * to repl@‘10.0.0.%‘ identified by ‘123‘;?

1.4从库需要将和主库相差的数据，进行追加

?一般情况下可以人为备份主库数据，恢复到从库上。

1.5应该从恢复之后的时间点，开始自动从主库获取二进制日志开始应用

?需要人为告诉从库，从哪里开始自动开始复制二进制日志(file+position)，另外还需要告诉从库user,password,ip,port

2.复制中的线程及文件

2.1主库

dump（IO） thread:在复制过程中，主库发送二进制日志的线程。

2.2从库

IO thread:向主库请求二进制日志，并且接受二进制日志的线程。

SQL thread:执行请求过来的二进制的线程

2.3在主库的文件

binlog文件，主库的二进制日志

2.4从库文件

relaylog：中继日志，存储请求过来的二进制日志。

master.info:

? 1.从库连接主库的重要参数（user，password，ip，port）

? 2.上次获取过的主库二进制日志的位置

relay-log.info

? 存储从库SQL线程已经执行过的relaylog日志位置。

3.主从复制的工作原理

3.1从库，IO线程，读取master.info中的信息，获取到连接参数（user，password，ip，port），和上次过的主库的binlog的位置（mysqlbin-0000,position）。

3.2IO线程使用连接到主库，拿着上次从主库获取到的binlog的位置，问主库有没有比这个更新的二进制日志。

3.3主库查询二进制日志，并对比从库发送过来的位置信息，如果有新的二进制日子，就通过dump thread发送给我从库。

3.4从库通过IO线程，接受主库发来的二进制日志，存储到TCP/IP缓存中，并且返回ACK确认给主库，这时主库认为复制完成了，可以继续其他工作了。

3.5从库更新master.info，二进制日志的为新的位置信息。

3.6从库IO线程会将TCP/IP缓存中的日志，存储到relay-log中继日志文件中。

3.7从库SQL线程，读取relay-log.info，获取到上次执行到的relay-log日志位置，以这个位置为起点，往后继续执行中继日志。

3.8SQL线程执行完成所有relay之后，会更新relay-log.info信息为新位置信息。

到此位置，一次完成的复制过程完成。

4、搭建主从复制

1、准备环境
思路：
1、两个以上节点（多实例）
3307：master
3308：slave1
3309：slave2
2、主库binlog开启，从库开启relay-log（默认在数据目录下生成）
vim /data/3307/my.cnf
log-bin=/data/3307/mysql-bin
binlog_format=row

3、server-id不同
[root@db02 data]# cat /data/3307/my.cnf |grep server-id
server-id=3307
[root@db02 data]# cat /data/3308/my.cnf |grep server-id
server-id=3308
[root@db02 data]# cat /data/3309/my.cnf |grep server-id
server-id=3309
4、关闭数据库的自动域名解析
每个节点都加入以下配置：
skip-name-resolve
5、启动多实例
mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/my.cnf &

6、主库创建复制账户
连接到主库：
mysql -S /data/3307/mysql.sock
grant replication slave on *.* to repl@‘10.0.0.%‘ identified by ‘123‘;

7、从库数据的追加
	（1）不需要追加的情况
	 主和从同时搭建的新环境，就不需要备份主库数据，恢复到从库了，直接从第一个binlog（mysql-bin.000001）的开头位置（120）
	（2）如果主库已经工作了很长时间了，我们一般需要备份主库数据，恢复到从库，然后从库从备份的时间点起自动进行复制
重点针对第二种情况进行演示：
备份主库：
mysqldump -S /data/3307/mysql.sock -A -R  --triggers --master-data=2 --single-transaction >/tmp/full.sql
sed -n ‘22p‘ /tmp/full.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000003‘, MASTER_LOG_POS=325;
恢复到从库：
mysql -S /data/3308/mysql.sock 
mysql> set sql_log_bin=0;
mysql> source /tmp/full.sql

8、从库开启主库：
mysql -S /data/3308/mysql.sock
help change master to

CHANGE MASTER TO
  MASTER_HOST=‘10.0.0.203‘,
  MASTER_USER=‘repl‘,
  MASTER_PASSWORD=‘123‘,
  MASTER_PORT=3307,
  MASTER_LOG_FILE=‘mysql-bin.000003‘,
  MASTER_LOG_POS=325;
开启主从（开启IO和SQL线程）：
start slave;
9、查看主从状态：
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
10、主从重要状态信息介绍

show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error: 


IO线程故障：
    1、主库连接不上
       user、password、port、ip 错误
	   解决方案：
		stop  slave;	
		reset slave all;  
		change master to	
		start slave;
       防火墙
       网络不通
       skip-name-resolve
	   stop  slave;
	   start slave;
	   
    2、主库二进制日志丢失或损坏
		解决方案：
		stop  slave;	
		reset slave all;  
		重新备份恢复
		change master to	
		start slave;

5、SQL线程故障

SQL线程故障：
   执行relaylog日志新事件
    1、删除、修改对象的操作时，没有这个对象
    2、创建对象时，对象已存在
    3、主键冲突
从库做写入操作，会导致以上问题出现
处理方法：
stop slave; 
set global sql_slave_skip_counter = 1; 
start slave;
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
但是，以上操作有时是有风险的，最安全的做法就是重新构建主从。
怎么预防以上问题？
从库加入配置文件
set global read_only=1;
vim /etc/my.cnf
read_only=1           ---->只能控制普通用户

6、主从异常--主从延时过长

show slave status \G
Seconds_Behind_Master:0

默认的主从复制机制是异步的一个过程。

主库原因:
1、主库做修改操作之后，才会记录二进制日志。
sync_binlog=0/1

If the value of this variable is greater than 0, 
the MySQL server synchronizes its binary log to disk (using fdatasync()) 
after sync_binlog commit groups are written to the binary log. 
The default value of sync_binlog is 0, which does no synchronizing to disk—in this case,
the server relies on the operating system to flush the binary log‘s contents from time to time as for any other file. 
A value of 1 is the safest choice because in the event of a crash you lose at most one commit group from the binary log. 
However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast)
---------------------
    1：表示：每次事务commit，刷新binlog到磁盘
    0：系统决定binlog什时候刷新到磁盘
2、主库的压力特别大（大事务、多事务）
3、从库数量多，导致dump线程繁忙
-------------------
从库原因：
1、relay-log写入慢
2、SQL线程慢（主从硬件差异比较大）
-----------------------------
尽可能的避免主从延时
1、sync_binlog=1
2、大事务拆成小事务，多事务进行分离
3、使用多级主从，分库分表架构
4、将binlog放到ssd或者flash上，高性能存储
5、将relay放到ssd或者flash上
6、尽量选择和主库一致硬件和配置

7、主从复制高级功能--半同步复制

出发点：保证主从数据一致性的问题，安全的考虑

5.5 出现的概念，但是不建议使用，性能太差

5.6以后出现group commit 组提交功能，来提升开启版同步复制的性能

5.7 增强半同步复制的新特性：after sync;

------
加载插件

主:
INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so‘;

从:
INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so‘;
查看是否加载成功:
show plugins;

启动:
主:
SET GLOBAL rpl_semi_sync_master_enabled = 1;

从:
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

重启从库上的IO线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
查看是否在运行
主:
show status like ‘Rpl_semi_sync_master_status‘;
从:
show status like ‘Rpl_semi_sync_slave_status‘;
-----
补充：
rpl_semi_sync_master_timeout       | 10000
默认情况先,到达10秒钟还没有ack，主从关系自动切换为普通复制
如果是1主多从的半同步复制，只要有一台落地relaylog，返回ack，这次半同步就完成了。

8、主从复制高级特性--延时从库

会专门找一个节点，配置成延时节点，尽可能防止逻辑损坏，一般情况下这个节点会被用备份
	
我们配置的是SQL_thread的延时

mysql>stop slave;

mysql>CHANGE MASTER TO MASTER_DELAY = 60;

mysql>start slave;

mysql> show slave status \G
SQL_Delay: 300

取消延时：
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_DELAY = 0;
mysql> start slave;

9、主从复制高级功能--复制过滤

主库方面控制（不建议使用）：
    白名单:只记录白名单中列出的库的二进制日志
     binlog-do-db
    黑名单：不记录黑名单列出的库的二进制日志
     binlog-ignore-db
	  
从库方面控制：
show slave status\G;查看相关参数。
白名单：只执行白名单中列出的库或者表的中继日志   

--replicate-do-db=test
--replicate-do-table=test.t1
--replicate-wild-do-table=test.x*
   
黑名单：不执行黑名单中列出的库或者表的中继日志
--replicate-ignore-db
--replicate-ignore-table
--replicate-wild-ignore-table
只复制world数据库的数据

10、主从复制新特性--GTID复制

GTID
5.6新特性
GTID(Global Transaction ID)是对于一个已提交事务的编号，并且是一个全局唯一的编号。
它的官方定义如下：
GTID = source_id ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
每一台mysql实例中，都会有一个唯一的uuid，标识实例的唯一性
auto.cnf，存放在数据目录下

重要参数：
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1


gtid-mode=on			            --启用gtid类型，否则就是普通的复制架构
enforce-gtid-consistency=true		 --强制GTID的一致性
log-slave-updates=1					--slave更新是否记入日志

-----------------
构建1主2从的GTID复制环境：

3台虚拟机，
db02 克隆两台虚拟机环境，分别命名为db01、db03，在生产中准备3台真实的物理机，不用多实例

要求：

1、IP地址、主机名
	 db01:10.0.0.51/24   
	 db03:10.0.0.53/24
2、清理所有之前3306的相关数据，只留软件

db01:
cd /application/mysql/data/
\rm -rf *
cd /data/binlog/
\rm -rf *

db02:
cd /application/mysql/data/
\rm -rf *
cd /data/binlog/
\rm -rf *

db03:
cd /application/mysql/data/
\rm -rf *
cd /data/binlog/
\rm -rf *

3、准备配置文件
规划：
	主库： 10.0.0.51/24
	从库1: 10.0.0.52/24
	从库2：10.0.0.53/24
主库：
加入以下配置信息
db01:10.0.0.51/24
vim /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=51
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/tmp/mysql.sock

slave1：
db02:10.0.0.52/24

vim /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=52
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/tmp/mysql.sock

slave2：
db02:10.0.0.53/24

vim /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=53
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/tmp/mysql.sock

-----------------
三台节点分别初始化数据：

/application/mysql/scripts/mysql_install_db --user=mysql  --basedir=/application/mysql --datadir=/application/mysql/data/ 

分别启动三个节点mysql：
/etc/init.d/mysqld start

测试启动情况：
mysql -e "show variables like ‘server_id‘"

master:51
slave:52,53

51:
grant replication slave  on *.* to repl@‘10.0.0.%‘ identified by ‘123‘;
52\53:
change master to master_host=‘10.0.0.51‘,master_user=‘repl‘,master_password=‘123‘ ,MASTER_AUTO_POSITION=1;
start slave;

十三、mysql高可用

1、普通主从复制架构存在的不足

高可用？
业务不间断的工作。
用户的体验不出来业务断点。

普通主从环境，存在的问题：

1、监控的问题：APP应用程序，并不具备监控数据库的功能，没有责任监控数据库是否能连接。
2、选主的问题：
3、failover：VIP漂移，对于应用透明
4、数据补偿

2、企业高可用解决方案：

MMM（过时）

MHA（目前推荐）
PXC、Galera Cluster（出现很多年，企业很少用）
5.7.17 MGR 、Innodb Cluster（未来的趋势，尽早研究）
MySQL NDB Cluster（出现很多年，仍然不完善）
MyCAT 高可用

3、MHA高可用架构部署实战：

3.0 MHA介绍及工作原理

（1）Manager程序负责监控所有已知Node（1主2从所有节点）
（2）当主库发生意外宕机
	（2.1）mysql实例故障（SSH能够连接到主机）
		   0、监控到主库宕机，选择一个新主（取消从库角色，reset slave），选择标准：数据较新的从库会被选择为新主(show slave status\G)
		   1、从库通过MHA自带脚本程序，立即保存缺失部分的binlog
		   2、二号从库会重新与新主构建主从关系，继续提供服务
		   3、如果VIP机制，将vip从原主库漂移到新主，让应用程序无感知
	（2.2）主节点服务器宕机（SSH已经连接不上了）
		   0、监控到主库宕机，尝试SSH连接，尝试失败
		   1、选择一个数据较新的从库成为新主库（取消从库角色 reset slave），判断细节：show slave status\G
		   2、计算从库之间的relay-log的差异，补偿到2号从库
		   3、二号从库会重新与新主构建主从关系，继续提供服务
		   4、如果VIP机制，将vip从原主库漂移到新主，让应用程序无感知
		   5、如果有binlog server机制，会继续讲binlog server中的记录的缺失部分的事务，补偿到新的主库

3.1、安装mha node：

依赖包perl-DBD-MySQL ，并在三个节点都安装node软件

检查防火墙和enforce开关情况：
iptables -L
getenforce
关闭二进制日志删除功能：relay_log_purge=0;
数据库中全局关闭：set relay_log_purge=0;
检查状态：mysql -e "show variables like ‘%relay%‘";
上传MHA软件，然后解压：unzip mha.zip
依赖包perl-DBD-MySQL ，并在三个节点都安装node软件
rpm包直接
rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

3.2、主库中创建mha管理用户

grant all privileges on *.* to mha@‘10.0.0.%‘ identified by ‘mha‘;

3.3、配置软连接

ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
ln -s /application/mysql/bin/mysql /usr/bin/mysql

3.4、部署manger节点（建议在从节点db03）

wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-6.repo
yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes

3.5、安装 manager软件

rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

3.6、创建Manager必要目录与配置文件

mkdir -p /etc/mha
mkdir -p /var/log/mha/app1    ----》可以管理多套主从复制
创建配置文件 (不需要的配置不要留着，注释没用,切换后会重写)
vim /etc/mha/app1.cnf     -----》serverdefault可以独立
[server default]                        
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/binlog
user=mha
password=mha
ping_interval=2
repl_password=123
repl_user=repl
ssh_user=root

[server1]
hostname=10.0.0.51
port=3306

[server2]
hostname=10.0.0.52
port=3306

[server3]
hostname=10.0.0.53
port=3306

3.7、配置互信(所有节点)

ssh-keygen -t dsa -P ‘‘ -f ~/.ssh/id_dsa >/dev/null 2>&1

ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.51
ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.52
ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.53

测试：ssh 10.0.0.51 date
...

3.8、检测互信

 masterha_check_ssh  --conf=/etc/mha/app1.cnf

3.9、检测主从

 masterha_check_ssh  --conf=/etc/mha/app1.cnf

3.10、启动MHA manager

nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

tail -f /var/log/mha/app1/manager

故障演练：

1、宕掉db01主库
/etc/init.d/mysqld stop
2、tail -f /var/log/mha/app1/manager  观察日志变化(实时监控日志)
3、恢复主库运行，重新将db01加入到主从复制关系中
检查状态：show slave stauts\G;
/etc/init.d/mysqld start
CHANGE MASTER TO MASTER_HOST=‘10.0.0.52‘, MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER=‘repl‘, MASTER_PASSWORD=‘123‘;
start slave;
show slave status\G;
4、将配置文件中加入修稿的故障节点(宕机后自动删除被删除的server信息)
5、启动MHA了manager程序（经历主库宕机后，manager会完成自杀进程的步骤）

 masterha_check_ssh  --conf=/etc/mha/app1.cnf 

 masterha_check_ssh  --conf=/etc/mha/app1.cnf 

nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

3.11、使用MHA自带脚本实现IP FailOver(vip 漂移，应用透明)
#################################END#########################################

配置步骤

上传准备好的/usr/local/bin/master_ip_failover(脚本文件)
chmod +x master_ip_failover
dos2unix /usr/local/bin/master_ip_failover

vim /etc/mha/app1.cnf
添加：
master_ip_failover_script=/usr/local/bin/master_ip_failover

重启mha
masterha_stop --conf=/etc/mha/app1.cnf

nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

手工在主库上绑定vip，注意一定要和配置文件中的ethN一致（master_ip_failover），我的是eth0:1(1是key指定的值)

ifconfig eth0:1 10.0.0.55/24

切换测试：
停主库，看vip是否漂移

/etc/init.d/mysqld stop

3.12、binlogserver配置：

找一台额外的机器，必须要有5.6以上的版本，支持gtid并开启，我们直接用的第二个slave
vim /etc/mha/app1.cnf（在10.0.0.53机器上）
[binlog1]
no_master=1
hostname=10.0.0.53
master_binlog_dir=/data/mysql/binlog

提前创建好,这个目录不能和原有的binlog一致
mkdir -p /data/mysql/binlog
chown -R mysql.mysql /data/mysql/*
修改完成后，将主库binlog拉过来（从000001开始拉，之后的binlog会自动按顺序过来）

cd /data/mysql/binlog     -----》必须进入到自己创建好的目录，在主库的/data/binlog目录中查看是否是从以下001开始的。

mysqlbinlog  -R --host=10.0.0.51 --user=mha --password=mha --raw  --stop-never mysql-bin.000001 &

重启MHA，生效配置：

重启mha
masterha_stop --conf=/etc/mha/app1.cnf

nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

3.13、其他参数说明
ping_interval=2 manager检测节点存活的间隔时间，总共会探测4次。

设置为候选master，如果设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不是集群中事件最新的slave

candidate_master=1

默认情况下如果一个slave落后master 100M的relay logs的话，MHA将不会选择该slave作为一个新的master，

因为对于这个slave的恢复需要花费很长时间，通过设置check_repl_delay=0,
MHA触发切换在选择一个新的master的时候将会忽略复制延时，这个参数对于设置了candidate_master=1的主机非常有用，
因为这个候选主在切换的过程中一定是新的master
check_repl_delay=0

十四、读写分离 Atlas架构

1、安装软件

rpm -ivh Atlas-2.2.1.el6.x86_64.rpm

2、修改配置

cd /usr/local/mysql-proxy/

vim /usr/local/mysql-proxy/conf/test.cnf
[mysql-proxy]
admin-username = user
admin-password = pwd
proxy-backend-addresses = 10.0.0.55:3306
proxy-read-only-backend-addresses = 10.0.0.51:3306,10.0.0.52:3306
pwds = repl:3yb5jEku5h4=,mha:O2jBXONX098=
daemon = true
keepalive = true
event-threads = 8
log-level = message
log-path = /usr/local/mysql-proxy/log
sql-log=ON
proxy-address = 0.0.0.0:33060
admin-address = 0.0.0.0:2345
charset=utf8

/usr/local/mysql-proxy/bin/encrypt  123      ---->制作加密密码

3、启动atlas

/usr/local/mysql-proxy/bin/mysql-proxyd test start
ps -ef |grep proxy

4、测试

测试读写分离：

读的测试
mysql -uroot -p123 -h10.0.0.53 -P33060 
show variables like ‘server_id‘;

写操作测试：

设置两个从节点只读
set global read_only=1;

连接测试

mysql -umha -pmha -h10.0.0.53 -P33060
create database db1;

5、管理atlas

连接管理接口：
mysql -uuser -ppwd -h127.0.0.1 -P2345
打印帮助：
mysql> select * from help;查看帮助命令
select * from backends;查看读写节点
动态添加删除节点：
REMOVE BACKEND 3;
ADD SLAVE 10.0.0.53:3306;
SAVE CONFIG;（配置文件中自动去除已经被删除的或者添加该节点）

6、扩展——atlas 自动分表

school.stu    1000w
id   name 

stu_0   stu_1   stu_2   stu_3    stu_4

以上分表方式,存数据非常均匀,取数据不均与,因为要考虑业务需求
如果业务查询热点数据集中在id是1-200w这些数据,那么读取就不均匀
取模分表

n/5  取余数  (0,1,2,3,4) 
(1)如果是  0  则分到 stu_0
(2)如果是  1  则分到 stu_1
(3)如果是  2  则分到 stu_2
(4)如果是  3  则分到 stu_3
(5)如果是  4  则分到 stu_4

取余数
配置文件
vim /usr/local/mysql-proxy/conf/test.cnf
tables = school.stu.id.5

重启atlas
(主库)手工创建,分表后的库和表,分别为定义的school  和 stu_0 stu_1 stu_2 stu_3 stu_4
create database school;
use school
create table stu_0 (id int,name varchar(20));
create table stu_1 (id int,name varchar(20));
create table stu_2 (id int,name varchar(20));
create table stu_3 (id int,name varchar(20));
create table stu_4 (id int,name varchar(20));

测试:

insert into stu values (3,‘wang5‘);

insert into stu values (2,‘li4‘);

insert into stu values (1,‘zhang3‘);

insert into stu values (4,‘m6‘);

insert into stu values (5,‘zou7‘);

commit;

select * from stu_01 

或者 select * from stu where id=01

结束

mysql数据库深入学习

码农公寓

mysql 数据库

一、数据库介绍

1、关系型数据库的特点

2、NoSQL：非关系型数据库（Not only SQL）

3、web1.0时代

4、web2.0时代

5、2003 NoSQL出现

6、redis特点

7、NoSQL特性总览：

8、NoSQL的分类、特点、典型产品

9、mysql发展史

10、mysql特点

二、mysql安装及启动

1、mysql安装方式介绍

2、mysql安装

3、客户端程序连接到MySQL方式

4、mysql 的SQL层处理：

5、mysql常用命令

6、清理不常用的数据库

三、mysql的基本管理

1、数据库关闭启动

2、mysql配置文件

3、配置mysql多实例

四、数据库用户管理

1、用户定义

2、用户授权

五、SELECT高级应用

1、课程大纲

2、开发环境

3、select语法环境

4、WHERE子句

5、ORDER BY子句

6、LIMIT子句

7、多表连接查询

8、集合操作

9、分组操作及分组处理

11、子查询

六、mysql数据类型

1、课程大纲

2、数据类型介绍

3、数值数据类型

4、字符串数据类型

5、二进制字符串数据类型

6、时间数据类型

7、列属性

8、如何选择数据类型

七、MySQL 客户端工具及SQL入门

1、课程大纲：

2、mysql接口程序使用及SQL入门

2、服务器端命令（SQL）

八、mysql 基础优化-索引管理

1、课程大纲

2、执行计划获取及分析

3、explain 调取语句的执行计划

4、数据库索引的设计原则：

5、索引的开发规范

九、mysql 存储引擎

1、课程大纲

2、mysql存储引擎介绍

3、mysql存储引擎种类

4、数据库的存储引擎

5、innodb体系结构：

6、Innodb引擎-事务

7、mysql事务控制语句

8、事务的日志

十、myql日志管理

1、日志的类型简介

2、MySQL错误日志配置及查看

3、MySQL binlog介绍及管理实战

4、慢日志管理：

十一、mysql 备份恢复

1、备份的类型

2、备份方式

3、备份工具：

4、mysqldump+binlog企业恢复实战

5、xtrabackup 介绍

6、xtrabackup 实现增量备份及故障恢复

7、xtrabackup生产恢复案例：

十二、mysql主从复制