专栏首页Laoqi's Linux运维专列Mysql主从同步架构配置

Mysql主从同步架构配置

一、mysql主从介绍:

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

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

主从过程大致有3个步骤

  1. 主将更改操作记录到binlog里。
  2. 从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里。
  3. 从根据relaylog里面的sql语句按顺序执行 主上有一个log dump线程,用来和从的I/O线程传递binlog 从上有两个线程,其中I/O线程用来同步主的binlog并生成relaylog,另外一个SQL线程用来把relaylog里面的sql语句落地。

1.2 主从原理图:

Master:主mysql

Slave:备mysql

当有数据写进主mysql中的时候,就出自动生成一个log dump thread,记录到如上讲的binlog中,然后从数据库就会和主mysql有个线程进行交互,从服务器就会读取binlog日志到Slave,然后再次生成一个relay log(中继日志),然后再次和从服务器中的SQL thread线程进程交互执行。

来段大白话:

从服务器把主服务器上的binlog弄到自己服务器上去,然后根据这个binlog生成自己的relay日志,根据这个relay日志进行更改数据库,最终达到两边数据一致。

1.3 主从的作用;

  1. 数据的备份;

假如A服务器(主)突然硬件问题,宕机了。但是线上跑了很多重要的数据,我们完全可以使用B服务器(从)直接顶上。

  1. 负载均衡;

我们线上的所有数据均是从A上面读取,由于压力比较大,我们可以使用B服务器分享一部分用户到自己的服务器,但是只是可以读,不可以写入数据。看图可知,数据的读取是有方向性的,如果首先从B上读取那就混乱了,数据肯定不一致了。就会导致主从失败!


二、主从配置 – 主Mysql操作(zhdy-03)

2.1 配置my.cnf

在之前配置LAMP和LNMP的时候只是配置了datadir和socket,

现在我们需要增加server-id=144和log_bin=zhdy1(server-id为了混淆我使用的IP地址的最后一位,log_bin需要自定义一个名字,此为bin_log的前缀)

修改完配置文件后,启动或者重启mysqld服务。

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

2.2查看生成的新文件:

[[email protected]03 ~]# cd /data/mysql

[[email protected]03 mysql]# ll
total 176296
-rw-rw----. 1 mysql mysql       56 Aug 25 00:23 auto.cnf
drwx------. 2 mysql mysql       80 Aug 25 00:18 db1
drwx------. 2 mysql mysql     4096 Aug 25 00:18 db123
-rw-r-----. 1 mysql mysql 79691776 Aug 30 19:20 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Aug 30 19:20 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Aug 25 00:18 ib_logfile1
drwx------. 2 mysql mysql     4096 Aug 25 00:18 lalala
drwx------. 2 mysql mysql     4096 Aug 25 00:18 mysql
drwx------. 2 mysql mysql     4096 Aug 25 00:18 mysql2
drwx------. 2 mysql mysql     4096 Aug 25 00:18 performance_schema
drwx------. 2 mysql mysql       20 Aug 25 00:18 test
-rw-r-----. 1 mysql mysql      499 Aug 25 00:18 xtrabackup_info
drwx------. 2 mysql mysql     4096 Aug 25 00:18 zhdy01
drwx------. 2 mysql mysql    36864 Aug 25 00:18 zhdy02
drwx------. 2 mysql mysql    12288 Aug 25 00:18 zhdy03
-rw-rw----. 1 mysql mysql    55367 Aug 30 19:20 zhdy-03.err
-rw-rw----. 1 mysql mysql        5 Aug 30 19:20 zhdy-03.pid
-rw-rw----. 1 mysql mysql      120 Aug 30 19:20 zhdy1.000001
-rw-rw----. 1 mysql mysql       15 Aug 30 19:20 zhdy1.index


zhdy1.000001;zhdy1.index 这两个文件非常重要,是我们实现主从的根本。

其实在上面我们还可以看到很多的之前做测试使用的数据库,我们今天就用这些数据库做测试:

2.3 备份数据库

[[email protected]03 mysql]# mysqldump -uroot -pzhangduanya zhdy01 > /tmp/zhdy01.sql
Warning: Using a password on the command line interface can be insecure.

2.4 创建一个新的库并把之前的数据恢复

[[email protected]03 mysql]# mysql -uroot -pzhangduanya -e "create database haha"
Warning: Using a password on the command line interface can be insecure.

[[email protected]03 mysql]# mysql -uroot -pzhangduanya  haha < /tmp/zhdy01.sql 
Warning: Using a password on the command line interface can be insecure.

2.5 创建用作同步数据的用户

mysql> grant replication slave on *.* to 'repl'@'192.168.240.142' identified by 'zhangduanya';

授予repl从服务器ip为192.168.240.142, 密码为zhangduanya允许权限为replication slave

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 |
+--------------+----------+--------------+------------------+-------------------+
| zhdy1.000001 |   403309 |              |                  |                   |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

我们需要记住file 和Position。待会会用到。

