Mysql主从服务器的配置笔记

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

实验环境

Master Mysql服务器版本5.1.36,IP:192.168.128.130

Slave Mysql服务器版本5.1.36,IP:192.168.128.132

一. MySQL主服务器配置

1.建立授权用户

用法:grant replication slave on *.* to ‘用户名’@'主机’ identified by ‘密码’;

mysql>grant replication slave on *.* to backup@192.168.128.132 identified by ‘123′;

刷新权限

mysql>flush privileges;

可以用这条命令查看mysql现在有哪些用户:

mysql>select user,host from mysql.user;

2.编辑配置文件/etc/my.cnf

# 确保有如下行

server-id = 1 //主服务器的id号要为1.从服务器比主服务器的低.

log-bin=mysql-bin

binlog-do-db=test //需要备份的数据库名.这里为test库. 如果要备份多个库可写多行.

binlog-ignore-db=mysql //不需要备份的库.也可以写多行.

二.MySQL从服务器配置

1.编辑/etc/my.cnf

server-id=2

log-bin=mysql-bin

master-host=192.168.128.130 //mysql主服务器的ip

master-user=backup //前面主服务器建立的授权用户名

master-password=123 //主服务器授权用户的密码

master-port=3306 //主服务器端口

replicate-do-db=test //需要备份的数据库名

replicate-ignore-db=mysql //不需要备份的数据库

master-connect-retry=60 如果从服务器发现主服务器断掉，重新连接的时间差(秒)

Ps:需要备份的数据库.主从服务器的配置里都要写明.不然就没法同步了.

记得先手动同步一次主从服务器中要备份的数据库，如果没有先同步数据库.配置完成后也不会同步.然后重启主，从服务器让配置生效。

三..验证是否配置正确

1.登录主服务器输入如下命令查看主服务器的需要备份的数据库配置是否正确:

mysql> show master status;

+------------------+----------+--------------+--------------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+--------------------------+

| mysql-bin.000008 | 585 | test,| |

+------------------+----------+--------------+--------------------------+

2.登录从服务器输入如下命令:

mysql> show slave status/G;

显示如下:

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.50.216

Master_User: repluser217

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000007

Read_Master_Log_Pos: 106

Relay_Log_File: Mysql217-relay-bin.000024

Relay_Log_Pos: 251

Relay_Master_Log_File: mysql-bin.000007

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: test

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 106

Relay_Log_Space: 554

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 2013

Last_IO_Error: error reconnecting to master 'repluser217@192.168.50.216:3306' - retry-time: 60 retries: 86400

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set (0.01 sec)

ERROR:

No query specified

确如如下行一致:

Waiting for master to send event

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

如果Slave_IO_Running: No一般是配置文件不正确导致的,或者Master服务器没有及时同步info导致的,因为Master-slave的同步其实是异步方式

同步完后在slave的datadir下会出现一个master.info和一个relay.info

如果mysql认为binlog已经删除了.必须要手动删除这两个文件才可重启slave

测试:

1).先查看master跟slave的test.

mysql> use test

Database changed

mysql> show tables;

现在两台机的test都为空.

2.)在master上进行操作.

mysql> use test

mysql> create table repl_test(id int);

mysql> insert into repl_test values(1),(2);

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| repl_test |

+----------------+

1 row in set (0.00 sec)

mysql> select * from repl_test;

+------+

| id |

+------+

| 1 |

| 2 |

|+------+

2 rows in set (0.00 sec)

3).登陆slave查看test表内容是否与master一致.

mysql> use test

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| repl_test |

+----------------+

1 row in set (0.00 sec)

mysql> select * from repl_test;

+------+

| id |

+------+

| 1 |

| 2 |

|+------+

内容一致.同步成功.

四.关于备份

如果你想使用复制数据文件的方式来备份数据库

只要在从服务器上的mysql命令行先键入

mysql>slave stop;

然后复制数据库文件，复制好了，再在mysql命令行键入

mysql>slave start;

启动从服务器，这样就即备份了数据有保证了数据完整性，而且整个过程中主服务器的mysql无需停止。

五.修改配置需要注意

需要删除从服务器上的/var/lib/mysql/master.info文件

六.主服务器上的相关命令

mysql>show master status

mysql> show slave hosts

mysql> show logs

mysql> show binlog events

mysql> purge logs to ‘log_name’

mysql> purge logs before ‘date’

mysql> reset master(老版本flush master)

mysql> set sql_log_bin=

七.从服务器上的相关命令

mysql> slave start

mysql> slave stop

mysql> SLAVE STOP IO_THREAD //此线程把master段的日志写到本地

mysql> SLAVE start IO_THREAD

mysql> SLAVE STOP SQL_THREAD //此线程把写到本地的日志应用于数据库

mysql> SLAVE start SQL_THREAD

mysql> reset slave

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER

mysql> load data from master

mysql> show slave status(SUPER,REPLICATION CLIENT)

mysql> CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, mysql> MASTER_PASSWORD= //动态改变master信息

mysql> PURGE MASTER [before 'date'] 删除master端已同步过的日志

产生了mysql-bin.00000x文件可以删除

reset master; #http://bbs.chinaunix.net/thread-745343-1-1.html`

同步出错时，如果被同步语句确定在从库上可以被忽略

mysql> slave stop;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n;

mysql> slave start;

看show slave status /G中的Seconds_Behind_Master: 0为正常

附件1:检查从服务器状态脚本

注:以下IP_LIST和ssh $i这两行需要根据自己的情况去修改

#!/bin/bash

#/usr/local/scripts/CheckMysqlStatus.sh

DATE=`date +%Y%m%d-%H%M%S`

LOGPATH=/mydata/mybak/logs

LOG=$LOGPATH//SlaveStatus_log_$DATE

IP_LIST=(218.xx.xx.xx 192.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=/mydata/mybak/$date/

DBNAME=test

DSTDIR=/mydata/mybak/$date/

IP_LIST=(xx.xx.xx.13 xx.xx.xx.14 xx.xx.xx.15)

DSTIP=192.168.50.217

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

savelog()

{

echo -e “/nSaving error logs,pls wait…”

/usr/local/scripts/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 -ppassword -e /”select * from $DBNAME.slavetest order by id desc limit 10 /”"

done

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

}

checkandmail()

{

logfile=/mydata/mybak/logs/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.” tonyty163@766.com

}

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

常见问题：

1、输入命令mysql> show slave status/G;报错如下：

Last_IO_Error: error connecting to master 'back@192.168.128.132:3306' - retry-time: 60 retries: 86400

用户名是back，但是我在my.cnf中配置的username为backup。

解决方法：

第一、关闭master和slave服务器.修改配置文件username=backup.

第二、slave服务器上删除数据库目录下的mysql-bin*、master.info、你的主机名-relay-bin.*。

第三、Master服务器上删除数据目录下的mysql-bin*

第四、重启master

第五、重启slave

2、在slave中新建一张表，但是master中没有出现。

这样做是错误的，只有master中创建的表格以及插入的数据才会同步到slave中，反之不行，而且这样做将会导致slave无法正常工作。

3、在slave中新建一张表a，接着在master中新建一张表a，发现之后master所有的新表都没有同步到slave中。

切记，一定不要在slave中做sql增删改操作，否则有可能出现这个状况，这个时候只能把slave中的a表删除，然后在slave服务器如下操作

Mysql>slave stop

Mysql>slave start

问题即可解决。