MySQL主从复制

前言

系列文章:
1.MySQL主从复制
2.OneProxy实现MySQL读写分离

配置MySQL主从复制,先来讲讲一些理论。

MySQL复制功能提供分担读负载。
基于二进制日志的复制是异步的,那么复制有什么好处?
1.实现在不同服务器上的数据分布,利用二进制日志增量进行,不需要太多带宽,但是使用基于行的复制在进行大批量的更改时,会对带宽带来一定的压力。特别是跨IDC环境下进行复制,应该分批进行。
2.实现数据读取的负载均衡。可以通过DNS轮询的方式把程序的读连接分配到不同的备份数据库中。还有可以通过LVS+Keepalived等等。
3.增强了数据安全性,利用备库的备份来减少主库负载,还可以避免单点故障。值得注意的是,复制并不能替代备份。
4.实现数据库在线升级。

说到日志,MySQL的服务层日志有二进制日志,慢查询日志,通用日志。而存储引擎的日志有innodb的重做日志(Redo log),回滚日志(Undo log)。

Redo Log:可以实现事务的持久性。用于记录某数据块被修改后的值,可以用来恢复未写入data file的已成功事务更新的数据。通常会初始化2个或更多的 ib_logfile 存储 redo log,由参数 innodb_log_files_in_group 确定个数,命名从 ib_logfile0 开始,依次写满 ib_logfile 并顺序重用。如果最后1个 ib_logfile 被写满,而第一个ib_logfile 中所有记录的事务对数据的变更已经被持久化到磁盘中,将清空并重用之。


image.png
image.png

Undo Log:未提交的事务,独立于表空间,需要随机访问,可以存储在高性能IO设备中。存放于ibdata中。


image.png

Undo日志记录某数据被修改前的值,可以用来在事务失败时进行rollback;Redo日志记录某数据块被修改后的值,可以用来恢复未写入data file的已成功事务更新的数据。

那么二进制日志是什么?
二进制日志记录了所有对MySQL数据库的修改事件,包括了增删查改事件和对表结构进行修改的事件。

二进制日志格式,可以设置基于段的格式,比如binlog_format=statement
基于段的格式,优点是:日志记录相对较小,节约磁盘网络IO。缺点是:必须要记录上下文信息,保证语句在slave上执行的结果和master上相同。

而基于段的格式binlog_format=row适用于只对一条记录修改或者插入,此时所产生的日志量小于段产生的日志量。

环境配置如下:
1.master(192.168.10.21) MySQL 5.7.17
2.slave(192.168.10.6) MySQL 5.7.17

给master创建sync用户并分配所需权限。

grant all privileges on *.* to sync@'%' identified by 'sync' \g
image.png

给slave创建sync用户并分配所需权限。

grant all privileges on *.* to sync@'%' identified by 'sync' \g
image.png

修改master的配置文件


image.png

修改slave的配置文件


image.png

在master查看binlog

SHOW BINARY LOGS
FLUSH BINARY LOGS #刷新binlog
SHOW BINARY LOGS
image.png

在slave查看binlog

SHOW BINARY LOGS
image.png

我们在配置slave的时候,不要对master进行操作。每次在master进行CRUD和对表结构进行修改时,binlog会发生变化。
在slave中,配置master相关的信息。

mysql> change master to
    -> master_host="192.168.10.21",
    -> master_user="sync",
    -> master_password="sync",
    -> master_log_file="mysql-bin.000002",
    -> master_log_pos=154\g
Query OK, 0 rows affected, 2 warnings (0.02 sec)

启动slave。


image.png

查看slave运行状态,Slave_IO_Running和Slave_SQL_Running都为yes的话,说明slave运行没问题。

SHOW SLAVE STATUS

image.png

image.png

在master创建测试库rap_test,我们发现binlog也发生了变化。


image.png

image.png

我们查看master的状态

SHOW MASTER STATUS
image.png

我们再来查看slave的状态,发现slave已经复制成功了。

  1. The position, ON THE MASTER, from which the I/O thread is reading: Master_Log_File/Read_Master_Log_Pos. —–相对于主库,从库读取主库的二进制日志的位置,是IO线程

  2. The position, IN THE RELAY LOGS, at which the SQL thread is executing: Relay_Log_File/Relay_Log_Pos —-相对于从库,是从库的sql线程执行到的位置

  3. The position, ON THE MASTER, at which the SQL thread is executing: Relay_Master_Log_File/Exec_Master_Log_Pos —-相对于主库,是从库的sql线程执行到的位置

更多返回参数说明可以参考show slave status 参数详解

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.21
                  Master_User: sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1119
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 541
        Relay_Master_Log_File: mysql-bin.000002
             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: 1119
              Relay_Log_Space: 752
              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: 21
                  Master_UUID: 67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

我们在slave中也看到了rap_test库,说明从库复制成功。


image.png

打开slave中的localhost-relay-bin.000002文件,可以看到sql线程执行了create database rap_test语句。


image.png

另外我们可以通过show slave status命令返回的Seconds_Behind_Master参数查看主从延迟状态。


image.png

尾言

大家好,我是cmazxiaoma(寓意是沉梦昂志的小马),感谢各位阅读本文章。
小弟不才。
如果您对这篇文章有什么意见或者错误需要改进的地方,欢迎与我讨论。
如果您觉得还不错的话,希望你们可以点个赞。
希望我的文章对你能有所帮助。
有什么意见、见解或疑惑,欢迎留言讨论。

最后送上:心之所向,素履以往。生如逆旅,一苇以航。


saoqi.png
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 148,637评论 1 318
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 63,443评论 1 266
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 99,164评论 0 218
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 42,075评论 0 188
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 50,080评论 1 266
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 39,365评论 1 184
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 30,901评论 2 283
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 29,649评论 0 176
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,122评论 0 223
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 29,734评论 2 225
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,093评论 1 236
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 27,548评论 2 222
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,028评论 3 216
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,765评论 0 9
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,291评论 0 178
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 34,162评论 2 239
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 34,293评论 2 242

推荐阅读更多精彩内容