这篇文章主要介绍了CentOS7.5使用mysql_multi方式安装MySQL5.7.28多实例,非常不错,具有一定的参考借鉴价值,需要的朋友可以参考下

因使用源码安装的MySQL5.7.28多实例,在导入数据库时会出现问题,所以重新研究使用mysql_multi的方法来管理多实例,经过测试环境验证之后,在各方面使用上特别在备份还原上,没有报MySQL5.7.28多实例的问题,踩了不少坑,这里我将我的部署过程分享下,如果在哪里出问题的,还请多多指正与指导,谢谢!!

参考文章:Centos7.5安装mysql5.7.24二进制包方式部署

https://www.lovean.com/article/151867.htm

本从就直接从2.7章节开始安装mysql多实例,具体部署过程如下:

2.7 安装mysql多实例

2.7.1. 创建软件安装目录(部署路径请根据实际修改)

[root@~]# mkdir -pv /data/mysql/{3306,3307}
[root@~]# mkdir -v /data/mysql/3306/{logs,data,binlog}
[root@~]# mkdir -v /data/mysql/3307/{logs,data,binlog}

2.7.2. MySQL安装包下载

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

2.7.3. MySQL参数配置

 配置my.cnf参数文件

(1)server_id=3306与server_id=3307数值请根据实际配置,注意配置的id值与局域网内其他各实例所配置的数值不可以冲突;

(2)max_connections=1000配置MySQL数据库的最大连接数,根据实际需要配置,其他参数的优化根据实际需要修改或添

加;

(3)配置文件全部内容如下

[root@~]# vim /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /data/mysql/mysqld_multi.log 
#user=root
#pass=

[mysql]
prompt="\u@jsshapp \R:\m:\s [\d]> "
no-auto-rehash

[mysqld3306]
user = mysql
port = 3306
symbolic-links = 0
#basedir = /usr/
datadir = /data/mysql/3306/data
socket = /data/mysql/3306/mysql3306.sock
pid-file = /data/mysql/3306/mysqld3306.pid
server_id = 3306
character_set_server = utf8
max_connections = 1000
skip_name_resolve = 1
open_files_limit = 65536
thread_cache_size = 64
table_open_cache = 4096
table_definition_cache = 1024
table_open_cache_instances = 64
max_prepared_stmt_count = 1048576
explicit_defaults_for_timestamp = true
log_timestamps = system

binlog_format = row
log_bin = /data/mysql/3306/binlog/mysql-bin
binlog_rows_query_log_events = on
expire_logs_days = 7
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
sync_binlog = 1
log_bin_trust_function_creators = 1

slow_query_log = on
slow_query_log_file = /data/mysql/3306/data/slow.log
log-error = /data/mysql/3306/logs/error.log
log_queries_not_using_indexes = on
long_query_time = 1.000000

gtid_mode = on
enforce_gtid_consistency = on

default_storage_engine = innodb
default_tmp_storage_engine = innodb
innodb_data_file_path = ibdata1:12M:autoextend:max:2000M
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2000M
innodb_buffer_pool_filename = ib_buffer_pool
innodb_log_files_in_group = 3
innodb_log_file_size = 512M
innodb_online_alter_log_max_size = 1024M
innodb_open_files = 4096
innodb_page_size = 16k
innodb_thread_concurrency = 0
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_print_all_deadlocks = on
innodb_lock_wait_timeout = 20
innodb_spin_wait_delay = 128
innodb_autoinc_lock_mode = 2
innodb_io_capacity = 200
innodb_io_capacity_max = 2000
#innodb_flush_neighbors = 
innodb_log_buffer_size = 8M
innodb_flush_log_at_timeout = 1
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 1024M
innodb_buffer_pool_instances = 4
autocommit = 1
innodb_buffer_pool_dump_pct = 25
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

[mysqld3307]
user = mysql
port = 3307
symbolic-links = 0
lower_case_table_names = 1
#basedir = /usr/
datadir = /data/mysql/3307/data
socket = /data/mysql/3307/mysql3307.sock
pid-file = /data/mysql/3307/mysqld3307.pid
server_id = 3307
character_set_server = utf8
max_connections = 1000
skip_name_resolve = 1
open_files_limit = 65536
thread_cache_size = 64
table_open_cache = 4096
table_definition_cache = 1024
table_open_cache_instances = 64
max_prepared_stmt_count = 1048576
explicit_defaults_for_timestamp = true
log_timestamps = system

