MySQL5.5多实例编译安装——多配置文件

2025-04-17 技术教程

一、什么是MySQL多实例？
MySQL多实例简单的说就是在一台服务器上安装一套MySQL程序，通过不同的端口对外提供访问，多实例不仅节省物理主机成本，还有效提升了单台物理主机的CPU、磁盘I/O使用效率，而且还可以在多实例之间做部署数据库HA方案。
二、如何配置MySQL多实例？
配置mysql多实例有两种方式
1、根据官方提供的是通过mysqld_multi使用单独的配置文件来实现多实例，这种方式定制每个实例的配置不太方面，优点是管理起来很方便，集中管理。
2、使用多个配置文件和启动文件，配置文件之间的区别：server-id、socket文件的位置、配置路径和数据存放位置不同。初始化的时候只用不同的配置文件进行初始化数据库，启动时使用不同的启动文件来启动，这种方法逻辑和配置简单，但是不方便管理。
下面我们以第二种多实例的方法进行配置
三、多实例配置
MySQL安装的是mysql5.5.52版本，安装方法请看MySQL5.5.52编译安装
1、停止单实例mysql数据库

[root@db01~]#/etc/init.d/mysqldstopShuttingdownMySQL.SUCCESS!

2、禁止开机自启动

[root@db01~]#chkconfigmysqldoff[root@db01~]#chkconfig--listmysqldmysqld0:关闭1:关闭2:关闭3:关闭4:关闭5:关闭6:关闭

[root@db01~]#mkdir-p/data/{3306,3307}/data

需要特别说明一下，在多实例启动文件中，启动MySQL不同势力服务所需要执行的命令实质是有区别的，例如，启动3306实例命令如下

mysql_safe--defaults-file=/data/3306/mysql&>/dev/null

启动3307实例的命令如下：

mysql_safe--defaults-file=/data/3307/mysql&>/dev/null

下面看看多实例启动文件中，停止MySQL不同实例服务的实质命令
停止3306实例的命令如下：

mysqladmin-uroot-p123456-S/data/3306/mysql.sockshutdown

停止3307实例的命令如下：

mysqladmin-uroot-p123456-S/data/3307/mysql.sockshutdown

4、创建MySQL多实例的配置文件和启动文件
1）3306mysql实例配置文件

[root@db01~]#vim/data/3306/my.cnf[client]port=3306socket=/data/3306/mysql.sock[mysql]no-auto-rehash[mysqld]user=mysqlport=3306socket=/data/3306/mysql.sockbasedir=/application/mysqldatadir=/data/3306/dataopen_files_limit=1024back_log=600max_connections=800max_connect_errors=3000table_cache=614external-locking=FALSEmax_allowed_packet=8Msort_buffer_size=1Mjoin_buffer_size=1Mthread_cache_size=100thread_concurrency=2query_cache_size=2Mquery_cache_limit=1Mquery_cache_min_res_unit=2k#default_table_type=InnoDBthread_stack=192K#transaction_isolation=READ-COMMITTEDtmp_table_size=2Mmax_heap_table_size=2Mlong_query_time=1#log_long_format#log-error=/data/3306/error.log#log-slow-queries=/data/3306/slow.logpid-file=/data/3306/mysql.pidlog-bin=/data/3306/mysql-binrelay-log=/data/3306/relay-binrelay-log-info-file=/data/3306/relay-log.infobinlog_cache_size=1Mmax_binlog_cache_size=1Mmax_binlog_size=2Mexpire_logs_days=7key_buffer_size=16Mread_buffer_size=1Mread_rnd_buffer_size=1Mbulk_insert_buffer_size=1M#myisam_sort_buffer_size=1M#myisam_max_sort_file_size=10G#myisam_max_extra_sort_file_size=10G#myisam_repair_threads=1#myisam_recoverlower_case_table_names=1skip-name-resolveslave-skip-errors=1032,1062replicate-ignore-db=mysqlserver-id=1innodb_additional_mem_pool_size=4Minnodb_buffer_pool_size=32Minnodb_data_file_path=ibdata1:128M:autoextendinnodb_file_io_threads=4innodb_thread_concurrency=8innodb_flush_log_at_trx_commit=2innodb_log_buffer_size=2Minnodb_log_file_size=4Minnodb_log_files_in_group=3innodb_max_dirty_pages_pct=90innodb_lock_wait_timeout=120innodb_file_per_table=0[mysqldump]quickmax_allowed_packet=2M[mysqld_safe]log-error=/data/3306/mysql_3306.errpid-file=/data/3306/mysqld.pid

