Centos6.3下mysql主從複製筆記

MySQL主從複製結構是基於mysql bin-log日誌基礎上從庫通過開啟IO進程收到主庫的bin-log日誌增量資訊,並儲存到本地relay log,而後再通過開啟MYSQL進程從relay log上擷取的增量資訊並翻譯成SQL語句後寫到從資料庫。

主從複製結構實際上可以實現兩個功能

1.從庫充當主庫的Database Backup執行個體

2.讀寫分離主庫負責正常讀寫資料從庫只負責讀資料

實際生產環境因為很多應用實際讀資料庫的次數遠大於寫資料庫的次數所以在項目開發初期編寫程式時做一個判斷對所有讀的操作全部推到從庫若從庫無法擷取資料則再向主庫擷取資料從而在一定意義上實現讀寫分離緩解主庫的IO壓力。

所以生產環境下推薦使用這種架構。

系統內容centos6.3 x64

資料庫 mysql-5.6.10

mysql master:192.168.100.90

mysql slave:192.168.100.91

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131228/2233303Y7-0.jpg" title="mysql主從複製.jpg" alt="091218484.jpg" />

一.部署環境

1.關閉iptables和SELINUX

# service iptables stop

# setenforce 0

# vi /etc/sysconfig/selinux

---------------

SELINUX=disabled

---------------

2.安裝配置mysql傳送門http://showerlee.blog.51cto.com/2047005/1174141

二.主mysql配置:(mysql master)

修改mysql設定檔

# vi /etc/my.cnf

添加

-----------------

# Replication Master Server

# bin日誌路徑

log-bin = /usr/local/mysql/log/bin.log

# 伺服器ID號

server-id = 1

# 忽略mysql系統庫複製

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

------------

重啟服務

# service mysqld restart

登入mysql後台

# mysql -u root -p123456

查看此刻登入帳號

> select user();

在master為slave添加同步帳號

> grant replication slave on *.* to 'slave'@'192.168.100.91' identified by '123456';

查看建立的使用者

> select user.host from mysql.user;

查看許可權

> show grants for 'slave'@'192.168.100.91';

mysql鎖表唯讀(其他賬戶登入mysql後無法進行寫表操作防止備份資料庫後主mysql表更新導致和從資料庫內容不一致)

> flush tables with read lock;

查看鎖表倒計時時間

> show variables like '%timeout%';

------------------------

....

wait_timeout | 28800

------------------------

將master的資料庫表全部備份匯出並傳送到slave伺服器上。

# /usr/local/mysql/bin/mysqldump -u root -p123456 --opt --flush-logs --all-database > /root/allbak.sql

# cd ~

# scp allbak.sql root@192.168.100.91:/root

查看mysql位移量(資料庫如果有寫操作位移值會遞增)

# mysql -u root -p123456 -e "show master status"

----------------------

+------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------+----------+--------------+------------------+-------------------+

| bin.000009 | 120 | | | |

+------------+----------+--------------+------------------+-------------------+

----------------------

保證FILE列和Position列與從庫配置一致

三.從mysql配置mysql slave

修改mysql設定檔

# vi /etc/my.cnf

添加

--------------------

# Replication Slave Server

# bin日誌路徑無需開bin-log日誌

#log-bin = /usr/local/mysql/log/bin.log

server-id=2

# 唯讀

read-only

# 忽略mysql系統庫複製

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

---------------------

重啟服務

# service mysqld restart

恢複server的資料庫到slave

#/usr/local/mysql/bin/mysql -u root -p123456 < /root/allbak.sql

配置串連同步到server端

# mysql -u root -p123456;

> stop slave;

> reset slave;

> change master to master_host='192.168.100.90',master_user='slave',master_password='123456',master_log_file="bin.000009",master_log_pos= 120 ;

> start slave;

注master_log_file表示從主要資料庫哪個bin-log檔案開始同步

master_log_pos表示從該bin-log檔案哪條記錄點開始同步

需與主庫位移值保持同步

回到主mysql資料庫解鎖mysql master

# mysql -u root -p123456

