mysql主從同步配置方法和原理

標籤：mysql主從配置 mysql主從配置原理

mysql主從複製實驗環境:
    1.CentOS5.8 x86_64位 採用迷你安裝，系統經過了基本最佳化篇
    2.mysql版本： 5.1.73-log ,同台伺服器多執行個體,安裝方法參考:mysql多執行個體安裝方法

    3.源碼包存放位置：/home/oldboy/tools
    4.源碼包編譯安裝位置：/application/
一.修改master配置
    1.修改my.cnf配置
         vi /data/3306/my.cnf
         egrep "log-bin|server-id" /data/3306/my.cnf    #檢查設定成如下
            log-bin = /data/3306/mysql-bin
            server-id = 1
            #transaction_isolation = READ-COMMITTED        
            #要注釋掉.不然插入資料,會報如下錯誤
                mysql> insert into stu values (1, ‘txidc‘),(2,‘gaogao‘);
                ERROR 1598 (HY000): Binary logging not possible. Message: Transaction level ‘READ-COMMITTED‘ in InnoDB is not safe for binlog mode ‘STATEMENT‘
        /data/3306/mysql start
        
    2.mysql主庫增加用於同步處理的使用者:
        
        mysql> grant replication slave on *.* to [email protected]‘192.168.1.%‘ identified by ‘mygirl99‘
        mysql> select user,host from mysql.user;
        +------+-------------+
        | user | host        |
        +------+-------------+
        | root | 127.0.0.1   |
        | rep  | 192.168.1.% |
        | root | localhost   |
        | root | ser200      |
        +------+-------------+
        4 rows in set (0.00 sec)

        mysql> show grants for [email protected]‘192.168.1.%‘;
        +--------------------------------------------------------------------------------------------------------------------------+
        | Grants for [email protected]%                                                                                               |
        +--------------------------------------------------------------------------------------------------------------------------+
        | GRANT REPLICATION SLAVE ON *.* TO ‘rep‘@‘192.168.1.%‘ IDENTIFIED BY PASSWORD ‘*3561920064C790E42F7FBEBD0303ACC7C1EF855E‘ |
        +--------------------------------------------------------------------------------------------------------------------------+
        1 row in set (0.00 sec)
    3.主庫鎖表唯讀:此時業務會受影響.生產環境要考慮.要是停機選擇流量最小時間
        mysql> flush tables with read lock;
        Query OK, 0 rows affected (0.00 sec)

        mysql> show variables like ‘%timeout%‘
            -> ;
        +----------------------------+-------+
        | Variable_name              | Value |
        +----------------------------+-------+
        | connect_timeout            | 10    |
        | delayed_insert_timeout     | 300   |
        | innodb_lock_wait_timeout   | 120   |
        | innodb_rollback_on_timeout | OFF   |
        | interactive_timeout        | 28800 |        #互動操時時間是28800秒
        | net_read_timeout           | 30    |
        | net_write_timeout          | 60    |
        | slave_net_timeout          | 3600  |
        | table_lock_wait_timeout    | 50    |
        | wait_timeout               | 28800 |        #串連等待時間是28800秒,這2項時間要是過短.有可能會自動解鎖表
        +----------------------------+-------+
        10 rows in set (0.00 sec)
    4.查看主庫的位置狀態,從庫裡面是從此位置開始同步
        mysql> show master status;
        +------------------+----------+--------------+------------------+
        | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
        +------------------+----------+--------------+------------------+
        | mysql-bin.000008 |      832 |              |                  |
        +------------------+----------+--------------+------------------+
        1 row in set (0.00 sec)
    5.匯出主庫
        [[email protected] 3306]# mysqldump -uroot -p  -A -B -S /data/3306/mysql.sock |gzip >/server/bak/mysql_bak.$(date +%Y%m%d).sql.gz
        Enter password:
        -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
        [[email protected] 3306]# ll /server/bak/mysql_bak.20141029.sql.gz
        -rw-r--r-- 1 root root 144960 Oct 29 15:48 /server/bak/mysql_bak.20141029.sql.gz
        
        注意事項:
            -A:備份所有庫    -B,備份時增加use db 或者drop
            如果資料庫超過了100G+,備份時允許停機的話.可以直接打包資料檔案遷移

    6.在確認下主庫的位置狀態,確保匯出過程,沒有資料插入或者sql更新等操作
        [[email protected] 3306]# mysql -uroot -pmybaby3306 -S /data/3306/mysql.sock -e ‘show master status‘;
        +------------------+----------+--------------+------------------+
        | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
        +------------------+----------+--------------+------------------+
        | mysql-bin.000008 |      832 |              |                  |
        +------------------+----------+--------------+------------------+
    7.解鎖主庫,恢複業務    
        mysql> unlock tables;
        Query OK, 0 rows affected (0.00 sec)
