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主从库搭建(原创)
文章位置：http://ybzwz.com/article/jjspsi.html