MYSQL主从配置之重做从库

使用innobackupex 备份工具在线备份数据到从库

1, 主库：

1),备份主库到从库

mycnf=/etc/my.cnf
user=root
passwd=xxxxxxxxxxxxxxxxx
socket=/data/mysql/3306/var/run/mysql.sock

dest_ip=192.168.30.27
dest_user=root
dest_dir=/data/mysql_19
backupname=mysql_`date +%F_%H-%M-%S`.tar.gz
innobackupex  --user=$user --password=$passwd --defaults-file=$mycnf --no-lock  --socket=$socket --stream=tar $dest_dir |ssh $dest_user@$dest_ip "gzip > $dest_dir/$backupname"

2),创建同步账号

grant select on mysql.* to repl@'192.168.10.2' identified by '123456'

2，从库：

1)停止从库，并删除数据库文件

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

2)，解压备份到从库的数据目录:，参数i为忽略zero

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

3)还原数据到从库的数据目录   --apply-log

innobackupex --user=root --apply-log /opt/data/mysql/3306/data
chown -R mysql:mysql /opt/data/mysql      权限配置

4)查看备份的 position 

cat xtrabackup_binlog_info
mysql-bin.003786        275137157

5)启动从库 mysql

# nohup /usr/local/mysql/bin/mysqld_safe --defaults-file=/opt/data/mysql/3306/var/my.cnf 2>&1 &
/etc/init.d/mysqld start

6)配置从库

mysql --socket=/opt/data/mysql/3306/var/run/mysql.sock -uroot -p
change master to
MASTER_HOST='192.168.30.19',
MASTER_USER='repl',
MASTER_PASSWORD='xxxxxx',
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 -p
stop slave;
start slave;