linux mysql数据库备份_linux系统mysql数据库备份及主从基础(1)

一、物理备份Xtrabackup

1.安装

#上传文件包

[root@db03 ~]# rz percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

#下载epel源

wget -O /etc/yum.repos.d/epel.repo https://mirrors.aliyun.com/repo/epel-6.repo

#安装依赖

yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL

#下载Xtrabackup

wget httpss://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

#安装

[root@db03 ~]# yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

#安装好后的命令

[root@db03 ~]# xtrabackup

[root@db03 ~]# innobackupex

2.Xtrabackup备份

1)对于非innodb表(比如myisam)是直接锁表cp数据文件，属于一种温备。

2)对于innodb的表(支持事务)，不锁表，cp数据页最终以数据文件方式保存下来，并且把redo和undo一并备走，属于热备方式。

3)备份时读取配置文件/etc/my.cnf(如果使用Xtrabackup备份，必须要配置datadir)

3.Xtrabackup全量备份

1)准备备份目录

[root@db03 ~]# mkdir /backup

2)备份(全备)

[root@db03 ~]# innobackupex --user=root --password=123 /backup/full

#去掉时间戳进行备份

[root@db03 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full

3)查看全备内容

[root@db03 ~]# ll /backup/full/

总用量 129052

-rw-r----- 1 root root 434 7月 23 08:51 backup-my.cnf

drwxr-x--- 2 root root 68 7月 23 08:51 dump

-rw-r----- 1 root root 79691776 7月 23 08:51 ibdata1

-rw-r----- 1 root root 52428800 7月 23 08:51 ibdata2

drwxr-x--- 2 root root 4096 7月 23 08:51 mysql

drwxr-x--- 2 root root 4096 7月 23 08:51 performance_schema

drwxr-x--- 2 root root 68 7月 23 08:51 row

drwxr-x--- 2 root root 20 7月 23 08:51 test

-rw-r----- 1 root root 21 7月 23 08:51 xtrabackup_binlog_info#记录binlog的信息

-rw-r----- 1 root root 113 7月 23 08:51 xtrabackup_checkpoints

-rw-r----- 1 root root 483 7月 23 08:51 xtrabackup_info#工具或数据的信息

-rw-r----- 1 root root 2560 7月 23 08:51 xtrabackup_logfile#redo-log

[root@db03 full]# cat xtrabackup_checkpoints

backup_type = full-backuped

from_lsn = 0

to_lsn = 8417759

4.Xtrabackup全备恢复数据

1)删除所有数据库

mysql> drop database dump;

mysql> drop database performance_schema;

mysql> drop database row;

mysql> drop database test;

2)停止数据库

[root@db03 ~]# systemctl stop mysqld.service

3)手动模拟CSR的过程

#将redo进行重做，已提交的写到数据文件，未提交的使用undo回滚，模拟CSR的过程

[root@db03 ~]# innobackupex --user=root --password=123 --apply-log /backup/full

4)恢复数据

1>方法一：

#移走原数据目录

[root@db03 mysql]# mv data data.back

#将全备的数据目录迁移回来

[root@db03 mysql]# cp -r /backup/full ./data

[root@db03 mysql]# chown -R mysql.mysql data

2>方法二：

#使用innobackupex恢复数据

[root@db03 mysql]# innobackupex --copy-back /backup/full/

[root@db03 mysql]# chown -R mysql.mysql data

5)启动数据库查看数据

#启动数据库

[root@db03 data]# systemctl start mysqld

[root@db03 data]# mysql -uroot -p123

mysql> show databases;

5.Xtrabackup增量备份

1.基于上一次备份进行增量

2.增量备份无法单独恢复，必须基于全备进行恢复

3.所有增量必须要按顺序合并到全备当中

1)先全备

[root@db03 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full_$(date +%F)

2)写入新数据

[root@db03 ~]# mysql -uroot -p123

mysql> use dump

mysql> insert dump values(10000),(20000),(30000);

