MySQL进阶（五）其他杂项与数据库优化

七、触发器与存储过程

7.1 触发器

与表有关的数据对象，在满足某种条件的时候，被动执行的SQL语句。

7.1.1 触发器的特性

1. 有begin、end的结构体（多条sql语句）
2. 需要指定触发的条件：INSERT，UPDATE，DELETE
3. 有指定的触发时间：BEFORE，AFTER

7.1.2 触发器的创建

• 单条业务逻辑的触发器创建

  /*
  CREATE TRIGGER 触发器名称 BEFORE|AFTER INSERT|UPDATE|DELETE ON 表名
  FOR EACH ROW 业务逻辑
  */
  #当b_user表中插入数据后，b_log表中也插入一条数据
  CREATE TRIGGER trigger_insert AFTER INSERT ON b_user
  FOR EACH ROW INSERT INTO b_log(字段) VALUES('插入数据')
  

• 多条业务逻辑的触发器

  /*
  DELIMITER $
  CREATE TRIGGER 触发器名称 BEFORE|AFTER INSERT|UPDATE|DELETE ON 表名
  FOR EACH ROW
  BIGIN
  INSERT...;
  UPDATE...;
  END;$
  */
  #在b_user表中插入数据前，b_log表中插入2条数据
  DELIMITER $
  CREATE TRIGGER trigger_ insert_before BEFORE INSERT ON b_user
  FOR EACH ROW
  BEGIN
  INSERT INTO b_log(comments,name) values('insert1' ，NEW.name);
  INSERT INTO b_log(comments,name) values('insert2' , NEW.name) ;
  END;$
  

总结

• BEFORE|AFTER INSERT用于获取将要插入的数据
• BEFORE|AFTER UPDATE|DELETE用于获取已经修改或删除的数据

7.1.3 删除触发器

DROP TRIGGER 触发器名称

7.2 存储过程

7.2.1 变量

7.2.1.1 系统变量

由mysql数据库管理系统提供的，变量名称固定，可以修改和查看值，分为全局变量和会话变量

全局变量：当mysql服务没有重启时，我们可以查看和修改的变量

会话变量：和MySQL连接形成的会话，生命周期在整个会话过程中

全局变量用global修饰，会话变量用session修饰，通常session可以省略

• 查看系统变量

  SHOW GLOBAL variables; -- 查看全局变量
  SHOW SESSION variables; -- 查看会话变量
  SHOW variables; -- 查看会话变量
  SHOW GLOBAL variables like '%dir%'; -- 模糊查询环境变量
  SELECT @@datadir; -- 查看全局系统变量
  SELECT @@session_track_transaction_info;
  

• 修改系统变量

  SHOW GLOBAL variables like 'autocommit'; -- 全局系统变量中为自动提交事务
  SET GLOBAL autocommit=0; -- 将全局的自动提交的事务改为手动提交
  SHOW SESSION variables link 'autocommit'; -- 查看会话变量中自动提交事务
  SET SESSION autocommit=0; -- 将会话变量中自动提交的事务改为手动提交
  SET @@session.autocommit=1;
  SET @@global.autocommit=1;
  

全局变量在修改后，在不同的会话中都会立即生效，但是在重新启动mysql服务后，全局变量会恢复为默认值，如果想让全局变量依旧有效，需要去修改.ini文件（MySQL配置文件）

会话变量在修改后只对当前会话有效。一般在开发过程中修改会话变量。如：字符编码格式等可以在ini文件中进行设置

7.2.1.2 用户变量

MySQL允许用户自定义变量，分为用户变量和局部变量

• 用户变量
  作用域：当前会话有效

  #设置方式1，先去声明并初始化用户变量，赋值操作既可以使用=进行赋值，也可以使用:=进行赋值
  SET @变量名=值;
  SET @变量名:=值;
  SELECT @变量名;
  #设置方式2
  SELECT 字段 into @变量名 FROM 表名;
  
  

set @aa =‘cra’;
select @aa;

- 局部变量
作用域：在begin end的结构体中，声明必须是begin end结构体的第一句

