MySQL主從配置

標籤：

MySQL這個東西呢，是一個比較容易上手的資料庫，但是要想精通，是需要下很大功夫的。

以下是一些MySQL簡單的主從配置，隨便寫寫，有時間就改改，慢慢進步吧。

目前公司裡面用的是1台實體機作為MySQL的資料庫，前段時間做了個從庫的配置，目前只是簡單的主從同步，接下來的主備切換/資料恢複還在研究當中。

1.理論基礎

在實際的一些生產環境中，由單台Mysql作為獨立的資料庫在工作的過程中，非常容易由於短時間內的頻繁讀寫操作，造成資料庫負載、流量的增高，導致伺服器記憶體佔用太高，進而頁面卡死，系統不能正常運作。這樣是完全不能滿足實際需求的，無論是在安全性，高可用性以及高並發等各個方面。

 

2.準備工作

2台實體機作為主從。

主庫IP：192.168.0.111 主庫上MySQL安裝版本為：5.5.25

從庫IP：192.168.0.121 從庫上MySQL安裝版本為：5.5.43

 

3.開始

主庫my.cnf設定檔：

啟用log_bin，設定對應路徑如log_bin=/var/lib/mysql/mysql-bin(mysql的二進位日誌，記錄資料庫的操作)

server-id = 1

#Binlog-do-db=xxx(配置要複製的庫)

#Binlog-ignore-db=xxx(配置不要複製的庫)

重啟主庫 service mysqld restart

 

從庫my.cnf設定檔：

啟用logs-bin

server-id = 121

skip-slave-start

replicate-ignore-db = mysql 不同步資料庫mysql

replicate-ignore-db = test 不同步資料庫test

replicate-ignore-db = information_schema 不同步資料庫information_schema

重啟從庫 service mysqld restart

 

在從庫建立一個user，並在主庫上賦予該 從庫IP:user 查詢主庫的許可權

mysql>GRANT REPLICATION SLAVE ON *.* to ‘slave121‘@‘192.168.0.121‘ IDENTIFIED BY ‘slave121_password‘;

 

備份主庫資料->從庫

A.備份主庫資料

方法1：

mysqldump -uroot -p --single-transaction --master-data=2 --all-databases --default-character-set="utf8" >/var/mysqlbk/111all.sql 2>/var/mysqlbk/111all.log 打包0.111上資料庫的資料到/var/mysqlbk/111all.sql中，打包記錄在111all.log中方法2：備份指定資料庫，過濾test mysql information_schema

mysqldump -uroot -p --single-transaction --master-data=2 --default-character-set="utf8" --databases xxx_a xxx_b xxx_c(指定打包的資料庫)  >/var/mysqlbk/111all.sql 2>/var/mysqlbk/111all.log

 

B.還原資料 若用方法1，要排除掉mysql test information_schema

方法1對應還原：

mysql -utest -p  --one-database xxx_a xxx_b xxx_c  < /tmp/111all.sql  --default-character-set=utf8 -h 127.0.0.1

方法2對應的還原：

mysql -utest -p  < /tmp/111all.sql  --default-character-set=utf8 -h 127.0.0.1

 

讓從庫讀主庫的log_bin，實現同步

在打包的111all.sql檔案中，尋找binlog的File與Position值，如mysql-bin.000039    261

在從庫上執行：

mysql> change master to
master_host=’192.168.0.111’,

master_user=’slave121’,

master_password=’slave121_password’,

master_log_file=’mysql-bin.000039’,

master_log_pos=261;

 

mysql>slave start;

mysql>show slave status;

**************** 1. row *******************
Slave_IO_State:
Master_Host: 192.168.0.111
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000039
Read_Master_Log_Pos: 415
Relay_Log_File: localhost-relay-bin.000008
Relay_Log_Pos: 561
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: YES
Slave_SQL_Running: YES
Replicate_Do_DB:
……………省略若干……………
Master_Server_Id: 1
1 row in set (0.01 sec)
==============================================

其中Slave_IO_Running 與 Slave_SQL_Running 的值都必須為YES，才表明狀態正常。

 

至此，MySQL的主從同步就實現了。

驗證：主庫中my.cnf檔案定義binlog_format=mixed(row,statement不推薦)，注意觀察預存程序、視圖等對資料一致性的影響。

 

MySQL主從配置