大型项目mysql5.6主从搭建(一主三从)

开源中国社区最新推荐博客 / 2018-11-03 11:50

    本篇文章记录博主所负责的一个大型项目最近一次遇到主从失效,重新搭建的整个过程。博主可是干了几个小时才搭建好,但是小伙伴们不要怕,其实搭建主从本身是非常简单的;主要还是数据量十几个G还原数据库花费太多时间。

    首先,因为博主负责的该项目服务器几十台的规模,所以批量操作博主都已经做成了自动化脚本。如果大家要想看脚本请到前面博主的文章查看:大型项目linux自动化版本发布脚本(shell)之tomcat、nginx服务脚本

    以下是详细步骤:

   一、停止web应用,接口服务器,mycat

#执行nginx集群下线脚本(主要解决几台nginx转发问题)  sh n_downline_ssh.sh  #停止所有web服务器tomcat  sh t_kill.sh  #停止接口服务器tomcat  Kill -9 接口服务器tomcat进程号  #停止mycat服务  ./mycat stop

    二、备份主数据库

#备份命令  nohup mysqldump -uxxx -pxxxxx   xxery_wcc_test > /opt/xxery/dbbackup/xxery_20181031.sql 1>myout.out 2>/dev/null &  #由于博主主了脚本执行,此处就直接运行脚本备份  sh t_ump_database_backup.sh   

    三、通知项目公司运维部门的人上传sql到几台从数据库所在服务器(人家是内网,速度快)

    四、把三台数据库主从挂载去掉

#需要先分别登录几台从库上mysql命令行  stop slave;  reset slave  all;  show slave status;

    五、把三台从数据库的xxery_wcc库删掉,重新创建数据库

#从库sql命令行执行  drop database if exists `xxery_wcc`;  create database `xxery_wcc` DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;  

    六、设置几台从数据库参数

#设置一下参数来提高sql执行速度  set global net_buffer_length=1000000;(经测试无效)  set global max_allowed_packet=1000000000;(经测试无效)  //目标数据库(从库),查看参数设置结果  show variables like 'max_allowed_packet';  show variables like 'net_buffer_length';    #上面的设置可能会没生效,可执行下面登录时设置  mysql -u rxxx -p -e "set global net_buffer_length=1000000; set global max_allowed_packet=1000000000;"    #设置关闭一些数据库校验,提高sql执行速度  SET foreign_key_checks = 0;  SET UNIQUE_CHECKS = 0;  SET AUTOCOMMIT = 0;  SET GLOBAL log_bin_trust_function_creators = 1; 

    七、重启主库,在主库执行grant命令授权从库挂载 mysql命令:show master status; 记录posion和bin块

#将mysql锁住不写数据  flush tables with read lock;  #重启mysql  service mysql restart;      #主数据库配置授权从库的连接权限  grant replication slave on *.* to 'xxx'@'10.x.x.xx7' identified by 'xxxxxxxxxx@xxx~!@';  grant replication slave on *.* to 'xxx'@'10.x.x.xx1' identified by 'xxxxxxxxxx@xxx~!@';  grant replication slave on *.* to 'xxx'@'10.x.x.xx1' identified by 'xxxxxxxxxx@xxx~!@';  #查看mysql pos位置和bin文件块  show master status;  #执行结果  +------------------+----------+--------------+------------------+-------------------+  | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |  +------------------+----------+--------------+------------------+-------------------+  | mysql-bin.000094 |      715 | xxery_wcc    | mysql,test       |                   |  +------------------+----------+--------------+------------------+-------------------+  

    八、在三台从数据上执行备份的sql文件还原数据库,重启mysql

#mysql>    use xxery_wcc;  source /opt/xxery/xxery_wcc20181031_214314.sql;    --需手动复制函数视图过来(函数、视图同步)

    九、将从库校验参数修改回来

SET foreign_key_checks = 1;  SET UNIQUE_CHECKS = 1;  SET AUTOCOMMIT = 1;

    十、在从库上执行命令配置主从,并启动:start slave;

