分库分表就是把数据打散，减小单库的压力 主从为了读写分离，大部分读多写少，单纯的主从的单一从多， 分库分表 主多从多 show slave status;为空 意味着不是从库，是不是主库不知道 show master status; 例如 91：3301 和 91：3308作为主库 #无限扩充的架构(数据库) ##大网站数据库会遇到什么问题 **大网站的大指什么?** 数据量大 访问量高 **两者必然有联系吗?** 博客、新闻 抢购、社交 **如何处理才能无限扩充?** 只需要运维添加机器和设备,软件架构不用修改 **单机性能** 提高机器性能 换用更强大的db类型 **无限扩充** 分表:纵向与横向分表 分表:业务分库与数据分库 扩充:只需要添加数据库与表的数量 讨论:如何无缝过渡扩充? 分散访问:多库读取 集中写入:数据一致性 多主库:写入比访问多的场景 扩充:只需要添加从库机器 讨论:写入过多在业务上如何处理? #MySql的binlog与备份还原 ##多实例运行 多个端口 mysqld_multi 步骤: 配置 mysqld_multi.cnf ~~~ /var/lib/mysql56/bin/mysqld_multi ‐‐defaults‐extra‐file=/etc/my_multi.cnf report /var/lib/mysql56/bin/mysqld_multi ‐‐defaults‐extra‐file=/etc/my_multi.cnf start ‐‐log=./test.log ~~~ ##binlog作用 **数据恢复** **主从同步** **log-bin log-slave-updates** ##binlog限制 性能慢1% loaddata不能恢复 ##MySQL启用binlog ##启用binlog my.cnf 添加 log_bin = mysql_bin 其他参数: max_binlog_size 分割文件,但一个事物不会写入2个日志文件 binlog-do-db、binlog-ignore-db 选择特定数据库写入binlog ##查看是否启用 a) 查看数据文件,mysql-bin.index b) mysql> show global variables like 'sql_log_bin'; ##MySQL处理binlog ###sql查看 ~~~ mysql> show binlog events in ‘mysql-bin.000002’; ~~~ ###mysqlbinlog查看 ~~~ mysqlbinlog mysql-bin.000006 --start-position=120 --stop-position=280 mysqlbinlog mysql-bin.000006 --start-datetime='2014-7-2 8:10:00' --stop- datetime='2014-07-03 00:00:00’ ~~~ ###处理binlog ~~~ mysql> flush logs; 产生新的binlog文件,什么情况下使用? mysql> reset master; 重置binlog,会删除所有的binlog mysql> PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 2 DAY); 删除指定 ~~~ ##利用binlog恢复数据库 ###恢复流程 禁用binlog(为什么) -> 利用mysqlbinlog导出恢复(管道或文件) -> 启用binlog mysql> set global sql_log_bin=0 在新的会话中,才生效,当前会话不生效 ###增量恢复场景 恢复全备 -> 确定恢复点 -> 导入binlog到指定时间或位置 ###删除数据恢复场景 恢复全备 -> 查找删除位置 -> 导入binlog到删除位置前 -> 从删除位置后恢复余下的 ###乱码问题及解决方案 大多数情况下,binlog 直接导出成 SQL 语句文件,再入库即可 但是在有中文字符的情况下,会出错。 办法: 1、set global sql_log_bin=0 先关闭 binlog 写入。 2、得到想要用的 SQL 语句 mysql -uroot -S /tmp/mysql_3303.sock -e "show binlog events in 'mysql- bin.000002'"|cut -f 6 > quanzhan.sql 3、去掉前面两行,Info 和 Server ver: 5.6.20-log, Binlog ver: 4 4、再 cat quanzhan.sql |awk '{printf "%s;\n",$0}' > quanzhan.sql.ok 这样为每个 SQL 语句最后都加上了 ; 号 5、再导入 mysql -uroot -S /tmp/mysql_3303.sock --default-character-set=utf8 < quanzhan.sql.ok 这样可以完成恢复。 ###通过 mysqldump 导出备份 注意 mysqldump 会锁表,当然可以加 参数消除锁 mysqldump备份不锁表:加上--lock-tables=false参数,如果是innodb,则加上--single- transcation比较好。 但是? 在备份时,不仅仅备份,留下当时的时间点和状态点 ~~~ mysql -uroot -S /tmp/mysql_3303.sock -e "stop slave; show slave status\G" > status.txt mysqldump --single-transaction --flush-logs --master-data=2 -uroot -S /var/lib/mysql56/data/mysql.sock -d quanzhan -p ~~~ ###数据恢复场景 恢复适合的地方,出了问题重建 还原做扩容 做切库 #单主多从架构实践 ##主从配置步骤 ###所有服务器启用binlog,配置server-id, 在my.cnf 中开启 log-bin ###主库创建同步账号,配置同步位置 ~~~ grant ALL PRIVILEGES on db.* to ‘user’@‘ip' identified by ‘ pass' with grant option; ~~~ ###从库设置同步信息,启用从库,并查看同步状态 ~~~ CHANGE MASTER TO XXX ~~~ ###测试数据库,注意防火墙 ###程序需要处理的问题:数据同步有时间差!!! ##主从配置 ###所有服务器 启用binlog,配置文件设置log-bin=mysql_bin 每台服务器设置server-id,所有机器不能重复 配置文件里设置server-id=1,2,3.... ###主库 创建同步账号: `GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* to repl@'%' IDENTIFIED by 'replpass';` 记录同步位置: `show master status;` 记下File和Position,从库从这个位置开始同步 ###创建同步账号: GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* to repl@'%' IDENTIFIED by 'replpass'; 记录同步位置: show master status; 记下File和Position,从库从这个位置开始同步 ###每台从库 设置同步信息: `change master to master_host='192.168.2.110', master_user='repl', master_password='replpass',MASTER_LOG_FILE='mysql- bin.000003',MASTER_LOG_POS=338;` 启用同步: `start slave;` 查看同步状态(Slave_IO_Running和Slave_SQL_Running都为yes即正常): `show slave status\G` ###从库再做主库 1、从库再做主库,可以做扩容 2、可以更大规模地扩充节点 3、开启 log_slave_updates ###切库扩容 1、第一步,规划好扩容,要分成几组 2、第二步,通过备份,恢复数据库 3、第三步,导入备份的修改时间节点,导入数据 4、第四步,同步数据,跟上数据库 5、第五步,修改配置库 6、第六步,重新加载配置 ###数据测试 在主库写入数据,查看每个从库的数据有没有同步过来 如果同步状态有问题,注意防火墙,打开3306等端口 注意通过show slave status 、show master status 查看状态 #多主多从  ##环形多点配置(node A)  ~~~ # node A - water [mysqld] server-id = 10 log-bin = mysql-bin log-slave-updates replicate-same-server-id auto_increment_increment auto_increment_offset master-host master-user master-password report-host = 0 = 10 = 1 = earth.stardata.it = nodeAuser # node D = nodeApass = nodeA ~~~ ##环形多点配置(node B) ~~~ # Node B - air [mysqld] server-id = 20 log-bin = mysql-bin log-slave-updates replicate-same-server-id auto_increment_increment auto_increment_offset master-host master-user master-password report-host = 0 = 10 = 2 = water.stardata.it = nodeBuser # node A = nodeBpass = nodeB ~~~ ##环形多点特点 ###优点: 适合写入量特别大的情况 服务器均衡压力 故障处理简单,只需要当机的下一个环点切换到上一个环点,不影响其他机器 ###缺点 机器多时,同步时间慢 只适合少部分情况,大部分网站都是读取为主的