MySQL备份与恢复

一

1.创建新的库

mysql -u root
create database client;
use client; ##切换到所使用的库

2.创建表

create table user(身份证 char(20) not null,姓名 char(20)not null,性别
 char(4));

3.写入数据
多创建几个

 insert into user values('000001','张三','男');

4.查看库

 show databases;

5.查看表

 select * from user;

6.备份整个数据库

 /usr/local/mysql/bin/mysqld.sh  stop
 mkdir /backup
 tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data

7.模拟故障

 mkdir bak
 mv /usr/lical/mysql/data/ /bak
 /usr/local/mysql/bin/mysqld.sh start

8.完整恢复

 mkdir restore
  tar zxf /backup/mysql_all-2020-12-16.tar.gz -C restore/
  mv restore/usr/local/mysql/data/ /usr/local/mysql/
   /usr/local/mysql/bin/mysqld.sh start
   mysql -u root

9.MySQL增量备份-二进制

vim /tec/my.cnf

添加

log-bin=/usr/local/mysql/mysql-bin
/usr/local/mysql/bin/mysqld.sh restart
ls -l /usr/local/mysql/mysql-bin.*

10进行MySQL库完全备份

mkdir /mysql_bak
mysqldump -uroot -p client user>/mysql_bak/client_userinfo-$(data +%F).sql  
ls /mysql_bak/
mysqladmin -u root flush-logs
ls -l /usr/local/mysql/mysql-bin.*
cp /usr/local/mysql/mysql-bin.000002 /mysql_bak

模拟删除user表

mysql -u root -e 'drop table client.user;'
 mysql -u root -e 'select * from client.user;'

恢复完全备份的库

mysql -u root client < /mysql_bak/client_userinfo-2020-12-15.sql
mysql -u root -e 'select * from client.user;'

恢复增量备份

 mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 | mysql -u root
  mysql -u root -e 'select * from client.user;'