mysql：基础管理、体系结构、升级降级

2022-11-30 17:48:55

基础管理
1.1 用户管理
1.2 权限管理
1.3 连接管理

1.4 初始化配置文件
1.4.1 作用
<1. 影响到数据库的启动、日常工作。
<2. 影响到客户端连接（本地服务器上发起的）。

1.4.2 文件格式

[root@db01 ~]# cat /etc/my.cnf 
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock

[标签项] ：用来表示不同的程序
服务端： [mysqld] [mysqld_safe] [server]
客户端: [mysql] [mysqldump] [client]
配置=xxx ：设置的参数键值对
user=mysql # 数据库管理用户
basedir=/data/app/mysql # 程序路径
datadir=/data/3306/data # 数据路径
socket=/tmp/mysql.sock # socket文件位置

1.4.3 配置文件读取顺序

默认配置文件读取顺序

[root@db01 ~]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf ---》 /etc/mysql/my.cnf ---》 /usr/local/mysql/etc/my.cnf ----》 ~/.my.cnf 
建议： 每个数据库保留一个配置文件。
彩蛋： 
	如果有多个配置文件，例如 
		/etc/my.cnf  ---> port=3306
		~/.my.cnf    ---> port=3308
	数据库启动时，port是多少？

手工定义配置文件读取路径

mysqld  --defaults-file=/opt/my1.cnf  &
mysqld_safe --defaults-file=/opt/my2.cnf  &

1.4.4 root本地管理员的密码忘记？
<1. 停数据库

[root@db01 tmp]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS! 
[root@db01 tmp]#

<2. 启动数据库到“安全”模式

mysqld_safe --skip-grant-tables --skip-networking &

参数作用：

	 --skip-grant-tables ： 跳过授权表，不开启验证功能。
	 --skip-networking   :  阻止所有TCP/IP网络连接。

❤️. 改密码

mysql> flush privileges;
mysql> alter user root@'localhost' identified by '123456';

<4. 重启至正常模式

[root@db01 tmp]# /etc/init.d/mysqld restart

1.6 多实例应用
1.6.1 创建相关目录

[root@db01 ~]# mkdir -p /data/330{7..9}/data  /data/330{7..9}/logs

1.6.2 准备配置文件

cat >/data/3307/my.cnf <<EOF
[mysqld] 
user=mysql 
basedir=/data/app/mysql
datadir=/data/3307/data 
server_id=7
port=3307
log_bin=/data/3307/logs/mysql-bin
socket=/tmp/mysql3307.sock
EOF

cat >/data/3308/my.cnf <<EOF
[mysqld] 
user=mysql 
basedir=/data/app/mysql
datadir=/data/3308/data 
server_id=8
port=3308
log_bin=/data/3308/logs/mysql-bin
socket=/tmp/mysql3308.sock
EOF


cat >/data/3309/my.cnf <<EOF
[mysqld] 
user=mysql 
basedir=/data/app/mysql
datadir=/data/3309/data 
server_id=9
port=3309
log_bin=/data/3309/logs/mysql-bin
socket=/tmp/mysql3309.sock
EOF

1.6.3 授权

[root@db01 ~]# chown -R mysql. /data/*

1.6.4 初始化数据

mv /etc/my.cnf /etc/my.cnf.bak

mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3307/data 
mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3308/data 
mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3309/data 

mv /etc/my.cnf.bak  /etc/my.cnf

1.6.5 启动多实例

[root@db01 ~]# mysqld_safe --defaults-file=/data/3307/my.cnf &
[root@db01 ~]# mysqld_safe --defaults-file=/data/3308/my.cnf &
[root@db01 ~]# mysqld_safe --defaults-file=/data/3309/my.cnf &

1.6.7 使用navicat连接多实例

mysql -S /tmp/mysql3307.sock 
grant all on *.* to root@'10.0.0.%' identified by '123';
mysql -S /tmp/mysql3308.sock -e "grant all on *.* to root@'10.0.0.%' identified by '123';"
mysql -S /tmp/mysql3309.sock -e "grant all on *.* to root@'10.0.0.%' identified by '123';"

1.6.8 配置多实例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=/data/app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
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=/data/app/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
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=/data/app/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF

[root@db01 ~]# pkill mysqld
[root@db01 ~]# systemctl start mysqld3307
[root@db01 ~]# systemctl start mysqld3308
[root@db01 ~]# systemctl start mysqld3309

1.7 多版本多实例应用
真实案例：第一周工作

1.7.1 上传软件、解压、软连接

tar xf mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz 
tar xf mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz 

ln -s mysql-8.0.18-linux-glibc2.12-x86_64 mysql80
ln -s mysql-5.6.46-linux-glibc2.12-x86_64 mysql56