```mysql
#声明方式，必须在begin后面从第一行开始
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;
#局部变量的赋值
SET 变量名:=值;
SELECT @变量名:=值;
SELECT 字段 into 变量名 FROM 表名;

7.2.2 存储过程的创建

存储过程是一组已经预先编译好的sql语句的集合，理解为批处理语句（增加流程控制语句），一般在复杂逻辑中才会使用存储过程

• 存储过程的优点

  • 提供了代码的可用性

  • 简化了数据库操作，将业务逻辑的细节隐藏在存储过程中

  • 减少了编译次数，减少了网络IO的次数，从而提高操作效率

• 存储过程的创建

  /*
  DELIMITER $
  CREATE PROCEDURE 存储过程的名称(参数列表)
  BEGIN
  局部变量的定义
  多条sql语句
  流程控制语句
  END;$
  */
  

  如果存储过程中只有一条SQL语句可以省略BEGIN END
  参数列表

  参数模式	形参名称	参数类型
  IN	uesrname	mysql数据库中的数据类型（数值型，字符型，日期型）
  OUT	pwd	mysql数据库中的数据类型（数值型，字符型，日期型）
  INOUT	xxx	mysql数据库中的数据类型（数值型，字符型，日期型）

IN：声明该参数是一个输入姓参数（类似于java中的形参）

OUT：声明该参数为一个输出型参数（类似于java中的返回值），在一个存储过程中可以定义多个out类型的参数

INOUT：声明该参数可以为输入型参数，也可以为输出型参数

• 存储过程调用

  CALL 存储过程的名称(实参列表)
  -- 实参列表中包含由输出类型的参数
  

• 存储过程演示

  • 无参的存储过程

    mysql #用于向b_user表中插入2条数据 DELIMITER $ CREATE PROCEDURE pro_insert() BEGIN INSERT INTO b_user(name,sex) VALUES('1','1'); INSERT INTO b_user(name,sex) VALUES('2','2'); END;$ CALL pro_insert();

  • 带有IN模式参数的存储过程

    #用于向b_user表中插入2条数据，性别由客户输入
    DELIMITER $
    CREATE PROCEDURE pro_insert2(IN sex CHAR(1))
    BEGIN
    INSERT INTO b_user(name,sex) VALUES('1',sex);
    INSERT INTO b_user(name,sex) VALUES('2',sex);
    END;$
    CALl pro_insert2('男');
    

  • 多个带有IN参数的存储过程

    #用于向b_user插入2条数据，用户名和密码由客户输入
    DELIMITER $
    CREATE PROCEDURE pro_insert3(IN name VARCHAR(10),IN sex
    VARCHAR(20))
    BEGIN
    INSERT INTO b_user(name,sex) VALUES(name,sex);
    INSERT INTO b_user(name,sex) VALUES(name,sex);
    END;$
    CALL pro_insert2('uname','男');
    

  • 带IN，OUT参数的存储过程

    #判断用户登录，如果用户名和密码输入正确登录成功，否则登录失败
    #根据输入的用户名和密码作为条件去b_user表中查询，如果查询总行数==1，则认为
    登录成功，让result返回登录成功，否则登录失败
    DELIMITER $
    CREATE PROCEDURE pro_login(IN name VARCHAR(20),IN pwd
    VARCHAR(20),OUT result VARCHAR(20))
    BEGIN
    DECLARE total INT DEFAULT 0;-- 用于存放查询总行数
    select count(*) from b_user u where u.name=name and u.pwd=pwd;-
    - 将查询结果赋值给total局部变量
    SET result:=IF(total=1,'登录成功','登录失败');
    END;$
    #存储过程如何执行
    -- 解决判断，使用自定义变量
    SET @result:='';
    CAll pro_login('李四','123',@result);
    select @result;
    

  • 删除存储过程

    DROP 1 PROCEDURE 存储过程名称
    

  • 查看存储过程

    SHOW CREATE PROCEDURE 存储过程名称;
    

  • 修改存储过程

    DROP
    CREATE
    

7.2.2.1 流程控制语句

选择结构

• IF函数

  • 功能：三目运算

  • 语法：IF(逻辑表达式，表达式1，表达式2)

• IF结构

  • 功能：实现多路选择

  • 注意：只能用在BEGIN END结构体中

    /*
    IF 逻辑表达式 THEN 语句1;
    ELSEIF 逻辑表达式2 THEN 语句2;
    ...
    ELSE 语句n;
    END IF;
    */
    

• CASE结构

  • 等值选择

    mysql CASE 字段|变量|表达式 WHEN 值 THEN 值|语句 WHEN 值 THEN 值 ... ELSE 值 END

  • 不等值选择

    mysql CASE WHEN 逻辑表达式 THEN 语句1 ... ELSE 语句n END

循环结构

• WHILE

  /*
  WHILE 逻辑表达式 DO
  循环体
  END WHILE;
  */
  #需求：创建存储过程，输入一个值，返回1到该值的和
  #分析：一个输入参数，一个返回值，在结构体中，从1循环到输入的值，求和
  DELIMITER //
  CREATE PROCEDURE pro_sum(IN input INT,OUT total INT)
  BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE sum_ INT DEFAULT 0;
  WHILE i<=input do
  SET sum_=sum_+i;
  SET i=i+1;
  END WHILE;
  SET totle:=sum_;
  END;//
  SET @result=0;
  CALL por_sun(10,@result);
  SELECT @result;
  

• LOOP

  #Loopnaem是定义的循环名称，为了跳出循环时指定跳出的循环
  loopname:LOOP;
  IF 逻辑表达式 THEN
  LEAVE loopname; -- 跳出当前指定的循环，类似于java中的break
  END IF;
  END LOOP;
  DElIMITER //
  CREATE PROCEDURE pro_sum_loop(IN input INT,OUT total INT)
  BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE sum_ INT DEFAULT 0;
  a:LOOP;
  SET sum_:=sum_+i;
  SET i:=i+1;
  IF i>input THEN
  LEAVE a;
  END IF;
  END LOOP;
  SET total:=sum_;
  END;//
  SET @result=0;
  CALL por_sum_loop(10,@result);
  SELECT @result;
  

• REPEAT

  REPEAT
  循环体
  UNTIL 逻辑表达式 -- 当满足逻辑表达式，跳出循环
  END REPEAT;
  DELIMITER //
  CREATE PROCEDURE pro_sum_loop(IN input INT,OUT total INT)
  BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE sum_ INT DEFAULT 0;
  REPEAT
  SET sum_:=sum_+i;
  SET i:=i+1
  UNTIL i>input
  END REPEAT;
  SET total:=sum_;
  END;//
  SET @result=0;
  CALL por_sum_loop(10,@result);
  SELECT @result;
  

7.3 存储函数

函数也是一组预先编译好的sql的集合，基本和存储过程相似

函数和存储过程的区别

1. 存储过程可以有0个，1个或多个返回值，适用于insert、update、delete操作
2. 函数只能有一个返回值，适用于在处理数据以后，返回一个已知的结果

7.3.1 创建函数

CREATE FUNCTION 函数名称(参数列表) RETURNS 返回类型 BINLOG参数
BEGIN
函数体
END

参数列表：参数名称 参数类型、

BINLOG参数

• NO SQL：函数体中没有sql语句， 也不会改参数

• READS SQL DATE：函数体中存在sql语句，但是整个数据是只读的，不会修改数据

• MODIFIES SQL DATE ：函数体中存在SQL语句，并且会修改数据

• CONTAINS SQL：函数体中包含有SQL语句

函数体：在函数体汇总必须包含return语句，将return放在函数体最后一行执行

mysql #写一个函数，用于求两数之和 DELIMITER // CREATE FUNCTION sum_(input1 INT,input2 INT) RETURNS INT NO SQL BEGIN return input1+input2; END;//

7.3.2 使用函数

SELECT 函数名(参数列表);

7.3.3 查看函数

SHOW CREATE FUNCTION 函数名;

7.3.4 删除函数

DROP FUNCTION 函数名;

7.4 定时任务

7.4.1 查看定时策略是否开启

show variables like '%event_sche%';

开启定时策略：

set global event_scheduler=1;

7.4.2 创建定时任务

create event run_event
on schedule every 1 minute
on completion preserve disable
do call test_procedure ();

1、create event day_event：是创建名为run_event的事件
2、创建周期定时的规则，意思是每分钟执行一次
3、on completion preserve disable是表示创建后并不开始生效。
4、do call test_procedure ()是该event(事件)的操作内容

7.4.3 定时任务操作

1、查看定期任务

SELECT event_name,event_definition,interval_value,interval_field,status
FROM information_schema.EVENTS;

2、开启或关闭定时任务

alter event run_event on completion preserve enable;//开启定时任务
alter event run_event on completion preserve disable;//关闭定时任务

7.4.4 定时规则

1、周期执行–关键字 EVERY

单位有：second、minute、hour、day、week(周)、quarter(季度)、month、year

on schedule every 1 week //每周执行1次

2、在具体某个时间执行–关键字 AT

on schedule at current_timestamp()+interval 5 day //5天后执行
on schedule at '2019-01-01 00:00:00' //在2019年1月1日，0点整执行

3、在某个时间段执行–关键字STARTS ENDS

on schedule every 1 day starts current_timestamp()+interval 5 day ends
current_timestamp()+interval 1 month //5天后开始每天都执行执行到下个月底
on schedule every 1 day ends current_timestamp()+interval 5 day //从现在起每天
执行，执行5天

tar -zxvf FastDFS_v5.05.tar.gz -C /usr/local

八、其他

8.1 MySQL的目录结构

8.1.1 windows中的目录

（1）整体的目录结构

（2）数据文件目录

• 1、bin 目录

  用于放置一些可执行文件，如mysql.exe、mysqld.exe、mysqlshow.exe等。

• 2、data 目录

  用于放置一些日志文件以及数据库。

• 3、include 目录

  用于放置一些头文件，如：mysql.h、mysql_ername.h等。

• 4、lib 目录

  用于放置一系列库文件。

• 5、share 目录

  用于存放字符集、语言等信息。

• 6、my.ini 这个很重要

  是MySQL数据库中使用的配置文件。

配置文件很重要，所谓配置文件就是配置一下你的mysql让他成为你想要的的样子。

8.1.2 linux中的文件目录

当使用的是rpm安装，他会有个默认的路径。

（1）bin

（2）配置文件

（3）pid文件

这个文件用来指导当前运行的mysql实例的进程号！

（4）数据库文件

8.1.3 附录：常用配置

port：端口

server-id：数据库唯一标识码

datadir：数据目录路径

basedir：基本路径

socket：指定socket文件路径

skip-name-resolve：禁止mysql对外部连接进行DNS解析，启用时所有远程连接都要使用ip地址方式

skip-external-locking：避免外部锁，默认开启

skip-grant-tables：mysql启动时不使用grant-tables授权表，常用于忘记密码情况下

user：使用的用户

sql_mode：约束SQL语法模式

show_query_log_file：慢日志存储路径，5.6及以上版本，log-slow-queries是5.6以下版本

long_query_time：设置慢查询时间，默认10s

log-queries-not-using-indexes：记录未使用索引查询

log_throttle_queries_not_using_indexs：5.6.5版本新增参数，设定每分钟记录到日志未使用索引的语句数目，超过则之后只记录语句数量和花费总时间

log_output：日志存储方式，默认FILE，TABLE表示写入到mysql.slow_log表

expire_logs_days：二进制日志保留的时间

log-bin：设置日志文件路径及名称

log-bin-index：日志文件后缀形式

relay-bin：中继日志文件

relay-bin-index：中继日志文件后缀形式

max_connections：mysql最大连接数（超出会报Too many connections）

back_log：mysql暂存的连接数量(每个连接占用256kb)

wait_timeout：关闭一个非交互的连接之前所需要等待的秒数

max_connect_errors：最大错误连接数，flush host进行解禁

max_allowed_packet：接受数据包大小，需要是才会分配内存，设置过大会造成内存溢出

max_heap_table_size：用户可以创建的内存表大小

tmp_table_size：mysql的heap表缓冲大小

read_rnd_buffer_size：mysql随机读缓冲区大小

sort_buffer_size：mysql执行排序使用的缓冲大小

join_buffer_size：联合查询操作所能使用的缓冲区大小

8.1.4 Linux修改MySQL配置的方法

（1）设置全局变量方法1(不推荐): 修改参数文件, 然后重启mysqld

# vi /etc/my.cnf
[mysqld]
wait_timeout=10
# service mysqld restart

不过这个方法太生硬了, 线上服务重启无论如何都应该尽可能避免.

（2）设置全局变量方法2(推荐): 在命令行里通过SET来设置, 然后再修改参数文件

如果要修改全局变量, 必须要显示指定"GLOBAL"或者"@@global.", 同时必须要有SUPER权限.

mysql> set global wait_timeout=10;
mysql> set @@global.wait_timeout=10;

然后查看设置是否成功:

mysql> select @@global.wait_timeout=10;
or
mysql> show global variables like 'wait_timeout';

如果查询时使用的是show variables的话, 会发现设置并没有生效, 除非重新登录再查看. 这是因为使用show variables的话就等同于使用show session variables, 查询的是会话变量, 只有使用show globalvariables查询的才是全局变量. 如果仅仅想修改会话变量的话, 可以使用类似set wait_timeout=10;或者set session wait_timeout=10;这样的语法.

当前只修改了正在运行的MySQL实例参数, 但下次重启mysqld又会回到默认值, 所以别忘了修改参数文件:

# vi /etc/my.cnf
[mysqld]
wait_timeout=10

（3）设置会话变量方法: 在命令行里通过SET来设置

如果要修改会话变量值, 可以指定"SESSION"或者"@@session.“或者”@@“或者"LOCAL"或者”@@local.",或者什么都不使用.

mysql> set wait_timeout=10;
mysql> set session wait_timeout=10;
mysql> set local wait_timeout=10;
mysql> set @@wait_timeout=10;
mysql> set @@session.wait_timeout=10;
mysql> set @@local.wait_timeout=10;

然后查看设置是否成功:

mysql> select @@wait_timeout;
mysql> select @@session.wait_timeout;
mysql> select @@local.wait_timeout;
mysql> show variables like 'wait_timeout';
mysql> show local variables like 'wait_timeout';
mysql> show session variables like 'wait_timeout';

（4）会话变量和全局变量转换方法:

在命令行里通过SET来设置

# 将会话变量值设置为对应的全局变量值呢:
mysql> set @@session.wait_timeout=@@global.wait_timeout;
# 将会话变量值设置为MySQL编译时的默认值(wait_timeout=28800):
mysql> set wait_timeout=DEFAULT;

这里要注意的是, 并不是所有的系统变量都能被设置为DEFAULT, 如果设置这些变量为DEFAULT则会返回错误.

8.2 MySQL实现主从复制/主从同步

如果一个项目只有一个数据库服务器，而这个服务器万一宕机，就会导致业务停顿，造成影响。这个时候 就需要做高可用，避免单点问题。

8.2.1 什么是mysql 的主从复制？

8.2.1.1 简介

指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。对于多级复制，数据库服务器即可充当主机，也可充当从机。

MySQL主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。

一句话表示就是，主数据库做什么，从数据库就跟着做什么。

8.2.1.2 主从复制工作原理剖析

• Master 数据库只要发生变化，立马记录到Binary log 日志文件中
• Slave数据库启动一个I/O thread连接Master数据库，请求Master变化的二进制日志
• Slave I/O获取到的二进制日志，保存到自己的Relay log 日志文件中。
• Slave 有一个 SQL thread定时检查Realy log是否变化，变化那么就更新数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oYeCIhpr-1611052098279)(D:\Java2020\MySQL总结.assets\image-20210119170759532.png)]

8.2.2 为什么要用MySQL的主从

1、实现服务器负载均衡

即可以通过在主服务器和从服务器之间切分处理客户查询的负荷，从而得到更好的客户相应时间。通常情况下，数据库管理员会有两种思路。

一是在主服务器上只实现数据的更新操作。包括数据记录的更新、删除、新建等等作业。而不关心数据的查询作业。数据库管理员将数据的查询请求全部 转发到从服务器中。这在某些应用中会比较有用。如某些应用，像基金净值预测的网站。其数据的更新都是有管理员更新的，即更新的用户比较少。而查询的用户数 量会非常的多。此时就可以设置一台主服务器，专门用来数据的更新。同时设置多台从服务器，用来负责用户信息的查询

二是在主服务器上与从服务器切分查询的作业。在这种思路下，主服务器不单单要完成数据的更新、删除、插入等作业，同时也需要负担一部分查询作业。而从服务器的话，只负责数据的查询。当主服务器比较忙时，部分查询请求会自动发送到从服务器重，以降低主服务器的工作负荷。

2、通过复制实现数据的异地备份

可以定期的将数据从主服务器上复制到从服务器上，这无疑是先了数据的异地备份。在传统的备份体制下，是将数据备份在本地。此时备份 作业与数据库服务器运行在同一台设备上，当备份作业运行时就会影响到服务器的正常运行。有时候会明显的降低服务器的性能。同时，将备份数据存放在本地，也 不是很安全。如硬盘因为电压等原因被损坏或者服务器被失窃，此时由于备份文件仍然存放在硬盘上，数据库管理员无法使用备份文件来恢复数据。这显然会给企业 带来比较大的损失。

3、提高数据库系统的可用性

数据库复制功能实现了主服务器与从服务器之间数据的同步，增加了数据库系统的可用性。当主服务器出现问题时，数据库管理员可以马上让从服务器作为主服务器，用来数据的更新与查询服务。然后回过头来再仔细的检查主服务器的问题。此时一般数据库管理员也会采用两种手段。一是主服务器故障之后，虽然从服务器取代了主服务器的位置，但是对于主服务器可以采取的操作仍然做了一些限制。如仍然只能够进行数据的查询，而 不能够进行数据的更新、删除等操作。这主要是从数据的安全性考虑。如现在一些银行系统的升级，在升级的过程中，只能够查询余额而不能够取钱。这是同样的道理。

二是从服务器真正变成了主服务器。当从服务器切换为主服务器之后，其地位完全与原先的主服务器相同。此时可以实现对数据的查询、更新、删除等操 作。为此就需要做好数据的安全性工作。即数据的安全策略，要与原先的主服务器完全相同。否则的话，就可能会留下一定的安全隐患。

8.2.3 怎么配置mysql主从复制

1、环境准备

本地安装两个mysql，或者使用虚拟机，需要准备两个mysql，

环境，可以使伪集群，也可以是真集群

mysql1(master): 192.168.120.200:3306
mysql2(slave): 192.168.120.201:3306

2、mysql 配置文件配置

mysql1(master): 配置文件设置

#mysql master1 config
[mysqld]
server-id = 1 # 节点ID，确保唯一
# log config
log-bin = master-bin #开启mysql的binlog日志功能
sync_binlog = 1 #控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次
事物提交都会刷到日志文件中，性能最差，最安全
binlog_format = mixed #binlog日志格式，mysql默认采用statement，建议使用mixed
expire_logs_days = 7 #binlog过期清理时间
max_binlog_size = 100m #binlog每个日志文件大小
binlog_cache_size = 4m #binlog缓存大小
max_binlog_cache_size= 512m #最大binlog缓存大
binlog-ignore-db=mysql #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制
这句话，写多行
auto-increment-offset = 1 # 自增值的偏移量
auto-increment-increment = 1 # 自增值的自增量
slave-skip-errors = all #跳过从库错误

mysql2(slave): 201 mysql.cnf配置

[mysqld]
server-id=2
log-bin=mysql-bin
relay-log=mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

重启两个mysql，让配置生效

3、master数据库，创建复制用户并授权

1.进入master的数据库，为master创建复制用户

CREATE USER repl IDENTIFIED BY 'Root12345_';

2.赋予该用户复制的权利

grant replication slave on *.* to 'repl'@'192.168.120.200' identified by
'Root12345_';
FLUSH PRIVILEGES;

3.查看master的状态

show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+------------
-------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------
-------+
| mysql-bin.000005 120| | mysql |
|
+------------------+----------+--------------+------------------+------------
-------+
1 row in set (0.00 sec)

4,配置从库

mysql> CHANGE MASTER TO
MASTER_HOST = '192.168.120.200', #master的IP
MASTER_USER = 'repl', #授权的用户
MASTER_PASSWORD = 'Root12345_', #授权用户的密码
MASTER_PORT = 3306,
MASTER_LOG_FILE='mysql-bin.000005', #上述查询master状态的File栏
MASTER_LOG_POS=120, #上述查询master状态的Position栏
MASTER_RETRY_COUNT = 60,
MASTER_HEARTBEAT_PERIOD = 10000;
# MASTER_LOG_FILE与主库File 保持一致
# MASTER_LOG_POS=120, #与主库position保持一致

启动从库slave进程

mysql> slave start;
Query OK, 0 rows affected(0.04sec)

查看是否配置成功

start slave

显示上图则表示成功

8.2.4 可能遇到的问题

在配置mysql主从复制的时候出现错误：

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

1.现象

Slave_IO_Running:No，mysql的IO线程没有正常工作

2.原因

笔者使用两台虚拟机，一主一从，从库的mysql是直接克隆的。在mysql 5.6的复制引入了uuid的概念，各个复制结构中的server_uuid得保证不一样，但是查看到直接克隆data文件夹后server_uuid是相同的。

3.解决

找到data文件夹下的auto.cnf文件，修改里面的server_uuid值，保证各个db的server_uuid不一样，重启db即可。

找出auto.cnf

find / -name 'auto.cnf'

修改server_uuid的值

vim /var/lib/mysql/auto.cnf

使用

select uuid();

生成一个uuid即可。

重启数据库即可。

8.3 MySQL的优化

8.3.1 MySQL优化主要分为以下四大方面：

设计：存储引擎，字段类型，范式与逆范式

功能：索引，缓存，分区分表。

架构：主从复制，读写分离，负载均衡。

合理SQL：测试，经验。

8.3.2 常见优化的方法

1.选择合适的存储引擎（详见MySQL进阶的2.3 选择合适的存储引擎）

2.字段类型选择（详见MySQL入门的1.2.1 常见数据类型）

​ 字段类型选择尽量要满足以下需求：

​ 尽可能小（占用存储空间少）、尽可能定长（占用存储空间固定）、尽可能使用整数。

3.范式与逆范式（详见MySQL入门的3.1 三范式）

​ 为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

4.sql语句本身优化

• 对于并发性的SQL，少用（不用）多表操作（子查询，联合查询），而是将复杂的SQL拆分多次执行。如果查询很原子（很小），会增加查询缓存的利用率。
• 大量数据的插入，多条 insert或者Load data into table（从文件里载入数据到表里）。建议，先关闭约束及索引，完成数据插入，再重新生成索引及约束。
• like语句操作。一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
• 不要在列上进行运算。将在每个行上进行运算（例：select * from users where YEAR(adddate)<2007;），这将导致索引失效而进行全表扫描（建议：select * from users where adddate<‘2007-01-01’;）。
• 不使用NOT IN和<>操作。NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3来代替。
• select count(*) from table 这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的，如果一定要建议使用MyISAM引擎。
• 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

5.创建合理的索引（详见MySQL进阶的4.5 索引的分类和创建）

• 确保创建索引。不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。
• 多使用复合索引。由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，例如在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age, salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引。
• 索引不会包含有NULL值的列。复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。
• 使用短索引。如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
• 索引并不是越多越好。索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

6.尽量避免大事务操作，提高系统并发能力（详见MySQL进阶的五、MySQL事务）

​ 事务的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。

7.锁，在并发事务下是不是存在行锁升表锁的情况（详见MySQL进阶的六、MySQL锁的机制）

​ 由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，就会产生比较严重的响应延迟。

8.服务器架构优化（详见MySQL进阶的8.2.2 为什么要用MySQL的主从）

• 主从复制，读写分离，负载均衡。因为生产环境中，数据库大多都是读操作，所以部署一主多从架构，主数据库负责写操作，并做双击热备，多台从数据库做负载均衡，负责读操作
• 给数据库增加缓存系统，把热数据缓存到内存中，如果缓存中有要请求的数据就不再去数据库中返回结果，提高读性能。

9.服务器硬件

​ 加大物理内存，提高文件系统性能。linux内核会从内存中分配出缓存区（系统缓存和数据缓存）来存放热数据，通过文件系统延迟写入机制，等满足条件时（如缓存区大小到达一定百分比或者执行sync命令）才会同步到磁盘。也就是说物理内存越大，分配缓存区越大，缓存数据越多。当然，服务器故障会丢失一定的缓存数据

持续更新……