MySQL多实例
多实例安装
-
概念
- 在一台服务器上安装多个MySQL示例
- 能够充分利用硬件资源
- 通过mysqld_multi程序管理
- 数据库与数据库实例的概念参见第一篇mysql博客
-
应用
- 高可用:在单机上配置多个实例的从服务器
- 云环境:云数据库RDS,单机配置多实例服务不同用户
-
简易配置
- 修改my.cnf配置文件
# vim /etc/my.cnf # 添加以下内容 [mysqld1] port = 3307 datadir = /mysql/data1 socket = /tmp/mysql.sock1
-
对新实例做初始化操作(如果配置了密码检测插件,使用带密码的初始化方式)
- 初始化的时候一定要指定datadir,否则会报错(目录内有内容,初始化失败)。检查对应data1目录是否有生成文件
[root@mysql1 data]# mysqld --initialize-insecure --datadir=/mysql/data1 2022-04-08T12:26:59.501402Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2022-04-08T12:27:00.072909Z 0 [Warning] InnoDB: New log files created, LSN=45790 2022-04-08T12:27:00.284318Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2022-04-08T12:27:00.315428Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 304e229d-b737-11ec-8765-000c2985b9d8. 2022-04-08T12:27:00.316836Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2022-04-08T12:27:00.336157Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. [root@mysql1 data]# cd ../ [root@mysql1 mysql]# ls data data1 [root@mysql1 mysql]# cd data1 [root@mysql1 data1]# ls auto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 mysql mysql-index.000001 mysql-index.index performance_schema sys
-
配置mysqld_multi标签
- 我的数据库安装在/app/mysql下了,按自己的安装位置填
# vim /etc/my.cnf # 添加以下内容 [mysqld_multi] mysqld=/app/mysql/bin/mysqld_safe mysqladmin=/app/mysql/bin/mysqladmin log=/app/mysql/mysqld_multi.log
-
使用
mysqld_multi report
检测实例状态[root@mysql1 mysql]# mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld1 is not running
-
使用
mysqld_multi start
启动对应实例,不指定默认全部开启[root@mysql1 mysql]# mysqld_multi start 1 [root@mysql1 mysql]# mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld1 is running MySQL server from group: mysqld1 is running [root@mysql1 mysql]# ss -tnlp State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* users:(("sshd",pid=8898,fd=3)) LISTEN 0 80 :::3306 :::* users:(("mysqld",pid=20054,fd=32)) LISTEN 0 80 :::3307 :::* users:(("mysqld",pid=20764,fd=22)) LISTEN 0 128 :::22 :::* users:(("sshd",pid=8898,fd=4))
-
查看对应日志
-
指定对应套接字登录或端口登录
[root@mysql1 mysql]# mysql -S /tmp/mysql.sock1 -uroot -p Enter password:
注:[mysqldx]标签会自动继承[mysqld]内的参数,如果存在重复的参数,依然是替换原则,以[mysqldx]内的参数为准
MySQL服务的几种关闭方式
-
连接mysql示例,执行
shutdown
命令(要有权限)
-
使用
mysqladmin
来关闭服务
-
使用系统级别的关闭systemctl、service、kill等
- 本质上还是
kill
- 本质上还是
- 注:
kill -0 pid
不发送任何信号,但是系统会进行错误检查。
所以经常用来检查一个进程是否存在,存在返回0;不存在返回1
忘记数据库密码
- 一般直接等着被开…
- 解决方法:
- 先关闭数据库服务
- 在配置文件或者命令行设置
skip-grant-tables
跳过授权表 - 无密码登录,在数据库内设置登录密码
- 因为跳过了授权表,所以只能采用修改
mysql.user
表的方式(平时不建议用这种方式),记得用password()
函数 - 关闭skip授权表,重启mysql服务
[root@mysql1 ~]# cat /etc/my.cnf |grep skip
skip-grant-tables
[root@mysql1 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
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.
(root@localhost) [(none)]> update mysql.user set authentication_string=password('123') where user='root' and host='localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
(root@localhost) [(none)]>
(root@localhost) [(none)]> show variables like '%skip%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| skip_external_locking | ON |
| skip_name_resolve | OFF |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_skip_errors | OFF |
| sql_slave_skip_counter | 0 |
+------------------------+-------+
6 rows in set (0.01 sec)
# 注释skip-grant-tables,重启数据库
[root@mysql1 ~]# sed -i 's/skip.*/#&/g' /etc/my.cnf
[root@mysql1 ~]# cat /etc/my.cnf |grep skip
#skip-grant-tables
[root@mysql1 ~]# service mysql restart
Redirecting to /bin/systemctl restart mysql.service
[root@mysql1 ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
多实例下关闭实例的坑
- 启动正常,关闭关不掉
-
原因分析
- 查看配置文件
-
我们使用mysqladmin来关闭数据库实例是需要用户名和密码的,但是配置文件中没有提供对应的用户名和密码
-
解决:
- 在
[client]
标签下添加user
和password
- 或在
[mysqld_multi]
标签下添加user
和pass
(注意这里是pass)
- 在
-
实例法一:
-
[root@mysql1 ~]# sed -i "/\[client\]/ a user='root'\npassword='123'" /etc/my.cnf [root@mysql1 ~]# cat /etc/my.cnf |grep -A 5 'client'; [client] user='root' password='123' port=3306 socket=/tmp/mysql.sock [root@mysql1 ~]# service mysql restart Redirecting to /bin/systemctl restart mysql.service [root@mysql1 ~]# mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld1 is running [root@mysql1 ~]# mysqld_multi stop 1 [root@mysql1 ~]# mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld1 is not running
安装不同版本的MySQL实例
basedir
引入:不同版本MySQL实例管理的最大问题是安装目录不一致,一些启动、关闭命令对应不上- 解决办法:在对应版本的数据库实例下添加
basedir
字段指定安装的路径
[mysqld80]
server-id=80
port=3308
datadir=/mysql/data80
basedir=/app/mysql8.0.28
socket=/tmp/mysql.sock80
补充知识:密码过期expire
-
default_password_lifetime
:默认密码过期时间,默认是0,即不过期。 -
建议配置到配置文件中,因为之前有些版本默认值是360/180,不是0
(root@localhost) [(none)]> show variables like 'default_password%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| default_password_lifetime | 0 |
+---------------------------+-------+
1 row in set (0.00 sec)
[root@mysql1 ~]# sed -i "/\[mysqld\]/ a default_password_lifetime=0 " /etc/my.cnf
[root@mysql1 ~]# grep -A 5 'mysqld' /etc/my.cnf
[mysqld]
default_password_lifetime=0
user=mysql
basedir=/app/mysql
datadir=/mysql/data
port=3306