MYSQL主從配置之innobackupex

標籤：

1, 主庫：備份資料庫到從庫1),備份指令碼

mycnf=/etc/my.cnfuser=rootpasswd=xxxxxxxxxxxxxxxxxsocket=/data/mysql/3306/var/run/mysql.sockdest_ip=192.168.30.27dest_user=rootdest_dir=/data/mysql_19backupname=mysql_`date +%F_%H-%M-%S`.tar.gzinnobackupex  --user=$user --password=$passwd --defaults-file=$mycnf --no-lock  --socket=$socket --stream=tar $dest_dir |ssh [email protected]$dest_ip "gzip > $dest_dir/$backupname"

2),主庫上配置同步帳號

grant select on mysql.* to [email protected]‘localhost‘ identified by ‘123456‘

2，從庫：

1)停止從庫mysql ，並刪除資料

/etc/init.d/mysqld stoprm -rf /opt/mysql/3306/data/*

2)，解壓到從庫的資料目錄:，參數i為忽略zero

tar xvfi backup.tar -C /opt/mysql/3306/data

 

3)還原到從庫的資料目錄   --apply-log

# innobackupex --user=root --socket=/opt/data/mysql/3306/var/run/mysql.sock --defaults-file=/opt/data/mysql/3306/var/my.cnf  --apply-log /opt/data/mysql/3306/datainnobackupex --user=root --apply-log /opt/data/mysql/3306/datachown -R mysql:mysql /opt/data/mysql      許可權配置

 

4)查看備份的 position 

cat xtrabackup_binlog_infomysql-bin.003786        275137157

 

5)啟動從庫 mysql

nohup /usr/local/mysql/bin/mysqld_safe --defaults-file=/opt/data/mysql/3306/var/my.cnf 2>&1 &

 

6)配置從庫

/usr/local/mysql/bin/mysql  --socket=/opt/data/mysql/3306/var/run/mysql.sock -uroot -pchange master toMASTER_HOST=‘192.168.30.19‘,MASTER_USER=‘slaves‘,MASTER_PASSWORD=‘JQFvS4EPazp‘,MASTER_PORT=3306,MASTER_LOG_FILE=‘mysql-bin.003786‘,MASTER_LOG_POS=275137157;
start slave;show slave status\G

 

 7)修複報錯  ./mysql/proc‘ is marked as crashed and should be repaired 

mysqlcheck --repair mysql proc --socket=/opt/data/mysql/3306/var/run/mysql.sock -uroot -pstop slave;start slave;

 

  

MYSQL主從配置之innobackupex