文档章节

MySQL 主从同步

野雪球
 野雪球
发布于 2018/12/17 08:12
字数 1819
阅读 19
收藏 0

MySQL主从介绍

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

  • MySQL主从是基于binlog的,主上须开启binlog才能进行主从

  • 主从过程大致有3个步骤

    • 主将更改操作记录到binlog里
    • 从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里
    • 从根据relaylog里面的sql语句按顺序执行
  • 主上有一个log dump线程,用来和从的I/O线程传递binlog

  • 从上有两个线程,其中I/O线程用来同步主的binlog并生成relaylog(中继日志),另外一个SQL线程用来把relaylog里面的sql语句落地

原理图

配置 - 主

在配置文件中mysqld模块下添加两行(注意值中最好不要有特殊符号,比如横线,可能会导致启动失败)
server-id = 134master
log_bin = syntest

[root@test-a ~]# vim /etc/my.cnf
[root@test-a ~]# cat /etc/my.cnf
# [mysql]
[client]
socket          = /usr/local/mysql/mysql.sock
# The MySQL server
[mysqld]
port            = 3306
socket          = /usr/local/mysql/mysql.sock
server-id = 134master
log_bin = syntest
#skip-grant-tables
datadir=/data/mysql
log-error=/usr/local/mysql/db.err
pid-file=/usr/local/mysql/mysqld.pid
character-set-server = utf8
[mysql.server]
basedir=/usr/local/mysql
[root@test-a ~]# /etc/init.d/mysqld restart # 启动
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@test-a ~]#  ls -lh /data/mysql   # 可以看到有新生成以syntest开头的文件
total 185M
-rw-r-----. 1 mysql mysql   56 Nov 25 15:29 auto.cnf
drwxr-x---. 2 mysql mysql   19 Dec  6 15:25 db1
-rw-r-----. 1 mysql mysql  351 Dec 17 14:28 ib_buffer_pool
-rw-r-----. 1 mysql mysql  76M Dec 17 14:28 ibdata1
-rw-r-----. 1 mysql mysql  48M Dec 17 14:28 ib_logfile0
-rw-r-----. 1 mysql mysql  48M Nov 25 15:29 ib_logfile1
-rw-r-----. 1 mysql mysql  12M Dec 17 14:28 ibtmp1
drwxr-x---. 2 mysql mysql 4.0K Nov 25 15:29 mysql
drwxr-x---. 2 mysql mysql 4.0K Dec  7 15:57 mysql2
drwxr-x---. 2 mysql mysql 8.0K Nov 25 15:29 performance_schema
-rw-r-----. 1 mysql mysql  154 Dec 17 14:28 syntest.000001
-rw-r-----. 1 mysql mysql   17 Dec 17 14:28 syntest.index
drwxr-x---. 2 mysql mysql 8.0K Nov 25 15:29 sys
drwxr-x---. 2 mysql mysql   57 Dec  7 08:28 test_db0
drwxr-x---. 2 mysql mysql 4.0K Dec 14 09:15 zrlog