二.從庫slave操作
    1.修改從庫配置:
        [[email protected] 3306]# cd /data/3307/
        [[email protected] 3307]# vi my.cnf
        [[email protected] 3307]# egrep "server-id|log-bin" my.cnf
        #log-bin = /data/3307/mysql-bin
        server-id = 2
    2.匯入資料庫
        [[email protected] 3307]# /data/3307/mysql start
        [[email protected] 3307]# gunzip /server/bak/mysql_bak.20141029.sql.gz
        [[email protected] 3307]# ll /server/bak/mysql_bak.20141029.sql
        -rw-r--r-- 1 root root 527860 Oct 29 15:48 /server/bak/mysql_bak.20141029.sql
        [[email protected] 3307]# mysql -uroot -pmybaby3307 -S /data/3307/mysql.sock < /server/bak/mysql_bak.20141029.sql

        檢查匯入狀態:

        mysql> show databases;
        +--------------------+
        | Database           |
        +--------------------+
        | information_schema |
        | mysql              |
        | test               |
        | test3306           |
        +--------------------+
        4 rows in set (0.00 sec)
    3.登陸從庫.配置從庫同步參數
        change master to \
        master_host="192.168.1.200", \
        master_user="rpe", \
        master_port=3306, \
        master_password="mygirl99", \
        master_log_file="mysql-bin.000008", \
        master_log_pos=832;
    4.啟動從庫同步開關
        mysql> start slave;         
        Query OK, 0 rows affected (0.00 sec)


        報錯
        mysql> show slave status \G;
        *************************** 1. row ***************************
                   Slave_IO_State: Connecting to master
                  Master_Host: 192.168.1.200
                  Master_User: rpe
                  Master_Port: 3306
                Connect_Retry: 60
                  Master_Log_File: mysql-bin.000008
              Read_Master_Log_Pos: 832
                   Relay_Log_File: relay-bin.000001
                Relay_Log_Pos: 4
            Relay_Master_Log_File: mysql-bin.000008
                 Slave_IO_Running: No
                Slave_SQL_Running: Yes
        原因:
            主庫的使用者名稱設定錯誤,造成串連不上.設定對應的就OK了
        mysql> show slave status \G;
        *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.200
                  Master_User: rpe
                  Master_Port: 3306
                Connect_Retry: 60
                  Master_Log_File: mysql-bin.000008
              Read_Master_Log_Pos: 1329
                   Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 748
            Relay_Master_Log_File: mysql-bin.000008
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB:
              Replicate_Ignore_DB: mysql
               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: 1329
                  Relay_Log_Space: 897
                  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)


三.不停機實現主從庫
    1,原理:備份資料庫記下bin-log的檔案名稱和位置,下次匯入時,從該位置開始同步就行了.

更多的mysql安裝教程請點擊

本文出自 “蜘蛛俠” 部落格，請務必保留此出處http://txidc.blog.51cto.com/9138217/1569821

mysql主從同步配置方法和原理