MySQL之2---基础管理
MySQL之2---基础管理
目录
一、用户管理
作用：登录数据库，管理数据库对象
格式
mysql用户账号由两部分组成：
'USERNAME'@'HOST‘
HOST说明：
- 限制此用户可通过哪些远程主机连接mysql服务器
- 支持使用通配符：
% 匹配任意长度的任意字符
172.16.0.0/255.255.0.0 或 172.16.%.%
_ 匹配任意单个字符
示例:
'root'@'localhost‘ 本地用户
'root'@'db02‘ 单一IP用户
'root'@'10.0.0.2‘ 单一IP用户
'root'@'10.0.0.%‘ 范围IP用户
'root'@'10.0.0.5%‘ 范围IP用户
'root'@'10.0.0.0/255.255.254.0‘ 范围IP用户
'root'@'%‘ 范围IP用户
不指定@'HOST‘默认@'%‘
- 匹配优先级：
安全规范:
a. 尽量小, 最好细化到单一IP, 不能使用%
b. 用户名有特点
c. 无用的用户要删除(5.7以前)或者锁定
d. 密码超过三种复杂度, 12位以上
操作命令
创建用户和密码
create user 'USERNAME'@'HOST' identified by 'PASSWORD';
8.0新特性:
8.0 之后语句变化, grant不再支持创建用户和修改密码的功能, 不能
grant all on *.* to wordpress@'10.0.0.%' identified by '123';权限和用户分开管理
必须先创建用户再授权
create user 'wordpress'@'10.0.0.%' identified by '123'; grant all on *.* to wordpress@'10.0.0.%';8.0 之前使用的密码插件是
mysql_native_password8.0 之后使用的密码插件是
caching_sha2_password导致问题: 老的客户端程序, 连接不了8.0版本
解决方法:
- 创建用户时指定密码加密插件为
mysql_native_passwordcreate user wordpress@'10.0.0.%' identified with mysql_native_password by '123';
- 修改用户时替换密码加密插件为
mysql_native_passwordalter user wordpress@'10.0.0.%' identified with mysql_native_password by '123';
- 配置文件中指定默认加密插件为
mysql_native_password
修改用户
alter user wordpress@'10.0.0.%' identified by '123';
锁定用户
alter user wordpress@'10.0.0.%' account lock;
解锁用户
alter user wordpress@'10.0.0.%' account unlock;
删除用户
drop user wordpress@'10.0.0.%';
获取数据表结构
desc mysql.user;
查询所有mysql用户
select user,host,authentication_string,plugin from mysql.user;
设置别名as ""
select user as "用户",host as "白名单",authentication_string as "密码",plugin as "插件" from mysql.user;
查看帮助
? 或者 help
查看创建用户帮助
help create user;
生产中开用户
- 如何沟通开用户
1.是否有邮件批复
2.对哪些库和表做操作
3.做什么操作(增删改查)
4.从什么地址来登录- 开发人员找你要root用户密码？
1.走流程拒绝他
2.如果是金融类的公司
（1）原则上是不允许任何非DBA人员持有或申请root
（2）如果有人私下索要root密码，即时举报。
三、权限管理
作用：约束用户能够对数据库对象(库, 表)使用的功能(SQL)
语法格式
grant 权限 on 权限范围 to 用户;
权限范围
- 对于数据库及内部其他权限
*.* 全局范围, 所有数据库
数据库名.* 单库范围, 指定数据库中的所有
数据库名.表 单表范围, 指定数据库中的某张表
数据库名.存储过程 指定数据库中的存储过程
- 对于用户和IP的权限
用户名@IP地址 用户只能在该IP下才能访问
用户名@192.168.1.% 用户只能在该IP段下才能访问(通配符%表示任意)
用户名@% 用户可以再任意IP下访问(默认IP地址为%)
权限列表
查看所有权限列表
show privileges;
all：除 Grant option 外的所有权限
alter：使用alter table
alter routine：使用alter procedure和drop procedure
create：使用create table
create routine：使用create procedure
create temporary tables：使用create temporary tables
create user：使用create user、drop user、rename user和revoke all privileges
create view：使用create view
delete：使用delete
drop：使用drop table
execute：使用call和存储过程
file：使用select into outfile 和 load data infile
grant option：使用grant 和 revoke
index：使用index
insert：使用insert
lock tables：使用lock table
process：使用show full processlist
select：使用select
show databases：使用show databases
show view：使用show view
update：使用update
usage：无权限-仅允许连接
reload：使用flush
shutdown：使用mysqladmin shutdown(关闭MySQL)
super：使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆
replication client：服务器位置的访问
replication slave：由复制从属使用
... ...
权限类别
管理类
CREATE TEMPORARY TABLES
CREATE USER
FILE
SUPER
SHOW DATABASES
RELOAD
SHUTDOWN
REPLICATION SLAVE
REPLICATION CLIENT
LOCK TABLES
PROCESS
程序类： FUNCTION、PROCEDURE、TRIGGER
CREATE
ALTER
DROP
EXCUTE
库和表级别：DATABASE、TABLE
ALTER
CREATE
CREATE VIEW
DROP
INDEX
SHOW VIEW
GRANT OPTION：能将自己获得的权限转赠给其他用户
数据操作
SELECT
INSERT
DELETE
UPDATE
字段级别
SELECT(col1,col2,...)
UPDATE(col1,col2,...)
INSERT(col1,col2,...)
所有权限
ALL PRIVILEGES 或 ALL
注意：
① MariaDB 服务进程启动时会读取mysql 库中所有授权表至内存
② GRANT 或 REVOKE 等执行权限操作会保存于系统表中，MariaDB 的服务进程通常会自动重读授权表，使之生效
③ 对于不能够或不能及时重读授权表的命令，可手动让MariaDB 的服务进程重读授权表：
FLUSH PRIVILEGES;
操作命令
① 授权
grant 权限 on 数据库.表 to '用户'@'IP地址';
不能重复授权, 重复授权会追加而不是覆盖
② 取消授权(回收权限)
revoke 权限 on 数据库.表 from '用户名'@'IP地址';
③ 查看用户权限
- 专用命令
show grants for '用户'@'IP地址';
- 查看授权表
select * from mysql.user \G;
注意：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）
加载mysql库 use mysql; 查看表名 show tables; user : user host auth plugin 全局范围授权的用户权限 (*.*) db : user host auth plugin 单库范围授权的用户权限 (wp_user.*) table_priv : user host auth plugin 单表范围授权的用户权限 (wp_user.d1)
示例
create user root@'10.0.0.%' identified with mysql_native_password by '123';
grant all on *.* to root@'10.0.0.%';
create user wp_user@'10.0.0.%' identified with mysql_native_password by '123';
grant select,update,delete,insert on wordpress.* to wp_user@'10.0.0.%';
revoke delete on *.* to wp_user@'10.0.0.%';
8.0 新特性
角色 role
创建角色
create role app_rw;给角色授权
grant select,update,delete,insert on wordpress.* to app_rw;创建用户
create user test@'10.0.0.%' identified by '123';给用户授权
grant app_rw to test@'10.0.0.%';查看当前登录用户所属角色
select * from information_schema.APPLICABLE_ROLES;删除角色
drop role app_rw;删除用户
drop user test@'10.0.0.%';
本地管理员( root@localhost 用户)
管理员密码设定（root@localhost）
[root@db01 ~]# mysqladmin password 1 # 设置密码为: 1
[root@db01 ~]# mysqladmin -uroot -p password 123 # 修改密码为: 123
Enter password: # 输入旧密码: 1
本地管理员 root@localhost 用户 忘记密码(误删除\误修改)
- 关闭数据库
[root@db01 ~]# systemctl stop mysqld
- 维护模式启动数据库
[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &
--skip-grant-tables # 跳过授权表 --skip-networking # 跳过远程登录vim /etc/my.cnf [mysqld] skip-grant-tables=1 skip-networking=1
skip-networking=1：关闭TCP/IP网络连接，只侦听本地客户端， 所有和服务器的交互都通过一个socket实现，socket的yum安装默认存放在/var/lib/mysql/mysql.sock1，ON，TRUE，启用意义相同 0，OFF，FALSE，关闭意义相同 _和-相同
- 登录并修改密码
[root@db01 ~]# mysql
mysql> alter user root@'localhost' identified by '1';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> flush privileges;
mysql> alter user root@'localhost' identified by '1';
Query OK, 0 rows affected (0.01 sec)
mysql> exit
如果 root@localhost 用户 (误删除\误修改) 不存在
误修改改回来就行
[root@db01 ~]# mysql mysql> select user,host from mysql.user; +------------------+-----------+ | user | host | +------------------+-----------+ | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | 10.0.0.% | +------------------+-----------+ 4 rows in set (0.00 sec) mysql> update mysql.user set host='localhost' where user='root' and host='10.0.0.%' mysql> exit误删除就只能重新建立, 不能使用 CREATE 创建语句, 只能使用 INSERT 插入语句直接编辑用户表
- 关闭数据库，正常启动验证
[root@db01 ~]# mysql -uroot -p1
四、连接管理
自带客户端命令
mysql
mysqldump
mysql 常用参数
-u USERNAME: 用户名; 默认为root
-h HOST: 服务器主机地址/主机名; 默认为localhost
-p PASSWORD： 用户密码; 默认为空
-P POST： 端口（大写字母）; 默认为3306
-s 静默
-S 套接字位置（指定连接socket文件路径）
-e 免交互执行SQL命令
< 导入数据库恢复数据
[root@db01 ~]# mysql -uroot -p123 -h 10.0.0.51 -P3306
mysql> select @@socket;
+-----------------+
| @@socket |
+-----------------+
| /tmp/mysql.sock |
[root@db01 ~]# mysql -uroot -p123 -S /tmp/mysql.sock
[root@db01 ~]# mysql -uroot -p123 -e "select user,host from mysql.user;"
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | 10.0.0.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
[root@db01 ~]# mysql -uroot -p123 < mysql.sql
[root@db01 ~]# mysql -uroot -p1 -e 'source /root/world.sql'
mysql 命令连接
mysql服务器监听两种socket地址：
- unix sock: 监听在sock文件上，仅支持本机通信，host为localhost,127.0.0.1时自动使用unix sock
- ip socket: 监听在tcp的3306端口，支持远程通信
① 本地(socket文件)连接
前提条件: 提前创建 localhost 用户和密码
mysql -uroot -p123 -S /tmp/mysql.sock
连接错误①：不能连接套接字文件
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2), it means that the MySQL server daemon (Unix) or service (Windows) is not running.解决方案：检查配置文件指定路径，检查MySQL是否启动
② 远程网络连接串(TCP/IP)连接
前提条件: 提前创建 远程连接(IP) 用户和密码
mysql -uroot -p123 -h10.0.0.51 -P3306
mysql 内置命令
help 打印mysql帮助
\c ctrl+c 结束上个命令运行
\q quit; exit; ctrl+d 退出mysql连接
\G 将数据竖起来显示
source 恢复备份文件
mysqld 帮助
# 获取mysqld的可用选项列表
mysqld --help --verbose
# 获取mysqld的默认设置
mysqld --print-defaults
开发工具
sqlyog
navicat
workbench(官方开源)
程序(驱动)连接
PHP
python
go
java
五、配置文件
初始化方式
- 源码包, 编译时配置参数(CMAKE)
- 命令行启动时指定配置参数
- 配置文件配置参数：类ini格式，集中式的配置
如果冲突,命令行优先级最高
配置文件默认读取路径
[root@db01 ~]# mysqld --help --verbose | grep my.cnf | head -1
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
注意:
默认情况下，MySQL启动时，从左到右依次读取以上配置文件，如果有重复选项，以最后一个文件设置的为准。
启动服务时, 强制指定读取那个配置文件
--defautls-file[root@db01 ~]# mysqld --defaults-file=/tmp/aa.txt & [root@db01 ~]# mysqld_safe --defaults-file=/tmp/aa.txt &
配置文件结构
[root@db01 ~]# cat >/etc/my.cnf << EOF
# 服务器端配置
[mysqld]
# 用户
user=mysql
# 软件安装目录
basedir=/application/mysql
# 数据存放路径
datadir=/data/mysql/data
# socket文件路径
socket=/tmp/mysql.sock
# 服务器id号
server_id=6
# 端口号
port=3306
# 客户端配置
[mysql]
# socket文件路径
socket=/tmp/mysql.sock
EOF
标签:
服务端: [mysqld] [mysqld_safe] [server] [mysqld_safe] [mysqld_multi](多实例)
影响: 数据库启动,初始化
客户端: [mysql] [mysqladmin] [mysqldump] [client]
影响: 只影响到本机客户端程序运行
格式：parameter = value
服务器端设置
SHOW GLOBAL VARIABLES;
SHOW [SESSION] VARIABLES;
SELECT @@VARIABLES;
服务器状态变量（只读）：用于保存mysqld运行中的统计数据的变量，不可更改
-- 全局
SHOW GLOBAL STATUS;
-- 会话
SHOW [SESSION] STATUS;
设置服务器变量的值：分全局和会话两种
help SET
设置全局变量：仅对修改后新创建的会话有效，对已经建立的会话无效，重启mysqld服务失效
SET GLOBAL system_var_name=value;
SET @@global.system_var_name=value;
设置会话变量：仅对当前建立的会话有效
SET [SESSION] system_var_name=value;
SET @@[session.]system_var_name=value;
设置服务器选项：重启永久生效
vim /etc/my.cnf
[mysqld]
...
六、启动关闭
以上多种方式，都可以单独启动MySQL服务
mysqld_safe和mysqld一般是在临时维护时使用。
从 CentOS 7 系统开始，支持systemd直接调用mysqld的方式进行启动数据库
启动
① /usr/local/mysql/bin/mysqld &
- 二进制文件
- 只有启动没有关闭, 不记录日志
② /usr/local/mysql/bin/mysqld_safe &
- shell脚本
- 只有启动没有关闭, 记录日志, 监控状态, 异常重启, ... ...
③ /usr/local/mysql/support-files/mysql.server {start|stop|restart|reload|force-reload|status}
④ service mysqld (start, stop, restart, try-restart, reload, force-reload, status)
⑤ systemctl mysqld {start|stop|restart|reload|enable|disable|status}
关闭
① /usr/local/mysql/bin/mysqladmin -uroot -p1 shutdown
②
[root@db01 ~]# /usr/local/mysql/bin/mysql -uroot -p1
mysql> shutdown;
# 8.0新特性
mysql> restart;
③ /usr/local/mysql/support-files/mysql.server stop
④ service mysqld stop
⑤ systemctl mysqld stop
七、多实例
MySQL5.7.12之后默认开启MySQL X插件, 监听33060端口, 多实例时必须关闭
查看
[root@db01 ~]# netstat -tupln | grep mysqld tcp6 0 0 :::3306 :::* LISTEN 2385/mysqld tcp6 0 0 :::33060 :::* LISTEN 2385/mysqld关闭
① 命令行启动使用参数:
--mysqlx=0或--skip-mysqlx② 配置文件
/etc/my.cnf的[mysqld]添加配置mysqlx=0
单版本多实例
- 规划相关目录
mkdir /mysql/330{7,8,9}/{data,etc,socket,log,pid} -pv
chown -R mysql.mysql /mysql
- 准备配置文件
cat > /mysql/3307/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/mysql/3307/data
socket=/mysql/3307/mysql.sock
port=3307
server_id=7
log_error=/mysql/3307/mysql.log
log_bin=/mysql/3307/mysql-bin
EOF
cat > /mysql/3308/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/mysql/3308/data
socket=/mysql/3308/mysql.sock
port=3308
server_id=8
log_error=/mysql/3308/mysql.log
log_bin=/mysql/3308/mysql-bin
EOF
cat > /mysql/3309/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/mysql/3309/data
socket=/mysql/3309/mysql.sock
port=3309
server_id=9
log_error=/mysql/3309/mysql.log
log_bin=/mysql/3309/mysql-bin
EOF
- 初始化三套数据并授权
mv /etc/my.cnf /etc/my.cnf.bak
mysqld --initialize-insecure --user=mysql --datadir=/mysql/3307/data --basedir=/usr/local/mysql
mysqld --initialize-insecure --user=mysql --datadir=/mysql/3308/data --basedir=/usr/local/mysql
mysqld --initialize-insecure --user=mysql --datadir=/mysql/3309/data --basedir=/usr/local/mysql
chown -R mysql.mysql /mysql
- systemd管理多实例
cat > /etc/systemd/system/mysqld3307.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=/mysql/3307/my.cnf --mysqlx=0
LimitNOFILE = 5000
EOF
cat > /etc/systemd/system/mysqld3308.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=/mysql/3308/my.cnf --mysqlx=0
LimitNOFILE = 5000
EOF
cat > /etc/systemd/system/mysqld3309.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=/mysql/3309/my.cnf --mysqlx=0
LimitNOFILE = 5000
EOF
- 启动
systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service
- 验证多实例
netstat -tlnp | grep 330
mysql -S /mysql/3307/mysql.sock -e "select @@server_id"
mysql -S /mysql/3308/mysql.sock -e "select @@server_id"
mysql -S /mysql/3309/mysql.sock -e "select @@server_id"
多版本多实例
- 解压二进制包和软链接
[root@db01 opt]# tar xf mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz
[root@db01 opt]# tar xf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
[root@db01 opt]# ln -s /opt/mysql-5.6.46-linux-glibc2.12-x86_64 /usr/local/mysql56
[root@db01 opt]# ln -s /opt/mysql-5.7.30-linux-glibc2.12-x86_64 /usr/local/mysql57
- 创建相关目录并授权
mkdir /mysql/33{16,17}/{data,etc,socket,log,pid} -pv
chown -R mysql.mysql /mysql
- 准备配置文件
cat > /mysql/3316/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql56
datadir=/mysql/3316/data
socket=/mysql/3316/mysql.sock
port=3316
server_id=16
log_error=/mysql/3316/mysql.log
log_bin=/mysql/3316/mysql-bin
EOF
cat > /mysql/3317/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql57
datadir=/mysql/3317/data
socket=/mysql/3317/mysql.sock
port=3317
server_id=17
log_error=/mysql/3317/mysql.log
log_bin=/mysql/3317/mysql-bin
EOF
- 初始化两套数据并授权
/usr/local/mysql56/scripts/mysql_install_db --user=mysql --datadir=/mysql/3316/data --basedir=/usr/local/mysql56
/usr/local/mysql57/bin/mysqld --initialize-insecure --user=mysql --datadir=/mysql/3317/data --basedir=/usr/local/mysql57
chown -R mysql.mysql /mysql
- systemd管理多实例并加载
cat > /etc/systemd/system/mysqld3316.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/mysql56/bin/mysqld --defaults-file=/mysql/3316/my.cnf
LimitNOFILE = 5000
EOF
cat > /etc/systemd/system/mysqld3317.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/mysql57/bin/mysqld --defaults-file=/mysql/3317/my.cnf
LimitNOFILE = 5000
EOF
systemctl daemon-reload
- 启动
systemctl start mysqld3316.service
systemctl start mysqld3317.service
- 验证多实例
netstat -tlnp | grep 331
mysql -S /mysql/3316/mysql.sock -e "select @@server_id"
mysql -S /mysql/3317/mysql.sock -e "select @@server_id"
八、升级
升级方式
-
inplace (就地升级)
适合于主从环境, 先升级从库, 没问题再升级主库
-
merging (逻辑备份迁移升级)
INPLACE 升级注意事项
官网说明
- 仅在一般可用性（GA）版本之间支持升级。
- 支持从MySQL 5.5升级到5.6。建议先升级到最新版本，然后再升级到下一版本。例如，在升级到MySQL 5.6之前，先升级到最新的MySQL 5.5版本。
- 不支持跳过版本的升级。例如，不支持直接从MySQL 5.1升级到5.6。
- 支持在发行系列中进行升级。例如，支持从MySQL 5.6.x 升级至5.6.y。还支持跳过发行版。例如，从MySQL 5.6.x升级至5.6.z支持。
- 仅在一般可用性（GA）版本之间支持升级。
- 支持从MySQL 5.6升级到5.7。建议先升级到最新版本，然后再升级到下一版本。例如，在升级到MySQL 5.7之前，先升级到最新的MySQL 5.6版本。
- 不支持跳过版本的升级。例如，不支持从MySQL 5.5直接升级到5.7。
- 支持在发行系列中进行升级。例如，支持从MySQL 5.7.x升级至5.7.y。还支持跳过发行版。例如，从MySQL 5.7.x升级至5.7.z支持。
- 支持从MySQL 5.7升级到8.0。但是，仅在一般可用性（GA）版本之间支持升级。对于MySQL 8.0，要求您从MySQL 5.7 GA版本（5.7.9或更高版本）升级。不支持从非GA版本的MySQL 5.7升级。
- 建议先升级到最新版本，然后再升级到下一版本。例如，在升级到MySQL 8.0之前，先升级到最新的MySQL 5.7版本。
- 不支持跳过版本的升级。例如，不支持直接从MySQL 5.6升级到8.0。
- 某个发行系列达到通用（GA）状态后，便支持在该发行系列中进行升级（从一个GA版本升级到另一个GA版本）。例如，支持从MySQL 8.0.x升级到8.0.y。（不支持涉及开发状态非GA版本的升级。）还支持跳过版本。例如，支持从MySQL 8.0.x升级到8.0.z。MySQL 8.0.11是MySQL 8.0发行系列中的第一个GA状态发行版。
总结
-
仅支持GA版本之间升级，不支持跳过版本的升级，支持从MySQL 5.7升级到8.0
-
5.6 --> 5.7 ,先将5.6升级至最新版，再升级到5.7
-
5.5 --> 5.7 ,先将5.5升级至最新，再 5.5 --> 5.6 最新，再 5.6 --> 5.7 最新
-
回退方案要提前考虑好，最好升级前要备份(特别是往8.0版本升级)。
-
降低停机时间（停业务的时间）
INPLACE 升级过程原理
- 备份原数据库数据
- 安装新版本软件
- 关闭原数据库（网站挂维护页）
- 使用新版本软件 “挂” 旧版本数据启动(
--skip-grant-tables,--skip-networking) - 升级（只是升级系统表，升级时间和数据量无关）
- 正常重启数据库
- 验证各项功能是否正常
- 恢复业务
INPLACE 升级演练 5.6.46 ---> 5.7.30
-
安装 新(5.7.30)版本软件
-
优雅关闭库(等待所有正在进行的事务运行完毕再关库)
mysql -S /mysql/3316/mysql.sock -e "set global innodb_fast_shutdown=0;"
mysql -S /mysql/3316/mysql.sock -e "select @@innodb_fast_shutdown;"
+------------------------+
| @@innodb_fast_shutdown |
+------------------------+
| 0 |
+------------------------+
mysql -S /mysql/3316/mysql.sock -e "shutdown;"
- 冷备
cp -r /mysql/3316/data/ /tmp/3316bak
- 修改配置文件, 使用高版本（5.7.30）软件挂低版本（5.6.48）数据启动
cat > /mysql/3316/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql57
datadir=/mysql/3316/data
socket=/mysql/3316/mysql.sock
port=3316
server_id=16
log_error=/mysql/3316/mysql.log
log_bin=/mysql/3316/mysql-bin
EOF
/usr/local/mysql57/bin/mysqld_safe --defaults-file=/mysql/3316/my.cnf --skip-grant-tables --skip-networking &
- 升级（升级到8.0可以省略此步骤）
/usr/local/mysql57/bin/mysql_upgrade -S /mysql/3316/mysql.sock --force
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Upgrading the sys schema.
Checking databases.
sys.sys_config OK
Upgrade process completed successfully.
Checking if update is needed.
- 修改并重载systemd管理配置文件
cat > /etc/systemd/system/mysqld3316.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/mysql57/bin/mysqld --defaults-file=/mysql/3316/my.cnf
LimitNOFILE = 5000
EOF
systemctl daemon-reload
- 关闭数据库并正常启动
/usr/local/mysql57/bin/mysqladmin -S /mysql/3316/mysql.sock shutdown
systemctl start mysqld3316
- 验证
mysql -S /mysql/3316/mysql.sock -e "select user,host from mysql.user;"
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| | db01 |
| root | db01 |
| | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
mysql -S /mysql/3316/mysql.sock -e "SELECT @@VERSION;"
+------------+
| @@VERSION |
+------------+
| 5.7.30-log |
+------------+
INPLACE 升级演练 5.7.30 ---> 8.0.20
-
安装 新(8.0.20)版本软件
-
优雅关闭库(等待所有正在进行的事务运行完毕再关库)
mysql -S /mysql/3317/mysql.sock -e "set global innodb_fast_shutdown=0;"
mysql -S /mysql/3317/mysql.sock -e "select @@innodb_fast_shutdown;"
+------------------------+
| @@innodb_fast_shutdown |
+------------------------+
| 0 |
+------------------------+
mysql -S /mysql/3317/mysql.sock -e "shutdown;"
- 冷备
cp -r /mysql/3317/data/ /tmp/3317bak
- 修改配置文件, 使用高版本（8.0.20）软件挂低版本（5.7.30）数据启动
cat > /mysql/3317/my.cnf << EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/mysql/3317/data
socket=/mysql/3317/mysql.sock
port=3317
server_id=17
log_error=/mysql/3317/mysql.log
log_bin=/mysql/3317/mysql-bin
mysqlx=0
EOF
/usr/local/mysql/bin/mysqld_safe --defaults-file=/mysql/3317/my.cnf --skip-grant-tables --skip-networking &
- 修改并重载systemd管理配置文件
cat > /etc/systemd/system/mysqld3317.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=/mysql/3317/my.cnf
LimitNOFILE = 5000
EOF
systemctl daemon-reload
- 关闭数据库并正常启动
/usr/local/mysql/bin/mysqladmin -S /mysql/3317/mysql.sock shutdown
systemctl start mysqld3317
- 验证
mysql -S /mysql/3317/mysql.sock -e "select user,host from mysql.user;"
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
mysql -S /mysql/3317/mysql.sock -e "SELECT @@VERSION;"
+-----------+
| @@VERSION |
+-----------+
| 8.0.20 |
+-----------+
升级前预检查
- mysqlsh ( 最低8.0.20 ) 安装
[root@db01 opt]# tar xf mysql-shell-8.0.20-linux-glibc2.12-x86-64bit.tar.gz
[root@db01 opt]# ln -s /opt/mysql-shell-8.0.20-linux-glibc2.12-x86-64bit /usr/local/mysqlsh
[root@db01 opt]# echo export PATH=\$PATH:/usr/local/mysqlsh/bin/ >> /etc/profile && . /etc/profile
- 验证安装
[root@db01 opt]# mysqlsh --version
mysqlsh Ver 8.0.20 for Linux on x86_64 - for MySQL 8.0.20 (MySQL Community Server (GPL))
- 在 5.7.30 数据库中创建远程用户
mysql -S /tmp/mysql3317.sock -e "create user root@'10.0.0.%' identified with mysql_native_password by '123';"
mysql -S /tmp/mysql3317.sock -e "grant all on *.* to root@'10.0.0.%';"
- 升级前检测
mysqlsh root:123@10.0.0.51:3317 -e "util.checkForServerUpgrade()" > /tmp/upcheck.log
