Tag Archives: mysql

Mysql 主从设置

最近刚好有人问到Mysql主从库怎么配置,想想干脆写篇文章,也方便以后使用。
Mysql设置主从后可以缓解数据库的读取压力,将读表操作分散到从库进行。

首先我们假设:
主库(Master): 10.10.1.10
从库(Slave): 10.10.1.11 从库可以多台,但是需要考虑主从之间的传输速度

 

主库配置

首先修改 my.cof

vim /etc/my.cof

保证配置文件中有以下语句
server-id = 1    //master端ID号
log-bin = /usr/local/mysql/data/mysql-bin    //日志路径及文件名

以下语句按照需要配置
binlog-do-db = data    //需要同步的数据库(通过多行添加多个,如果没有次行,就是同步不允许以外所有的库)
binlog-ignore-db = mysql    //不需要同步的数据库(通过多行添加多个)
binlog-ignore-db = information_schema
//以上配置都是通过use table实现的,如果你使用database.table可能会存在问题;
//比如你忽略mysql库,但是你在data库中运行 UPDATE mysql.table SET xxx 词句依然不会被忽略

添加从库同步帐号权限,以及查看主库状态

mysql -uroot -p
mysql> GRANT REPLICATION SLAVE ON *.* TO '<slave_username>'@'<slave_ip>' IDENTIFIED BY '<slave_password>';
mysql> FLUSH PRIVILEGES;
mysql> SHOW MASTER STATUS; //查看主库日志状态

 

从库设置

首先修改 my.cof

vim /etc/my.cof

保证配置文件中有以下语句
server-id = 2    //slave端ID号,保证比master端ID大

添加从库同步帐号权限,以及查看主库状态

mysql -uroot -p
mysql> CHANGE MASTER TO
MASTER_HOST='<master_host>',
MASTER_USER='<master_username>',
MASTER_PASSWORD='<master_password>',
MASTER_LOG_FILE='<log_file>', //同步的起始日志文件名称
MASTER_LOG_POS='<log_position>'; //同步的起始Position
mysql> START SLAVE; //开始同步
mysql> SHOW SLAVE STATUS \G //查看从库状态

 

注意

1、如果您的主库日志文件一直存在就直接将起始日志文件定位到第一个日志的第一个位置就可以。
2、如果您的主库日志不完整,按请先锁定主库,然后将主数据库导入从数据库中。
3、一旦开始主从同步切勿使用“RESET MASER”来清除主库日志。

 

附:将主库文件备份到从库

锁定主库

mysql -uroot -p
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS; //将信息记录下来,要在从库配置中使用到

备份数据库文件

rsync -var /usr/local/mysql/data/ 10.10.1.11:/usr/local/mysql/data/

解锁主库

mysql -uroot -p
mysql> UNLOCK TABLES;

然后配置从库

 

附:删除主库日志

Mysql命令删除

mysql -uroot -p
mysql> SHOW BINARY LOGS;
mysql> PURGE BINARY LOGS TO 'mysql-bin.000003'; //上面取出的文件名称

Rm命令删除

cd /usr/local/mysql/data/
rm -f mysql-bin.000001
rm -f mysql-bin.000002
//切勿删除最后一个日志文件

通过MySql日志恢复丢失的数据

最郁闷的情况是没有备份数据库,但是因为自己的疏忽不小心把数据给删除了,特别是删除了积累很久的数据,那真是想死的心都会有。

不过如果你没有关闭MySql的日志功能,那么还是有补救的方法。前两天就遇到这样一件事,一朋友由于误操作导致公司网站数据几乎全丢,并且之前没有任何备份;我上服务器查看后发现,还好MySql日志全部在(mysql下的var目录下可以看到mysql-bin.0000XX这样的文件,这个就是MySql的日至,这个里面记录了所有之前运行过的SQL语句;没有查询语句)。

既然日志还在,那就算有得救,先将二进制的日志文件转成SQL语句,MySql提供了这样的方法“mysqlbinlog”;方法还提供了–start-date和–stop-date这两个选项来确定开始和结束时间,如果不带这两个参数将恢复日至文件中的所有SQL语句,当然-u,-p,-h,-P这样的基础参数一定会有;因为是误操作导致数据丢失,所以要将结束时间定义在误操作前,不然恢复出来的数据还是和现在一样。以下命令是从bin.000001这个日志中生成2012年1月1日0点到2012年8月1日0点之间的SQL语句,并且存入1.sql中,如果有多个日志文件那就要进行多次这个动作。

cd /usr/local/mysql/bin
mysqlbinlog -uroot -p --start-date="2012-01-01 0:00:00" --stop-date="2012-08-01 0:00:00" /usr/local/mysql/bin.000001 > 1.sql

通过这个命令导出的文件估计大家都能看懂了,就是一些带了注释的SQL语句;有了这个那直接将SQL导入到MySql中即可;这里注意下,因为日至中有那个时间段所有的建库,建表,添加,删除,修改语句,所以最好将数据库恢复到导出时设定的那个开始时间点,这样不会出现语句冲突的问题;当然如果是在已经忘记时间或者有很多数据库,那么就要自己去处理下所有的SQL语句,要么将需要恢复的有关SQL整理好或者解决导入过程中所有的冲突。

这里着重提醒,恢复前一定要对现在的MySql做全面备份,以免产生更大损失

具体的导入数据方法应该就不用过多说了,只要将你导出的所有sql文件全部运行一遍即可。

mysql -uroot -p < 1.sql