MySQL主備複製搭建(使用mysqld_multi)

MySQL主備複製搭建(使用mysqld_multi)

這裡我使用單台伺服器上的兩個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 replication@'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
 

--------------------------------------------------------------------------------

change master to master_host = "localhost", 

master_user = 'replication', 

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

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 'replication@localhost: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 'replication@localhost: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;

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

--------------------------------------分割線 --------------------------------------

MySQL5.7.3.0安裝配置圖解教程

Ubuntu 14.04下安裝MySQL

《MySQL權威指南(原書第2版)》清晰中文掃描版 PDF

Ubuntu 14.04 LTS 安裝 LNMP Nginx\PHP5 (PHP-FPM)\MySQL

Ubuntu 14.04下搭建MySQL主從伺服器

Ubuntu 12.04 LTS 構建高可用分布式 MySQL 叢集

Ubuntu 12.04下原始碼安裝MySQL5.6以及Python-MySQLdb

MySQL-5.5.38通用二進位安裝

--------------------------------------分割線 --------------------------------------

本文永久更新連結地址：