linux系统mysql数据库备份及主从基础(1) [数据库教程]
一、物理备份Xtrabackup
1.安装
#上传文件包[[email protected] ~]# 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
#安装
[[email protected] ~]# yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
#安装好后的命令
[[email protected] ~]# xtrabackup
[[email protected] ~]# innobackupex
2.Xtrabackup备份
1）对于非innodb表（比如myisam）是直接锁表cp数据文件，属于一种温备。2）对于innodb的表（支持事务），不锁表，cp数据页最终以数据文件方式保存下来，并且把redo和undo一并备走，属于热备方式。
3）备份时读取配置文件/etc/my.cnf（如果使用Xtrabackup备份，必须要配置datadir）
3.Xtrabackup全量备份
1）准备备份目录
[[email protected] ~]# mkdir /backup2）备份(全备)
[[email protected] ~]# innobackupex --user=root --password=123 /backup/full#去掉时间戳进行备份
[[email protected] ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full
3）查看全备内容
[[email protected] ~]# 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
[[email protected] 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）停止数据库
[[email protected] ~]# systemctl stop mysqld.service3）手动模拟CSR的过程
#将redo进行重做，已提交的写到数据文件，未提交的使用undo回滚，模拟CSR的过程[[email protected] ~]# innobackupex --user=root --password=123 --apply-log /backup/full
4）恢复数据
1>方法一：
#移走原数据目录[[email protected] mysql]# mv data data.back
#将全备的数据目录迁移回来
[[email protected] mysql]# cp -r /backup/full ./data
[[email protected] mysql]# chown -R mysql.mysql data
2>方法二：
#使用innobackupex恢复数据[[email protected] mysql]# innobackupex --copy-back /backup/full/
[[email protected] mysql]# chown -R mysql.mysql data
5）启动数据库查看数据
#启动数据库[[email protected] data]# systemctl start mysqld
[[email protected] data]# mysql -uroot -p123
mysql> show databases;
5.Xtrabackup增量备份
1.基于上一次备份进行增量2.增量备份无法单独恢复，必须基于全备进行恢复
3.所有增量必须要按顺序合并到全备当中
1）先全备
[[email protected] ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full_$(date +%F)2）写入新数据
[[email protected] ~]# mysql -uroot -p123mysql> use dump
mysql> insert dump values(10000),(20000),(30000);
3）第一次增备
[[email protected] ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full_2020-07-23 /backup/inc1参数说明:
--incremental：开启增量备份功能
--incremental-basedir：上一次备份的路径
#验证
[[email protected] ~]# cat /backup/full/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 8417759
[[email protected] ~]# cat /backup/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 8417759
to_lsn = 8419281
4）再次写入数据
[[email protected] ~]# mysql -uroot -p123mysql> use dump
mysql> insert dump values(100000),(200000),(300000);
5）第二次增备
[[email protected] ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc1/ /backup/inc2#验证
[[email protected] backup]# cat /backup/full_2020-07-23/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 1636167
[[email protected] backup]# cat /backup/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1636167
to_lsn = 1640828
[[email protected] backup]# cat /backup/inc2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1640828
to_lsn = 1645877
6）再次写入数据
7）第三次增量备份
[[email protected] backup]# innobackupex --user=root --no-timestamp --incremental --incremental-basedir=/backup/inc2 /backup/inc36.Xtrabackup增量恢复数据
1）将全备执行redo
[[email protected] backup]# innobackupex --apply-log --redo-only /backup/full_2020-07-232）将第一次增备只执行redo并合并到第一次全备
[[email protected] backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1/ /backup/full_2020-07-23#验证
[[email protected] backup]# cat /backup/full_2020-07-23/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 1640828 #该值本来是inc1的位置点
3）将第二次增备只执行redo并合并到第一次全备
[[email protected] backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc2/ /backup/full_2020-07-23#验证
[[email protected] backup]# cat /backup/full_2020-07-23/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 1645877 #该值本来是inc2的位置点
4）将最后一次增备执行redo和undo并合并到第一次全备
[[email protected] backup]# innobackupex --apply-log --incremental-dir=/backup/inc3/ /backup/full_2020-07-23#验证
[[email protected] backup]#
[[email protected] backup]# cat /backup/full_2020-07-23/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 1649869
5）将整体数据进行一次CSR
[[email protected] backup]# innobackupex --apply-log /backup/full_2020-07-23/6）恢复数据
[[email protected] mysql]# mv data data.bak[[email protected] mysql]# innobackupex --copy-back /backup/full_2020-07-23/
[[email protected] mysql]# chown -R mysql.mysql data
[[email protected] mysql]# systemctl start mysqld
7.总结
1.增备： 优点：占用磁盘空间小，没有重复数据
缺点：恢复麻烦
2.全备：
优点：恢复只需一次
缺点：占用磁盘空间，每次全备都有重复数据
思考
企业级增量恢复实战背景：
某大型网站，mysql数据库，数据量500G，每日更新量100M-200M
备份策略：
xtrabackup，每周六0:00进行全备，周一到周五及周日00:00进行增量备份。
故障场景：
周三下午2点出现数据库意外删除表操作。
二、mysql主从
1.主库操作
1.主库配置server_id2.主库开启binlog
3.主库授权从库连接的用户
4.查看binlog信息
5.导出所有数据
2.从库操作
1.从库配置server_id（跟主库不一致）2.确认主库授权的用户可以连接主库
3.同步主库数据
4.配置主库信息（change master to）
5.开启slave
3.主从复制原理
1）图解
2）文字描述
1.主库配置server_id和开启binlog2.主库授权从库连接的用户
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）配置
[[email protected] ~]# vim /etc/my.cnf[mysqld]
server_id=1
log_bin=/service/mysql/data/mysql-bin
[[email protected] ~]# /etc/init.d/mysqld start
2）授权一个用户
mysql> grant replication slave on *.* to [email protected]‘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）导出所有数据
[[email protected] data]# mysqldump -uroot -p -A --master-data=2 --single-transaction > /tmp/full.sql[[email protected] data]# scp /tmp/full.sql 172.16.1.52:/tmp/
2.从库操作
1）配置
[[email protected] ~]# vim /etc/my.cnf[mysqld]
server_id=2
[[email protected] ~]# /etc/init.d/mysqld start
2）验证主库用户
[[email protected] ~]# mysql -urep -p -h172.16.1.533）同步数据
[[email protected] ~]# mysql -uroot -p123 < /tmp/full.sql4）配置主从
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 statusG Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3.主从数据库出错
1）IO线程出错
mysql> show slave statusG Slave_IO_Running: No
Slave_SQL_Running: Yes
mysql> show slave statusG
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
#排查思路
1.网络
[[email protected] ~]# ping 172.16.1.53
2.端口
[[email protected] ~]# telnet 172.16.1.53 3306
3.防火墙
4.主从授权的用户错误
5.反向解析
skip-name-resolve
6.UUID或server_id相同
2）SQL线程出错
mysql> show slave statusG 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.编辑配置文件
[[email protected] ~]# vim /etc/my.cnf
#在[mysqld]标签下添加以下参数
slave-skip-errors=1032,1062,1007
#处理方式三：正解
重新同步数据，重新做主从
linux系统mysql数据库备份及主从基础(1)
以上是 linux系统mysql数据库备份及主从基础(1) [数据库教程] 的全部内容， 来源链接： utcz.com/z/534790.html
