mysql主從配置

標籤：sla   原理   ide   參數   產生   lin   perm   start   操作   

mysql 主從

MySQL主從又叫做Replication、AB複製。簡單講就是A和B兩台機器做主從後，在A上寫資料，另外一台B也會跟著寫資料，兩者資料即時同步。

• MySQL主從是基於binlog的，主上須開啟binlog才能進行主從

  • binlog，其實就是一個檔案，檔案裡記錄了一些日誌，檔案是 二進位檔案，無法cat

• 主從過程大致有3個步驟：

  • 主將更改操作記錄到 binlog 裡
  • 從將主的binlog事件（sql語句）同步到本機上並記錄在 relaylog（中繼日誌） 裡
  • 從根據 relaylog 裡面的sql語句按順序執行

• mysql主從共有三個線程

  • mysql 主從共有三個線程。主上有一個log dump 線程，用來和從的I/O 線程傳遞binlog；
  • 從上有兩個線程，其中I/O 線程用來同步主的binlog並產生relaylog，另外一個sql線程用來把relaylog 裡面的sql語句落地
• mysql 主從原理圖

• mysql 主從應用情境

1、資料備份，主機器宕機，從機器還能隨時供服務

2、作為一個從庫，讀的庫，減輕主庫的壓力，資料備份且可以分擔主機器被調用資料時的壓力，mysql主從，是有方向性的，寫資料，必須從主機器開始；如果不依照原理會導致資料紊亂。

準備工作

兩台機器都安裝並啟動mysql。

配置主

編輯/etc/my.cnf設定檔[[email protected] ~]# vim /etc/my.cnf……server-id=131// 自訂log_bin=testlinux// 指定log首碼重啟mysql服務[[email protected] ~]# /etc/init.d/mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS![[email protected] ~]# ls -l /data/mysql-rw-rw----. 1 mysql mysql      120 1月  23 21:00 testlinux.000001-rw-rw----. 1 mysql mysql       19 1月  23 21:00 testlinux.index// 此時，/data/mysql目錄下會產生兩個新的檔案// 之後還會產生更多以testlinux開頭的檔案// 這些檔案都非常重要，是實現主從的根本，沒有這些檔案主從也無法完成建立一個資料庫為實驗做準備[[email protected] ~]# mysql -uroot -p......MySQL > create database testlinux;Query OK, 1 row affected (0.00 sec)// 二進位檔案testlinux.000001 大小增加，它裡面記錄了資料庫的建立過程。建立一個用於同步資料的使用者[[email protected] ~]# mysql -uroot -p......mysql> grant replication slave on *.* to ‘repl‘@‘192.168.159.132‘ identified by ‘112233‘;Query OK, 0 rows affected (0.01 sec)// IP為“從”的IPmysql> flush tables with read lock;Query OK, 0 rows affected (0.12 sec)// 鎖定資料表（目的是暫時使其不能繼續寫，保持現有狀態用於同步）mysql> show master status;+-------------------+----------+--------------+------------------+-------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-------------------+----------+--------------+------------------+-------------------+| testlinux.000001  |   441    |              |                  |                   |+-------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)#記住file和position(設定主從同步時會使用)

• 備份主庫中的所有資料庫

  [[email protected] ~]# mysqldump -uroot -p112233 test > /tmp/test.sql

  [[email protected] ~]# mysqldump -uroot -p112233 zrlog > /tmp/zrlog.sql

  [[email protected] ~]# mysqldump -uroot -p112233 testlinux > /tmp/testlinux.sql

配置從

[[email protected] ~]# vim /etc/my.cnf……server-id=130……// 增加一個sever-id 和主不一樣就行重啟mysql[[email protected] ~]# /etc/init.d/mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!將主中備份的資料庫發送到從中[[email protected] ~]# scp 192.168.159.131:/tmp/*.sql /tmp/The authenticity of host ‘192.168.159.131 (192.168.159.131)‘ can‘t be established.ECDSA key fingerprint is b2:66:7f:db:00:38:59:11:9e:75:75:02:fd:7a:95:d7.Are you sure you want to continue connecting (yes/no)? yesWarning: Permanently added ‘192.168.159.131‘ (ECDSA) to the list of known hosts.[email protected]‘s password: testlinux.sql                                                                        100%    0     0.0KB/s   00:00    test.sql                                                                             100%    0     0.0KB/s   00:00    zrlog.sql                                                                            100%    0     0.0KB/s   00:00    建立庫[[email protected] ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor. ......mysql> create database testlinux;Query OK, 1 row affected (0.00 sec)mysql> create database test;Query OK, 1 row affected (0.00 sec)mysql> create database zrlog;Query OK, 1 row affected (0.00 sec)恢複資料庫[[email protected] ~]# mysql -uroot -p159820 test < /tmp/test.sql[[email protected] ~]# mysql -uroot -p159820 zrlog < /tmp/zrlog.sql[[email protected] ~]# mysql -uroot -p159820 testlinux < /tmp/testlinux.sql// 該過程要保證主從資料庫內容一致實現主從同步[[email protected] ~]# mysql -uroot -p.....mysql> stop slave;Query OK, 0 rows affected, 1 warning (0.06 sec)mysql> change master to master_host=‘192.168.159.131‘,master_user=‘repl‘,master_password=‘112233‘,master_log_file=‘testlinux.000001‘,master_log_pos=441;Query OK, 0 rows affected, 2 warnings (0.02 sec)// IP為主的IP；file、pos分別為主的filename和position。mysql> start slave;Query OK, 0 rows affected (0.00 sec)判斷主從是否配置成功mysql> show slave status\G...... Slave_IO_Running: Yes Slave_SQL_Running: Yes Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it......解鎖主庫的表（在主上操作）[[email protected] ~]# mysql -uroot -p......mysql> unlock tables;Query OK, 0 rows affected (0.00 sec)......

測試主從

• 主上幾個配置參數（在/etc/my.cnf中配置）

  • binlog-do-db= 僅同步指定的庫
  • binlog-ignore-db= 忽略指定的庫

• 從上幾個配置參數（在/etc/my.cnf中配置）

  • replicate_do_db= 同步指定的庫
  • replicate_ignore_db= 忽略指定的庫
  • replicate_do_table= 同步指定的表

  • replicate_ignore_table= 忽略指定的表

  • replicate_wild_do_table= 如aming.%，支援萬用字元
  • replicate_wild_ignore_table= 所有的忽略

注意： 進行從伺服器的配置時盡量使用參數“replicatewild”，使匹配更精確，提升使用效能。

• 測試

  主伺服器：
  mysql> show tables;
  +---------------------------+
  | Tables_in_adaitest |
  +---------------------------+
  | columns_priv |
  | db |
  | event |
  +---------------------------+

  刪除表：
  mysql> drop table db;
  mysql> show tables;
  +---------------------------+
  | Tables_in_adaitest |
  +---------------------------+
  | columns_priv |
  | event |
  +---------------------------+

  從伺服器：
  主伺服器刪除表之前：
  mysql> show tables;
  +---------------------------+
  | Tables_in_adaitest |
  +---------------------------+
  | columns_priv |
  | db |
  | event |
  +---------------------------+

  主伺服器刪除表之後：
  mysql> show tables;
  +---------------------------+
  | Tables_in_adaitest |
  +---------------------------+
  | columns_priv |
  | event |
  +---------------------------+

mysql主從配置