1、概述
对于中小型企业来说，各方面都希望压缩成本，所以在测试环境，开发环境使用多个项目环境或者设计数据库读写分离都是这一台服务器。那么针对应该怎么解决呢
MySQL多实例就是在一台linux服务器上开启多个不同的服务端口，运行多个MySQL服务进程，通过不同的socket监听不同的服务端口来提供各自的服务。
在同一环境下部署多个数据库，需主要以下几点
- 配置文件安装路径不能相同
- 数据库目录不能相同
- 启动脚本不能同名
- 端口不能相同
- socket文件的生产路径不能相同
mysql软件包：
链接：网盘地址
提取码：gEt5
--来自百度网盘超级会员V5的分享
2、MySQL环境部署
2.1、安装MySQL依赖
# yum install gcc gcc-c++ autoconf bison cmake zlib* fiex* libxml* ncurses-devel libmcrypt* libtool-ltdl-devel* automake openssl openssl-devel pcre pcre-devel -y2.2、MySQL源码安装
# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
# make
# make
2.3、环境变量配置
# echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile
# source /etc/profile
3、MySQL多实例配置
3.1、创建工作目录
# mkdir -p /data/mysql/{mysql_3306,mysql_3307}/{data,log,tmp}
# useradd mysql
# chown -R mysql:mysql /data/mysql/
3.2、修改配置文件
# vim /etc/my.cnf
[client]
port=3306
socket=/tmp/mysql.sock
[mysqld_multi] #多实例配置
mysqld = /usr/local/mysql /bin/mysqld_safe
mysqladmin = /usr/local/mysql /bin/mysqladmin
log = /data/mysql/mysqld_multi.log
[mysqld] #多实例配置
user=mysql
basedir = /usr/local/mysql
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
key_buffer_size = 32M
wait_timeout=3600
max_allowed_packet = 200M
table_open_cache = 128
sort_buffer_size = 768K
net_buffer_length = 8K
read_buffer_size = 768K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
thread_cache_size = 16
query_cache_size = 16M
tmp_table_size = 32M
performance_schema_max_table_instances = 500
max_connections = 1000
max_connect_errors = 100
open_files_limit = 65535
[mysqld3306]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/data/mysql/mysql_3306/data
port=3306
server_id=3306
socket=/tmp/mysql_3306.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql_3306/log/slow.log
log-error = /data/mysql/mysql_3306/log/error.log
binlog_format = mixed
log-bin = /data/mysql/mysql_3306/log/mysql3306_bin
innodb_file_per_table = 1
innodb_data_file_path = ibdata1:10M:autoextend
innodb_buffer_pool_size = 128M
innodb_log_file_size = 32M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump3306]
quick
max_allowed_packet = 1024M
[mysql3306]
no-auto-rehash
[myisamchk3306]
key_buffer_size = 32M
sort_buffer_size = 768K
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy3306]
interactive-timeout
[mysqld3307]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/data/mysql/mysql_3307/data
port=3307
server_id=3307
socket=/tmp/mysql_3307.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql_3307/log/slow.log
log-error = /data/mysql/mysql_3307/log/error.log
binlog_format = mixed
log-bin = /data/mysql/mysql_3307/log/mysql3307_bin
innodb_file_per_table = 1
innodb_data_file_path = ibdata1:10M:autoextend
innodb_buffer_pool_size = 128M
innodb_log_file_size = 32M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump3307]
quick
max_allowed_packet = 1024M
[mysql3307]
no-auto-rehash
[myisamchk3307]
key_buffer_size = 32M
sort_buffer_size = 768K
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy3307]
interactive-timeout
3.3、初始化数据库
# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3306/data --defaults-file=/etc/my.cnf
# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3307/data --defaults-file=/etc/my.cnf
如下图表示安装成功
3.4、启动多实例
# /usr/local/mysql/bin/mysqld_multi start
# /usr/local/mysql/bin/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
3.5、修改实例密码
# mysql -S /tmp/mysql_3306.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.44-log Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set password for root@'localhost'=password('123456');
mysql> flush privileges;
# mysql -S /tmp/mysql_3307.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.44-log Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set password for root@'localhost'=password('123456');
登陆MySQL
# mysql -uroot -h127.0.0.1 -P3306 -p #创建一个外部连接账号
mysql> grant all on *.* to admin@'%' identified by '123456';
mysql> flush privileges;
4、部署总结
- 首先必须安装mysql服务；
- my.cnf也有一些公共配置必须配置[mysqld_multi] [mysqld]；
- 通过mysqld类型加后缀来区别各个实例配置；
- 通过mysql_install_db来实现各个实例初始化；
- 注意各个实例目录的权限；
- 首次连接实例方式需特别注意。
