MySQL管理篇
mysql管理篇
1.主机环境
1.1 操作系统
| 操作系统 | IP | CPU | MEM |
|---|---|---|---|
| CentOS Linux release 7.9.2009 (Core) | 172.16.1.7 | 4 | 8G |
disk：
[root@localhost ~]# df -hT
Filesystem Type Size Used Avail Use% Mounted on
devtmpfs devtmpfs 3.8G 0 3.8G 0% /dev
tmpfs tmpfs 3.9G 0 3.9G 0% /dev/shm
tmpfs tmpfs 3.9G 12M 3.8G 1% /run
tmpfs tmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup
/dev/sda3 xfs 11G 1.3G 9.8G 12% /
/dev/sda1 xfs 1014M 145M 870M 15% /boot
tmpfs tmpfs 781M 0 781M 0% /run/user/0
1.2 数据盘规划
CentOS6使用ext4，CentOS7推荐使用XFS文件系统，做好规划，磁盘不推荐使用LVM管理。MySQL数据分区独立，例如挂载点为：/data
添加一块硬盘，这里是/dev/sdb:
[root@localhost ~]# fdisk -l
Disk /dev/sdb: 53.7 GB, 53687091200 bytes, 104857600 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 /dev/sda: 21.5 GB, 21474836480 bytes, 41943040 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: 0x000bca2e
Device Boot Start End Blocks Id System
/dev/sda1 * 2048 2099199 1048576 83 Linux
/dev/sda2 2099200 18876415 8388608 82 Linux swap / Solaris
/dev/sda3 18876416 41943039 11533312 83 Linux
格式化为XFS文件系统，并挂载到/data目录：
[root@localhost ~]# mkfs.xfs /dev/sdb
meta-data=/dev/sdb isize=512 agcount=4, agsize=3276800 blks
= sectsz=512 attr=2, projid32bit=1
= crc=1 finobt=0, sparse=0
data = bsize=4096 blocks=13107200, imaxpct=25
= sunit=0 swidth=0 blks
naming =version 2 bsize=4096 ascii-ci=0 ftype=1
log =internal log bsize=4096 blocks=6400, version=2
= sectsz=512 sunit=0 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0
[root@localhost ~]# mkdir /data
[root@localhost ~]# mount /dev/sdb /data
[root@localhost ~]# df -hT
Filesystem Type Size Used Avail Use% Mounted on
devtmpfs devtmpfs 3.8G 0 3.8G 0% /dev
tmpfs tmpfs 3.9G 0 3.9G 0% /dev/shm
tmpfs tmpfs 3.9G 12M 3.8G 1% /run
tmpfs tmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup
/dev/sda3 xfs 11G 1.3G 9.8G 12% /
/dev/sda1 xfs 1014M 145M 870M 15% /boot
tmpfs tmpfs 781M 0 781M 0% /run/user/0
/dev/sdb xfs 50G 33M 50G 1% /data
配置开机自动挂载：
# 查看磁盘UUID
[root@localhost ~]# blkid
/dev/sdb: UUID="94e9a422-37df-4304-bf4a-3cc4a6650495" TYPE="xfs"
/dev/sda1: UUID="c031936e-1541-44d9-a9a8-497a61405fc0" TYPE="xfs"
/dev/sda2: UUID="a4e0c64d-ba47-4b5c-964d-4edfcf255969" TYPE="swap"
/dev/sda3: UUID="cb3cf63b-99d2-4a9a-86d9-8ce764015d75" TYPE="xfs"
/dev/sr0: UUID="2022-07-26-18-09-05-00" LABEL="CentOS 7 x86_64" TYPE="iso9660"
# 把磁盘/dev/sdb挂载信息写入/etc/fstab,UUID要换成自己对应的磁盘UUID，这里是
# UUID=94e9a422-37df-4304-bf4a-3cc4a6650495 /data xfs defaults,noatime,nodiratime,nobarrier 1 2
[root@localhost ~]# cat /etc/fstab
#
# /etc/fstab
# Created by anaconda on Wed Sep 27 06:39:33 2023
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
UUID=cb3cf63b-99d2-4a9a-86d9-8ce764015d75 / xfs defaults 0 0
UUID=c031936e-1541-44d9-a9a8-497a61405fc0 /boot xfs defaults 0 0
UUID=a4e0c64d-ba47-4b5c-964d-4edfcf255969 swap swap defaults 0 0
UUID=94e9a422-37df-4304-bf4a-3cc4a6650495 /data xfs defaults,noatime,nodiratime,nobarrier 1 2
2. MySQL部署
2.1linux
2.1.1 软件获取
官网：https://www.mysql.com
下载地址：https://downloads.mysql.com/archives/community/
下滑找到社区版：
2.2 软件校验
软件校验一般官网下载都没有问题，可以不做，但是有时候软件包从其它地方下载之后可能异样需要校验一下。
[root@localhost ~]# md5sum mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz
0bdd171cb8464ba32f65f7bf58bc9533 mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz
2.3 MySQL 8.0.x安装
2.3.1 安装
添加用户，用户组
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
清理遗留环境
rpm -qa|grep 'mariadb'
yum -y remove mariadb-libs
rm -rf /etc/my.cnf
解压并创建软连接：
tar -xvf mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz -C /opt/
ln -s /opt/mysql-8.0.27-linux-glibc2.12-x86_64 /opt/mysql
配置环境变量
cat >/etc/profile.d/mysql.sh<<'EOF'
#!/bin/bash
export MYSQL_HOME=/opt/mysql
export PATH=$PATH:$MYSQL_HOME/bin
EOF
source /etc/profile.d/mysql.sh
测试：
[root@localhost opt]# mysql -V
mysql Ver 8.0.27 for Linux on x86_64 (MySQL Community Server - GPL)
如果是Ubuntu操作系统可能会报：
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
安装依赖即可
apt install libncurses*
创建数据目录，并授权：
mkdir -p /data/3306/data
chown -R mysql.mysql /data
准本基础的配置文件：
cat >/etc/my.cnf<<'EOF'
[mysqld]
user=mysql
basedir=/opt/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=6
[mysql]
socket=/tmp/mysql.sock
EOF
初始化数据(建库)：mysqld --initialize-insecure --user=mysql --basedir=/opt/mysql --datadir=/data/3306/data
# 报错
[root@localhost opt]# mysqld --initialize-insecure
mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
#这里缺少依赖，需要安装libaio
[root@localhost opt]# yum -y install libaio-devel
# 再初始化
[root@localhost opt]# mysqld --initialize-insecure
2023-09-27T05:49:33.266145Z 0 [System] [MY-013169] [Server] /opt/mysql-8.0.27-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.27) initializing of server in progress as process 1702
2023-09-27T05:49:33.274258Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-09-27T05:49:34.024526Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-09-27T05:49:34.802972Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2023-09-27T05:49:34.802991Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2023-09-27T05:49:34.846188Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
如果有报错失败的话可以删除配置文件中“datadir”中定义的路径下的数据，然后再重新初始化。这仅仅适用于新环境，生产中不要乱删。
以上的初始化方式不会生成密码，如果需要密码，使用如下初始化命令，他会生成随机密码，而且复杂度高:
mysqld --initialize
这个密码必须要登录之后马上改，不然无法管理数据库，所以建议使用第一种。
拓展：
5.6版本初始化命令：mysql_install_db --user=mysql --basedir=/data/app/mysql56 --datadir=/data/3306/data
/opt/mysql56/scripts/mysql_install_db --user=mysql --basedir=/opt/mysql56 --datadir=/data/3306/data
2.3.2 启动
2.3.2.1 Sys-V方式
复制启动文件到管理路径：
cp /opt/mysql/support-files/mysql.server /etc/init.d/mysqld
管理：
# 启动
/etc/init.d/mysqld start
# 停止
/etc/init.d/mysqld stop
#重启
/etc/init.d/mysqld restart
#也可以使用以下方式
service mysqld start
service mysqld stop
service mysqld restart
2.3.2.2 systemd方式
# 先停止，不然接管不上
service mysqld stop
# 使用开机自启动，这里他会自动生成systemd相关配置文件
systemctl enable mysqld
然后就可以使用systemd方式管理了
systemctl start mysqld
systemctl status mysqld
systemctl stop mysqld
2.2 windows
2.2.1 安装Viusal Studio
windows安装MySQL需要先安装Viusal Studio，否则无法安装。
下载地址：https://visualstudio.microsoft.com/zh-hans/vs/older-downloads/
下滑找到其它工具、框架和可再发组件并打开，选择需要的版本，我这里选2017版：
安装的时候
2.2.2 软件获取
2..2.3 开始安装
解压软件到自己想要安装的路径。
在目录下新建my.ini文件，别写入配置：
[mysqld]
port=3306
basedir=C:/services/mysql
datadir=C:/services/mysql/date
character-set-server=utf8
default-storage-engine=InnODB
[mysql]
port=3306
default-character-set=utf8
当前路径进入cmd，然后再进入bin目录,之后安装步骤基本都和linux上是一样的，使用mysqld -install [服务名]注册成系统服务即可。
2.2.4 配置环境变量
大概资源管理器(win+E)-->右键此电脑-->属性-->高级系统设置-->环境变量
一路确认保存退出测试：
3.mysql的管理
3.1登录
使用“mysql -uroot -p”连接，密码为空：
[root@localhost data]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
3.2 mysql的用户管理
3.2.1 用户的功能
·登录数据库
·管理数据库对象
3.2.2 用户的组成
用户名@'白名单'
白名单？
地址列表：
% 任意地址都可以连接
10.0.0.10 只有该地址可以连接
10.0.0.% 该网段的任意地址都可以连接
10.0.0.5% 10.0.0.50~10.0.0.59的地址可以连接
10.0.0.0/255.255.254.0 指定的网段内的所有地址都可以连接
guojie.com 也可以写域名
127.0.0.1 只允许本地登录
localhost 只允许本地登录
3.2.3 用户的管理
3.2.3.1 查看用户表
select user,host,authentication_string,plugin from mysql.user;
命令太长记不住字段名的话可以先使用“desc mysql.user”查看表结构
3.2.3.2 创建用户
create user guojie@'127.0.0.1' identified by '123';
默认的密码插件是caching_sha2_password，这个加密是不可逆的，比较安全，但是有时候开发的应用程序还比较老，还无法识别，这时候就需要用老版本的mysql_native_password。
create user guojie@'127.0.0.1' identified with mysql_native_password by '123';
3.2.3.3 修改密码
alter user guojie@'127.0.0.1' identified by '123456';
3.2.3.4 修改白名单
update mysql.user set host='localhost' where user='guojie';
3.2.3.5 用户的锁与解锁
部分时候直接删除用户有风险，可以先给其锁上，后期没有问题再删除
alter user guojie@'127.0.0.1' account lock;
查看，字段account_locked为Y的就是锁上的：
select user,host,authentication_string,plugin,account_locked from mysql.user;
解锁：
alter user guojie@'127.0.0.1' account unlock;
3.2.3.6 删除用户
drop user test@'127.0.0.1';
注意：root用户不要删除,也不建议开启远程。
3.2.3.7 刷新权限
做完修改之后要刷新授权表让它生效
flush privileges;
3.3 密码遗失
步骤一、先停止数据库服务
systemctl stop mysqld
步骤二、以安全模式启动数据库，执行之后回车会推出打印
mysqld_safe --skip-grant-tables --skip-networking &
步骤三、此时你只要是在本地登录，不管用什么用户名什么密码都可以登录。
mysql -ucsdcds -pcsdcsdcdfvver
当前它不加载权限表，也不支持网络tcp/ip连接，也不能直接修改密码，要修改密码的话需要先使用刷新授权表之后再修改密码。一般普通用户忘记密码联系root用户修改即可，所以一般只要root用户密码不遗失都没什么问题，我这里改的密码也不是root的。
flush privileges;
alter user guojie@'localhost' identified by 'x6388991';
步骤四、修改完成之后重启数据库服务到正常模式即可。
3.4 用户权限
授权命令：grant 权限 on 权限级别 to 用户;
3.4.1权限级别
全库级别 : *.* ---> 管理员
单库级别 : db_name.* ---> 业务层面
单表级别 : db_name.tb_name ---> 表层面
列级别 : select (id,name) ---> 需求比较少，有时候表列有些类似敏感信息如身份证号等会用。
3.4.2 权限列表
#查看权限表：
mysql> show privileges;
| 序号 | 权限名称 | 权限对象 | 权限作用 |
|---|---|---|---|
| 1 | Alter | 表 | 修改表结构，例如添加、删除或修改列。 |
| 2 | Alter routine | 功能，程序 | 存储过程、函数、触发器等的变更权限。 |
| 3 | Create | 库、表、索引 | 创建新的数据库、表、索引以及视图。 |
| 4 | Create routine | 库 | 创建存储过程和函数。 |
| 5 | Create role | 管理员 | 创建新角色。 |
| 6 | Create temporary tables | 库 | 创建临时表。 |
| 7 | Create view | 表 | 创建新的视图。 |
| 8 | Create user | 管理员 | 创建新的用户。 |
| 9 | Delete | 表 | 从表中删除行。 |
| 10 | Drop | 库、表 | 删除数据库、表、视图。 |
| 11 | Drop role | 管理员 | 删除角色。 |
| 12 | Event | 管理员 | 创建、修改、删除和执行事件 |
| 13 | Execute | 功能，程序 | 执行存储过程和函数。 |
| 14 | File | 服务器上的文件访问 | 授予用户对服务器上的文件的访问权限，允许他们读取和写入文件。 |
| 15 | Grant option | 库，表，函数，功能 | 允许用户授予其他用户与自己拥有的相同权限。 |
| 16 | Index | 表 | 允许用户在表上创建或删除索引。 |
| 17 | Insert | 表 | 允许用户将数据插入到表中。 |
| 18 | Lock tables | 库 | 允许用户使用LOCK TABLES语句（与SELECT权限结合使用）。 |
| 19 | Process | 管理员 | 允许用户查看当前执行的查询的纯文本。 |
| 20 | Proxy | 管理员 | 允许用户进行代理服务器设置。 |
| 21 | References | 库、表 | 允许用户引用表。 |
| 22 | Reload | 管理员 | 允许用户重新加载或刷新表、日志和权限。 |
| 23 | Replication client | 管理员 | 允许用户询问主从服务器位置。 |
| 24 | Replication slave | 管理员 | 允许用户从主服务器读取二进制日志事件。 |
| 25 | Select | 表 | 允许用户从表中检索行。 |
| 26 | Show databases | 管理员 | 允许用户查看所有数据库（使用SHOW DATABASES语句）。 |
| 27 | Show view | 表 | 允许用户查看视图（使用SHOW CREATE VIEW语句）。 |
| 28 | Shutdown | 管理员 | 关闭服务。 |
| 29 | Super | 管理员 | 允许用户使用KILL thread、SET GLOBAL、CHANGE MASTER等超级用户功能。 |
| 30 | Trigger | 表 | 允许用户使用触发器。 |
| 31 | Create tablespace | 管理员 | 允许用户创建、修改或删除表空间。 |
| 32 | Update | 表 | 允许用户更新表中的现有行。 |
| 33 | Usage | 管理员 | 允许用户连接到MySQL服务器，但没有任何其他权限。 |
生产中用户类型规范
管理员:all
all不包含Grant option这个权限，这个权限也已给其它用户授权。
开发:Create, Create routine, Create temporary tables, Create view, Delete, Event, Execute, Insert, References, Select, Show view, Trigger, Update
监控:Select, Replication slave, Replication client, Super, Process
备份:Select, Show databases, Process, Lock tables, Reload
业务:Insert, Update, delete, select
3.4.3 授权
例1、创建开发用户"dev_user1"并授权"dev-db"给他
mysql> create user dev_user1@'%' identified by '123';
Query OK, 0 rows affected (0.01 sec)
mysql> grant Create, Create routine, Create temporary tables, Create view, Delete, Event, Execute, Insert, References, Select, Show view, Trigger, Update on dev_db.* to dev_user1@'%';
Query OK, 0 rows affected (0.00 sec)
例2、创建一个主重复制的用户repl
mysql> create user repl@'%' identified with mysql_native_password by '123';
Query OK, 0 rows affected (0.20 sec)
mysql> grant Replication slave,Replication client on *.* to repl@'%';
Query OK, 0 rows affected (0.16 sec)
例3、给所有权限，除了给其它用户授权的权限外
grant all privileges on *.* to guojie@'%';
3.4.4 查看权限
show grants for guojie@'localhost';
mysql> show grants for repl@'%';
+------------------------------------------------------------------+
| Grants for repl@% |
+------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `repl`@`%` |
+------------------------------------------------------------------+
1 row in set (0.00 sec)
也可以查询mysql.user和mysql.db
mysql> select * from mysql.user where user='repl'\G
mysql> select * from mysql.db\G
3.4.5 权限回收
revoke 权限 on *.* from repl@%;
# 查看
mysql> show grants for repl@'%';
+------------------------------------------------------------------+
| Grants for repl@% |
+------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `repl`@`%` |
+------------------------------------------------------------------+
1 row in set (0.00 sec)
# 回收“REPLICATION SLAVE”权限
mysql> revoke REPLICATION SLAVE on *.* from repl@`%`;
Query OK, 0 rows affected (0.22 sec)
#再看
mysql> show grants for repl@'%';
+-----------------------------------------------+
| Grants for repl@% |
+-----------------------------------------------+
| GRANT REPLICATION CLIENT ON *.* TO `repl`@`%` |
+-----------------------------------------------+
1 row in set (0.00 sec)
3.5 MySQL的多种连接方式
3.5.1 Socket
通过socket连接前提：
1.数据库启动
2.必须是localhost白名单的用户才能登录
连接命令：mysql -uroot -p -S /tmp/mysql.sock,socket文件可以通过配置文件指定。
mysql> select user, host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
mysql> quit;
Bye
[root@localhost tmp]# mysql -uroot -p -S /tmp/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
3.5.2 远程登录
通过远程登录的前提：
1.数据库在运行。
2.用户的在白名单。
3.网络得通畅。
也可以在其它机器使用mysql-client远程：
远程命令：mysql -uguojie -h172.16.1.2 -P3306 -p
[root@localhost ~]# mysql -uguojie -h172.16.1.2 -P3306 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
3.5.3 基于SSL加密连接(了解)
默认就是开启的，什么也不用配置：
mysql> show variables like '%ssl%';
+-------------------------------------+-----------------+
| Variable_name | Value |
+-------------------------------------+-----------------+
| admin_ssl_ca | |
| admin_ssl_capath | |
| admin_ssl_cert | |
| admin_ssl_cipher | |
| admin_ssl_crl | |
| admin_ssl_crlpath | |
| admin_ssl_key | |
| have_openssl | YES |
| have_ssl | YES |
| mysqlx_ssl_ca | |
| mysqlx_ssl_capath | |
| mysqlx_ssl_cert | |
| mysqlx_ssl_cipher | |
| mysqlx_ssl_crl | |
| mysqlx_ssl_crlpath | |
| mysqlx_ssl_key | |
| performance_schema_show_processlist | OFF |
| ssl_ca | ca.pem |
| ssl_capath | |
| ssl_cert | server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_fips_mode | OFF |
| ssl_key | server-key.pem |
+-------------------------------------+-----------------+
25 rows in set (0.00 sec)
连接方法：
把数据路径下客户端的证书和密钥拷到要连接的客户端，然后指定连接：
mysql -uguojie -h172.16.1.2 -P3306 -p --ssl-cert=D:\client-cert.pem --ssl-key=D:\client-key.pem
D:\>mysql -uguojie -h172.16.1.2 -P3306 -p --ssl-cert=D:\client-cert.pem --ssl-key=D:\client-key.pem
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 34
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
3.6 MySQL的配置文件应用
3.6.1mysql配置文件
mysql内定了配置文件加载路径，默认情况会从如下路径从左往右加载：
mysqld --help --verbose|grep my.cnf
3.6.2 指定配置文件启动
多实例启动的时候会用到
mysqld_safe --defaults-file=/etc/my.cnf &
3.6.3 配置文件内容
标签：
server端：
[server]
[mysqld]
[mysqld_safe]
client端：
[client]
[mysql]
[mysqldump]
这里是基本的配置文件，后期用到会讲解其它配置。
[mysqld] # 标签项，这里是服务器端标签，这里他会读取以下的内容一直到下一个标签[]作为服务端的配置
user=mysql # 管理用户
basedir=/opt/mysql # 软件路径
datadir=/data/3306/data # 数据存放路径
socket=/tmp/mysql.sock # socket文件位置
server_id=6 # 服务器ID，主从复制时标识不同主机
log_bin=/data/mysql/binlog_3306 # 二进制日志
port=3306 # 端口
[mysql] # 标签项，这里是客户端端标签，这里他会读取以下的内容作为客户端的配置
socket=/tmp/mysql.sock
3.7 MySQL的启动关闭
3.7.1启动
· /etc/init.d/mysqld start
· systemctl start mysqld
以上两种方式实际上都是通过/opt/mysql/support-files/mysql.server文件来管理，他们都是通过调用安装路径/opt/mysql/bin/mysqld_safe下的脚本来启动，真正启动的主程序是/opt/mysql/bin/mysqld,/opt/mysql/bin/mysqld_safe也是通过调用/opt/mysql/bin/mysqld来启动。
mysqld_safe程序的作用：
·记录日志到文件
·自动监控mysqld的状态（你用kill -9 干掉mysqld进程，他会自动尝试拉起来）
3.7.2关闭
· systemctl stop mysqld
· /etc/init.d/mysqld stop
注意：systemctl只能关闭自己systemctl启动的
· 登录到数据库里执行shutdown;
· mysqladmin -uroot -p -S /tmp/mysql.sock shutdown
· kill干掉进程，这个不建议使用
3.8 MySQL多实例
多实例即一台主机上运行多个mysql，有时候资源有限的时候考虑会使用。
3.8.1 同版本
3.8.1.1 实例准备
# 3306之前已经部署，这里添加3307和3308，先创建数据路径并授权
[root@localhost ~]# mkdir -p /data/3307/data /data/3308/data
[root@localhost ~]# chown -R mysql.mysql /data
# 复制配置文件
[root@localhost ~]# cp /etc/my.cnf /etc/my3307.cnf
[root@localhost ~]# cp /etc/my.cnf /etc/my3308.cnf
# 分别修改如下配置
# 3306实例，vi /etc/my.cnf
[mysqld]
user=mysql
basedir=/opt/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=6
port=3306
[mysql]
socket=/tmp/mysql.sock
# 3307实例，vi /etc/my3307.cnf
[mysqld]
user=mysql
basedir=/opt/mysql
datadir=/data/3307/data
socket=/tmp/mysql3307.sock
server_id=7
port=3307
[mysql]
socket=/tmp/mysql3307.sock
# 3308实例，vi /etc/my3308.cnf
[mysqld]
user=mysql
basedir=/opt/mysql
datadir=/data/3308/data
socket=/tmp/mysql3308.sock
server_id=8
port=3308
[mysql]
socket=/tmp/mysql3308.sock
3.8.1.2 实例初始化
3307实例初始化
mysqld --defaults-file=/etc/my3307.cnf --initialize-insecure
3308实例初始化
mysqld --defaults-file=/etc/my3308.cnf --initialize-insecure
3.8.1.3 实例启动
3306实例使用之前的启动方法即可
mysqld_safe --defaults-file=/etc/my3307.cnf &
mysqld_safe --defaults-file=/etc/my3308.cnf &
3.8.1.4 实例连接
mysql -uroot -p -S /tmp/mysql.sock
mysql -uroot -p -S /tmp/mysql3307.sock
mysql -uroot -p -S /tmp/mysql3308.sock
3.8.2 不同版本
3.8.2.1实例准备
# 解压安装包并常见软连接
[root@localhost ~]# tar -zxvf mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz -C /opt/
[root@localhost opt]# ln -s /opt/mysql-5.7.39-linux-glibc2.12-x86_64 /opt/mysql57
# 创建数据路径
[root@localhost ~]# mkdir -p /data/3357/data
[root@localhost ~]# chown -R mysql.mysql /data
# 复制配置文件
[root@localhost ~]# cp /etc/my.cnf /etc/my3357.cnf
# 修改配置文件 vi /etc/my3357.cnf
[mysqld]
user=mysql
basedir=/opt/mysql57
datadir=/data/3357/data
socket=/tmp/mysql3357.sock
server_id=57
port=3357
[mysql]
socket=/tmp/mysql3357.sock
3.8.2.2 实例初始化
[root@localhost data]# /opt/mysql57/bin/mysqld --defaults-file=/etc/my3357.cnf --initialize-insecure
3.8.2.3 实例启动
[root@localhost data]# /opt/mysql57/bin/mysqld_safe --defaults-file=/etc/my3357.cnf &
3.8.2.4 实例连接
[root@localhost data]# mysql -uroot -p -S /tmp/mysql3357.sock
3.9 mysql 日志
3.9.1 错误日志
3.9.1.1概述
作用：
从启动开始，记录发生过的error,warning,note信息。用于定位数据库问题，如启动不了，主从故障，报错，异常（死锁），数据库hang，堆栈信息
配置：
默认位置：log_error=$DATADIR/hostname.err
查看日志：主要关注[ERROR],deadlock
调整位置：
# 修改配置文件，vi /etc/my.cnf
[mysqld]
log_error=/data/3306/log/mysql-err.log
# 创建对应文件并授权
mkdir -p /data/3306/log/
touch /data/3306/log/mysql-err.log
chown -R mysql.mysql /data/3306/log
技巧：有时会数据库启动不起来可以通过mysqld程序启动，他会把日志打到屏幕：
[root@localhost log]# mysqld &
3.9.1.2 错误日志级别
默认是2，如果想要日志记录得更详细，就需要把级别调高
| 记录内容 | log_error_verbosity的值 |
|---|---|
| ERROR (错误信息) | 1 |
| ERROR,WARNING（错误和警告信息） | 2 |
| ERROR,WARNING,INFORMATION（错误、警告、通知信息） | 3 |
3.9.1.3 配置日志记录级别
登录服务器使用set global log_error_verbosity=3;
一般生产中设置到级别3，登录失败日志也是需要调到3才会记录。
mysql> set global log_error_verbosity=3;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%log_err%';
+----------------------------+----------------------------------------+
| Variable_name | Value |
+----------------------------+----------------------------------------+
| binlog_error_action | ABORT_SERVER |
| log_error | /data/3306/log/mysql-err.log |
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 3 |
+----------------------------+----------------------------------------+
5 rows in set (0.00 sec)
3.9.2 二进制日志（binlog）
3.9.2.1 概述
记录了MySQL发生修改的操作日志。修改操作都会记录到binlog
用途：
数据恢复、主从、SQL问题排查、审计（工具：binlog2sql、 my2sql）
3.9.2.2 配置
8.0版本之后默认是开启的，5.7、5.6需要手动开启。默认在$DATADIR\binlog.000001。建议日志和数据分开存储。
配置的必要参数：
[mysqld]
server_id=6
log_bin=/data/3306/log/mysql-bin
# 然后重启数据库
systemctl restart mysqld
3.9.3 慢日志（slow_log）
3.9.3.1 概述
默认没有打开，用于记录数据库运行期间，执行较慢的SQL。
3.9.3.2 配置
| 参数 | 作用 |
|---|---|
| slow_query_log=1 | 开关。 |
| slow_query_log_file=/data/3306/log/slow.log | 日志文件存放路径。 |
| long_query_time=3 | 执行时间操作多少秒算慢。 |
| log_queries_not_using_indexes=1 | 没有用索引的也记录。 |
| log_throttle_queries_not_using_indexes=1000 | 没有用索引的记录条目记录在1000条内。 |
[mysqld]
slow_query_log=1
slow_query_log_file=/data/3306/log/slow.log
long_query_time=3
log_queries_not_using_indexes=1
log_throttle_queries_not_using_indexes=1000
以下参数均可在mysql里使用SQL直接设置，但是重启之后会失效。
set global slow_query_log=1
set global long_query_time=3
set global log_queries_not_using_indexes=1
set global log_throttle_queries_not_using_indexes=1000
# 配置之后重启
systemctl restart mysqld
3.9.4 普通日志 (general_log)
3.9.5 概述
文本格式记录MySQL运行期间，所有的操作语句，可以做问题诊断和调试，生产环境一般不使用。
3.9.6 配置
| 参数 | 作用 |
|---|---|
| general_log=on | 开关 |
| general_log_file=/data/3306/log/general.log |
[mysqld]
general_log=on
general_log_file=/data/3306/log/general.log
# 配置之后重启
systemctl restart mysqld
3.10 mysql 升级
3.10.1 升级方式介绍
· INPLACE就地升级
在一台服务器上，原版本升级到新版版本，该方式风险比较大，除非是主从环境。
· Mergeing(Logical)迁移
备份方式、主从方式（其实就是把数据导出升级后导入）。
注意：不管使用哪种升级方式，都应该先做冷备份，即Copy打包$DATADIR和配置文件以及所有日志文件，方便回退。
3.10.2 升级注意事项
a. 只支持GA版之间升级。GA版本就是版本号是纯数字不带字母的，如下是非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. 降低停机时间（停业务的时间），在业务不繁忙期间升级，做好足够的预算。
3.10.3 INPLACE升级过程原理（生产思路）
a. 安装新版本软件。
b. 关闭数据库业务（挂维护页），innodb_fast_shutdown=0。
c. 备份原数据库数据（冷备）。
d. 使用新版本软件“挂”旧版本数据启动（--skip-grant-tables, --skip-networking）
e. 升级：只是升级系统表。升级时间和数据库数据量无关的。
f. 正常重启数据库。
g. 验证各项功能是否正常。
h. 业务恢复。
建议：inplace升级最好是主从环境，先升级从库，再升级主库。
3.10.4 演练
5.6.51 --> 5.7.42
(1)安装，参考以上多版本实例配置
安装旧版本MySQL5.6
tar -zxvf mysql-5.6.51-linux-glibc2.12-x86_64.tar.gz -C /opt/
ln -s /opt/mysql-5.6.51-linux-glibc2.12-x86_64 /opt/mysql56
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
mkdir -p /data/3356/data
cat >/data/3356/my.cnf<<'EOF'
[mysqld]
user=mysql
basedir=/opt/mysql56
datadir=/data/3356/data
server_id=56
port=3356
socket=/tmp/mysql56.sock
[mysql]
socket=/tmp/mysql56.sock
EOF
chown -R mysql.mysql /data
# mysql5.6初始化方式与5.7、5.8不一样，使用如下方法初始化。
/opt/mysql56/scripts/mysql_install_db --user=mysql --basedir=/opt/mysql56 --datadir=/data/3356/data
# 有可能会提示-bash: /opt/mysql56/scripts/mysql_install_db: /usr/bin/perl: 坏的解释器: 没有那个文件或目录
# 安装依赖之后再初始化
yum -y install perl perl-devel autoconf
# 看见两个OK就可以。
启动并导随意导入一点数据：
# 启动
/opt/mysql56/bin/mysqld_safe --defaults-file=/data/3356/my.cnf &
# 登录
/opt/mysql56/bin/mysql -uroot -p -S /tmp/mysql56.sock
# 制造数据，这里是导入
mysql> create database if not exists employees default character set utf8;
mysql> use employees;
mysql> source /root/employees.sql;
mysql> show tables;
+----------------------+
| Tables_in_employees |
+----------------------+
| current_dept_emp |
| departments |
| dept |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| emp |
| employees |
| salaries |
| titles |
+----------------------+
10 rows in set (0.00 sec)
升级到5.7.42
先安装新版本的软件mysql5.7.42。
tar -zxvf mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz -C /opt/
ln -s /opt/mysql-5.7.42-linux-glibc2.12-x86_64 /opt/mysql57
mkdir -p /data/3357/data
cat >/data/3357/my.cnf<<'EOF'
[mysqld]
user=mysql
basedir=/opt/mysql57
datadir=/data/3357/data
server_id=57
port=3357
socket=/tmp/mysql57.sock
[mysql]
socket=/tmp/mysql57.sock
EOF
chown -R mysql.mysql /data/3357
# 初始化，并登录测试
/opt/mysql57/bin/mysqld --defaults-file=/data/3357/my.cnf --initialize-insecure
/opt/mysql57/bin/mysqld_safe --defaults-file=/data/3357/my.cnf &
/opt/mysql57/bin/mysql -uroot -p -S /tmp/mysql57.sock
# 没问题后关闭
mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)
停止旧版本的数据库业务：
# 连接数据库
/opt/mysql56/bin/mysql -uroot -p -S /tmp/mysql56.sock
# 杀掉所有的连接
mysql> select concat('kill ',id,';') from information_schema.`processlist`;
+------------------------+
| concat('kill ',id,';') |
+------------------------+
| kill 3; |
| kill 7; |
+------------------------+
2 rows in set (0.01 sec)
# 使用alt+左键选中所有kill复制并粘贴；
mysql> kill 3;
mysql> kill 7;
# 调整参数，关闭快速关闭的功能。
mysql> set global innodb_fast_shutdown=0;
# 关闭数据库
# mysql5.7和8.0支持在数据库里使用shutdown关闭。
# 5.6需要退出去使用如下命令关闭
/opt/mysql56/bin/mysqladmin -S /tmp/mysql56.sock shutdown
对旧版本的数据做冷备份：
# 进入数据目录（$DATADIR）
cd /data/3356/data/
# 打包压缩到/data下并取名mysql56_databak.tar.gz
tar -zcvf /data/mysql56_databak.tar.gz ./*
使用新版本软件mysql5.7.42“挂”旧版本mysql5.6.51数据启动
# 注意我这里使用的是mysql57的程序去指定mysql56的程序启动的，要跳过授权表和网络。
/opt/mysql57/bin/mysqld_safe --defaults-file=/data/3356/my.cnf --skip-grant-tables --skip-networking &
此时数据库已经启动了但是它会报许多的错误，端口也没有在监听，但是可以连接：
/opt/mysql57/bin/mysql -hlocalhost -p -S /tmp/mysql56.sock
并且可以看见版本已经升级，并且可以看见数据也还在，此时还需要退出登录，然后升级系统表，注意不要关闭数据库。
# 使用新本本的升级程序做升级
/opt/mysql57/bin/mysql_upgrade -S /tmp/mysql56.sock --force
可以看见升级了很多系统表升级成功的状态，之后现在处于安全模式，需要登录之后将其关闭，然后以正常模式启动就可以了。
/opt/mysql57/bin/mysql -uroot -hlocalhost -p -S /tmp/mysql56.sock
mysql> shutdown;
mysql> quit;
# 这时候以以久版本的配置文件启动，或者cp一份改成57的,然后把端口改回对应的保证业务可以连接，再启动即可
cat /data/3356/my.cnf > /data/3357/my.cnf
# 正常启动
/opt/mysql57/bin/mysqld_safe --defaults-file=/data/3357/my.cnf &
# 连接查看数据都还在，现在即升级完成，端口也正常监听
/opt/mysql57/bin/mysql -uroot -p -S /tmp/mysql57.sock
5.7.42 --> 8.0.33
这个升级不同于5.6到5.7，它有一些新特性：
1、mysql-shell工具，8.0以后，可以调用这个命令，升级之前的预检查。
例如：mysqlsh root:123:@172.16.1.3:3306 -e "util.checkForServerUpgrade()"
2、升级时不需要手工 mysql_upgrade
3、限制：升级之前必须备份。否则无法回退。
8.0.33部署
tar -xvf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz -C /opt/
ln -s /opt/mysql-8.0.33-linux-glibc2.12-x86_64 /opt/mysql80
mkdir -p /data/3380/data
cat >/data/3380/my.cnf<<'EOF'
[mysqld]
user=mysql
basedir=/opt/mysql80
datadir=/data/3380/data
socket=/tmp/mysql80.sock
server_id=80
[mysql]
socket=/tmp/mysql80.sock
EOF
chown -R mysql.mysql /data/3380
# 初始化
/opt/mysql80/bin/mysqld --defaults-file=/data/3380/my.cnf --initialize-insecure
# 启动
/opt/mysql80/bin/mysqld_safe --defaults-file=/data/3380/my.cnf &
#登录
/opt/mysql80/bin/mysql -uroot -p -S /tmp/mysql80.sock
# 关闭退出
mysql> shutdown;
mysql> quit;
**mysql-shell安装 **
下载：https://downloads.mysql.com/archives/shell/
# 解压安装
tar -zxvf mysql-shell-8.0.33-linux-glibc2.12-x86-64bit.tar.gz -C /opt/
ln -s /opt/mysql-shell-8.0.33-linux-glibc2.12-x86-64bit /opt/mysqlsh
cd /opt/mysqlsh/
使用脚本检查要升级的数据库：
/opt/mysqlsh/bin/mysqlsh -uroot -p -S /tmp/mysql57.sock -e "util.checkForServerUpgrade()"
# 回车 --> Y --> 回车
# 执行之后他会在最后显示一下内容，即错误、警告、和提示。
Errors: 0
Warnings: 15
Notices: 1
# 警告内容不会影响数据库正常运行，但是要尽量解决。
关闭业务并关闭数据库做冷备份：
[root@localhost data]# /opt/mysql57/bin/mysql -uroot -p -S /tmp/mysql57.sock
mysql> set global innodb_fast_shutdown=0;
mysql> select concat('kill ',id,';') from information_schema.`processlist`;
+------------------------+
| concat('kill ',id,';') |
+------------------------+
| kill 7; |
+------------------------+
1 row in set (0.00 sec)
mysql> kill 8;
mysql> shutdown;
mysql> quit;
[root@localhost data]# cd /data/3357/data/
[root@localhost data]# tar -zcvf /data/mysql57bak.tar.gz ./*
# 使用新版本MySQL80的程序“挂”MySQL57的数据
/opt/mysql80/bin/mysqld_safe --defaults-file=/data/3357/my.cnf --skip-grant-tables --skip-networking &
# 登录查看
/opt/mysql80/bin/mysql -uroot -p -S /tmp/mysql57.sock
mysql> shutdown;
# 以正常方式启动
/opt/mysql80/bin/mysqld_safe --defaults-file=/data/3357/my.cnf &
完成！！！。
升级失败回退
升级失败的话就解压冷备份的数据，然后修改配置文件把数据路径重新制定到解压的数据路径即可。比如这里回退之前57到56版本。
[root@localhost data]# mkdir /data/56bak
[root@localhost data]# tar -zxvf /data/mysql56_databak.tar.gz -C /data/56bak/
# 修改datadir，把数据路径指向/data/56bak
[root@localhost data]# vi /data/3356/my.cnf
[mysqld]
user=mysql
basedir=/opt/mysql56
datadir=/data/56bak
server_id=56
port=3356
socket=/tmp/mysql56.sock
[mysql]
socket=/tmp/mysql56.sock
# 授权
[root@localhost data]# chown -R mysql.mysql /data/56bak
# 再启动
[root@localhost data]# /opt/mysql56/bin/mysqld_safe --defaults-file=/data/3356/my.cnf &
完成！！！。
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 全程不用写代码，我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了