文档章节

MySQL(Replication-MS)

赵-猛
 赵-猛
发布于 2016/07/03 01:15
字数 538
阅读 14
收藏 0

mysql cluster 

高可用集群(High Availability Cluster)
   双master+keepalived
负载均衡集群(Load Balance Cluster)
科学计算集群(High Performance Computing Cluster)

master: .\bin\mysqld --defaults-file=.\my.ini 
create directory: D:/Server/mysql/mysql-master/tmp and D:/Server/mysql/mysql-master/data 

[mysqld] 
server-id=1 
#replicate-same-server-id=0 
basedir = D:/Server/mysql/mysql-master  
datadir = D:/Server/mysql/mysql-master/data 
tmpdir = D:/Server/mysql/mysql-master/tmp 
port = 3310 
log-bin=master-bin 
log-bin-index=master-bin.index 
join_buffer_size = 128M 
sort_buffer_size = 2M 
read_rnd_buffer_size = 2M 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
[client] 
port = 3310 


master-client: .\bin\mysql -h localhost -P 3310  -u root

grant replication slave, replication client on *.* to repl@'localhost' identified by 'password';

 

slave: .\bin\mysqld --defaults-file=.\my.ini 
create directory: D:/Server/mysql/mysql-slave/tmp and D:/Server/mysql/mysql-slave/data
change the UUID(should be different with the master) in auto.cnf

[mysqld] 
server-id=2 
#replicate-same-server-id=0 
innodb_buffer_pool_size = 128M 
basedir = D:/Server/mysql/mysql-slave 
datadir = D:/Server/mysql/mysql-slave/data 
tmpdir = D:/Server/mysql/mysql-slave/tmp 
port = 3311 
log_bin           = slave-bin
relay-log = slave-relay-bin 
relay-log-index = slave-relay-bin.index 
log_slave_updates = 1
skip-name-resolve
slave-skip-errors = all
read_only         = 1
join_buffer_size = 128M 
sort_buffer_size = 2M 
read_rnd_buffer_size = 2M 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
##master-host     =   localhost
##master-user     =   repl
##master-password =   password
##master-port     =  3310
[client] 
port = 3311 


slave-client: .\bin\mysql -h localhost -P 3311 -u root

stop slave
change master to master_host='localhost',master_port=3310,master_user='repl',master_password='password', master_log_file='master-bin.000001',master_log_pos=0; 
start slave

 

some script:

  show master status
  show master logs
  show processlist
  reset master

  show slave status
  show slave logs
  show processlist
  reset slave

 

replicate flow:
   slave io process connect to master
   master io process write bin-log to slave
   slave io process write sql to relay-log
   slave sql process parse relay-log and execute sql

replicate mode:
   statement level
   row level
   mixed level

replicate slave fail mode:
    slave_exec_mode = strict: default
    slave_exec_mode = idempotent: 忽略duplicate-key, no-key-found和一些其他错误

replicate threads:
    master host: IO process
    slave host:IO process + SQL process


replicate config file:
    server-id = 2
    replicate-wild-ignore-table=mysql.%
    log-slave-updates
    replicate-ignore-db = mysql
    replicate-ignore-db = test
    binlog-ignore-db = mysql
    binlog-ignore-db = test
    replicate-do-db = xx
    binlog-do-db = xx
    replicate-do-db = xx
    binlog-do-db = xx
    replicate-do-db = xx
    binlog-do-db = xx
    replicate-do-db = xx
    binlog-do-db = xx
    replicate-do-db = xx
    binlog-do-db = xx


binlog skip error:
  使用SET GLOBAL SQL_SLAVE_SKIP_COUNTER 命令跳过失败的SQL
    slave stop
    set global sql_slave_skip_counter=1
    slave start
  (or slave_skip_errors = 1062 in my.cnf)

binlog variables:
  show variables like '%bin%';

binlog files:
  ll /data/mysql/binlog*
  or sudo cat /data/mysql/binlog.index

show binlog:
  show binary logs;
  show master logs

show status:
  show master status\G;
  show stalve status\G;

show binlog event:
  show binlog events;
  binlog events in 'binlog.000178';
  binlog events in 'binlog.000178' from 92562567 limit 100;

