MySQL主從複製部署

標籤：主機   其他   mysql主從   mysqldump   參數取值   使用   日誌功能   注意   nlog   

前言

　　MySQL的主從複製是基於二進位日誌機制的，需開啟二進位日誌功能。在具體的配置過程中，需注意主伺服器與從伺服器均配置唯一ID編號，且從伺服器必須設定主伺服器的主機名稱、記錄檔名、檔案位置等參數。整個過程可分為如下幾個步驟：

（1）     主伺服器：開啟二進位日誌功能，設定伺服器ID，並重啟MySQL服務；

（2）     在所有從伺服器上設定伺服器ID（唯一）；

（3）     在主伺服器上為不同的從伺服器建立可讀取主伺服器記錄檔的賬戶，或使用統一賬戶；

一、系統規劃1、主伺服器

作業系統版本: RHEL 6.6 x64

資料庫版本: MySQL 5.6

主機IP：192.168.152.129

主機名稱：master

資料庫名稱：

ID編號：1

2、從伺服器

作業系統版本：RHEL 6.6 x64

資料庫版本：MySQL 5.6（必須保證MySQL版本一致）

主機IP：192.168.152.128

主機名稱：slave

資料庫名稱：

ID編號：2

註：本文為個人學習之用，使用虛擬機器類比，可作為實施參考。

二、安裝準備1、儲存

需要根據實際情況，為源數和目標端資料庫分配足夠的可用空間

2、網路

需要開通源端和目標端伺服器的any-any的網路連接埠互連性

3、NTP

需要同步源和目標主機時鐘同步機制，若為內網環境，推薦配置內網時鐘伺服器

4、其他

配置靜態IP、主機名稱、IP與主機名稱綁定、防火牆（開啟連接埠或關閉防火牆）

5、安裝MySQL

       為主伺服器和從伺服器安裝MySQL，須保證兩者版本一致，或者slave端版本高於master版本。

三、主伺服器配置1、準備測試資料

準備測試資料，類比要同步對象。

mysql> create database hr;

mysql> use hr;

mysql> create table emp(id int primary key,name char(20));

mysql> insert into emp(id,name) values(1,‘tom‘);

2、設定my.cnf參數（1）拷貝設定檔

# cp /usr/local/mysql/my.cnf /etc/my.cnf

如有必要，應注意備份設定檔，並且在生產活動懂養成備份的意識。

（2）參數設定

以下僅列出幾個必要參數：

log-bin=mysql-bin             啟用二進位日誌server-id=1                        伺服器ID，可1-之間的任意整數binlog-ignore-db=information_schema           避免同步的資料庫binlog-ignore-db=performance_schema binlog-ignore-db=mysqlbinlog-do-db=hr                       二進位需要同步的資料庫名

注意：在設定檔中不可使用skip-networking參數，否則從伺服器將無法串連主伺服器並進行資料同步

（3）重啟伺服器

# service mysqld restart

3、為slave使用者添加同步專用許可權（1）建立複製賬戶並賦權

mysql> GRANT FILE ON *.* TO ‘slave_cp‘@‘192.168.1.2‘ IDENTIFIED BY ‘root‘;mysql> GRANT REPLICATION SLAVE ON *.* TO ‘slave_cp‘@‘192.168.1.2‘ IDENTIFIED BY ‘root‘;mysql> FLUSH PRIVILEGES;

（2）添加系統管理使用者

grant all privileges on *.* to [email protected]‘%‘ identified by ‘root‘;

（3）重新整理許可權

flush privileges; 

四、從伺服器設定1、設定my.cnf參數（1）備份設定檔

# cp /usr/local/mysql/my.cnf /etc/my.cnf

（2）修改參數資訊

必要參數如下：

log-bin=mysql-binserver-id=2                       #從伺服器IDbinlog-ignore-db=information_schemabinlog-ignore-db=performance_schemabinlog-ignore-db=mysqlreplicate-do-db=hr                 #同步的資料庫名replicate-ignore-db=mysql        #屏蔽不同步的資料庫log-slave-updatesslave-skip-errors=allslave-net-timeout=60

註：MySQL 5.6之後沒有master-user等參數，否則重啟報錯

（3）重啟伺服器

# service mysqld restart

五、Database Backup、同步1、對資料庫進行備份

[[email protected] mysql]# /usr/local/mysql/bin/mysqldump -h localhost -uroot -proot --all-databases --lock-all-tables >/tmp/dbdump.sql[[email protected] mysql]# scp /tmp/dbdump.sql [email protected]:/tmp/[[email protected] ~]# mysql -u root -p < /tmp/dbdump.sql

註：從伺服器不需要建立要同步的資料庫，備份指令碼會自動建立。

2、配置從伺服器串連主伺服器同步（1）、擷取主伺服器二進位日誌資訊

[[email protected] mysql]# mysql -uroot -pEnter password:mysql> flush tables with read lock;        --唯讀鎖定mysql> show master status;mysql> unlock tables;                       --解除鎖定

（2）設定從伺服器同步

[[email protected] ~]# mysql -u root –pmysql> stop slaves;mysql> change master to    -> master_host=‘192.168.152.129‘,    -> master_user=‘slave_cp‘,    -> master_password=‘root‘,    -> master_log_file=‘mysql-bin.000003‘,    -> master_log_pos=195;                 --這裡的參數取值參考查詢到的master日誌資訊mysql> start slave;mysql> show slave status \G

3、同步驗證

在master上添加資料，查看slave端的同步情況。

master節點：

[[email protected] ~]# mysql -uroot -pmysql> create database test;mysql> use test;mysql> create table users(id int primary key,name char(20));mysql> insert into users(id,name)values(1,‘aa‘);mysql> use hr;mysql> show tables;mysql> create table bb(id int,name char(20));mysql> insert into emp(id,name)values(2,‘bb‘);

 slave節點：

[[email protected] ~]# mysql -uroot -pmysql> select * from test.users;

錯誤1、啟動從伺服器失敗

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

解決：

mysql> reset slave;mysql> change master to master_host=‘192.168.152.129‘,master_user=‘slave_cp‘,master_password=‘root‘,master_log_file=‘mysql-bin.000005‘,master_log_pos=610;mysql> start slave;

參考：https://zhangge.net/5091.html

參考：http://blog.csdn.net/xlgen157387/article/details/51331244/

 

MySQL主從複製部署