多应用+插件架构,代码干净,二开方便,首家独创一键云编译技术,文档视频完善,免费商用码云13.8K 广告
CleverCode最近在研究mysql的多实例,发现有两种方式: 第一种是使用多个配置文件启动不同的进程来实现多实例。这种方式的优势逻辑简单,配置简单,缺点是管理起来不太方便。 第二种是通过官方自带的mysqld_multi。使用单独的配置文件来实现多实例,这种方式定制每个实例的配置不太方面,优点是管理起来很方便,集中管理。 推荐使用多个配置文件方式。这种实际应用中好,耦合性不强,配置方便,特别是主从复制的时候。 上一篇《mysql多实例(多个配置文件方式)》:http://blog.csdn.net/clevercode/article/details/47610619。介绍了多个配置文件方式。本篇将介绍mysqld_multi方式。 1 环境介绍: 1)简介 mysql 版本:mysql-5.5.27 cmake:cmake-2.8.8 操作系统:CentOS6.5 mysql实例数:3个 实例占用端口分别为:3306、3307、3308 2)本次安装所有的软件资源包下载地址 http://download.csdn.net/detail/clevercode/8662323 2 配置防火墙 1) 在防火墙配置文件中添加3306,3307,3308(允许3306,3307,3308端口通过防火墙)  ~~~  # vi /etc/sysconfig/iptables   #编辑防火墙配置文件  -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT -A INPUT -m state --state NEW -m tcp -p tcp --dport 3307 -j ACCEPT -A INPUT -m state --state NEW -m tcp -p tcp --dport 3308 -j ACCEPT ~~~ 2)重启防火墙 ~~~  # /etc/init.d/iptables restart  #最后重启防火墙使配置生效   ~~~ 3 安装cmake 解压 ~~~  # cd /usr/local/src/mysql  # tar zxvf cmake-2.8.8.tar.gz  # cd cmake-2.8.8 ~~~ 配置 ` # ./configure`   编译 ` # make`   安装 ` # make install `   查看版本 ` # cmake -version`   4 安装Mysql 1) 创建用户 ~~~  # groupadd mysql  #添加mysql组      # useradd -g mysql mysql -s /bin/false  #创建用户mysql并加入到mysql组,不允许mysql用户直接登录系统 ~~~ 2) 解压 ~~~  # cd /usr/local/src/mysql  # tar zxvf mysql-5.5.27.tar.gz  # cd mysql-5.5.27 ~~~ 4) 配置 ` # cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql `   PS: cmake的时候,参数可以不用那么多,只要一个-DCMAKE_INSTALL_PREFIX=/usr/local/mysql就行了,我们可以在 my.cnf里面配置。[mysqld]中的内容,看看你copy后的my.cnf有没有这些设置,有就不用了在设置了。   5)编译 ` # make `   6)安装 ` # make install ` 5 初始化数据库 ~~~  # mkdir -p /data0/dbdata/mysql/3306  # mkdir -p /data0/dbdata/mysql/3307  # mkdir -p /data0/dbdata/mysql/3308  # chown -R mysql:mysql /data0/dbdata/mysql/3306  # chown -R mysql:mysql /data0/dbdata/mysql/3307  # chown -R mysql:mysql /data0/dbdata/mysql/3308  # /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data0/dbdata/mysql/3306 --user=mysql  # /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data0/dbdata/mysql/3307 --user=mysql  # /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data0/dbdata/mysql/3308 --user=mysql ~~~ 6 修改配置文件(只用/etc/my.cnf文件) ~~~  # cp /usr/local/mysql/support-files/my-huge.cnf /etc/my.cnf  # vi /etc/my.cnf ~~~ ~~~ [mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin  #用于登陆和关闭此服务 user = root password = CleverCode123 [mysqld3306]  #数据目录 datadir = /data0/dbdata/mysql/3306  #连接 port = 3306 socket = /tmp/mysql3306.sock  #binlog log-bin=/data0/dbdata/mysql/3306/mysql-bin pid-file = /data0/dbdata/mysql/3306/mysql_3306.pid [mysqld3307] datadir = /data0/dbdata/mysql/3307 port = 3307 socket = /tmp/mysql3307.sock log-bin=/data0/dbdata/mysql/3307/mysql-bin pid-file = /data0/dbdata/mysql/3307/mysql_3307.pid [mysqld3308] datadir = /data0/dbdata/mysql/3308 port = 3308 socket = /tmp/mysql3308.sock log-bin=/data0/dbdata/mysql/3308/mysql-bin pid-file = /data0/dbdata/mysql/3308/mysql_3308.pid ~~~ 7 启动3306、3307、3308的mysql  ~~~  # /usr/local/mysql/bin/mysqld_multi start 3306  # /usr/local/mysql/bin/mysqld_multi start 3307  # /usr/local/mysql/bin/mysqld_multi start 3308 ~~~ 8 查看端口是否监听,如果出现3306,3307,3308则启动正常 ~~~  # netstat -anp | grep 3308 tcp        0      0 0.0.0.0:3308                0.0.0.0:*                   LISTEN      2348/mysqld          unix  2      [ ACC ]     STREAM     LISTENING     10780  2348/mysqld         /tmp/mysql3308.sock   ~~~ 9 初始化密码并且授权远程登录,mysqladmin用户名和密码需要和/ect/my.cnf中保持一致,否则stop不了服务。 ~~~  # /usr/local/mysql/bin/mysqladmin -u root password "CleverCode123" -S /tmp/mysql3306.sock  # /usr/local/mysql/bin/mysql -uroot -pCleverCode123 -S  /tmp/mysql3306.sock mysql> grant all privileges on  *.* to 'root'@'%' identified by 'pwd3306' with grant option; mysql> flush privileges;  # /usr/local/mysql/bin/mysqladmin -u root password "CleverCode123" -S /tmp/mysql3307.sock  # /usr/local/mysql/bin/mysql -uroot -pCleverCode123 -S  /tmp/mysql3307.sock mysql> grant all privileges on  *.* to 'root'@'%' identified by 'pwd3307' with grant option; mysql> flush privileges;  # /usr/local/mysql/bin/mysqladmin -u root password "CleverCode123" -S /tmp/mysql3308.sock  # /usr/local/mysql/bin/mysql -uroot -pCleverCode123 -S  /tmp/mysql3308.sock mysql> grant all privileges on  *.* to 'root'@'%' identified by 'pwd3308' with grant option; mysql> flush privileges; ~~~ 10 停止(必须先初始化密码)。可以使用netstat -anp | grep 3308查看监听进程是否还存在 ~~~  # /usr/local/mysql/bin/mysqld_multi stop 3306  # /usr/local/mysql/bin/mysqld_multi stop 3307  # /usr/local/mysql/bin/mysqld_multi stop 3308 ~~~ 11 报告。显示进程的状态 ~~~  # /usr/local/mysql/bin/mysqld_multi report 3306  # /usr/local/mysql/bin/mysqld_multi report 3307  # /usr/local/mysql/bin/mysqld_multi report 3308 ~~~