.jpg)
**********************************************************?
第一部分, 在一臺(tái)服務(wù)器構(gòu)建多mysql?服務(wù).?
**********************************************************?
一，緒言?
在mysql中有一mysqld_multi命令，可用于在一臺(tái)物理服務(wù)器運(yùn)行多個(gè)mysql服務(wù)，今天參考一些文檔，親自測(cè)試并通過,真高興，現(xiàn)將操作過程共享給大家!?
操作系統(tǒng):linux?2.6.13?(slackware),其它版的linux應(yīng)該也差不多.?
數(shù)據(jù)庫:mysql?4.0.17?源程序安裝(相信最新的5.1.*也差不多，過些天再試試)?
規(guī)劃:運(yùn)行4個(gè)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è)在安裝時(shí)用的configura選擇項(xiàng)氯??
./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管理手冊(cè)中提到:每個(gè)mysql的服務(wù)都可為獨(dú)立的，所以它都調(diào)用一個(gè)my.cnf中各自不同的啟動(dòng)選項(xiàng)–就是下文中將提到的gnr值,使用不同的端口,生成各自的套接文件,服務(wù)的數(shù)據(jù)庫都是獨(dú)立的(更多可查閱mysql官方網(wǎng)站的英文管理手冊(cè)).?
mysqld_multi是管理多個(gè)mysqld的服務(wù)進(jìn)程，這些服務(wù)進(jìn)程程序不同的unix?socket或是監(jiān)聽于不同的端口。他可以啟動(dòng)、停止和監(jiān)控當(dāng)前的服務(wù)狀態(tài)。?
—-程序在my.cnf(或是在–config-file自定義的配置文件)中搜索[mysqld#]段，”#”可以是任意的正整數(shù)。這個(gè)正整數(shù)就是在下面提及的段序列，即gnr。段的序號(hào)做為mysqld_multi的參數(shù)，來區(qū)別不同的段，這樣你就可以控制特定mysqld進(jìn)程的啟動(dòng)、停止或得到他的報(bào)告信息。這些組里的參數(shù)就像啟動(dòng)一個(gè)mysqld所需要的組的參數(shù)一樣。但是，如果使用多服務(wù)，必須為每個(gè)服務(wù)指定一個(gè)unix?socket或端口(摘自http://mifor.4dian.org中的使用mysqld_multi程序管理多個(gè)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?(用一個(gè)帳號(hào)來啟動(dòng)所有的mysql服務(wù)器,因?yàn)槭怯靡幌嗤膸ぬ?hào)。那個(gè)么這帳號(hào)必須都是每個(gè)mysql服務(wù)都要用的帳號(hào),最好是管理帳號(hào),下面的口令與相同)?
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。就是說我將啟動(dòng)4個(gè)mysql服務(wù)在同一服務(wù)器的不同端口——3306-3309，每datadir所指定的數(shù)據(jù)庫文件路徑都是不相同的，都有各自不同的日志文件。其它一些設(shè)置可用my.cnf?原來的內(nèi)容.?
ok,?重要的my.cnf編好的，現(xiàn)在為配置中不同mysql?服務(wù)建立各自文件夾和初始數(shù)據(jù)庫等.?
[mysqld1]是一個(gè)默認(rèn)的,在我們安裝mysql時(shí)已經(jīng)有了，所以不用管它.?
[mysqld2],只要根據(jù)配置就的路徑為它建立一個(gè)目錄就可以了.將把該目錄改為mysql管理權(quán)?
db-app:/?#?mkdir?/usr/local/mysql/data2?
建數(shù)據(jù)庫,我們可以把默認(rèn)的mysql數(shù)據(jù)庫復(fù)制過來，以利用其它的mysql帳號(hào),其它數(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啟動(dòng)了。?
三,mysqld_multi命令.?
使用如下參數(shù)來啟動(dòng)mysqld_multi:?(注:該命令在mysql的bin目錄中,根據(jù)上面所提到./configure?–prefix=/usr/local/mysql?,所以該文件應(yīng)該在?/usr/local/mysq/bin,?這得根據(jù)你安裝時(shí)所指定的路徑?)?
db-app:/?#?mysqld_multi?[options]?{start|stop|report}?[gnr[,gnr]…]?
start,stop和report是指你想到執(zhí)行的操作。你可以在單獨(dú)的服務(wù)或是多服務(wù)上指定一個(gè)操作，區(qū)別于選項(xiàng)后面的gnr列表。如果沒有指定gnr列表，那么mysqld_multi將在所有的服務(wù)中根據(jù)選項(xiàng)文件進(jìn)行操作。?
每一個(gè)gnr的值是組的序列號(hào)或是一個(gè)組的序列號(hào)范圍。此項(xiàng)的值必須是組名字最后的數(shù)字，比如說如果組名為mysqld17，那么此項(xiàng)的值則為17.如果指定一個(gè)范圍，使用”-“(破折號(hào))來連接二個(gè)數(shù)字。如gnr的值為10-13,則指組mysqld10到組mysqld13。多個(gè)組或是組范圍可以在命令行中指定，使用”,”(逗號(hào))隔開。不能有空白的字符(如空格或tab)，在空白字符后面的參數(shù)將會(huì)被忽略。?(注:gnr值就是我們定義my.cnf中mysqld#中的值,我這里只有1-4).?
db-app:/?#?/usr/local/mysq/bin/mysqld_multi?–config-file=/etc/my.cnf?start?1?只啟動(dòng)?第一個(gè)mysql服務(wù),相關(guān)文件由my.cnf中mysql1設(shè)定.?
db-app:/?#?/usr/local/mysq/bin/mysqld_multi?–config-file=/etc/my.cnf?stop?1?啟止?第一個(gè)mysql服務(wù)?
db-app:/?#?/usr/local/mysq/bin/mysqld_multi?–config-file=/etc/my.cnf?start?1-4,?啟動(dòng)?第1至4mysql服務(wù),其實(shí)就是我這里的全部.?
db-app:/?#?/usr/local/mysq/bin/mysqld_multi?–config-file=/etc/my.cnf?report?1-4?
查看啟動(dòng):?
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。?
**********************************************************?
第二部分, 在一臺(tái)服務(wù)器構(gòu)建多mysql主?服務(wù).?
**********************************************************?
提示:建立一個(gè)帳號(hào),專門用于下面主從復(fù)制,本例用的的帳號(hào)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)容,讓每個(gè)mysql生成自己的bin-log文件及各自的運(yùn)行環(huán)境，現(xiàn)貼上我的my.cnf全部?jī)?nèi)容,相關(guān)參數(shù)與請(qǐng)參考mysql?官文手冊(cè).?
#[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?
**********************?
啟動(dòng)多mysql服務(wù)都是相同的,?
db-app:/?#?/usr/local/mysq/bin/mysqld_multi?–config-file=/etc/my.cnf?start?1-4?
**********************************************************?
第三部分,?在一臺(tái)服務(wù)器構(gòu)建多mysql?從服務(wù).?
**********************************************************?
構(gòu)建從服務(wù)器的預(yù)選準(zhǔn)備:?建議用mysqld_multi?把主服務(wù)器的mysql全部停掉.刪除數(shù)據(jù)目錄中的所有除數(shù)據(jù)庫目錄以外的任何文件(此文中的數(shù)據(jù)目錄庫有４個(gè),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?
同時(shí)，請(qǐng)確認(rèn)系統(tǒng)帳號(hào)mysql是否對(duì)主/從服務(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的全部?jī)?nèi)容.?
提示:下面的my.cnf中將會(huì)提到一個(gè)帳號(hào):repl,?口令為:’1234567890′,?這個(gè)帳號(hào)就是上面專門建立的。?
其實(shí)都一樣,主要是修改my.cnf中的內(nèi)容,讓每個(gè)從mysql通過主mysql的不同的端口,去獲取各自bin-log來更新自生的數(shù)據(jù)庫內(nèi)容．現(xiàn)貼上我的my.cnf全部?jī)?nèi)容(從服務(wù)器),相關(guān)參數(shù)與請(qǐng)參考mysql?官文手冊(cè).?
#[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)在分別啟動(dòng)兩臺(tái)主機(jī)上的多mysql服務(wù)，這樣，每個(gè)主服務(wù)的每個(gè)mysql有變化，都會(huì)自動(dòng)復(fù)制／更新到從服務(wù)器對(duì)應(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?
********************?
五,未來測(cè)試,?
接下來我想做一個(gè)多主一從的mysql服務(wù)器復(fù)制解決方案! 那位可以給點(diǎn)意見啊!結(jié)構(gòu)如下．?
就是有主服務(wù)器server?a?,?server?b和從服務(wù)器?server?c,?a?和?b運(yùn)行著不同的數(shù)據(jù)庫應(yīng)用,?假設(shè)數(shù)據(jù)庫名都不同。server?c（假設(shè)這三臺(tái)ＰＣ上都只運(yùn)行了一個(gè)mysql服務(wù)），包括了Ａ了Ｂ服務(wù)器的所有mysql?用戶及相同的訪問權(quán)限,?并集成在一個(gè)mysql服務(wù)中.?c通過主/從方式復(fù)制Ａ和Ｂ的數(shù)據(jù)庫。?
就是差不就是把兩個(gè)主服務(wù)器的mysql合并到一個(gè)從服務(wù)器中。
【相關(guān)推薦】
.png)
