過程 19.1. Master 設置步驟

1. 配置 my.cnf 檔案

   確保主伺服器主機上my.cnf檔案的[mysqld]部分包括一個log-bin選項。該部分還應有一個server-id=Master_id選項

   # vim /etc/mysql/my.cnf
   
   server-id               = 1
   log_bin                 = /var/log/mysql/mysql-bin.log
   expire_logs_days        = 10
   max_binlog_size         = 100M
   binlog_do_db            = test
   binlog_ignore_db        = mysql
   					

   bind-address預設是127.0.0.1你必須更改它，否則Slave將無法連結到 Master

   #bind-address		= 127.0.0.1
   bind-address		= 0.0.0.0
   					

   重啟伺服器

   neo@netkiller:~$ sudo /etc/init.d/mysql reload
    * Reloading MySQL database server mysqld          [ OK ]
   					

   建議使用reload,如果不起作用再用restart

   					
   mysql> SHOW GLOBAL VARIABLES like 'server_id';
   +---------------+-------+
   | Variable_name | Value |
   +---------------+-------+
   | server_id     | 1     |
   +---------------+-------+
   1 row in set (0.00 sec)
   					
   					

2. 登錄slave伺服器，測試主庫3306工作情況，如果看到下面相關信息表示工作正常。

   					
   # telnet 192.168.1.246 3306
   Trying 192.168.1.246...
   Connected to 192.168.1.246.
   Escape character is '^]'.
   I
   5.1.61-0ubuntu0.11.10.1-log1W<gs/*'#}p<u[J=5//:
   					
   					

3. 創建賬戶並授予REPLICATION SLAVE權限

   					
   mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'replication'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
   mysql> FLUSH PRIVILEGES;
   					
   					

   創建監控賬號monitor（可選項），monitor 使用SHOW MASTER STATUS和SHOW SLAVE STATUS命令但沒有複製權限

   GRANT REPLICATION CLIENT ON *.* TO monitor@'192.168.245.131' IDENTIFIED BY 'monitorpass'

4. 鎖表禁止寫入新數據

   					
   mysql> FLUSH TABLES WITH READ LOCK;
   					
   					

5. 查看Master 工作狀態

   					
   mysql> SHOW MASTER STATUS;
   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000002 |      106 | test         | mysql            |
   +------------------+----------+--------------+------------------+
   1 row in set (0.00 sec)
   					
   					

   如果顯示下面內容表示，配置不正確

   					
   
   mysql> SHOW MASTER STATUS;
   Empty set (0.02 sec)
   					
   					

   取得快照並記錄日誌名和偏移量後，可以在主伺服器上重新啟用寫活動

   					
   mysql> UNLOCK TABLES;
   					
   					

過程 19.2. Slave 設置步驟

1. 配置my.cnf

   從伺服器的ID必須與主伺服器的ID不相同,如果設置多個從伺服器，每個從伺服器必須有一個唯一的server-id值，必須與主伺服器的以及其它從伺服器的不相同。

   # vim /etc/mysql/my.cnf
   
   [mysqld]
   server-id               = 2
   					

2. # service mysql restart
   mysql start/running, process 22893
   					

3. 指定 master 相關參數

   在從伺服器上執行下面的語句，用你的系統的實際值替換選項值

   					
   mysql> CHANGE MASTER TO
   	->     MASTER_HOST='master_host_name',
   	->     MASTER_USER='replication_user_name',
       ->     MASTER_PASSWORD='replication_password',
       ->     MASTER_LOG_FILE='recorded_log_file_name',
       ->     MASTER_LOG_POS=recorded_log_position;
   	   				
   					

   如果是全新伺服器，空資料庫可以忽略MASTER_LOG_FILE與MASTER_LOG_POS

   CHANGE MASTER TO MASTER_HOST='192.168.245.129', MASTER_USER='replication', MASTER_PASSWORD='slavepass';
   					

   如果是複製已經存在的資料庫需要MASTER_LOG_FILE與MASTER_LOG_POS選項

   首先到Master上運行 show master status 找到File與Position

   					
   mysql> show master status \G
   *************************** 1. row ***************************
               File: mysql-bin.000009
           Position: 3988
       Binlog_Do_DB:
   Binlog_Ignore_DB:
   1 row in set (0.00 sec)
   					
   					

   CHANGE MASTER TO
        MASTER_HOST='192.168.2.1',
        MASTER_USER='replication',
        MASTER_PASSWORD='kJZBTo3BjMx9AnmD9Ryn',
        MASTER_LOG_FILE='mysql-bin.000009',
        MASTER_LOG_POS=3988;
   					

4. 啟動從伺服器綫程

   					
   mysql> START SLAVE;
   Query OK, 0 rows affected (0.00 sec)
   					
   					

5. SLAVE STATUS

   					
   mysql> SHOW SLAVE STATUS\G
   *************************** 1. row ***************************
                Slave_IO_State: Connecting to master
                   Master_Host: 192.168.245.129
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File:
           Read_Master_Log_Pos: 4
                Relay_Log_File: mysqld-relay-bin.000002
                 Relay_Log_Pos: 98
         Relay_Master_Log_File:
              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:
                    Last_Errno: 0
                    Last_Error:
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 0
               Relay_Log_Space: 98
               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
   1 row in set (0.00 sec)
   					
   					

1. 登錄 master

   複製進程的信息

   SHOW PROCESSLIST語句可以提供在主伺服器上和從伺服器上發生的關於複製的信息

   mysql> SHOW PROCESSLIST\G
   *************************** 1. row ***************************
        Id: 62
      User: replication
      Host: ken-hx409.local:36465
        db: NULL
   Command: Binlog Dump
      Time: 679
     State: Has sent all binlog to slave; waiting for binlog to be updated
      Info: NULL
   *************************** 2. row ***************************
        Id: 64
      User: root
      Host: localhost
        db: NULL
   Command: Query
      Time: 0
     State: NULL
      Info: SHOW PROCESSLIST
   2 rows in set (0.00 sec)
   
   					

2. 登錄從庫，查看複製綫程

   					
   mysql> SHOW PROCESSLIST\G
   *************************** 1. row ***************************
        Id: 273
      User: root
      Host: localhost
        db: NULL
   Command: Query
      Time: 0
     State: NULL
      Info: SHOW PROCESSLIST
   *************************** 2. row ***************************
        Id: 276
      User: system user
      Host:
        db: NULL
   Command: Connect
      Time: 2
     State: Waiting for master to send event
      Info: NULL
   *************************** 3. row ***************************
        Id: 277
      User: system user
      Host:
        db: NULL
   Command: Connect
      Time: 2
     State: Has read all relay log; waiting for the slave I/O thread to update it
      Info: NULL
   3 rows in set (0.00 sec)
   					
   					

   如果沒有複製進程，請使用start slave;啟動

   					
   mysql> SHOW PROCESSLIST\G
   *************************** 1. row ***************************
        Id: 273
      User: root
      Host: localhost
        db: NULL
   Command: Query
      Time: 0
     State: NULL
      Info: SHOW PROCESSLIST
   1 row in set (0.02 sec)
   
   mysql> start slave;
   Query OK, 0 rows affected (0.00 sec)
   					
   					

3. 登錄 master

   					
   mysql> insert into foo(id,data) values(2,'Hello world!!!');
   Query OK, 1 row affected (0.00 sec)
   					
   					

4. 登錄 slave

   					
   mysql> select * from foo;
   					
   					

   在master伺服器上插入一條記錄，你可以立刻在slave伺服器上看到變化。

1. Master 鎖表禁止寫入新數據

   					
   mysql> FLUSH TABLES WITH READ LOCK;
   					
   					

2. Slave 停止複製進程

   					
   mysql> stop slave;
   					
   					

3. 備份Master資料庫

   					
   mysqldump yourdb | gzip > yourdb.sql.gz
   					
   					

4. 恢復資料庫

   如果使用mysqldump備份主伺服器的數據，將轉儲檔案裝載到從伺服器

   					
   # zcat yourdb.sql.gz | mysql -u root -p yourdb
   					
   					

5. 啟動 Slave 複製進程

   					
   mysql> start slave;
   					
   					

6. 解除 Master 表鎖定

   					
   mysql> UNLOCK TABLES;