MYSQL主从库搭建(原创)

longpan

MYSQL主从库搭建(原创)　　

　　一、  单主库配置
　　1.1环境说明
　　本节讲述的环境为一个主库，两个从库，具体环境如下。
　　1.1.1Linux version

　　Red Hat Enterprise Linux Server>　　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
　　1.2  master 服务器配置
　　1.2.1 创建同步账号

　　mysql> grant replication slave on . to 'rep'@'192.168.212.%'>　　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 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>　　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;