1.7.2 创建相关目录

mkdir -p /data/331{7..8}/data  /data/331{7..8}/logs

1.7.3 准备配置文件

cat >/data/3317/my.cnf <<EOF
[mysqld] 
user=mysql 
basedir=/data/app/mysql56
datadir=/data/3317/data 
server_id=17
port=3317
log_bin=/data/3317/logs/mysql-bin
socket=/tmp/mysql3317.sock
EOF

cat >/data/3318/my.cnf <<EOF
[mysqld] 
user=mysql 
basedir=/data/app/mysql80
datadir=/data/3318/data 
server_id=18
port=3318
log_bin=/data/3318/logs/mysql-bin
socket=/tmp/mysql3318.sock
EOF

1.7.3 授权

[root@db01 ~]# chown -R mysql. /data/*

1.7.4 初始化数据

mv /etc/my.cnf /etc/my.cnf.bak

vim /etc/profile

#注释环境变量：
export PATH=/data/app/mysql/bin:$PATH

最好重连一个Xshell窗口

[root@db01 ~]# mysql -V
-bash: mysql: command not found
[root@db01 ~]#

5.6初始化

/data/app/mysql56/scripts/mysql_install_db  --user=mysql --basedir=/data/app/mysql56 --datadir=/data/3317/data

8.0初始化

/data/app/mysql80/bin/mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql80 --datadir=/data/3318/data

1.7.5 配置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=/data/app/mysql56/bin/mysqld --defaults-file=/data/3317/my.cnf
LimitNOFILE = 5000
EOF


cat > /etc/systemd/system/mysqld3318.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=/data/app/mysql80/bin/mysqld --defaults-file=/data/3318/my.cnf
LimitNOFILE = 5000
EOF

1.7.6 连接测试

[root@db01 app]# /data/app/mysql56/bin/mysql -S /tmp/mysql3317.sock 
[root@db01 app]# /data/app/mysql80/bin/mysql -S /tmp/mysql3318.sock

彩蛋： MySQL 的升级、降级
2.1 升级
2.1.1 INPLACE就地
在一台服务器上，原版本升级到新版本。
风险较大。

**** 建议：不管哪种方式升级，都应该先做备份。方便失败回退。****

2.1.2 Mergeing(logical)迁移 ----> 建议
备份迁移
主从迁移

2.1.3 升级注意事项

Upgrade is only supported between General Availability (GA) releases.
Upgrade from MySQL 5.6 to 5.7 is supported. Upgrading to the latest release is recommended before upgrading to the next version. For example, upgrade to the latest MySQL 5.6 release before upgrading to MySQL 5.7.
Upgrade that skips versions is not supported. For example, upgrading directly from MySQL 5.5 to 5.7 is not supported.
Upgrade within a release series is supported. For example, upgrading from MySQL 5.7.x to 5.7.y is supported. Skipping a release is also supported. For example, upgrading from MySQL 5.7.x to 5.7.z is supported.

a. 支持GA版本之间升级
b. 5.6--> 5.7 ,先将5.6升级至最新版，再升级到5.7
c. 5.5 ---> 5.7 ,先将5.5 升级至最新，再5.5---> 5.6最新，再5.6--->5.7 最新
d. 回退方案要提前考虑好，最好升级前要备份(特别是往8.0版本升级)。
e. 降低停机时间（停业务的时间）,在业务不繁忙期间升级，做好足够的预演。

2.1.4 INPLACE 升级过程原理（生产思路）

备份原数据库数据
a. 安装新版本软件
b. 关闭原数据库业务（挂维护页）
c. 使用新版本软件 “挂” 旧版本数据启动(--skip-grant-tables ,--skip-networking)
d. 升级：只是升级系统表。升级时间和数据量无关的。
e. 正常重启数据库。
f. 验证各项功能是否正常。
g. 业务恢复。

2.1.5 5.6.46 ----> 5.7.28 Inplace 升级演练
a. 安装新版本软件
略。

b. 停原库（5.6.46）

1. 快速关库功能关闭(优雅关闭、干净的关闭)

vim /data/3317/my.cnf

添加以下配置

innodb_fast_shutdown=0 
[root@db01 data]# systemctl stop mysqld3317

c. 使用高版本软件挂低版本数据启动

[root@db01 data]# vim /data/3317/my.cnf 
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3317/data
socket=/tmp/mysql3317.sock
port=3317
server_id=17
innodb_fast_shutdown=0

[root@db01 data]# /data/app/mysql/bin/mysqld_safe --defaults-file=/data/3317/my.cnf --skip-grant-tables --skip-networking &

d. 升级（升级到8.0可以省略）

[root@db01 data]# /data/app/mysql/bin/mysql_upgrade -S /tmp/mysql3317.sock --force

