三台機器:
director(eth0192.168.0.8, vip eth0:0: 192.168.0.101)
real server1(eth0 rip: 192.168.0.140 vip lo:0:192.168.0.101)
real server2(eth0 rip: 192.168.0.141, vip lo:0:192.168.0.101)
1、自己編寫的一鍵源碼安裝的lnmp腳本
2、安裝LVS（DR）
yum install ipvsadm
Director 上 vim /usr/local/sbin/lvs_dr.sh //增加
vi /usr/local/sbin/lvs_DR.sh #編輯一個腳本
#!/bin/bash
# directory 伺服器開啟路由轉發功能：
echo 1 > /proc/sys/net/ipv4/ip_forward
ipv='/sbin/ipvsadm'
vip=192.168.0.100
rs1=192.168.0.7
rs2=192.168.0.5
ifconfig eth0:0 $vip broadcast $vip netmask255.255.255.255 up
route add -host $vip dev eth0:0
$ipv -C
$ipv -A -t $vip:80 -s rr
$ipv -a -t $vip:80 -r $rs1:80 -g -w 1 #-g代表是DR模式
$ipv -a -t $vip:80 -r $rs2:80 -g -w 1
兩台rs上：vim/usr/local/sbin/lvs_dr_rs.sh
#! /bin/bash
ifconfig lo:0 $vip broadcast $vip netmask255.255.255.255 up
route add -host $vip lo:0
echo "1">/proc/sys/net/ipv4/conf/lo/arp_ignore
echo "2">/proc/sys/net/ipv4/conf/lo/arp_announce
echo "1">/proc/sys/net/ipv4/conf/all/arp_ignore
echo "2">/proc/sys/net/ipv4/conf/all/arp_announce
3、 分别配置三台機器的keepalived的配置：（配置完畢啟動是先從後主）
yum安裝keepalived,
Keeplived(負載均衡+HA合成的二者為一體)
vi /etc/keepalived/keepalived.conf
##########MASTER###########
global_defs {
#出現故障時候發郵件給誰# notification_email {
#}
#出現故障從哪裡發郵件出去##notification_email_from [email protected]
##第三方的smtp##smtp_server mail.qq.com
#smtp_connect_timeout30
router_id LVS1
}
#vrrp_script chk_http_port {
# script "/etc/keepalived/nginx_check.sh"
# interval 2
# weight 2
vrrp_sync_group VS_group {
group {
VS_1
}
vrrp_instance VS_1 {
state MASTER #備用伺服器上為 BACKUP，主是MASTER
interface eth0
lvs_sync_daemon_interface eth0
virtual_router_id 60
priority 150 #備用伺服器上為100,主是150
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.0.101
}
virtual_server 192.168.0.101 80 {
delay_loop 6 #(每隔10秒查詢realserver狀态)
lb_algo rr #(lvs 算法)
lb_kind DR #(Direct Route)
persistence_timeout 20 #(同一IP的連接配接60秒内被配置設定到同一台realserver)
protocol TCP #(用TCP協定檢查realserver狀态)
real_server 192.168.0.140 80 {
weight 100 #(權重)
TCP_CHECK {
connect_timeout 10 #(10秒無響應逾時)
nb_get_retry 3
delay_before_retry 3
connect_port 80
}
real_server192.168.0.141 80 {
weight 100
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 80
}
}
至此，lvs(DR)+keepalived已完成
4、添加防火牆規則後并儲存：
iptables -A INPUT -d 192.168.2.0/24 -j ACCEPT
iptables -A INPUT -p vrrp -j ACCEPT #LVS DR模式。當使用者請求LVS-DR的VIP時，隻有DR響應用戶端的ARP廣播包，允許vrrp虛拟路由器備援協定
iptables -A INPUT -p icmp -j ACCEPT
iptables -A INPUT -jREJECT --reject-with icmp-host-prohibited
iptables -A FORWARD -j REJECT --reject-withicmp-host-prohibited
service iptables save
5、mysql主從配置
理論:主（A）的資料庫資料有變化記錄一個bin_log，再推給從（B），B資料庫的bin_log會記錄去更改自己資料庫（資料同步）
（1）打開主的mysql配置（my.cnf）添加或打開注釋如下配置:
[nysqld]
log-bin=mysql-bin #打開這兩個注釋，log-bin的值可以自定義
server id = 1
binlog-do-db=db1 #隻針對指定資料庫做主從
#binlog-ignore-db=mysql #打開注釋是不去同步指定的庫（黑名單）
#log-slave-updates = true #是否繼續傳遞下去為“是”，這個是用于主主，若是多個主的server id
(2)從的my.cnf将原來的配置改為以下： #（用于多執行個體）
[mysqld]
port = 3307
socket =/tmp/mysql_slave.sock
datadir =/data/mysql_slave
server id =1 改成server id =2
replicate-do-db=db1 #隻針對指定資料庫做主從
#replicate-ignore-db=mysql #不去同步指定的庫（黑名單）
(2)<1>cp /etc/init.d/mysqld /etc/init.d/mysqldslave
<2>vim /etc/init.d/mysqldslave将原來的配置改為以下：
找到第一個basedir:
basedir=/usr/local/mysql_slave
datadir=/data/mysql_slave
conf=$basedir/my.cnf
PS:上面mysql的從是用于多執行個體
（2）從的mysql配置：
server id =2 #server id的變更
6、測試能否進入資料庫（前提在/etc/profile.d/path.sh裡加入PATH=$PATH:/usr/local/mysql/bin後source /etc/profile.d/path.sh; 通過sock登入：mysql -S sock所在位置
(想添加多一個mysql,按照slave的做法即可)
7、做主從前的最後一個主要配置（主從資料庫庫資訊一緻）：
mysqldump -S /tmp/mysql.sock mysql > 123.sql #備份mysql資料庫
mysql -S /tmp/myslq.sock db1 < 123.sql #将mysql資料庫裡的東西導入db1資料庫
8、進入主的mysql:
mysql>grant replication slave on *.* to ‘repl’@’127.0.0.1’ identifiedby ‘123456’; #針對本地做主從，隻賦予replication權限
mysql>grant replication slave on *.* to 'repl'@'從ip’ identifiedby ‘密碼’;
mysql> flush privileges;
mysql> flush tables with read lock; #對表的讀鎖死（read lock）的作用是讓show master status的file和position不變
mysql> show master status; #檢視master資料資訊
+------------------+----------+--------------+------------------+
| File | Position| Binlog_Do_DB | Binlog_Ignore_DB |
| mysql-bin.000009 | 106 | db1 | |
1 row in set (0.00 sec)
9、在從的将主傳過來的123.sql導入本地資料庫
主的操作：scp 123.sql 從ip:123.sql
[root@user16 ~]# mysql -S /tmp/mysql_slave.sock -e "createdatabase db1" #在從上建立db1資料庫
[root@user16 ~]# mysql -S /tmp/mysql_slave.sock db1 < 123.sql #導入備份的sql去從的db1,實作主從相同
mysql> slave stop; #先停掉從的
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql>change master tomaster_host='主ip',master_port=3306,master_user='repl',master_password='',master_log_file='mysql-bin.000009',master_log_pos=106; #這一步很重要，核心指令
mysql>flush privileges;
mysql>slave start;
mysql> show slave status\G; #檢視從庫的配置，是否成功看Slave_IO_Running和Slave_SQL_Running是否為 Yes
至此，沒有出現任何錯誤，mysql主從已基本完成
以下是mysql主從過程中出現的一些錯誤或解決方法：
mysql> show slave status\G;
*************************** 1. row***************************
Slave_IO_State: Connecting tomaster
Master_Host: 192.168.0.140
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000048
Read_Master_Log_Pos: 106
Relay_Log_File:user14-relay-bin.000003
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000048
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB: test
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 106
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2013
Last_IO_Error: error connectingto master '[email protected]:3306' - retry-time: 60 retries: 86400
Last_SQL_Errno: 0
Last_SQL_Error:
ERROR:
No query specified
檢視mysql的slave錯誤日志
[root@user14 ~]# tail -f/data/mysql/user14.err
160227 21:58:18 [Note] Slave I/O threadexiting, read up to log 'mysql-bin.000048', position 106
160227 21:58:18 [Note] Error reading relaylog event: slave SQL thread was killed
160227 22:01:11 [Note] 'CHANGE MASTER TOexecuted'. Previous state master_host='192.168.0.140', master_port='3306',master_log_file='mysql-bin.000048', master_log_pos='106'. New statemaster_host='192.168.0.140', master_port='3306',master_log_file='mysql-bin.000048', master_log_pos='106'.
160227 22:01:33 [Note] Slave SQL threadinitialized, starting replication in log 'mysql-bin.000048' at position 106,relay log './user14-relay-bin.000001' position: 4
160227 22:01:33 [ERROR] Slave I/O: errorconnecting to master '[email protected]:3306' - retry-time: 60 retries: 86400, Error_code: 2013
160227 22:01:41 [Note] Slave I/O threadkilled while connecting to master
160227 22:01:41 [Note] Slave I/O threadexiting, read up to log 'mysql-bin.000048', position 106
160227 22:01:41 [Note] Error reading relaylog event: slave SQL thread was killed
160227 22:01:47 [Note] Slave SQL threadinitialized, starting replication in log 'mysql-bin.000048' at position 106,relay log './user14-relay-bin.000001' position: 4
160227 22:01:47 [ERROR] Slave I/O: errorconnecting to master '[email protected]:3306' - retry-time: 60 retries: 86400, Error_code: 2013
Last_IO_Errno: 1593
Last_IO_Error: Fatal error: Theslave I/O thread stops because master and slave have equal MySQL server ids;these ids must be different for replication to work (or the--replicate-same-server-id option must be used on slave but this does notalways make sense; please check the manual before using it).
配置完Error_code: 2013從沒重新開機mysql服務，Last_IO_Errno: 1593是與主的id号沖突
[root@directory ~]# mysqldump -S/tmp/mysql.sock -p123456 mysql >aaa.sql
-- Warning: Skipping the data of tablemysql.event. Specify the --events option explicitly.
mysqldump-uroot -pxxxxx --events --ignore-table=mysql.events 需要備份庫 > 自定義備份庫名字
ERROR 1201 (HY000): Could not initializemaster info structure; more error messages can be found in the MySQL error log
reset slave後再重新授權
本文轉自wsw26 51CTO部落格，原文連結：http://blog.51cto.com/wsw26/1752975，如需轉載請自行聯系原作者
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)