MySQL主从配置及mysqldump备份实战

1. Mysql主从概念：

mysql的主从复制，是用来建立一个和主数据库完全一样的数据库环境，从库会同步主库的所有数据，可轻松实现故障转移。

1.1 mysql主从主要作用：

在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运行。

做数据的热备，主库宕机后能够及时替换主库，保证业务可用性。

架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

1.2 常见mysql主从架构：

1.2.1MySQL主从复制的流程

主库的更新事件(update、insert、delete)被写到binlog；

从库启动并发起连接，连接到主库；

主库创建一个binlog dump thread，把binlog的内容发送到从库；

从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log；

从库启动之后，创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的数据库。

1.2.2 MySQL主从复制的原理

MySQL主从复制是一个异步的复制过程，主库发送更新事件到从库，从库读取更新记录，并执行更新记录，使得从库的内容与主库保持一致。

binlog：binary log，主库中保存所有更新事件日志的二进制文件。binary log是从数据库服务启动的一刻起，保存数据库所有变更记录（数据库结构和内容）的文件。在主库中，只要有更新事件出现，就会被依次地写入到binary log中，之后会推送到从库中作为从库进行复制的数据源。

binlog输出线程：每当有从库连接到主库的时候，主库都会创建一个线程然后发送binlog内容到从库。 对于每一个即将发送给从库的sql事件，binlog输出线程会将其锁住。一旦该事件被线程读取完之后，该锁会被释放，即使在该事件完全发送到从库的时候，该锁也会被释放。

在从库中，当复制开始时，从库就会创建从库I/O线程和从库的SQL线程进行复制处理。

从库I/O线程：当start slave语句在从库开始执行之后，从库创建一个I/O线程，该线程连接到主库并请求主库发送binlog里面的更新记录到从库上。 从库I/O线程读取主库的binlog输出线程发送的更新并拷贝这些更新到本地文件，其中包括relay log文件。

从库的SQL线程：从库创建一个SQL线程，这个线程读取从库I/O线程写到relay log的更新事件并执行。

综上所述，可知：

对于每一个主从复制的连接，都有三个线程。拥有多个从库的主库为每一个连接到主库的从库创建一个binlog输出线程，每一个从库都有它自己的I/O线程和SQL线程。

从库通过创建两个独立的线程，使得在进行复制时，从库的读和写进行了分离。因此，即使负责执行的线程运行较慢，负责读取更新语句的线程并不会因此变得缓慢。比如说，如果从库有一段时间没运行了，当它在此启动的时候，尽管它的SQL线程执行比较慢，它的I/O线程可以快速地从主库里读取所有的binlog内容。这样一来，即使从库在SQL线程执行完所有读取到的语句前停止运行了，I/O线程也至少完全读取了所有的内容，并将其安全地备份在从库本地的relay log，随时准备在从库下一次启动的时候执行语句。

1.3 MySQL主从部署：

node3：master，192.168.48.183

node4：slave， 192.168.48.184

1.3.2 master端配置：

xxxxxxxxxx

# 安装好mysql/mariadb数据库：

[root@node03 ~]# yum install mariadb mariadb-server -y

# 修改/etc/my.cnf配置文件，在[mysqld]指令段添加以下行：

log-bin=node3-bin

server-id=1

​

# 启动数据库服务：

[root@node03 ~]# systemctl start mariadb

[root@node03 ~]#

# 查看mysql进程：

[root@node03 ~]# ps -ef|grep mysqld

mysql      6130      1  0 20:37 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr

mysql      6316   6130  0 20:37 ?        00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock

root       6365   5819  0 20:38 pts/0    00:00:00 grep --color=auto mysqld

[root@node03 ~]#

# 查看mysql端口：

[root@node03 ~]# netstat -ntlp|grep 3306

tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      6316/mysqld        

[root@node03 ~]#

1.3.3 查看配置是否生效：

xxxxxxxxxx

# 通过mysql直接进入数据库：

[root@node03 ~]# mysql

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 2

Server version: 5.5.65-MariaDB MariaDB Server

​

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

​

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

​

MariaDB [(none)]>

# 查看log_bin和sql_log_bin是否均为on;

MariaDB [(none)]> show variables like "%log_bin";

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin       | ON    |

| sql_log_bin   | ON    |

+---------------+-------+

2 rows in set (0.00 sec)

​

MariaDB [(none)]>

1.3.4 授权从库：

xxxxxxxxxx

MariaDB [(none)]> grant replication slave on *.* to "superman"@"192.168.48.184" identified by "123456";

Query OK, 0 rows affected (0.00 sec)

​

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

​

MariaDB [(none)]>

1.3.5 查看master状态：

xxxxxxxxxx

MariaDB [(none)]> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| node3-bin.000004 |      479 |              |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

​

MariaDB [(none)]>

1.3.6 slave端配置：

xxxxxxxxxx

# 修改/etc/my.cnf配置文件，在[mysqld]指令块下添加如下行：

server-id=2

