《MYSQL教程在同一臺機(jī)器上運行多個 MySQL 服務(wù)》要點：
本文介紹了MYSQL教程在同一臺機(jī)器上運行多個 MySQL 服務(wù)，希望對您有用。如果有疑問，可以聯(lián)系我們。
**********************************************************?
第一部分, 在一臺服務(wù)器構(gòu)建多mysql?服務(wù).?
**********************************************************?
一,緒言?
在Mysql中有一mysqld_multi命令,可用于在一臺物理服務(wù)器運行多個Mysql服務(wù),今天參考一些文檔,親自測試并通過,真高興,現(xiàn)將操作過程共享給大家!?
操作系統(tǒng):Linux?2.6.13?(Slackware),其它版的Linux應(yīng)該也差不多.?
數(shù)據(jù)庫:Mysql?4.0.17?源程序安裝(相信最新的5.1.*也差不多,過些天再試試)?
規(guī)劃:運行4個mysql服務(wù):?
假設(shè)服務(wù)器名:db-app?(IP為192.168.0.100),?
假設(shè)服務(wù)器名:db-app2?(IP為192.168.0.101),?
下文直到第二部分,均為講述db-app中的mysql,?
二,準(zhǔn)備?
用mysql源程序安裝,假設(shè)在安裝時用的configura選擇項氯??
./configure?--prefix=/usr/local/mysql?--datadir=/usr/local/mysql/data1?--sysconfdir=/etc?
備注:--prefix將MYSQL安裝到/usr/local/mysql,?
--datadir將數(shù)據(jù)庫生成/usr/local/mysql/data1?
sysconfdir是指定mysql使用到的my.cnf配置文件的搜索路徑為/etc?
其他mysql安裝過程略.?
根據(jù)Mysql管理手冊中提到:每個Mysql的服務(wù)都可為獨立的,所以它都調(diào)用一個my.cnf中各自不同的啟動選項--就是下文中將提到的GNR值,使用不同的端口,生成各自的套接文件,服務(wù)的數(shù)據(jù)庫都是獨立的(更多可查閱mysql官方網(wǎng)站的英文管理手冊).?
mysqld_multi是管理多個mysqld的服務(wù)進(jìn)程,這些服務(wù)進(jìn)程程序不同的unix?socket或是監(jiān)聽于不同的端口.他可以啟動、停止和監(jiān)控當(dāng)前的服務(wù)狀態(tài).?
----程序在my.cnf(或是在--config-file自定義的配置文件)中搜索[mysqld#]段,"#"可以是任意的正整數(shù).這個正整數(shù)就是在下面提及的段序列,即GNR.段的序號做為mysqld_multi的參數(shù),來區(qū)別不同的段,這樣你就可以控制特定mysqld進(jìn)程的啟動、停止或得到他的報告信息.這些組里的參數(shù)就像啟動一個mysqld所需要的組的參數(shù)一樣.但是,如果使用多服務(wù),必須為每個服務(wù)指定一個unix?socket或端口(摘自http://mifor.4dian.org中的使用mysqld_multi程序管理多個MySQL服務(wù)?).?
從上述文字可看到多Mysql服務(wù)中最重要的就是my.cnf配置文件了.?
現(xiàn)我貼出我的my.cnf文件.-----------------------?
[mysqld_multi]?
mysqld?=?/usr/local/mysql/bin/mysqld_safe?
mysqladmin?=?/usr/local/mysql/bin/mysqladmin?
user?=?mysql?(用一個帳號來啟動所有的mysql服務(wù)器,因為是用一相同的帳號.那個么這帳號必須都是每個mysql服務(wù)都要用的帳號,最好是管理帳號,下面的口令與相同)?
password?=?mypaswd?
[mysqld1]?
port?=?3306?
socket?=?/tmp/mysql.sock1?
pid-file=/usr/local/mysql/data1/db-app1.pid?
log=/usr/local/mysql/data1/db-app.log?
datadir?=?/usr/local/mysql/data?
user?=?mysql?
[mysqld2]?
port?=?3307?
socket?=?/tmp/mysql.sock2?
pid-file?=?/usr/local/mysql/data2/db-app2.pid?
datadir?=?/usr/local/mysql/data2?
log=/usr/local/mysql/data2/db-app.log?
user?=?mysql?
[mysqld3]?
port?=?3308?
socket?=?/tmp/mysql.sock3?
pid-file?=?/usr/local/mysql/data3/db-app3.pid3?
datadir?=?/usr/local/mysql/data3?
log=/usr/local/mysql/data3/db-app.log?
user?=?mysql?
[mysqld4]?
port?=?3309?
socket?=?/tmp/mysql.sock4?
pid-file?=?/usr/local/mysql/data3/db-app4.pid?
datadir?=?/usr/local/mysql/data4?
log=/usr/local/mysql/data4/db-app.log?
user?=?mysql?
[mysqldump]?
quick?
max_allowed_packet?=?16M?
[mysql]?
no-auto-rehash?
[isamchk]?
key_buffer?=?128M?
sort_buffer_size?=?128M?
read_buffer?=?2M?
write_buffer?=?2M?
[myisamchk]?
key_buffer?=?128M?
sort_buffer_size?=?128M?
read_buffer?=?2M?
write_buffer?=?2M?
[mysqlhotcopy]?
interactive-timeout?
我的配置文件中有mysqld1,mysqld2,mysqld3,mysqld4.就是說我將啟動4個mysql服務(wù)在同一服務(wù)器的不同端口――3306-3309,每datadir所指定的數(shù)據(jù)庫文件路徑都是不相同的,都有各自不同的日志文件.其它一些設(shè)置可用my.cnf?原來的內(nèi)容.?
ok,?重要的my.cnf編好的,現(xiàn)在為配置中不同mysql?服務(wù)建立各自文件夾和初始數(shù)據(jù)庫等.?
[mysqld1]是一個默認(rèn)的,在我們安裝mysql時已經(jīng)有了,所以不用管它.?
[mysqld2],只要根據(jù)配置就的路徑為它建立一個目錄就可以了.將把該目錄改為mysql管理權(quán)?
db-app:/?#?mkdir?/usr/local/mysql/data2?
建數(shù)據(jù)庫,我們可以把默認(rèn)的mysql數(shù)據(jù)庫復(fù)制過來,以利用其它的mysql帳號,其它數(shù)據(jù)庫根據(jù)應(yīng)用再建立.?
db-app:/?#?cp?/usr/local/mysql/data1/mysql?/usr/local/mysql/data2?-R?
db-app:/?#?chmod?mysql.mysql?/usr/local/mysql/data2?-R?
[mysqld3],[mysqld4],?相同.?
我們可能看看這些目錄是否都存在.?
db-app:/?#?ls?-l?/usr/local/mysql/?
drwxr-xr-x?6?mysql?mysql?4096?Apr?9?17:54?data4?
drwxr-x---?2?mysql?mysql?4096?Apr?9?17:14?data1?
drwxr-xr-x?3?mysql?mysql?4096?Apr?9?17:54?data2?
drwxr-xr-x?3?mysql?mysql?4096?Apr?9?17:54?data3?
現(xiàn)在可以通過mysqld_multi啟動了.?
三,mysqld_multi命令.?
使用如下參數(shù)來啟動mysqld_multi:?(注:該命令在mysql的bin目錄中,根據(jù)上面所提到./configure?--prefix=/usr/local/mysql?,所以該文件應(yīng)該在?/usr/local/mysq/bin,?這得根據(jù)你安裝時所指定的路徑?)?
db-app:/?#?mysqld_multi?[options]?{start|stop|report}?[GNR[,GNR]...]?
start,stop和report是指你想到執(zhí)行的操作.你可以在單獨的服務(wù)或是多服務(wù)上指定一個操作,區(qū)別于選項后面的GNR列表.如果沒有指定GNR列表,那么mysqld_multi將在所有的服務(wù)中根據(jù)選項文件進(jìn)行操作.?
每一個GNR的值是組的序列號或是一個組的序列號范圍.此項的值必須是組名字最后的數(shù)字,比如說如果組名為mysqld17,那么此項的值則為17.如果指定一個范圍,使用"-"(破折號)來連接二個數(shù)字.如GNR的值為10-13,則指組mysqld10到組mysqld13.多個組或是組范圍可以在命令行中指定,使用","(逗號)隔開.不能有空白的字符(如空格或tab),在空白字符后面的參數(shù)將會被忽略.?(注:GNR值就是我們定義my.cnf中mysqld#中的值,我這里只有1-4).?
db-app:/?#?/usr/local/mysq/bin/mysqld_multi?--config-file=/etc/my.cnf?start?1?只啟動?第一個mysql服務(wù),相關(guān)文件由my.cnf中mysql1設(shè)定.?
db-app:/?#?/usr/local/mysq/bin/mysqld_multi?--config-file=/etc/my.cnf?stop?1?啟止?第一個mysql服務(wù)?
db-app:/?#?/usr/local/mysq/bin/mysqld_multi?--config-file=/etc/my.cnf?start?1-4,?啟動?第1至4mysql服務(wù),其實就是我這里的全部.?
db-app:/?#?/usr/local/mysq/bin/mysqld_multi?--config-file=/etc/my.cnf?report?1-4?
查看啟動:?
db-app:/?#?ps?aux?
root?10467?0.0?0.2?2712?1300?pts/0?S?18:59?0:00?/bin/sh?/usr/local/mysql/bin/mysqld_safe?--port=3306?--socket=/tmp/mysql.sock1?
root?10475?0.0?0.2?2712?1300?pts/0?S?18:59?0:00?/bin/sh?/usr/local/mysql/bin/mysqld_safe?--port=3307?--socket=/tmp/mysql.sock2?
root?10482?0.0?0.2?2716?1300?pts/0?S?18:59?0:00?/bin/sh?/usr/local/mysql/bin/mysqld_safe?--port=3308?--socket=/tmp/mysql.sock3?
root?10487?0.0?0.2?2716?1300?pts/0?S?18:59?0:00?/bin/sh?/usr/local/mysql/bin/mysqld_safe?--port=3309?--socket=/tmp/mysql.sock4?
.............................................?
四,客戶端訪問?
任何客戶端訪問都需要指定訪問端口.方才能進(jìn)入指定數(shù)據(jù)庫服務(wù).否則將使用到Mysql默認(rèn)的端口(3306)所服務(wù)的MYSQL.?
**********************************************************?
第二部分, 在一臺服務(wù)器構(gòu)建多mysql主?服務(wù).?
**********************************************************?
提示:建立一個帳號,專門用于下面主從復(fù)制,本例用的的帳號repl,?口令為:'1234567890',?可讓其擁有select_priv,?reload_priv,process_priv,grant_priv,super_priv,repl_slave_priv,repl_client_priv權(quán)限,并能通過任何客戶進(jìn)行訪問,即訪問客戶為'%'.?
主要是修改my.cnf中的內(nèi)容,讓每個mysql生成自己的bin-log文件及各自的運行環(huán)境,現(xiàn)貼上我的my.cnf全部內(nèi)容,相關(guān)參數(shù)與請參考mysql?官文手冊.?
#[client]?
#password?=?your_password?
#port?=?3306?
#socket?=?/tmp/mysql.sock?
[mysqld_multi]?
mysqld?=?/usr/local/mysql/bin/mysqld_safe?
mysqladmin?=?/usr/local/mysql/bin/mysqladmin?
user?=?mysql?
password?=?mypasswd?
[mysqld1]?
port?=?3306?
socket?=?/tmp/mysql.sock1?
skip-locking?
pid-file=/usr/local/mysql/data/net-app1a.pid?
datadir?=?/usr/local/mysql/data?
log=/usr/local/mysql/data/net-app1.log?
user?=?mysql?
log-slow-queries=/usr/local/mysql/data/slowquery.log?
long_query_time?=?2?
key_buffer?=?256M?
max_allowed_packet?=?1M?
table_cache?=?512?
sort_buffer_size?=?2M?
read_buffer_size?=?2M?
myisam_sort_buffer_size?=?64M?
thread_cache?=?32?
query_cache_size?=?32M?
thread_concurrency?=?2?
max_connections=500?
log-bin?
log-bin=/usr/local/mysql/data/app-net1_1-bin?
server-id?=?1?
[mysqld2]?
port?=?3307?
socket?=?/tmp/mysql.sock2?
pid-file?=?/usr/local/mysql/data2/net-app1b.pid?
datadir?=?/usr/local/mysql/data2?
log=/usr/local/mysql/data2/net-app1.log?
user?=?mysql?
log-slow-queries=/usr/local/mysql/data2/slowquery.log?
long_query_time?=?10?
key_buffer?=?128M?
max_allowed_packet?=?1M?
table_cache?=?512?
sort_buffer_size?=?1M?
read_buffer_size?=?1M?
myisam_sort_buffer_size?=?32M?
thread_cache?=?32?
query_cache_size?=?16M?
thread_concurrency?=?2?
max_connections=300?
log-bin?
log-bin=/usr/local/mysql/data2/app-net1_2-bin?
server-id?=?1?
[mysqld3]?
port?=?3308?
socket?=?/tmp/mysql.sock3?
skip-locking?
pid-file?=?/usr/local/mysql/data3/net-app1c.pid?
datadir?=?/usr/local/mysql/data3?
log=/usr/local/mysql/data3/net-app1.log?
user?=?mysql?
log-bin?
log-bin=/usr/local/mysql/data3/app-net1_3-bin?
server-id?=?1?
[mysqld4]?
port?=?3309?
socket?=?/tmp/mysql.sock4?
skip-locking?
pid-file?=?/usr/local/mysql/data1/app-net1d.pid?
datadir?=?/usr/local/mysql/data1?
log=/usr/local/mysql/data1/net-app1.log?
user?=?mysql?
log-bin?
log-bin=/usr/local/mysql/data1/app-net1_4-bin?
server-id?=?1?
[mysqldump]?
quick?
max_allowed_packet?=?16M?
[mysql]?
no-auto-rehash?
#?Remove?the?next?comment?character?if?you?are?not?familiar?with?SQL?
#safe-updates?
[isamchk]?
key_buffer?=?128M?
sort_buffer_size?=?128M?
read_buffer?=?2M?
write_buffer?=?2M?
[myisamchk]?
key_buffer?=?128M?
sort_buffer_size?=?128M?
read_buffer?=?2M?
write_buffer?=?2M?
[mysqlhotcopy]?
interactive-timeout?
**********************?
啟動多mysql服務(wù)都是相同的,?
db-app:/?#?/usr/local/mysq/bin/mysqld_multi?--config-file=/etc/my.cnf?start?1-4?
**********************************************************?
第三部分,?在一臺服務(wù)器構(gòu)建多mysql?從服務(wù).?
**********************************************************?
構(gòu)建從服務(wù)器的預(yù)選準(zhǔn)備:?建議用mysqld_multi?把主服務(wù)器的mysql全部停掉.刪除數(shù)據(jù)目錄中的所有除數(shù)據(jù)庫目錄以外的任何文件(此文中的數(shù)據(jù)目錄庫有４個,datadir?=?/usr/local/mysql/中的?data1?--?data1)．建主從都用相同的數(shù)據(jù)目錄路徑．?
用Tar?命令把每數(shù)據(jù)庫封裝起來,并通過sftp命令put/get到從服務(wù)器(db-app1?192.168.0.101).?
下列操作供參考:?
在db-app主機(jī)上的操作?
db-app:/?#?tar?-cf?data1.tar?/usr/local/mysql/data1?
db-app:/?#?tar?-cf?data2.tar?/usr/local/mysql/data2?
db-app:/?#?tar?-cf?data3.tar?/usr/local/mysql/data3?
db-app:/?#?tar?-cf?data4.tar?/usr/local/mysql/data4?
在db-app1主機(jī)上的操作?
db-app1:/?#?tar?xvf?data1.tar?
db-app1:/?#?tar?xvf?data2.tar?
db-app1:/?#?tar?xvf?data3.tar?
db-app1:/?#?tar?xvf?data4.tar?
同時,請確認(rèn)系統(tǒng)帳號mysql是否對主/從服務(wù)器的中的mysql數(shù)據(jù)目錄都有操作權(quán)限,如果無法確認(rèn),你直接更修改這些目錄的所有權(quán)即可.?
在db-app主機(jī)上的操作?
db-app:/?#?chown?mysql.mysql?/usr/local/mysql/data1?-R?
db-app:/?#?chown?mysql.mysql?/usr/local/mysql/data2?-R?
db-app:/?#?chown?mysql.mysql?/usr/local/mysql/data3?-R?
db-app:/?#?chown?mysql.mysql?/usr/local/mysql/data4?-R?
在db-app1主機(jī)上的操作?
db-app1:/?#?chown?mysql.mysql?/usr/local/mysql/data1?-R?
db-app2:/?#?chown?mysql.mysql?/usr/local/mysql/data2?-R?
db-app3:/?#?chown?mysql.mysql?/usr/local/mysql/data3?-R?
db-app4:/?#?chown?mysql.mysql?/usr/local/mysql/data4?-R?
下面就是從服務(wù)器上/etc/my.cnf的全部內(nèi)容.?
提示:下面的my.cnf中將會提到一個帳號:repl,?口令為:'1234567890',?這個帳號就是上面專門建立的.?
其實都一樣,主要是修改my.cnf中的內(nèi)容,讓每個從mysql通過主mysql的不同的端口,去獲取各自bin-log來更新自生的數(shù)據(jù)庫內(nèi)容．現(xiàn)貼上我的my.cnf全部內(nèi)容(從服務(wù)器),相關(guān)參數(shù)與請參考mysql?官文手冊.?
#[client]?
#password?=?your_password?
#port?=?3306?
#socket?=?/tmp/mysql.sock?
[mysqld_multi]?
mysqld?=?/usr/local/mysql/bin/mysqld_safe?
mysqladmin?=?/usr/local/mysql/bin/mysqladmin?
user?=?mysql?
password?=?netmoniit?
[mysqld1]?
port?=?3306?
socket?=?/tmp/mysql.sock1?
skip-locking?
pid-file=/usr/local/mysql/data/net-app1a.pid?
datadir?=?/usr/local/mysql/data?
log=/usr/local/mysql/data/net-app1.log?
user?=?mysql?
log-slow-queries=/usr/local/mysql/data/slowquery.log?
long_query_time?=?2?
key_buffer?=?256M?
max_allowed_packet?=?1M?
table_cache?=?512?
sort_buffer_size?=?2M?
read_buffer_size?=?2M?
myisam_sort_buffer_size?=?64M?
thread_cache?=?32?
query_cache_size?=?32M?
thread_concurrency?=?2?
max_connections=500?
server-id?=?2?
master-host?=?192.168.0.100?
master-user?=?'repl'?
master-password?=?'1234567890'?
master-port?=?3309?
report-host?=?net-app1?
master-connect-retry?=?30?
log-bin?
log-slave-updates?
[mysqld2]?
port?=?3307?
socket?=?/tmp/mysql.sock2?
pid-file?=?/usr/local/mysql/data2/net-app1b.pid?
datadir?=?/usr/local/mysql/data2?
log=/usr/local/mysql/data2/net-app1.log?
user?=?mysql?
log-slow-queries=/usr/local/mysql/data2/slowquery.log?
long_query_time?=?10?
key_buffer?=?128M?
max_allowed_packet?=?1M?
table_cache?=?512?
sort_buffer_size?=?1M?
read_buffer_size?=?1M?
myisam_sort_buffer_size?=?32M?
thread_cache?=?32?
query_cache_size?=?16M?
thread_concurrency?=?2?
max_connections=300?
server-id?=?2?
master-host?=?192.168.0.100?
master-user?=?'repl'?
master-password?=?'1234567890'?
master-port?=?3309?
report-host?=?net-app1?
master-connect-retry?=?30?
log-bin?
log-slave-updates?
[mysqld3]?
port?=?3308?
socket?=?/tmp/mysql.sock3?
pid-file?=?/usr/local/mysql/data3/net-app1c.pid?
datadir?=?/usr/local/mysql/data3?
log=/usr/local/mysql/data3/net-app1.log?
user?=?mysql?
log-slow-queries=/usr/local/mysql/data3/slowquery.log?
long_query_time?=?10?
key_buffer?=?128M?
max_allowed_packet?=?1M?
table_cache?=?512?
sort_buffer_size?=?1M?
read_buffer_size?=?1M?
myisam_sort_buffer_size?=?32M?
thread_cache?=?32?
query_cache_size?=?16M?
thread_concurrency?=?2?
max_connections=300?
server-id?=?2?
master-host?=?192.168.0.100?
master-user?=?'repl'?
master-password?=?'1234567890'?
master-port?=?3309?
report-host?=?net-app1?
master-connect-retry?=?30?
log-bin?
log-slave-updates?
[mysqld3]?
port?=?3308?
socket?=?/tmp/mysql.sock4?
pid-file?=?/usr/local/mysql/data4/net-app1d.pid?
datadir?=?/usr/local/mysql/data4?
log=/usr/local/mysql/data4/net-app1.log?
user?=?mysql?
log-slow-queries=/usr/local/mysql/data4/slowquery.log?
long_query_time?=?10?
key_buffer?=?128M?
max_allowed_packet?=?1M?
table_cache?=?512?
sort_buffer_size?=?1M?
read_buffer_size?=?1M?
myisam_sort_buffer_size?=?32M?
thread_cache?=?32?
query_cache_size?=?16M?
thread_concurrency?=?2?
max_connections=300?
server-id?=?2?
master-host?=?192.168.0.100?
master-user?=?'repl'?
master-password?=?'1234567890'?
master-port?=?3309?
report-host?=?net-app1?
master-connect-retry?=?30?
log-bin?
log-slave-updates?
[mysqldump]?
quick?
max_allowed_packet?=?16M?
[mysql]?
no-auto-rehash?
#?Remove?the?next?comment?character?if?you?are?not?familiar?with?SQL?
#safe-updates?
[isamchk]?
key_buffer?=?128M?
sort_buffer_size?=?128M?
read_buffer?=?2M?
write_buffer?=?2M?
[myisamchk]?
key_buffer?=?128M?
sort_buffer_size?=?128M?
read_buffer?=?2M?
write_buffer?=?2M?
[mysqlhotcopy]?
interactive-timeout?
****************************************?
在功告成,現(xiàn)在分別啟動兩臺主機(jī)上的多mysql服務(wù),這樣,每個主服務(wù)的每個mysql有變化,都會自動復(fù)制／更新到從服務(wù)器對應(yīng)的數(shù)據(jù)庫中.?
db-app:/?#?/usr/local/mysq/bin/mysqld_multi?--config-file=/etc/my.cnf?start?1-4?
db-app1:/?#?/usr/local/mysq/bin/mysqld_multi?--config-file=/etc/my.cnf?start?1-4?
********************?
五,未來測試,?
接下來我想做一個多主一從的Mysql服務(wù)器復(fù)制解決方案! 那位可以給點意見啊!結(jié)構(gòu)如下．?
就是有主服務(wù)器Server?A?,?Server?B和從服務(wù)器?Server?C,?A?和?B運行著不同的數(shù)據(jù)庫應(yīng)用,?假設(shè)數(shù)據(jù)庫名都不同.Server?C(假設(shè)這三臺ＰＣ上都只運行了一個mysql服務(wù)),包括了Ａ了Ｂ服務(wù)器的所有Mysql?用戶及相同的訪問權(quán)限,?并集成在一個Mysql服務(wù)中.?C通過主/從方式復(fù)制Ａ和Ｂ的數(shù)據(jù)庫.?
就是差不就是把兩個主服務(wù)器的mysql合并到一個從服務(wù)器中.
《MYSQL教程在同一臺機(jī)器上運行多個 MySQL 服務(wù)》是否對您有啟發(fā)，歡迎查看更多與《MYSQL教程在同一臺機(jī)器上運行多個 MySQL 服務(wù)》相關(guān)教程，學(xué)精學(xué)透。維易PHP學(xué)院為您提供精彩教程。
轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/13990.html
