MySQL5.7.36稳定版常用操作、8.0特点及安装部署
一、Mysql5.7.36常用操作
1. 实例1安装
1.清理环境mariadb可能和mysql5.7冲突.
yum remove mariadb-libs -y
yum install libaio -y
2.创建用户和组
useradd mysql -s /sbin/nologin -M
3.上传tar包解压并创建软连接
tar xvf mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz
ln -s mysql-5.7.36-linux-glibc2.12-x86_64/ /user/local/mysql
4.创建相关目录
mkdir /data/mysql
5.设置权限
chown -R mysql.mysql /usr/local/mysql /data /usr/local/mysql-5.7.36-linux-glibc2.12-x86_64/
6.配置环境变量
vim /etc/profile.d/mysql.sh
- 输入以下内容保存：
export PATH=/usr/local/mysql/bin:$PATH -
输入命令：source /etc/profile
7.配置my.cnf
[mysqld]
port=3306
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
log-error=/data/mysql/mysql.err
pid-file=/data/mysql/mysql.pid
server_id=1
log_bin=/data/mysql/mysql-bin
##以下配置可选
character-set-server=utf8
skip-name-resolve
symbolic-links=0
default_storage_engine = InnoDB
[mysql]
default-character-set=utf8
8.初始化mysql
cd /usr/local/mysql/bin/
./mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/ --datadir=/data/mysql/ --user=mysql --initialize-insecure
##注意--initialize-insecure后面的参数不能错，否则可以正常初始化，但是启动会报错：
Can‘t open the mysql.plugin table. Please run mysql_upgrade to create it
'或者'
Starting MySQL...[ERROR] The server quit without updating PID file
9.创建启动文件并启动
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
service mysql start （stop/restart/reload）
10.或者创建systemctl启动
[root@web06]# vi /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
# (此处对应MySQL程序和my.cnf所在的路径)
LimitNOFILE = 5000
保存退出
[root@web06]# systemctl start mysqld
11.启动并授权用户
mysql -uroot -p
grant all on *.* to root@'%' identified by '*****'
2. 实例2的安装
## ---多实例方式安装实例2---
1.创建mysql文件夹及授权
[root@web06]# mv mysql5.7.36 /usr/local/mysql3307
[root@web06]# mkdir /data/mysql3307
[root@web06]# chown -R mysql.mysql /data/mysql3307 /usr/local/mysql3307
2.配置my3307.cnf
[mysqld]
port=3307
user=mysql
basedir=/usr/local/mysql3307
datadir=/data/mysql3307
socket=/data/mysql3307/mysql.sock
log-error=/data/mysql3307/mysql.err
pid-file=/data/mysql3307/mysql.pid
##以下配置可选
character_set_server=utf8mb4
skip-name-resolve
explicit_defaults_for_timestamp=true
symbolic-links=0
server_id=2
log_bin=/data/mysql3307/mysql3307-bin
3.配置环境变量
vim /etc/profile.d/mysql3307.sh
-
输入以下内容保存：
export PATH=/usr/local/mysql3307/bin:$PATH -
输入命令：source /etc/profile
4.初始化mysql
cd /usr/local/mysql3307/bin/
./mysqld --defaults-file=/etc/my3307.cnf --basedir=/usr/local/mysql3307/ --datadir=/data/mysql3307/ --user=mysql --initialize-insecure
5.创建systemctl启动
[root@web06]# vim /usr/lib/systemd/system/mysqld3307.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql3307/bin/mysqld --defaults-file=/etc/my3307.cnf # (此处对应MySQL程序和my.cnf所在的路径)
LimitNOFILE = 5000
保存退出
[root@web06]# systemctl start mysqld3307
6.启动mysql3307实例2
mysql -S /data/mysql3307/mysql.sock
3.快速xbk主从
0. 安装xbk
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
# 安装2.4.*版对应mysql5.7.*以下版本
yum install percona-xtrabackup-24 -y
1. 主库全备
xtrabackup --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=root --password= --backup --target-dir=/data/backup/xbk
2. 停止数据库3307
systemctl stop mysql3307
3. 恢复全备#同主机多实例需额外指定对应实例防止出错
xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log --target-dir=/backup/xbk
4. 覆盖数据文件
\cp -fr /backup/xbk/* /data/mysql3307/
5. 恢复权限和用户mysql
chown -R mysql.mysql /data/mysql3307
6. 启动从库mysql3307，主从库都创建用户
mysql -S /data/mysql3307/mysql.sock
grant replication slave on *.* to rep@'%' identified by '123';
7. 查看xbk下的info信息文件并配置主从
cat /backup/xbk/xtrabackup_info
change master to master_host='10.0.0.6',master_port=3306,
master_user='rep',master_password='123',master_log_file='mysql-bin.000001',master_log_pos=4049;
start slave;
8. 设置从库只读，root权限也只读
set global read_only = on;
set global super_read_only = on;
'做mha需要关闭'
4. 快速MHA
1. 各节点安装依赖
yum install -y perl-DBD-MySQL perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
2. 各节点先安装node，再安装manager
yum localinstall -y mha4mysql-node-0.56-0.el6.noarch.rpm
yum localinstall -y mha4mysql-manager-0.56-0.el6.noarch.rpm
3. 配置mha
mkdir /data/mha
vim /data/mha/config.cnf
------
[server default]
#指定日志存放路径及名称
manager_log=/data/mha/manager.log
#指定mha工作目录
manager_workdir=/data/mha
#备份的binlog存放目录(必须与master的mysql的binlog位置一致)
master_binlog_dir=/data/mysql/
#MHA管理用户
user=mha
password=mha
ping_interval=2
repl_user=rep
repl_password=123
ssh_user=root
[server1]
hostname=10.0.0.6
port=3306
[server2]
hostname=10.0.0.6
port=3307
4. 创建mha用户 #主库执行即可
mysql> grant all on *.* to mha@'%' identified by 'mha';
5. 创建rep用户 #每个mysql上需要创建rep用户用于切换:
grant replication slave on *.* to rep@'%' identified by '123';
6. 创建秘钥对
[root@web06 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
7. 发送公钥，包括自己
[root@web06 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.6
8. 检查mha状态
masterha_check_repl --conf=/data/mha/config.cnf
masterha_check_ssh --conf=/data/mha/config.cnf
9. 启动mha
nohup masterha_manager --conf=/data/mha/config.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /data/mha//manager.log 2>&1 &
10. 检查启动状态
masterha_check_status --conf=/data/mha/config.cnf
显示如下即为成功：
config (pid:21288) is running(0:PING_OK), master:10.0.0.6
二、MySQL8.0的部署
MySQL8.0的快速安装和配置方法:
- 操作系统：CentOS7_x64
- 数据库版本：MySQL-8.0.28
1. 下载安装包
2. 安装8.0
1. #创建安装目录
mkdir /data/mysql/ -p
可选操作：
mkdir /data/mysql/{binlog,data,ibdata,iblog,log,run,tmp} -p
2. #卸载自带的mariadb
yum remove mariadb-* -y
3. #安装依赖
yum install libaio-devel.x86_64 -y
4. #上传并解压安装包:
tar xvf mysql-8.0.22-linux-glibc2.12-x86_64.tar.xz -C /usr/local/mysql/
5. #建立软连接
ln -s /usr/local/mysql/mysql-8.0.22-linux-glibc2.12-x86_64 /usr/local/mysql
6. #设置环境变量
vim /etc/profile.d/mysql.sh
- 输入并保存
export PATH=/usr/local/mysql/bin:$PATH -
执行source /etc/profile
7. #创建用户，授权目录
useradd mysql -s /sbin/nologin -M
chown -R mysql.mysql /data/mysql /usr/local/mysql
- 报错原因：没有安装依赖
3. 配置文件和启动
8.配置my.cnf并初始化
[mysqld]
port=3306
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
log-error=/data/mysql/mysql.err
pid-file=/data/mysql/mysql.pid
# 初始化mysql8.0
cd /usr/local/mysql/bin/
./mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/ --datadir=/data/mysql/ --user=mysql --initialize-insecure
- 安装成功
# 设置启动脚本
cat >/etc/systemd/system/mysqld.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
4. 登录8.0
1.安装完成后启动MySQL，验证是否安装成功：
systemctl start mysqld
2.登录mysql:
mysql -uroot -p 或者 mysql 或者
mysql -S /tmp/mysql3306.sock
在MySQL8.0需要分开执行：
>create user '用户名'@'主机' identified by '密码'；
>grant all on *.* to '用户名'@'主机'；
三、5.7升级8.0
1.注意事项
-
- 升级说明
MySQL升级：MySQL中不存在打补丁的说法，MySQL的所谓的补丁就是升级到一个新版本，然后把原始数据拉起来进行升级。
- 升级说明
-
- 升级方式
inplace就地升级
在一台服务器上，进行原版本升级到新版本，风险较大。如果是主从环境可以先就地升级从库，然后再升级主库，滚动方式升级。
- 升级方式
-
- 升级注意事项
- 支持GA版本之间的升级，不支持跨版本升级。
- 5.5升级5.7时，先将5.5升级到最新版本，然后从5.5的最新版本升级到5.6最新版本，最后从5.6最新版本升级到5.7最新版本
2.升级步骤
1.）设置参数并关闭数据库
1.落盘并关闭
innodb_fast_shutdown=1，
#1表示不干净的关闭数据库，建议设置0，表示干净的关闭，该落盘的数据都落盘
mysql> set global innodb_fast_shutdown=0;
mysql> shutdown;
mysql> exit;
或者
/usr/local/mysql/bin/mysqladmin -uroot -p123 -S /tmp/mysql.sock shutdown
2.冷备份所有数据
2.）升级预检查
1.准备工作
预检查，8.0新特性：mysql8.0有检查工具：mysql-shell,升级前通过该工具检查当前版本是否具备条件升级到8.0，下载：https://downloads.mysql.com/archives/shell/
也可以yum安装：
yum install -y mysql-shell-8.0.28-1.el7.x86_64.rpm
注意：要升级到8.0的哪个版本，建议下载哪个版本的mysql-shell
2.部署mysql-shell
[root@web7]# tar -xf mysql-shell-8.0.28-linux-glibc2.12-x86-64bit.tar.gz
3.进入目录执行检查命令：
./mysqlsh root:123@10.0.0.7:3306 -e "util.checkForServerUpgrade()"
或
mysqlsh -uroot -p123 -S /tmp/mysql.sock -e "util.checkForServerUpgrade()"
--结果例如：
Errors: 0 # 检查是否有错误，如果没有，则可以进行升级
Warnings: 1
Notices: 1
No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.
3.）替换升级
4. 使用新版本数据库，拉起旧版本的数据进行启动，参数（--skip-grant-tables --skip-networking）
[root@web7]# /usr/local/mysql8/bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables --skip-networking &
5.启动完成后，验证业务功能
[root@web7]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.28 MySQL Community Server - GPL
...
mysql> set global innodb_fast_shutdown=0;
mysql> shutdown;
mysql> exit;
6.正常重启业务，升级完成。
[root@web07 local]# systemctl start mysqld
四、MySQL8.0的优势
从5.7到8.0的版本更新，在功能上主要有以下6点：
- 账户与安全
- 优化器索引
- 通用表表达式
- 窗口函数
- InnoDB 增强
- JSON 增强
1. 账户与安全
1.用户的创建与授权
在MySQL5.7的版本：
> grant all privileges on *.* to '用户名'@'主机' identified by '密码'；
在MySQL8.0需要分开执行：
>create user '用户名'@'主机' identified by '密码'；
>grant all privileges on *.* to '用户名'@'主机'；
用以前的一条命令在8.0里面创建用户，会出现sql语法错误
2.认证插件更新
MySQL5.7默认身份插件是mysql_native_password
MySQL8.0默认的身份插件是caching_sha2_password
# 查看身份认证插件命令：show variables like 'default_authentication_plugin%';
# 身份认证插件可以通过以下2中方式改变：
1）系统变量default_authentication_plugin去改变，
在my.ini文件的[mysqld]下面设置default_authentication_plugin=mysql_native_password即可
2）如果希望只是某一个用户通过mysql_native_password的方式认证，可以修改数据库mysql下面的user表的字段，
执行以下命令：>alter user '用户名'@'主机' identified with mysql_native_password by '密码';
3.密码管理
# MySQL8.0的密码管理策略有3个变量:
password_history 修改密码不允许与最近几次使用或的密码重复，默认是0，即不限制
password_reuse_interval 修改密码不允许与最近多少天的使用过的密码重复，默认是0,即不限制
password_require_current 修改密码是否需要提供当前的登录密码，默认是OFF,即不需要；如果需要，则设置成ON
查询当前MySQL密码管理策略相关变量，使用以下命令：
>show variables like 'password%';
1)设置全局的密码管理策略，在my.ini配置文件中，设置以上3个变量的值这种设置方式，需要重启mysql服务器；某些生产环境不允许重启，MySQL8.0提供了关键字persist,持久化，执行以下命令：
>set persist password_history=6;
这条命令会在数据目录下生成新的配置文件（/var/lib/mysql/mysqld-auto.cnf），下次服务器重启的时候除了读取全局配置文件，还会读取这个配置文件,这条配置就会被读入从而达到持久化的目的
2)针对某一个用户单独设置密码管理策略
>alter user '用户名'@'主机' password history 5;
这样，这个用户的password_history 就被设置成了5,查看一下：
>show user,host,Password_reuse_history from user;
查看某一张的字段的所有字段，使用以下命令:
>desc 表名;
4.角色管理
角色：一组权限的集合
一组权限赋予某个角色，再把某个角色赋予某个用户，那用户就拥有角色对应的权限
1)创建一个角色
>create role '角色1';
2)为这个角色赋予相应权限
>grant insert,update on *.* to '角色1';
3)创建一个用户
>create user '用户1' identified by '用户1的密码';
4)为这个用户赋予角色的权限
>grant '角色1' on *.* to '用户1'；
执行完上面4步，用户1就拥有了插入与更新的权限
5)再创建1个用户
>create user '用户2' identified by '用户2的密码';
6)为这个用户赋予同样的角色
>grant '角色1' on *.* to '用户2';
执行完上面2步，用户2也用了角色1的权限，即插入与更新
查看用户权限，执行以下命令：
>show grants for '用户名';
7)启用角色,设置了角色，如果不启用，用户登录的时候，依旧没有该角色的权限
>set default role '角色名' to '用户名';
8)如果一个用户有多个角色，使用以下命令
>set default role all to '用户名';
MySQL中与用户角色相关的表：mysql.default_roles、mysql.role_edges,有兴趣的朋友可以进去查看下。
9)撤销权限
>revoke insert,update on *.* from '角色名';
2. 优化器索引
1.隐藏索引（invisible index）
1.隐藏索引不会被优化器使用，但仍需要维护
应用场景：=============
1）软删除
删除索引，在线上，如果删除错了索引，只能通过创建索引的方式将其添加回来，对于一些大的数据库而言，是比较耗性能的；为了避免删错，可以先将其设置为不可见，优化器这时候就不会使用它，但是后台仍然在维护，确定后，再删除。
2）灰度发布
与软删除差不多，如果想要测试一些索引的功能或者随后可能会使用到这个索引，可以先将其设置为隐藏索引，对于现有的查询不会产生影响，测试后，确定需要该索引，可以将其设置成可见索引。
2.创建隐藏索引，执行如下命令（如果是不隐藏，则不需要后面的invisible关键字）：
>create index 索引名称 on 表名(字段名) invisible;
查询某一张表的索引，执行如下命令：
>show index from 表名；
使用explain语句查看查询优化器对索引的使用情况
>explain select * from 表名 where 条件;
3.查询优化器有很多开关，有一个是use_invisible_indexes(是否使用隐藏索引),默认是off(不适用)，将其设置成on,即可使用隐藏索引。查看当前查询优化器的所有开关变脸，执行如下命令：
>select @@optimizer_switch;
设置已经存在的索引为可见或者隐藏，执行如下命令：
>alter table 表名 alter index 索引名 visible;
>alter table 表名 alter index 索引名 invisible;
# 主键不可以设置为隐藏。
2.降序索引（descending index）
MySQL8.0开始真正支持降序索引，只有InnoDB引擎支持降序所以，且必须是BTREE降序索引，MySQL8.0不在对group by操作进行隐式排序。
3.函数索引
索引中使用函数表达式
支持JSON数据节点的索引
函数索引是基于虚拟列的功能实现的
假设用户表（tb_user）的的用户登录账号(username)不需要区分大小写，则可以创建一个函数索引
>create index username_upper_index on tb_user((upper(username)));
这样在查询的时候 SELECT * FROM tb_user WHERE upper(username) = 'ABD123DSJ'; 就会使用索引。
上面的函数索引，也可以通过MySQL5.7已有的虚拟计算列来模拟，为用户表（tb_user）创建新的一列（new_column）,这一列是计算列，不需要赋值，它的值就是username的大写。
>alter tbale tb_user add column new_column varchar(10) generated always as (upper(username));
然后给new_column创建一个索引，可以达到模拟MySQL8.0中的函数索引的效果。
3. 通用表表达式
1.非递归 CTE
派生表：select * from (select 1) as dt;
通用表表达式：with cte as (select 1) select * from cte;
with cte1(id) as (select 1),cte2 as (select id+1 from cte1) select * from cte1 join cte2;
2.递归 CTE
4. 窗口函数
5. InnoDB增强
1.集成数据字段
2.原子ddl操作
MySQL5.7执行drop命令 drop table t1,t2; 如果t1存在，t2不存在，会提示t2表不存在，但是t1表仍然会被删除。
MySQL8.0执行同样的drop命令，会提示t2表不存在，而且t1表不会被删除，保证了原子性。
ddl操作（针对表）的原子性前提是该表使用的存储引擎是InnoDB
3.自增列持久化
解决了之前的版本，主键重复的问题。
MySQL5.7及其以前的版本，MySQL服务器重启，会重新扫描表的主键最大值，如果之前已经删除过id=100的数据，但是表中当前记录的最大值如果是99，那么经过扫描，下一条记录的id是100，而不是101。
MySQL8.0则是每次在变化的时候，都会将自增计数器的最大值写入redo log,同时在每次检查点将其写入引擎私有的系统表。则不会出现自增主键重复的问题。
4.死锁检查控制
5.锁定语句选项
6. JSON增强
1.内联路径操作符
column->>path
等价于之前的：
JSON_UNQUOTE(column -> path)
JSON_UNQUOTE(JSON_EXTRACT(column,path))
2.JSON聚合函数
MySQL8.0和MySQL5.7.22增加了2个聚合函数
1)JSON_ARRAYAGG(),将多行数据组合成json数组
示例：select o_id,json_arrayagg(attribute) as attributes from t group by o_id;
2)JSON_OBJECTAGG()，用于生成json对象
示例：select o_id json_objectagg(attribute,value) as attributes from t group by o_id;
注意：json的聚合函数针对重复key,会使用最后的覆盖前面已有的值，如果下面的o_id=3，它的color有2个值，一个green,一个yellow,使用生成json的聚合函数的时候，前面的green会被覆盖掉。
3.JSON实用函数
1)JSON_PRETTY() 输出json数据的时候，格式化。
select json_object('id',3,'name','Barney');
select json_pretty(json_object('id',3,'name','Barney'));
2)JSON_STORAGE_SIZE() json数据所占用的存储空间（单位：字节）
3)JSON_STORAGE_FREE() json数据更新后所释放的空间（单位：字节）
4.JSON合并函数
MySQL8.0废弃了JSON_MERGE()函数，推荐使用以下两个函数合并JSON数据
1)JSON_MERGE_PATCH()
2)JSON_MERGE_PRESERV()
上面两个函数都是JSON数据合并，最大的区别就是前者遇到相同key的时候会用后面的覆盖前面的，后者会都保留，看下面的截图：
5.JSON表函数
MySQL8.0新增了JSON_TABLE()函数，将JSON数据转换成关系表，可以将该函数的返回结果当做一个普通的临时表进行sql查询。
