数据库备份和恢复

prostar

mysql  数据库备份和恢复备份策略：1，数据库可以关的情况下或者把表全部锁住，直接使用系统的cp命令去拷（但象oracle这种数据库，关闭的话是可以cp，但如果不关闭只是锁表的话，是不可以cp，除非锁数据文件头)mysql如果是online的情况，但没有写操作，也可以直接cpmysql如果是online的情况，但是一个繁忙的数据，正在大量读写，这个时候就不要去cp了冷备  offline热备  online2，sql语法备份导出 用于辅助备份 (数据的逻辑导入导出)3，mysqldump备份数据库   中小型数据库 无论何种存储引擎 备出来的是sql语句，速度较慢，也无法做增量备份和累计增量备份4，mysqlhotcopy 备份数据库， 只能是myisam引擎 适合中小型myisam引擎数据库5，使用二进制日志恢复6，对innodb的备份，innodb引擎的大数据使用mysqldump备份太慢，mysqlhotcopy又不能备份innodb引擎的数据。可以选用mysql自带的企业级备份工具mysql enterprise backup，或者是xtrabackup开源备份工具（可以实现增量备份和累计增量备份）7，做mysqlAB复制实现实时备份8，存储层的备份，如lvm（logical volumn management)快照===========================================================1,冷备 （不推荐）  数据库可以关闭的情况下的备份                1，关闭数据库                        cp -a 源数据文件  目标数据文件                2，打开数据库热备   数据库不可以关闭的情况下的备份－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－2，sql语法备份backup table  原理是先锁表，然后拷贝数据文件，拷.frm和.MYD,不拷 .MYI索引文件,最后再解锁  --只限MYISAM存储引擎[root@li ~]# mkdir /tmp/mysqlbackup        --建立一个存放备份的目录[root@li ~]# chown mysql.mysql /tmp/mysqlbackup/        --改权限--backup table的这种方式在5.6的版本里已经没有了mysql> backup table emp to '/tmp/mysqlbackup';        --备份emp表到/tmp/mysqlbackup目录+------------+--------+----------+----------+| Table      | Op     | Msg_type | Msg_text |+------------+--------+----------+----------+| source.emp | backup | status   | OK       | +------------+--------+----------+----------+[root@li ~]# ll /tmp/mysqlbackup/                --查看时只有.frm和.MYD文件-rw-rw---- 1 mysql mysql 8690 08-14 15:32 emp.frm-rw-rw---- 1 mysql mysql  512 08-14 15:32 emp.MYDmysql> restore table emp from '/tmp/mysqlbackup';        --误删表后恢复emp表+------------+---------+----------+----------+| Table      | Op      | Msg_type | Msg_text |+------------+---------+----------+----------+| source.emp | restore | status   | OK       | +------------+---------+----------+----------+# vim backup_table.sh          --把backup table写成脚本来做#!/bin/bash/usr/local/mysql/bin/mysql -p123 <<EOFuse aaa;lock table emp read;EOFcp -af /data/aaa/emp.frm /tmp/mysqlbackup/cp -af /data/aaa/emp.MYD /tmp/mysqlbackup/cp -af /data/aaa/emp.MYI /tmp/mysqlbackup//usr/local/mysql/bin/mysql -p123 <<EOFunlock tables;EOF--上面的脚本可以使用时间来做成不覆盖的备份========================================数据的逻辑导入导出select  xxx  into outfile ...   --无论是什么存储引擎本身是一种数据导出的方法，同时可以用来辅助备份，它可以对一个表的其中一列或者某几列做备份（导出）mysql> select ename,sal into outfile '/tmp/mysqlbackup/emp_ename_sal.txt' from emp;        --把emp表的ename,sal两列数据导出（备份）实例1：把aaa库的emp表导出成文本，然后误删掉数据后进行恢复mysql> select * into outfile '/tmp/mysqlbackup/emp.txt' from emp;--导出来的emp.txt默认都是以制表符来分隔的两个方法：mysql> load data local infile ....     --编译时要加上--enable-local-infile参数# mysqlimport   它就是一个load data local infile的一个功能的打包实现先delete或者truncate删除表的内容来模拟数据误删除恢复方法一：mysql> use aaa;mysql> load data local infile '/tmp/mysqlbackup/emp.txt' into table emp;--此方法需要你在编译时加上--enable-local-infile这个编译参数才支持。但实际环境我不太建议加上此参数，因为可以完全使用方法二来替代恢复方法二:# /usr/local/mysql/bin/mysqlimport aaa /tmp/mysqlbackup/emp.txt -p123--这条命令aaa是库(schema)名，但没有指定表名，是由文件名来决定的(emp.txt决定了是aaa.emp表)练习：创建一个表，把你系统里的/etc/passwd导入到数据库mysql> use aaa;mysql> create table password (    -> username varchar(30),    -> password char(1),    -> uid int(5),    -> gid int(5),    -> comment varchar(50),    -> homedir varchar(36),    -> shell varchar(20));mysql> desc password;+----------+-------------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| username | varchar(30) | YES  |     | NULL    |       | | password | char(1)     | YES  |     | NULL    |       | | uid      | int(5)      | YES  |     | NULL    |       | | gid      | int(5)      | YES  |     | NULL    |       | | comment  | varchar(50) | YES  |     | NULL    |       | | homedir  | varchar(36) | YES  |     | NULL    |       | | shell    | varchar(20) | YES  |     | NULL    |       | +----------+-------------+------+-----+---------+-------+然后导入的话，有两种方法：方法一：先用awk或sed等对/etc/passwd处理出一个新的文件，七列之间以制表符为分隔符(\t)，然后用上面的mysqlimport或load data local infile来导入# sed -r 's/:/\t/g' /etc/passwd > /etc/password方法二：直接用mysqlimport指定分隔符# cp /etc/passwd /etc/password        --因为表名和文件名不一致，所以拷一份# /usr/local/mysql/bin/mysqlimport aaa -p123 --fields-terminated-by=":" --lines-terminated-by="\n" /etc/password \n 代表linux系统回车键的行结束符号windows默认为\r\n可以使用下面的命令互相转换dos2unixunix2dos=======================================用文本或数据库的方式来查找a用户的uid[root@li ~]# grep ^a: /etc/passwd |cut -d":" -f3502[root@li ~]# awk -F: '$1=="a" {print $3}' /etc/passwd502mysql> select uid from password where username='a';+------+| uid  |+------+|  502 | +------+1 row in set (0.00 sec)--从上面可以看到数据库和文本都是一种存放数据的方式，你都可以用不同的方式对它们进行读写--但把数据放到数据库，有太多比放到文本里的优势；--那么在这个基础上，程序数据(如网站的用户数据，银行的用户数据，网游数据等)放到数据库里方便读写和管理--我们也可以把一些系统管理的东西(如脚本处理的数据放到数据库)练习：(要求用文本和数据库两种方式来做)1，把系统里用户登录信息存放到数据库里用户名        时间        from where        user1   2015-08-01 10:55:30    10.2.2.102，把本月的apache访问日志进行脚本处理，然后放到数据库里，查找出类似于被访问最多的top的页面，访问最多的top10IP等========================================================================3，mysqldump工具备份导出的是sql语句文件mysqldump客户端可用来转储数据库或搜集数据库进行备份或将数据转移到另一个SQL服务器(不一定是一个MySQL服务器)。转储包含创建表和/或装载表的SQL语句。如果你在服务器上进行备份，并且表均为MyISAM表，应考虑使用mysqlhotcopy，因为可以更快地进行备份和恢复优点：无论是什么存储引擎，都可以用mysqldump备成sql语句缺点:速度较慢,导入时可能会出现格式不兼容的突发状况.无法做增量备份和累计增量备份提供三种级别的备份，表级，库级和全库级shell> mysqldump [options] db_name [tables]shell> mysqldump [options] --database DB1 [DB2 DB3...]shell> mysqldump [options] --all-database全库级：[root@li ~]# /usr/local/mysql/bin/mysqldump -p123 --all-database > /tmp/mysqlbackup/all.sqlvim /all.sql  可以看到，全是create和insert数据的sql语句库级:[root@li ~]# /usr/local/mysql/bin/mysqldump -p123 --database aaa > /tmp/mysqlbackup/aaa.sql库级恢复：[root@li ~]# /usr/local/mysql/bin/mysql -p123  < /tmp/mysqlbackup/aaa.sql 表级：[root@li ~]# /usr/local/mysql/bin/mysqldump -p123 aaa emp> /tmp/mysqlbackup/emp.sql表级恢复：[root@li ~]# /usr/local/mysql/bin/mysql -p123 aaa < /tmp/mysqlbackup/emp.sql mysql> source /tmp/mysqlbackup/emp.sql        --或者在mysql数据库内使用source命令来执行外部的sql文件全库恢复演示：        --注意mysqldump的恢复速度较慢，所以数据太大需要的时间较长rm /data/*  -rf恢复步骤：1，初始化 /usr/local/mysql/bin/mysql_install_db --datadir=/data --user=mysql2，把数据库先启起来3，恢复# /usr/local/mysql/bin/mysql < /tmp/mysqlbackup/all.sql --注意，这样恢复完后，密码还是为空，因为是误删了所有数据（包括权限表)，重新初始化回的权限表，而mysqldump恢复的只有数据＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝4,mysqlhotcopy是一个perl写的程序，原理先锁表，再拷，再解锁。只适用于myisam存储引擎mysqlhotcopy是一个Perl脚本，最初由Tim Bunce编写并提供。它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。它是备份数据库或单个表的最快的途径，但它只能运行在数据库目录所在的机器上。mysqlhotcopy只用于备份MyISAM。它运行在Unix和NetWare中。shell> mysqlhotcopy db_name [/path/to/new_directory]shell> mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory备份给定数据库中的匹配正则表达式的表：shell> mysqlhotcopy db_name./regex/加上发音符(‘~’)前缀，表名的正则表达式可以被否定：shell> mysqlhotcopy db_name./~regex/perldoc mysqlhotcopy  --用此命令去查看帮助文件备份库[root@li ~]# /usr/local/mysql/bin/mysqlhotcopy -p 123 --socket=/var/run/mysqld/mysql5.socket  source  /tmp/mysqlbackup/恢复：  拷回去就可以了，包括了db.opt文件备份表[root@li ~]# /usr/local/mysql/bin/mysqlhotcopy -p 123 --socket=/var/run/mysqld/mysql5.socket  source./emp/  /tmp/mysqlbackup/--注意：  -p 和密码中间要空格他备份的文件，也有库的目录，也有db.opt文件恢复：mysql> restore table emp from '/tmp/mysqlbackup/source';=============================================================innodb存储引擎的快速在线热备份        --使用mysql企业级备份工具（还有一种开源的xtrabackup)文档：http://dev.mysql.com/doc/mysql-e ... p/3.9/en/intro.html安装包笔记目录下/mysql/mysql_soft/V39526-01.zip   --3.9版第一步:软件包安装        mkdir /usr/local/mebunzip V39526-01.zip -d /usr/local/meb/cd /usr/local/meb/tar xf meb-3.9.0-el6-x86-64bit.tar.gzcd meb-3.9.0-el6-x86-64bitmv meb-3.9.0-el6-x86-64bit/* .[root@li meb]# pwd/usr/local/meb# ls /usr/local/meb/bin/mysqlbackup --备份工具现在就综合到这一个命令里了;这个命令的路径你可以加到$PATH里或者拷贝到/bin下，再或者做一个软链接到/bin/usr/local/meb/bin/mysqlbackup# ln -s /usr/local/meb/bin/mysqlbackup /usr/bin/mysqlbackup# ls /usr/local/meb/manual.html         --自带的文档/usr/local/meb/manual.html        第二步:备份前的准备（我这里没有实际生产库，只能模拟一些数据）以rpm版mysql为例# rm /var/lib/mysql/* -rfvim /etc/my.cnf                --在[mysqld]参数组下先加上下面的参数log-bin=mysql-bininnodb_data_home_dir=/var/lib/mysqlinnodb_data_file_path=ibdata1:10M:autoextendinnodb_log_group_home_dir=/var/lib/mysqlinnodb_log_files_in_group=2innodb_log_file_size=5242880# /etc/init.d/mysqld  restart--下面进入数据库，创建几个不同engine的表，模拟数据库的数据# mysql mysql> create database innodb;mysql> use innodbmysql> create table aaa (id int,name varchar(20)) engine=innodb;mysql> insert into aaa values (1,'sfsfsadf');mysql> insert into aaa select * from aaa;   --不断地重复这句，使这张innodb的表很大        mysql> insert into aaa select * from aaa;Query OK, 2097152 rows affected (43.36 sec)Records: 2097152  Duplicates: 0  Warnings: 0mysql> select count(*) from aaa;+----------+| count(*) |+----------+|  4194304 |+----------+--主要是要上面这个大的innodb的表，你也可以再去创建几个其它不同引擎的表（为了模拟并验证mysqlbackup工具可以备份任何引擎）--准备一个备份目录[root@li bin]# mkdir /tmp/mysqlbackup[root@li bin]# chown mysql.mysql /tmp/mysqlbackup/第三步：使用mysqlbackup命令实现mysql数据库的全备（无论什么引擎都可以）--可以使用下面的命令来对你的mysql做全备参数说明:socket指定你本地的哪个mysqluser指定用户名，我这里是使用root@localhost管理员用户，如果使用普通用户的话还需要对其授权password指定登录用户的密码backup-dir指定备份的目录，这里可以使用类似`date +%F`这样的来以时间命令备份backup-and-apply-log指定备份并应用日志，应用日志的目的就是可以直接用来恢复# mysqlbackup  --socket=/var/lib/mysql/mysql.sock --user=root --password=123 --backup-dir=/tmp/mysqlbackup/`date +%F` backup-and-apply-log--全备完后的信息，下面两个信息有用一个信息是二进制日志的位置，用于恢复备份后做二进制日志恢复第二个信息是END LSN号，它用于增量备份时所接的编号150801 14:29:13 mysqlbackup: INFO: MySQL binlog position: filename mysql-bin.000003, position 10971-------------------------------------------------------------   Parameters Summary         -------------------------------------------------------------   Start LSN                  : 522485760   End LSN                    : 522488053-------------------------------------------------------------上面的编号是在备份日志里,如果不记得,可以在备份目录里的文件找到;如下# cat /tmp/mysqlbackup/2015-08-01/meta/backup_variables.txt 第四步：下面要模拟增量差异备份，所以要去数据里再建一张表来模拟数据的改变mysql> use innodb;mysql> create table bbb (id int) engine=innodb;mysql> insert into bbb values (1);mysql> insert into bbb select * from bbb;  --再次重复这条命令，让bbb表有一定的大小mysql> select count(*) from bbb;+----------+| count(*) |+----------+|   524288 |+----------+增量差异备份的命令如下:参数介绍incremental表示做的是增量差异备份，不是全备start-lsn是表示从上一次备份的哪个编号开始进行差异增量备份backup表示只备份，没有去apply-log# mysqlbackup  --socket=/var/lib/mysql/mysql.sock --user=root --password=123 --incremental --start-lsn=522488053 --incremental-backup-dir=/tmp/mysqlbackup/incre-`date +%F` backup第五步：下一步，再建一个小表ccc，模拟数据库在差异增量备份之后的改变mysql> use innodb;mysql> create table ccc (id int) engine=innodb;mysql> insert into ccc values (1);mysql> insert into ccc values (2);mysql> insert into ccc values (3);mysql> insert into ccc values (4);mysql> insert into ccc values (5);第六步：模拟数据库挂掉，用备份实现恢复ccc表还没有来得及备份，然后数据库数据全丢了，我们这里模拟的话不要删掉数据，因为我们要保存二进制日志，所以下面我只是把数据移到另一个地方来模拟# mv /var/lib/mysql /var/lib/mysqlbak# mkdir /var/lib/mysql恢复的话在这里有两个步骤：1，apply-log                2, copy-back恢复步骤一:先在全备里去执行apply-log操作，但是因为我们已经在前面的备份时用了backup-and-apply-log参数，所以apply-log不需要再做了如果你在备份时没有使用backup-and-apply-log参数，而是使用backup参数，就需要mysqlbackup --backup-dir=/全备的目录路径/ apply-log恢复步骤二:mysqlbackup --user=root --password=123 --incremental-backup-dir=/tmp/mysqlbackup/incre-2015-08-01/ --backup-dir=/tmp/mysqlbackup/2015-08-01/ apply-incremental-backup如果有多个增量备份，则按照增量一，增量二，增量三...这样的顺序全部应用到全备里恢复步骤三:mysqlbackup  --defaults-file=/etc/my.cnf --backup-dir=/tmp/mysqlbackup/2015-08-01/ copy-back  --user=root --password=123这样就能恢复到你的最后一次备份的状态但这里有个小问题，你是root用户去操作的，所以权限都是root的，不是mysql的，还要用下面的命令改一下权限# chown mysql.mysql /var/lib/mysql -R# /etc/init.d/mysqld restart        --启动数据库去验证，会看到aaa和bbb表备份之后的数据只能通过二进制日志来恢复但是从哪开始恢复呢?虽然在备份时的日志里有告诉我们他备到了哪个二进制日志的位置，但是现在不记得了可以去找最后一次备份的里的文件去查路径为/最后一次备份的目录/meta/backup_variables.txt以我这次实验为例，如下# cat /tmp/mysqlbackup/incre-2015-08-01/meta/backup_variables.txt |grep binlogbinlog_position=mysql-bin.000003:22334找到的恢复的位置，就可以使用下面的命令来恢复了# mysqlbinlog --start-position=22334 /var/lib/mysqlbak/mysql-bin.000003 | mysql -p123到此，恢复结束，数据零丢失--最后考虑一个问题，如果要设计一个周期性的备份策略（有全备和增量备份），脚本如何写？1        2        3        4        5        6        7        星期几全        增        增        累(1-4)        增        增        累(4-7)按lsn号来做周一:mysqlbackup  --socket=/var/lib/mysql/mysql.sock --user=root --password=123 --backup-dir=/tmp/mysqlbackup/`date +%F` backup-and-apply-log &>/dev/null周二，三，五，六:lastday=`date -d "-1 days" +%F'lastlsn=`cat /tmp/mysqlbackup/$lastday/meta/backup_variables.txt |grep ^end_lsn|cut -d"=" -f2`mysqlbackup  --socket=/var/lib/mysql/mysql.sock --user=root --password=123 --incremental --start-lsn=$lastlsn --incremental-backup-dir=/tmp/mysqlbackup/`date +%F` backup &>/dev/null周四，天:lastday=`date -d "-3 days" +%F'lastlsn=`cat /tmp/mysqlbackup/$lastday/meta/backup_variables.txt |grep ^end_lsn|cut -d"=" -f2`mysqlbackup  --socket=/var/lib/mysql/mysql.sock --user=root --password=123 --incremental --start-lsn=$lastlsn --incremental-backup-dir=/tmp/mysqlbackup/`date +%F` backup &>/dev/null--------------------------------按定义上一次备份目录和当前备份目录差异的做法:第一天:vim /path/1.sh#!/bin/bash/path/mysqlbackup  --socket=/var/lib/mysql/mysql.sock --user=root --password=123 --backup-dir=/tmp/mysqlbackup/`date +%F` backup-and-apply-log &> /dev/null第二天和第三天和第五天和第六天（都是减1天，一样，所以用同一个脚本）:vim /path/2.sh#!/bin/bash/path/mysqlbackup  --socket=/var/lib/mysql/mysql.sock --user=root --password=123 --incremental --incremental-base=dir:/tmp/mysqlbackup/`date -d '-1 days' +%F` --incremental-backup-dir=/tmp/mysqlbackup/`date +%F` backup  &>/dev/null第四天和第七天（都是减3天，一样，所有用同一个脚本）:vim /path/3.sh#!/bin/bash/path/mysqlbackup  --socket=/var/lib/mysql/mysql.sock --user=root --password=123 --incremental --incremental-base=dir:/tmp/mysqlbackup/`date -d '-3 days' +%F` --incremental-backup-dir=/tmp/mysqlbackup/`date +%F` backup  &>/dev/null然后把上面写到crontab里01 2 * * 1        sh /path/1.sh01 2 * * 2,3,5,6   sh /path/2.sh01 2 * * 4,7    sh /path/3.sh----------------------------------或者把上面写成一个脚本vim /path/4.sh#!/bin/bashday=`date +%A`case "$day" in        Monday )                /path/mysqlbackup  --socket=/var/lib/mysql/mysql.sock --user=root --password=123 --backup-dir=/tmp/mysqlbackup/`date +%F` backup-and-apply-log &> /dev/null                ;;        Tuesday|Wednesday|Friday|Saturday )                /path/mysqlbackup  --socket=/var/lib/mysql/mysql.sock --user=root --password=123 --incremental --incremental-base=dir:/tmp/mysqlbackup/`date -d '-1 days' +%F` --incremental-backup-dir=/tmp/mysqlbackup/`date +%F` backup  &>/dev/null                ;;        Thursday|Sunday )                /path/mysqlbackup  --socket=/var/lib/mysql/mysql.sock --user=root --password=123 --incremental --incremental-base=dir:/tmp/mysqlbackup/`date -d '-3 days' +%F` --incremental-backup-dir=/tmp/mysqlbackup/`date +%F` backup  &>/dev/null                ;;                * )                echo "日期有误"esac然后把这一个脚本加入crontab01 2 * * * sh /path/4.sh==================================================================        mysql - ndb  cluster集群                同读同写        ndb cluster 是一个基于ndbcluster存储引擎的分布式数据库系统和oracle RAC集群不同的是，ndb cluster是一种share nothing的架构，各个mysql server之间并不共享任何数据，高度可扩展性和可用性。                                manager  10.2.2.10                                ｜                ｜                                ｜                ｜                        data node                data node                        10.2.2.11          10.2.2.12                                        ｜                ｜                        sql node                sql node                        10.2.2.11          10.2.2.12在rhel6.5下，使用mysql-cluster-gpl-7.2.8-linux2.6-x86_64.tar.gz  --这是一个64位的二进制版(非源码版本)准备三台机器1，主机名三步2，时间同步3，iptables,selinux关闭--因为我要安装到/usr/local/mysql，所以以前安装的数据不希望删掉的话，你可以先mv一下mv /usr/local/mysql   /usr/local/mysqlbak# tar xf mysql-cluster-gpl-7.2.8-linux2.6-x86_64.tar.gz  -C /usr/local/# mv /usr/local/mysql-cluster-gpl-7.2.8-linux2.6-x86_64/ /usr/local/mysql