MySQL Backup and Replcation
Posted On 2012年5月17日 星期四 at 於 下午3:51 by Lani使用 MySQL Admin 登入
/usr/local/mysql/bin/mysql -u root -p xxxx
設定 repuser 權限
GRANT ALL PRIVILEGES ON *.* TO 'repuser'@'db1.mysql.net.tw' IDENTIFIED BY PASSWORD 'reppass' WITH GRANT OPTION;
鎖定所有tables禁止更新
mysql #FLUSH TABLES WITH READ LOCK;
顯示 master status
mysql#show master status \G
*************************** 1. row ***************************
File: db1-bin.074
Position: 118993931
Binlog_do_db:
Binlog_ignore_db:
1 row in set (0.00 sec)
將 MySQL 備份
# tar zcvf mysql.`date +%Y%m%d`.tar.gz /var/mysql
解除 Master MySQL 表格禁止更新
mysql #UNLOCK TABLES;
Buildup New Replcation Server
####################################
# cd /var
# tar zxvf mysql.`date +%Y%m%d`.tar.gz
在 /etc/my.cnf 新增以下設定:
server-id = 101
master-host = db1.mysql.net.tw
master-user = repuser
master-password = reppass
master-info-file = /var/mysql/master.info
master-port = 3306
# vi /var/mysql/master.info
db1-bin.074
118993931db1.mysql.net.tw
repuser
reppass
3306
60
# /usr/local/etc/rc.d/mysql.sh start
宣告 Replcation 從哪一個Log_File & Log_Pos_Id 開始:
# change master to Master_Log_File=' db1-bin.074 ',Master_Log_Pos= 118993931 ;
mysql # show slave status \G
*************************** 1. row ***************************
Master_Host: db1.mysql.net.tw
Master_User: repuser
Master_Port: 3306
Connect_retry: 60
Log_File: db1-bin.074
Pos: 118993931
Slave_Running: Yes
Replicate_do_db:
Replicate_ignore_db:
Last_errno: 0
Last_error:
Skip_counter: 0
1 row in set (0.00 sec)