mysql主備複製搭建(使用mysqld_muti)

標籤：mysql

   這裡我使用單台伺服器上的兩個mysql執行個體進行搭建，主要用到了mysql內建的mysqld_multi

 

一、複製原理

開始搭建前有個mysql複製原理的基礎知識需要補充:

mysql進行主備複製使用到了三個線程：

1.主庫上的轉儲存線程:

    會將mysql server提交的事務寫入到二進位檔案中，這個二進位檔案就叫做binlog。

2.備庫上的連接線程：

    備庫啟動後，負責和主庫通訊，讀取binlog，同時，將binlog儲存進自己的一個叫中繼日誌的relaylog中。

3.備庫上的relaylog重放線程:

   此線程會將relaylog中的事件在備庫上進行回放，說白點就是重新執行一次

 

二、搭建步驟

1./etc新增檔案mysqld_multi.cnf

將/user/share/mysql/my-innodb-heavy-4G.cnf檔案複製到/etc，重新命名為xxx.cnf（任何你想要的名字）

在設定檔中新增三個執行個體

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = multi_admin
password = multipass
log = /var/log/mysqld_multi.log

[mysqld3307]
port = 3307
pid-file = /var/lib/mysql3307/mysql3307.pid
socket = /var/lib/mysql3307/mysql3307.sock
datadir=/var/lib/mysql3307
user=mysql
set-variable=max_connections=27000
log_bin = mysql-bin
server_id = 3307

[mysqld3308]
port = 3308
pid-file = /var/lib/mysql3308/mysql3308.pid
socket = /var/lib/mysql3308/mysql3308.sock
datadir=/var/lib/mysql3308
user=mysql
set-variable=max_connections=28000
log_bin = mysql-bin
server_id = 3308
relay_log = /var/lib/mysql3308/mysql-relay-bin
log_slave_updates = 1
read_only = 1

[mysqld3309]
port = 3309
pid-file = /var/lib/mysql3309/mysql3309.pid
socket = /var/lib/mysql3309/mysql3309.sock
datadir=/var/lib/mysql3309
user=mysql
set-variable=max_connections=29000
log_bin = mysql-bin
server_id = 3309
relay_log = /var/lib/mysql3309/mysql-relay-bin
log_slave_updates = 1
read_only = 1

 這裡我會將將mysqld3307這個執行個體做為主庫，mysqld3308和mysql3309這個執行個體作為備庫

 

 

2.做好了配置之後開啟兩個執行個體：

mysqld_multi --defaults-file=/etc/mysql/mysqld_muti.cnf start

 

3.開啟複製前主庫的準備工作:

   1.在主庫上增加一個複製帳號：

    使用sock檔案登陸mysql:

       mysql -uroot -p -S /var/lib/mysql3307/mysql3307.sock（這個通訊端檔案還記得嗎，是在之前定義執行個體的時候定義的）

mysql>grant replication slave,replication client on *.* to [email protected]‘localhost‘ identified by ‘replication‘; 

mysql>flush privileges;

  查看主庫上的binlog是否開啟:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     1001 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec) 

 

4.開啟複製:

登陸到備庫：

mysql -uroot -p -S /var/lib/mysql3308/mysql3308.sock

 

1. change master to master_host = "localhost",  

2. master_user = ‘replication‘,  

3. master_password = ‘replication‘,（你之前在主庫上建立複製帳號時指定的）  

4. master_port = 3306;  

 

start slave;

 

查看複製是否開始工作:

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: replication
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 622
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 767
        Relay_Master_Log_File: mysql-bin.000001
             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: 622
              Relay_Log_Space: 922
              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)

 當看到Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes證明io通訊線程和sql回放線程都已經啟動。至此，主備複製結構配置完成

5.進行正常主從測試：

在mysql3308資料庫停止複製

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G;           
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: localhost
                  Master_User: replication
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 408
               Relay_Log_File: mysql-relay-bin.000012
                Relay_Log_Pos: 553
        Relay_Master_Log_File: mysql-bin.000005

在mysql3309資料庫停止複製

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: localhost
                  Master_User: replication
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 316

查看mysql日誌情況：

150510  1:33:39 [Note] Error reading relay log event: slave SQL thread was killed
150510  1:33:39 [Note] Slave I/O thread killed while reading event
150510  1:33:39 [Note] Slave I/O thread exiting, read up to log ‘mysql-bin.000005‘, position 408


150510  1:35:41 [Note] Error reading relay log event: slave SQL thread was killed
150510  1:35:41 [Note] Slave I/O thread killed while reading event
150510  1:35:41 [Note] Slave I/O thread exiting, read up to log ‘mysql-bin.000006‘, position 316

     在這期間，主庫mysql3307進行了flush logs操作，重建了mysql-bin日誌，並對錶進行添加，刪除操作。然後啟動從庫的複製，進行查看。

150510  1:42:48 [Note] Slave SQL thread initialized, starting replication in log ‘mysql-bin.000005‘ at position 408, relay log ‘/var/lib/mysql3308/mysql-relay-bin.000012‘ position: 553
150510  1:42:48 [Note] Slave I/O thread: connected to master ‘[email protected]:3307‘,replication started in log ‘mysql-bin.000005‘ at position 408

150510  1:43:04 [Note] Slave SQL thread initialized, starting replication in log ‘mysql-bin.000006‘ at position 316, relay log ‘/var/lib/mysql3309/mysql-relay-bin.000015‘ position: 461
150510  1:43:04 [Note] Slave I/O thread: connected to master ‘[email protected]:3307‘,replication started in log ‘mysql-bin.000006‘ at position 316

    說明：從庫從停止的時間點重新補回了停止期間的所有資料。

6.進行異常測試：

      停掉從庫mysql3308和mysql3309的複製，並對主庫進行日誌清除操作：

flush logs

delete from sky where id=‘2000‘;

purge binary logs to ‘mysql-bin.000010‘;

     然後重新啟動從庫的複製，日誌進行報錯，資料丟失，主從失敗。

150510  1:50:53 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file‘, Error_code: 1236

解決方案：

     只能停止從資料庫,然後重新在從資料庫中CHANGE MASTER TO 開始,指向正確的二進位檔案及位移量

change master  to  master_log_file=‘mysql-bin.000001‘,master_log_pos=106;

     之後的資料會恢複正常主從同步。

本文出自 “秋天的童話” 部落格，請務必保留此出處http://wushank.blog.51cto.com/3489095/1649929

mysql主備複製搭建(使用mysqld_muti)