一、MySQL备份
1.逻辑备份
mysqldump -uroot -p -A -R --triggers --single-transaction --master-data=2 > /tmp/full.sql
2.物理备份
1.备份data目录
2.xtrabackup
3.xtrabackup全备
#全备
[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full$(date +%F)/
4.xtrabackup全备恢复
#前提条件
1.数据库必须停止
2.数据库的数据目录必须为空
#恢复过程
[root@db01 ~]# systemctl stop mysqld
[root@db01 ~]# cd /usr/local/mysql
[root@db01 /usr/local/mysql]# mv data databack
[root@db01 /usr/local/mysql]# innobackupex --copy-back /backup/full2020-11-05
[root@db01 /usr/local/mysql]# chown -R mysql.mysql data
5.xtrabackup增量备份
1.基于上一次备份进行增量
2.增量备份无法单独恢复,必须基于全备进行恢复
3.所有增量必须要按顺序合并到全备当中
6.xtrabackup增量备份实践
1.首先进行全备
[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full$(date +%F)
2.模拟新增数据
mysql> create database xtra;
mysql> use xtra
mysql> create table xtrabackup(id int);
mysql> insert xtrabackup values(1),(2),(3);
3.进行增量备份
[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full2020-11-05 /backup/firstadd
参数说明:
--incremental:
--incremental-basedir:上一次备份的路径
4.确认增量备份是否准确
[root@db01 /backup]# cat full2020-11-05/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2327894806
[root@db01 /backup]# cat firstadd/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2327894806 #第一次增备的起始位置点是全备的结束位置点
to_lsn = 2327902069
5.再次新增数据
mysql> use xtra
mysql> insert xtrabackup values(4),(5),(6);
6.再次增备
[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/firstadd /backup/twoadd
7.再次查看是否衔接
[root@db01 /backup]# cat full2020-11-05/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2327894806
[root@db01 /backup]# cat firstadd/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2327894806 #全备的结束点
to_lsn = 2327902069
[root@db01 /backup]# cat twoadd/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2327902069 #第一次增备的结束点
to_lsn = 2327903988
7.xtrabackup增量备份恢复实践
1.准备恢复
1)full + firstadd + twoadd
2)需要将 firstadd 和 twoadd 按顺序合并到full中
3)分步骤进行 --apply-log
2.第一步:在全备中apply-log时,只应用redo,不应用undo
[root@db01 ~]# innobackupex --apply-log --redo-only /backup/full2020-11-05
3.第二步:将 firstadd 合并到 full 中,并且apply-log,只应用redo,不应用undo
[root@db01 ~]# innobackupex --apply-log --redo-only --incremental-dir=/backup/firstadd/ /backup/full2020-11-05
4.确认合并
[root@db01 ~]# cat /backup/full2020-11-05/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 2327902069 #全备的值等于第一次增备的值
5.第三步:将 twoadd 合并到 full 中,redo和undo都应用
[root@db01 ~]# innobackupex --apply-log --incremental-dir=/backup/twoadd/ /backup/full2020-11-05
6.再次确认合并
[root@db01 ~]# cat /backup/full2020-11-05/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 2327903988
7.第四步:整体full执行apply-log,redo和undo都应用
[root@db01 mysql]# innobackupex --apply-log /backup/full2020-11-05
[root@db01 ~]# systemctl stop mysqld
[root@db01 ~]# mv /usr/local/mysql/data /usr/local/mysql/databack
[root@db01 ~]# innobackupex --copy-back /backup/full2020-11-05
[root@db01 ~]# chown -R mysql.mysql /usr/local/mysql/data
[root@db01 ~]# systemctl start mysqld
二、mysql主从复制
1.主从复制主库的操作
1.主库配置server_id
2.主库开启binlog
3.授权主从用户
4.主库查看binlog信息
2.主从复制从库的操作
1.配置server_id,与主库不同
2.先同步主库数据
3.配置主从
change master to
master_host=172.16.1.51,
master_user=rep,
master_password=123,
master_port=3306,
master_log_file=mysql-bin.000001,
master_log_pos=120;
4.开启主从
3.主从复制图解
4.文字描述
1.主库配置server_id和binlog
2.主库授权主从用户
3.主库查看binlog信息
4.从库配置server_id,只要跟主库不同就可以(只做主从的情况下)
5.从库执行change master to语句
6.从库开启IO和sql线程(start slave)
7.从库的IO线程会去连接主库的dump线程,问问是否有比从库中master.info记录的更新的位置点
8.主库dump线程被询问,dump线程会去查询binlog
9.dump线程会将查到的新数据从binlog中截取出来交给从库的IO线程
10.IO线程拿到新的binlog数据,会将内容存储到TCP/IP缓存中
11.TCP/IP缓存会返回给IO线程一个ACK
12.IO线程会更新master.info,将新的位置点更新到文件中,然后继续去主库获取数据
13.SQL线程会去读取relay-log.info中的位置点,去对比relay-log的数据,如果有比relay-log.info中的位置点新的数据,则取出并执行,如果没有则不执行
14.SQL线程执行完操作,会将新的位置点更新到relay-log.info中
5.主从复制涉及到的文件
1)主库
1.主库的binlog:记录的是数据库的操作
2.dump线程:对比binlog,取出新binlog中的数据,并将数据给到IO线程
2)从库
1.IO线程:连接主库,接收主发送过来的新binlog数据
2.sql线程:执行relay-log中主库传过来的新数据
3.relay-log(中继日志):存储所有主库传过来的新数据
4.master.info:记录上一次请求到主库返回的binlog信息,下一才从该位置点读取对比数据
5.relay-log.info:记录上一次sql线程执行到relay-log中的位置点,下一次从该位置点执行
三、主从复制的搭建
1.主库操作
1)配置主库
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=mysql-bin
2)授权一个主从用户
mysql> grant replication slave on *.* to rep@'172.16.1.%' identified by '123';
#grant replication slave 是一个全局授权,不能指定单个库授权,如果想同步单个库使用过滤复制
3)查看主库的binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 326 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
2.从库操作
1)配置从库
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
2)配置主从复制
change master to
master_host='172.16.1.51',
master_user='rep',
master_password='123',
master_port=3306,
master_log_file='mysql-bin.000001',
master_log_pos=326;
3)开启IO线程和sql线程
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
4)查看主从状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.51
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 326
Relay_Log_File: db02-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
四、主从复制故障
1.IO线程故障
#Slave_IO_Running: No 或者 Connecting
1.检测网络
[root@db02 ~]# ping 172.16.1.51
2.检测端口
[root@db02 ~]# telnet 172.16.1.51 3306
3.防火墙是否开启
4.主从的用户名或者密码错误
#测试使用主从用户的用户名和密码连接主库
[root@db02 ~]# mysql -urep -p123 -h172.16.1.51
5.反向解析
[root@db01 ~]# mysql -uroot -p123 -h172.16.1.51
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'db01' (using password: YES)
#解决
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
skip_name_resolve
2.sql线程故障
#Slave_SQL_Running: No 或者 Connecting
1.主库有的数据,从库没有
2.从库有的数据,主库没有
3.主库数据与从库不一致
1)解决办法一:(不认)
1.停止主从复制
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
2.跳过一个错误
mysql> set GLOBAL sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)
3.开启主从
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
2)解决办法二:
1.停止主从复制
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
2.清空主从复制的信息
mysql> reset slave all;
Query OK, 0 rows affected (0.00 sec)
3.同步主库所有数据
4.重新配置主从复制
3.在数据库运行过程中添加从库
1.准备从库
2.主库导出数据
#打点备份不注释
[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=1 --single-transaction > /tmp/full.sql
#打点备份注释
[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction > /tmp/full2.sql
3.将数据推送至从库
[root@db01 ~]# scp /tmp/full.sql 172.16.1.52:/tmp
[root@db01 ~]# scp /tmp/full2.sql 172.16.1.53:/tmp
4.模拟运行的主库数据写入
mysql> use master;
mysql> insert test values(2),(3);
mysql> insert test values(8),(9);
5.从库导入数据
[root@db02 ~]# mysql < /tmp/full.sql
[root@db03 ~]# mysql < /tmp/full2.sql
6.查看主从的位置点
[root@db01 ~]# less /tmp/full.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=520;
[root@db01 ~]# less /tmp/full2.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=520;
7.从库配置主从复制
change master to
master_host='172.16.1.51',
master_user='rep',
master_password='123',
master_port=3306,
master_log_file='mysql-bin.000008',
master_log_pos=120;