• 幕客老师召集小伙伴
  • python自动化运维项目实战
  • nginx从入门到实战
  • 阿里云与Centos7实战

MySQL数据备份

MySQL数据备份

========================================================
物理备份: 直接复制数据库文件,适用于大型数据库环境。但不能恢复到异构系统中如Windows。
逻辑备份: 备份的是建表、建库、插入等操作所执行SQL语句,适用于中小型数据库,效率相对较低。
导出表:    将表导入到文本文件中。


一、使用mysqldump实现逻辑备份

语法:
# mysqldump  -h 服务器  -u用户名  -p密码   数据库名  > 备份文件.sql
===数据库名
        –all-databases                        所有库
        school                                    数据库名
        school stu_info t1                    school数据库的表stu_info、t1
        –databases bbs test mysql    多个数据库

示例:
 

1
2
3
4
5
6
7
单库备份
# mysqldump -uroot -p123 bbs > bbs.sql
# mysqldump -uroot -p123 bbs table1 table2 > bbs-table1-table2.sql
多库备份
# mysqldump -uroot -p123 --databases bbs test mysql > bbs_test_mysql.sql
备份所有库
# mysqldump -uroot -p123 --all-databases > all.sql


二、恢复逻辑备份

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
方法一:
[root@yang backup]# mysql -uroot -p123 -h 192.168.2.115 < /backup/all.sql
 
方法二:
mysql> use bbs;
mysql> SET SQL_LOG_BIN=0;
mysql> source /root/bbs.sql
 
 
注1:如果备份/恢复单个库时,可以修改sql文件
DROP database if exists school;
create database school;
use school;
 
注2:对于有innodb引擎的表恢复时的注意事项:
mysql> create database school;
Query OK, 1 row affected (0.00 sec)
 
mysql> drop database school;
Query OK, 0 rows affected (0.00 sec)


三、备份/恢复案例


数据库备份/恢复实验一:数据库损坏
 

1
2
3
4
5
6
7
8
9
10
11
备份:
1.  mysqldump -uroot -p123 --all-database > /backup/`date +%F`_all.sql
2.  mysql -uroot -p123 -e 'flush logs'                                  //截断并产生新的binlog
3.  插入数据                                                                    //模拟服务器正常运行
4. mysql> set sql_log_bin=0;                                            //模拟服务器损坏
    mysql> drop database bbs;
   
恢复:
1.  mysql> set sql_log_bin=0;           
     mysql> source /backup/2014-02-13_all.sql                    //恢复最近一次完全备份           
2.  mysqlbinlog 最后一个binlog  |mysql -uroot -p123            //恢复最后个binlog文件


数据库备份/恢复实验二:如果有误删除
 

1
2
3
4
5
6
7
8
9
10
11
备份:
1.  mysqldump -uroot -p123 --all-database > /backup/`date +%F`_all.sql
2.  mysql -uroot -p123 -e 'flush logs'                                  //截断并产生新的binlog
3.  插入数据                                                                    //模拟服务器正常运行
4.  drop table db1.t1                                                        //模拟误删除
 
恢复:
1.  mysql> set sql_log_bin=0;           
     mysql> source /backup/2014-02-13_all.sql                                            //恢复最近一次完全备份           
2.  mysqlbinlog 最后一个binlog --stop-position=260  |mysql -uroot -p123    //恢复最后个binlog文件   
     mysqlbinlog 最后一个binlog --start-position=260  |mysql -uroot -p123   

注意事项:
1. 完全恢复到一个干净的环境(例如新的数据库或删除原有的数据库)
2. 恢复期间所有SQL语句不应该记录到binlog中


四、实现自动化备份


========================================================
备份计划:
1. 什么时间  2:00
2. 对哪些数据库备份
3. 备份文件放的位置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
备份脚本:
[root@yang ~]# vim /mysql_back.sql
#!/bin/bash
back_dir=/backup
back_file=`date +%F`_all.sql
user=root
pass=123
 
if [ ! -d /backup ];then
        mkdir -p /backup
fi
 
# 备份并截断日志
mysqldump -u${user} -p${pass} --events --all-databases > ${back_dir}/${back_file}
mysql -u${user} -p${pass} -e 'flush logs'
 
# 只保留最近一周的备份
cd $back_dir
find . -mtime +7 -exec rm -rf {} \;
 
手动测试:
[root@yang ~]# chmod a+x /mysql_back.sql
[root@yang ~]# chattr +i /mysql_back.sql
[root@yang ~]# /mysql_back.sql
 
配置cron
[root@yang ~]# crontab -l
0 2 * * * /mysql_back.sql

 

五、表的导出和导入


 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT... INTO OUTFILE 导出文本文件
示例:
mysql> SELECT * FROM school.student1
            INTO OUTFILE 'student1.txt'
            FIELDS TERMINATED BY ','                //定义字段分隔符
            OPTIONALLY ENCLOSED BY '”'            //定义字符串使用什么符号括起来
            LINES TERMINATED BY '\n' ;            //定义换行符
 
 
mysql 命令导出文本文件
示例:
# mysql -u root -p123 -e 'select * from student1.school' > /tmp/student1.txt
# mysql -u root -p123 --xml -e 'select * from student1.school' > /tmp/student1.xml
# mysql -u root -p123 --html -e 'select * from student1.school' > /tmp/student1.html
 
LOAD DATA INFILE 导入文本文件
mysql> DELETE FROM student1;
mysql> LOAD DATA INFILE '/tmp/student1.txt'
            INTO TABLE school.student1
            FIELDS TERMINATED BY ','
            OPTIONALLY ENCLOSED BY '”'
            LINES TERMINATED BY '\n';


六、数据库迁移

相同版本之间迁移
# mysqldump -h 迁移源 IP -uroot -p123 –databases bbs | mysql -h 目标 IP -uroot -p456
//迁移源为本机,不用申明
========================================================

练习:
数据库备份扩展练习。
1、实现异地备份。
2、数据压缩。
3、判断mysqldump执行成功。
4、创建专门备份用户。
//建立数据库备份用户需要的权限;
grant SELECT,RELOAD,LOCK TABLES,SHOW VIEW,SHOW EVENTS on *.* to backup@'localhost' identified by '123';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#!/bin/sh
#Chenjie
backdir=/opt/backup
user=backup
pass=123
 
if [ ! -d ${backdir} ];then
    mkdir  ${backdir}
fi
 
for in `/usr/local/mysql/bin/mysqlshow  -ubackup -p123|awk '{print $2}'|grep -vE '(Database|information_schema|backup|performance_schema)'`
do
   mysqldump -u${user} -p${pass} $i > ${backdir}/$i`date +%F`
   if [ $? -eq 0 ];then
      echo "Backup $i sucess"
      cd ${backdir}
      tar -czvf $i`date +%F`.tar.gz $i`date +%F`
   else
      echo "Backup $i error"
      exit
   fi
   mysql -u${user} -p${pass} -e 'flush logs'
done

MySQL数据备份

Pingbacks已打开。

引用地址

暂无评论

发表评论