返回
网站建设知识
通过官方自带的mysqld_multi来实现mysql多实例
2025-07-22 10:02  点击:0

软件环境

**操作系统：**Centos7 64bit

**数据库：**MySQL5.6

mysqld_multi的配置

[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 = 3306pid-file = /data/dbdata_3306/3306.pid   #文件不用事先创建datadir = /data/dbdata_3306     #文件不用事先创建user = mysqlskip-name-resolvelower_case_table_names=1innodb_file_per_table=1back_log = 50max_connections = 300max_connect_errors = 1000table_open_cache = 2048max_allowed_packet = 16Mbinlog_cache_size = 2Mmax_heap_table_size = 64Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 64thread_concurrency = 8query_cache_size = 64Mquery_cache_limit = 2Mft_min_word_len = 4default-storage-engine = innodbthread_stack = 192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 64Mlog-bin=mysql-binbinlog_format=mixedslow_query_loglong_query_time = 1server-id = 1key_buffer_size = 8Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recoverinnodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 200Minnodb_data_file_path = ibdata1:10M:autoextendinnodb_file_io_threads = 8innodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 16Minnodb_log_file_size = 512Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 60innodb_lock_wait_timeout = 120#以下为编码设置init_connect='SET collation_connection = utf8_unicode_ci'init_connect='SET NAMES utf8'character-set-server=utf8collation-server=utf8_unicode_ciskip-character-set-client-handshake#以上为编码设置[mysqld2]socket = /data/dbdata_3307/mysql.sockport = 3307pid-file = /data/dbdata_3307/3307.piddatadir = /data/dbdata_3307user = mysqlskip-name-resolvelower_case_table_names=1innodb_file_per_table=1back_log = 50max_connections = 300max_connect_errors = 1000table_open_cache = 2048max_allowed_packet = 16Mbinlog_cache_size = 2Mmax_heap_table_size = 64Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 64thread_concurrency = 8query_cache_size = 64Mquery_cache_limit = 2Mft_min_word_len = 4default-storage-engine = innodbthread_stack = 192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 64Mlog-bin=mysql-binbinlog_format=mixedslow_query_loglong_query_time = 1server-id = 1key_buffer_size = 8Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recoverinnodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 200Minnodb_data_file_path = ibdata1:10M:autoextendinnodb_file_io_threads = 8innodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 16Minnodb_log_file_size = 512Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 60innodb_lock_wait_timeout = 120init_connect='SET collation_connection = utf8_unicode_ci'init_connect='SET NAMES utf8'character-set-server=utf8collation-server=utf8_unicode_ciskip-character-set-client-handshake[mysqld3]socket = /data/dbdata_3308/mysql.sockport = 3308pid-file = /data/dbdata_3308/3308.piddatadir = /data/dbdata_3308user = mysqlskip-name-resolvelower_case_table_names=1innodb_file_per_table=1back_log = 50max_connections = 300max_connect_errors = 1000table_open_cache = 2048max_allowed_packet = 16Mbinlog_cache_size = 2Mmax_heap_table_size = 64Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 64thread_concurrency = 8query_cache_size = 64Mquery_cache_limit = 2Mft_min_word_len = 4default-storage-engine = innodbthread_stack = 192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 64Mlog-bin=mysql-binbinlog_format=mixedslow_query_loglong_query_time = 1server-id = 1key_buffer_size = 8Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recoverinnodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 200Minnodb_data_file_path = ibdata1:10M:autoextendinnodb_file_io_threads = 8innodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 16Minnodb_log_file_size = 512Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 60innodb_lock_wait_timeout = 120init_connect='SET collation_connection = utf8_unicode_ci'init_connect='SET NAMES utf8'character-set-server=utf8collation-server=utf8_unicode_ciskip-character-set-client-handshake[mysqldump]quickmax_allowed_packet = 256M[mysql]no-auto-rehashprompt=\\u@\\d \\R:\\m>[myisamchk]key_buffer_size = 512Msort_buffer_size = 512Mread_buffer = 8Mwrite_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