MySQL主從架構

標籤：mysql

   MySQL主從又叫做Replication、AB複製。簡單講就是A和B兩台機器做主從後，在A上寫資料，另外一台B也會跟著寫資料，兩者資料即時同步的。它基於binlog，主裝置上須開啟binlog才能進行主從。

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

     1）主裝置將更改操作記錄到binlog裡；

     2）從將主裝置的binlog事件(sql語句)同步到從本機上並記錄在relaylog裡；

     3）從根據relaylog裡面的sql語句按順序執行。

   主裝置上有一個log dump線程，用來和從的I/O線程傳遞binlog。從裝置上有兩個線程，其中I/O線程用來同步主的binlog並產生relaylog，另外一個SQL線程用來把relaylog裡面的sql語句落地。

650) this.width=650;" src="https://s3.51cto.com/wyfs02/M02/05/89/wKiom1mmxVLDrgOOAAHlMAz2Xnk817.png" title="1.png" alt="wKiom1mmxVLDrgOOAAHlMAz2Xnk817.png" />

準備工作

1、準備兩台裝有mysql的伺服器，並啟動mysql服務。

2、分配角色，確定裝置主從。

配置主裝置

1、編輯設定檔

[[email protected] ~]# vim /etc/my.cnf[mysqld]server-id=88log_bin=bin01

2、重啟mysql

[[email protected] ~]# /etc/init.d/mysqld restartShutting down MySQL.... SUCCESS! Starting MySQL. SUCCESS!

3、檢查log_bin

[[email protected] ~]# ls /data/mysql/bin01.*/data/mysql/bin01.000001  /data/mysql/bin01.index

4、建立資料庫

[[email protected] mysql]# cd /data/mysql[[email protected] mysql]# /usr/local/mysql/bin/mysql -uroot -e ‘create database db01‘

5、增加測試資料

[[email protected] mysql]# /usr/local/mysql/bin/mysqldump -uroot zrlog >/tmp/zrlog.sql[[email protected] mysql]# /usr/local/mysql/bin/mysql -uroot db01 </tmp/zrlog.sql

6、備份所有資料庫

[[email protected] mysql]# /usr/local/mysql/bin/mysqldump -uroot db01 > /tmp/db01.sql[[email protected] mysql]# /usr/local/mysql/bin/mysqldump -uroot zrlog > /tmp/zrlog.sql

7、建立使用者

[[email protected] mysql]# /usr/local/mysql/bin/mysql -urootWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 414Server version: 5.6.35-log MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> grant replication slave on *.* to ‘repl‘@‘122.112.197.192‘ identified by ‘123456‘;Query OK, 0 rows affected (0.01 sec)

8、鎖表並查看狀態

mysql> flush tables with read lock;  ##鎖表，防止寫入Query OK, 0 rows affected (0.01 sec)mysql> show master status;   ##下方兩個參數需要在從裝置上配置+--------------+----------+--------------+------------------+-------------------+| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+--------------+----------+--------------+------------------+-------------------+| bin01.000001 |    10472 |              |                  |                   |+--------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

配置從裝置

1、編輯設定檔

[[email protected] mysql]# vi /etc/my.cnf[mysqld]server-id=192

2、重啟mysql

[[email protected] mysql]# /etc/init.d/mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!

3、複製主裝置Database Backup檔案

[[email protected] mysql]# scp 122.112.253.88:/tmp/*.sql /tmp/The authenticity of host ‘122.112.253.88 (122.112.253.88)‘ can‘t be established.ECDSA key fingerprint is 2e:6e:90:32:87:05:9e:63:63:d6:2d:44:a5:5f:be:51.Are you sure you want to continue connecting (yes/no)? yesWarning: Permanently added ‘122.112.253.88‘ (ECDSA) to the list of known hosts.[email protected]‘s password: db01.sql                                    100% 9877     9.7KB/s   00:00          zrlog.sql                                   100% 9878     9.7KB/s   00:00

4、建立對應資料庫

[[email protected] mysql]# /usr/local/mysql/bin/mysql -urootWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.35 MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> create database db01;Query OK, 1 row affected (0.00 sec)mysql> create database zrlog;Query OK, 1 row affected (0.00 sec)[[email protected] mysql]# /usr/local/mysql/bin/mysql -uroot db01 </tmp/db01.sql[[email protected] mysql]# /usr/local/mysql/bin/mysql -uroot zrlog </tmp/zrlog.sql

5、配置主備同步

[[email protected] mysql]# /usr/local/mysql/bin/mysql -urootWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.6.35 MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> stop slave;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> change master to master_host=‘122.112.253.88‘,master_user=‘repl‘,master_password=‘123456‘,master_log_file=‘bin01.000001‘,master_log_pos=10472;Query OK, 0 rows affected, 2 warnings (0.03 sec)mysql> start slave;Query OK, 0 rows affected (0.01 sec)

6、查看主從狀態

mysql> show slave status\G;  ##如果出現錯誤，可能是雲主機策略沒放過3306連接埠*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 122.112.253.88                  Master_User: repl                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: bin01.000001          Read_Master_Log_Pos: 10708               Relay_Log_File: ecs-89c1-relay-bin.000003                Relay_Log_Pos: 515        Relay_Master_Log_File: bin01.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: 10708              Relay_Log_Space: 691              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: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 88                  Master_UUID: 79b66469-8cc8-11e7-ae36-fa163eb51d35             Master_Info_File: /data/mysql/master.info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it           Master_Retry_Count: 86400                  Master_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set:                 Auto_Position: 01 row in set (0.00 sec)ERROR: No query specified

7、主裝置解鎖

[[email protected] mysql]# /usr/local/mysql/bin/mysql -urootWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 875Server version: 5.6.35-log MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> unlock tables;Query OK, 0 rows affected (0.00 sec)

測試主從同步

1、在主裝置上刪除db01資料庫的表；

mysql> show tables;+----------------+| Tables_in_db01 |+----------------+| comment        || link           || log            || lognav         || plugin         || tag            || type           || user           |+----------------+8 rows in set (0.00 sec)mysql> drop table tag;Query OK, 0 rows affected (0.01 sec)

2、在從裝置查看對應的表也已經不存在了。

mysql> show tables;+----------------+| Tables_in_db01 |+----------------+| comment        || link           || log            || lognav         || plugin         || type           || user           |+----------------+7 rows in set (0.00 sec)

擴充學習

▎配置參數

1. 主伺服器上：

binlog-do-db=      //僅同步指定的庫(其他庫不同步)

binlog-ignore-db= //忽略指定庫(其他庫都同步)

2. 從伺服器上：

replicate_do_db=   //(不常用)

replicate_ignore_db=   //(不常用)

replicate_do_table=   //(不常用)

replicate_ignore_table=   //(不常用)

replicate_wild_do_table=   //如aming.%, (支援萬用字元%)

replicate_wild_ignore_table=

本文出自 “Gorilla City” 部落格，請務必保留此出處http://juispan.blog.51cto.com/943137/1961233

MySQL主從架構