文档章节

MySQL主从

雁南飞丶
 雁南飞丶
发布于 2014/09/22 18:03
字数 2338
阅读 43
收藏 0

 二进制日志
事务日志
错误日志
一般查询日志
中继日志
慢查询日志

二进制日志:
    数据目录
    mysql-bin.XXXXXX
        滚动:达到最大上限,flush logs,服务器重启
        mysql> PURGE

    二进制日志的格式:
        statement
        row
        mixed

    mysql-bin.index: 二进制日志文件索引文件

    mysql> SHOW MASTER STATUS;
    mysql> SHOW BINARY LOGS;
    mysql> SHOW BINLOG EVENTS IN "file";

    event:
        timestamp
        position, offset,OPERATION, server-id


即时点还原:
    MySQL: tx1

    MySQL隔离级别:
        READ-UNCOMMITTED
        READ-COMMITTED
        REPEATABLE-READ

        SERIALIZABLE

复制的作用:
    辅助实现备份
    高可用
    异地容灾
    scale out:分摊负载

读写分离:
    mysql-proxy
    amoeba

数据拆分:
    cobar:




master: slave
    1-->N
slave: master
    1-->N X

一个从只能属于一个主服务器

MySQL 5.5:
MySQL 5.6: gtid, multi-thread replication

配置MySQL复制基本步骤:

一、master

1、启用二进制日志
log-bin = master-bin
log-bin-index = master-bin.index

2、选择一个惟一server-id
server-id = {0-2^32}

3、创建具有复制权限的用户
REPLICATION SLAVE
REPLICATION CLIENT

二、slave

1、启用中继日志
relay-log = relay-log
relay-log-index =

2、选择一个惟一的server-id
server-id = {0-2^32}

3、连接至主服务器,并开始复制数据;
    mysql> CHANGER MASTER TO MASTER_HOST='',MASTER_PORT='',MASTER_LOG_FILE='',MASTER_LOG_FIEL_POS='',MASTER_USER='',MASTER_PASSWORD='';
    mysql> START SLAVE;

    mysql> START SLAVE IO_Thread;
    mysql> START SLAVE SQL_Thread;

复制线程:
    master: dump
    slave: IO_Thread, SQL_Thread




read-only = YES
    在从服务器上设定,但对具有SUPER权限的用户不生效;

sync-binlog = ON
    在主服务器上设定,用于事务安全;

2台mysql5.5服务器

在主配置文件/etc/my.cnf做如下设置

log-bin=master-bin
log-bin-index=master-bin.index
innodb_file_per_table = 1

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

创建复制权限的用户

mysql> grant replication slave on *.* to 'repluser'@'192.168.1.%' identified by 'replpass';
Query OK, 0 rows affected (0.10 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

在第2台服务器上slave

关闭主服务器的二进制日志
relay-log = relay-log
relay-log-index = relay-log.index
server-id       = 11

启动服务

连接主服务器
mysql> change master to master_host='192.168.1.99',master_user='repluser',master_password='replpass',master_log_file='master-bin.000001',master_log_pos=107;
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.1.99
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 107
               Relay_Log_File: relay-log.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 107
              Relay_Log_Space: 107
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
1 row in set (0.00 sec)

启动从服务

mysql> start slave;  stop是停止
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.99
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 107
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 254
        Relay_Master_Log_File: master-bin.000001
             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: 107
              Relay_Log_Space: 404
              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: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)

ok主从架构

测试在主的上面创建一个数据库在从的上面能够看到。我这里测试可以。

现在主从是好了 但是如果不允许从服务器写该如何是好

修改全局变量

mysql> show global variables like 'read%';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| read_buffer_size     | 1048576 |
| read_only            | OFF     |
| read_rnd_buffer_size | 4194304 |
+----------------------+---------+
3 rows in set (0.00 sec)
把改为yes就可以了

修改主配置文件添加

read-only = 1 从服务器上设定

重启生效

重启生效后我们没有手动连接主服务器为什么会连接呢

在数据目录下有个master.info这个文件里记录

*管理员登陆只读服务器依然可以写

为了防止主从不一致问题主服务器提交事物,应该立刻同步从服务器需要在主服务器上开启同步二进制

sync-binlog = ON 在主服务器上设定,用于事物安全


设置半同步步骤:

在Master和Slave的mysql命令行运行如下代码:

# On Master  
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';  
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;  
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;  

# On Slave  
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';  
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;  
mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;  

在Master和Slave的my.cnf中编辑:

# On Master  
[mysqld]  
rpl_semi_sync_master_enabled=1  
rpl_semi_sync_master_timeout=1000 # 1 second  

# On Slave  
[mysqld]  
rpl_semi_sync_slave_enabled=1  


