CentOS8 MySQL5.7多实例部署

前言：

本节的多实例部署是基于上一篇mysql5.7安装后的多实例配置，针对上篇文章的安装方式部署多实例。

上篇文章地址：https://blog.csdn.net/m0_70298159/article/details/124542226

1. /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] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock #datadir=/usr/local/src/3306/data/mysql #socket=/usr/local/src/3306/data/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysqld@3308] datadir=/var/lib/mysql3308 socket=/var/lib/mysql3308/mysql.sock innodb_flush_method=O_DIRECT innodb_file_per_table=1 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 user=mysql port=3308 log-error=/var/log/mysqld3308.log pid-file=/var/run/mysqld3308/mysqld.pid character-set-server=utf8 collation-server=utf8_general_ci explicit_defaults_for_timestamp=1

 配置3308多实例

2.启动3308

systemctl start mysqld@3308.service

3.检查状态

systemctl status mysqld@3308.service

查看临时密码，需要记录

在/var/log/mysqld3308.log文件中有一个默认临时密码，用户名是root（背景已高亮）

022-05-03T06:43:31.481277Z 0 [Warning] InnoDB: New log files created, LSN=45790 2022-05-03T06:43:31.638566Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2022-05-03T06:43:31.718081Z 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: 58f37d34-caac-11ec-8ae6-52540017e440. 2022-05-03T06:43:31.721800Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2022-05-03T06:43:32.164274Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher. 2022-05-03T06:43:32.164290Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher. 2022-05-03T06:43:32.164688Z 0 [Warning] CA certificate ca.pem is self signed. 2022-05-03T06:43:32.200774Z 1 [Note] A temporary password is generated for root@localhost: xy?P3Tol*uRh 2022-05-03T06:43:35.840126Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.38) starting as process 376244 ... 2022-05-03T06:43:35.844017Z 0 [Note] InnoDB: PUNCH HOLE support available 2022-05-03T06:43:35.844052Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2022-05-03T06:43:35.844058Z 0 [Note] InnoDB: Uses event mutexes 2022-05-03T06:43:35.844064Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier 2022-05-03T06:43:35.844069Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 2022-05-03T06:43:35.844075Z 0 [Note] InnoDB: Using Linux native AIO 2022-05-03T06:43:35.844379Z 0 [Note] InnoDB: Number of pools: 1 2022-05-03T06:43:35.844521Z 0 [Note] InnoDB: Using CPU crc32 instructions 2022-05-03T06:43:35.847758Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M 2022-05-03T06:43:35.855332Z 0 [Note] InnoDB: Completed initialization of buffer pool 2022-05-03T06:43:35.857731Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2022-05-03T06:43:35.882357Z 0 [Note] InnoDB: Highest supported file format is Barracuda. 2022-05-03T06:43:35.963001Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2022-05-03T06:43:35.963077Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2022-05-03T06:43:36.075775Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2022-05-03T06:43:36.076405Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active. 2022-05-03T06:43:36.076421Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active. 2022-05-03T06:43:36.076826Z 0 [Note] InnoDB: Waiting for purge to start 2022-05-03T06:43:36.127552Z 0 [Note] InnoDB: 5.7.38 started; log sequence number 2750122 2022-05-03T06:43:36.127927Z 0 [Note] Plugin 'FEDERATED' is disabled. 2022-05-03T06:43:36.128330Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql3308/ib_buffer_pool 2022-05-03T06:43:36.143001Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them. 2022-05-03T06:43:36.143020Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory. 2022-05-03T06:43:36.143026Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher. 2022-05-03T06:43:36.143029Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher. 2022-05-03T06:43:36.143438Z 0 [Warning] CA certificate ca.pem is self signed. 2022-05-03T06:43:36.143474Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory. 2022-05-03T06:43:36.143555Z 0 [Note] Server hostname (bind-address): '*'; port: 3308 2022-05-03T06:43:36.143585Z 0 [Note] IPv6 is available. 2022-05-03T06:43:36.143593Z 0 [Note] - '::' resolves to '::'; 2022-05-03T06:43:36.143613Z 0 [Note] Server socket created on IP: '::'. 2022-05-03T06:43:36.169812Z 0 [Note] Event Scheduler: Loaded 0 events 2022-05-03T06:43:36.170006Z 0 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.7.38' socket: '/var/lib/mysql3308/mysql.sock' port: 3308 MySQL Community Server (GPL) 2022-05-03T06:43:36.184757Z 0 [Note] InnoDB: Buffer pool(s) load completed at 220503 14:43:36 2022-05-03T07:07:47.257136Z 2 [Note] Access denied for user 'root'@'localhost' (using password: YES) 2022-05-03T07:45:04.943788Z 4 [Warning] IP address '111.18.144.95' could not be resolved: Name or service not known 2022-05-03T07:51:00.880890Z 8 [Note] Access denied for user 'root'@'localhost' (using password: YES) 2022-05-03T07:51:49.358394Z 9 [Note] Access denied for user 'root'@'localhost' (using password: YES) 2022-05-03T07:52:54.609152Z 10 [Note] Access denied for user 'root'@'localhost' (using password: YES) 2022-05-03T07:53:01.209998Z 11 [Note] Access denied for user 'root'@'localhost' (using password: YES) 2022-05-03T07:57:52.002871Z 13 [Note] Access denied for user 'root'@'111.18.144.95' (using password: YES) 2022-05-03T07:57:54.203454Z 14 [Note] Access denied for user 'root'@'111.18.144.95' (using password: YES) 2022-05-03T07:58:02.952891Z 15 [Note] Access denied for user 'root'@'111.18.144.95' (using password: YES) 2022-05-03T07:58:49.834383Z 16 [Note] Access denied for user 'root'@'111.18.144.95' (using password: YES) 2022-05-03T07:59:19.112153Z 17 [Note] Access denied for user 'root'@'111.18.144.95' (using password: YES)