[root@test-a ~]# mysqldump -uroot -p zrlog > /bak/zrlog.sql
Enter password:
[root@test-a ~]# ls /bak/
zrlog.sql
[root@test-a ~]# mysql -uroot -p -e "create database syn_zrlog"
Enter password:
[root@test-a ~]# mysql -uroot -p syn_zrlog < /bak/zrlog.sql
Enter password:
[root@test-a ~]# ls /data/mysql/
auto.cnf        ibdata1      ibtmp1  performance_schema  syn_zrlog  zrlog
db1             ib_logfile0  mysql   syntest.000001      sys
ib_buffer_pool  ib_logfile1  mysql2  syntest.index       test_db0
[root@test-a ~]# ls /data/mysql/ -lh
total 185M
-rw-r-----. 1 mysql mysql   56 Nov 25 15:29 auto.cnf
drwxr-x---. 2 mysql mysql   19 Dec  6 15:25 db1
-rw-r-----. 1 mysql mysql  351 Dec 17 14:28 ib_buffer_pool
-rw-r-----. 1 mysql mysql  76M Dec 17 14:42 ibdata1
-rw-r-----. 1 mysql mysql  48M Dec 17 14:42 ib_logfile0
-rw-r-----. 1 mysql mysql  48M Nov 25 15:29 ib_logfile1
-rw-r-----. 1 mysql mysql  12M Dec 17 14:40 ibtmp1
drwxr-x---. 2 mysql mysql 4.0K Nov 25 15:29 mysql
drwxr-x---. 2 mysql mysql 4.0K Dec  7 15:57 mysql2
drwxr-x---. 2 mysql mysql 8.0K Nov 25 15:29 performance_schema
-rw-r-----. 1 mysql mysql  14K Dec 17 14:42 syntest.000001
-rw-r-----. 1 mysql mysql   17 Dec 17 14:28 syntest.index
drwxr-x---. 2 mysql mysql 4.0K Dec 17 14:42 syn_zrlog
drwxr-x---. 2 mysql mysql 8.0K Nov 25 15:29 sys
drwxr-x---. 2 mysql mysql   57 Dec  7 08:28 test_db0
drwxr-x---. 2 mysql mysql 4.0K Dec 14 09:15 zrlog
[root@test-a ~]# grant replication slave on *.* to 'repl'@'192.168.77.129' identified by 'test111'
-bash: grant: command not found
[root@test-a ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.23-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant replication slave on *.* to 'repl'@'192.168.77.129' identified by 'test111'
    -> ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show grants for repl@192.168.77.129
    -> ;
+-----------------------------------------------------------+
| Grants for repl@192.168.77.129                            |
+-----------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.77.129' |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> flush tables with read lock; # 锁表,不让再继续读写,保证数据一致
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| syntest.000001 |    13691 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

[root@test-a ~]# mysqldump -uroot -p test_db0 > /bak/test_db0.sql
Enter password:
[root@test-a ~]# mysqldump -uroot -p db1 > /bak/db1.sql
Enter password:

配置 - 从

编辑my.cnf,配置server-id=129slave,要求和主不一样

[root@centos0 ~]# vim /etc/my.cnf
[root@centos0 ~]# cat /etc/my.cnf
# [mysql]
[client]
socket          = /usr/local/mysql/mysql.sock
# The MySQL server
[mysqld]
port            = 3306
socket          = /usr/local/mysql/mysql.sock
server-id = 129
#skip-grant-tables
datadir=/data/mysql
log-error=/usr/local/mysql/db.err
pid-file=/usr/local/mysql/mysqld.pid
character-set-server = utf8
[mysql.server]
basedir=/usr/local/mysql

[root@centos0 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS


[root@centos0 ~]# scp 192.168.77.134:/bak/*.sql /bak/
The authenticity of host '192.168.77.134 (192.168.77.134)' can't be established.
ECDSA key fingerprint is ad:9c:a3:94:7d:9b:e6:be:02:67:d0:ab:0c:de:2e:f3.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.77.134' (ECDSA) to the list of known hosts.
root@192.168.77.134's password: 
db1.sql                                                                    100% 1258     1.2KB/s   00:00    
test_db0.sql                                                               100% 1787     1.8KB/s   00:00    
zrlog.sql                                                                  100% 9935     9.7KB/s   00:00    

[root@centos0 ~]# mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.23

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

-- 创建对应的库并恢复

mysql> create database db1;
Query OK, 1 row affected (0.05 sec)

mysql> create database test_db0;
Query OK, 1 row affected (0.05 sec)

mysql> create database zrlog;
Query OK, 1 row affected (0.04 sec)

mysql> quit
Bye
[root@centos0 ~]# mysql -uroot db1 < /bak/db1.sql 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@centos0 ~]# ^[[Cm^Cql -uroot db1 < /bak/db1.sql 
[root@centos0 ~]# 
[root@centos0 ~]# 
[root@centos0 ~]# mysql -uroot -p db1 < /bak/db1.sql 
Enter password: 
[root@centos0 ~]# mysql -uroot -p test_db0 < /bak/test_db0.sql 
Enter password: 
[root@centos0 ~]# mysql -uroot -p zrlog < /bak/zrlog.sql 
Enter password: 
[root@centos0 ~]# ls /data/mysql/
auto.cnf  ib_buffer_pool  ib_logfile0  ibtmp1  performance_schema  test_db0
db1       ibdata1         ib_logfile1  mysql   sys                 zrlog
[root@centos0 ~]# mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> change master to master_host='192.168.77.134', master_user='repl', master_password='test111', master_log_file='syntest.000001', master_log_pos=13691;
Query OK, 0 rows affected, 2 warnings (0.20 sec)

mysql> start slave;
Query OK, 0 rows affected (0.24 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.77.134
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: syntest.000001
          Read_Master_Log_Pos: 13691
               Relay_Log_File: centos0-relay-bin.000002
                Relay_Log_Pos: 318
        Relay_Master_Log_File: syntest.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: 13691
              Relay_Log_Space: 527
              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: 140509184
                  Master_UUID: e43279dd-f083-11e8-b63a-000c29b95699
             Master_Info_File: /data/mysql/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.02 sec)

从配置完成后通过show slave status看Slave_IO_Running和Slave_SQL_Running是Yes则说明同步配置成功,这时需要去主库服务上打开表锁

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

Seconds_Behind_Master: 0 # 主从延迟的时间

一些主要参数说明
主服务器上
binlog-do-db= //仅同步指定的库
binlog-ignore-db= //忽略指定库
从服务器上
replicate_do_db= // 不常用 replicate_ignore_db= //不常用 replicate_do_table= //不常用 replicate_ignore_table= //不常用 replicate_wild_do_table= //如aming.%, 支持通配符%
replicate_wild_ignore_table=

测试主从同步

主库上:


mysql> use test_db0;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------------+
| Tables_in_test_db0 |
+--------------------+
| test_tb1           |
+--------------------+
1 row in set (0.00 sec)

mysql> drop table test_tb1;
Query OK, 0 rows affected (0.33 sec)

从库上查看:

mysql> show tables;
Empty set (0.00 sec)

同步OK.

© 著作权归作者所有

共有 人打赏支持
野雪球
粉丝 7
博文 70
码字总数 79542
作品 0
贵阳
私信 提问
MySQL主从介绍&准备工作&配置主&配置从&测试主从同步

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

影夜Linux
2018/07/06
0
1
Mysql主从复制

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

657188918
2017/11/13
0
0
mysql主从和半主从

mysql主从和半主从 Mysql主从复制(Mysql主从复制就是为了保障数据的数据安全性和安全性) MYSQL主从同步架构是目前使用最多的数据库架构之一,尤其是负载比较大的网站,因此对于主从同步的管...

stevenhuhu
2014/04/11
0
0
mysql主从同步开启后的iptables的设定问题

这两台在做mysql的主从同步,我的mysql服务器版本是5.6.10,mysql5.6之后做主从同步的时候,官方建议把master.info中的信息放在数据库中,具体位置在mysql数据库中的master.info表中 mysql的...

吴朝辉
2013/05/14
0
1
MYSQL主从同步

MYSQL主从同步架构是目前使用最多的数据库架构之一,尤其是负载比较大的网站,因此对于主从同步的管理也就显得非常重要,新手往往在出现主从同步错误的时候不知道如何入手,这篇文章就是根据...

jk409
2015/05/25
36
0

没有更多内容

加载失败,请刷新页面

加载更多

监听DOM上某一个元素是否发生变化,利用MutationObserver来监听元素变化

/** * 观察DOM是否发变化的事件 * @type {MutationObserver|*} */ var MutationObserver = window.MutationObserver || window.WebKitMutationObse......

lwkai
刚刚
0
0
遇到的问题

问题1:前两天在Nodepad++写了一个登录页面,但在Chrome中调试一直写不进Cookie。 解决办法:Chrome浏览器不支持本地静态js写Cookie。换用Edge调试即可。 内心:卧槽,浪费我这么多时间。 问...

akane_oimo
2分钟前
0
0
Oracle学习日志-6(聚合查询)

聚合函数 聚合函数可以对数据进行某种操作或者计算。比如几个常用的函数: COUNT:计算表中行数。 SUM:计算表中数据列中数据的合计值。 AVG:计算表中数据列中数据的平均值。 MAX:求出表中...

白话
4分钟前
0
0
Axure原型工具Axure RP9安装及Licensee

http://www.zhanshaoyi.com/9132.html

晚风0623
8分钟前
0
0
如何限制用户仅通过HTTPS方式访问OSS?

一、当前存在的问题 当前OSS支持用户使用HTTPS/HTTP协议访问Bucket。但由于HTTP存在安全漏洞。大型企业客户都要求使用HTTPS方式访问OSS,并且拒绝HTTP访问请求。 目前OSS可以通过RAM policy方...

阿里云官方博客
30分钟前
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部