mysql主从服务器的配置笔记

mysql主从服务器的配置笔记

注: 从MySQL从服务器的版本不能小于主服务器的版本

一. MySQL主服务器配置
1.建立用户
grant replication slave on *.* to repluser1@192.168.8.126 identified by ‘111111′;
# grant replication slave on *.* to ‘用户名’@'主机’ identified by ‘密码’;
# 可在B Slave上做连接测试: mysql -h 192.168.8.126 -u test -p

2.编辑配置文件/etc/my.cnf
# 确保有如下行
server-id = 1
log-bin=mysql-bin
binlog-do-db=test
binlog-ignore-db=mysql
#binlog-do-db=需要备份的数据库名，可写多行
#binlog-ignore-db=不需要备份的数据库名，可写多行
二.MySQL从服务器配置
1.编辑/etc/my.cnf
server-id=2
log-bin=mysql-bin
master-host=192.168.8.125
master-user=repluser1
master-password=111111
master-port=3306
replicate-do-db=test
replicate-do-db=test1
# replicate-do-db=test  需要备份的数据库名
# replicate-ignore-db=mysql 忽略的数据库
# master-connect-retry=60 如果从服务器发现主服务器断掉，重新连接的时间差(秒)

记得先手动同步一下主从服务器中要备份的数据库，然后重启主，从服务器。

三.验证是否配置正确
登录从服务器输入如下命令:
mysql> show slave status\G;
确如如下行一致:
Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

四.其它
++如果你想使用复制数据文件的方式来备份数据库
只要在从服务器上的mysql命令行先键入slave stop;然后复制数据库文件，复制好了，再在mysql命令行键入slave start;启动从服务器，这样就即备份了数据有保证了数据完整性，而且整个过程中主服务器的mysql无需停止。

++修改配置需要注意
需要删除从服务器上的/var/lib/mysql/master.info文件

++主服务器上的相关命令
show master status
show slave hosts
show logs
show binlog events
purge logs to ‘log_name’
purge logs before ‘date’
reset master(老版本flush master)
set sql_log_bin=

++从服务器上的相关命令
slave start
slave stop
SLAVE STOP IO_THREAD //此线程把master段的日志写到本地
SLAVE start IO_THREAD
SLAVE STOP SQL_THREAD //此线程把写到本地的日志应用于数据库
SLAVE start SQL_THREAD
reset slave
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
load data from master
show slave status(SUPER,REPLICATION CLIENT)
CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= //动态改变master信息
PURGE MASTER [before 'date'] 删除master端已同步过的日志

++产生了mysql-bin.00000x文件可以删除
reset master; #`

++同步出错时，如果被同步语句确定在从库上可以被忽略
slave stop;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n;
slave start;
# 看show slave status \G中的Seconds_Behind_Master: 0为正常

附件1:检查从服务器状态脚本
#!/bin/bash
#/blog.zhangjianfeng.com/sh/mysql/CheckMysqlStatus.sh

DATE=`date +%Y%m%d-%H%M%S`
LOGPATH=/blog.zhangjianfeng.com/logs/Mysql
LOG=$LOGPATH\/SlaveStatus_log_$DATE
IP_LIST=(219.xx.xx.xx 60.xx.xx.xx)

if [ ! -d $LOGPATH ]
then
mkdir -p $LOGPATH
fi

checkstatus()
{
for i in ${IP_LIST[*]}
do
echo -e “\n====== Checking $i ======”
ssh $i “mysql -ppassword -e \”show slave status\\G \”"
done
}

case “$1″ in

checkselect)
checkselect
;;
*)
checkstatus
;;
esac

附件2:自动同步从服务器数据
#!/bin/bash
now=`date +%y%m%d-%H%M`
date=`date +%Y%m%d`
srcdir=/blog.zhangjianfeng.com/backup/mysql/forscript/$date/
DBNAME=ptv
DSTDIR=/blog.zhangjianfeng.com/tmp/mysql/$date/
IP_LIST=(xx.xx.xx.13 xx.xx.xx.14 xx.xx.xx.15)

[ ! -d $srcdir ] && mkdir -p $srcdir

savelog()
{
echo -e “\nSaving error logs,pls wait…”
/blog.zhangjianfeng.com/sh/mysql/CheckMysqlStatus.sh wlog
}

start ()
{

#savelog

### MYSQLDUMP & GZIP ###
echo -e “\eMysqlDump running, pls wait…”
cd $srcdir
rm -f $srcdir/*
mysqldump -ppassword –master-data $DBNAME > $DBNAME.$date
SRCFILEMD5=`md5sum $DBNAME.$date|awk ‘{print $1}’ `
gzip $DBNAME.$date
echo “MysqlDump and GZIP done!”

### transfer
echo “starting transfer…”
ssh $DSTIP ” [ -d $DSTDIR ] && rm -rf $DSTDIR ”
ssh $DSTIP ” mkdir -p $DSTDIR ”
scp $srcdir$DBNAME.$date\.gz $DSTIP:$DSTDIR
echo “Transfer done…”

ssh $DSTIP “cd $DSTDIR && gzip -d $DBNAME.$date\.gz”
DSTFILEMD5=` ssh $DSTIP “cd $DSTDIR && md5sum $DBNAME.$date” |awk ‘{print $1}’`

echo $SRCFILEMD5 $DSTFILEMD5

if [ ! $SRCFILEMD5 == $DSTFILEMD5 ]
then
echo “Transfer failed, pls check!”
exit 999
fi
echo “MD5 check passed”

ssh $DSTIP “mysql -ppassword -A -e \”slave stop\” && mysql -ppassword $DBNAME < $DSTDIR$DBNAME.$date && mysql -ppassword -A -e \”slave start\”"

}

check()
{
echo -e “\n`date +%Y.%m.%d\ %R:%S\ start…`”
mysql -ppassword -e “use $DBNAME; insert into slavetest(field1) values (\”slavetest-$now\”);”
echo MasterDB
mysql -ppassword -e “select * from $DBNAME.slavetest order by id desc limit 10 ”
sleep 10

for i in ${IP_LIST[*]}
do
echo $i
ssh $i “mysql -pzhangjianfeng0123456 -e \”select * from $DBNAME.slavetest order by id desc limit 10 \”"
done

echo -e “\n`date +%Y.%m.%d\ %R:%S\ END!`”
}

checkandmail()
{
logfile=/blog.zhangjianfeng.com/tmp/checkallslavedbstatus
> $logfile
echo -e “\n`date +%Y.%m.%d\ %R:%S\ start…`” >>$logfile
mysql -e “use $DBNAME; insert into slavetest(field1) values (\”slavetest-$now\”);” -ppassword
sleep 10
echo ==$DSTIP1== >>$logfile
ssh $DSTIP1 “mysql -e \”select * from $DBNAME.table1 order by id desc limit 1 \”" >>$logfile
echo -e “`date +%Y.%m.%d\ %R:%S\ END!`” >>$logfile
cat $logfile | mail -s “Check all SlaveDB status every week.” [e-mail]
}

case $1 in

xx.xx.xx.13|13)
DSTIP=xx.xx.xx.13
start
;;

xx.xx.xx.14|14)
DSTIP=xx.xx.xx.14
start
;;

xx.xx.xx.15|15)
DSTIP=xx.xx.xx.15
start
;;

check)
check
;;

checkandmail)
checkandmail
;;

*)
echo “$0 {SLAVEIP|check}”
;;

esac