# 也可通过设置全局变量的方式来设置,如下:
set global rpl_semi_sync_master_enabled=1
# 取消加载插件
mysql> UNINSTALL PLUGIN rpl_semi_sync_master;
==============================================

查看从服务器上的semi_sync是否开启:
mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';

查看主服务器上的semi_sync是否开启,注意clients 变为1 ,证明主从半同步复制连接成功:
mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';

主---主复制

设置主-主复制:
1、在两台服务器上各自建立一个具有复制权限的用户;
2、修改配置文件:
# 主服务器上
[mysqld]
server-id = 10
log-bin = mysql-bin
relay-log = relay-mysql
relay-log-index = relay-mysql.index
auto-increment-increment = 2
auto-increment-offset = 1

# 从服务器上
[mysqld]
server-id = 20
log-bin = mysql-bin
relay-log = relay-mysql
relay-log-index = relay-mysql.index
auto-increment-increment = 2
auto-increment-offset = 2

3、如果此时两台服务器均为新建立,且无其它写入操作,各服务器只需记录当前自己二进制日志文件及事件位置,以之作为另外的服务器复制起始位置即可

server1|mysql> SHOW MASTER STATUS\G
************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 710
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

server2|mysql> SHOW MASTER STATUS\G
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
            File: mysql-bin.000003
        Position: 811
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

4、各服务器接下来指定对另一台服务器为自己的主服务器即可:
server1|mysql> CHANGE MASTER TO ...,MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=811

server2|mysql> CHANGE MASTER TO ...,MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=710

A: 查看B的二进制日志文件及位置,并以及作为自己的复制起点;
B:

复制过滤:可以复制指定的库和表:但是不应该在主的山过滤,应该在从的上设定

从主配置文件中写到

replicate-do-db = yema3  表示只复制yema3数据库


MySQL 5.6引入的GTID(Global Transaction IDs)使得其复制功能的配置、监控及管理变得更加易于实现,且更加健壮。

要在MySQL 5.6中使用复制功能,其服务配置段[mysqld]中于少应该定义如下选项:

binlog-format:二进制日志的格式,有row、statement和mixed几种类型;
    需要注意的是:当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW,现在MySQL官方认为STATEMENT这个已经不再适合继续使用;但mixed类型在默认的事务隔离级别下,可能会导致主从数据不一致;
log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port和report-host:用于启动GTID及满足附属的其它需求;
master-info-repository和relay-log-info-repository:启用此两项,可用于实现在崩溃时保证二进制及从服务器安全的功能;
sync-master-info:启用之可确保无信息丢失;
slave-paralles-workers:设定从服务器的SQL线程数;0表示关闭多线程复制功能;
binlog-checksum、master-verify-checksum和slave-sql-verify-checksum:启用复制有关的所有校验功能;
binlog-rows-query-log-events:启用之可用于在二进制日志记录事件相关的信息,可降低故障排除的复杂度;
log-bin:启用二进制日志,这是保证复制功能的基本前提;
server-id:同一个复制拓扑中的所有服务器的id号必须惟一;


report-host:
The host name or IP address of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server.

report-port:
The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration.

master-info-repository:
The setting of this variable determines whether the slave logs master status and connection information to a FILE (master.info), or to a TABLE (mysql.slave_master_info)

relay-log-info-repository:
This option causes the server to log its relay log info to a file or a table.

log_slave_updates:
Whether updates received by a slave server from a master server should be logged to the slave's own binary log. Binary logging must be enabled on the slave for this variable to have any effect.

 enforce_gtid_consistency:




一、简单主从模式配置步骤

1、配置主从节点的服务配置文件

1.1、配置master节点:
[mysqld]
binlog-format=ROW
log-bin=master-bin
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=1
report-port=3306
port=3306
datadir=/mydata/data
socket=/tmp/mysql.sock
report-host=master.9527.com

1.2、配置slave节点:
[mysqld]
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=11
report-port=3306
port=3306
log-bin=mysql-bin.log
datadir=/mydata/data
socket=/tmp/mysql.sock
report-host=slave.9527.com

2、创建复制用户

mysql> GRANT REPLICATION SLAVE ON *.* TO repluser@172.16.100.7 IDENTIFIED BY 'replpass';

说明:172.16.100.7是从节点服务器;如果想一次性授权更多的节点,可以自行根据需要修改;

3、为备节点提供初始数据集

锁定主表,备份主节点上的数据,将其还原至从节点;如果没有启用GTID,在备份时需要在master上使用show master status命令查看二进制日志文件名称及事件位置,以便后面启动slave节点时使用。

4、启动从节点的复制线程

如果启用了GTID功能,则使用如下命令:
mysql> CHANGE MASTER TO MASTER_HOST='master.9527.com', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_AUTO_POSITION=1;

