linux營運、架構之路-MySQL主從複製

標籤：default   相同   tar   ica   blog   登入   date   kconfig   image   

一、MySQL主從複製原理圖

 MySQL主從複製原理：實現主從複製原理是三個線程完成的，主的I/O線程，備的I/O線程與SQL線程

1、首先主庫db01需要開啟binlog、授權一個replication client帳號，鎖表進行一次資料全備

2、從庫db02、db03匯入主庫全備資料從而實現主從資料一致性，從庫執行change master to將主機、連接埠、使用者名稱、密碼、binlog檔案名稱、binlog位置點存入master.info中

3、從庫start slave，首先由從庫的I/O線程攜帶master.info的資訊去詢問主庫，驗證rep帳號，根據binlog檔案和位置點，將新增的binlog檔案發回去，同時攜帶著發送到的檔案和位置點，從庫收到存到master.info中，收到的binlog日誌存放在reply-bin檔案中，由從庫的SQL執行，先解析binlog檔案再先入資料庫，執行到的資訊存入repay.info中。從庫會一直詢問主庫是否有新的資料，主庫有新的資料會返回給從庫。

二、MySQL主從複製部署

1、部署環境

[[email protected] tools]# cat /etc/redhat-release CentOS release 6.9 (Final)[[email protected] tools]# uname -r2.6.32-696.el6.x86_64[[email protected] tools]# ll /application/total 4lrwxrwxrwx  1 root  root    26 2017-10-24 11:33 mysql -> /application/mysql-5.6.34/drwxr-xr-x 13 mysql mysql 4096 2017-10-24 11:33 mysql-5.6.34

2、伺服器規劃

伺服器名稱	外網IP	內網IP	主機名稱
MySQL資料庫db01	172.19.5.51/24	172.16.1.51/24	db01
MySQL資料庫db02	172.19.5.52/24	172.16.1.52/24	db02
MySQL資料庫db03	172.19.5.53/24	172.16.1.53/24	db03

3、安裝部署

useradd -s /sbin/nologin -M mysqlcd /server/toolstar xf mysql-5.6.34-linux-glibc2.5-x86_64.tar.gzmkdir -p /application/mv /server/tools/mysql-5.6.34-*-x86_64 /application/mysql-5.6.34ln -s /application/mysql-5.6.34/ /application/mysqlchown -R mysql.mysql /application/mysql//application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/application/mysql/data --user=mysql\cp /application/mysql/support-files/mysql.server /etc/init.d/mysqldchmod +x /etc/init.d/mysqldsed -i ‘s#/usr/local/mysql#/application/mysql#g‘ /application/mysql/bin/mysqld_safe /etc/init.d/mysqld\cp /application/mysql/support-files/my-default.cnf /etc/my.cnf/etc/init.d/mysqld startlsof -i:3306echo ‘export PATH=/application/mysql/bin:$PATH‘ >>/etc/profilesource /etc/profilechkconfig --add mysqld chkconfig mysqld on/application/mysql/bin/mysqladmin -u root password ‘123456‘

4、修改db01、db02、db03資料庫設定檔/etc/my.conf

[[email protected] ~]# egrep "log_bin|server_id" /etc/my.cnflog_bin = mysql-bin  #主庫開啟log_bin server_id = 1        #server_id一定不能相同[[email protected] ~]# grep server_id /etc/my.cnf server_id = 2[[email protected] ~]# grep server_id /etc/my.cnf server_id = 3

5、主庫db01操作

mysql -uroot -p123456#登入資料庫grant replication slave on *.* to ‘rep‘@‘172.16.1.%‘ identified by ‘123456‘;#授權從庫使用者flush table with read lock;#鎖表show master status; #查看主庫binlog和位置點+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 |      326 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)
mysqldump -uroot -p123456 -A -B --master-data=2|gzip >/tmp/mysql_$(date +%F).sql.gz #全備主庫資料
[[email protected] ~]# scp -rp /tmp/mysql_2017-10-24.sql.gz 172.16.1.52:/tmp/ #分發主庫全備資料到從庫
[[email protected] ~]# scp -rp /tmp/mysql_2017-10-24.sql.gz 172.16.1.53:/tmp/
mysql> unlock table;#解鎖表

linux營運、架構之路-MySQL主從複製