资讯

精准传达 • 有效沟通

从品牌网站建设到网络营销策划，从策略到执行的一站式服务

网站建设 >

查看其它板块

MySQL主从配置及mysqldump备份的步骤

本篇内容主要讲解“MySQL主从配置及mysqldump备份的步骤”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL主从配置及mysqldump备份的步骤”吧!

10年积累的成都做网站、成都网站制作经验，可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你，你也不认识我。但先网站设计后付款的网站建设流程，更有平城免费网站建设让你可以放心的选择与我们合作。

1. MySQL主从概念

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

1.1 MySQL主从主要作用

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

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

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

1.2 常见MySQL主从架构

MySQL主从配置及mysqldump备份的步骤

1.2.1MySQL主从复制的流程

MySQL主从配置及mysqldump备份的步骤

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

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

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

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

  5. 从库启动之后，创建一个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.1 master端配置
# 安装好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.2 查看配置是否生效
# 通过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.3 授权从库
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.4 查看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.5 slave端配置
# 修改/etc/my.cnf配置文件，在[MySQLd]指令块下添加如下行：
server-id=2
1.3.6 启动slave数据库服务
[root@node04 ~]# systemctl start mariadb
1.3.7 在slave数据库上指定master
[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.8 查看slave状态
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.9 验证数据同步
# 在主库创建一个数据库：
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.10 同步错误分析
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 只备份表，不备份数据本身
# 备份zabbix数据库中的所有表，但是不会自动生成创建zabbix数据库的语句：
[root@node03 ~]# MySQLdump -uroot -p*** zabbix > zabbix_tables.sql
[root@node03 ~]#
1.5.2 备份zabbix数据库与表
备份zabbix数据库中的所有表，并且会生成创建zabbix数据库的SQL语句，也就是导入时不需要先创建数据库：
[root@node03 ~]# MySQLdump -uroot -p*** --databases zabbix > zabbix_database.sql
[root@node03 ~]#
1.5.3 备份多个数据库
[root@node03 ~]# MySQLdump -uroot -p*** --databases zabbix MySQL > zabbix_MySQL_database.sql
[root@node03 ~]#
1.5.4 备份所有数据库
[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点
[root@node03 ~]# MySQLdump -uroot -p*** --master-data zabbix > zabbix_pos.sql
[root@node03 ~]#
1.5.6  备份数据库，并刷新日志
[root@node03 ~]# MySQLdump -uroot -p*** --master-data --flush-logs zabbix > zabbix_pos_flush.sql
[root@node03 ~]#

到此，相信大家对“MySQL主从配置及mysqldump备份的步骤”有了更深的了解，不妨来实际操作一番吧！这里是创新互联网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！


当前文章：MySQL主从配置及mysqldump备份的步骤
链接地址：http://cdkjz.cn/article/jcggii.html
返回首页 了解更多建站资讯

相关资讯

多年建站经验

多一份参考，总有益处

联系快上网，免费获得专属《策划方案》及报价

咨询相关问题或预约面谈，可以通过以下方式与我们联系

大客户专线   成都：13518219792   座机：028-86922220

在线咨询 提交需求
友情链接 交换友情链接
防护网厂注册域名四川雅安服务器托管营销型网站建设营山产后护理服务专业LED亮化工程成都标识标牌设计制作重庆网站建设成都柴油发电机组四川发电机回收

成都网站建设公司地址：成都市青羊区太升南路288号锦天国际A座10层 建设咨询028-86922220

成都快上网科技有限公司-四川网站建设设计公司 | 蜀ICP备19037934号 Copyright 2020,ALL Rights Reserved cdkjz.cn | 成都网站建设 | © Copyright 2020版权所有.

专家团队为您提供成都网站建设,成都网站设计,成都品牌网站设计,成都营销型网站制作等服务,成都建网站就找快上网！ | 成都网站建设哪家好？ | 网站建设地图