binlog_format = row
log_bin = /data/mysql/3307/binlog/mysql-bin
binlog_rows_query_log_events = on
expire_logs_days = 7
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
sync_binlog = 1

slow_query_log = on
slow_query_log_file = /data/mysql/3307/data/slow.log
log-error = /data/mysql/3307/logs/error.log
log_queries_not_using_indexes = on
long_query_time = 1.000000

gtid_mode = on
enforce_gtid_consistency = on

default_storage_engine = innodb
default_tmp_storage_engine = innodb
innodb_data_file_path = ibdata1:12M:autoextend:max:2000M
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2000M
innodb_buffer_pool_filename = ib_buffer_pool
innodb_log_files_in_group = 3
innodb_log_file_size = 512M
innodb_online_alter_log_max_size = 1024M
innodb_open_files = 4096
innodb_page_size = 16k
innodb_thread_concurrency = 0
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_print_all_deadlocks = on
innodb_lock_wait_timeout = 20
innodb_spin_wait_delay = 128
innodb_autoinc_lock_mode = 2
innodb_io_capacity = 200
innodb_io_capacity_max = 2000
#innodb_flush_neighbors = 
innodb_log_buffer_size = 8M
innodb_flush_log_at_timeout = 1
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 1024M
innodb_buffer_pool_instances = 4
autocommit = 1
innodb_buffer_pool_dump_pct = 25
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

[mysqldump]
quick
max_allowed_packet = 32M

2.7.4. 配置MySQL环境变量

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

2.7.5. testone实例初始化

 操作实例初始化

[root@~]# mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql/ --user=mysql --datadir=/data/mysql/3306/data/ > /tmp/3306.log 2>&1
[root@~]# tail -100f /tmp/3306.log
---使用tail命令查看初始化日志,有出现如下内容,即表示初始化完成(其中#5+t+xYW+<t?即为root用户的临时密码)
A temporary password is generated for root@localhost: #5+t+xYW+<t?

 生成ssl文件

[root@~]# mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3306/data

 启动testone实例

[root@~]# mysqld_multi start 3306
[root@~]# tail -100f /data/mysql/3306/logs/error.log 
----使用tail命令查看启动日志,有出现即表示启动成功

Version: '5.7.28-log' socket: '/data/mysql/3306/mysql3306.sock' port: 3306 MySQL Community Server (GPL)

 修改root密码

[root@~]# less /tmp/3306.log | grep 'A temporary password'
[root@~]# mysql -uroot -p -S /data/mysql/3306/mysql3306.sock
Enter password:
mysql> alter user 'root'@'localhost' identified by '统一密码';
mysql> flush privileges;
mysql> exit;

 验证 testone root用户统一密码(界面正常输出information_schema内容表示正常)

[root@~]# mysql -uroot -p -S /data/mysql/3306/mysql3306.sock -e "show databases;" | grep information_schema
Enter password:

 修改my.cnf配置文件,将修改的统一密码,添加到配置文件中

[root@~]# sed -i "s@^#user=root@user=root@g" /etc/my.cnf
[root@~]# sed -i "s@^#pass=@pass=统一密码@g" /etc/my.cnf
[root@~]# cat /etc/my.cnf | grep pass= ---使用cat命令查看配置文件pass字段输出的结果是否一致

 停止testone实例

[root@~]# mysqld_multi stop 3306
[root@~]# netstat -tnlp|grep 3306 ---输入结果为空表示服务停止正常

2.7.6. testtwo实例初始化

 操作实例初始化

[root@~]# mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql/ --user=mysql --datadir=/data/mysql/3307/data/ > /tmp/3307.log 2>&1
[root@~]# tail -100f /tmp/3307.log
---使用tail命令查看初始化日志,有出现如下内容,即表示初始化完成(其中-pn>t;Ye)Ay6=I即为root用户的临时密码)
A temporary password is generated for root@localhost: -pn>t;Ye)Ay6=I
 生成ssl文件

[root@~]# mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3307/data

 启动testwo实例

[root@~]# mysqld_multi start 3307
[root@~]# tail -100f /data/mysql/3307/logs/error.log
Version: '5.7.28-log' socket: '/data/mysql/3307/mysql3307.sock' port: 3307 MySQL Community Server (GPL)

 修改root密码

