mysql多實例（mysqld_multi方式） · Mysql數據庫總結

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 ~~~