進擊的Mysql——Slave環境搭建及配置

一）環境拓撲

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131228/2232221632-0.png" title="QQ20130614135543.png" />

   今天討論的是mysql叢集這一塊，資源如下所示：

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131228/223222O20-1.png" title="QQ20130614140136.png" />

二）Mysql安裝配置

   這裡不一一贅述，可以選擇原始碼編譯安裝，也可以參考我之前的一篇博文“懶人“速成——linux LAMP環境。

三）節點配置

主節點：

   1.首先在資料庫中建立2個資料庫和表：

#service mysqld start#mysqlmysql>create database www;mysql>use www;mysql>create table www(id int);mysql>insert into www values(1);mysql> select * from www;

   查看資料： 650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131228/22322252K-2.png" title="111.png" />；

   同理建立blog資料庫和表：

mysql>create database blog;mysql>use blog;mysql>create table blog(id int);mysql>insert into blog values(1);mysql> select * from blog;

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131228/2232223049-3.png" title="1.png" />

2.修改My.cnf設定檔

vi etc/my.cnf

[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log-bin=mysql-bin          //日誌為2進位，不需要更改server-id    =1            //為1就是Master,不需要更改binlog-do-db=blog          //需要同步的庫binlog-do-db=www           //需要同步的庫binlog-ignore-db=mysql,test,information_schema   //不需要記錄日誌的資料庫名，多個資料庫中間用逗號(,)隔開                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 innodb_data_home_dir = /usr/local/mysql/data/      //innodb的資料表空間位置innodb_data_file_path = ibdata1:50M:autoextend     //資料表空間的名字，開始50Minnodb_log_group_home_dir = /usr/local/mysql/data/innodb_buffer_pool_size = 256M                     //為系統記憶體的50-80%innodb_additional_mem_pool_size = 20Minnodb_log_file_size = 64Minnodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 1innodb_lock_wait_timeout = 50[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

   3.重啟資料庫

[root@Test01 /]# service mysqld restart

Stopping mysqld:  [  OK  ]Starting mysqld:  [  OK  ]

4.建立有許可權的帳號，讓Slave資料庫訪問主要資料庫

[root@Test01 /]# mysql -u root -pEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.1.69-log Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

mysql> grant replication slave on *.* to repl@"192.168.1.26" identified by '123456';Query OK, 0 rows affected (0.00 sec)

mysql>flush privileges;;

注意：格式:GRANT REPLICATION SLAVE ON *.* TO '帳號'@'從伺服器IP或主機名稱' IDENTIFIED BY '密碼';

 5.備份Master資料庫

mysql> flush tables with read lock;  //不要退出這個終端，否則這個鎖就不生效了。同時取得快照，並記錄日誌和位移量：mysql> show master status;+------------------+----------+--------------+-------------------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB              |+------------------+----------+--------------+-------------------------------+| mysql-bin.000001 |      196 | blog,www     | mysql,test,information_schema |+------------------+----------+--------------+-------------------------------+1 row in set (0.00 sec)

6.開啟另一個終端，對主要資料庫做快照

[root@Test01 mysql]#cd /var/lib/mysql/[root@Test01 mysql]#tar -zcvf backup.tar.gz www blog

 7.庫解鎖

mysql> unlock tables;

從節點：

   1.安裝mysql，方法同上，不在贅述。

   2.修改My.cnf設定檔

[root@Test02 mysql]# vi /etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log-bin=mysql-binserver-id  = 2#master-host     =   192.168.1.10#master-user     =   root#master-password =   584911644#master-port     =  3306#master-log-pos =   196#master-log-file = mysql-bin.000001#master-connect-retry=60replicate-do-db=www        //告訴slave只做www資料庫的更新replicate-do-db=blog       //告訴slave只做blog資料庫的更新log-slave-updates[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid"/etc/my.cnf" 23L, 551C

3.把之前主要資料庫壓縮的資料拷貝到對應位置

我這裡用的是SecureFX,當然，方法很多，也不細說了，可謂是條條大路通羅馬。

4.啟動資料庫

[root@Test02 mysql]# service mysqld start

Starting mysqld:  [  OK  ]

5.配置，啟動Slave

注意：Slave_IO_Running：是否要從 Master Server 複製 Binary Log 資料，必須為 Yes。Slave_SQL_Running：是否要執行從 Master Server 複製過來的 Binary Log 資料，必須為 Yes。

mysql> slave stop;Query OK, 0 rows affected (0.00 sec)mysql> change master to master_host='192.168.1.10',master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=196;Query OK, 0 rows affected (0.04 sec)mysql> slave start;Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.1.10                  Master_User: repl                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000001          Read_Master_Log_Pos: 339               Relay_Log_File: mysqld-relay-bin.000002                Relay_Log_Pos: 394        Relay_Master_Log_File: mysql-bin.000001             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB: www,blog          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: 339              Relay_Log_Space: 550              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)

四）後期測試

主節點-資料增加：

mysql>  insert into www values(2);Query OK, 1 row affected (0.00 sec)mysql> select * from www;+------+| id   |+------+|    1 ||    2 |+------+2 rows in set (0.00 sec)

從節點資料同步驗證：

mysql> select * from www;+------+| id   |+------+|    1 ||    2 |+------+2 rows in set (0.00 sec)

主節點-資料刪除：

mysql> delete from www  where id ='2';Query OK, 1 row affected (0.04 sec)mysql> select * from www;+------+| id   |+------+|    1 |+------+1 row in set (0.00 sec)

從節點資料同步驗證：

mysql> select * from www;+------+| id   |+------+|    1 |+------+1 row in set (0.00 sec)

綜述：

   Test02，從資料庫上能做到同步資料庫的更新操作，目前是WWW和blog兩張表。當然，這裡只是拋磚引玉的作用，大家可以舉一反三，根據自己實際需求，做出最好的環境搭建和配置。

   最近在弄RHCS和keepalived，閑來有空就記錄幾筆資料庫slave配置，方便大家學習和自己的積累。

本文出自 “Aaron” 部落格，請務必保留此出處http://qishiding.blog.51cto.com/3381613/1221960