แสดงกระทู้
251
MySQL / Re: xtrabackup กับการสำรองข้อมูล แบบที่เร็วที่สุด
« เมื่อ: เมษายน 11, 2011, 15:28:25 PM »ตัวล่างเป็น script ที่ผมให้พี่คนหนึ่งเขียนให้ครับ copy จาก master ไป slave เลยครับท่านใด ลองแล้วบ้างครับ เป็นอย่างไรบ้าง
ใครมีความสามารถเอาไปใช้ได้นะครับ ไม่หวงครับ
export masterip="192.168.99.90"
export slaveip="192.168.99.91"
export database="hos"
export mypwd="password"
echo "Start transfer data from master $masterip to slave $slaveip"
echo "If you want automatic login please use command"
ssh-keygen -t rsa
ssh root@$slaveip mkdir -p .ssh
cat /root/.ssh/id_rsa.pub | ssh root@$slaveip 'cat >> .ssh/authorized_keys'
mysql -u root -p$mypwd -e "show full processlist"
while [ "$?" -ne "0" ]
do
echo "Wait for master mysql startup"
sleep 3
mysql -u root -p$mypwd -e "show full processlist"
done
export eventid=`mysql -u root -p$mypwd -e "select max(event_id) from $database.replicate_log"`
export eventid=`echo $eventid | cut -d ' ' -f2`
mysql -u root -p$mypwd -e "delete from $database.replicate_cfg"
echo "Current maximum event_id from replicate_log is $eventid"
#innobackupex-1.5.1 --stream=tar ./ | ssh root@$slaveip cat ">" backup.tar
#ssh root@$slaveip "tar -ixf backup.tar -C /var/lib/mysql/"
#innobackupex-1.5.1 --user=root --password=$mypwd --stream=tar ./ | gzip > /root/backup.tar.gz
innobackupex-1.5.1 --user=root --password=$mypwd --stream=tar ./ > /root/backup.tar
#############################
echo "------Master tier finished------"
#############################
echo "#######################"
echo "------Slave tier start-----"
ssh root@$slaveip 'service mysql stop'
ssh root@$slaveip 'rm -fr /var/lib/mysql/*'
# copy my.cnf from master to slave (can ignore)
rsync -avP -e ssh /etc/my.cnf root@$slaveip:/etc/
# copy xtrabackup program from master to slave (can ignore)
rsync -avP -e ssh /usr/bin/xtrabackup root@$slaveip:/usr/bin/
rsync -avP -e ssh /usr/bin/innobackupex-1.5.1 root@$slaveip:/usr/bin/
# copy backup file from master to slave
echo "backup file transfering to slave..."
#rsync -avP -e ssh /root/backup.tar.gz root@$slaveip:/root
rsync -avP -e ssh /root/backup.tar root@$slaveip:/root
echo "Transfer data done wait 3 sec to continue"
sleep 3
# extract backup file at slave
echo "extracting backup file at slave..."
#ssh root@$slaveip "tar -izxf /root/backup.tar.gz -C /var/lib/mysql/"
ssh root@$slaveip "tar -ixf /root/backup.tar -C /var/lib/mysql/"
ssh root@$slaveip '/usr/bin/mysql_install_db'
ssh root@$slaveip 'innobackupex-1.5.1 --apply-log --use-memory=150M /var/lib/mysql'
ssh root@$slaveip 'xtrabackup --prepare --use-memory=150M --target-dir=/var/lib/mysql'
ssh root@$slaveip 'chown mysql:mysql /var/lib/mysql/* -R'
ssh root@$slaveip 'myisamchk -r /var/lib/mysql/hos/*.MYI'
sleep 2
export mypid=`ssh root@$slaveip 'ps -ea |grep mysqld'`
export mypid1=`echo $mypid | cut -d ' ' -f1`
export mypid2=`echo $mypid | cut -d ' ' -f5`
ssh root@$slaveip "kill -9 $mypid1"
ssh root@$slaveip "kill -9 $mypid2"
sleep 2
ssh root@$slaveip 'service mysql start'
ssh root@$slaveip "mysql -u root -p$mypwd -e 'show full processlist'"
while [ "$?" -ne "0" ]
do
echo "Wait for slave mysql startup"
sleep 3
ssh root@$slaveip "mysql -u root -p$mypwd -e 'show full processlist'"
done
echo "delete from $database.replicate_cfg;" | ssh root@$slaveip 'cat > final.sql'
echo "truncate table $database.replicate_log;" | ssh root@$slaveip 'cat >> final.sql'
echo "insert into $database.replicate_cfg (server) values (0);" | ssh root@$slaveip 'cat >> final.sql'
echo "update $database.replicate_cfg set server =\"$masterip\";" | ssh root@$slaveip 'cat >> final.sql'
echo "update $database.replicate_cfg set lastreplicate =\"$eventid\";" | ssh root@$slaveip 'cat >> final.sql'
ssh root@$slaveip "mysql -u root -p$mypwd < final.sql"
echo "-----Sucessfully------"
