MySQL入门

一、Mysql安装与基本使用

1.1、yum安装mysql

yum install mariadb-server -y

# mariadb新版yum仓库配置文件
vim /etc/yum.repos.d/mariadb.repo
# MariaDB 10.4 CentOS repository list - created 2020-06-02 06:50 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/10.3/centos7-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

rpm -ql MariaDB-server

1.1.1、安装完成后的安全配置

完成安装后运行安全脚本

mysql_secure_installation
# 步骤如下
Enter current password for root (enter for none):	# 输入root密码，这里没有直接回车
Set root password? [Y/n]	# 设置root密码，建议输入 y
New password:				# 设置新root密码
Re-enter new password:		# 重新输入root密码
Remove anonymous users? [Y/n]	# 删除匿名用户，建议 y
Disallow root login remotely? [Y/n]	# 是否禁止远程root登录，建议 y
Remove test database and access to it? [Y/n]	# 删除test库，建议 y
Reload privilege tables now? [Y/n]	# 加载特权表，以上设置立即生效，建议 y

1.2、二进制安装mysql

二进制安装一些目录是固定的，如数据目录位于/data/mysql，程序目录在/usr/local/mysql

# 创建mysql用户
useradd -r -s /sbin/nologin -d /data/mysql mysql

# 创建mysql数据目录
mkdir /data/mysql && chown mysql:mysql -R mysql

# 解压mysql程序文件
tar zxvf mariadb-10.4.13-linux-x86_64.tar.gz -C /usr/local/

# 创建mysql软连接，软连接名必须为mysql
cd /usr/local/
ln -s mariadb-10.4.13-linux-x86_64/ mysql
chown -R root.root mysql/

# 初始化mysql安装脚本
cd /usr/local/mysql/
yum install -y libaio
./scripts/mysql_install_db --datadir=/data/mysql --user=mysql

# 创建mysql配置文件
mkdir /etc/mysql
]# cat /etc/mysql/my.cnf
[client]
port                            = 3306
socket                          = /tmp/mysql.sock
[mysqld]
user                            = mysql
port                            = 3306
socket                          = /tmp/mysql.sock
#basedir                         = /opt/mysql
datadir                         = /data/mysql
character-set-server            = utf8mb4
collation-server                = utf8mb4_general_ci
#skip-networking
skip-external-locking
server-id                       = 1
key_buffer_size                 = 16M
max_allowed_packet              = 1M
sort_buffer_size                = 512K
net_buffer_length               = 16K
myisam_sort_buffer_size         = 8M
innodb_data_home_dir            = /data/mysql
innodb_log_group_home_dir       = /data/mysql
#innodb_data_file_path           = ibdata1:100M:autoextend
innodb_buffer_pool_size         = 2G
innodb_log_file_size            = 48M
innodb_log_buffer_size          = 16M
innodb_flush_log_at_trx_commit  = 1
innodb_lock_wait_timeout        = 50
[mysqldump]
quick
max_allowed_packet              = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer                      = 20M
sort_buffer_size                = 20M
read_buffer                     = 2M
write_buffer                    = 2M
[myisamchk]
key_buffer_size                 = 20M
sort_buffer_size                = 20M
read_buffer                     = 2M
write_buffer                    = 2M
[mysqlhotcopy]
interactive-timeout

cp support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
mkdir /var/log/mariadb/

# 创建mysql环境变量
echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh

# 启动mysql
systemctl restart mysqld

1.3、源码编译安装

为了mysql以后数据盘能拓展，建议使用逻辑卷

1.3.1、磁盘分区

1.3.1.1、创建逻辑卷

# 将data盘更改为逻辑卷
fdisk /dev/sda
Command (m for help): t	#输入t
Partition number (1-5, default 5): 5	#输入data盘号
Hex code (type L to list all codes): 8e	#修改为逻辑卷，可以用L列出所有code
Command (m for help): p

Disk /dev/sda: 107.4 GB, 107374182400 bytes, 209715200 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0x0002ce33

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *        2048     1026047      512000   83  Linux
/dev/sda2         1026048   105883647    52428800   83  Linux
/dev/sda3       105883648   114272255     4194304   82  Linux swap / Solaris
/dev/sda4       114272256   209715199    47721472    5  Extended
/dev/sda5       114274304   209715199    47720448   8e  Linux LVM


partprobe
pvcreate /dev/sda5
pvs
vgcreate vg0 /dev/sda5 -s 16M
vgdisplay
lvcreate -n mysql -l 100%free vg0
lvdisplay
mkfs.xfs /dev/vg0/mysql
blkid
vi /etc/fstab	#将mysql逻辑卷信息写入fstab文件
mount -a
df -h

1.3.2、源码编译安装mysql

getent passwd mysql
useradd -r -s /sbin/nologin -d /data/mysql mysql
chown mysql.mysql /data/mysql

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

tar xf mariadb-10.4.13.tar.gz
cd mariadb-10.4.13
cmake . \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/data/mysql/ \
-DSYSCONFDIR=/etc/ \
-DMYSQL_USER=mysql \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITHOUT_MROONGA_STORAGE_ENGINE=1 \
-DWITH_DEBUG=0 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci
make -j 16 && make install

cp support-files/mysql.server /etc/init.d/
./scripts/mysql_install_db --datadir=/data/mysql --user=mysql
chmod +x /etc/rc.d/init.d/mysql.server
chown mysql.mysql /var/log/mariadb/
# 配置文件参考二进制安装部分创建

systemctl daemon-reload
systemctl start mysql

1.4、多实例安装

以yum安装的举例，其他安装方式类同

cd /var/lib/mysql/
# 创建3个实例，监听端口分别为3306 3307 3308
mkdir /mysql/{3306,3307,3308}/{data,bin,logs,socket,pid,etc} -pv
mysql_install_db --datadir=/mysql/3306/data/ --user=mysql
mysql_install_db --datadir=/mysql/3307/data/ --user=mysql
mysql_install_db --datadir=/mysql/3308/data/ --user=mysql
chown -R mysql.mysql /mysql/
cp /etc/my.cnf /mysql/3306/etc/	#复制配置文件，先复制一台，修改完成后在复制至另外2个文件

vim /mysql/3306/etc/my.cnf
[mysqld]
port=3306
datadir=/mysql/3306/data/
socket=/mysql/3306/socket/mysql.sock
symbolic-links=0
[mysqld_safe]
log-error=/mysql/3306/logs/mariadb.log
pid-file=/mysql/3306/pid/mariadb.pid


cp /mysql/3306/etc/my.cnf /mysql/3307/etc/	#将配置文件里面的内容修改，3306改为3307
cp /mysql/3306/etc/my.cnf /mysql/3308/etc/	#将配置文件里面的内容修改，3306改为3308

在/mysql/{prot}/bin下创建启动脚本，脚本如下：

mysql启动脚本，按实际需要修改下面脚本变量

#!/bin/bash

port=3306
mysql_user="root"
mysql_pwd=""
cmd_path="/usr/bin"
mysql_basedir="/mysql"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"

function_start_mysql()
{
    if [ ! -e "$mysql_sock" ];then
      printf "Starting MySQL...\n"
      ${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf  &> /dev/null  &
    else
      printf "MySQL is running...\n"
      exit
    fi
}


function_stop_mysql()
{
    if [ ! -e "$mysql_sock" ];then
       printf "MySQL is stopped...\n"
       exit
    else
       printf "Stoping MySQL...\n"
       ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
   fi
}


function_restart_mysql()
{
    printf "Restarting MySQL...\n"
    function_stop_mysql
    sleep 2
    function_start_mysql
}

case $1 in
start)
    function_start_mysql
;;
stop)
    function_stop_mysql
;;
restart)
    function_restart_mysql
;;
*)
    printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac

#复制mysqld启动脚本，并修改脚本内容
cp mysqld /mysql/3307/bin/
cp mysqld /mysql/3308/bin/

# 启动服务
/mysql/3306/bin/mysqld start
/mysql/3307/bin/mysqld start
/mysql/3308/bin/mysqld start

# 连接，使用其他mysql命令也需要指明是哪个socket文件
mysql -S /mysql/3306/socket/mysql.sock

二、SQL各种语句

2.1、关系型数据库的常见组件：

• 数据库：database

• 表：table

  • 行：row

  • 列：column

• 索引：index

• 视图：view

• 用户：user

• 权限：privilege

• 存储过程：procedure

• 存储函数：function

• 触发器：trigger

• 事件调度器：event scheduler，任务计划

2.2、数据库常用操作：

-- mysql sql查看帮助可使用help，如
help create