3)第一次增备

[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full_2020-07-23 /backup/inc1

参数说明:

--incremental：开启增量备份功能

--incremental-basedir：上一次备份的路径

#验证

[root@db03 ~]# cat /backup/full/xtrabackup_checkpoints

backup_type = full-prepared

from_lsn = 0

to_lsn = 8417759

[root@db03 ~]# cat /backup/inc1/xtrabackup_checkpoints

backup_type = incremental

from_lsn = 8417759

to_lsn = 8419281

4)再次写入数据

[root@db03 ~]# mysql -uroot -p123

mysql> use dump

mysql> insert dump values(100000),(200000),(300000);

5)第二次增备

[root@db03 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc1/ /backup/inc2

#验证

[root@db03 backup]# cat /backup/full_2020-07-23/xtrabackup_checkpoints

backup_type = full-backuped

from_lsn = 0

to_lsn = 1636167

[root@db03 backup]# cat /backup/inc1/xtrabackup_checkpoints

backup_type = incremental

from_lsn = 1636167

to_lsn = 1640828

[root@db03 backup]# cat /backup/inc2/xtrabackup_checkpoints

backup_type = incremental

from_lsn = 1640828

to_lsn = 1645877

6)再次写入数据

7)第三次增量备份

[root@db03 backup]# innobackupex --user=root --no-timestamp --incremental --incremental-basedir=/backup/inc2 /backup/inc3

6.Xtrabackup增量恢复数据

1)将全备执行redo

[root@db03 backup]# innobackupex --apply-log --redo-only /backup/full_2020-07-23

2)将第一次增备只执行redo并合并到第一次全备

[root@db03 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1/ /backup/full_2020-07-23

#验证

[root@db03 backup]# cat /backup/full_2020-07-23/xtrabackup_checkpoints

backup_type = log-applied

from_lsn = 0

to_lsn = 1640828#该值本来是inc1的位置点

3)将第二次增备只执行redo并合并到第一次全备

[root@db03 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc2/ /backup/full_2020-07-23

#验证

[root@db03 backup]# cat /backup/full_2020-07-23/xtrabackup_checkpoints

backup_type = log-applied

from_lsn = 0

to_lsn = 1645877#该值本来是inc2的位置点

4)将最后一次增备执行redo和undo并合并到第一次全备

[root@db03 backup]# innobackupex --apply-log --incremental-dir=/backup/inc3/ /backup/full_2020-07-23

#验证

[root@db03 backup]#

[root@db03 backup]# cat /backup/full_2020-07-23/xtrabackup_checkpoints

backup_type = full-prepared

from_lsn = 0

to_lsn = 1649869

5)将整体数据进行一次CSR

[root@db03 backup]# innobackupex --apply-log /backup/full_2020-07-23/

6)恢复数据

[root@db03 mysql]# mv data data.bak

[root@db03 mysql]# innobackupex --copy-back /backup/full_2020-07-23/

[root@db03 mysql]# chown -R mysql.mysql data

[root@db03 mysql]# systemctl start mysqld

7.总结

1.增备：

优点：占用磁盘空间小，没有重复数据

缺点：恢复麻烦

2.全备：

优点：恢复只需一次

缺点：占用磁盘空间，每次全备都有重复数据

思考

企业级增量恢复实战

背景：

某大型网站，mysql数据库，数据量500G，每日更新量100M-200M

备份策略：

xtrabackup，每周六0:00进行全备，周一到周五及周日00:00进行增量备份。

故障场景：

周三下午2点出现数据库意外删除表操作。

二、mysql主从

1.主库操作

1.主库配置server_id

2.主库开启binlog

3.主库授权从库连接的用户

4.查看binlog信息

5.导出所有数据

2.从库操作

1.从库配置server_id(跟主库不一致)

2.确认主库授权的用户可以连接主库

3.同步主库数据

4.配置主库信息(change master to)

5.开启slave

3.主从复制原理

1)图解

2)文字描述

