MySQL多实例配置，一台主机上运行多个mysql服务

Mysql多实例概述

什么是多实例？

－一台主机上运行多个mysql服务

为什么用多实例？

－节约运维成本

－提高硬件利用率

 

如何配置

这里演示在一台主机上启动两个mysql服务的配置

1）解压软件、修改目录名、设置PATH路径

libaio简介：

The libaio package is an asynchronous I/O facility ("async I/O", or "aio") that has a richer API and capability set than the simple POSIX async I/O facility. This library, libaio, provides the Linux-native API for async I/O. The POSIX async I/O facility requires this library in order to provide kernel-accelerated async I/O capabilities, as do applications which require the Linux-native async I/O API.

This package is known to build and work properly using an LFS-9.0 platform.

简言之就是提供IO异步支持,mysql在运行的时候会需要一个IO进程，但是运行多实例mysql就需要多个IO进程，并且异步的进行读写操作，这就需要用到libaio提供的异步io支持．

为什么要用到PATH环境变量？因为我们使用Mysql多实例时并不安装mysql服务,如果采用rpm安装mysql，则会自动在/usr/local/下创建mysql目录,并且创建环境变量，从而可以通过命令来启动，而多实例不才用rpm安装，那就需要我们自己配置环境变量

1. ]# yum –y install libaio
2. ]# useradd mysql　　　　　　　
3. ]# tar -zxvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
4. ]# mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql
5. ]# PATH=/usr/local/mysql/bin:$PATH
6. ]# vim /etc/bashrc
7. export PATH=/usr/local/mysql/bin:$PATH
8. :wq

 

2）编辑主配置文件/etc/my.cnf

每个实例要有独立的数据库目录、监听端口号、实例名称和独立的sock文件

这个文件默认是不存在的，需要我们自己创建并修改

1. ]# vim /etc/my.cnf
2. [mysqld_multi]        //启用多实例　
3. mysqld = /usr/local/mysql/bin/mysqld_safe        //指定进程文件路径
4. mysqladmin = /usr/local/mysql/bin/mysqladmin    //指定管理命令路径
5. user = root        //指定进程用户
6.  
7. [mysqld1]        //实例进程名称
8. port=3307        //端口号
9. datadir=/dir1        //数据库目录 ，要手动创建
10. socket=/dir1/mysqld1.sock        //指定sock文件的路径和名称
11. pid-file=/dir1/mysqld1.pid        //进程pid号文件位置
12. log-error=/dir1/mysqld1.err        //错误日志位置
13.  
14. [mysqld2]
15. port=3308
16. datadir=/dir2
17. socket=/dir2/mysqld2.sock
18. pid-file=/dir2/mysqld2.pid
19. log-error=/dir2/mysqld2.err
20. :wq

3）创建数据库目录 

1. ]# mkdir /dir2
2. ]# mkdir /dir1

4）启动多实例

首次启动服务会做数据初始化 并初始和提示数据库管理员本机登录密码

1. [root@host57 ~]# mysqld_multi start 1 //启动实例1
2.  
3. Installing new database in /dir1
4.  
5. 2019-06-13T10:46:29.307866Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
6. 2019-06-13T10:46:30.997233Z 0 [Warning] InnoDB: New log files created, LSN=45790
7. 2019-06-13T10:46:31.436904Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
8. 2019-06-13T10:46:31.582129Z 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: 816bf015-8dc8-11e9-b492-525400cffedc.
9. 2019-06-13T10:46:31.605276Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
10. 2019-06-13T10:46:31.606321Z 1 [Note] A temporary password is generated for root@localhost: ly#LryiFE5fT 管理员本机登录密码
11.  
12. ]# ls /dir1 //查看数据库目录文件列表
13. auto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 mysql mysql3307.log mysql3307.pid mysql3307.sock mysql3307.sock.lock performance_schema sys
14.  
15. ]# mysqld_multi start 2 //启动实例2
16.  
17.  
18. Installing new database in /dir1
19.  
20. 2019-06-13T10:56:55.580796Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
21. 2019-06-13T10:56:57.199217Z 0 [Warning] InnoDB: New log files created, LSN=45790
22. 2019-06-13T10:56:57.571839Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
23. 2019-06-13T10:56:57.708168Z 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: f69f30fa-8dc9-11e9-8a17-525400cffedc.
24. 2019-06-13T10:56:57.724096Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
25. 2019-06-13T10:56:57.724677Z 1 [Note] A temporary password is generated for root@localhost: qedTjrZs*8ma 管理员本机登录密码
26.  
27. ]# ls /dir1 //查看数据库目录文件列表
28. auto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 mysql mysql3308.log mysql3308.pid mysql3308.sock mysql3308.sock.lock performance_schema sys