-- 常用数据库语法：
-- 创建
CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME';
CHARACTER SET 'character set name' COLLATE 'collate name'
-- 修改数据库：
ALTER DATABASE DB_NAME character set utf8;
-- 删除数据库
DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME'; 
-- 查看支持所有字符集：
SHOW CHARACTER SET;
-- 查看支持所有排序规则：
SHOW COLLATION;

2.2.1、创建数据库

-- 创建数据库sql：
CREATE DATABASE db1
SHOW CREATE DATABASE db1;	-- 查看字符集
CREATE DATABASE db2 CHARACTER SET utf8mb4;	-- 创建库时指定字符集

2.2.2、数据类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KdJJRz2I-1592459641354)(http://ops-img.cniws.com/img/image-20200608160044807.png)]

2.2.2.1、整型

• tinyint(m) 1个字节 范围(-128~127)

• smallint(m) 2个字节 范围(-32768~32767)

• mediumint(m) 3个字节 范围(-8388608~8388607)

• int(m) 4个字节 范围(-2147483648~2147483647)

• bigint(m) 8个字节 范围(±9.22*10的18次方)

  加了unsigned，则最大值翻倍，如：tinyint unsigned的取值范围为(0~255)，int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，Int(1)和Int(20)是相同的

• BOOL，BOOLEAN：布尔型，是TINYINT(1)的同义词。zero值被视为假，非zero值视为真

2.2.2.2、浮点型(float和double)，近似值

• float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位
• double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位
• 设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位

2.2.2.3、定点数

• 在数据库中存放的是精确值,存为十进制
• decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位
• MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。例如，decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节
• 浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用8个字节
• 因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal，例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal

2.2.2.4、字符串(char,varchar,_text)

• char(n) 固定长度，最多255个字符
• varchar(n) 可变长度，最多65535个字符
• tinytext 可变长度，最多255个字符
• text 可变长度，最多65535个字符
• mediumtext 可变长度，最多2的24次方-1个字符
• longtext 可变长度，最多2的32次方-1个字符
• BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节
• VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节
• 内建类型：ENUM枚举, SET集合

2.2.2.5.二进制数据：BLOB

• BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob是以二进制方式存储，不分大小写
• BLOB存储的数据只能整体读出
• TEXT可以指定字符集，BLOB不用指定字符集

2.2.2.6.日期时间类型

• date 日期 ‘2008-12-2’
• time 时间 ‘12:25:36’
• datetime 日期时间 ‘2008-12-2 22:06:44’
• timestamp 自动存储记录修改时间
• YEAR(2), YEAR(4)：年份
  timestamp字段里的时间数据会随其他字段修改的时候自动刷新，这个数
  据类型的字段可以存放这条记录最后被修改的时间

2.2.2.6 所有类型：

• NULL 数据列可包含NULL值
• NOT NULL 数据列不允许包含NULL值
• DEFAULT 默认值
• PRIMARY KEY 主键
• UNIQUE KEY 唯一键
• CHARACTER SET name 指定一个字符集

2.2.2.7 数值型

• AUTO_INCREMENT 自动递增，适用于整数类型
• UNSIGNED 无符号

2.2.2、创建表

use db1
CREATE TABLE student(id int unsigned auto_increment primary key,name varchar(20) not null,gender ENUM('m', 'f') default 'm',mobile char(11));

MariaDB [db1]> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)      | NO   |     | NULL    |                |
| gender | enum('m','f')    | YES  |     | m       |                |
| mobile | char(11)         | YES  |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.018 sec)

-- 复制其他表结构
create table student like db1.student;

-- 将查询结构作为表结构
create table student1 select * from db1.student;

2.2.3、DML语句（增删改）

-- 增加数据
insert student values(1,'luther','18971140826')
insert student(name,gender,mobile)values('luther','f','18468221470');
insert student(name,gender,mobile)values('luther1','f','18468221471'),('luther2','m','10086');

/*
修改数据，一定要加where，否则为修改表中所有数据，
建议登录mysql增加-U|--safe-updates| --i-am-a-dummy选项*/
update students set classid=2 where StuID=26;	-- 修改stuid为26的clssid为2

-- 删除数据
delete from students where stuid>25;

-- 清空表数据
TRUNCATE TABLE tbl_name; 
-- 删除表
drop tbl_name;

2.2.4、DQL语句（select）

2.2.4.1、单表查询

• 字段显示可以使用别名：
  col1 AS alias1, col2 AS alias2, …

• WHERE子句：指明过滤条件以实现“选择”的功能：
  过滤条件：布尔型表达式
  算术操作符：+, -, *, /, %
  比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <=
  BETWEEN min_num AND max_num
  IN (element1, element2, …)
  IS NULL
  IS NOT NULL

-- 示例
SELECT * FROM students WHERE id < 3;
SELECT * FROM students WHERE gender='m';
SELECT * FROM students WHERE gender IS NULL;
SELECT * FROM students WHERE gender IS NOT NULL;
SELECT * FROM students ORDER BY name DESC LIMIT 2;
SELECT * FROM students ORDER BY name DESC LIMIT 1,2;
SELECT * FROM students WHERE id >=2 and id <=4
SELECT * FROM students WHERE BETWEEN 2 AND 4
SELECT * FROM students WHERE name LIKE ‘t%’
SELECT * FROM students WHERE name RLIKE '.*[lo].*';
SELECT id stuid,name as stuname FROM students

• DISTINCT 去除重复列
  SELECT DISTINCT gender FROM students;
• LIKE:
  % 任意长度的任意字符
  _ 任意单个字符
• RLIKE：正则表达式，索引失效，不建议使用
• REGEXP：匹配字符串可用正则表达式书写模式，同上
• 逻辑操作符：
  NOT
  AND
  OR
  XOR
• GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算
  avg(), max(), min(), count(), sum()
  HAVING: 对分组聚合运算后的结果指定过滤条件
• ORDER BY: 根据指定的字段对查询结果进行排序
  升序：ASC
  降序：DESC
• LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制
• 对查询结果中的数据请求施加“锁”
  FOR UPDATE: 写锁，独占或排它锁，只有一个读和写
  LOCK IN SHARE MODE: 读锁，共享锁，同时多个读

2.2.4.2、多表查询

2.2.4.2.1、多表纵向合并

• union（字段数必须一样，且字段顺序和类型须一样）

  SELECT StuID,Name,Age,Gender FROM students union SELECT * FROM teachers;
  

2.2.4.2.2、交叉连接（横向）

-- 用的不多
SELECT * FROM students CROSS JOIN teachers;

SELECT * FROM students INNER JOIN teachers ON students.TeacherID=teachers.TID;
SELECT StuID,s.Name,s.Age,tid,t.name,t.age FROM students as s INNER JOIN teachers as t ON s.TeacherID=t.TID and s.age > 30;

select st.name,co.course,sc.score from students as st inner join scores as sc on st.stuid = sc.stuid inner join courses as co on co.courseid = sc.courseid;

2.2.5、函数、储存过程、触发器

2.2.5.1、函数

• 保存在mysql.proc表中

• 创建UDF

  CREATE [AGGREGATE] FUNCTION function_name(parameter_name
  type,[parameter_name type,...])
  RETURNS {STRING|INTEGER|REAL}
  runtime_body
  /*参数可以有多个,也可以没有参数
  必须有且只有一个返回值*/
  
  -- 示例：有参数UDF
  DELIMITER //
  CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED) RETURNS
  VARCHAR(20)
  BEGIN
  DELETE FROM students WHERE stuid = uid;
  RETURN (SELECT COUNT(stuid) FROM students);
  END//
  DELIMITER ;
  

2.2.5.2、储存过程

• 存储过程：存储过程保存在mysql.proc表中

• 创建存储过程

  CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter ...]])
  routime_body
  proc_parameter : [IN|OUT|INOUT] parameter_name type
  -- 其中IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；
  -- param_name表示参数名称；type表示参数的类型
  

• 查看存储过程列表

  SHOW PROCEDURE STATUS;
  

• 查看存储过程定义

  SHOW CREATE PROCEDURE sp_name
  

• 调用存储过程

  CALL sp_name ([ proc_parameter [,proc_parameter ...]])
  CALL sp_name
  -- 说明:当无参时,可以省略"()",当有参数时,不可省略"()”
  

• 存储过程修改

  ALTER语句修改存储过程只能修改存储过程的注释等无关紧要的东西,不能修改存储过程体,所以要修改存储过程,方法就是删除重建

• 删除存储过程

  DROP PROCEDURE [IF EXISTS] sp_name
  

