通过官方自带的mysqld_multi来实现mysql多实例

软件环境

**操作系统：**Centos7 64bit
**数据库：**MySQL5.6

mysqld_multi的配置

/etc/my.cnf的配置

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe       #mysqld_safe文件所在之处
mysqladmin = /usr/bin/mysqladmin    #mysqladmin文件所在之处
user = admin                #不需要admin登录，直接root登录也可以，不过admin登录也行
password = password
[mysqld1]
socket = /data/dbdata_3306/mysql.sock   #文件不用事先创建
port = 3306
pid-file = /data/dbdata_3306/3306.pid   #文件不用事先创建
datadir = /data/dbdata_3306     #文件不用事先创建
user = mysql
skip-name-resolve
lower_case_table_names=1
innodb_file_per_table=1
back_log = 50
max_connections = 300
max_connect_errors = 1000
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 2M
max_heap_table_size = 64M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 64
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default-storage-engine = innodb
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin=mysql-bin
binlog_format=mixed
slow_query_log
long_query_time = 1
server-id = 1
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 200M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 60
innodb_lock_wait_timeout = 120
#以下为编码设置
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
#以上为编码设置
[mysqld2]
socket = /data/dbdata_3307/mysql.sock
port = 3307
pid-file = /data/dbdata_3307/3307.pid
datadir = /data/dbdata_3307
user = mysql
skip-name-resolve
lower_case_table_names=1
innodb_file_per_table=1
back_log = 50
max_connections = 300
max_connect_errors = 1000
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 2M
max_heap_table_size = 64M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 64
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default-storage-engine = innodb
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin=mysql-bin
binlog_format=mixed
slow_query_log
long_query_time = 1
server-id = 1
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 200M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 60
innodb_lock_wait_timeout = 120
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
[mysqld3]
socket = /data/dbdata_3308/mysql.sock
port = 3308
pid-file = /data/dbdata_3308/3308.pid
datadir = /data/dbdata_3308
user = mysql
skip-name-resolve
lower_case_table_names=1
innodb_file_per_table=1
back_log = 50
max_connections = 300
max_connect_errors = 1000
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 2M
max_heap_table_size = 64M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 64
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default-storage-engine = innodb
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin=mysql-bin
binlog_format=mixed
slow_query_log
long_query_time = 1
server-id = 1
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 200M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 60
innodb_lock_wait_timeout = 120
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
[mysqldump]
quick
max_allowed_packet = 256M
[mysql]
no-auto-rehash
prompt=\\u@\\d \\R:\\m>
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192

mysqld_multi启动

一个个分开启动
“/usr/local/mysql/bin/mysqld_multi”是“mysqld_multi”文件所在之处，可用whereis mysqld_multi命令查找，“start 1”中的“1”对应/etc/my.cnf中[mysqld1]的“1”

/usr/local/mysql/bin/mysqld_multi start 1   
/usr/local/mysql/bin/mysqld_multi start 2
/usr/local/mysql/bin/mysqld_multi start 3

统一启动

/usr/local/mysql/bin/mysqld_multi start 1-3

创建登录三个实例的账号密码

其中“/usr/local/mysql/bin/mysqladmin”为“mysqladmin”文件所在之处，“/data/dbdata_3306/mysql.sock”等对应/etc/my.cnf中[mysqld1]下的“socket ”

/usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3306/mysql.sock
/usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3307/mysql.sock
/usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3308/mysql.sock

登录3308端口实例
“root”为上文设置的登录账号，“password”为密码，通过“-S /data/dbdata_3308/mysql.sock”指定连接的文件

mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sock

添加到开机自动启动

vi /etc/init.d/boot.local
/usr/local/mysql/bin/mysqld_multi start 1-3

远程使用Navicate测试

开放端口
开启端口：

firewall-cmd --permanent --add-port=80/tcp

–zone #作用域

–add-port=80/tcp #添加端口，格式为：端口/通讯协议

–permanent #永久生效，没有此参数重启后失效
重启防火墙：

firewall-cmd --reload

附加针对某ip开放端口

针对“192.168.110.1”开放80端口

firewall-cmd --permanent --add-rich-rule 'rule family=ipv4 source address=192.168.110.1 port port=80 protocol=tcp accept'

重启防火墙

mysql赋予远程登录权限
先用“root”登录需要连接的实例，然后使用一下命令：

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

其中“root”为登录账号，“123456”为密码，’%’表示所有ip都可以登录，可以在‘’里面指定特定ip可以登录，比如指定“192.168.110.1”登录，命令如下：

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.110.1' IDENTIFIED BY '123456' WITH GRANT OPTION;

**

注意事项

**
以上设置成功的前提是数据库的服务是“mysqld”，在ubuntu16.04中使用“yum install mysql”命令安装mysql5.7时，数据库的服务是“mysql”，所以会报错。可通过以下命令查看服务：

netstat -tlnp