编写复杂的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