1.3.7 启动slave数据库服务：

xxxxxxxxxx

[root@node04 ~]# systemctl start mariadb

1.3.8 在slave数据库上指定master:

xxxxxxxxxx

[root@node04 ~]# mysql

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 2

Server version: 5.5.65-MariaDB MariaDB Server

​

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

​

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

​

MariaDB [(none)]>

MariaDB [(none)]> change master to

   -> master_host="192.168.48.183",

   -> master_user="superman",

   -> master_password="123456",

   -> master_log_file="node3-bin.000004",

   -> master_log_pos=479;

Query OK, 0 rows affected (0.02 sec)

​

MariaDB [(none)]>

1.3.9 查看slave状态：

xxxxxxxxxx

MariaDB [(none)]> slave start;

Query OK, 0 rows affected (0.00 sec)

​

MariaDB [(none)]>

MariaDB [(none)]> show slave status\G

*************************** 1. row ***************************

              Slave_IO_State: Waiting for master to send event

                 Master_Host: 192.168.48.183

                 Master_User: superman

                 Master_Port: 3306

               Connect_Retry: 60

             Master_Log_File: node3-bin.000004

         Read_Master_Log_Pos: 479

              Relay_Log_File: mariadb-relay-bin.000002

               Relay_Log_Pos: 529

       Relay_Master_Log_File: node3-bin.000004

            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: 479

             Relay_Log_Space: 825

             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)

​

MariaDB [(none)]>

1.3.10 验证数据同步：

xxxxxxxxxx

# 在主库创建一个数据库：

MariaDB [(none)]> create database zabbix charset=utf8;

Query OK, 1 row affected (0.00 sec)

​

MariaDB [(none)]>

MariaDB [(none)]> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

| zabbix             |

+--------------------+

5 rows in set (0.00 sec)

​

MariaDB [(none)]>

​

# 在从库查看：

MariaDB [(none)]> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

| zabbix             |

+--------------------+

5 rows in set (0.00 sec)

​

MariaDB [(none)]>

1.3.11 同步错误分析:

xxxxxxxxxx

Slave_IO_Running: Connecting

# 第一种：主库宕机

# 第二种：从库指定的用户名与密码错误（与主库授权的用户名和密码不一致）

# 第三种：关闭防火墙

Slave_IO_Running: No

# 从库指定的二进制文件有误

Slave_SQL_Running: No

# pos点问题

1.4 主从复制延迟问题及解决方法：

1.4.1 从库过多：

建议从库数量3-5 为宜，要复制的从节点数量过多，会导致复制延迟。

1.4.2 从库硬件差：

从库硬件比主库差，导致复制延迟，查看master和slave的系统配置，可能会因为机器配置的问题，包括磁盘IO、CPU、内存等各方面因素造成复制的延迟，一般发生在高并发大数据量写入场景。

1.4.3 网络问题：

主从库之间的网络延迟，主库的网卡、网线、连接的交换机等网络设备都可能成为复制的瓶颈，导致复制延迟。

1.5  Mysqldump备份：

1.5.1 只备份表，不备份数据本身：

xxxxxxxxxx

# 备份zabbix数据库中的所有表，但是不会自动生成创建zabbix数据库的语句：

[root@node03 ~]# mysqldump -uroot -p*** zabbix > zabbix_tables.sql

[root@node03 ~]#

1.5.2 备份zabbix数据库与表：

xxxxxxxxxx

备份zabbix数据库中的所有表，并且会生成创建zabbix数据库的SQL语句，也就是导入时不需要先创建数据库：

[root@node03 ~]# mysqldump -uroot -p*** --databases zabbix > zabbix_database.sql

[root@node03 ~]#

1.5.3 备份多个数据库：

xxxxxxxxxx

[root@node03 ~]# mysqldump -uroot -p*** --databases zabbix mysql > zabbix_mysql_database.sql

[root@node03 ~]#

1.5.4 备份所有数据库：

xxxxxxxxxx

[root@node03 ~]# mysqldump -uroot -p*** --all-databases > all_databases.sql

[root@node03 ~]#

或者：

[root@node03 ~]# mysqldump -uroot -p*** -A > all.sql

[root@node03 ~]#

1.5.5 备份zabbix数据库，并且记录pos点：

xxxxxxxxxx

[root@node03 ~]# mysqldump -uroot -p*** --master-data zabbix > zabbix_pos.sql

[root@node03 ~]#

1.5.6  备份数据库，并刷新日志：

xxxxxxxxxx

[root@node03 ~]# mysqldump -uroot -p*** --master-data --flush-logs zabbix > zabbix_pos_flush.sql

[root@node03 ~]#

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 205,132评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 87,802评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,566评论 0赞 338
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,858评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,867评论 5赞 368
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,695评论 1赞 282
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,064评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,705评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 42,915评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,677评论 2赞 323
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,796评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,432评论 4赞 322
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,041评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,992评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,223评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,185评论 2赞 352
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,535评论 2赞 343