CentOS7.X使用mysql_multi部署MySQL5.7.34多实例

CentOS7.X使用mysql_multi部署MySQL5.7.34多实例

一、安装mysql

1.1 环境清理

[root@~]# rpm -e --nodeps mariadb-libs-*
[root@~]# rpm -e mysql* 
[root@~]# yum list installed | grep mariadb ---确认已经清理干净
[root@~]# yum list installed | grep mysql ---确认已经清理干净
[root@~]# mv /etc/my.cnf /etc/my.cnf.`date +%Y%m%d%H%M%S`.bak

1.2 安装依赖包

[root@ ~]# yum -y install bzr zlib-devel gcc-c++ ncurses ncurses-devel libev make cmake \
 gcc autoconf automake zlib libxml libgcrypt libtool bison perl perl-devel libaio libaio-devel \
 perl-Time-HiRes perl-DBD-MySQL perl-Digest-MD5 rsync perl-Data-Dumper net-tools \
 wget vim openssl openssl-devel

1.3 MySQL安装包下载

[root@~]#  cd /opt
[root@~]#  wget -c https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.14-linux-glibc2.5-x86_64.tar.gz
[root@~]#  tar zxvf mysql-5.7.14-linux-glibc2.5-x86_64.tar.gz
[root@~]#  mv mysql-5.7.14-linux-glibc2.5-x86_64 /usr/local/mysql

[root@master ~]#chown -R root:root /usr/local/mysql

[root@master ~]# mkdir -pv /usr/local/mysql/data{3306,3307}
[root@master ~]# mkdir -v /usr/local/mysql/data3306/{logs,data,binlog}
[root@master ~]# mkdir -v /usr/local/mysql/data3307/{logs,data,binlog}
[root@master ~]# mkdir /var/run/mysqld

