mysql 误删找回_mysql 删除恢复

一、模拟误删除数据表的恢复

1 二进制日志功能启用

vim /etc/my.cnf

[mysqld]

log-bin

2  完全备份

mysqldump -A -F --master-data=2 --single-transaction |gzip > /data/all.sql.gz

3 修改数据表

insert students (name,age)values('a',20); 修改第一次

insert students (name,age)values('b',30); 修改第二次

flush logs; 切换二进制日志

insert students (name,age)values('e',30); 修改第一次

4 误删除数据表

drop table students; 误删除table

5 用户继续修改其它表

insert teachers (name,age)values('mage',30); 误删除后继续修改

6 发现表删除

flush tables with read lock; 发现表被删除，第一时间锁定表格，设为只读

7 干净系统还原

systemctl stop mairadb 暂停服务

rm -rf /var/lib/mysql/ 删除config中标明的datadir

确保用户不能访问服务器

vim /etc/my.cnf

[mysqld]

skip-networking=1

systemctl start mariadb

8 完全备份还原

gzip -d /data/all.sql.gz 解压完全备份的/data/all.sql.gz

mysql < /data/all.sql 导入完全备份的all.sql

9 还原增量备份

less /data/all.sql //查看二进制日志位置

-- CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=245; //查看完全备份信息，好确定二进制恢复的位置

//找到mariadb-bin.000003 245之后的所有二进制日志

mariadb-bin.000002 mariadb-bin.000004

mysqlbinlog --start-position=245 /var/lib/mysql/mariadb-bin.000002 > /data/binlog.sql

mysqlbinlog /var/lib/mysql/mariadb-bin.000003 >> /data/binlog.sql

vim /data/binlog.sql

//找到drop table students;删除此行

mysql < /data/binlog.sql

10恢复用户的访问

vim /etc/my.cnf

[mysqld]

skip-networking=1 删除此行

##mysql -e "show databases" |grep -Ev "Database|information_schema|performance_schema"|sed -r 's#(.*)#mysqldump -B \1 |gzip > /data/\1-`date +%F`.sql.gz#' |bash

备份所有databases中除information_schema&performance_schema的数据库

二、xtrabackup完全备份和还原

1 在原主机完全备份

xtrabackup --backup --target-dir=/backups/ 备份源主机到/backups

scp -r /backups/ 192.168.134.192:/

2 在目标主机

systemctl stop mariadb //停止恢复主机的mariadb服务

mkdir /data/mysql -pv

chown mysql.mysql /data/mysql/ //确保目录权限没问题

vim /etc/my.cnf

datadir=/data/mysql/ //修改配置

xtrabackup --prepare --target-dir=/backups //恢复前预处理

xtrabackup --copy-back|move-back --target-dir=/backups //将预处理过的backups目录复制或者移动到datadir

chown -R mysql.mysql /data/mysql/ //确保所有mysql下的所有文件都是mysql的用户与组

systemctl restart mariadb //完工

三、xtrabackup完全，增量备份和还原

1在原主机上实现

1)完全备份

mkdir /backups/{full,inc1,inc2} -pv

xtrabackup --backup --target-dir=/backups/full/

2)第一次修改数据

mysql hellodb -e ”insert hellodb.students (name,age) values('a',18)“

3)第一次增量备份

xtrabackup --backup --target-dir=/backups/inc1/ --incremental-basedir=/backups/full

4)第二次修改数据

mysql -e "insert hellodb.students (name,age) values('b',28);"

5)第二次增量备份

xtrabackup --backup --target-dir=/backups/inc2/ --incremental-basedir=/backups/inc1/

scp -r /backups/ 目标主机:/

2在目标主机还原

1)清空数据，停服务

systemctl stop mariadb

