程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> mysql高可用性復制

mysql高可用性復制

編輯：關於MYSQL數據庫

Centos5.2
MySQL 5.1.44
Master:192.168.x.166
Slaver:192.168.x.172 Master數據庫安裝操作
下載MySQL
http://dev.MySQL.com/downloads/ 參考INSTALL-SOURCE
shell> groupadd MySQL
shell> useradd -g mysql MySQL
shell> gunzip < MySQL-VERSION.tar.gz | tar -xvf -
shell> cd MySQL-VERSION
shell> ./configure --prefix=/usr/local/MySQL
shell> make
shell> make install
shell> cp support-files/my-medium.cnf /etc/my.cnf
shell> cd /usr/local/MySQL
shell> chown -R MySQL .
shell> chgrp -R MySQL .
shell> bin/mysql_install_db --user=MySQL
shell> chown -R root .
shell> chown -R MySQL var
shell> bin/mysqld_safe --user=MySQL & CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3 -felide-constructors \
       -fno-exceptions -fno-rtti -fomit-frame-pointer -ffixed-ebp"
    ./configure \
       --prefix=/usr/local/MySQL --enable-assembler \
       --with-MySQLd-ldflags=-all-static \
       --with-clIEnt-ldflags=-all-static \
       --with-unix-socket-path=/usr/local/mysql/tmp/MySQL.sock \
       --with-charset=utf8 \
       --with-collation=utf8_general_ci \
       --with-extra-charsets=all (1)Chmod +x support-files/MySQL.server
(2)cp support-files/mysql.server /etc/init.d/MySQLd
(3)chkconfig –add MySQLd
(4)chkconfig –level 345 MySQLd on
(5)service MySQLd start/stop測試
Slave數據庫安裝操作如上。主從設置
需要復制的數據庫為：shops
MySQL> desc user; | FIEld | Type     | Null | Key | Default | Extra | | name | char(30) | NO   |     | NULL    |       | 1 row in set (0.00 sec) Master端配置
(1)修改my.cnf配置文件
[MySQLd]
port            = 3306
socket          = /usr/local/mysql/tmp/MySQL.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log-bin=MySQL-bin
binlog_format=mixed
server-id       = 1
binlog-do-db=shops
[MySQLdump]
quick
max_allowed_packet = 16M
[MySQL]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[MySQLhotcopy]
interactive-timeout (2)給予用戶復制權限
grant replication slave on *.* to 'repbadboy'@'192.168.x.172' identifIEd by '111111';
flush privileges; 在借用mysqldump的—master-data備份數據的前提下，就需要二進制日志功能開啟，所以在這前，沒有開啟的朋友，需要(配置my.cnf開啟log-bin見上面)重啟下mysql服務喽。在此申明，這些所有的操作，都應該在用戶量非常少得情況下使用。注：--master-data,這個參數作用：MySQLdump會在dump文件中產生一條change master to命令，命令中記錄了dump時刻所對應的詳細的Log Position信息。如下：
[root@centos01 MySQL]# more /tmp/shops.sql|grep "CHANGE MASTER"
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=199; (3)備份shops數據(MySQLdump)
[a.] flush tables with read lock;( 全局讀鎖定)
[b.]show master status\G(這個要記住，或者到MySQLdump後的dump文件中找有關log與log_pos有關的數據)
MySQL> show master status\G
*************************** 1. row ***************************
            File: MySQL-bin.000001
        Position: 199
    Binlog_Do_DB: shops
Binlog_Ignore_DB:
1 row in set (0.00 sec)
這個數據應該與MySQLdump中的dump文件的信息是一樣的。
[c.] bin/MySQLdump --master-data shops >/tmp/shops.sql
[e.]unlock tables; 從服務器配置
(1)恢復shops數據(創建shops庫)
MySQL shops< /tmp/shops.sql
(2)從數據庫的my.cnf配置
[clIEnt]
port            = 3306
socket          = /usr/local/mysql/tmp/MySQL.sock
[MySQLd]
port            = 3306
socket          = /usr/local/mysql/tmp/MySQL.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
#log-bin=MySQL-bin
#binlog_format=mixed
server-id       = 2
replicate-do-db=shops
[MySQLdump]
quick
max_allowed_packet = 16M
[MySQL]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[MySQLhotcopy]
interactive-timeout (3) change master to master_host='192.168.x.166' ,master_user='repbadboy', master_passWord='111111' ,master_log_file='MySQL-bin.00001',master_log_pos=199;
(4)Start slave
a.   (如果重啟了master服務器,那麼Slave服務器只需等待默認的60秒(Connect_Retry)就會重新連接來完成復制工作) [測試模擬背景：起初，網站比較小，數據提供僅一台mysql。後期發現，一台mysql無法為客戶提供優質的訪問了，為此需要采用MySQL復制技術，一方面做到讀寫分離，提高性能，另一方面保障了數據的安全。] 假設數據需要復制的數據庫為shops,表為user，
MySQL> desc user; | FIEld | Type     | Null | Key | Default | Extra | | name | char(30) | NO   |     | NULL    |       | 1 row in set (0.00 sec) 因運行了一段時間，肯定有數據的，如下：
MySQL> select * from user; | name     | | badboy01 |
| badboy02 |
| badboy03 |
| badboy04 |
| badboy05 | 5 rows in set (0.00 sec) (1)   修改其my.cnf（開啟日志功能）
log-bin=MySQL-bin
binlog_format=mixed
server-id       = 1
binlog-do-db=shops (2)   添加復制帳號
grant replication slave on *.* to 'repbadboy'@'192.168.x.172' identifIEd by '111111'; (3)   重啟service MySQLd restart 再次假設，重啟後就有部分數據更新。如:
MySQL> select * from user; | name     | | badboy01 |
| badboy02 |
| badboy03 |
| badboy04 |
| badboy05 |
| badboy06 |?
| badboy07 |?注意這兩個數據是重啟數據庫後更新的數據，但然如果訪問量真的很大的話，會出現這種情況的，不過，它已經被記錄到二進制日志中了。不用擔心。。。 7 rows in set (0.00 sec)
(4)
MySQL> flush tables with read lock;(鎖表)
Query OK, 0 rows affected (0.00 sec) MySQL> show master status\G(查看master狀態)
*************************** 1. row ***************************
            File: MySQL-bin.000001
        Position: 304
    Binlog_Do_DB: shops
