MySQL 主從複製，mysql主從複製

時間  2019-11-21 標籤 mysql 主從 複製 mysql 主從 複製

1 複製概述

      Mysql內建的複製功能是構建大型，高性能應用程序的基礎。將Mysql的數據分佈到多個系統上去，這種分佈的機制，是經過將Mysql的某一臺主機的數據複製到其它主機（slaves）上，並從新執行一遍來實現的。複製過程當中一個服務器充當主服務器，而一個或多個其它服務器充當從服務器。主服務器將更新寫入二進制日誌文件，並維護文件的一個索引以跟蹤日誌循環。這些日誌能夠記錄發送到從服務器的更新。當一個從服務器鏈接主服務器時，它通知主服務器從服務器在日誌中讀取的最後一次成功更新的位置。從服務器接收從那時起發生的任何更新，而後封鎖並等待主服務器通知新的更新。mysql

請注意當你進行復制時，全部對複製中的表的更新必須在主服務器上進行。不然，你必需要當心，以免用戶對主服務器上的表進行的更新與對從服務器上的表所進行的更新之間的衝突。sql

1.1 mysql支持的複製類型：

　　（１）：基於語句的複製：  在主服務器上執行的SQL語句，在從服務器上執行一樣的語句。MySQL默認採用基於語句的複製，效率比較高。  
            一旦發現無法精確複製時，   會自動選着基於行的複製。    
　　（２）：基於行的複製：把改變的內容複製過去，而不是把命令在從服務器上執行一遍. 從mysql5.0開始支持
　　（３）：混合類型的複製: 默認採用基於語句的複製，一旦發現基於語句的沒法精確的複製時，就會採用基於行的複製。數據庫

1.首先在主庫上執行操做vim

1.1 設置server-id值並開啓binlog參數安全

 根據前文MySQL 的主從同步原理，咱們知道要實現主從複製，關鍵因素就是開啓binlog日誌功能，因此，咱們首先打開主庫的binlog日誌參數。服務器

1.2實現主從複製的必要條件：ide

(1)在配置文件my.cnf中配置server-id不能同樣。工具

(2)在主庫的配置文件my.cnf中開啓log-bin。性能

查詢是否開啓代碼以下：測試

 

  1. [root@mysql ~]# egrep "log-bin|server-id"/data/{3306,3307}/my.cnf
  2. /data/3306/my.cnf:log-bin =/data/3306/mysql-bin ##顯示已經開啓
  3. /data/3306/my.cnf:server-id =1
  4. /data/3307/my.cnf:#log-bin =/data/3307/mysql-bin
  5. /data/3307/my.cnf:server-id =3
  6. [root@mysql ~]#

若是未開啓修改主庫的配置文件 執行vim /data/3306/my.cnf,編輯多實例3306的my.cnf配置文件，兩個參數按以下內容修改： 提示： 1.上面兩參數要放在my.cnf中的[mysqld]模塊下，不然會出錯。  1.3.修改完配置文件後，重啓數據庫

  1. [root@mysql ~]#/data/3306/mysql stop
  2. StopingMySQL...
  3. [root@mysql ~]#/data/3306/mysql start

1.4.咱們也能夠在登陸數據庫中，查看修改的兩處配置是否生效； 使用show variables;查看mysql究竟配置了什麼參數；

  1. mysql> show variables like "log_bin";
  2. +---------------+-------+
  3. |Variable_name|Value|
  4. +---------------+-------+
  5. | log_bin | ON |
  6. +---------------+-------+
  7. 1 row in set (0.00 sec)
  8.  
  9. mysql> show variables like "server_id";
  10. +---------------+-------+
  11. |Variable_name|Value|
  12. +---------------+-------+
  13. | server_id |1|
  14. +---------------+-------+
  15. 1 row in set (0.00 sec)

2.1 創建用於主從複製的帳號  根據主從複製的原理，從庫要想和主庫同步，必須有一個能夠鏈接主庫的帳號，而且這個帳號的權限是主庫上建立的，權限是容許主庫的從庫鏈接並同步數據。  

 