[root@~]# less /tmp/3307.log|grep 'A temporary password'
[root@~]# mysql -uroot -p -S /data/mysql/3307/mysql3307.sock
Enter password:
mysql> alter user 'root'@'localhost' identified by '统一密码';
mysql> flush privileges;
mysql> exit;

 验证 testtwo root用户统一密码(界面正常输出information_schema内容表示正常)

[root@~]# mysql -uroot -p -S /data/mysql/3307/mysql3307.sock -e "show databases;" | grep information_schema

Enter password:

 停止testtwo实例

[root@~]# mysqld_multi stop 3307
[root@~]# netstat -tnlp|grep 3307 ---输入结果为空表示服务停止正常

2.7.7. mysqld_multi多实例管理命令

 启动全部实例

[root@~]# mysqld_multi start

 停止单个实例

[root@~]# mysqld_multi stop 3306
[root@~]# mysqld_multi stop 3307

 启动单个实例

[root@~]# mysqld_multi start 3306
[root@~]# mysqld_multi start 3307

 查看全部实例的状态(is running)

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

 查看单个实例状态

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

 停止全部实例

[root@~]# mysqld_multi stop

2.7.8. 数据导入

(1)将待导入的数据库脚本(test.sql、testtwo.sql)文件(名称根据实际情况操作)上传至/data/路径下

(2)使用命令确认两实例已经处于运行状态

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

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

[root@~]# cd /data/
[root@~]# mysql -uroot -p -S /data/mysql/3306/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@~]# mysql -uroot -p -S /data/mysql/3306/mysql3306.sock testone < /data/test.sql

(3)进入testtwo的mysql数据库,创建testtwo服务数据库实例、用户名与密码并导入数据

[root@~]# mysql -uroot -p -S /data/mysql/3307/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@~]# mysql -uroot -p -S /data/mysql/3307/mysql3307.sock testtwo < /data/testtwo.sql

2.7.9. 防火墙配置

 根据实际要求,添加开放端口

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

 重新载入

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

总结

以上所述是小编给大家介绍的CentOS7.5使用mysql_multi方式安装MySQL5.7.28多实例,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对爱安网网站的支持!如果你觉得本文对你有帮助,欢迎转载,烦请注明出处,谢谢!

最新资讯
5G第一个演进版本标准完成:3GPP宣布R16标准冻结

5G第一个演进版本标准

国际通信标准组织3GPP宣布R16标准冻结,标志5G第一个演
保价5万寄古董遭损引赔偿纠纷 律师建议完善保价制度

保价5万寄古董遭损引

王永康建议,快递公司应当尽快改进完善保价制度,在客户进
特斯拉在华开放电动皮卡Cybertruck预订 订金1000元

特斯拉在华开放电动皮

预订者目前可以以64000元人民币(约合9000美元)的价格锁
欧洲消费者向苹果公施压 要求其就iPhone电池门进行赔偿

欧洲消费者向苹果公施

在苹果公司为保护老化电池而放慢iPhone手机速度三年后
苹果新功能追踪数据 遭欧洲16家广告协会反对

苹果新功能追踪数据

对于苹果要求应用程序在追踪用户行为前征得其额外同意
贾跃亭这回又是虚晃一枪?

贾跃亭这回又是虚晃一

乐视网随即澄清表示,无法判断补偿的可行性。如无意外,7
最新文章
从一个小需求感受Redis的独特魅力(需求设计)

从一个小需求感受Redi

Redis在实际应用中使用的非常广泛,本篇文章就从一个简
详解redis desktop manager安装及连接方式

详解redis desktop ma

这篇文章主要介绍了redis desktop manager安装及连接
Redis集群下过期key监听的实现代码

Redis集群下过期key监

这篇文章主要介绍了Redis集群下过期key监听的实现代码
Redis集群增加节点与删除节点的方法详解

Redis集群增加节点与

这篇文章主要给大家介绍了关于Redis集群增加节点与删
Linux 下redis5.0.0安装教程详解

Linux 下redis5.0.0安

这篇文章主要介绍了Linux 下redis5.0.0安装教程,本文图
Redis 实现“附近的人”功能

Redis 实现“附近的人

Redis基于geohash和有序集合提供了地理位置相关功能。