MYSQL主从库搭建(原创)

                     MySQL主从库搭建(原创)

一、 单主库配置
1.1环境说明
本节讲述的环境为一个主库,两个从库,具体环境如下。
1.1.1Linux version
Red Hat Enterprise Linux Server release 6.4 (Santiago)
1.1.2 mysql version
5.1.73
1.1.3 master 服务器ip 192.168.212.134, slaver 服务器IP 192.168.212.142 ,slaver 服务器IP 192.168.212.143

目前创新互联建站已为近1000家的企业提供了网站建设、域名、虚拟主机绵阳服务器托管、企业网站设计、鸠江网站维护等服务,公司将坚持客户导向、应用为本的策略,正道将秉承"和谐、参与、激情"的文化,与客户和合作伙伴齐心协力一起成长,共同发展。

1.2 master 服务器配置
1.2.1 创建同步账号
mysql> grant replication slave on . to 'rep'@'192.168.212.%' identified by 'rep';
mysql> flush privileges;
说明: . 代表所有的数据库的所有表,也可指定对某个数据库的所有表进行同步,例如testdb.*
192.168.212.%代表对192.168.212网段都可以连接master 数据库
1.2.2 打开master 数据库的binlog功能
编辑/etc/my.cng
在mysqld模块中添加bin-log参数。例如bin-log=master
重启数据库
#service mysqld restart
1.2.3 备份数据库
 为了保持一致性,检查binlog。
mysql>show master status;
当前的语句节点在mysqlbin.000002 ,位置在106,这个在slaver数据库中恢复起始位置
mysql>show master logs;

 备份全库
为了保持数据一致,对于非innodb的库表需要锁库进行备份,可以通过mysql命令mysql>flush table with read lock;锁数据库(unlock tables;解锁),此命令锁表的时间是通过系统参数wait_time和interactive_timeout控制,缺省时间是28800秒。如果此命令执行窗口关闭,锁表也失效。
也可以在mysqldump中加参数-x锁表,如下命令# mysqldump -uroot -B -A -x --events –master-data=2|gzip >/root/mysql_all.gz
对于innodb存储引擎的表可以在mysqldump中加参数--single-transaction在备份时快照来保持数据一致,如下命令# mysqldump -uroot -B -A --single-transaction --events --master-data=2|gzip >/root/mysql_all.gz
将备份文件传至slaver 服务器,如下:
Scp /root/mysql_all.gz root@192.168.212.141:/home/mysql/

1.3 Slaver 服务器配置
本部分只对192.168.212.142进行配置,192.168.212.143配置与此相同。
1.3.1 将备份数据导入数据库,如下:
$gzip –dv /root/mysql_all.gz
$mysql –uroot 1.3.2 设置恢复点
Mysql>change master to
master_host='192.168.212.134',
master_port=3306,
master_user='rep',
master_password='rep',
master_log_file='mysqlbin.000002',
master_log_pos=106;
说明:
把备份文件中change master 命令master_log_file,master_log_pos值填入以上命令对应位置。如果备份时设置--master-data=1(2会把增量位置信息注释),增量位置信息会写入备份文件,备份文件导入从数据库时就会把位置信息一起导入,这样在从库执行change master 时就不再需要写master_log_file和master_log_pos。
1.3.3 启动同步
Mysql>start slave;
1.3.4 检查主从状态
Mysql>show slave status\G;
mysql> show slave status\G;
1. row
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.212.134
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqlbin.000004
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000004
Relay_Log_Pos: 250
Relay_Master_Log_File: mysqlbin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 550
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: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
ERROR:
No query specified
说明:如果Slave_IO_Running和 Slave_SQL_Running都是yes 说明开始同步,Seconds_Behind_Master说明从库落后主库的时间(秒)。

二、多主库配置
多主库模式包含超过一个以上的主库,主库间数据库互相同步。Master1、Master2 分别代表两个主库。
2.1 参数配置
Mater1:
auto_increment_offset=1 #解决主键自增变量冲突.自增初始位置,此例为1
auto_increment_increment=2 #解决主键自增变量冲突.自增间隔为2,例如1、3、5
log_slave_updates
Mater2:
auto_increment_offset=2 #解决主键自增变量冲突。自增初始位置,此例为2
auto_increment_increment=2 #解决主键自增变量冲突。自增间隔为2,例如2、4、6
log_slave_updates

2.2 把master1的数据备份,导入mater2数据库
参照单主库的备份与导入章节。

2.3 master1启动同步
Mysql> change master to
master_host='192.168.212.141',
master_port=3306,
master_user='rep',
master_password='rep';
mysql>start slave;

2.4 master2启动同步
Mysql> change master to
master_host='192.168.212.141',
master_port=3306,
master_user='rep',
master_password='rep';
mysql>start slave;

三、Binlog日志的自动删除
可以通过系统参数expire_logs_days配置Binlog日志保留天数。此参数默认值为0,
例如expire_logs_days=10 ,表示保留10天。

四、读写分离
为了防止应用对从库进行DML操作而参数主从不一致,可以在从库添加read-only参数,使从库只读(super,或all privileges权限用户不受此限制)。

五、从库切换为新主库
5.1选择同步最新的从库为新主库。
5.1.1 查看各从库如果如下红框显示说明同步一致。

5.1.2 查看两台从库的master.info文件,对比不同从库红色部分判断是否最新,选择logbin file和pos最大的为最新,此例为logbinfile:000006,pos:106。
#cat master.info
15
mysqlbin.000006
106
192.168.212.134
rep
rep
3306
60
0
5.2 把所有从库relaylog内容都导入库中。
在每个从库执行stop slave io_thread;show processlist\G;直到看到Has read all relay log;表示从库导入完毕。
mysql> stop slave io_thread;
mysql> show processlist\G

5.3 新主库操作

          新主库的检查

 在新主机的数据目录下,删除master.info,relay-log.info 两个文件。
 确认mysql.user表满足应用和其它从库的连接需求。
 确认去掉了read-only、log_slave_updates系统参数。
 确认打开了binlog功能。
 检查原主库是否还有没同步到新主库的binlog,不没同步到新从库的binlog通过mysqlbinlog工具生成可读命令文档,导入新主库。

在同步最新的从库上执行如下语句,把此从库改成新主库,语句如下。
Mysql>stop slave; --停止slave服务
Mysql>reset master; --进行主库binlog初始化工作
#service mysqld restart 重启数据库
5.4 其它从库操作
Mysql>stop slave;
Mysql>change master to master host=’192.168.212.142’; --指向新主库
Mysql>start slave;
Mysql>show slave status\G;


本文题目:MYSQL主从库搭建(原创)
链接URL:http://pwwzsj.com/article/jjspsi.html