《Mysql實(shí)例MySQL主從配置》要點(diǎn)：
本文介紹了Mysql實(shí)例MySQL主從配置，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。

1. 分散負(fù)載：用主從復(fù)制最大的好處之一就是由主庫(kù)執(zhí)行所有插入操作,由從庫(kù)承擔(dān)起大部分的查詢操作.簡(jiǎn)單地通過主從就能極大地提速你的應(yīng)用.
2. 用從庫(kù)備份數(shù)據(jù)：這個(gè)優(yōu)點(diǎn)常常被遺忘.使用從庫(kù)備份不會(huì)影響你的網(wǎng)站工作,特別是當(dāng)數(shù)據(jù)量大到G級(jí)別的時(shí)候,由備份引起的鎖表引導(dǎo)致網(wǎng)站延遲響應(yīng).更嚴(yán)重地可能會(huì)導(dǎo)致網(wǎng)站瞬間癱瘓.如果在從庫(kù)上做這些就無關(guān)緊要了,你甚至可以停掉某個(gè)從庫(kù),并且暴力的復(fù)制var目錄.

MYSQL應(yīng)用假設(shè)你有兩臺(tái)數(shù)據(jù)庫(kù)服務(wù)器,計(jì)劃如下部署：

MYSQL應(yīng)用Master server ip: 10.0.0.1 Slave server ip: 10.0.0.2 Slave username: slaver Slave pw: 123456 Your data directory is:?usr/local/mysql/var

MYSQL應(yīng)用主庫(kù)配置文件my.cnf [mysqld]節(jié)下面加上：

MYSQL應(yīng)用server-id = 1 relay-log = /usr/local/mysql/var/mysql-relay-bin relay-log-index = /usr/local/mysql/var/mysql-relay-bin.index log-error = /usr/local/mysql/var/mysql.err master-info-file = /usr/local/mysql/var/mysql-master.info relay-log-info-file = /usr/local/mysql/var/mysql-relay-log.info datadir = /usr/local/mysql/var log-bin = /usr/local/mysql/var/mysql-bin

MYSQL應(yīng)用從庫(kù)配置文件my.cnf [mysqld]節(jié)下面加上

MYSQL應(yīng)用server-id = 2 relay-log = /usr/local/mysql/var/mysql-relay-bin relay-log-index = /usr/local/mysql/var/mysql-relay-bin.index log-error = /usr/local/mysql/var/mysql.err master-info-file = /usr/local/mysql/var/mysql-master.info relay-log-info-file = /usr/local/mysql/var/mysql-relay-log.info datadir = /usr/local/mysql/var

MYSQL應(yīng)用在主庫(kù)上創(chuàng)建用戶：

MYSQL應(yīng)用mysql> grant replication slave on?.?to 'slaver'@'10.0.0.2' identified by '123456';

MYSQL應(yīng)用值得注意的是,grant replication slave是全局操作,不能對(duì)單個(gè)庫(kù)操作.

MYSQL應(yīng)用使用mysqldump工具,導(dǎo)出主庫(kù)數(shù)據(jù),配好之前先手動(dòng)同步主從庫(kù)的數(shù)據(jù)：

MYSQL應(yīng)用mysqldump -u root --all-databases --single-transaction --master-data=1 > masterdump.sql

MYSQL應(yīng)用然后在從庫(kù)中導(dǎo)入： mysql < masterdump.sql

MYSQL應(yīng)用接下來要告庫(kù)從庫(kù)去復(fù)制哪個(gè)主庫(kù),以及連接主庫(kù)的信息： mysql> CHANGE MASTER TO MASTER\_HOST='10.0.0.1', MASTER\_USER='slaver', MASTER\_PASSWORD='123456';

MYSQL應(yīng)用緊接著開啟從庫(kù)同步： mysql> start slave;

MYSQL應(yīng)用可以通過下命的命令查看同步狀態(tài)： mysql> show slave status\G

MYSQL應(yīng)用最后執(zhí)行完成之后,剛添加的用戶出現(xiàn)怪異現(xiàn)像,任何帳號(hào)都能登陸而且不用密碼,最后百思不得其解,大量搜索之后發(fā)現(xiàn)“重啟MySQL即可”,然后和然后的然后,就可以了...

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/5801.html