e. 重启数据库到正常状态

[root@db01 data]# /data/app/mysql/bin/mysqladmin -S /tmp/mysql3317.sock shutdown

[root@db01 data]# vim /etc/systemd/system/mysqld3317.service 

修改以下内容 

ExecStart=/data/app/mysql/bin/mysqld --defaults-file=/data/3317/my.cnf

启动数据库

[root@db01 data]# systemctl start mysqld3317 

连接查看

[root@db01 data]# /data/app/mysql/bin/mysql -S /tmp/mysql3317.sock

2.1.6 将5.7 升级至 8.0

新特性：

1、 mysql-shell工具，8.0以后，可以调用这个命令，升级之前的预检查。
例子：
[root@db01 ~]# mysqlsh root:123@10.0.0.51:3306 -e "util.checkForServerUpgrade()"
2、升级时不再需要手工 mysql_upgrade
3、限制：升级前必须要备份。否则无法回退。

5.7.28 升级至 8.0.18 版本演练

a. 下载 8.0.18 版本的 mysql-shell，并安装。

https://downloads.mysql.com/archives/
[root@db01 app]# yum install -y mysql-shell-8.0.18-1.el7.x86_64.rpm

b. 创建连接用户

[root@db01 data]# /data/app/mysql/bin/mysql -S /tmp/mysql3317.sock
mysql> grant all on *.* to root@'10.0.0.%' identified by '123';
mysql> drop user root@'127.0.0.1';
mysql> drop user root@'db01';
mysql> drop user ''@'db01';
mysql> drop user ''@'localhost';
mysql> drop user root@'::1';
mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | 10.0.0.%  |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+

c. 预检查

[root@db01 data]# mysqlsh root:123@10.0.0.51:3317 -e "util.checkForServerUpgrade()" >/tmp/up.log

d. 停原库（5.7.28）

vim /data/3317/my.cnf 

添加以下配置

innodb_fast_shutdown=0 
[root@db01 data]# systemctl stop mysqld3317

e. 使用高版本软件挂低版本数据启动

[root@db01 data]# vim /data/3317/my.cnf 
[mysqld]
user=mysql
basedir=/data/app/mysql80
datadir=/data/3317/data
socket=/tmp/mysql3317.sock
port=3317
server_id=17
innodb_fast_shutdown=0

[root@db01 data]# /data/app/mysql80/bin/mysqld_safe --defaults-file=/data/3317/my.cnf --skip-grant-tables --skip-networking &

f. 重启数据库到正常状态

[root@db01 data]# /data/app/mysql80/bin/mysqladmin -S /tmp/mysql3317.sock shutdown

[root@db01 data]# vim /etc/systemd/system/mysqld3317.service 
修改以下内容 

ExecStart=/data/app/mysql80/bin/mysqld --defaults-file=/data/3317/my.cnf

启动数据库

[root@db01 data]# systemctl start mysqld3317 

连接查看

[root@db01 data]# /data/app/mysql80/bin/mysql -S /tmp/mysql3317.sock

降级：

3. 3.1 限制
   官方解释： 
   https://dev.mysql.com/doc/refman/5.7/en/downgrade-paths.html
   Downgrade from MySQL 5.7 to 5.6 is supported using the logical downgrade method.

https://dev.mysql.com/doc/refman/5.7/en/downgrade-binary-package.html#downgrade-procedure-inplace
In-place downgrade is supported for downgrades between GA releases within the same release series(5.7.y  ---> 5.7.x).

3.2 5.7.28 ---》 5.7.10 inplace downgrade演练

原版本：
软件： 5.7.28 /data/app/mysql + 数据：/data/3306/data
目标版本： 5.7.10 /data/app/mysql5710

3.2.1. 安装 5.7.10 （低）二进制版本

[root@db01 app]# ln -s mysql-5.7.10-linux-glibc2.5-x86_64 mysql5710

3.2.2. 针对5728版本（高）进行处理工作

https://dev.mysql.com/doc/refman/5.7/en/downgrading-to-previous-series.html

[root@db01 app]# cp /etc/my.cnf.bak /etc/my.cnf
[root@db01 app]# /etc/init.d/mysqld restart
[root@db01 app]# /data/app/mysql/bin/mysql -uroot -p123456 -S /tmp/mysql.sock

set sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ;
set global sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ;
select @@sql_mode;                              
ALTER TABLE mysql.proc MODIFY definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.event MODIFY definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.tables_priv MODIFY Grantor char(77) COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.procs_priv MODIFY Grantor char(77) COLLATE utf8_bin NOT NULL DEFAULT '';

3.2.3. 优雅的关闭5.7.28（高）。

