MySQL一主一从

MySQL一主一从

Scroll Down

主从同步结构模式

基本应用:
— 单项复制:一主一从
扩展应用:
— 一主多从
— 链式复制:主从从
— 主主(互为主从)

主从复制原理

主从复制原理介绍

MySQL 的主从复制是一个异步的复制过程(虽然一般情况下感觉是实时的),数据将从一个 MySQL 数据库(Master)复制到另一个 MySQL 数据库(Slave),在 Master 和 Slave 之间实现整个主从复制的过程是由三个线程参与完成的,其中有两个线程(SQL 和 I/O )在 Slave 端,另一个线程(I/O)在 Master 端
要实现 MySQL 的主从复制,首先必须打开 Master 端的 binlog 记录功能,否则就无法实现,因为整个复制过程实际上就是 Slave 从 Master 获取 binlog 日志,然后再在 Slave 上以相同顺序执行获取的 binlog 日志中所记录的各种 SQL 操作。MySQL 的 binlog 功能在 /etc/my.cnf 中的 [mysqld] 模块下增加 log-bin 参数来实现
mysql1.png
总结:
① 主从复制是异步的逻辑的 SQL 语句级的复制;
② 复制时,主库有一个 I/O 线程,从库有两个线程,及 I/O 和 SQL 线程;
③ 实现主从复制的必要条件是主库要开启记录 binlog 的功能;
④ 作为复制的所有 MySQL 节点的 server-id 都不能相同;
⑤ binlog 文件只记录对数据内容有更改的 SQL 语句,不记录任何查询语句;

环境介绍

HostnameIP系统mysql版本
db01192.168.200.65centos7.5mysql5.6.40
db02192.168.200.66centos7.5mysql5.6.40
db03192.168.200.67centos7.5mysql5.6.40
db04192.168.200.68centos7.5mysql5.6.40

一主一从

将db01作为主库Master;db02作为从库Slave;

将主库Master配置文件my.cnf里binlog打开

[root@db01 ~]# vim /etc/my.cnf
....
#在mysqld模块中添加如下内容
log-bin = /application/mysql/data/mysql-bin    # 打开 binlog 日志功能,可自定义路径
server-id = 1  #主从库id不能相同

重启mysqld

[root@db01 ~]# systemctl restart mysqld
#查看上面配置是否生效
[root@db01 ~]# ll /application/mysql/data/
总用量 110676
-rw-rw----. 1 mysql mysql       56 12月 31 11:36 auto.cnf
-rw-rw----. 1 mysql mysql    51908 12月 31 18:21 db01.err
-rw-rw----. 1 mysql mysql        6 1月   2 11:27 db01.pid
-rw-rw----. 1 mysql mysql 12582912 1月   2 18:04 ibdata1
-rw-rw----. 1 mysql mysql 50331648 1月   2 18:04 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 12月 31 11:36 ib_logfile1
drwx------. 2 mysql mysql       20 1月   2 18:00 lw
drwx------. 2 mysql mysql     4096 12月 31 11:36 mysql
-rw-rw----. 1 mysql mysql      924 1月   2 13:19 mysql-bin.000001
-rw-rw----. 1 mysql mysql     1104 1月   2 13:46 mysql-bin.000002
-rw-rw----. 1 mysql mysql     2664 1月   2 18:04 mysql-bin.000003
-rw-rw----. 1 mysql mysql      123 1月   2 13:46 mysql-bin.index
drwx------. 2 mysql mysql       20 1月   2 17:59 oldboy
drwx------. 2 mysql mysql       56 1月   2 14:07 ops
drwx------. 2 mysql mysql     4096 12月 31 11:36 performance_schema
drwxr-xr-x. 2 mysql mysql       20 12月 31 11:30 test

查看binlog功能生效

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

从库配置

[root@db02 ~]# vim /etc/my.cnf
#下mysqld模块下添加如下内容
log-bin = /application/mysql/data/mysql-bin  #不做级联的话从库binlog功能是否开启都不影响主库
server-id = 3  # 两个id要不同

主库建立同步的账号 rep

mysql> grant replication slave on *.* to 'rep'@'192.168.200.%' identified by '123456';
mysql> flush privileges ;
#提示:replication slave 为 mysql 同步的必须权限,此处不要授权 all

主库上备份

mysql> flush table with read lock;   # 在主库上给所有表加只读锁
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |     2664 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       924 |
| mysql-bin.000002 |      1104 |
| mysql-bin.000003 |      2664 |
+------------------+-----------+
3 rows in set (0.00 sec)

[root@db01 ~]# mysqldump -uroot -p123456 -A -B --events >/opt/rep.sql
[root@db01 ~]# mysqldump -uroot -p123456 -A -B --events --master-data=2 >/opt/rep.sql

mysql> unlock tables;   # 打开只读锁
mysql> show master status;   #备份完毕查看是否有变化,确保锁表成功

提示:上面是官方文档给的备份方式,生产场景中的备份常用 -x 参数锁表,用 --master-data 参数记录 binlog 的文件及位置

从库上操作

把上面的备份数据放入从库

#将主库备份的rep.sql文件拷贝到db02,导入数据
[root@db02 ~]# mysql -uroot -p 123456 < /opt/rep.sql

mysql> CHANGE MASTER TO MASTER_HOST = '192.168.200.65', MASTER_USER = 'rep', MASTER_PASSWORD = '123456', MASTER_PORT = 3306, MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=2767;
mysql> flush privileges;
#change语句结果会放到master.info里面
[root@db02 ~]# cat /application/mysql/data/master.info  
23
mysql-bin.000003
2664
192.168.200.65
rep
123456
3306
60
.......

打开从库两个线程

mysql> start slave;   #  打开从库两个线程的开关。
Query OK, 0 rows affected (0.10 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.200.65
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 2664
               Relay_Log_File: db02-relay-bin.000004
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
.......            

主从复制检查

#主库创建库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lw                 |
| mysql              |
| oldboy             |
| ops                |
| performance_schema |
| test               |
+--------------------+
7 rows in set (0.00 sec)

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lw                 |
| mysql              |
| oldboy             |
| oldgril            |
| ops                |
| performance_schema |
| test               |
+--------------------+
8 rows in set (0.00 sec)

#登录从库查看
[root@db02 ~]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.6.40-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lw                 |
| mysql              |
| oldboy             |
| oldgril            |
| ops                |
| performance_schema |
| test               |
+--------------------+
8 rows in set (0.00 sec)