2）3307mysql实例配置文件

[root@db01~]#cp/data/3306/my.cnf/data/3307/my.cnf[root@db01~]#sed-i's#3306#3307#g'/data/3307/my.cnf[root@db01~]#sed-n/server-id/p/data/3307/my.cnfserver-id=1[root@db01~]#sed-i's#server-id=1#server-id=2#g'/data/3307/my.cnf[root@db01~]#cat/data/3307/my.cnf[client]port=3307socket=/data/3307/mysql.sock[mysql]no-auto-rehash[mysqld]user=mysqlport=3307socket=/data/3307/mysql.sockbasedir=/application/mysqldatadir=/data/3307/dataopen_files_limit=1024back_log=600max_connections=800max_connect_errors=3000table_cache=614external-locking=FALSEmax_allowed_packet=8Msort_buffer_size=1Mjoin_buffer_size=1Mthread_cache_size=100thread_concurrency=2query_cache_size=2Mquery_cache_limit=1Mquery_cache_min_res_unit=2k#default_table_type=InnoDBthread_stack=192K#transaction_isolation=READ-COMMITTEDtmp_table_size=2Mmax_heap_table_size=2Mlong_query_time=1#log_long_format#log-error=/data/3307/error.log#log-slow-queries=/data/3307/slow.logpid-file=/data/3307/mysql.pidlog-bin=/data/3307/mysql-binrelay-log=/data/3307/relay-binrelay-log-info-file=/data/3307/relay-log.infobinlog_cache_size=1Mmax_binlog_cache_size=1Mmax_binlog_size=2Mexpire_logs_days=7key_buffer_size=16Mread_buffer_size=1Mread_rnd_buffer_size=1Mbulk_insert_buffer_size=1M#myisam_sort_buffer_size=1M#myisam_max_sort_file_size=10G#myisam_max_extra_sort_file_size=10G#myisam_repair_threads=1#myisam_recoverlower_case_table_names=1skip-name-resolveslave-skip-errors=1032,1062replicate-ignore-db=mysqlserver-id=2innodb_additional_mem_pool_size=4Minnodb_buffer_pool_size=32Minnodb_data_file_path=ibdata1:128M:autoextendinnodb_file_io_threads=4innodb_thread_concurrency=8innodb_flush_log_at_trx_commit=2innodb_log_buffer_size=2Minnodb_log_file_size=4Minnodb_log_files_in_group=3innodb_max_dirty_pages_pct=90innodb_lock_wait_timeout=120innodb_file_per_table=0[mysqldump]quickmax_allowed_packet=2M[mysqld_safe]log-error=/data/3307/mysql_3307.errpid-file=/data/3307/mysqld.pid

5、MySQL多实例启动文件的创建和配置文件创建几乎一样，也可以通过vim命令来添加如下：
1）3306mysql实例启动文件

