mysql 主從同步

    mysql主從復制(replication同步)現在企業用的比較多,也很成熟.它有以下優點:

1.降低主服務器壓力,可在從庫上執行查詢工作.

2.在從庫上進行備份,避免影響主服務器服務.

3.當主庫出現問題時,可以切換到從庫上.

不過,用它做備份時就會也有弊端,如果主庫有誤操作的話,從庫也會收到命令.

    下面直接進入操作.這里使用的是debian5操作系統,mysql5.0,默認引擎innodb

     10.1.1.45 主庫

     10.1.1.43 從庫

1.設置主庫

1)修改主庫my.cnf,這里主要是server-id一定主從不要設置成一樣的.打開binlog日志

log-bin         = /opt/log.bin/45
server-id       = 45

mysql> grant REPLICATION SLAVE ON *.* TO 'repl'@'10.1.1.43' IDENTIFIED BY 'replpass';

注意:mysql的權限系統在實現上比較簡單,相關權限信息主要存儲在幾個系統表中:mysql.user,mysql.db,mysql.host,mysql.table_priv,mysql.columm_priv.由于權限信息的數據量比較小,訪問又非常頻繁,所以mysql在啟動的時候,就會將所有的權限信息都加載到內存中,并保存在幾個特定的結構里.這就使得每次手動修改了相關權限表之后,都需要執行flush privileges，通知mysql重新加載mysql的權限信息.當然,如果通過grants,revoke或drop user 命令來修改相關權限，則不需要手動執行flush privileges命令.

3)在主服務器上導出數據庫當時的快照,傳給從庫上.

root@10.1.1.45:mysql# mysqldump -uroot -p --single-transaction --flush-logs --master-data --all-databases > all.sql

--single-transaction:這個選項能夠讓innoDB和Falcon數據表在備份過程中保持不變.這一做法的關鍵在于它是在同一個事務里來導入各有關數據表的.mysqldump使用repeatable read事務隔離層來生成一份穩定一致的轉儲文件,同時不會阻塞其他客戶(對于非事務性表,轉儲過程可能有變化),它不能與--lock-all-tables選項一起使用.

--flush-logs:在導出工作開始之前先清空mysql服務器的日志文件.這樣更容易恢復操作,知道在檢查點時間之后創建的二進制日志文件是在備份給定數據庫之后完成的.結合使用--lock-all-tables或--master-data,只有在所有數據表都鎖定之后才清除日志.這個選項需要具備reload權限.

--master-data:使用后mysqldump會在dump文件中產生changer master to命令,里面記錄了dump時刻所對應的詳細的log position信息.

root@10.1.1.45:mysql# sed -n '1,40p' all.sql 
-- MySQL dump 10.11
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version   5.0.51a-24+lenny1-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

CHANGE MASTER TO MASTER_LOG_FILE='45.000064', MASTER_LOG_POS=98;

--
-- Current Database: `bbs`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `bbs` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `bbs`;

--
-- Table structure for table `davehe`
--

DROP TABLE IF EXISTS `davehe`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `davehe` (

2.設置從庫

1).修改從庫my.cnf

server-id = 43   #主從可1對多 從各id不能相同

root@10.1.1.43:tmp# cat all.sql | mysql -uroot -p 

mysql> change master to master_host='10.1.1.45', master_user='repl',master_password='replpass',master_log_file='45.000064',master_log_pos=98;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
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: 10.1.1.45
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: 45.000064
        Read_Master_Log_Pos: 98
             Relay_Log_File: mysqld-relay-bin.000002
              Relay_Log_Pos: 228
      Relay_Master_Log_File: 45.000064
           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: 98
            Relay_Log_Space: 228
            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
1 row in set (0.00 sec)


ERROR: 
No query specified

測試OK

當然這只是最簡單的配置

還有很多參數可根據環境需求變化.

比如

replicate-do-db=test    過濾拉主日志到從只需要這個庫和下面的表
replicate-wild-do-table=test.dave
replicate-wild-do-table=test.davehe

mysql數據庫同步跳過臨時錯誤  
stop slave;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;  (事務類型,可能需要執行幾次)
start slave;

stop  slave IO_THREAD //此線程把master段的日志寫到本地
start slave IO_THREAD
stop slave SQL_THREAD //此線程把寫到本地的日志應用于數據庫
start slave  SQL_THREAD