> unlock tables;

最後登陸從mysql後台查看主從串連狀態

# mysql -u root -p123456 -e "show slave status\G;"

找到這五行如下則主從配置成功

------------------------

Slave_IO_State: Waiting for master to send event

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Read_Master_Log_Pos: 120

Relay_Master_Log_File: bin.000009

-------------------------

四.測試主從是否同步

(server)

# mysql -u root -p123456 -e "create database test02;"

# mysql -u root -p123456 -e "show databases like 'test02';"

------------

+-------------------+

| Database (test02) |

+-------------------+

| test02 |

+-------------------+

------------

(cilent)

# mysql -u root -p123456 -e "show databases like 'test02';"

-------------

+-------------------+

| Database (test02) |

+-------------------+

| test02 |

+-------------------+

-------------

測試成功。。

當server端的資料庫資料發生變化時client端會同步更新從而實現主庫備份和讀寫分離作用。

這裡注意幾點

1.mysql從庫中需在my.cnf設定檔中加入 read-only參數保證從庫唯讀。

# echo "read-only" >> /etc/my.cnf

2.忽略從庫mysql與information_schema系統資料表同步

# echo "binlog-ignore-db=mysql" >> /etc/my.cnf

# echo "binlog-ignore-db=information_schema" >> /etc/my.cnf

生產環境主庫使用者的授權,授權增刪改查許可權。

> GRANT SELECT,INSERT,UPDATE,DELETE ON *.* to 'user'@'%' identified by '123456';

生產環境從庫的授權,僅授權查許可權。

> GRANT SELECT ON *.* to 'user'@'%' identified by '123456';

3.從庫預設不開啟bin-log日誌功能除非做下級從庫級聯同步才需開啟從庫的bin-log日誌。

4.主庫由於硬體故障如何將從庫提升為主庫(一主多從)

(mysql slave)

(1) 確保從機沒有再同步的SQL語句即出現Has read all relay log再關閉從庫IO_Threat進程

# mysql -uroot -p123456

> stop slave IO_THREAD

(2) 關閉從庫slave服務然後將其提升為主庫

> stop slave

> reset master

(3) 更換從庫IP為故障主庫IP(配置方法略)

(4) 刪除新的主庫master.info和relay-log.info,防止下次重啟還會按照從庫啟動

# cd /usr/local/mysql/log

# rm -rf master.info relay-log.info

(5) 重新設定從庫串連主庫的帳號同步資訊以及在下級從庫重新設定位移量保持與新的主庫一致即可。

最後待主庫硬體恢複將其再設定為從庫並更換為上述從庫IP地址,完成主從切換。

五.配置指令碼

1.如果想實現無人值守備份主要資料庫可添加如下指令碼並在淩晨執行定時任務

# vi /etc/rc.d/mysql_bak.sh

---------------------

#!/bin/sh

MYSQL_USER=root

MYSQL_PW="123456"

LOG_PATH=/usr/local/mysql/log

DATA_PATH=/usr/local/mysql/data

LOG_FILE=${LOG_PATH}/mysqllog_`date +%F`.log

DATA_FILE=${DATA_PATH}/mysql_backup_`date +%F`.sql.gz

BIN_PATH=/usr/local/mysql/bin

MYSQL_CMD="$BIN_PATH/mysql -u$MYSQL_USER -p$MYSQL_PW"

MYSQL_DUMP="$BIN_PATH/mysqldump -u$MYSQL_USER -p$MYSQL_PW --opt --flush-logs --all-database"

$MYSQL_CMD -e "flush tables with read lock;"

echo "-------show master status result-------" >> $LOG_FILE

$MYSQL_CMD -e "show master status" >> $LOG_FILE

${MYSQL_DUMP}|gzip > $DATA_FILE

$MYSQL_CMD -e "unlock tables"

mail -s "mysql slave log" 1234567@qq.com < $LOG_FILE

---------------------

淩晨3:30執行備份資料庫操作

# crontab -e

---------------------

30 3 * * * /bin/sh /etc/rc.d/mysql_bak.sh > /dev/mull 2>&1

