@import url(http://m.tkk7.com/CuteSoft_Client/CuteEditor/Load.ashx?type=style&file=SyntaxHighlighter.css);@import url(/css/cuteeditor.css);
@import url(http://m.tkk7.com/CuteSoft_Client/CuteEditor/Load.ashx?type=style&file=SyntaxHighlighter.css);@import url(/css/cuteeditor.css);
@import url(http://m.tkk7.com/CuteSoft_Client/CuteEditor/Load.ashx?type=style&file=SyntaxHighlighter.css);@import url(/css/cuteeditor.css);
@import url(http://m.tkk7.com/CuteSoft_Client/CuteEditor/Load.ashx?type=style&file=SyntaxHighlighter.css);@import url(/css/cuteeditor.css);
@import url(http://m.tkk7.com/CuteSoft_Client/CuteEditor/Load.ashx?type=style&file=SyntaxHighlighter.css);@import url(/css/cuteeditor.css);
@import url(http://m.tkk7.com/CuteSoft_Client/CuteEditor/Load.ashx?type=style&file=SyntaxHighlighter.css);@import url(/css/cuteeditor.css);
主服務(wù)器 OS:CentOS IP:192.168.8.130
從服務(wù)器 OS:CentOS IP:192.168.8.131
在主,從服務(wù)器上安裝MySQL,安裝方法如下:
[root@localhost Desktop]$ rpm -qa | grep mysql
mysql-libs-5.1.73-5.el6_6.x86_64
[root@localhost Desktop]# rpm -e mysql-libs-5.1.73-5.el6_6.x86_64 --nodeps
[root@localhost Desktop]# yum -y install mysql-server mysql mysql-devel
啟動(dòng)MySQL
[root@localhost Desktop]# service mysqld start
#可以設(shè)置MySQL開機(jī)啟動(dòng),運(yùn)行命令chkconfig mysqld on
#給root賬號(hào)設(shè)置密碼
[root@localhost Desktop]# mysqladmin -u root password 'root'
[root@localhost Desktopps]# mysql -u root -p
給從服務(wù)器(192.168.8.131)授權(quán),并且給從服務(wù)器創(chuàng)建訪問主服務(wù)器的賬號(hào)和密碼 admin
mysql> grant replication slave on *.* to 'admin'@'192.168.8.131' identified by 'admin';
創(chuàng)建數(shù)據(jù)庫contract
mysql> create database contract;
mysql>quit;
復(fù)制MySQL數(shù)據(jù)庫配置模版覆蓋/etc/my.cnf
[root@localhost Desktopps]# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
[root@localhost Desktopps]#vi /etc/my.cnf
設(shè)置以下三個(gè)值
log-bin=mysql-bin #指定從服務(wù)器讀取的日志文件
server-id = 1 #主服務(wù)器必須設(shè)定為1,從服務(wù)器的值>1
binlog-do-db=contract #對(duì)contract數(shù)據(jù)庫的操作日志會(huì)記錄到mysql-bin
#原理:MySQL主從復(fù)制的原理是主服務(wù)器把對(duì)指定數(shù)據(jù)庫操作的日志寫到指定的日志文件中,從服務(wù)器
讀取這個(gè)日志文件,寫到從服務(wù)器的指定日志文件中,然后在從服務(wù)器重新執(zhí)行日志文件。
配置完之后,重啟MySQL
[root@localhost Desktopps]#service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@localhost Desktopps]# mysql -u root -p
查看主服務(wù)器的狀態(tài)
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000005
Position: 106
Binlog_Do_DB: contract
Binlog_Ignore_DB:
1 row in set (0.00 sec)
這里記好File和Position的值,配置從服務(wù)器的時(shí)候需要用到。File就是從服務(wù)器需要讀取的日志文件,Position表示從日志文件的什么位置開始讀起。
下面開始配置從服務(wù)器
[root@localhost Desktop]# mysqladmin -u root password 'root'
[root@localhost Desktopps]# mysql -u root -p
創(chuàng)建數(shù)據(jù)庫contract
mysql> create database contract;
mysql>quit;
[root@localhost Desktopps]# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
[root@localhost Desktopps]#vi /etc/my.cnf
設(shè)置以下兩個(gè)值
log-bin=mysql-bin #指定主服務(wù)器讀取的日志文件
server-id = 2 #主服務(wù)器必須設(shè)定為1,從服務(wù)器的值>1
[root@localhost Desktopps]# mysql -u root -p
mysql> CHANGE MASTER TO MASTER_HOST='192.168.8.130', MASTER_PORT=3306,
MASTER_USER='admin', MASTER_PASSWORD='admin',
MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=106;
啟動(dòng)從服務(wù)器同步
mysql>start slave;
mysql>show slave status\G;
Slave_IO_Running: YES
Slave_SQL_Running: YES
如果輸出以上內(nèi)容,則表示MySQL主從復(fù)制配置成功。
驗(yàn)證
在主服務(wù)器上運(yùn)行
[root@localhost Desktopps]# mysql -u root -p
mysql> use contract;
mysql> show tables;
Empty set (0.04 sec)
在從服務(wù)器上運(yùn)行
[root@localhost Desktopps]# mysql -u root -p
mysql> use contract;
Database changed
mysql> show tables;
Empty set (0.04 sec)
確定主從服務(wù)器的數(shù)據(jù)庫contract的下面都沒有表。
在主服務(wù)器上運(yùn)行建表命令,并往表里插入一條記錄:
mysql> create table `user` (`id` int not null auto_increment,`name` varchar (60),`password` varchar (20),`role` int not null,`email` varchar (30),`alertday` int,primary key (`id`));
Query OK, 0 rows affected (0.36 sec)
mysql> insert into `user` (`name`,`password`,`role`,`email`,`alertday`) values('admin','admin',0,'xxxx@xxx.com',30);
Query OK, 1 row affected (0.08 sec)
在從服務(wù)器上運(yùn)行查詢語句。
mysql> select * from user;
+----+-------+----------+------+--------------+----------+
| id | name | password | role | email | alertday |
+----+-------+----------+------+--------------+----------+
| 1 | admin | admin | 0 | xxxx@xxx.com | 30 |
+----+-------+----------+------+--------------+----------+
1 row in set (0.01 sec)
從輸出結(jié)果可以看出,主服務(wù)器上的數(shù)據(jù)被同步到從服務(wù)器上了。
通過搭建MySQL主從復(fù)制結(jié)構(gòu),可以提高數(shù)據(jù)的安全性,同時(shí)可以實(shí)現(xiàn)讀寫分離,讓寫操作在主服務(wù)器上進(jìn)行,
讀操作在從服務(wù)器上進(jìn)行,可以分擔(dān)主服務(wù)器的負(fù)擔(dān)。但是如果當(dāng)主服務(wù)器宕機(jī)之后,數(shù)據(jù)庫就只能提供
讀操作了,不能做到故障轉(zhuǎn)移,這時(shí)候,主主復(fù)制就應(yīng)運(yùn)而生了,有時(shí)間整理一下主主復(fù)制的配置。
@import url(http://m.tkk7.com/CuteSoft_Client/CuteEditor/Load.ashx?type=style&file=SyntaxHighlighter.css);@import url(/css/cuteeditor.css);