MySQL多实例实战案例

一.部署MySQL多实例案例

1.初始化数据库实力的数据目录

[root@mysql107.oldboyedu.com ~]# mkdir /oldboyedu/data/mysql3307
[root@mysql107.oldboyedu.com ~]# 
[root@mysql107.oldboyedu.com ~]# chown mysql:mysql /oldboyedu/data/mysql3307
[root@mysql107.oldboyedu.com ~]# 
[root@mysql107.oldboyedu.com ~]# mysqld --initialize-insecure --user=mysql --basedir=/oldboyedu/softwares/mysql --datadir=/oldboyedu/data/mysql3307

温馨提示:
    对于MySQL5.6而言，初始化数据库的命令为:"/oldboyedu/softwares/mysql/scripts/mysql_install_db --user=mysql --basedir=/oldboyedu/softwares/mysql --datadir=/oldboyedu/data/mysql3307"

2 .创建配置文件

[root@mysql107.oldboyedu.com ~]# cd /oldboyedu/softwares/mysql
[root@mysql107.oldboyedu.com /oldboyedu/softwares/mysql]# mkdir conf
[root@mysql107.oldboyedu.com /oldboyedu/softwares/mysql]# 
[root@mysql107.oldboyedu.com /oldboyedu/softwares/mysql]# chown mysql:mysql conf
[root@mysql107.oldboyedu.com /oldboyedu/softwares/mysql]#
[root@mysql107.oldboyedu.com /oldboyedu/softwares/mysql]# cd conf/
[root@mysql107.oldboyedu.com /oldboyedu/softwares/mysql/conf]# 
[root@mysql107.oldboyedu.com /oldboyedu/softwares/mysql/conf]# mv /etc/my.cnf mysql3306.cnf
[root@mysql107.oldboyedu.com /oldboyedu/softwares/mysql/conf]# 
[root@mysql107.oldboyedu.com /oldboyedu/softwares/mysql/conf]# ll
总用量 4
-rw-r--r--. 1 root root 248 7月  19 15:39 mysql3307.cnf
[root@mysql107.oldboyedu.com /oldboyedu/softwares/mysql/conf]# 
[root@mysql107.oldboyedu.com /oldboyedu/softwares/mysql/conf]# vim mysql3307.cnf 
[root@mysql107.oldboyedu.com /oldboyedu/softwares/mysql/conf]# 
[root@mysql107.oldboyedu.com /oldboyedu/softwares/mysql/conf]# 
[root@mysql107.oldboyedu.com /oldboyedu/softwares/mysql/conf]# cat mysql3307.cnf 
[mysqld]
basedir=/oldboyedu/softwares/mysql
datadir=/oldboyedu/data/mysql3307
port=3307
socket=/tmp/mysql3307.sock
[root@mysql107.oldboyedu.com /oldboyedu/softwares/mysql/conf]# 

温馨提示:
    对于MySQL 8.0级以上版本需要额外指定一下两个参数:
        mysqlx_port=33070
        mysqlx_socket=/tmp/mysql33070.sock

3.启动mysql多实例

mysqld_safe --defaults-file=/oldboyedu/softwares/mysql/conf/mysql3307.cnf

4.课后作业

编写启动脚本实现多实例的开机自启动。

二.常见的错误

1.Setup of bind-address: '*' port: 33060 failed, bind() failed with error: Address already in use (98).

报错原因:   
    启动多实例失败，原因是mysqlx协议的端口被占用。

解决方案:
    port=3307  # 默认是3306端口，可以自定义哟~
    mysqlx_port=33070  # 该端口默认为33060，可以自定义哟~

2.'Setup of socket: '/tmp/mysqlx.sock' failed, another process with PID 3206 is using UNIX socket file

报错原因:
    多个mysql实例共用了同一个套接字文件。

解决方案:
    socket=/tmp/mysql3307.sock  # 指定mysql服务的套接字文件。
    mysqlx_socket=/tmp/mysql33070.sock  # 指定mysqlx服务的套接字文件。

3.mysqld_safe A mysqld process already exists

报错原因:
    MySQL数据库已启动。

解决方案:
    停止数据库在启动即可。