#mysql命令  #关闭从库连接  stop slave;  change master to master_host='10.x.x.xxx',master_port=3x06,master_user='xxx',master_password='xxxxxx@xxxx~!@',master_log_file = 'mysql-bin.000094',master_log_pos = 715;  #开启从库连接同步  start slave;  #在主库中执行主库,检查从库是否挂载成功  show processlist G;  #上一句执行效果  *************************** 1. row ***************************       Id: 19     User: root     Host: 10.x.x.xxx:52739       db: NULL  Command: Binlog Dump     Time: 273    State: Master has sent all binlog to slave; waiting for binlog to be updated     Info: NULL  *************************** 2. row ***************************       Id: 20     User: root     Host: 10.x.x.xxx:56306       db: NULL  Command: Binlog Dump     Time: 158    State: Master has sent all binlog to slave; waiting for binlog to be updated     Info: NULL  *************************** 3. row ***************************       Id: 21     User: root     Host: 10.x.x.xxx:36930       db: NULL  Command: Binlog Dump     Time: 78    State: Master has sent all binlog to slave; waiting for binlog to be updated     Info: NULL  *************************** 4. row ***************************       Id: 22     User: root     Host: localhost       db: NULL  Command: Query     Time: 0    State: init     Info: show processlist  4 rows in set (0.34 sec)

    十一、检查从库状态 show slave status. 测试同步是否成功(主库手动加数据看从库是否同步)

#主库执行,解锁主库  unlock tables;  #从库执行,查看从库状态  show slave statusG;

    十二、修改mycat配置,重启Mycat(因为从库坏后,我把连接配置修改回了单库支撑线上业务,此处需要修改回读写分离)

cd /opt/xxxx/mycat/conf/  vi  schema.xml  cd /opt/xxxx/bin  ./mycat start;
#修改前(读写配置同一台主库):  <?xml version="1.0"?>  <!DOCTYPE mycat:schema SYSTEM "schema.dtd">  <mycat:schema xmlns:mycat="http://org.opencloudb/">          <schema name="xxery_wcc" checkSQLschema="false" dataNode="dn1">          </schema>          <dataNode name="dn1" dataHost="localhost1" database="xxery_wcc" />          <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"                  writeType="0" dbType="mysql" dbDriver="native" switchType="2"  slaveThreshold="100">                  <heartbeat>show slave status</heartbeat>                  <!-- can have multi master write hosts -->                  <writeHost host="wilon-centos5" url="10.x.x.xxx:3x06" user="xxx"                          password="xxxxx@xxx~!@">                  <!-- can have multi slave read hosts -->                  <readHost host="wilon-centos5" url="10.x.x.xxx:3x06" user="xxx"                          password="xxxxx@xxx~!@" />                  </writeHost>          </dataHost>  </mycat:schema>  
#修改后:  <?xml version="1.0"?>  <!DOCTYPE mycat:schema SYSTEM "schema.dtd">  <mycat:schema xmlns:mycat="http://org.opencloudb/">          <schema name="xxery_wcc" checkSQLschema="false" dataNode="dn1">          </schema>          <dataNode name="dn1" dataHost="localhost1" database="xxery_wcc" />          <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"                  writeType="0" dbType="mysql" dbDriver="native" switchType="2"  slaveThreshold="100">                  <heartbeat>show slave status</heartbeat>                  <!-- can have multi master write hosts -->                  <writeHost host="wilon-centos5" url="10.x.x.xxx:3x06" user="xxx"                          password="xxxxx@xxx~!@">                  <!-- can have multi slave read hosts -->                  <readHost host="wilon-centos6" url="10.x.x.xxx:3x06" user="xxx"                          password="xxxxx@xxx~!@" />                  <readHost host="wilon-centos7" url="10.x.x.xxx:3x06" user="xxx"                          password="xxxxx@xxx~!@" />                  <readHost host="wilon-centos8" url="10.2.4.xxx:3x06" user="xxx"                          password="xxxxx@xxx~!@" />                  </writeHost>          </dataHost>  </mycat:schema>  

    十三、重启web,接口服务器

sh t_restart_delay_new.sh 60s

    十四、上线nginx

sh n_go_online_ssh.sh

    十五、测试线上业务(完成)

     最后总结,mycat主从读写分离特别方便简单。以上是博主本次文章的全部内容,如果大家觉得博主的文章还不错,请点赞;如果您对博主其它服务器技术或者博主本人感兴趣,请关注博主博客,并且欢迎随时跟博主沟通交流。

223916_bL9y_2663968.jpg

Shared via Inoreader