[root@db01~]#vim/data/3306/mysql#!/bin/bash#################################################Filename:mysql#Description:StartMySQLmultiinstancescript#Version:1.0#Date:2016/12/10#Author:xuanwiei#Email:1756112532@qq.com#################################################initport=3306mysql_user="root"mysql_pwd="123456"#这里将来是要修改为和数据库密码一致CmdPath="/application/mysql/bin"mysql_sock="/data/${port}/mysql.sock"#startupfunctionfunction_start_mysql(){if[!-e"$mysql_sock"];thenprintf"StartingMySQL...\n"/bin/sh${CmdPath}/mysqld_safe--defaults-file=/data/${port}/my.cnf2>&1>/dev/null&elseprintf"MySQLisrunning...\n"exitfi}#stopfunctionfunction_stop_mysql(){if[!-e"$mysql_sock"];thenprintf"MySQLisstopped...\n"exitelseprintf"StopingMySQL...\n"${CmdPath}/mysqladmin-u${mysql_user}-p${mysql_pwd}-S/data/${port}/mysql.sockshutdownfi}#restartfunctionfunction_restart_mysql(){printf"RestartingMySQL...\n"function_stop_mysqlsleep2function_start_mysql}case$1instart)function_start_mysql;;stop)function_stop_mysql;;restart)function_restart_mysql;;*)printf"Usage:/data/${port}/mysql{start|stop|restart}\n"esac

2）3307mysql实例启动文件

[root@db01~]#cp/data/3306/mysql/data/3307/mysql[root@db01~]#sed-i's#3306#3307#g'/data/3307/mysql[root@db01~]#cat/data/3307/mysql#!/bin/bash#################################################Filename:mysql#Description:StartMySQLmultiinstancescript#Version:1.0#Date:2016/12/10#Author:xuanwiei#Email:1756112532@qq.com#################################################initport=3307mysql_user="root"mysql_pwd="123456"CmdPath="/application/mysql/bin"mysql_sock="/data/${port}/mysql.sock"#startupfunctionfunction_start_mysql(){if[!-e"$mysql_sock"];thenprintf"StartingMySQL...\n"/bin/sh${CmdPath}/mysqld_safe--defaults-file=/data/${port}/my.cnf2>&1>/dev/null&elseprintf"MySQLisrunning...\n"exitfi}#stopfunctionfunction_stop_mysql(){if[!-e"$mysql_sock"];thenprintf"MySQLisstopped...\n"exitelseprintf"StopingMySQL...\n"${CmdPath}/mysqladmin-u${mysql_user}-p${mysql_pwd}-S/data/${port}/mysql.sockshutdownfi}#restartfunctionfunction_restart_mysql(){printf"RestartingMySQL...\n"function_stop_mysqlsleep2function_start_mysql}case$1instart)function_start_mysql;;stop)function_stop_mysql;;restart)function_restart_mysql;;*)printf"Usage:/data/${port}/mysql{start|stop|restart}\n"esac

6、配置MySQL多实例的文件权限
（1）通过下面的命令授权mysql用户和用户组管理整个多实例的根目录/data

[root@db01~]#chown-Rmysql.mysql/data

（2）通过下面的mysql多实例所有启动文件的mysql可执行，设置700权限最佳，注意不要用755权限，因为文件里有数据库管理员密码，会被读取到。

[root@db01scripts]#find/data/-typef-name"mysql"/data/3306/mysql/data/3307/mysql[root@db01scripts]#find/data/-typef-name"mysql"|xargschmod700[root@db01scripts]#find/data/-typef-name"mysql"|xargsls-l-rwx------1rootroot135912月1016:20/data/3306/mysql-rwx------1rootroot135912月1016:22/data/3307/mysql

7、初始化MySQL多实例的数据库文件
（1）初始化MySQL数据库
cd /application/mysql/scripts/ <==注意和MySQL5.1的路径不同，MySQL5.1不在MySQL bin路径下了
3306实例
/application/mysql/scripts/mysql_install_db \
--basedir=/application/mysql \
--datadir=/data/3306/data \
--user=mysql
3307实例
/application/mysql/scripts/mysql_install_db \
--basedir=/application/mysql \
--datadir=/data/3307/data \
--user=mysql
提示：--basedir=/application/mysql为MySQL的安装路径，--datadir为不同的实例数据目录
操作过程：

