構建高效能的MYSQL資料庫系統

標籤：check   實驗   伺服器   replicate   res   .sh   環境   status   oca   

實驗環境：

DB1:172.16.1.100

DB2:172.16.1.101

VRRIP:172.16.1.99

步驟：

yum -y install mysql

1。修改DB1的mysql設定檔

server-id = 2 #DB1和DB2的id 必須不同log-bin=mysql-bin  #開啟mysql的二進位日誌功能relay-log = mysql-relay-bin #定義中繼日誌的命名格式replicate-wild-ignore-table=mysql.% #不複製的資料庫或表replicate-wild-ignore-table=test.% #不複製的資料庫或表
replicate-wild-ignore-table=information_schema.% #不複製的資料庫或表

2. 在DB1建立複製使用者並授權

grant replication slave on *.*  to  "chen"@"172.16.1.101" identified by "123456";
show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 | 106         |                    |　　　　　　　　　  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

3.在DB1的mysql庫將DB1設為自己的主伺服器

mysql > change master to         master_host="172.16.1.100",        master_user="chen",        master_password="123456",        master_log_file="mysql-bin.000006",        master_log_pos=106;        

4.然後運行

mysql > start slave;

5.查詢DB1運行狀態

mysql > show slave status;mysql> show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 172.16.1.100                  Master_User: chen                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000009          Read_Master_Log_Pos: 106               Relay_Log_File: mysql-relay-bin.000021                Relay_Log_Pos: 251        Relay_Master_Log_File: mysql-bin.000009             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table: mysql.%,test.%,information_schema.%                   Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 106              Relay_Log_Space: 551              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: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error: 1 row in set (0.00 sec)ERROR: No query specified

6.然後再DB1上配置上面的配置

7.在DB1和DB2上下載keepalived

yum install keepalived

8.配置keepalived的設定檔

[[email protected] ~]# vim /etc/keepalived/keepalived.conf 

[[email protected] ~]# vim /etc/keepalived/keepalived.conf  auth_type PASS auth_pass 1111 } virtual_ipaddress { 172.16.1.99 }}virtual_server 172.16.1.99 3306 { delay_loop 2 #lb_algo rr              #LVS演算法，用不到，我們就關閉了 #lb_kind DR              #LVS模式，如果不關閉，待命伺服器不能通過VIP串連主MySQL  persistence_timeout 50  #同一IP的串連60秒內被分配到同一台真實伺服器 protocol TCP real_server 172.16.1.101 3306 {  #檢測本地mysql，backup也要寫檢測本地mysql weight 3 notify_down /usr/local/keepalived/mysql.sh    #當mysq服down時，執行此指令碼，殺死keepalived實現切換 TCP_CHECK { connect_timeout 3    #連線逾時 nb_get_retry 3      #重試次數 delay_before_retry 3 #稍候再試時間  }}"/etc/keepalived/keepalived.conf" 40L, 1292C written[[email protected] ~]# vim /etc/keepalived/keepalived.conf  auth_type PASS auth_pass 1111 } virtual_ipaddress { 172.16.1.99 }}virtual_server 172.16.1.99 3306 { delay_loop 2 #lb_algo rr              #LVS演算法，用不到，我們就關閉了 #lb_kind DR              #LVS模式，如果不關閉，待命伺服器不能通過VIP串連主MySQL  persistence_timeout 50  #同一IP的串連60秒內被分配到同一台真實伺服器 protocol TCP real_server 172.16.1.101 3306 {  #檢測本地mysql，backup也要寫檢測本地mysql weight 3 notify_down /usr/local/keepalived/mysql.sh    #當mysq服down時，執行此指令碼，殺死keepalived實現切換 TCP_CHECK { connect_timeout 3    #連線逾時 nb_get_retry 3      #重試次數 delay_before_retry 3 #稍候再試時間  }}

　　

 

　

構建高效能的MYSQL資料庫系統