MySQL主从复制

soundhearer

发布于 2020-10-15 15:10:50

1.5K0

发布于 2020-10-15 15:10:50

文章被收录于专栏：数据湖数据湖

编写复杂的SQL语句一开始让我觉得很困难，当你熟悉了类似Java等的面向对象编程语言，要适应面向集合的SQL语言，还是需要一段时间的。不过作为一名数据工程师，不熟悉SQL，实在说不过去。我们就以互联网最常用的MySQL数据库为例，一起探索SQL的奥秘。本文主要讲解MySQL主从复制原理和搭建过程。

MySQL主备的应用场景

1.sql语句需要锁表，导致暂时不能使用读服务，使用主从复制，让主库负责写，从库负责读，通过读从库保证业务的正常运作。

2.做数据的热备

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

MySQL主从复制原理

binlog: binary log，主库中保存所有更新事件日志的二进制文件。

主从复制的基础是主库记录数据库的所有变更记录到binlog。

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

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

准备和配置

1.准备两个Linux服务器（192.168.0.207，192.168.0.208），数据库版本一致为，5.5.44-MariaDB

2.Master主服务配置(192.168.0.207)

修改/etc/my.conf文件，增加如下配置

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=mysql-bin
server-id=118
binlog_format=MIXED
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

3.Slave服务配置(192.168.0.208)

修改/etc/my.conf文件，增加如下配置

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=mysql-bin
server-id=190
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

4.修改配置后重启Master和Slave的MySQL服务。

构建主从复制

1.在Master(192.168.0.207) 主MySQL上创建一个mysnc用户

用户名：mysync 密码：mysync

[root@cdh2 ~]# systemctl restart mariadb
[root@cdh2 ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.44-MariaDB-log MariaDB Server

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

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

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'mysync'@'192.168.%' IDENTIFIED BY 'mysync';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

2.查看Master(172.31.10.118) MySQL二进制日志File与Position

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      472 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> show master status
    -> ;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      472 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

3.在Slave从MySQL上执行如下SQL

[root@cdh3 ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.44-MariaDB-log MariaDB Server

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

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

MariaDB [(none)]> change master to
    -> master_host='192.168.0.207',
    -> master_user='mysync',
    -> master_password='mysync',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=472;
Query OK, 0 rows affected (0.04 sec)

4.在Slave从MySQL上执行命令，启动同步

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

5.在Slave MySQL上查看Slave状态

MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.207
                  Master_User: mysync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 472
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.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: 472
              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: 118
1 row in set (0.00 sec)

主从复制验证

1.登录Master主MySQL上执行SQL

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

2.登录Slave从MySQL上执行SQL

MariaDB [test]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

3.在Master主MySQL上执行SQL创建一个iot数据

MariaDB [(none)]> create database iot;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use iot;
Database changed
MariaDB [iot]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| iot                |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [iot]>

4.在Slave从MySQL上执行SQL查看

MariaDB [test]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| iot                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [test]>

可以看出Slave数据库已经同步了Master数据库的iot

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2020-09-11，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server