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)
验证通过!