• 创建无参存储过程

  delimiter //
  CREATE PROCEDURE showTime()
  BEGIN
  SELECT now();
  END//
  delimiter ;
  CALL showTime;
  

• 创建含参存储过程：只有一个IN参数

  delimiter //
  CREATE PROCEDURE selectById(IN uid SMALLINT UNSIGNED)
  BEGIN
  SELECT * FROM students WHERE stuid = uid;
  END//
  delimiter ;
  call selectById(2);
  

• 示例

  delimiter //
  CREATE PROCEDURE dorepeat(n INT)
  BEGIN
  SET @i = 0;
  SET @sum = 0;
  REPEAT SET @sum = @sum+@i; SET @i = @i + 1;
  UNTIL @i > n END REPEAT;
  END//
  delimiter ;
  CALL dorepeat(100);
  SELECT @sum;
  
  -- 创建含参存储过程:包含IN参数和OUT参数
  delimiter //
  CREATE PROCEDURE deleteById(IN uid SMALLINT UNSIGNED, OUT num
  SMALLINT UNSIGNED)
  BEGIN
  DELETE FROM students WHERE stuid >= uid;
  SELECT row_count() into num;
  END//
  delimiter ;
  call deleteById(2,@Line);
  SELECT @Line;
  /*说明:创建存储过程deleteById,包含一个IN参数和一个OUT参数.调用时,传入删
  除的ID和保存被修改的行数值的用户变量@Line,select @Line;输出被影响行数*/
  

• 存储过程和函数中可以使用流程控制来控制语句的执行

• 流程控制：

  • IF：用来进行条件判断。根据是否满足条件，执行不同语句
  • CASE：用来进行条件判断，可实现比IF语句更复杂的条件判断
  • LOOP：重复执行特定的语句，实现一个简单的循环
  • LEAVE：用于跳出循环控制
  • ITERATE：跳出本次循环，然后直接进入下一次循环
  • REPEAT：有条件控制的循环语句。当满足特定条件时，就会跳出循环语句
  • WHILE：有条件控制的循环语句

2.2.5.3、触发器

• 创建触发器

CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body

-- 说明：
trigger_name：触发器的名称
trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发
trigger_event:：{ INSERT |UPDATE | DELETE }，触发的具体事件
tbl_name：该触发器作用在表名

• 触发器示例

CREATE TABLE student_info (
stu_id INT(11) NOT NULL AUTO_INCREMENT,
stu_name VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (stu_id)
);
CREATE TABLE student_count (
student_count INT(11) DEFAULT 0
);
INSERT INTO student_count VALUES(0);

示例：创建触发器，在向学生表INSERT数据时，学生数增加，DELETE学生时，学生数减少

CREATE TRIGGER trigger_student_count_insert
AFTER INSERT
ON student_info FOR EACH ROW
UPDATE student_count SET student_count=student_count+1;

CREATE TRIGGER trigger_student_count_delete
AFTER DELETE
ON student_info FOR EACH ROW
UPDATE student_count SET student_count=student_count-1;

• 查看触发器

SHOW TRIGGERS
-- 查询系统表information_schema.triggers的方式指定查询条件，查看指定的触发器信息。
mysql> USE information_schema;
Database changed
mysql> SELECT * FROM triggers WHERE
trigger_name='trigger_student_count_insert';

• 删除触发器

  DROP TRIGGER trigger_name;
  

2.2.6、用户管理

-- 创建用户
create user wsec@'128.0.%.%' identified by 'wsec6069';

-- 授权
grant all on *.* to wsec@'128.0.%.%';

-- 创建用户并授权
grant all on *.* to wsec1@'128.0.%.%' identified by 'wsec6069';

-- 取消授权
revoke all on *.* from wsec1@'128.0.%.%';

-- 查看授权
show grants for wsec@'128.0.%.%';

-- 重命名用户
RENAME USER old_user_name TO new_user_name;
-- 删除用户：
DROP USER 'USERNAME'@'HOST'
-- 示例：删除默认的空用户
DROP USER ''@'localhost';

-- 修改密码3钟方式：
SET PASSWORD FOR 'user'@'host' = PASSWORD('password');
UPDATE mysql.user SET password=PASSWORD('password') WHERE host='user';
-- 此方法需要执行下面指令才能生效：
mysql> FLUSH PRIVILEGES;
#mysqladmin -u root -poldpass password 'newpass'

• 忘记管理员密码的解决办法：

  • 启动mysqld进程时，为其使用如下选项：
    –skip-grant-tables

    –skip-networking

  • 使用UPDATE命令修改管理员密码

  • 关闭mysqld进程，移除上述两个选项，重启mysqld

三、索引管理

• 创建索引：

  CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name[(length)],...);
  ALTER TABLE tbl_name ADD INDEX index_name(index_col_name);
  help CREATE INDEX;
  

• 删除索引：

  DROP INDEX index_name ON tbl_name;
  ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);
  

• 查看索引：

  SHOW INDEXES FROM [db_name.]tbl_name;
  

• 优化表空间：

  OPTIMIZE TABLE tb_name;
  

• 查看索引的使用

  SET GLOBAL userstat=1;
  SHOW INDEX_STATISTICS;
  

• EXPLAIN来分析索引的有效性

  /*输出信息说明：
  参考 https://dev.mysql.com/doc/refman/5.7/en/explain-output.html*/
  

四、事务

• 启动事务：

  BEGIN
  BEGIN WORK
  START TRANSACTION
  

• 结束事务：

  COMMIT：提交
  ROLLBACK: 回滚
  -- 注意：只有事务型存储引擎中的DML语句方能支持此类操作
  

• 自动提交：set autocommit={1|0} 默认为1，为0时设为非自动

• 事务支持保存点：savepoint

  SAVEPOINT identifier
  ROLLBACK [WORK] TO [SAVEPOINT] identifier
  RELEASE SAVEPOINT identifier
  

五、日志

事务日志、错误日志、通用日志

5.1、慢查询日志

5.1.1、查看慢日志原因：

MariaDB [hellodb]> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set (0.001 sec)

MariaDB [hellodb]> set profiling=on;
Query OK, 0 rows affected (0.000 sec)

MariaDB [hellodb]> show profiles;
+----------+------------+-------------------------------+
| Query_ID | Duration   | Query                         |
+----------+------------+-------------------------------+
|        1 | 0.00051255 | select sleep(1) from teacher  |
|        2 | 4.00202871 | select sleep(1) from teachers |
+----------+------------+-------------------------------+
2 rows in set (0.000 sec)

MariaDB [hellodb]> show profile for query 2;
+------------------------+----------+
| Status                 | Duration |
+------------------------+----------+
| Starting               | 0.000084 |
| Checking permissions   | 0.000007 |
| Opening tables         | 0.000687 |
| After opening tables   | 0.000018 |
| System lock            | 0.000009 |
| Table lock             | 0.000019 |
| Opening tables         | 0.000055 |
| After opening tables   | 0.000011 |
| System lock            | 0.000008 |
| Table lock             | 0.000160 |
| Closing tables         | 0.000012 |
| Unlocking tables       | 0.000009 |
| Closing tables         | 0.000024 |
| Init                   | 0.000029 |
| Optimizing             | 0.000040 |
| Statistics             | 0.000018 |
| Preparing              | 0.000020 |
| Executing              | 0.000003 |
| Sending data           | 0.000074 |
| User sleep             | 1.000135 |
| User sleep             | 1.000112 |
| User sleep             | 1.000119 |
| User sleep             | 1.000163 |
| End of update loop     | 0.000036 |
| Query end              | 0.000009 |
| Commit                 | 0.000041 |
| Closing tables         | 0.000056 |
| Unlocking tables       | 0.000004 |
| Closing tables         | 0.000019 |
| Starting cleanup       | 0.000005 |
| Freeing items          | 0.000007 |
| Updating status        | 0.000030 |
| Reset for next command | 0.000004 |
+------------------------+----------+
33 rows in set (0.001 sec)

5.2、数据库表整理：

optimize table table_name;

5.3、二进制日志

注意：建议二进制日志和数据文件分开存放

• 二进制日志相关的服务器变量：
  sql_log_bin=ON|OFF：是否记录二进制日志，默认ON

• log_bin=/PATH/BIN_LOG_FILE：指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可

• binlog_format=STATEMENT|ROW|MIXED：二进制日志记录的格式，默认STATEMENT

  STATEMENT：语句模式；ROW：行模式；MIXED：自动模式

• max_binlog_size=1073741824：单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
  说明：文件达到上限时的大小未必为指定的精确值

• sync_binlog=1|0：设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘

• expire_logs_days=N：二进制日志可以自动删除的天数。 默认为0，即不自动删除

