MYSQL主从同步详细教程

MySQL 数据库集群实战

随着访问量的不断增加，单台MySQL数据库服务器压力不断增加，需要对MYSQL进行优化和架构改造，MYQSL优化如果不能明显改善压力情况，可以使用高可用、主从复制、读写分离来、拆分库、拆分表来进行优化。

MYSQL主从复制集群在中小企业、大型企业中被广泛使用，MYSQL 主从复制的目的是实现数据库冗余备份，将Master数据库数据定时同步至Slave库中，一旦Master数据库宕机，可以将WEB应用数据库配置快速切换至Slave数据库，确保WEB应用较高的可用率，如图所示，为MYSQL主从复制结构图:

简介

MYSQL 主从复制至少需要2台数据库服务器，其中一台为Master库，另外一台为Slave 库，mysql 主从同步是一个异步的过程。要实现复制，首先需要在master 上开启server-id和bin-log日志。

bin-log日志主要用来记录库中执行的增、删、修改、更新操作的sql语句，整个过程需要开启3个线程，分别是master上的IO线程， Slave 上的IO线程 和SQL线程。

同步原理详解

具体主从同步原理详解：

Slave 上执行start slave，Slave IO线程会 通过master创建的授权用户连接上master,并请求master从指定的文件和位置之后发送bin-log日志内容。

Master 接收到来自slave IO线程的请求后，master IO 线程会根据 Slave 指定的bin-log日志和 position点之后的内容返回给Slave IO线程。

Slave IO线程接收到信息后，将接收到的日志内容一次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的bin-log的文件和position 点记录到master.info文件中。以便在下一次读取的时候能告知master从响应的bin-log文件名及最后一个position点开始发起请求。

Slave Sql线程检测到relay-log日志中日志又更新，会立刻解析relay-log的内容，成为在Master真实执行时候的那些可执行的SQL语句，将解析的语句并在Slave里执行，执行成功后，，Master 和Slave 库克保持数据一致性。

MYSQL主从同步形式

一主一从

主主复制

一主多从(扩展系统读取的性能)

多主(5.7开始)

MYSQL 主从主要作用

实时灾备，用于故障切换

读写分离，提供查询服务

主从复制配置步骤

搭建二进制数据库

配置主数据库（修改配置文件）

在主数据库里创建一个同步账号授权给从数据库使用

配置从数据库（修改配置文件）

MYSQL主从搭建

1、实验环境

配置主从数据库

• master 库配置
  • 开启日志(server-id=1，log-bin=xx)

[root@localhost ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /data/mysql
socket = /tmp/mysql.sock
port = 3306
pid-file = /data/mysql/mysql.pid
user = mysql
skip-name-resolve
server-id=1
log-bin=hebao_log.bin
[root@localhost ~]# 

• 创建授权用户，授权( grant replication slave on *.* to 'liuaoni'@'%' identified by '123456')

[root@localhost ~]# mysql -uroot -proot
mysql: [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 2
Server version: 5.7.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> grant replication slave on *.* to 'liuaoni'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)    replication 复制权限

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| hebao_log.000001 |      592 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> 

Slave 数据库配置

mysql> change master to master_host='192.168.10.11',master_user='liuaoni',master_password='123456',master_log_file='hebao_log.000001',master_log_pos=592;
Query OK, 0 rows affected, 2 warnings (0.14 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;
  *************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.10.11
                Master_User: liuaoni
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: xx_log.000004
        Read_Master_Log_Pos: 836
             Relay_Log_File: localhost-relay-bin.000004
              Relay_Log_Pos: 317
      Relay_Master_Log_File: xx_log.000004
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
           看到   Slave_IO_Running: Yes
                    Slave_SQL_Running: Yes 都为yes就好

测试：

主上创建数据库：
mysql> create database Master_db;
Query OK, 1 row affected (0.14 sec)

mysql>


从上查看：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Master_db          |
| mysql              |
| mysql_back         |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql常用配置文件参数：

MYSQL 备份与恢复

数据库备份方案

全量备份：对某一个时间点上的所有数据或应用进行一个完全的拷贝，备份时间长，数据恢复快

增量备份：是指在一次完全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加 或者被修改的文件。没有重复数据，备份时间短，恢复数据必须按一定的顺序进行

差异备份：备份上一次的完全备份后发生变化的所有文件。差异备份是指 在一次全备份后到进行差异备份的这段时间内对那些以增加或者修改文件的备份。在恢复时，只需要对第一次全量备份和最后一次差异备份进行恢复

MYSQL 备份工具mysqldump

语法：
    mysqldump [OPTIONS] database [tables ...]
    mysqldump [OPTIONS] --all-databases [OPTIONS]
    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
    
常用的OPTIONS：
    -uUSERNAME      指定数据库用户名
    -hHOST          指定服务器主机，请使用ip地址
    -pPASSWORD      指定数据库用户的密码
    -P#             指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307

备份整个数据库(全备)

[root@localhost ~]# mysqldump -uroot -p -h127.0.0.1 --all-databases > all-202104202203.sql
Enter password: 

备份hebao整个数据库

[root@localhost ~]# mysqldump -uroot -p -h127.0.0.1 --databases hebao > hb-202104202201.sql Enter password:

备份hebao库里面的Student表和Course表

[root@localhost ~]# mysqldump -uroot -p -h127.0.0.1 --databases hebao > hb-202104202201.sql
Enter password:

恢复hebao 数据库

[root@localhost ~]# mysql -uroot -p -h127.0.0.1 < hb-202104202201.sql
Enter password:

[root@localhost ~]# mysql -uroot -p -h127.0.0.1 -e 'show databases;'
Enter password:

恢复hebao数据库的Student和Course表

mysql> use hebao;
mysql> source table-202104202201.sql

总结

mysql 主从同步原理很重要，最好理解记忆下来，面试常见问题

如果感觉小编写得不错，请素质三连：点赞+转发+关注。我会努力写出更好的作品分享给大家。更多JAVA进阶学习资料小编已打包好，可以关注私信找我领取哦！