show binlog sql:
  sudo mysqlbinlog  /data/mysql/binlog.000178|grep -C 5 'qpfp.com.cn2017-01-07 04:27:42'
  sudo mysqlbinlog  --start-datetime='2013-09-10 00:00:00' --stop-datetime='2013-09-10 01:01:01' --database=zm /data/mysql/binlog.000178 |grep -A 5 'qpfp.com.cn2017-01-07 04:27:42'
  sudo mysqlbinlog  --start-postion=107 --stop-position=1000 --database=zm /data/mysql/binlog.000178 |grep -B 5 'qpfp.com.cn2017-01-07 04:27:42'

limit logs days:
  set global expire_logs_days=3


relaylog:
  set relay_log_recovery=1

slave reset recovery:
  slave stop
  change master to master_log_file='yyyy-relay-bin.nnnn', master_log_pos=m
  slave start
 

© 著作权归作者所有

共有 人打赏支持
上一篇: Java(OpenGL)
赵-猛
粉丝 6
博文 759
码字总数 462462
作品 0
深圳
技术主管
私信 提问
MySQL Group Replication 学习(部署篇+排错篇)

写在前面:之前一直用mariadb 版本,mariadb 集成了galera插件,实现pxc部署较为简单。官方在5.7推出了MySQL Group Replication,之前因为时间原因,一直没有时间搭建,今天也是抱着学习对比...

雪隐千寻
2017/10/20
0
0
5.7.17 GR(group replication)

此搭建试验为5.7GR刚出三天左右搭建的,可能现在又有变化,最近京东出了个搭建文档可以瞅一下 多台服务器相比单台服务器搭建 group replication 需要注意几个点: 1、各个服务器之间需要添加解...

她二哥
2017/03/20
0
0
MySQL之GroupReplication初体验

https://dev.mysql.com/doc/refman/5.7/en/group-replication.html 基本的架构一主两从 master:192.168.100.41 slave:192.168.100.42 slave:192.168.100.43 下载软件官方网站 mysql-comm......

andylhz
2017/02/13
0
0
浅析MySQL主从复制技术(异步复制,同步复制,半同步复制)

导 读 作者:赵黎明,微信号:zhaoliming856132 知数堂MySQL实战/优化课程第12期学员 欢迎大家交流,拍砖~ 一、Preface As we all know,there're three kinds of replication in MySQL nowad...

老叶茶馆
09/05
0
0
数据库Mysql5.7 MGR集群的搭建

最近看了一下mysql5.7的MGR集群挺不错的,有单主和多主模式,于是乎搭建测试了一下效果还不错,我指的不错是搭建和维护方面都比较简单。网上绝大多数都是单主模式,当然我这里也是,为了加深...

English0523
2017/09/04
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Ubuntu18.04 安装MySQL

1.安装MySQL sudo apt-get install mysql-server 2.配置MySQL sudo mysql_secure_installation 3.设置MySQL非root用户 设置原因:配置过程为系统root权限,在构建MySQL连接时出现错误:ERROR...

AI_SKI
今天
3
0
3.6 rc脚本(start方法) 3.7 rc脚本(stop和status方法) 3.8 rc脚本(以daemon方式启动)

3.6-3.7 rc脚本(start、stop和status方法) #!/usr/bin/env python# -*- coding: utf-8 -*-# [@Version](https://my.oschina.net/u/931210) : python 2.7# [@Time](https://my.oschina.......

隐匿的蚂蚁
今天
3
0
Cnn学习相关博客

CNN卷积神经网络原理讲解+图片识别应用(附源码) 笨方法学习CNN图像识别系列 深度学习图像识别项目(中):Keras和卷积神经网络(CNN) 卷积神经网络模型部署到移动设备 使用CNN神经网络进行...

-九天-
昨天
5
0
flutter 底部输入框 聊天输入框 Flexible

想在页面底部放个输入框,结果键盘一直遮住了,原来是布局问题 Widget build(BuildContext context) { return Scaffold( appBar: AppBar( title: Text("评论"), ...

大灰狼wow
昨天
4
0
Kernel I2C子系统

备注:所有图片来源于网络 1,I2C协议: 物理拓扑: I2C总线由两根信号线组成,一条是时钟信号线SCL,一条是数据信号线SDA。一条I2C总线可以接多个设备,每个设备都接入I2C总线的SCL和SDA。I...

yepanl
昨天
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部