[root@db01 app]# /data/app/mysql/bin/mysql -uroot -p123456 -S /tmp/mysql.sock
set global innodb_fast_shutdown=0 ;

[root@db01 app]# /data/app/mysql/bin/mysqladmin  -uroot -p123456 shutdown

3.2.4. 删除ib_logfile*

[root@db01 mysql5710]# rm -rf /data/3306/data/ib_logfile*

3.2.5. 替换配置文件（替换成低版本）

[root@db01 mysql5710]# vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql5710
#basedir=/data/app/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock

3.2.6. 低版本启动高版本数据库

/data/app/mysql5710/bin/mysqld  --skip-grant-tables --skip-networking &

3.2.7. 执行upgrade

[root@db01 ~]# /data/app/mysql5710/bin/mysql_upgrade -uroot -p123456 --force

3.2.8 启动到正常模式

[root@db01 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
#连接
/data/app/mysql5710/bin/mysql -uroot -p123456 -S /tmp/mysql.sock

3.3 5.7.28 ---》 5.6.46 logical downgrade演练
3.3.0 恢复5.7.28 环境
[root@db01 data]# pkill mysqld
[root@db01 data]# rm -rf /data/3306/data/*

恢复配置文件

[root@db01 data]# vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock

恢复环境变量

vim /etc/profile 
export PATH=/data/app/mysql/bin:$PATH
[root@db01 data]# source /etc/profile

[root@db01 data]# mysql -V
mysql  Ver 14.14 Distrib 5.7.28, for linux-glibc2.12 (x86_64) using  EditLine wrapper

初始化数据

mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3306/data

启动数据库

[root@db01 data]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/3306/data/db01.err'.
 SUCCESS! 

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.28    |
+-----------+
1 row in set (0.00 sec)

3.3.1 安装5.6.46二进制版本软件
略。

3.3.2 处理5.7.28高版本数据

set sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ;
set global sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ;
select @@sql_mode;   
ALTER TABLE mysql.proc MODIFY definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.event MODIFY definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.tables_priv MODIFY Grantor char(77) COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.procs_priv MODIFY Grantor char(77) COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.tables_priv MODIFY User char(16) NOT NULL default '';
ALTER TABLE mysql.columns_priv MODIFY User char(16) NOT NULL default '';
ALTER TABLE mysql.user MODIFY User char(16) NOT NULL default '';
ALTER TABLE mysql.db MODIFY User char(16) NOT NULL default '';
ALTER TABLE mysql.procs_priv MODIFY User char(16) binary DEFAULT '' NOT NULL;
ALTER TABLE mysql.user ADD Password char(41) character set latin1
collate latin1_bin NOT NULL default '' AFTER user;
UPDATE mysql.user SET password = authentication_string WHERE
LENGTH(authentication_string) = 41 AND plugin = 'mysql_native_password';
UPDATE mysql.user SET authentication_string = '' WHERE
LENGTH(authentication_string) = 41 AND plugin = 'mysql_native_password';
ALTER TABLE mysql.help_category ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.help_keyword ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.help_relation ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.help_topic ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.time_zone ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.time_zone_leap_second ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.time_zone_name ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.time_zone_transition  ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.time_zone_transition_type ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.plugin ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.servers ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.user MODIFY plugin CHAR(64) COLLATE utf8_bin
DEFAULT 'mysql_native_password';
DROP DATABASE sys;

3.3.3. 逻辑全备5.7.28数据

[root@db01 ~]# mysqldump -A >/tmp/full.sql

3.3.4. 初始化一套5.6.46的空环境

[root@db01 ~]# vim /etc/profile
export PATH=/data/app/mysql56/bin:$PATH

[root@db01 ~]# source /etc/profile
[root@db01 ~]# mysql -V
mysql  Ver 14.14 Distrib 5.6.46, for linux-glibc2.12 (x86_64) using  EditLine wrapper

[root@db01 ~]# mv /etc/my.cnf /etc/my.cnf.bak
mv: overwrite ‘/etc/my.cnf.bak’? y

[root@db01 data]# rm -rf /data/3317/data/*

[root@db01 data]# /data/app/mysql56/scripts/mysql_install_db  --user=mysql --basedir=/data/app/mysql56 --datadir=/data/3317/data
[root@db01 data]# vim /etc/systemd/system/mysqld3317.service 
ExecStart=/data/app/mysql56/bin/mysqld --defaults-file=/data/3317/my.cnf

[root@db01 data]# systemctl daemon-reload
[root@db01 data]# systemctl start mysqld3317

3.3.5. 恢复备份数据到5.6.46中

[root@db01 data]# mysql -S /tmp/mysql3317.sock 
mysql> source /tmp/full.sql

码农公寓