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安装,那就需要我们自己配置环境变量
- ]# yum –y install libaio
- ]# useradd mysql
- ]# tar -zxvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
- ]# mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql
- ]# PATH=/usr/local/mysql/bin:$PATH
- ]# vim /etc/bashrc
- export PATH=/usr/local/mysql/bin:$PATH
- :wq
2)编辑主配置文件/etc/my.cnf
每个实例要有独立的数据库目录、监听端口号、实例名称和独立的sock文件
这个文件默认是不存在的,需要我们自己创建并修改
- ]# vim /etc/my.cnf
- [mysqld_multi] //启用多实例
- mysqld = /usr/local/mysql/bin/mysqld_safe //指定进程文件路径
- mysqladmin = /usr/local/mysql/bin/mysqladmin //指定管理命令路径
- user = root //指定进程用户
- [mysqld1] //实例进程名称
- port=3307 //端口号
- datadir=/dir1 //数据库目录 ,要手动创建
- socket=/dir1/mysqld1.sock //指定sock文件的路径和名称
- pid-file=/dir1/mysqld1.pid //进程pid号文件位置
- log-error=/dir1/mysqld1.err //错误日志位置
- [mysqld2]
- port=3308
- datadir=/dir2
- socket=/dir2/mysqld2.sock
- pid-file=/dir2/mysqld2.pid
- log-error=/dir2/mysqld2.err
- :wq
3)创建数据库目录
- ]# mkdir /dir2
- ]# mkdir /dir1
4)启动多实例
首次启动服务会做数据初始化 并初始和提示数据库管理员本机登录密码
- [root@host57 ~]# mysqld_multi start 1 //启动实例1
- Installing new database in /dir1
- 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).
- 2019-06-13T10:46:30.997233Z 0 [Warning] InnoDB: New log files created, LSN=45790
- 2019-06-13T10:46:31.436904Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
- 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.
- 2019-06-13T10:46:31.605276Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
- 2019-06-13T10:46:31.606321Z 1 [Note] A temporary password is generated for root@localhost: ly#LryiFE5fT 管理员本机登录密码
- ]# ls /dir1 //查看数据库目录文件列表
- auto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 mysql mysql3307.log mysql3307.pid mysql3307.sock mysql3307.sock.lock performance_schema sys
- ]# mysqld_multi start 2 //启动实例2
- Installing new database in /dir1
- 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).
- 2019-06-13T10:56:57.199217Z 0 [Warning] InnoDB: New log files created, LSN=45790
- 2019-06-13T10:56:57.571839Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
- 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.
- 2019-06-13T10:56:57.724096Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
- 2019-06-13T10:56:57.724677Z 1 [Note] A temporary password is generated for root@localhost: qedTjrZs*8ma 管理员本机登录密码
- ]# ls /dir1 //查看数据库目录文件列表
- 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)查看端口
- ]# netstat -utnlp | grep :3307
- tcp6 0 0 :::3307 :::* LISTEN 1151/mysqld
- ]# netstat -utnlp | grep :3308
- tcp6 0 0 :::3308 :::* LISTEN 1339/mysqld
- ]# netstat -utnlp | grep mysqld
- tcp6 0 0 :::3307 :::* LISTEN 1151/mysqld
- tcp6 0 0 :::3308 :::* LISTEN 1339/mysqld
- # ps -C mysqld
- PID TTY TIME CMD
- 1151 pts/1 00:00:00 mysqld
- 1339 pts/1 00:00:00 mysqld
- [root@host57 ~]#
6)访问多实例
使用初始化密码登录实例1
- [root@host57 ~]#grep -i 'password' /var/log/mysqld.log //获取密码
- [root@host57 ~]# mysql -uroot -p'ly#LryiFE5fT' -S /dir1/mysqld1.sock
- mysql> alter user root@"localhost" identified by "123456"; //修改密码
- mysql> exit
- Bye
- [root@host57 ~]# mysql -uroot -p123456 -S /dir1/mysqld1.sock //新密码登录
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | mysql |
- | performance_schema |
- | sys |
- +--------------------+
- 4 rows in set (0.00 sec)
- mysql> create database db1; //创建新库db1
- Query OK, 1 row affected (0.00 sec)
- mysql> show databases; //查看已有的库
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | db1 | //db1库
- | mysql |
- | performance_schema |
- | sys |
- +--------------------+
- 5 rows in set (0.00 sec)
- mysql> exit //断开连接
- Bye
- [root@host56 ~]# ls /dir1 //查看数据库目录文件列表 有db1库的文件夹
- auto.cnf ibdata1 ibtmp1 mysqld1.pid performance_schema
- db1 ib_logfile0 mysql mysqld1.socket sys
- ib_buffer_pool ib_logfile1 mysqld1.err mysqld1.socket.lock
- [root@host56 ~]#
使用初始化密码登录实例2
- [root@host57 ~]# mysql -uroot -p'qedTjrZs*8ma' -S /dir2/mysqld2.sock
- mysql> alter user root@"localhost" identified by "654321"; //修改密码
- mysql> exit
- Bye
- [root@host57 ~]# mysql -uroot –p654321 -S /dir2/mysqld2.sock //新密码登录
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | mysql |
- | performance_schema |
- | sys |
- +--------------------+
- 4 rows in set (0.00 sec)
- mysql>
- mysql> create database db2;
- Query OK, 1 row affected (0.00 sec)
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | db2 |
- | mysql |
- | performance_schema |
- | sys |
- +--------------------+
- 5 rows in set (0.00 sec)
- mysql> exit
- Bye
- [root@host56 ~]# ls /dir2
- auto.cnf ib_logfile0 mysqld2.err performance_schema
- db2 ib_logfile1 mysqld2.pid sys
- ib_buffer_pool ibtmp1 mysqld2.socket
- ibdata1 mysql mysqld2.socket.lock
- [root@host56 ~]#
7)停止多实例服务
mysqld_multi --user=root --password=密码 stop 实例编号
- ]# netstat -utnlp | grep mysqld
- tcp6 0 0 :::3307 :::* LISTEN 1250/mysql
- tcp6 0 0 :::3308 :::* LISTEN 1451/mysql
- ]# mysqld_multi --user=root --password=123456 stop 2
- [root@host56 ~]# netstat -utnlp | grep mysqld
- tcp6 0 0 :::3307 :::* LISTEN 1250/mysql
- ]# mysql -uroot -p123456 -S /dir2/mysqld2.sock //拒绝连接
- mysql: [Warning] Using a password on the command line interface can be insecure.
- ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/dir2/mysqld2.sock' (2)