[root@db01~]#cd/application/mysql/scripts/[root@db01scripts]#/application/mysql/scripts/mysql_install_db\>--basedir=/application/mysql\>--datadir=/data/3306/data\>--user=mysqlWARNING:Thehost'db01'couldnotbelookedupwithresolveip.Thisprobablymeansthatyourlibclibrariesarenot100%compatiblewiththisbinaryMySQLversion.TheMySQLdaemon,mysqld,shouldworknormallywiththeexceptionthathostnameresolvingwillnotwork.ThismeansthatyoushoulduseIPaddressesinsteadofhostnameswhenspecifyingMySQLprivileges!InstallingMySQLsystemtables...16111714:14:14[Note]/application/mysql/bin/mysqld(mysqld5.5.52)startingasprocess46676...OKFillinghelptables...16111714:14:15[Note]/application/mysql/bin/mysqld(mysqld5.5.52)startingasprocess46683...OK

如果有两个ok，就表示初始化成功
其中WARNING: The host 'db01' could not be looked up with resolveip.
原因是因为db01没有在hosts文件中解析
解决：echo "172.16.1.52 db01" >>/etc/hosts

[root@db01scripts]#/application/mysql/scripts/mysql_install_db\>--basedir=/application/mysql\>--datadir=/data/3307/data\>--user=mysqlInstallingMySQLsystemtables...16111714:18:20[Note]/application/mysql/bin/mysqld(mysqld5.5.52)startingasprocess46733...OKFillinghelptables...16111714:18:21[Note]/application/mysql/bin/mysqld(mysqld5.5.52)startingasprocess46740...OK

如果有两个ok，就表示初始化成功
这次没用出现WARNING: The host 'db01' could not be looked up with resolveip.
（2）初始化数据库的原理及结果

[root@db01scripts]#tree/data/data├──3306│├──data││├──mysql│││├──columns_priv.frm│││├──columns_priv.MYD│││├──columns_priv.MYI│││├──db.frm│││├──db.MYD│││├──db.MYI│││├──event.frm│││├──event.MYD│││├──event.MYI│││├──func.frm│││├──func.MYD│││├──func.MYI…………………省略部分………………………………

（3）初始化故障
示例1：给出了警告信息“WARNING: The host 'db01' could not be looked up with resolveip.”
这个警告信息可以忽略，如果非要解决则需修改主机名解析

echo"172.16.1.52db01">>/etc/hosts

8、启动MySQL多实例数据库
第一个实例3306的启动命令
/data/3306/mysql start
第二个实例3307的启动命令
/data/3307/mysql start
现在检查MySQL多实例数据库是否成功启动
netstat -lntup|grep 330
操作过程：

[root@db01scripts]#/data/3306/mysqlUsage:/data/3306/mysql{start|stop|restart}[root@db01scripts]#/data/3306/mysqlstartStartingMySQL...[root@db01scripts]#/data/3307/mysqlstartStartingMySQL...

查看端口

[root@db01scripts]#ss-nlutp|grep330tcpLISTEN0600*:3306*:*users:(("mysqld",48766,12))tcpLISTEN0600*:3307*:*users:(("mysqld",49510,12))

9、配置及管理MySQL多实例数据库
（1）配置MySQL多实例数据库开机自启动
服务的开机自启动和关键，MySQL多实例的启动也不例外，把MySQL多实例的启动命令加入/etc/rc.local，实现开机自启动：

cat>>/etc/rc.local<<EOF#mysqlmultiinstances/data/3306/mysqlstart/data/3307/mysqlstartEOFtail-3/etc/rc.local

提示：要确保MySQL脚本有执行权限
（2）登陆mysql测试
登录时要指定sock文件
测试命令如下：
mysql -S /data/3306/mysql.sock <==直接敲进来了，而且身份还是root，但是多了-S /data/3306/mysql.sock，用户区别登录不同的实例
操作演示

[root@db01scripts]#mysql-S/data/3306/mysql.sockWelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis1Serverversion:5.5.52-logSourcedistributionCopyright(c)2000,2016,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>[root@db013306]#mysql-S/data/3307/mysql.sockWelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis2Serverversion:5.5.52-logSourcedistributionCopyright(c)2000,2016,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>

到这里MySQL多实例就配置完成啦O(∩_∩)O~~！！！