5.3.1、二进制日志相关配置

• 查看mariadb自行管理使用中的二进制日志文件列表，及大小
  SHOW {BINARY | MASTER} LOGS
• 查看使用中的二进制日志文件
  SHOW MASTER STATUS
• 查看二进制文件中的指定内容
  SHOW BINLOG EVENTS [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]
  show binlog events in ‘mysql-bin.000001’ from 6516 limit 2,3

5.3.2、mysqlbinlog：二进制日志的客户端命令工具

• 命令格式：

  mysqlbinlog [OPTIONS] log_file…
  --start-position=# 指定开始位置
  --stop-position=#
  --start-datetime=
  --stop-datetime=
  时间格式：YYYY-MM-DD hh:mm:ss
  --base64-output[=name]
  -v -vvv
  示例：mysqlbinlog --start-position=6787 --stop-position=7527 /var/lib/mysql/mariadb-bin.000003 -v
  mysqlbinlog --start-datetime="2018-01-30 20:30:10" --stop-
  datetime="2018-01-30 20:35:22" mariadb-bin.000003 -vvv
  

• 清除指定二进制日志：
  PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
  示例：
  PURGE BINARY LOGS TO ‘mariadb-bin.000003’;删除3之前的日志
  PURGE BINARY LOGS BEFORE ‘2017-01-23’;
  PURGE BINARY LOGS BEFORE ‘2017-03-22 09:25:30’;

• 删除所有二进制日志，index文件重新记数
  RESET MASTER [TO #]; 删除所有二进制日志文件，并重新生成日志文件，文件名从#开始记数，默认从1开始，一般是master主机第一次启动时执行，MariaDB10.1.6开始支持TO #

• 切换日志文件：
  FLUSH LOGS;

六、备份与还原

6.1、mysqldump使用

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] –B DB1 [DB2 DB3...]
mysqldump [OPTIONS] –A [OPTIONS]

6.1.1、mysqldump常见选项：

• -A， --all-databases 备份所有数据库，含create database

• -B , --databases db_name… 指定备份的数据库，包括create database语句

• -E, --events：备份相关的所有event scheduler

• -R, --routines：备份所有存储过程和自定义函数

• –triggers：备份表相关触发器，默认启用,用–skip-triggers，不备份触发器

• –default-character-set=utf8 指定字符集