1.主库配置server_id和开启binlog

2.主库授权从库连接的用户

3.主库查看binlog信息，与服务器信息

4.从库配置跟主库不一致server_id

5.配置主从，通过change master to高速从库主库的信息：ip、用户、密码、端口、binlog位置点、binlog名字

6.从库开启IO线程和sql线程

7.从库连接主库以后，IO线程会向主库的dump线程发起询问，询问是否有新数据

8.dump线程被询问，去查找新数据，并将新数据返回给IO线程

9.IO线程拿到数据先写入TCP缓存

10.TCP缓存将数据写入中继日志，并返回给IO线程一个ACK

11.IO线程收到ACK会记录当前位置点到master.info

12.sql线程会读取relay-log，执行从主库获取的sql语句

13.执行完以后将执行到的位置点，记录到relay-log.info

4.主从中涉及到的文件或者线程

1)主库

1.binlog：主库执行的sql语句

2.dump线程：对比binlog是否更新，获取新的binlog

2)从库

1.IO线程：连接主库，询问新数据，获取新数据

2.SQL线程：执行从主库哪来的sql语句

3.relay-log：中继日志，记录从主库拿过来的binlog

4.master.info：记录主库binlog信息，会随着同步进行更新

5.relay-log.info：记录sql线程执行到了那里，下次从哪里开始执行

三、主从复制的搭建

1.主库操作

1)配置

[root@db03 ~]# vim /etc/my.cnf

[mysqld]

server_id=1

log_bin=/service/mysql/data/mysql-bin

[root@db03 ~]# /etc/init.d/mysqld start

2)授权一个用户

mysql> grant replication slave on *.* to rep@'172.16.1.%' identified by '123';

Query OK, 0 rows affected (0.03 sec)

3)查看binlog信息

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000003 | 326 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

4)导出所有数据

[root@db03 data]# mysqldump -uroot -p -A --master-data=2 --single-transaction > /tmp/full.sql

[root@db03 data]# scp /tmp/full.sql 172.16.1.52:/tmp/

2.从库操作

1)配置

[root@db02 ~]# vim /etc/my.cnf

[mysqld]

server_id=2

[root@db02 ~]# /etc/init.d/mysqld start

2)验证主库用户

[root@db02 ~]# mysql -urep -p -h172.16.1.53

3)同步数据

[root@db02 ~]# mysql -uroot -p123 < /tmp/full.sql

4)配置主从

mysql> change master to

-> master_host='172.16.1.53',

-> master_user='rep',

-> master_password='123',

-> master_log_file='mysql-bin.000003',

-> master_log_pos=326;

Query OK, 0 rows affected, 2 warnings (0.02 sec)

5)开启线程

mysql> start slave;

Query OK, 0 rows affected (0.04 sec)

6)查看主从

mysql> show slave status\G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

3.主从数据库出错

1)IO线程出错

mysql> show slave status\G

Slave_IO_Running: No

Slave_SQL_Running: Yes

mysql> show slave status\G

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

#排查思路

1.网络

[root@db02 ~]# ping 172.16.1.53

2.端口

[root@db02 ~]# telnet 172.16.1.53 3306

3.防火墙

4.主从授权的用户错误

5.反向解析

skip-name-resolve

6.UUID或server_id相同

2)SQL线程出错

mysql> show slave status\G

Slave_IO_Running: Yes

Slave_SQL_Running: No

#原因：

1.主库有的数据，从库没有

2.从库有的数据，主库没有

#处理方式一：自欺欺人

1.临时停止同步

mysql> stop slave;

2.将同步指针向下移动一个(可重复操作)

mysql> set global sql_slave_skip_counter=1;

3.开启同步

mysql> start slave;

#处理方式二：掩耳盗铃

1.编辑配置文件

[root@db01 ~]# vim /etc/my.cnf

#在[mysqld]标签下添加以下参数

slave-skip-errors=1032,1062,1007

#处理方式三：正解

重新同步数据，重新做主从