5）查看端口

1. ]# netstat -utnlp | grep :3307
2. tcp6 0 0 :::3307 :::* LISTEN 1151/mysqld
3.  
4. ]# netstat -utnlp | grep :3308
5. tcp6 0 0 :::3308 :::* LISTEN 1339/mysqld
6.  
7. ]# netstat -utnlp | grep mysqld
8. tcp6 0 0 :::3307 :::* LISTEN 1151/mysqld
9. tcp6 0 0 :::3308 :::* LISTEN 1339/mysqld
10.  
11. # ps -C mysqld
12. PID TTY TIME CMD
13. 1151 pts/1 00:00:00 mysqld
14. 1339 pts/1 00:00:00 mysqld
15. [root@host57 ~]#

 

6）访问多实例 

使用初始化密码登录实例1

1. [root@host57 ~]#grep -i 'password' /var/log/mysqld.log　//获取密码
2. [root@host57 ~]# mysql -uroot -p'ly#LryiFE5fT' -S /dir1/mysqld1.sock
3.     mysql> alter user root@"localhost" identified by "123456";    //修改密码
4. mysql> exit
5. Bye
6. [root@host57 ~]# mysql -uroot -p123456 -S /dir1/mysqld1.sock //新密码登录
7. mysql> show databases;
8. +--------------------+
9. | Database |
10. +--------------------+
11. | information_schema |
12. | mysql |
13. | performance_schema |
14. | sys |
15. +--------------------+
16. 4 rows in set (0.00 sec)
17.  
18. mysql> create database db1; //创建新库db1
19. Query OK, 1 row affected (0.00 sec)
20.  
21. mysql> show databases; //查看已有的库
22. +--------------------+
23. | Database |
24. +--------------------+
25. | information_schema |
26. | db1 | //db1库
27. | mysql |
28. | performance_schema |
29. | sys |
30. +--------------------+
31. 5 rows in set (0.00 sec)
32.  
33. mysql> exit //断开连接
34. Bye
35. [root@host56 ~]# ls /dir1 //查看数据库目录文件列表 有db1库的文件夹
36. auto.cnf ibdata1 ibtmp1 mysqld1.pid performance_schema
37. db1 ib_logfile0 mysql mysqld1.socket sys
38. ib_buffer_pool ib_logfile1 mysqld1.err mysqld1.socket.lock
39. [root@host56 ~]#

使用初始化密码登录实例2

1. [root@host57 ~]# mysql -uroot -p'qedTjrZs*8ma' -S /dir2/mysqld2.sock
2.     mysql> alter user root@"localhost" identified by "654321";    //修改密码
3. mysql> exit
4. Bye
5. [root@host57 ~]# mysql -uroot –p654321 -S /dir2/mysqld2.sock //新密码登录
6. mysql> show databases;
7. +--------------------+
8. | Database |
9. +--------------------+
10. | information_schema |
11. | mysql |
12. | performance_schema |
13. | sys |
14. +--------------------+
15. 4 rows in set (0.00 sec)
16.  
17. mysql>
18. mysql> create database db2;
19. Query OK, 1 row affected (0.00 sec)
20.  
21. mysql> show databases;
22. +--------------------+
23. | Database |
24. +--------------------+
25. | information_schema |
26. | db2 |
27. | mysql |
28. | performance_schema |
29. | sys |
30. +--------------------+
31. 5 rows in set (0.00 sec)
32.  
33. mysql> exit
34. Bye
35. [root@host56 ~]# ls /dir2
36. auto.cnf ib_logfile0 mysqld2.err performance_schema
37. db2 ib_logfile1 mysqld2.pid sys
38. ib_buffer_pool ibtmp1 mysqld2.socket
39. ibdata1 mysql mysqld2.socket.lock
40. [root@host56 ~]#

7）停止多实例服务

mysqld_multi --user=root --password=密码 stop 实例编号

1. ]# netstat -utnlp | grep mysqld
2. tcp6 0 0 :::3307 :::* LISTEN 1250/mysql
3. tcp6 0 0 :::3308 :::* LISTEN 1451/mysql
4.  
5. ]# mysqld_multi --user=root --password=123456 stop 2
6.  
7. [root@host56 ~]# netstat -utnlp | grep mysqld
8. tcp6 0 0 :::3307 :::* LISTEN 1250/mysql
9.  
10. ]# mysql -uroot -p123456 -S /dir2/mysqld2.sock //拒绝连接
11. mysql: [Warning] Using a password on the command line interface can be insecure.
12. ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/dir2/mysqld2.sock' (2)