十九、配置MySQL多实例

一、什么是多实例
– 在一台物理主机上运行多个数据库服务
为什么要使用多实例
– 节约运维成本
– 提高硬件利用率
二、配置mysql多实例。
2.1要求在192.168.4.12上配置多实例：运行两个数据库服务：
第1个数据库服务 数据库目录 /dataone
服务端口号 3307
sock文件 /dataone/mysqld.sock
日志文件 /dataone/mysqld.log
-----------------------------------------------------------------
第2个数据库服务
数据库目录 /datatow
服务端口号 3308
sock文件 /datatwo/mysqld.sock
日志文件 /datatwo/mysqld.log
-------------------------------------------------------------------
2.2 环境准备
netstat -utnalp | grep :3307
netstat -utnalp | grep :3308
2.3 安装支持多实例服务的软件包
我们这儿运用的是 mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz这个软件包。
[root@client mysql]# tar -zxvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
[root@client mysql]# mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql
#非必要操作
[root@client mysql]# ls /usr/local/mysql/
bin COPYING docs include lib man README share support-files
[root@client ~]# ls /usr/local/mysql/bin/
innochecksum mysqladmin mysqld_multi mysqlpump mysqlxtest
lz4_decompress mysqlbinlog mysqld_safe mysql_secure_installation perror
myisamchk mysqlcheck mysqldump mysqlshow replace
myisam_ftdump mysql_client_test_embedded mysqldumpslow mysqlslap resolveip
myisamlog mysql_config mysql_embedded mysql_ssl_rsa_setup resolve_stack_dump
myisampack mysql_config_editor mysqlimport mysqltest_embedded zlib_decompress
my_print_defaults mysqld mysql_install_db mysql_tzinfo_to_sql
mysql mysqld-debug mysql_plugin mysql_upgrade
2.4 修改环境变量
[root@client mysql]# echo "export PATH=/usr/local/mysql/bin:$PATH" >> /etc/profile
[root@client mysql]# source /etc/profile
[root@client mysql]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
2.5 编辑配置文件
[root@client ~]# rm -rf /etc/my.cnf
[root@client ~]# 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=/dataone #数据库目录，手动创建
socket=/dataone/mysqld.sock // 指定 sock 文件的路径和名称
log-error=/dataone/mysqld.log // 进程 pid 号文件位置
pid-file=/dataone/mysqld.pid // 错误日志位置

[mysqld2]
port=3308
datadir=/datatwo
socket=/datatwo/mysqld.sock
log-error=/datatwo/mysqld.log
pid-file=/datatwo/mysqld.pid
2.6 根据配置文件的设置，做相应的配置
2.6.1创建数据库目录
[root@client ~]# vim /etc/my.cnf
[root@client ~]# mkdir -p /dataone
[root@client ~]# mkdir -p /datatwo
2.6.2创建进程运行的所有者和组 mysql
[root@client ~]# useradd mysql
[root@client ~]# chown mysql:mysql /data*
2.7 初始化数据库
[root@client ~]# mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/dataone --initialize
------------------------------------------------------------------------------------------------------------------------------
2021-06-24T06:48:46.232075Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-06-24T06:48:47.985317Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-06-24T06:48:48.335934Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-06-24T06:48:48.405613Z 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: 3a6a6c09-d4b8-11eb-ba80-745208050301.
2021-06-24T06:48:48.408451Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-06-24T06:48:48.409075Z 1 [Note] A temporary password is generated for root@localhost: u/uq3gD6&wVP
[root@client ~]# ls /dataone/
auto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema sys
---------------------------------------------------------------------------------------------------------------------------------
[root@client ~]# mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/datatwo --initialize
2021-06-24T06:49:24.314957Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-06-24T06:49:27.202269Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-06-24T06:49:27.483369Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-06-24T06:49:27.546621Z 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: 51bedd9b-d4b8-11eb-bb9d-745208050301.
2021-06-24T06:49:27.551393Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-06-24T06:49:27.551980Z 1 [Note] A temporary password is generated for root@localhost: K,9KLzL)g<pR
[root@client ~]# ls /datatwo/
auto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema sys
-------------------------------------------------------------------------------------------------------------------------------------------
2.8 启动多实例服务
[root@client ~]# mysqld_multi start 1
[root@client ~]# mysqld_multi start 2
[root@client ~]# netstat -utnlp | grep :3308
tcp6 0 0 :::3308 :::* LISTEN 7897/mysqld
[root@client ~]# netstat -utnlp | grep :3307
tcp6 0 0 :::3307 :::* LISTEN 7739/mysqld
2.9 访问多实例服务
[root@client ~]# mysql -uroot -p'u/uq3gD6&wVP' -S /dataone/mysqld.sock
mysql> ALTER USER user() identified by "123456";
mysql> quit;
[root@client ~]# mysql -uroot -p'K,9KLzL)g<pR' -S /datatwo/mysqld.sock
mysql> ALTER USER user() identified by "123456";
mysql> quit;
2.10 停止启动的实例服务
[root@client ~]# mysqld_multi --user=root --password=123456 stop 1
备注：初始化授权库：
– 会提示 root 用户登录的初始化密码
[root@localhost bin]# ./mysqld --user=mysql --basedir= 软件安
装目录 --datadir= 数据库目录 – initialize // 初始化授权库
[root@stu ~]# mysqld_multi start 实例编号 // 启动实例进程
[root@localhost bin]# ./mysqld_multi --user=root --password=密码 stop 实例编号 // 停止实例进程
客户端访问：
[root@localhost bin]# ./mysql -uroot –p 初始密码 -S sock 文件
mysql> alter user user() identified by “ 新密码 ";
[root@localhost bin]# ./mysql –uroot –p 新密码 -S sock 文件