mysql数据库备份与还原_MySQL数据库备份和还原

MySQL数据库备份和还原

以下方法前景必须是一台用yum源安装的mysql数据库没操作过的崭新的服务器

*查看是否启用二进制日志：show master logs;/show binary logs;

*创建一个日志文件专门存放二进制日志:mkdir /data/logbin

*修改配置文件:vim /etc/my.cnf

*设置权限所有者和所属组:chown mysql.mysql /data/logbin/

*重新启动mysql服务:systemctl restart mariadb

*查看文件夹是否生成二进制文件:ll /data/logbin(上面是二进制文件，下面是索引)

*再次查看二进制文件是否启动:show master logs;(默认大小245)|show master status;(position默认大小)

*刷新日志(也是创建日志):flush logs;

*清除指定的二进制日志:purge binary logs to '删除文件之前的位置';(删除之前的文件不包括本身)

*删除所有的二进制文件从新计数:reset master;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

冷备份

*停止服务:systemctl stop mariadb;

*查看目录:ls /var/lib/mysql/(mysql.sock文件不存在了)

*压缩数据:tar Jcvf all.bak.tar.xz /var/lib/mysql(打包数据库)

tar Jcvf logbin.tar.xz(打包二进制文件)

/etc/my.cnf(直接拷贝)

*创建一个目录存放压缩数据:mkdir backup

mv all.bak.tar.xz logbin.tar.xz /backup/

cp /etc/my.cnf /backup/

*拷贝到另一台机器上:scp -r /backup/ 192.168.198.17:/data/(本机地址为192.168.198.7)

*打开ip为192.168.198.17的服务器(该机器未安装过mysql),查看传送过来的文件:ls /data

*yum安装数据库:yum install mariadb-server -y

*查看数据库:ls /var/lib/mysql(该目录是空的)

*覆盖配置文件:cp /data/backup/my.cnf  /etc/my.cnf -b(-b是备份)

*查看备份的my.cnf文件:ll /etc/my.cnf(my.cnf~备份文件)

*创建二进制文件,改变所有者和所属组:mkdri /data/logbin/  chown mysql.mysql /data/logbin/

*解压二进制文件压缩包:tar -xvf /data/backup/logbin.tar.xz -C /data/logbin

*进入目录查看:cd /data/logbin(这是这个目录不对)mv data/logbin/* .(把数据移出)rm -rf data(删除data目录)

*解压mysql压缩包然后移动到/var/lib/mysql中:cd /data/backup/(进入) tar xvf all.bak.tar.xz(解压在当前目录) cd var/lib/mysql(进入) mv * /var/lib/mysql(移动全部数据到该目录)

*启动数据库:systemctl start mariadb;(启动之后进入mysql查看即可)

----------------------------------------------------------------------------------------------------------------------------------------------------------

mysqldump

一、mysqldump [options] database [tables](备份一个数据库此法问题较多不推荐使用)

*创建一个备份目录:mkdir /data/backup/

*复制整个数据库成sql语句:mysqldump hellodb > /data/backup/hellodb.sql

*删除该数据库:mysql -e 'drop database hellodb';

*还原数据库得先创建该数据库:create database hello;(mysqldump恢复数据时不具有自动创建数据库的功能需要手工创建,创建的新数据库名字可以改变)

*还原数据:mysql hello < /data/backup/hellodb.sql

*进入数据库查看是否还原即可

二、mysqldump [options] -B DB1[DB2 DB3...](备份部分数据库)

*创建一个备份目录:mkdir /data/backup

*备份数据库:mysqldump -B hello[后面可以跟多个数据库] > /data/backup/hello.sql

*删除数据库:mysql -e 'drop database hello';

*还原数据:mysql < /data/backup/hello.sql

*进入数据库查看即可

三、mysqldump [options] -A [options](备份全部数据库)

*创建一个备份目录:mkdir /data/backup

*备份数据库:mysqldump -A > /data/backup/all.sql

*删除数据库

*还原数据:mysql < /data/backup/all.sql

*进入数据库查看即可

四、mysqldump --master-data[=#](二进制日志还原)

*创建备份存放目录:mkdir /data/backup/

*备份为注释的CHANGE MASTER TO语句:mysqldump -A --master-data=2 > /data/backup/all.sql

*查看现在的二进制文件:mysql -e 'show master logs';

*随便写一条sql语句即可

*再次查看二进制文件会发现文件变大:mysql -e 'show master logs';

*开始删库:rm -rf /var/lib/mysql/*(啥也没了)

*重启数据库服务:systemctl restart mariadb(会重新生成mysql文件)

*恢复数据库时禁止用户访问,在配置文件修改:vim /etc/my.cnf 添加:skip_networking 不允许网络连接或者skip_grant_tables跳过授权表只是服务器选项不是变量

*临时关闭二进制文件不让他生效:show variables like 'sql_log_bin'默认是NO,set sql_log_bin=OFF即可

*再次查看二进制文件:show master logs;发现他多生成出几个文件

*我要还原图中数据即可(只是我自己),可以查看备份的文件得出依据从哪里开始还原

*进入二进制文件目录备份丢失的数据,如下图所示

*导入还原数据:进入mysql输入source /data/backup/all.sql(先还原全部备份)source /data/backup/inc.sql(还原丢失数据)

*进入数据库查看即可

*恢复二进制数据记录:set sql_log_bin=on;

五、恢复误删的表

*创建数据备份的目录:mkdri /data/backup/

*备份二进制数据:mysqldump -A --master-data=2 > /data/backup/all_`date +%F`.sql

*随便写一条sql语句

*删表:drop table students;

*刷新日志,找出丢失的日志文件

*刷新丢失的二进制文件:mysqlbinlog  --start-position=245 /data/logbin/mysql-bin.000005 > /data/backup/inc.sql

*打开配置文件vim /data/backup/inc.sql,找到删除命令注销

*删除数据库:rm -rf /var/bin/mysql/*

*重启服务器:systemctl restart mariadb

*进入数据库停止二进制服务:set sql_log_bin=off;

*导入还原数据:进入mysql输入source /data/backup/all_2019_7_11.sql(先还原全部备份)source /data/backup/inc.sql(还原丢失数据)

*查看数据:执行一条查询语句(即可成功)