登陆MYSQL5

mysql -uroot -p -S /var/lib/mysql3308/mysql.sock 

修改mysql5密码

MYSQL5密码规则：至少8位／大写／小写／特殊符号（@!～等）／数字

使用命令简化密码规则

SET GLOBAL validate_password_length=4;
SET GLOBAL validate_password_mixed_case_count=0; 
SET GLOBAL validate_password_policy=LOW;
SET GLOBAL validate_password_special_char_count=0;

执行修改密码命令(1234可以换成你想要的4位密码数字或字母)

ALTER USER 'root'@'localhost' IDENTIFIED BY '1234';

开机自启

systemctl enable mysqld@3308.service

远程访问创建密码

GRANT ALL ON *.* TO root@'%' IDENTIFIED BY '这里输入你想用的远程密码'

刷新修改

flush privileges

退出MYSQL5

exit

停止服务命令：

systemctl stop mysqld@3308.service

启动服务命令

systemctl start mysqld@3308.service

检查启动状态

systemctl status mysqld@3308.service

云服务器要在安全组开通3306端口，CentOS要在防火墙打开3306端口

云服务器自己打开端口，有问题找客服
 防火墙打开3306端口

ffirewall-cmd --permanent --zone=public --add-port=3306/tcp 

或者

firewall-cmd --zone=public --add-port=8080/tcp --permanent

刷新防火墙

firewall-cmd --reload

创建开发者用户

创建开发者用户

create user 用户名称 identified by '用户密码';

开发者增删改查权限
grant select,insert,update,delete on `your-db-name`.* to 用户名@'%' identified by '用户密码';

开发者远程访问

GRANT ALL PRIVILEGES on *.* to 用户名称@'%' IDENTIFIED BY '用户密码'
刷新修改

flush privileges

写在最后，使用Navicat等工具远程连接出现问题的解决方式：

登陆MySql5

mysql -uroot -p -S /var/lib/mysql3308/mysql.sock

进入MySql数据库

use mysql;

查看USER表

select host from user where user='root';

如图没有root用户，因此远程连接失败

 解决方法

localhost改成%允许所有地址使用这个用户访问

update user set host='%' where user = 'root' and host='localhost';

再查user表

 刷新MySql配置

flush privileges;

完毕！！！

下一篇文章讲述主从备份。。。