2.6 查看当前数据库,开始备份

[[email protected]03 mysql]# ls
auto.cnf  db123  ibdata1      ib_logfile1  mysql   performance_schema  xtrabackup_info  zhdy02  zhdy-03.err  zhdy1.000001
db1       haha   ib_logfile0  lalala       mysql2  test                zhdy01           zhdy03  zhdy-03.pid  zhdy1.index

再此,我用到的数据库为:haha,zhdy01,zhdy02,zhdy03,然后我就模拟线上的环境,把如上经常用到的全部备份并开始同步。

2.7 备份数据库

zhdy01数据库我已经备份了(haha也是用的zhdy01的数据),再次备份其它两个:

[[email protected]03 mysql]# mysqldump -uroot -pzhangduanya zhdy02 > /tmp/zhdy02.sql
Warning: Using a password on the command line interface can be insecure.

[[email protected]03 mysql]# mysqldump -uroot -pzhangduanya zhdy03 > /tmp/zhdy03.sql
Warning: Using a password on the command line interface can be insecure.

[[email protected]03 mysql]# ls /tmp/*.sql

/tmp/zhdy01.sql  /tmp/zhdy02.sql  /tmp/zhdy03.sql

三、主从配置 – 从Mysql操作(zhdy-02)

3.1 配置my.cnf

配置server-id=132,要求和主不一样。bin_log就不需要配置了,二进制文件只需要在主服务器上面配置即可。

server-id=132

3.2 重新启动Mysql

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

3.3 把主服务器上面的数据库全部copy过来

[[email protected]02 ~]# scp 192.168.230.144:/tmp/*.sql /tmp/

[email protected]'s password: 
  
zhdy01.sql                                                                                                                                                    100%  393KB 392.9KB/s   00:00    
zhdy02.sql                                                                                                                                                    100% 2245KB   2.2MB/s   00:00    
zhdy03.sql                                                                                                                                                    100%  307KB 306.7KB/s   00:00

3.4 创建和主服务器上面一样的数据库:

mysql> create database haha;
Query OK, 1 row affected (0.02 sec)

mysql> create database zhdy01;
Query OK, 1 row affected (0.00 sec)

mysql> create database zhdy02;
Query OK, 1 row affected (0.00 sec)

mysql> create database zhdy03;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| haha               |
| mysql              |
| performance_schema |
| test               |
| zhdy01             |
| zhdy02             |
| zhdy03             |
+--------------------+
8 rows in set (0.00 sec)

3.5 导入数据:

[root@zhdy-02 ~]# mysql -uroot haha < /tmp/zhdy01.sql 
[root@zhdy-02 ~]# mysql -uroot zhdy01 < /tmp/zhdy01.sql 
[root@zhdy-02 ~]# mysql -uroot zhdy02 < /tmp/zhdy02.sql 
[root@zhdy-02 ~]# mysql -uroot zhdy03 < /tmp/zhdy03.sql 

[root@zhdy-02 ~]# ls /data/mysql/
 haha  ib_logfile1 zhdy01  zhdy02   zhdy03

3.6 配置从服务器

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

如下就是实现主从非常关键的一步:
mysql> change master to master_host='192.168.230.144', master_user='repl', master_password='zhangduanya', master_log_file='zhdy1.000001', master_log_pos=403309;
Query OK, 0 rows affected, 2 warnings (0.10 sec)

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

master_host=’192.168.230.144’//主mysql的IP;

master_user=’repl’//刚刚在主服务器上创建的用户名;

master_password=’zhangduanya’//密码

master_log_file=’zhdy1.000001’//此为刚刚主服务器上面show master status看到的其中一个file的值;

master_log_pos=403309//如上

3.7 配置全部成功了,如何测试配置的是否成功?

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.230.144
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: zhdy1.000001
          Read_Master_Log_Pos: 404262
               Relay_Log_File: zhdy-02-relay-bin.000002
                Relay_Log_Pos: 1232
        Relay_Master_Log_File: zhdy1.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: 404262
              Relay_Log_Space: 1407
              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: 144
                  Master_UUID: 98d511ff-88e8-11e7-94f3-000c29896c09
             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 the slave I/O thread to update it
           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
1 row in set (0.00 sec)
show slave stauts\G
 看是否有
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 还需关注
 Seconds_Behind_Master: 0  //为主从延迟的时间

当然我在配置好了之后也出现了错误:

Slave_IO_Running: connecting
Slave_SQL_Running: Yes

仔细检查后发现主mysql配置授权的时候密码有问题。重新授权后再次查看状态就ok了!

3.8 到主服务器(zhdy-03)执行恢复写操作:

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

四、测试主从同步

4.1 主服务器上

binlog-do-db=      //仅同步指定的库
binlog-ignore-db= //忽略指定库

假如在主服务器上面有很多数据库,但是我只想同步zhdy01这个库,需要在:

vim /etc/my.cnf

binlog-do-db=zhdy01
要是多个的话就用英文状态下的逗号去分隔;