rm -rf /data/mysql/*

2)预处理数据库

xtrabackup --prepare --apply-log-only --target-dir=/backups/full

xtrabackup --prepare --apply-log-only --target-dir=/backups/full --incremental-dir=/backups/inc1/

xtrabackup --prepare --target-dir=/backups/full --incremental-dir=/backups/inc2

3)复制数据

xtrabackup --copy-back --target-dir=/backups/full

4)权限和启动服务

chown -R mysql.mysql /data/mysql/

systemctl start mariadb

mysql实现主从-级联复制

MySQL主从结构实际中是用到最多的一种架构。 新上的两台服务器B和C，要替换掉之前旧的服务器A，同时，B和C是新的主从关系。因此，配置成级联复制，来迁移数据，也方便切换。

架构图如下：

master A ------> slave B(到此为主从) ------> slave C

MySQL复制

复制过滤器：

让从节点仅复制指定的数据库，或指定数据库的指定表

两种实现方式：

(1) 服务器选项：主服务器仅向二进制日志中记录与特定数据库相关的事件

注意：此项和binlog_format相关

参看：https://mariadb.com/kb/en/library/mysqld-options/#-binlogignore-db

binlog_do_db = 数据库白名单列表，多个数据库需多行实现

binlog_ignore_db = 数据库黑名单列表

问题：基于二进制还原将无法实现；不建议使用

(2) 从服务器SQL_THREAD在replay中继日志中的事件时，仅读取与特定数

据库(特定表)相关的事件并应用于本地

问题：会造成网络及磁盘IO浪费

MySQL复制

从服务器上的复制过滤器相关变量

replicate_do_db= 指定复制库的白名单

replicate_ignore_db= 指定复制库黑名单

replicate_do_table= 指定复制表的白名单

replicate_ignore_table= 指定复制表的黑名单

replicate_wild_do_table= foo%.bar% 支持通配符

replicate_wild_ignore_table=

实验机器ip

192.168.134.191 主服务器>192.168.134.192 从服务器 (主从复制)

一、主节点配置：

(1) 启用二进制日志

vim /etc/my.cnf

[mysqld]

log_bin

#log_bin启用二进制日志

(2) 为当前节点设置一个全局惟一的ID号

vim /etc/my.cnf

[mysqld]

server_id=1

(3) 创建有复制权限的用户账号

mysql > GRANT REPLICATION SLAVE ON *.* TO 'wang'@'HOST' IDENTIFIED BY 'centos';

二、从节点配置：

(1) 启动中继日志

vim /etc/my.cnf

[mysqld]

server_id=2

(2) 使用有复制权限的用户账号连接至主服务器，并启动复制线程

mysql > CHANGE MASTER TO

MASTER_HOST='192.168.134.191',

MASTER_USER='wang',

MASTER_PASSWORD='centos',

MASTER_PORT=3306,

MASTER_LOG_FILE='mariadb-bin.000001',

MASTER_LOG_POS=245;

(3) 启动slave

mysql > START SLAVE

这个时候mysql的主从复制就完成了。

如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点

实验机器：192.168.134.191 主服务器>192.168.134.192 从服务器>192.168.134.193 N从服务器(级联复制)

通过备份恢复数据至从服务器

复制起始位置为备份时，二进制日志文件及其POS

如果要启用级联复制,需要在从服务器(192.168.134.192)用以下配置

vim /etc/my.cnf

[mysqld]

log_bin 启用二进制日志

log_slave_updates

#一般情况下slave不会把从master接收到的binlog记录写入自己的binlog，这个参数会使slave通过SQL线程把从master接受到的binlog写进自己的binlog，但是前提是slave一定要开启自己的binlog，此参数一般用于级联复制，例如需要A复制到B，B复制到C，那么B就要开启此参数。

mysql半同步实现

主服务配置:mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';mysql> SET GLOBAL rpl_semi_sync_master_enabled=1;mysql> SHOW VARIABLES LIKE '%semi%';mysql> SHOW STATUS LIKE '%semi%‘;从服务器配置:mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1;