mysql主備雙向復(fù)制VIP

1、Mysql主備環(huán)境搭建1. 安裝系統(tǒng)依賴CentOS-6.2-x86_64，BasicServer模式安裝卸載系統(tǒng)自帶mysqlrpm -qa|grep -i mysqlrpm -ev -nodepsmysql v 查看mysql版本信息關(guān)閉防火墻service iptables stop (現(xiàn)場應(yīng)該開放相關(guān)端口即可)chkconfig -level 35 iptables off解壓mysqltar -zxvf mysql-5.5.30-linux2.6-x86_64.tar.gz rm -rf /usr/local/mysql/mv mysql-5.5.30-linux2.6-x86_64

2、 /usr/local/mysql/拷貝配置文件cp /usr/local/mysql/support-files/my-f /etc/fvi /etc/ff server-id=1 (此項存在，其他項新增)binlog-do-db=test binlog-do-db=quartz_amsbinlog-ignore-db=mysqlreplicate-do-db=testreplicate-do-db=quartz_amsreplicate-ignore-db=mysqllog-slave-updatesslave-skip-errors=allsync_binlog=1auto_increm

3、ent_increment=2auto_increment_offset=1 說明：binlog-do-db為指定哪些database需要同步，一個server中可以有多個database，每個database中有多個用戶注意：主備mysql的server-id，必須不同；主備的auto_increment_offset，必須不同，可以設(shè)置主為1，從為2，代表自增長數(shù)據(jù)的起始值；replicate-ignore-db為忽略的庫，不同步；replicate-do-db為同步庫。主f配置備f配置拷貝守護進程所需文件cp /usr/local/mysql/support-files/mysql.se

4、rver /etc/rc.d/init.d/mysqldchkconfig -add mysqldchkconfig -level 35 mysqld on添加mysql用戶組及用戶groupadd mysqluseradd mysql -g mysqlpasswd mysqlpasswd設(shè)為root_123456修改相關(guān)目錄權(quán)限chown -R mysql:mysql /usr/local/mysql切換到mysql用戶su mysql修改環(huán)境變量并立即生效vi /.bash_profile PATH=$PATH:$HOME/bin:/usr/local/mysql/bin. /.bash_

5、profile2. mysql設(shè)置初始化mysql數(shù)據(jù)并設(shè)置root密碼cd /usr/local/mysql./scripts/mysql_install_db -user=mysql -datadir=/usr/local/mysql/data/啟動mysql服務(wù)（以root用戶啟動，否則可能出現(xiàn)寫文件權(quán)限問題造成不能同步）service mysqld restart./bin/mysqladmin -u root password 'rootpasswd'防止“Couldn't release acquired trigger: Lock wait timeout

6、 exceeded; try restarting transaction”錯誤：設(shè)置MYSQL 數(shù)據(jù)庫的事務(wù)級別！SELECT global.tx_isolation;set global transaction isolation level read committed;主mysql(192.168.1.247)添加mysql同步用戶，賦予相關(guān)權(quán)限，mysql u root p登陸后執(zhí)行g(shù)rant replication slave,file on *.* to 'mysql1''' identified by '123456'flush

7、privileges;create database quartz_ams;show databases;show master status;* 1. row * File: mysql-bin.000005(這是上面show命令 查詢出來的信息，用于備的配置) Position: 107 Binlog_Do_DB: test,quarzt_amsBinlog_Ignore_DB: mysql說明：“mysql1”為給備創(chuàng)建的一個用戶，“123456”為密碼；備mysql(192.168.1.246)設(shè)置主mysql的IP和同步賬戶mysql -u root -p登陸后執(zhí)行change ma

8、ster to master_host='',master_user='mysql1', master_password='123456', master_log_file='mysql-bin.000005', master_log_pos=107;start slave;show slave statusG;同樣，也可以為備設(shè)置同步賬戶，主將備設(shè)置為master，即實現(xiàn)雙向互為主備。(按下面配置即可)備mysql(192.168.1.246)添加mysql同步用戶，賦予相關(guān)權(quán)限，mysql -u root -p登陸后執(zhí)行g(shù)ra

9、nt replication slave,file on *.* to 'mysql2''7' identified by '123456'flush privileges;create database quartz_ams;show databases;show master status;* 1. row * File: mysql-bin.000003 Position: 305 Binlog_Do_DB: test,quarzt_amsBinlog_Ignore_DB: mysql主mysql(192.168.1.247)設(shè)置備mysq

10、l的IP和同步賬戶mysql u root p登陸后執(zhí)行change master to master_host='6',master_user='mysql2', master_password='123456', master_log_file='mysql-bin.000003', master_log_pos=305;start slave;show slave statusG;3. 安裝keepalived 依賴安裝yum install gccyum install openssl-develyum install p

11、opt-devel1. tar zxvf keepalived-.tar.gz2. cd keepalived-3. ./configure -prefix=/usr/local/keepalived4. make && make install  5. cp /usr/local/keepalived/sbin/keepalived /usr/sbin/6. cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/7. cp /usr

12、/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/8. mkdir /etc/keepalived9. cd /etc/keepalived/主mysql的keepalived.conf配置文件/etc/keepalieved/keepalived.conf! Configuration File for keepalivedglobal_defs router_id MYSQL_NODE_1vrrp_script chk_mysql_port script "</dev/tcp/127.0.0.1/3306&qu

13、ot; interval 3 weight -2 fail 1 rise 1vrrp_instance V_MYSQLSERVER state MASTER interface eth0 lvs_sync_daemon_inteface eth0 virtual_router_id 70 priority 100 advert_int 2 authentication auth_type PASS auth_pass 4321 track_script chk_mysql_port virtual_ipaddress 注：藍色加粗字體部分，可根據(jù)實際情況調(diào)整 為虛擬IP（不能有沖突），客戶端訪

14、問時配成此IP，keepalieved會自動轉(zhuǎn)到具體的server;priority的值：主為100，備為99主keepalived配置備keepalived配置/etc/init.d/keepalived start （系統(tǒng)重啟后要啟動，最好放入守護進行隨系統(tǒng)一起啟動）tail -f /var/log/messages4. 驗證主mysql(192.168.1.247)建表并插入數(shù)據(jù)mysql u root p登陸后執(zhí)行use quartz_ams;drop table if exists quartz_ams.person;create table quartz_ams.person(id

15、 int unsigned not null primary key auto_increment, name varchar(40),birthday date)engine=innodb ;insert into quartz_ams.person(name, birthday) values('Cat',now();select * from quartz_ams.person;從mysql6)插入數(shù)據(jù)mysql u root p登陸后執(zhí)行select * from quartz_ams .person;insert into quartz_ams.person(name, birthday) values('Dog',now();select * from quartz_ams.person;主mysql(192.168.1.247)檢查是否已經(jīng)雙向同步mysql u root p登陸后執(zhí)行select * from quartz_ams.person;授權(quán)(服務(wù)器有訪問限制)例如，你想myuser使用myp