## 配置 /etc/my.cnf
[root@master ~]# vim /etc/my.cnf
For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[[mysqld_multi]
 mysqld     = /usr/local/mysql/bin/mysqld_safe
 mysqladmin = /usr/local/mysql/bin/mysqladmin
 log = /usr/local/mysql/mysqld_multi.log
 #user       = root
 #password = 

[mysql]
no-auto-rehash

[mysqld3306]
 server_id  = 3306
 socket     = /tmp/mysql3306.sock
 port       = 3306
 pid-file   = /var/run/mysqld/mysqld3306.pid
 datadir    = /usr/local/mysql/data3306/data
 log-error  = /usr/local/mysql/data3306/logs/error.log
 user=root
 query_cache_size = 32M
 character-set-server=utf8
 query_cache_type=1

[mysqld3307]
 server_id  = 3307
 socket     =  /tmp/mysql3307.sock
 port       = 3307
 pid-file   =  /var/run/mysqld/mysqld3307.pid
 datadir    =  /usr/local/mysql/data3307/data
 log-error  =  /usr/local/mysql/data3307/logs/error.log
 user = root
 query_cache_size = 32M
 character-set-server=utf8
 query_cache_type=1

[mysqldump]
 quick
 max_allowed_packet = 32M
"/etc/my.cnf" 35L, 853C written

二、配置环境变量

2.1 配置环境变量

[root@~]#  echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile
[root@~]#  tail -1 /etc/profile
[root@~]#  source /etc/profile

三、实例初始化

3.1 实例初始化及 操作实例初始化

[root@master ~]# mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql/ --user=root --datadir=/usr/local/mysql/data3306/data/ > /tmp/3306.log 2>&1
#2>&1 表示(>1)标准输出和 (2>)错误输出文件都追加到 /tmp/3306.log
[root@master /]# tail -100f /tmp/3306.log 
2021-09-30T07:17:29.539549Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-09-30T07:17:29.920264Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-09-30T07:17:29.963230Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-09-30T07:17:30.023336Z 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: 790ff03c-21be-11ec-83b9-08002735bfcf.
2021-09-30T07:17:30.024237Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-09-30T07:17:30.024836Z 1 [Note] A temporary password is generated for root@localhost: *tVZ0wfp-BOm（Ps:3306数据库临时密码）

3.2生成SSL文件

[root@master ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data3306/data
Ignoring -days; not generating a certificate
Generating a RSA private key
...............+++++
......................................................+++++
writing new private key to 'ca-key.pem'
-----
Ignoring -days; not generating a certificate
Generating a RSA private key
.............+++++
............................................+++++
writing new private key to 'server-key.pem'
-----
Ignoring -days; not generating a certificate
Generating a RSA private key
.....+++++
.............+++++
 [root@master ~]# tail -100f /usr/local/mysql/data3306/logs/error.log
 
#3307端口同理 

3.3 实例启动

 [root@master ~]# mysqld_multi start
 Reporting MySQL servers
 MySQL server from group: mysqld3306 is running
 MySQL server from group: mysqld3307 is running

3.3.1单例启动

 [root@master ~]# mysqld_multi start 3306
 [root@master ~]# mysqld_multi report 3306
 Reporting MySQL servers
 MySQL server from group: mysqld3306 is running

同理

 [root@master ~]# mysqld_multi start 3307
 [root@master ~]# mysqld_multi report 3307
 Reporting MySQL servers
 MySQL server from group: mysqld3306 is running

关闭命令

 [root@master ~]# killall mysqld
 [root@master ~]# ps -ef|grep mysqld 

3.4 密码修改

方法一（操作简单）：

[root@master ~]# mysqladmin -uroot -p"*tVZ0wfp-BOm"  password "hadoop" -S /tmp/mysql3306.sock    
[root@master ~]# mysqladmin -uroot -p"ZtiPwBene9+R" password "hadoop" -S /tmp/mysql3307.sock

方法二（进入mysql命令行操作）：

[root@master ~] #  mysql -uroot -p -S /tmp/mysql3306.sock
Enter password: 
mysql>create database testone default character set utf8 collate utf8_bin;
mysql> grant select,insert,update,delete,create,alter,execute on testone.* to 'testone'@'%' identified by '密码';
mysql> flush privileges;
mysql> exit

[root@master ~]#  mysql -uroot -p -S /tmp/mysql3307.sock
Enter password: 
mysql>create database testone default character set utf8 collate utf8_bin;
mysql> grant select,insert,update,delete,create,alter,execute on testone.* to 'testone'@'%' identified by '密码';
mysql> flush privileges;
mysql> exit

四、端口开放

4.1 防火墙配置 根据实际要求，添加开放端口

[root@master ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
[root@master ~]# firewall-cmd --zone=public --add-port=3307/tcp --permanent

4.1.1重新载入

[root@master ~]# firewall-cmd --reload

————到这里实例数据库基本操作已经完成
如需数据测试，采用以下操作即可

备注：my.cnf完整版请查看:

https://blog.csdn.net/weixin_38504735/article/details/120625827

五、数据测试（可测试）：

5.1.、数据导入
（1）将待导入的数据库脚本（test.sql、testtwo.sql）文件（名称根据实际情况操作）上传至/data/路径下（2）使用命令确认两实例已经处于运行状态

[root@master ~]#  mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running

5.2、进入testone的mysql数据库创建testone数据库实例、用户名及密码并导入数据

[root@master ~]#  cd /data/
[root@master ~]#  mysql -uroot -p -S /tmp/mysql3306.sock
Enter password: 
mysql>create database testone default character set utf8 collate utf8_bin;
mysql> grant select,insert,update,delete,create,alter,execute on testone.* to 'testone'@'%' identified by '密码';
mysql> flush privileges;
mysql> exit
[root@master ~]#  mysql -uroot -p -S /tmp/mysql3306.sock one < /usr/local/mysql/data3306/data/test.sql

5.3、进入testtwo的mysql数据库，创建testtwo服务数据库实例、用户名与密码并导入数据

[root@master ~]#  mysql -uroot -p -S /tmp/mysql3307.sock
Enter password:
mysql> create database testtwo default character set utf8 collate utf8_bin;
mysql> grant select,insert,update,delete,create,alter,execute on testtwo.* to 'testtwo'@'%' identified by '密码';
mysql> flush privileges;
mysql> exit
[root@master ~]# mysql -uroot -p -S /tmp/mysql3307.sock two < /usr/local/mysql/data3307/testtwo.sql