MySQL主从备份实操

MySQL主从备份实操

一、主从复制原理

原理：
（1）master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；
（2）slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件
（3）同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。
也就是说：
从库会生成两个线程,一个I/O线程,一个SQL线程;
I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;
主库会生成一个log dump线程,用来给从库I/O线程传binlog;
SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行;
注意：
1–master将操作语句记录到binlog日志中，然后授予slave远程连接的权限（master一定要开启binlog二进制日志功能；通常为了数据安全考虑，slave也开启binlog功能）。 2–slave开启两个线程：IO线程和SQL线程。其中：IO线程负责读取master的binlog内容到中继日志relay log里；SQL线程负责从relay log日志里读出binlog内容，并更新到slave的数据库里，这样就能保证slave数据和master数据保持一致了。 3–Mysql复制至少需要两个Mysql的服务，当然Mysql服务可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。 4–Mysql复制最好确保master和slave服务器上的Mysql版本相同（如果不能满足版本一致，那么要保证master主节点的版本低于slave从节点的版本） 5–master和slave两节点间时间需同步。

具体步骤：
1、从库通过手工执行change master to 语句连接主库，提供了连接的用户一切条件（user 、password、port、ip），并且让从库知道，二进制日志的起点位置（file名 position 号）； start slave
2、从库的IO线程和主库的dump线程建立连接。
3、从库根据change master to 语句提供的file名和position号，IO线程向主库发起binlog的请求。
4、主库dump线程根据从库的请求，将本地binlog以events的方式发给从库IO线程。
5、从库IO线程接收binlog events，并存放到本地relay-log中，传送过来的信息，会记录到http://master.info中
6、从库SQL线程应用relay-log，并且把应用过的记录到http://relay-log.info中，默认情况下，已经应用过的relay 会自动被清理purge

二、环境准备

准备两台linux服务器aliyun1，aliyun2，aliyun1作为主数据库服务器，aliyun2作为从数据库服务器。

三、mysql安装

1、在 /use/local下 创建mysql文件夹 mkdir mysql
2、切换到mysql文件夹下 cd mysql
3、下载mysql wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql8.0.20-linux-glibc2.12-x86_64.tar.xz， 也可以直接在官方下载最新版本 https://dev.mysql.com/downloads/mysql/ 选择linux
4、解压mysql tar xvJf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
5、重命名文件夹 mv mysql-8.0.20-linux-glibc2.12-x86_64 mysql-8.0
6、创建data文件夹 存储文件 mkdir data

[root@iZ0jl3fcjr7mcz709pzhnvZ mysql-8.0]# mkdir data

7、创建用户组和用户

[root@iZ0jl3fcjr7mcz709pzhnvZ mysql-8.0]# groupadd mysql
[root@iZ0jl3fcjr7mcz709pzhnvZ mysql-8.0]# useradd -g mysql mysql

8、授权用户

[root@iZ0jl3fcjr7mcz709pzhnvZ mysql-8.0]# chown -R mysql.mysql /usr/local/mysql-8.0/

9、进入bin目录，初始化基础信息

[root@iZ0jl3fcjr7mcz709pzhnvZ bin]# ./mysqld --user=mysql --basedir=/usr/local/mysql-8.0 --datadir=/usr/local/mysql-8.0/data/ --initialize
2021-04-01T01:36:43.712882Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2021-04-01T01:36:43.712988Z 0 [System] [MY-013169] [Server] /usr/local/mysql-8.0/bin/mysqld (mysqld 8.0.20) initializing of server in progress as process 18810
2021-04-01T01:36:43.906583Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-04-01T01:36:45.643308Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-04-01T01:36:46.850820Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: auqBu#cry7PB

10、编辑my.cnf文件 vi /etc/my.cnf 注释mysqld_safe

basedir=/usr/local/mysql-8.0/
datadir=/usr/local/mysql-8.0/data/
socket=/tmp/mysql.sock
character-set-server=UTF8MB4
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 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

11、添加mysqld服务到系统

cp -a ./support-files/mysql.server /etc/init.d/mysql

12、授权以及添加服务

[root@iZ0jl3fcjr7mcz709pzhnvZ mysql-8.0]# chmod +x /etc/init.d/mysql 
[root@iZ0jl3fcjr7mcz709pzhnvZ mysql-8.0]# chkconfig --add mysql

13、启动mysql服务

