文档章节

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
 

© 著作权归作者所有

共有 人打赏支持
赵-猛
粉丝 5
博文 756
码字总数 458977
作品 0
深圳
技术主管
MySQL Group Replication 学习(部署篇+排错篇)

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

雪隐千寻
2017/10/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
5.7.17 GR(group replication)

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

她二哥
2017/03/20
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

没有更多内容

加载失败,请刷新页面

加载更多

简单谈一谈压力测试

最近,在做API的压力测试,趟了不少坑,然后呢,简要记录一下。 压测前需要准备的一些事 拿到API文档不要立马上手,先基准测试,就是执行一次接口测试,至少要压这个接口,要先熟悉一下他的参...

浮躁的码农
33分钟前
0
0
PHP 错误调查

一.定义:PHP错误是由PHP无法读懂执行的代码引起的错误。 二:错误日志 error log 1.在php.ini 里设置 log_errors = on, log文件位置 error_log=/tmp/php_errors.log 2.代码里设置ini_set('...

忙碌的小蜜蜂
36分钟前
0
0
knn算法

import numpy as np def CreateDateSet(): group = np.array([[1.0, 2.0], [1.2, 0.1], [0.1, 1.4], [0.3, 3.5]]) labels = ['A','A','B','B'] return group,labels coding:utf-8 from numpy......

南桥北木
37分钟前
0
0
自己手写一个 SpringMVC 框架

前端框架很多,但没有一个框架称霸,后端框架现在Spring已经完成大一统.所以学习Spring是Java程序员的必修课. Spring 框架对于 Java 后端程序员来说再熟悉不过了,以前只知道它用的反射实现的,...

别打我会飞
今天
2
0
01-《Apache Tomcat 9》之文件索引

《Apache Tomcat 9》是《看Apache官方文档学英语》的第一个专栏!让我们一起在看文档的过程中学英语,在学英语的过程中夯实技术! Documentation Index - 文件索引 Introduction - 介绍 This...

飞鱼说编程
今天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部