mysql主从配置

1，必备条件，主库

1) 复制账号：

grant replication slave on *.* to repl@'192.168.30.19' identified by '123456'

2) 启用二进制日志，serverid

log-bin=/data/mysql/binlogmysql-bin

server_id=10     #主从不能一样

show master status;         #查看主库状态

2，停止从库，删除数据文件夹

/etc/init.d/mysqld stop

rm -rf /mysql/3306/data/*

3, 备份脚本：直接解压到从库的data文件夹

#!/bin/sh

CONFIG_FILE=/etc/my.cnf

USERNAME=root

PASSWORD=xxxxxxxxxxx

SOCKET=/data/mysql/3306/var/run/mysql.sock

REMOTE_IP=10.205.132.44

REMOTE_DIR=/data2/bi_dbbak/biotter

BACKUP_NAME=`date "+%Y%m%d_%H%M%S"`_backup.tar.gz

innobackupex \

--throttle=400 \

--user=$USERNAME \

--password=$PASSWORD \

--defaults-file=$CONFIG_FILE \

--socket=$SOCKET \

--stream=tar \

./ |ssh hadoop@$REMOTE_IP "tar xfi - -C /data/mysql/3306/data"      

4, --apply-log还原到从库的data目录   

innobackupex --user=root --defaults-file=/etc/my.cnf  --apply-log /data/mysql/3306/data

chown -R  mysql:mysql /data/mysql      权限配置

5, 查看 position

cat xtrabackup_binlog_info
mysql-bin.003786        275137157

6,启动 mysql

/etc/init.d/mysqld start

7，配置从库

change master to

MASTER_HOST='10.205.130.11',

MASTER_USER='slaves',

MASTER_PASSWORD='xxxxx',

MASTER_PORT=3306,

MASTER_LOG_FILE='mysql-bin.001341',

MASTER_LOG_POS=513301341;

start slave;

show slave status\G

8, 修复报错  ./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;