mysql service mysql start

14、查看启动状态

[root@iZ0jl3fcjr7mcz709pzhnvZ mysql-8.0]# service mysql status
MySQL running (19784)                                      [  OK  ]

15、将mysql命令添加到服务

 ln -s /usr/local/mysql-8.0/bin/mysql /usr/bin

16、登录mysql mysql -uroot -p 密码使用之前随机生成的密码

[root@iZ0jl3fcjr7mcz709pzhnvZ mysql-8.0]# service mysql status
MySQL running (19784)                                      [  OK  ]
[root@iZ0jl3fcjr7mcz709pzhnvZ mysql-8.0]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 8.0.20 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> 

17、修改root密码,密码xxxx自定义

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'xxxx'; 
flush privileges;

18、选择数据库mysql,修改远程连接

use mysql;
update user set host='%' where user='root';
flush privileges;
select user,host,plugin from user;
mysql> select user,host,plugin from user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| root             | %         | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
4 rows in set (0.00 sec)

18、从库重复操作一遍

四、主从库配置

1、在主库Master的配置，用vim 打开 /etc/my.cnf文件，添加server_id和log-bin参数并保存

server_id=1  #server_id不能和从库一样
log-bin=/usr/local/mysql-8.0/data/mysql-bin

2、重启数据库

service mysql restart

3、登录主数据库，查询bin-log是否打开

mysql> show variables like '%log_bin%';
+---------------------------------+-------------------------------------------+
| Variable_name                   | Value                                     |
+---------------------------------+-------------------------------------------+
| log_bin                         | ON                                        |
| log_bin_basename                | /usr/local/mysql-8.0/data/mysql-bin       |
| log_bin_index                   | /usr/local/mysql-8.0/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                       |
| log_bin_use_v1_row_events       | OFF                                       |
| sql_log_bin                     | ON                                        |
+---------------------------------+-------------------------------------------+
6 rows in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.01 sec)

4、在主库上建立用于主从复制的账号，检查账号建立成功。

mysql> CREATE USER 'master'@'%' IDENTIFIED BY 'Sz@944608';
Query OK, 0 rows affected (0.00 sec)

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

mysql> GRANT REPLICATION SLAVE ON *.* TO 'master'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

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

mysql> ALTER USER 'master'@'%' IDENTIFIED WITH mysql_native_password BY 'Sz@944608';
Query OK, 0 rows affected (0.01 sec)

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

mysql> select user,host,plugin from user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| master           | %         | mysql_native_password |
| rep              | %         | caching_sha2_password |
| root             | %         | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+

5、从库上操作，设置server-id并关闭binlog功能。 从库上的server-id不能和主库的serverid一致，这里我们设置主库的server-id为2，binlog的功能默认关闭的。

server_id=2
skip-log-bin

6、重启从数据库，并登录从数据库查询日志是否开启

service mysql restart
[root@iZ0jl3fcjr7mcz709pzhnvZ mysql-8.0]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 8.0.20 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> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
6 rows in set (0.01 sec)

7、主库查询日志文件和开始位置，从库上操作，配置复制参数，从库上开启同步开关，测试主从复制配置情况，查看slave的状态，注意查看slave的进程状态，主库复制的延迟秒数
（1）主库查询

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

（2）从库上操作，配置复制参数，从库上开启同步开关，测试主从复制配置情况，查看slave的状态，注意查看slave的进程状态，主库复制的延迟秒数

mysql> CHANGE MASTER TO
    -> MASTER_HOST='8.xx.xx.xx',
    -> MASTER_PORT=3306,
    -> MASTER_USER='master',
    -> MASTER_PASSWORD='xxxx',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=1220;
Query OK, 0 rows affected, 2 warnings (0.02 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: 8.xx.xx.xx
                  Master_User: master
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1220
               Relay_Log_File: iZ0jl3fcjr7mcz709pzhnvZ-relay-bin.000003
                Relay_Log_Pos: 324
        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: 1
          Exec_Master_Log_Pos: 3303
              Relay_Log_Space: 719
              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
                  Master_UUID: 23740a02-928a-11eb-8eab-00163e34a838
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

ERROR: 
No query specified

五、验证

1、主库从库对照

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

2、主库创建数据库，从库查看

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| tt                 |
+--------------------+
5 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| tt                 |
+--------------------+
5 rows in set (0.00 sec)

验证通过！