---------------------

# service crond restart

2.如果想實現將主庫備份的資料分發到從庫恢複從庫資料庫以及開啟從庫功能可添加如下指令碼

注這裡建議主從先做好秘鑰認證

詳見傳送門http://showerlee.blog.51cto.com/2047005/1217651

# vi /etc/rc.d/mysql_bak1.sh

------------------

#!/bin/sh

MYSQL_USER=root

MYSQL_PW="123456"

MYSQL_SLAVE_IP="192.168.100.91"

SSH_PATH="/usr/bin/ssh"

SSH_CMD="${SSH_PATH} ${MYSQL_SLAVE_IP}"

GZIP_CMD="/bin/gzip"

LOG_PATH=/usr/local/mysql/log

DATA_PATH=/usr/local/mysql/data

LOG_FILE=${LOG_PATH}/mysqllog_`date +%F`.log

DATA_FILE=${DATA_PATH}/mysql_backup_`date +%F`.sql.gz

BIN_PATH=/usr/local/mysql/bin

MYSQL_CMD="$BIN_PATH/mysql -u$MYSQL_USER -p$MYSQL_PW"

MYSQL_DUMP="$BIN_PATH/mysqldump -u$MYSQL_USER -p$MYSQL_PW --opt --flush-logs --all-database"

$MYSQL_CMD -e "flush tables with read lock;"

echo "-------show master status result-------" >> $LOG_FILE

$MYSQL_CMD -e "show master status" >> $LOG_FILE

${MYSQL_DUMP}|gzip > $DATA_FILE

#config slave

cd ${DATA_PATH}

scp "mysql_backup_`date +%F`.sql.gz" $MYSQL_SLAVE_IP:/tmp/

${SSH_CMD} "${GZIP_CMD} -d /tmp/mysql_backup_`date +%F`.sql.gz"

${SSH_CMD} "${MYSQL_CMD} < /tmp/mysql_backup_`date +%F`.sql"

${SSH_CMD} "cat |$MYSQL_CMD" << EOF

stop slave;

change master to

master_host='192.168.100.90',

master_user='slave',

master_password='123456',

master_log_file="bin.000009",

master_log_pos= 120;

start slave;

EOF

$SSH_CMD $MYSQL_CMD -e "show slave status\G;"|egrep "IO_Running|SQL_Running" >> $LOG_FILE

$MYSQL_CMD -e "unlock tables"

mail -s "mysql slave log" 1234567@qq.com < $LOG_FILE

$SSH_CMD mail -s "mysql slave log" 1234567@qq.com < $LOG_FILE

------------------

mysql主從my.cnf參數配置:

--------------------------------

[mysqld]

port= 3306

datadir=/usr/local/mysql/data

socket=/var/lib/mysql/mysql.sock

user=mysql

symbolic-links=0

max_connections = 16384

skip-external-locking

skip-name-resolve

key_buffer_size = 256M

query_cache_limit = 1M

query_cache_size = 64M

max_allowed_packet = 4M

#table_cache = 8

thread_concurrency = 8

sort_buffer_size = 8M

read_buffer_size = 4M

read_rnd_buffer_size = 4M

net_buffer_length = 1M

thread_stack = 1M

log-error=/usr/local/mysql/log/error.log

log=/usr/local/mysql/log/mysql.log

long_query_time=2

log-slow-queries= /usr/local/mysql/log/slowquery.log

# 伺服器ID號(主為1從為2)

server-id = 1

# 忽略mysql系統庫複製

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

log-bin = /usr/local/mysql/log/bin.log

# 刪除10天之前的二進位日誌

expire_logs_days = 10

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

#safe-updates

[isamchk]

key_buffer = 8M

sort_buffer_size = 8M

[myisamchk]

key_buffer = 8M

sort_buffer_size = 8M

[mysqlhotcopy]

interactive-timeout

--------------------------------

-------大功告成---------

本文出自 “一路向北” 部落格，請務必保留此出處http://showerlee.blog.51cto.com/2047005/1220801