当前位置: 主页 > 数据库 > Mysql >

mysql 5.1.61主从复制环境的部署

时间:2013-05-25 05:37来源:linux系统运维 作者:小编1000 点击:
使用mysql 5.1.61稳定版搭建主从复制功能,此版本比较多公司使用,如果对业务没有其它需求,用这个版本即可
欢迎大家分享自己的文档,请点击查阅:分享方法,Linux系统运维
如果你喜欢这文章,可以点击文章结尾处百度分享,分享到你的各种社区收藏,或推荐给朋友……


环境:
主服务器IP:192.168.1.56
从服务器IP:192.168.1.57
系统为:centos 6.2

主库:
修改主服务器my.cnf配置文档:
server-id = 1
log-bin = /var/mysql/mysql-bin

重启mysql服务:
service mysqld restart

在主服务器登陆mysql,创建从同步帐号:
grant replication slave on *.* to 'rep'@'192.168.1.%' identified by '123789';

锁表只读:
flush tables with read lock;

查看主库状态:
SHOW MASTER STATUS;

记录二进制日志文件名和位置偏移点:
+--------------------------+----------+--------------+------------------+
| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------------+----------+--------------+------------------+
mysql1-bin.000003 | 23242673 |              |                  |
+--------------------------+----------+--------------+------------------+

导出主库数据:
mysqldump -uroot -p -A -B >mysql-bak_$(date +%F).sql

然后在从库导入数据库
mysql -uroot -p<mysql_bak_2013-01-18.sql

解锁主库:
unlock tables;

从库操作:
修改从服务器my.cnf配置文档:
server-id = 2
log-bin = /var/mysql/mysql-bin

重启mysql服务
service mysqld restart

登陆从库配置同步参数:
mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.1.56',   
    -> MASTER_PORT=3306,
    -> MASTER_USER='rep', 
    -> MASTER_PASSWORD='123789',
    -> MASTER_LOG_FILE='mysql-bin.000003',
-> MASTER_LOG_POS=23242673;
Query OK, 0 rows affected (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

也可用多行插入:
cat |mysql -uroot -p'111111' -S /var/mysql/mysql.sock<< EOF
CHANGE MASTER TO  
MASTER_HOST='192.168.1.56',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='123789',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=23242673;
EOF


显示是否主从复制成功:
SHOW SLAVE STATUS\G

如果显示以下2个YES,则为配置成功:
Slave_IO_Running: Yes   #负责从库去主库读取BINLOG日志,并写入从库的中继日志中
Slave_SQL_Running: Yes  #负责读取并执行中继日志中的BINGLOG,转换SQL语句后应用到数据库汇总





错误总汇:
当提示IO为NO时,则可看以下错误提示:
                Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
这是配置文件server-id = 1没有更改,和主服务器ID号是一致的,更改从服务器的ID号后(如:server-id = 3),重启MYSQL就好了

相关mysql技术技巧概览

配置忽略权限库同步参数

  binlog-ignore-db=information_schema
  binlog-ignore-db=mysql

主从复制故障解决

show slave status;报错:Error xxx dosn't exist
且show slave status\G:
Slave_IO_Running: Yes
Slave_SQL_Running: NO
Seconds_Behind_Master: NULL
解决方法:
stop slave;
set global sql_slave_skip_counter =1 ;
start slave;
这样Slave就会和Master去同步,主要看点:
Slave_IO_Running:  Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master是否为0,#0表示已经同步状态
提示:
set global sql_slave_skip_counter=n; #n取值 >0 忽略执行N个更新

让mysql slave记录binlog方法

在从库的my.cnf中加入如下参数
log-slave-updates
     log-bin=mysql3307-bin
     expire_logs_days = 7
应用场景:级联复制或从库做数据备份。

严格设置从库只读

read-only的妙用,见相关的文档。

将没有使用索引的查询也记录下来,只需添加以下:
log-queries-not-using-indexes

生产环境如何确保从库只读?

1) mysql从服务器中加入read-only参数或者在从服务器启动时加该参数。
read-only
2)忽略mysql库及information_schema库同步。
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
3)授权从库用户仅授权select权限。
4)授权案例:
a.生产环境主库用户的授权:
GRANT SELECT, INSERT, UPDATE, DELETE ON `blog`.* TO 'master'@'192.168.1.%' identified by '111111';
b.生产环境从库用户的授权:
GRANT SELECT ON `blog`.* TO 'slave'@'1192.168.1.%' identified by '111111';
说明:这里表示给192.168.1.0的用户slave管理blog数据库的所有表(*表示所有表)只读权限(SELECT),密码为111111。
       当然从库除了做SELECT的授权外,还可以加read-only等只读参数。

生产环境读写分离的账户设置建议

给开发人员的读写分离用户设置
主库(尽量提供写服务):master  111111  ip:192.168.1.56  port 3306
从库(仅提供读服务):  slave    111111  ip:192.168.1.57  port 3306
提示:除了IP没办法修改之外,我们尽量为开发人员提供方便。
此处设置密码111111是方便做实验,在线上的话,一定要用复杂的密码。




错误问题:
 

Slave_SQL_Running: No mysql同步故障解决方法

错误现状:
Linux系统运维
mysql 同步时遇到错误,不要慌张,镇定,是第一位:
首先,说明:
Slave_IO_Running:连接到主库,并读取主库的日志到本地,生成本地日志文件
Slave_SQL_Running:读取本地日志文件,并执行日志里的SQL命令
这个错误是出现在我重启电脑之后。从以上图片来看,我遇到的错误是第二条,但至少说明了连接主库,并读取主库的日志到本地,生成本地日志文件这个过程是正确的。
从网上查找解决方案:
解决办法一、
Slave_SQL_Running: No
1.程序可能在slave上进行了写操作
2.也可能是slave机器重起后,事务回滚造成的.(我的就是这个错误)

一般是事务回滚造成的:
解决办法:
mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;
第一次操作完毕之后,输入命令showslave status\G ,发现还是没变,又操作了一次,就好了;
slave启动失败问题总结:
从服务器配置的结果一定是:Slave_IO_Running 与 Slave_SQL_Running 状态都要为Yes
1:Change master to
如果从库的Slave未启动,Slave_IO_Running为NO。
可能是主库是的master的信息有变化,
查看主库show master status;
记录下File,Position字段,假设为‘mysql-bin.000004’,98;
在从库执行:
mysql>stop slave;
mysql>change master to master_log_file='mysql-bin.000004',master_log_pos=98;
mysql>start slave;
2:SET global sql_slave_skip_counter=n;
如果从库的slave_sql_running为NO。
Err文件中记录:
Slave:Error "Duplicate entry '1' for key 1" on query.....
可能是master未向slave同步成功,但slave中已经有了记录。造成的冲突可以在从库上执行
set global sql_slave_skip_counter=n;
跳过几步。再restart slave就可以了。
3:发现mysql slave服务器经常因为一些特殊字符或者符号产生的更新语句报错,整个同步也会因此而卡在那,最初的办法只是手动去出错的机器执行下面三条SQL语句,跳过错误即可。
mysql>slave stop;
mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql>slave start;

本人多次遇到从数据库的同步进程自动停掉的问题,有时简单通过slave stop,slave start即可解决。有时slave start启动后又会自动停掉,这时使用 change master重设主数据库信息的方式解决了问题。

转载请注明linux系统运维
http://www.linuxyw.com/linux/Mysql/20130420/108.html

------分隔线----------------------------
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
验证码: 点击我更换图片