没启用GTID,需要使用如下命令:
slave> CHANGE MASTER TO MASTER_HOST='172.16.100.6',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='replpass',
-> MASTER_LOG_FILE='master-bin.000003',
-> MASTER_LOG_POS=1174;

二、半同步复制

1、分别在主从节点上安装相关的插件

master> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
slave> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

2、启用半同步复制

在master上的配置文件中,添加
rpl_semi_sync_master_enabled=ON

在至少一个slave节点的配置文件中添加
rpl_semi_sync_slave_enabled=ON

而后重新启动mysql服务即可生效。


或者,也可以mysql服务上动态启动其相关功能:

master> SET GLOBAL rpl_semi_sync_master_enabled = ON;
slave> SET GLOBAL rpl_semi_sync_slave_enabled = ON;
slave> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;

3、确认半同步功能已经启用

master> CREATE DATABASE magedudb;
master> SHOW STATUS LIKE 'Rpl_semi_sync_master_yes_tx';

slave> SHOW DATABASES;




© 著作权归作者所有

雁南飞丶
粉丝 37
博文 187
码字总数 248167
作品 0
西安
运维
私信 提问
高手问答第 141 期 —— MySQL 主从复制的那些事

OSCHINA 本期高手问答(2017 年 2 月 13 日 — 2 月 19 日)我们请来了@叶金荣 和@吴炳锡为大家解答 MySQL 主从复制相关的问题。 @叶金荣 ,知数堂培训联合创始人,Oracle MySQL ACE,ACMUG...

凝小紫
2017/02/13
7.2K
40
Mysql主从复制

Mysql主从复制 背景: Mysql可以实现主从复制,在学习了Mysql主从复制后,将一些如何主从复制过程记录下来,供以后复习使用。 准备: 在做Mysql的主从复制前需要做一些准备工作: 1、同步时间...

657188918
2017/11/13
0
0
菜鸟也玩mysql之主从复制篇(上)

在实际企业应用环境当中,单台mysql数据库是不足以满足日后业务需求的。譬如服务器发生故障,没有备份服务器来提供服务的话,业务就得停止。介于这种情况,我们来学习一下mysql主从复制。 使...

wbf961127
2017/11/14
0
0
Mysql数据库AB复制简单实现

Mysql 主 从 复 制 在实际企业应用环境当中,单台mysql数据库是不足以满足日后业务需求的。譬如服务器发生故障,没有备份服务器来提供服务的话,业务就得停止。介于这种情况,我们可以对mys...

YLSL2014
2018/07/04
0
0
MySQL主从介绍&准备工作&配置主&配置从&测试主从同步

17.1 MySQL主从介绍 MySQL主从的概念 • MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后,在A上写数据,另外一台B也会跟着写数据,两者数据实时同步的 • MySQL主从是...

影夜Linux
2018/07/06
103
2

没有更多内容

加载失败,请刷新页面

加载更多

怎样在磁盘上查找MySQL表的大小?这里有答案

导读 我想知道 MySQL 表在磁盘上占用多少空间,但看起来很琐碎。不应该在 INFORMATION_SCHEMA.TABLES 中提供这些信息吗?没那么简单! 我想知道 MySQL 表在磁盘上占用多少空间,但看起来很琐碎...

问题终结者
18分钟前
5
0
jQuery load() 方法实现加载远程数据

jQuery load() 方法是简单但强大的 AJAX 方法。load() 方法从服务器加载数据,并把返回的数据放入被选元素中。 语法: $(selector).load(URL,data,callback);必需的 URL 参数规定您希望加载的...

前端老手
20分钟前
3
0
Spring Boot缓存实战 Redis 设置有效时间和自动刷新缓存-2

问题 上一篇Spring Boot Cache + redis 设置有效时间和自动刷新缓存,时间支持在配置文件中配置,说了一种时间方式,直接扩展注解的Value值,如: @Override@Cacheable(value = "people#${s...

xiaolyuh
28分钟前
9
0
怎样在磁盘上查找MySQL表的大小?这里有答案

我想知道 MySQL 表在磁盘上占用多少空间,但看起来很琐碎。不应该在 INFORMATION_SCHEMA.TABLES 中提供这些信息吗?没那么简单! 我想知道 MySQL 表在磁盘上占用多少空间,但看起来很琐碎。不应...

Linux就该这么学
53分钟前
5
0
Redis

一、Redis支持的几种数据类型:字符串、List、SET、HASH、ZSET 二、Redis的缓存技术主要是为了降低关系数据库的负载并减少网站成本 三、在Redis里面,被MULTI命令和EXEC命令包围的所有命令会...

BobwithB
55分钟前
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部