或者有时候数据库比较多,我就一个zhdy02库不需要同步:

vim /etc/my.cnf

binlog-ignore-db=zhdy02

4.2 从服务器上

replicate_do_db=
replicate_ignore_db=
replicate_do_table=
replicate_ignore_table=

有时候我们需要在同步的时候忽略一些临时的表,这些表堵我们的用处不大,都是临时的。不建议使用上面去完成,有风险且会导致其它表有时候也不正常。所以建议用下面的两个参数去定义:

replicate_wild_do_table=  //如zhdy01.%, 支持通配符% 
replicate_wild_ignore_table=

4.3 测试主从

主服务器操作:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db123              |
| haha               |
| lalala             |
| mysql              |
| mysql2             |
| performance_schema |
| test               |
| zhdy01             |
| zhdy02             |
| zhdy03             |
+--------------------+
12 rows in set (0.00 sec)

mysql> use haha;
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_haha        |
+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_termmeta           |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
+-----------------------+
12 rows in set (0.00 sec)

mysql> select count(*) wp_users;
+----------+
| wp_users |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

我们看到了wp_users有一条数据存在,

从服务器操作:(查看wp_users的数据)

mysql> select count(*) wp_users;
+----------+
| wp_users |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

我尝试把主server上面的wp_users表内的内容清空:

mysql> truncate table wp_users;
Query OK, 0 rows affected (0.05 sec)

再次去从server去查看:

mysql> select * from wp_users;
Empty set (0.00 sec)

数据已经清空了!!!

再次测试删除一个表:

(!!!这绝对是危险动作,我的haha数据库是测试的,所以尽管删除好了,线上的机器一定要三思!!!)

主server操作:

mysql> drop table wp_users;
Query OK, 0 rows affected (0.06 sec)

从server查看:

mysql> show tables;
+-----------------------+
| Tables_in_haha        |
+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_termmeta           |
| wp_terms              |
| wp_usermeta           |
+-----------------------+
11 rows in set (0.01 sec)

wp_users表已经没有了!

千万别在server上面删除任何数据,一旦删除也就意味着数据不是一致的,主从就失败了!

如何再次恢复呢?

  1. 先把主server上面的数据与从server上面的数据保持一致,刚刚从server删除了什么数据,现在也需要把主server上面的数据也删除,为的就是数据一致!
  2. 首先主server上面操作:
mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| zhdy1.000001 |   404480 |              |                  |                   |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

记住Postion的这个值!
  1. 从server上操作使用新的Postion值:
mysql> stop slave;

mysql> change master to master_host='192.168.230.144', master_user='repl', master_password='zhangduanya', master_log_file='zhdy1.000001', master_log_pos=404480;
  1. 从server再次启动:
mysql> start slave;

mysql> show slave status\G

再次去验证就发现已经再次连接了!

思路:

  1. 保证数据一致性的前提下,按照如上操作即可!
  2. 数据不一致的前提下,建议直接重新备份,然后再次导入!再次change master即可!

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • CentOS 6_LAMP/LNMP–mysql的免编译安装

    老七Linux
  • Mysql(双主)主主架构配置

    老七Linux
  • Mysql指令select,update,insert,drop,truncate+MySQL数据库备份恢复

    一、select: 1.1 选择db1中mysql库和user表: mysql> use db1 Database changed mysql> select ...

    老七Linux
  • MySQL 切换数据库、用户卡死:“You can turn off this feature to get a quicker startup with -A“处理方法

    实战演示: 我演示的数据库就是一个数据量很大的数据库,切换数据库时半天也没有好。

    小蓝枣
  • 操作mysql第一次访问速度慢(远程)

    最近在使用java操作远程的mysql数据库的时候,第一次请求非常的慢,而且极其容易引起系统的崩溃报错连接超时

    Arebirth
  • playbook中when的使用

    在使用ansible编写playbook的过程中,我们发现在安装某服务时,例如部署fastdfs分布式存储时,有的机器需要启动tracker和storage两个...

    dogfei
  • mysql5.7.21安装和主从同步

    很久之前就做过mysql的部分应用架构,包括主从复制和集群等,一直没有形成博客记录下来,虽然网上也有很多的相关资料,但是出于加深记忆,还是把自己的操作步骤和截图...

    格子Lin
  • Linux下Mysql安装配置

    安装包 这里以centos 6.8 为例。下载Bundle包。然后解压:

    用户2929716
  • 记载一次删除mysql的默认数据库mysql的过程

    今天终于尝到了什么叫删库跑路,妈的真的悲剧,早上想在树莓派里安装个wordpress,想放点街舞视频给大家分享一下,突然发现数据库里面还有一个以前的论坛数据库,...

    bboysoul
  • Docker学习之搭建MySql容器服务

    MySQL 5.6 SQL数据库服务器Docker镜像,此容器映像包含用于OpenShift的MySQL 5.6 SQL数据库服务器和一般用法。用户可以选择RH...

    小柒2012

扫码关注云+社区

领取腾讯云代金券