• –master-data[=#]： 此选项须启用二进制日志

  1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1

  2：记录为注释的CHANGE MASTER TO语句此选项会自动关闭–lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启–single-transaction）

• -F, --flush-logs ：备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和–single-transaction或-x，–master-data 一起使用实现，此时只刷新一次日志

• –compact 去掉注释，适合调试，生产不使用

• -d, --no-data 只备份表结构

• -t, --no-create-info 只备份数据,不备份create table

• -n,–no-create-db 不备份create database，可被-A或-B覆盖

• –flush-privileges 备份mysql或相关时需要使用

• -f, --force 忽略SQL错误，继续执行

• –hex-blob 使用十六进制符号转储二进制列，当有包括BINARY，VARBINARY，BLOB，BIT的数据类型的列时使用，避免乱码

• -q, --quick 不缓存查询，直接输出，加快备份速度

6.1.2、实验：备份还原至最新状态

首先启用二进制日志启用

1 mysqldump -A --master-data=2 > /data/all.sql 710
数据库修改
insert students (name,age)values(‘a’,20);
insert students (name,age)values(‘b’,30);

2 删除库 rm -rf /var/lib/mysql/*

3 还原
确保无用户访问数据库
1）systemctl restart mariadb
2)mysql > show master logs; 查看当前二进制位置
3）根据/data/all.sql中日志位置和2）定位需要的二进制日志范围
mysqlbinlog --start-position=710 mysql-bin.000003 > /data/inc.sql
mysqlbinlog mysql-bin.000004 >> /data/inc.sql
mysqlbinlog mysql-bin.000005 >> /data/inc.sql

4. mysql > set sql_log_bin=off;
   mysql>source /data/all.sql
   mysql>source /data/inc.sql

5. mysql > set sql_log_bin=on;

6. 做检查确认数据库恢复成功，恢复用户访问

6.1.3、innodb热备

InnoDB备份选项： 支持热备，可用温备但不建议用

--single-transaction：此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务
此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE
此选项和–lock-tables（此选项隐含提交挂起的事务）选项是相互排斥
备份大型表时，建议将–single-transaction选项和–quick结合一起使用

6.1.3.1、InnoDB建议备份策略

mysqldump –uroot –A –F –E –R --single-transaction --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

6.1.3.2、MyISAM建议备份策略

mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

6.1.3.3、分库备份脚本

for db in `mysql -e 'show databases'|grep -Ev '^(information_schema|performance_schema|Database)$'`;do mysqldump -B $db --single-transaction --master-data=2 |gzip > /data/$db.sql.gz ;done

mysql -e 'show databases'|grep -Ev '^(information_schema|performance_schema|Database)$'|sed -rn 's@(.*)@mysqldump -B \1 --single-transaction --master-data=2 |gzip > /data/\1\.sql\.gz@p'|bash

6.2、xtrabackup

6.2.1、安装

yum install epel-release -y
yum install percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm

# 生成的文件列表
~]# rpm -ql percona-xtrabackup-24
/usr/bin/innobackupex
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/lib64/xtrabackup/plugin/keyring_file.so
/usr/lib64/xtrabackup/plugin/keyring_vault.so
/usr/share/doc/percona-xtrabackup-24-2.4.20
/usr/share/doc/percona-xtrabackup-24-2.4.20/COPYING
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz

6.2.2、xtrabackup用法

备份：innobackupex [option] BACKUP-ROOT-DIR

6.2.2.1、xtrabackup完全备份及还原

# 1 在原主机做完全备份到/backups
xtrabackup --backup --target-dir=/backup/
scp -r /backup/* 目标主机:/backup

# 2 在目标主机上
# 1）预准备：确保数据一致，提交完成的事务，回滚未完成的事务
xtrabackup --prepare --target-dir=/backup/
# 2）复制到数据库目录
# 注意：数据库目录必须为空，MySQL服务不能启动
xtrabackup --copy-back --target-dir=/backup/
# 3）还原属性
chown -R mysql:mysql /var/lib/mysql
# 4）启动服务
systemctl start mariadb

6.2.2.2、xtrabackup完全，增量备份及还原

# 1 备份过程
# 1）完全备份：
xtrabackup --backup --target-dir=/backup/base
# 2）第一次修改数据
# 3）第一次增量备份
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
# 4）第二次修改数据
# 5）第二次增量
xtrabackup --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
# 6）scp -r /backup/* 目标主机:/backup/
# 备份过程生成三个备份目录
/backup/{base，inc1，inc2}

# 还原过程
# 1）预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
xtrabackup --prepare --apply-log-only --target-dir=/backup/base
# 2）合并第1次增量备份到完全备份，
xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
# 3）合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
# 4）复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
xtrabackup --copy-back --target-dir=/backup/base
# 5）还原属性：
chown -R mysql:mysql /var/lib/mysql
# 6）启动服务：
systemctl start mariadb

七、MySQL主从复制

主从复制原理：

• 主从复制线程：
  • 主节点：
    dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events
  • 从节点：
    I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
    SQL Thread：从中继日志中读取日志事件，在本地完成重放
• 跟复制功能相关的文件：
  • master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等
  • relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地replay log日志的对应关系

7.1、主从复制简单配置

7.1.1、主节点配置：

1. 启用二进制日志

   [mysqld]
   log_bin
   

2. 为当前节点设置一个全局惟一的ID号

   [mysqld]
   server_id=#
   log-basename=master # 可选项，设置datadir中日志名称，确保不依赖主机名
   

3. 创建有复制权限的用户账号

   GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';
   

7.1.2、从节点配置：

1. 启动中继日志

   [mysqld]
   server_id=# #为当前节点设置一个全局惟的ID号
   read_only=ON #设置数据库只读，建议
   relay_log=relay-log #relay log的文件路径，默认值hostname-relay-bin
   relay_log_index=relay-log.index #默认值 hostname -relay-bin.index
   

2. 使用有复制权限的用户账号连接至主服务器，并启动复制线程

   mysql> CHANGE MASTER TO MASTER_HOST='host',
   	MASTER_USER='repluser', MASTER_PASSWORD='replpass',
   	MASTER_LOG_FILE='mariadb-bin.xxxxxx', MASTER_LOG_POS=#;
   mysql> START SLAVE [IO_THREAD|SQL_THREAD]; -- 一般同时启动，执行START SLAVE
   
   mysql> show slave status;
   

7.1.3、现有mysql服务器添加从服务器

1. 首先创建用户并备份，并拷贝到从服务器

   GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';
   mysqldump -A --single-transaction --master-data=1 -F > /root/all.sql
   scp all.sql root@HOST:/root
   

2. 从服务器配置

   [mysqld]
   server_id=# #为当前节点设置一个全局惟的ID号
   read_only=ON #设置数据库只读，建议
   
   # 修改从主服务器上面传过来的sql文件
   # 将CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=385;修改为以下行，主要添加账号信息
   CHANGE MASTER TO
    MASTER_HOST='128.0.1.119',		#添加的行
    MASTER_USER='repluser',		#添加的行
    MASTER_PASSWORD='wsec6069',	#添加的行
    MASTER_PORT=3306,				#添加的行
    MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=385;
    
   # 导入主服务器数据
   mysql < all.sql
   
   start slave;
   

7.1.4、主从服务故障恢复

如果同步出现错误，在没有时间解决的情况下，可以暂时忽略这个错误，否则后面的同步无法进行

stop slave;
set global sql_slave_skip_counter=1;	-- 1表示错误数量
start slave;

-- 也可以在/etc/my.cnf中添加如下配置
slave_skip_errors=1062	-- 1062为错误编号

7.1.4.1、手动提升其中一台从服务为主节点

-- mysql
stop slave;
reset slave all;

修改/etc/my.cnf配置

server_id=10
log-bin
#删除read_only=ON

systemctl restart mariadb

配置另外一台从节点，讲从节点的主节点配置修改为新的

stop slave;
reset slave all;

CHANGE MASTER TO
 MASTER_HOST='HOST',
 MASTER_USER='repluser',
 MASTER_PASSWORD='wsec6069',
 MASTER_PORT=3306,
 MASTER_LOG_FILE='mysql-bin.000001',
 MASTER_LOG_POS=245,
 MASTER_CONNECT_RETRY=10;
 
start slave;

7.1.5、级联复制

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rM0uQANi-1592459641369)(http://ops-img.cniws.com/img/image-20200615172724748.png)]

中间节点slave配置：

• 如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点（参考7.2.3）

  • 通过备份恢复数据至从服务器

• 复制起始位置为备份时，二进制日志文件及其POS

• 如果要启用级联复制,需要在从服务器启用以下配置

  [mysqld]
  log_bin
  log_slave_updates
  server_id=2
  read_only
  

7.2.6、如何保证主从复制的事务安全

• 在master节点启用参数：

  sync_binlog=1 						#每次写后立即同步二进制日志到磁盘，性能差
  # 如果用到的为InnoDB存储引擎：
  innodb_flush_log_at_trx_commit=1 	#每次事务提交立即同步日志写磁盘
  innodb_support_xa=ON 				#默认值，分布式事务MariaDB10.3.0废除
  sync_master_info=# 					#次事件后master.info同步到磁盘
  

• 在slave节点启用服务器选项：

  skip-slave-start=ON 不自动启动slave
  

• 在slave节点启用参数：

  sync_relay_log=# #次写后同步relay log到磁盘
  sync_relay_log_info=# #次事务后同步relay-log.info到磁盘
  

7.1.7、主主复制

容易产生的问题：数据不一致；因此慎用
考虑要点：自动增长id

• 配置一个节点使用奇数id

  auto_increment_offset=1 	#开始点
  auto_increment_increment=2 	#增长幅度
  

• 另一个节点使用偶数id

  auto_increment_offset=2
  auto_increment_increment=2
  

• 主主复制的配置步骤：

  • 各节点使用一个惟一server_id
  • 都启动binary log和relay log
  • 创建拥有复制权限的用户账号
  • 定义自动增长id字段的数值范围各为奇偶
  • 均把对方指定为主节点，并启动复制线程

7.1.8、半同步复制（生产建议配置）

一般在多个从服务节点时启用，一个从服务节点时没必要启动（至少3台，1主2从）

默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失

7.1.8.1、半同步复制实现

配置完成主从后：

• 主服务器配置:

  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;	-- 超时长1s
  mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';
  mysql> SHOW GLOBAL STATUS LIKE '%semi%';
  

• 从服务器配置

  mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
  mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1;
  mysql> stop slave;
  mysql> start slave;
  

7.1.9、MySQL复制

• 复制过滤器：
  让从节点仅复制指定的数据库，或指定数据库的指定表

• 两种实现方式：

  1. 服务器选项：主服务器仅向二进制日志中记录与特定数据库相关的事件

     注意：此项和binlog_format相关

     binlog-do-db = 数据库白名单列表，多个数据库需多行实现
     binlog-ignore-db = 数据库黑名单列表
     

     问题：基于二进制还原将无法实现；不建议使用

  2. 从服务器SQL_THREAD在relay log中的事件时，仅读取与特定数据库(特定表)相关的事件并应用于本地
     问题：会造成网络及磁盘IO浪费

• 从服务器上的复制过滤器相关变量

  replicate_do_db= 					#指定复制库的白名单
  replicate_ignore_db= 				#指定复制库黑名单
  replicate_do_table=	 				#指定复制表的白名单
  replicate_ignore_table= 			#指定复制表的黑名单
  replicate_wild_do_table=foo%.bar% 	#支持通配符
  replicate_wild_ignore_table=
  

7.2、MySQL复制加密（生产建议）

基于SSL复制：

在默认的主从复制过程或远程连接到MySQL/MariaDB所有的链接通信中的数据都是明文的，外网里访问数据或则复制，存在安全隐患。通过SSL/TLS加密的方式进行复制的方法，来进一步提高数据的安全性

7.2.1、配置实现：

• 主服务器开启SSL，配置证书和私钥路径

  mkdir /etc/my.cnf.d/ssl && cd /etc/my.cnf.d/ssl
  openssl genrsa 2048 > cakey.pem
  openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650
  openssl req -newkey rsa:1024 -days 3650 -nodes -keyout master.key > master.csr
  openssl x509 -req -in master.csr -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt
  
  openssl req -newkey rsa:1024 -days 3650 -nodes -keyout slave.key > slave.csr
  openssl x509 -req -in slave.csr -CA cacert.pem -CAkey cakey.pem -set_serial 02 > slave.crt
  

  show variables like '%ssl%';
  

  # 在/etc/my.cnf中添加以下配置
  ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
  ssl-cert=/etc/my.cnf.d/ssl/master.crt
  ssl-key=/etc/my.cnf.d/ssl/master.key
  
  scp -r /etc/my.cnf.d/ssl/ 128.0.1.120:/etc/my.cnf.d/
  

  GRANT REPLICATION SLAVE ON *.* TO 'repluser1'@'128.0.1.%' IDENTIFIED BY 'wsec6069' REQUIRE SSL;
  

• Slave服务器配置

  mysql> reset slave all;
  mysql> stop slave;
  mysql> show slave status\G
  
  mysql> CHANGE MASTER TO
      MASTER_HOST='128.0.1.119',
      MASTER_USER='repluser1',
      MASTER_PASSWORD='wsec6069',
      MASTER_LOG_FILE='mysql-bin.000002',
      MASTER_LOG_POS=543,
      MASTER_SSL=1,
      MASTER_SSL_CA = '/etc/my.cnf.d/ssl/cacert.pem',
  	MASTER_SSL_CERT = '/etc/my.cnf.d/ssl/slave.crt',
  	MASTER_SSL_KEY = '/etc/my.cnf.d/ssl/slave.key';
  

7.3、复制的监控与维护

1. 清理日志

   PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE
   datetime_expr }
   RESET MASTER
   RESET SLAVE
   

2. 复制监控

   SHOW MASTER STATUS
   SHOW BINARY LOGS
   SHOW BINLOG EVENTS
   SHOW SLAVE STATUS
   SHOW PROCESSLIST
   

3. 从服务器是否落后于主服务

   Seconds_Behind_Master：0
   

4. 如何确定主从节点数据是否一致

   percona-tools
   

5. 数据不一致如何修复

   删除从数据库，重新复制

八、基于MySQL5.7的二进制安装和GTID复制

GTID复制：（global transaction id 全局事务标识符） MySQL5.6版本开始支持，GTID复制不像传统的复制方式（异步复制、半同步复制）需要找到binlog和POS点，只需知道master的IP、端口、账号、密码即可。开启GTID后，执行change master to master_auto_postion=1即可，它会自动寻找同步

• GTID 架构

  GTID = server_uuid:transaction_id，在一组复制中，全局唯一server_uuid 来源于 auto.cnf

• GTID服务器相关选项

  gtid_mode #gtid模式
  enforce_gtid_consistency #保证GTID安全的参数
  

8.1、GTID在binlog中的结构和GTID event 结构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-o9hHxLjN-1592459641380)(http://ops-img.cniws.com/img/image-20200617104744195.png)]

8.2、MySQL 5.7二进制安装

tar xvf mysql-5.7.30-el7-x86_64.tar.gz -C /usr/local/
yum install -y libaio vim
useradd -r -s /sbin/nologin mysql
ln -s mysql-5.7.30-el7-x86_64/ mysql
echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh
mkdir /data/mysql && chown mysql.mysql /data/mysql/

# 与上面不同，mysql5.7可以用mysqld初始化数据库
# 初始化完成后，会生成root密码，保存root密码。
mysqld --initialize --user=mysql --datadir=/data/mysql	
# 2020-06-17T03:19:42.852284Z 1 [Note] A temporary password is generated for root@localhost: MUyV8AD(?q&O

mkdir /data/logbin
chown mysql.mysql /data/logbin

cp /etc/my.cnf{,.bak}
vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid

[client]
socket=/data/mysql/mysql.sock

cp mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --list
chkconfig --add mysqld

service mysqld start

8.2.1、主从复制与GTID配置

# vim /etc/my.cnf	从服务类似配置
[mysqld]
server_id=1
log_bin=/data/logbin/ws-mysql-bin
gtid_mode=ON
enforce_gtid_consistency

主与从服务器配置开启gitd后，从服务器就无需指定binlog的开始位置，直接在从服务器上执行：

-- 从服务器
CHANGE MASTER TO MASTER_HOST='128.0.1.121',
    MASTER_USER='repluser',
    MASTER_PASSWORD='wsec6069',
    MASTER_PORT=3306,
    MASTER_AUTO_POSITION=1;
    
start slave;

九、基于proxySQL实现MySQL的读写分离

实现读写分离前，先实现主从复制

注意：slave节点需要设置read_only=1

9.1、proxySQL安装与配置

基于YUM仓库安装

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl= http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

yum install proxysql -y
~]# rpm -ql proxysql
/etc/init.d/proxysql
/etc/proxysql.cnf
/usr/bin/proxysql
/usr/share/proxysql/tools/proxysql_galera_checker.sh
/usr/share/proxysql/tools/proxysql_galera_writer.pl

service proxysql start

基于RPM下载安装：https://github.com/sysown/proxysql/releases

• ProxySQL组成

  服务脚本：/etc/init.d/proxysql
  配置文件：/etc/proxysql.cnf
  主程序：/usr/bin/proxysql
  基于SQLITE的数据库文件：/var/lib/proxysql/

• 启动ProxySQL：service proxysql start

  启动后会监听两个默认端口

  • 6032：ProxySQL的管理端口
  • 6033：ProxySQL对外提供服务的端口

• 使用mysql客户端连接到ProxySQL的管理接口6032，默认管理员用户和密码都是admin：

  mysql -uadmin -padmin -P6032 -h127.0.0.1
  

• 数据库说明：

  • main 是默认的”数据库”名，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容，不能通过dml语句修改，只能修改对应的不以 runtime_ 开头的（在内存）里的表，然后 LOAD 使其生效，
  • SAVE 使其存到硬盘以供下次重启加载
  • disk 是持久化到硬盘的配置，sqlite数据文件
  • stats 是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间，等等
  • monitor 库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查

• 说明：

  在main和monitor数据库中的表， runtime_开头的是运行时的配置，不能修改，只能修改非runtime_表
  修改后必须执行LOAD … TO RUNTIME才能加载到RUNTIME生效
  执行save … to disk 才将配置持久化保存到磁盘，即保存在proxysql.db文件中
  global_variables 有许多变量可以设置，其中就包括监听的端口、管理账号等
  参考: https://github.com/sysown/proxysql/wiki/Global-variables

• 向ProxySQL中添加MySQL节点，以下操作不需要use main也可成功

  MySQL> show tables;
  MySQL > select * from sqlite_master where name='mysql_servers'\G
  MySQL > select * from mysql_servers;
  MySQL > insert into mysql_servers(hostgroup_id,hostname,port) values(10,'128.0.1.121',3306);
  MySQL > insert into mysql_servers(hostgroup_id,hostname,port) values(10,'128.0.1.122',3306);
  MySQL > load mysql servers to runtime;
  MySQL > save mysql servers to disk;
  

• 添加监控后端节点的用户。ProxySQL通过每个节点的read_only值来自动调整它们是属于读组还是写组

  • 在mysql master上执行，会自动同步到从

    MySQL> grant replication client on *.* to monitor@'128.0.1.%' identified by 'wsec6069';
    

  • ProxySQL上配置监控

    MySQL [(none)]> set mysql-monitor_username='monitor';
    MySQL [(none)]> set mysql-monitor_password='wsec6069';
    

    • 加载到RUNTIME，并保存到disk

    MySQL [(none)]> load mysql variables to runtime;
    MySQL [(none)]> save mysql variables to disk;
    

• ProxySQL监控

  监控模块的指标保存在monitor库的log表中

  -- 查看监控连接是否正常的 (对connect指标的监控)：(如果connect_error的结果为NULL则表示正常)
  MySQL> select * from mysql_server_connect_log;
  
  -- 查看监控心跳信息 (对ping指标的监控)：
  MySQL> select * from mysql_server_ping_log;
  
  -- 查看read_only和replication_lag的监控日志
  MySQL> select * from mysql_server_read_only_log;
  MySQL> select * from mysql_server_replication_lag_log;
  

• 设置分组信息（读和写2个分组）

  需要修改的是main库中的mysql_replication_hostgroups表，该表有3个字段：writer_hostgroup，reader_hostgroup，comment, 指定写组的id为10，读组的id为20

  MySQL> insert into mysql_replication_hostgroups values(10,20,"wsec");
  
  -- 将mysql_replication_hostgroups表的修改加载到RUNTIME生效
  MySQL> load mysql servers to runtime;
  MySQL> save mysql servers to disk;
  
  -- Monitor模块监控后端的read_only值，按照read_only的值将节点自动移动到读/写组
  MySQL> select hostgroup_id,hostname,port,status,weight from mysql_servers;
  +--------------+-------------+------+--------+--------+
  | hostgroup_id | hostname    | port | status | weight |
  +--------------+-------------+------+--------+--------+
  | 10           | 128.0.1.121 | 3306 | ONLINE | 1      |
  | 20           | 128.0.1.122 | 3306 | ONLINE | 1      |
  +--------------+-------------+------+--------+--------+
  

• 配置发送SQL语句的用户

  1. 在master节点上创建访问用户（自动同步到从节点）

     MySQL> grant all on *.* to sqluser@'128.0.1.%' identified by 'wsec6069';
     MySQL> flush privileges;
     

  2. 在ProxySQL配置

     将用户sqluser添加到mysql_users表中， default_hostgroup默认组设置为写组10，当读写分离的路由规则不符合时，会访问默认组的数据库

     MySQL> insert into mysql_users(username,password,default_hostgroup) values('sqluser','wsec6069',10);
     MySQL> load mysql users to runtime;
     MySQL> save mysql users to disk;
     

     使用sqluser用户测试是否能路由到默认的10写组实现读、写数据

     mysql -usqluser -pwsec6069 -P6033 -h127.0.0.1 -e 'select @@server_id'
     mysql -usqluser -pwsec6069 -P6033 -h127.0.0.1 -e 'create database testdb'
     mysql -usqluser -pwsec6069 testdb -P6033 -h127.0.0.1 -e 'create table t(id int)'
     

     在proxysql上配置路由规则，实现读写分离

     与规则有关的表：mysql_query_rules和mysql_query_rules_fast_routing，后者是前者的扩展表，1.4.7之后支持

     插入路由规则：将select语句分离到20的读组，select语句中有一个特殊语句SELECT...FOR UPDATE它会申请写锁，应路由到10的写组

     MySQL> insert into mysql_query_rules
         (rule_id,active,match_digest,destination_hostgroup,apply)VALUES
         (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1);
     MySQL> load mysql query rules to runtime;
     MySQL> save mysql query rules to disk;
     
     MySQL>select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;
     +---------+--------+----------------------+-----------------------+-------+
     | rule_id | active | match_digest         | destination_hostgroup | apply |
     +---------+--------+----------------------+-----------------------+-------+
     | 1       | 1      | ^SELECT.*FOR UPDATE$ | 10                    | 1     |
     | 2       | 1      | ^SELECT              | 20                    | 1     |
     +---------+--------+----------------------+-----------------------+-------+
     

     注意：因ProxySQL根据rule_id顺序进行规则匹配，select … for update规则的rule_id必须要小于普通的select规则的rule_id

• 测试

  mysql -usqluser -pwsec6069 -P6033 -h127.0.0.1 -e 'select @@server_id'
  
  # 测试写操作，以事务方式进行测试
  mysql -usqluser -pwsec6069 -P6033 -h127.0.0.1 \
  	-e 'start transaction;select @@server_id;commit;select @@server_id'
  mysql -usqluser -pwsec6069 -P6033 -h127.0.0.1 -e 'insert testdb.t values (1)'
  mysql -usqluser -pwsec6069 -P6033 -h127.0.0.1 -e 'select id from testdb.t'
  

  -- 路由的信息（查看sql的调度过程）：查询stats库中的stats_mysql_query_digest表
  MySQL > SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
  

十、主从复制高可用性解决方案

10.1、MHA

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6LbdZJPM-1592459641386)(http://ops-img.cniws.com/img/image-20200617165452664.png)]

10.1.1、MHA工作原理

1. 从宕机崩溃的master保存二进制日志事件（binlog events）
2. 识别含有最新更新的slave
3. 应用差异的中继日志（relay log）到其他的slave
4. 应用从master保存的二进制日志事件（binlog events）
5. 提升一个slave为新的master
6. 使其他的slave连接新的master进行复制

10.1.2、MHA安装配置

10.1.2.1、MHA基础

MHA由两部分组成：

1. Manager工具包
2. Node工具包

• Manager工具包主要包括以下几个工具：

  masterha_check_ssh 		#检查MHA的SSH配置状况
  masterha_check_repl 	#检查MySQL复制状况
  masterha_manger 		#启动MHA
  masterha_check_status 	#检测当前MHA运行状态
  masterha_master_monitor #检测master是否宕机
  masterha_master_switch 	#故障转移（自动或手动）
  masterha_conf_host 		#添加或删除配置的server信息
  

Node工具包：这些工具通常由MHA Manager的脚本触发，无需人为操作）主要包括以下几个工具：

save_binary_logs 		#保存和复制master的二进制日志
apply_diff_relay_logs 	#识别差异的中继日志事件并将其差异的事件应用于其他的slave
filter_mysqlbinlog 		#去除不必要的ROLLBACK事件（MHA已不再使用此工具）
purge_relay_logs 		#清除中继日志（不会阻塞SQL线程）

注意：为了尽可能的减少主库硬件损坏宕机造成的数据丢失，因此在配置MHA的同时建议配置成MySQL 5.5的半同步复制

自定义扩展：

secondary_check_script： 		#通过多条网络路由检测master的可用性
master_ip_ailover_script：		#更新Application使用的masterip
shutdown_script： 				#强制关闭master节点
report_script： 					#发送报告
init_conf_load_script： 			#加载初始配置参数
master_ip_online_change_script：	#更新master节点ip地址

配置文件：

• global配置，为各application提供默认配置
• application配置：为每个主从复制集群

10.1.2.2、实现MHA

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FSb6L4oH-1592459641401)(http://ops-img.cniws.com/img/image-20200617192409008.png)]

10.1.2.2.1、主从节点配置

首先保障主-从3台主机已经完成配置

• 在管理节点上安装两个包

  mha4mysql-manager
  mha4mysql-node
  

• 在被管理节点安装

  mha4mysql-node
  

• master增加配置

  vim /etc/my.cnf
  [mysqld]
  log-bin
  server_id=1
  skip_name_resolve		#是禁止域名解析的
  

  grant all on *.* to mhauser@'128.0.1.%' identified by 'wsec6069';
  

• slave从节点配置

  vim /etc/my.cnf
  [mysqld]
  server_id=2 #不同节点此值各不相同
  log-bin
  read_only
  relay_log_purge=0	#中继日志不清理
  skip_name_resolve
  

10.1.2.2.2、MHA配置

MHA包下载地址：https://code.google.com/archive/p/mysql-master-ha/downloads

• mha控制节点配置

  yum install epel-release
  
  ~]# ll mha4mysql-*
  -rw-r--r-- 1 root root 87119 Jun  8 11:19 mha4mysql-manager-0.56-0.el6.noarch.rpm
  -rw-r--r-- 1 root root 36326 Jun  8 11:19 mha4mysql-node-0.56-0.el6.noarch.rpm
  
  yum install mha*.rpm
  

• mysql节点配置（MHA被控制节点）

  yum install epel-release
  yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y
  

• 在所有节点实现相互之间ssh key验证

  ssh-keygen
  ssh-copy-id 128.0.1.119
  
  scp -r .ssh 128.0.1.121:/root/
  scp -r .ssh 128.0.1.122:/root/
  scp -r .ssh 128.0.1.123:/root/
  

• 在管理节点建立配置文件

mkdir /etc/mha
# vim /etc/mha/app1.cnf
[server default]
user=mhauser
password=wsec6069
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=repluser
repl_password=wsec6069
ping_interval=1
[server1]
hostname=128.0.1.121
candidate_master=1
[server2]
hostname=128.0.1.122
candidate_master=1
[server3]
hostname=128.0.1.123

• Mha验证和启动

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

• 排错日志：

  /data/mastermha/app1/manager.log
  

10.2、Galera Cluster

注意：都至少需要三个节点，不能安装mariadb-server

Galera Cluster：集成了Galera插件的MySQL集群，是一种新型的，数据不共享的，高度冗余的高可用方案，目前Galera Cluster有两个版本，分别是Percona Xtradb Cluster及MariaDB Cluster，Galera本身是具有多主特性的，即采用multi-master的集群架构，是一个既稳健，又在数据一致性、完整性及高性能方面有出色表现的高可用解决方案

下图图示：三个节点组成了一个集群，与普通的主从架构不同，它们都可以作为主节点，三个节点是对等的，称为multi-master架构，当有客户端要写入或者读取数据时，连接哪个实例都是一样的，读到的数据是相同的，写入某一个节点之后，集群自己会将新数据同步到其它节点上面，这种架构不共享任何数据，是一种高冗余架构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GR4GMxrj-1592459641412)(http://ops-img.cniws.com/img/image-20200618092504694.png)]

10.2.1、Galera Cluster特点

• 多主架构：真正的多点读写的集群，在任何时候读写数据，都是最新的
• 同步复制：集群不同节点之间数据同步，没有延迟，在数据库挂掉之后，数据不会丢失
• 并发复制：从节点APPLY数据时，支持并行执行，更好的性能
• 故障切换：在出现数据库故障时，因支持多点写入，切换容易
• 热插拔：在服务期间，如果数据库挂了，只要监控程序发现的够快，不可服务时间就会非常少。在节点故障期间，节点本身对集群的影响非常小
• 自动节点克隆：在新增节点，或者停机维护时，增量数据或者基础数据不需要人工手动备份提供，Galera Cluster会自动拉取在线节点数据，最终集群会变为一致
• 对应用透明：集群的维护，对应用程序是透明的

工作过程：

Galera Cluster官方文档：
http://galeracluster.com/documentation-webpages/galera-documentation.pdf
http://galeracluster.com/documentation-webpages/index.html
https://mariadb.com/kb/en/mariadb/getting-started-with-mariadb-galera-cluster/

10.2.2、安装与配置

参考仓库：https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-5.5.68/yum/centos7-amd64/

以下配置3台节点都需执行：

yum install MariaDB-Galera-server

vim /etc/my.cnf.d/server.cnf
[galera]
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://128.0.1.121,128.0.1.122,128.0.1.123"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
#下面配置可选项
wsrep_cluster_name = 'mycluster' #集群名称，默认my_wsrep_cluster
wsrep_node_name = 'ws_node1'
wsrep_node_address = '128.0.1.121’

首次启动时，需要初始化集群，在其中一个节点上执行命令

/etc/init.d/mysql start --wsrep-new-cluster

而后正常启动其它节点

service mysql start

查看集群中相关系统变量和状态变量

SHOW VARIABLES LIKE 'wsrep_%';
SHOW STATUS LIKE 'wsrep_%';
SHOW STATUS LIKE 'wsrep_cluster_size';

10.3、复制的问题和解决方案

• 复制的问题和解决方案：

  1. 数据损坏或丢失

     Master： MHA + semi repl（半同步复制）
     Slave： 重新复制

  2. 混合使用存储引擎

     MyISAM：不支持事务
     InnoDB： 支持事务

  3. 不惟一的server id

     重新复制

  4. 复制延迟

     需要额外的监控工具的辅助
     一从多主：mariadb10版后支持
     多线程复制：对多个数据库复制

10.4、TiDb

• TiDB 是 PingCAP 公司受 Google Spanner / F1 论文启发而设计的开源分布式HTAP (Hybrid Transactional and Analytical Processing) 数据库，结合了传统的 RDBMS 和NoSQL 的最佳特性。TiDB 兼容 MySQL，支持无限的水平扩展，具备强一致性和高可用性。 tidb和mysql几乎完全兼容
• TiDB 是一个分布式 NewSQL 数据库。它支持水平弹性扩展、ACID 事务、标准 SQL、MySQL 语法和 MySQL 协议，具有数据强一致的高可用特性，是一个不仅适合 OLTP 场景还适合 OLAP 场景的混合数据库。
• TiDB 的目标是为 OLTP(Online Transactional Processing) 和 OLAP (Online Analytical Processing) 场景提供一站式的解决方案。

TiDB 具备如下核心特点：

1. 高度兼容 MySQL 大多数情况下，无需修改代码即可从 MySQL 轻松迁移至 TiDB，分库分表后的 MySQL 集群亦可通过 TiDB 工具进行实时迁移
2. 水平弹性扩展 通过简单地增加新节点即可实现 TiDB 的水平扩展，按需扩展吞吐或存储，轻松应对高并发、海量数据场景。
3. 分布式事务 TiDB 100% 支持标准的 ACID 事务
4. 真正金融级高可用 相比于传统主从 (M-S) 复制方案，基于 Raft 的多数派选举协议可以提供金融级的 100% 数据强一致性保证，且在不丢失大多数副本的前提下，可以实现故障的自动恢复 (auto-failover)，无需人工介入。
5. 一站式 HTAP 解决方案 TiDB 作为典型的 OLTP 行存数据库，同时兼具强大的 OLAP 性能，配合 TiSpark，可提供一站式 HTAP解决方案，一份存储同时处理OLTP & OLAP(OLAP、OLTP的介绍和比较 )无需传统繁琐的 ETL 过程。
6. 云原生 SQL 数据库 TiDB 是为云而设计的数据库，同 Kubernetes （十分钟带你理解Kubernetes核心概念 ）深度耦合，支持公有云、私有云和混合云，使部署、配置和维护变得十分简单。 TiDB 的设计目标是 100% 的 OLTP 场景和 80% 的 OLAP 场景，更复杂的 OLAP分析可以通过 TiSpark 项目来完成。 TiDB 对业务没有任何侵入性，能优雅的替换传统的数据库中间件、数据库分库分表等 Sharding 方案。同时它也让开发运维人员不用关注数据库 Scale的细节问题，专注于业务开发，极大的提升研发的生产力.

十一、性能测试

11.1、性能衡量指标

• 数据库服务衡量指标：
  Qps：query per second（每秒钟的查询速度）
  Tps：transaction per second（事务每秒的执行次数）
• 压力测试工具：
  • mysqlslap
  • Sysbench：功能强大
    https://github.com/akopytov/sysbench
  • tpcc-mysql
  • MySQL Benchmark Suite
  • MySQL super-smack
  • MyBench

11.2、MYSQL压力测试

Mysqlslap：来自于mariadb包，测试的过程默认生成一个mysqlslap的schema,生成测试表t1，查询和插入测试数据，mysqlslap库自动生成，如果已经存在则先删除。用–only-print来打印实际的测试过程，整个测试完成后不会在数据库中留下痕迹

常用参数 [options] 说明：

• –auto-generate-sql, -a 自动生成测试表和数据，表示用mysqlslap工具自己生成的SQL脚本来测试并发压力
• –auto-generate-sql-load-type=type 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括：read，key，write，update和mixed(默认)
• –auto-generate-sql-add-auto-increment 代表对生成的表自动添加auto_increment列，从5.1.18版本开始支持
• –number-char-cols=N, -x N 自动生成的测试表中包含多少个字符类型的列，默认1
• –number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列，默认1
• –number-of-queries=N 总的测试查询次数(并发客户数×每客户查询次数)
• –query=name,-q 使用自定义脚本执行测试，例如可以调用自定义的存储过程或者sql语句来执行测试
• –create-schema 代表自定义的测试库名称，测试的schema
• –commint=N 多少条DML后提交一次
• –compress, -C 如服务器和客户端都支持压缩，则压缩信息
• –concurrency=N, -c N 表示并发量，即模拟多少个客户端同时执行select。可指定多个值，以逗号或者–delimiter参数指定值做为分隔符如：–concurrency=100,200,500
• –engine=engine_name, -e engine_name 代表要测试的引擎，可以有多个，用分隔符隔开。例如：–engines=myisam,innodb
• –iterations=N, -i N 测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次
• –only-print 只打印测试语句而不实际执行。
• –detach=N 执行N条语句后断开重连
• –debug-info, -T 打印内存和CPU的相关信息

11.3、mysqlslap示例

• 单线程测试

  mysqlslap -a -uroot -pwsec6069
  

• 多线程测试。使用–concurrency来模拟并发连接

  mysqlslap -a -c 100 -uroot -pwsec6069
  

• 迭代测试。用于需要多次执行测试得到平均值

  mysqlslap -a -i 10 -uroot -pwsec6069
  mysqlslap ---auto-generate-sql-add-autoincrement -a
  mysqlslap -a --auto-generate-sql-load-type=read
  mysqlslap -a --auto-generate-secondary-indexes=3
  mysqlslap -a --auto-generate-sql-write-number=1000
  mysqlslap --create-schema world -q "select count(*) from City”
  mysqlslap -a -e innodb -uroot -pwsec6069
  mysqlslap -a --number-of-queries=10 -uroot -pwsec6069
  

• 测试同时不同的存储引擎的性能进行对比

  mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info -uroot -pwsec6069
  

• 执行一次测试，分别50和100个并发，执行1000次总查询

  mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info -uroot -pwsec6069
  

• 50和100个并发分别得到一次测试结果(Benchmark)，并发数越多，执行完所有查询的时间越长。为了准确起见，可以多迭代测试几次

  mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --debug-info -uroot -pwsec6069
  

十二、生产环境my.cnf配置示例

硬件：内存32G

innodb_file_per_table = 1			# 每个表存放于单个文件，mysql5.7默认
max_connections = 8000				# MySQL 服务所允许的同时会话数的上限，经常出现Too Many Connections的错误提示，则需要增大此值
back_log = 300						# back_log 是操作系统在监听队列中所能保持的连接数
max_connect_errors = 1000			# 每个客户端连接最大的错误允许数量，当超过该次数，MYSQL服务器将禁止此主机的连接请求，直到MYSQL服务器重启或通过flush hosts命令清空此主机的相关信息
open_files_limit = 10240			# 所有线程所打开表的数量
max_allowed_packet = 32M			# 每个连接传输数据大小.最大1G，须是1024的倍数，一般设为最大的BLOB的值
wait_timeout = 10					# 指定一个请求的最大连接时间
sort_buffer_size = 16M				# 排序缓冲被用来处理类似ORDER BY以及GROUP BY队列所引起的排序
join_buffer_size = 16M   			# 不带索引的全表扫描.使用的buffer的最小值
query_cache_size = 128M     		# 查询缓冲大小
query_cache_limit = 4M				# 指定单个查询能够使用的缓冲区大小，缺省为1M
transaction_isolation = REPEATABLE-READ	# 设定默认的事务隔离级别
thread_stack = 512K					# 线程使用的堆大小. 此值限制内存中能处理的存储过程的递归深度和SQL语句复杂性，此容量的内存在每次连接时被预留.
log-bin								# 二进制日志功能
binlog_format=row					# 二进制日志格式
innodb_buffer_pool_size = 24G		# InnoDB使用一个缓冲池来保存索引和原始数据, 可设置这个变量到物理内存大小的80%
innodb_file_io_threads = 4			# 用来同步IO操作的IO线程的数量
innodb_thread_concurrency = 16		# 在InnoDb核心内的允许线程数量，建议的设置是CPU数量加上磁盘数量的两倍
innodb_log_buffer_size = 16M		# 用来缓冲日志数据的缓冲区的大小
innodb_log_file_size = 512M			# 在日志组中每个日志文件的大小
innodb_log_files_in_group = 3		# 在日志组中的文件总数
innodb_lock_wait_timeout = 120		# SQL语句在被回滚前,InnoDB事务等待InnoDB行锁的时间
long_query_time = 2					# 慢查询时长
log-queries-not-using-indexes		# 将没有使用索引的查询也记录下来

高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”

• 参考：
  • 阿里巴巴Java开发手册
  • 58到家数据库30条军规解读
    http://zhuanlan.51cto.com/art/201702/531364.htm