2.一、登陸mysql3306 實例主數據庫

  1. [root@mysql ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock

2.二、創建用於從庫複製的帳號rep:

  1. mysql> grant replication slave on *.* to 'rep'@'192.168.1.%' identified by '123456';
  2. Query OK,0 rows affected (0.00 sec)

2.三、查看受權

  1. mysql> drop user 'root'@'mysql';##爲安全考慮，將無用的受權刪除
  2. Query OK,0 rows affected (0.02 sec)
  3.  
  4. mysql> select user,host from mysql.user;
  5. +------+-------------+
  6. | user | host |
  7. +------+-------------+
  8. | root |127.0.0.1|
  9. | rep |192.168.1.%|
  10. | root | localhost |
  11. +------+-------------+
  12. 3 rows in set (0.00 sec)
  13. mysql> flush privileges; ##刷新權限 Query OK, 0 rows affected (0.00 sec)

2.4 查看咱們作過受權的用戶，所具有的權限2.5 給數據庫一個讀鎖，是的用戶沒法建立庫，只能讀：

  1. mysql> flush table with read lock; ##將數據庫表鎖起來，鎖表窗口不能退出，不然鎖表失效
  2. Query OK,0 rows affected (0.00 sec)
  3.  
  4. mysql> create database zhu;
  5. ERROR 1223(HY000):Can't execute the query because you have a conflicting read lock

 

2.6記錄鎖表的位置點
  1. mysql> show master status;##查看鎖表的位置點
  2. +------------------+----------+--------------+------------------+
  3. |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
  4. +------------------+----------+--------------+------------------+
  5. | mysql-bin.000004|894|||
  6. +------------------+----------+--------------+------------------+
  7. 1 row in set (0.00 sec)
  8.  
  9. mysql>

  2.7 鎖表打包數據庫

  1. [root@mysql ~]# mysqldump -uroot -p123456 --events -S /data/3306/mysql.sock -A -B|gzip>/opt/bak_$(date +%F).sql.gz
  2. [root@mysql ~]# ll /opt/
  3. 總用量148
  4. -rw-r--r--.1 root root 1443244月900:13 bak_2016-04-09.sql.gz

2.8 鎖表打包完畢後，檢查位置點有無變化，若是有變化，表示在打包的過程當中有數據寫入，鎖表無效

  1. mysql> show master status;##鎖表後位置點事894
  2. +------------------+----------+--------------+------------------+
  3. |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
  4. +------------------+----------+--------------+------------------+
  5. | mysql-bin.000004|894|||
  6. +------------------+----------+--------------+------------------+
  7. 1 row in set (0.00 sec)
  8.  
  9. mysql> show master status;##鎖表打包後，位置點沒有變化，表示打包數據完成
  10. +------------------+----------+--------------+------------------+
  11. |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
  12. +------------------+----------+--------------+------------------+
  13. | mysql-bin.000004|894|||
  14. +------------------+----------+--------------+------------------+
  15. 1 row in set (0.00 sec)

2.9 接着將鎖表打開

  1. mysql> unlock tables;
  2. Query OK,0 rows affected (0.00 sec)
  1. mysql> create database zhu;##解鎖之後能夠寫數據
  2. Query OK,1 row affected (0.03 sec)
  3.  
  4. mysql>

  2.9.1 查看生成的binlog文件  2.9.2 使用mysqlbinlog命令查看生成的binlog日誌，mysqlbinlog 專門是將二進制語句翻譯成sql語句的工具：

  1. [root@mysql 3306]# mysqlbinlog mysql-bin.000004

3.接着作恢復從庫的工做

  1. [root@mysql opt]# cd /opt/
  2. [root@mysql opt]# ll
  3. 總用量664
  4. -rw-r--r--.1 root root 5282954月900:50 all-tmp.sql
  5. -rw-r--r--.1 root root 1443244月900:13 bak_2016-04-09.sql.gz
  6. drwxr-xr-x.2 root root 40963月262015 rh
  7. [root@mysql opt]# gzip -d bak_2016-04-09.sql.gz
  8. [root@mysql opt]# mysql -uroot -p123456 -S /data/3307/mysql.sock <bak_2016-04-09.sql
  9. [root@mysql opt]#

  3.1.1 接着在從庫上執行change master to命令，實質實在從庫上生成master.info文件的過程

  1. [root@mysql opt]# mysql -uroot -p123456 -S /data/3307/mysql.sock<<EOF
  2. stop slave;
  3. CHANGE MASTER TO
  4. MASTER_HOST='192.168.1.31',
  5. MASTER_PORT=3306,
  6. MASTER_USER='rep',
  7. MASTER_PASSWORD='123456',
  8. MASTER_LOG_FILE='mysql-bin.000004',
  9. MASTER_LOG_POS=894;
  10. EOF

   3.1.2 最後一步開啓從庫開關：

  1. mysql> start slave; ##打開開關
  2. Query OK,0 rows affected (0.00 sec)
  3.  
  4. mysql>

接着檢查從庫狀態信息：

  1. mysql> show slave status \G;
  2. ***************************1. row ***************************
  3. Slave_IO_State:Waitingfor master to send event
  4. Master_Host:192.168.1.31
  5. Master_User: rep
  6. Master_Port:3306
  7. Connect_Retry:60
  8. Master_Log_File: mysql-bin.000004
  9. Read_Master_Log_Pos:975
  10. Relay_Log_File: relay-bin.000002
  11. Relay_Log_Pos:334
  12. Relay_Master_Log_File: mysql-bin.000004
  13. Slave_IO_Running:Yes
  14. Slave_SQL_Running:Yes
  15. Replicate_Do_DB:
  16. Replicate_Ignore_DB: mysql
  17. Replicate_Do_Table:
  18. Replicate_Ignore_Table:
  19. Replicate_Wild_Do_Table:
  20. Replicate_Wild_Ignore_Table:
  21. Last_Errno:0
  22. Last_Error:
  23. Skip_Counter:0
  24. Exec_Master_Log_Pos:975
  25. Relay_Log_Space:484
  26. Until_Condition:None
  27. Until_Log_File:
  28. Until_Log_Pos:0
  29. Master_SSL_Allowed:No
  30. Master_SSL_CA_File:
  31. Master_SSL_CA_Path:
  32. Master_SSL_Cert:
  33. Master_SSL_Cipher:
  34. Master_SSL_Key:
  35. Seconds_Behind_Master:0
  36. Master_SSL_Verify_Server_Cert:No
  37. Last_IO_Errno:0
  38. Last_IO_Error:
  39. Last_SQL_Errno:0
  40. Last_SQL_Error:
  41. Replicate_Ignore_Server_Ids:
  42. Master_Server_Id:1
  43. 1 row in set (0.00 sec)

3.1.3 最後測試主從   主庫建立庫：  從庫：    最佳方案： 4.1 無須鎖表的mysqldump備份命令       myisam 引擎企業生產備份命令： mysqldump -uroot -p123456 -A -B --mastet-data=2 -x --events|gzip >/opt /all.sql.gz       innodb 引擎企業生產備份命令：推薦使用的 mysqldump -uroot -p123456    -A     -B     -F     --mastet-data=2    --events   --single-transaction|gzip   >/opt/all.sql.gz  

  1. [root@mysql opt]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B --master-data=2-x --events >/opt/all-tmp.sql ##演示以下命令
  2. [root@mysql opt]# vim all-tmp.sql
  3. 其中--master-data=2,表示在前面加--
     
  4. -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=975;##記錄了增量備份的起點位置，主要是參數--master-data=2起的做用

  注： --master-data做用： 一、使用--master-data=2 進行備份文件會增長以下內容：適合普通備份增量恢復  

联系我们 沪ICP备2021010478号-7