1.MySQL多实例概述
MySQL多实例就是在一台linux服务器上开启多个不同的服务端口，运行多个MySQL服务进程，通过不同的socket监听不同的服务端口来提供各自的服务。
在同一环境下部署多个数据库，需主要以下几点
配置文件安装路径不能相同
数据库目录不能相同
启动脚本不能同名
端口不能相同
socket文件的生产路径不能相同
2.MySQL多实例部署
2.1部署环境
CentOS Linux release 7.9.2009 (Core)
MySQL-5.6.50
2.2部署mysql服务
2.2.1安装MySQL依赖
# yum -y install gcc gcc-c++ autoconf bison \
cmake zlib* fiex* libxml* ncurses-devel \
libmcrypt* libtool-ltdl-devel* automake \
openssl openssl-devel pcre pcre-devel
2.2.2源码安装MySQL
# tar -xf mysql-5.6.50.tar.gz
# cd mysql-5.6.50
# 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 install
2.2.4环境变量
# echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile
# source /etc/profile
2.2.5创建工作目录
# mkdir -p /data/mysql/{mysql_3306,mysql_3307,mysql_3308}/{data,log,tmp}
# chown -R mysql:mysql /data/mysql/
# useradd mysql
2.2.6修改配置文件my.cnf
# cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
# 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
[mysqld3308]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/data/mysql/mysql_3308/data
port=3308
server_id=3308
socket=/tmp/mysql_3308.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql_3308/log/slow.log
log-error = /data/mysql/mysql_3308/log/error.log
binlog_format = mixed
log-bin = /data/mysql/mysql_3307/log/mysql3308_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
[mysqldump3308]
quick
max_allowed_packet = 1024M
[mysql3308]
no-auto-rehash
[myisamchk3308]
key_buffer_size = 32M
sort_buffer_size = 768K
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy3308]
interactive-timeout
2.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
# /usr/local/mysql/scripts/mysql_install_db \
--basedir=/usr/local/mysql/ \
--datadir=/data/mysql/mysql_3308/data \
--defaults-file=/etc/my.cnf
如下提示表示初始化成功：
[root@Template ~]# ll /data/mysql/mysql_3306/data/total 77840-rw-rw---- 1 mysql mysql 56 Dec 24 20:14 auto.cnf-rw-rw---- 1 mysql mysql 12582912 Dec 24 20:14 ibdata1-rw-rw---- 1 mysql mysql 33554432 Dec 24 20:14 ib_logfile0-rw-rw---- 1 mysql mysql 33554432 Dec 24 20:14 ib_logfile1drwx------ 2 mysql mysql 4096 Dec 24 20:13 mysqldrwx------ 2 mysql mysql 4096 Dec 24 20:13 performance_schema-rw-rw---- 1 mysql mysql 6 Dec 24 20:14 Template.piddrwx------ 2 mysql mysql 6 Dec 24 20:13 test
2.4启动多实例
# /usr/local/mysql/bin/mysqld_multi start #开启多实例
# /usr/local/mysql/bin/mysqld_multi report #查看全部实例状态
# /usr/local/mysql/bin/mysqld_multi start 3306 #启动单个实例
# /usr/local/mysql/bin/mysqld_multi stop 3306 #停止单个实例
2.5修改实例密码（特别注意是初始密码为空）
# mysql -S /tmp/mysql_3306.sock #其他实例均按照这个方式处理
mysql> set password for root@'localhost'=password('123456');
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
# mysql -uroot -h127.0.0.1 -P3306 -p #创建一个外部连接账号
mysql> grant all on *.* to admin@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
测试远程连接是否正常
2.6部署总结
首先必须安装mysql服务；
my.cnf也有一些公共配置必须配置[mysqld_multi] [mysqld]；
通过mysqld类型加后缀来区别各个实例配置；
通过mysql_install_db来实现各个实例初始化；
注意各个实例目录的权限；
首次连接实例方式需特别注意。