Binlog_Ignore_DB:
1 row in set (0.00 sec) mysql> (4)   bin/mysqldump --master-data shops >/tmp/shops.sql (mysqldump備份數據) [root@centos01 MySQL]# more /tmp/shops.sql |grep "CHANGE MASTER"
CHANGE MASTER TO MASTER_LOG_FILE='MySQL-bin.000001', MASTER_LOG_POS=304; (5)unlock tables; 再再次假設，但解鎖後，又有部分數據插入。如下：
MySQL> select * from user; | name     | | badboy01 |
| badboy02 |
| badboy03 |
| badboy04 |
| badboy05 |
| badboy06 |
| badboy07 |
| badboy08 |?
| badboy09 |?
| badboy10 |?這3條數據是在解鎖後插入的。 10 rows in set (0.00 sec) 現在Master端的工作已經做完，現在准備做slave端。
(1) 配置my.cnf
#log-bin=MySQL-bin
#binlog_format=mixed
server-id       = 2
replicate-do-db=shops、重啟service MySQLd restart (2) 恢復shops
恢復之前看下MySQL數據存放位置的文件
[root@centos01 MySQL]# ls var/
centos01.tianciyt.com.cn.err centos01.tianciyt.com.cn.pid MySQL shops test
bin/MySQL shops< /tmp/shops.sql
恢復後看下MySQL數據存放位置的文件
[root@centos01 MySQL]# ls var/
centos01-relay-bin.000001 centos01.tianciyt.com.cn.err master.info relay-log.info test
centos01-relay-bin.index   centos01.tianciyt.com.cn.pid MySQL        shops
[點評]這就是—master-data的作用所在。 (3) 查看下當前的數據情況
MySQL> select * from user; | name     | | badboy01 |
| badboy02 |
| badboy03 |
| badboy04 |
| badboy05 |?這是在沒有開啟二進制日志時的數據
| badboy06 |
| badboy07 |?這是在開啟二進制日志時並且在全局讀鎖定之前的數據，(此時預言)還有badboy08, badboy09, badboy10……應該在我們配置好slave後，可以復制過來的。 7 rows in set (0.00 sec)
(4)查看slave狀態
MySQL> show slave status\G
Empty set (0.00 sec)
(5)   啟動slave准備
change master to master_host='192.168.x.166' ,master_user='repbadboy', master_passWord='111111',MASTER_LOG_FILE='MySQL-bin.000001', MASTER_LOG_POS=304;
這兩個標紅色的，應該注意到與上面的shops.sql和show master status\G是一樣的。
[再看下shops.sql
[root@centos01 MySQL]# more /tmp/shops.sql|grep "CHANGE MASTER"
CHANGE MASTER TO MASTER_LOG_FILE='MySQL-bin.000001', MASTER_LOG_POS=304; 最後，
MySQL> start slave;
Query OK, 0 rows affected (0.00 sec)
(6)   查看到slave狀態
MySQL> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.x.166
                  Master_User: repbadboy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: MySQL-bin.000001
          Read_Master_Log_Pos: 601
               Relay_Log_File: centos01-relay-bin.000002
                Relay_Log_Pos: 548
        Relay_Master_Log_File: MySQL-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: shops
          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: 601
              Relay_Log_Space: 706
              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: 0
Master_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)
(7)檢查數據
MySQL> select * from user;                                                                                                          +----------+
| name     | | badboy01 |
| badboy02 |
| badboy03 |
| badboy04 |
| badboy05 |
| badboy06 |
| badboy07 |
| badboy08 |?
| badboy09 |?
| badboy10 |?這三條數據就是我預言的證明，哈哈。我們再來試試。。 10 rows in set (0.00 sec) (7)   再測試下，在master插入badboy11,badboy12。。。
MySQL> insert into user values('badboy11');
Query OK, 1 row affected (0.00 sec) MySQL> insert into user values('badboy12');
Query OK, 1 row affected (0.00 sec) 查看master數據
MySQL> select * from user; | name     | | badboy01 |
| badboy02 |
| badboy03 |
| badboy04 |
| badboy05 |
| badboy06 |
| badboy07 |
| badboy08 |
| badboy09 |
| badboy10 |
| badboy11 |
| badboy12 | 12 rows in set (0.01 sec)
再查看slave端如何,
MySQL> select * from user; | name     | | badboy01 |
| badboy02 |
| badboy03 |
| badboy04 |
| badboy05 |
| badboy06 |
| badboy07 |
| badboy08 |
| badboy09 |
| badboy10 |
| badboy11 |
| badboy12 | 12 rows in set (0.01 sec) [/img]..