mysql数据库备份，备份有：冷备和热备。
冷备：把服务停止，
把/var/lib/mysql下面的数据拷贝到其他地方或者其他硬盘上即可，然后可以把/var/lib/mysql下面的数据全部删除，来模拟故障。数据拷贝回来之后需要把文件的属主和属组改成mysql即可，mysql冷备比较快。在这里就不在演示了，因为我已经测试过了。
热备：就是服务不能停止，备份是sql语句
新建一个数据库和表来进行测试使用，库名：back2，表：d。
mysql> create database back2;
Query OK, 1 row affected (0.00 sec)
mysql> use back2
Database changed
mysql> create table d(a int);
Query OK, 0 rows affected (0.00 sec)
mysql> create table d(a int);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into d values(1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into d values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into d values(1);
Query OK, 1 row affected (0.00 sec)
备份back2库，下面信息就不一一详解了自己看看就明白了。如果有用户名和密码的话
命令格式 -u用户名,-p和密码（木有空格）备份的库名，因为我这里木有设置密码，所以使用默认
mysqldump -u root -p123 back2 > /tmp/zxl/back2.sql
[root@localhost ~]# mysqldump back2
-- MySQL dump 10.11
--
-- Host: localhost Database: back2
-- ------------------------------------------------------
-- Server version 5.0.77-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `d`
--
DROP TABLE IF EXISTS `d`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `d` (
`a` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
--
-- Dumping data for table `d`
--
LOCK TABLES `d` WRITE;
/*!40000 ALTER TABLE `d` DISABLE KEYS */;
INSERT INTO `d` VALUES (1),(1),(1);
/*!40000 ALTER TABLE `d` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2012-12-01 16:10:42
由于备份信息显示在终端上，重定向一下
[root@localhost ~]# mysqldump back2 > /tmp/zxl/back2.sql
把数据库删除，模拟故障
mysql> drop database back2;
Query OK, 1 row affected (0.00 sec)
新建一个数据库名为：back2
mysql> create database back2;
Query OK, 1 row affected (0.00 sec)
还原数据库
mysql back < /tmp/zxl/back2.sql
查看数据库，有个back2
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| back |
| back1 |
| back2 |
| my_db |
| mysql |
| niba |
| nimei |
| test |
+--------------------+
9 rows in set (0.00 sec)
mysql> use back2
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
查看表有d
mysql> show tables;
+-----------------+
| Tables_in_back2 |
+-----------------+
| d |
+-----------------+
1 row in set (0.00 sec)
查看表
mysql> select * from d;
+------+
| a |
+------+
| 1 |
| 1 |
| 1 |
+------+
3 rows in set (0.00 sec)
以上是系统自带的备份和还原。
使用第三方软件mydumper来备份和还原，需要一下软件
安装需要的包
yum install gcc gcc-c++ glib2-devel mysql-devel zlib-devel pcre-devel -y
rpm -ivh cmake-2.6.4-7.el5.i386.rpm
解压mydumper软件包
tar fvxz mydumper-0.2.3.tar.gz
进入mydumper解压目录下安装
[root@localhost mydumper-0.2.3]# cmake .
-- The C compiler identification is GNU
-- The CXX compiler identification is GNU
-- Check for working C compiler: /usr/bin/gcc
-- Check for working C compiler: /usr/bin/gcc -- works
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- Check for working CXX compiler: /usr/bin/c++
-- Check for working CXX compiler: /usr/bin/c++ -- works
-- Detecting CXX compiler ABI info
-- Detecting CXX compiler ABI info - done
-- Using mysql-config: /usr/bin/mysql_config
-- Found MySQL: /usr/include/mysql, /usr/lib/mysql/libmysqlclient_r.so;/usr/lib/libz.so;/usr/lib/libpthread.so;/usr/lib/libcrypt.so;/usr/lib/libnsl.so;/usr/lib/libm.so;/usr/lib/libpthread.so;/usr/lib/libssl.so;/usr/lib/libcrypto.so
-- Found ZLIB: /usr/lib/libz.so
-- checking for one of the modules 'glib-2.0'
-- found glib-2.0, version 2.12.3
-- checking for one of the modules 'gthread-2.0'
-- found gthread-2.0, version 2.12.3
-- checking for module 'libpcre'
-- found libpcre, version 6.6
-- Found PCRE: /usr/include
WARNINGUnable to find Sphinx documentation generator
WARNINGMissing python-sphinx documentation tools, docs will not be built
-- Configuring done
-- Generating done
-- Build files have been written to: /root/Desktop/mydumper-0.2.3
编译make
[root@localhost mydumper-0.2.3]# make
Scanning dependencies of target mydumper
[ 25%] Building C object CMakeFiles/mydumper.dir/mydumper.c.o
[ 50%] Building C object CMakeFiles/mydumper.dir/binlog.c.o
[ 75%] Building C object CMakeFiles/mydumper.dir/server_detect.c.o
Linking C executable mydumper
[ 75%] Built target mydumper
Scanning dependencies of target myloader
[100%] Building C object CMakeFiles/myloader.dir/myloader.c.o
Linking C executable myloader
[100%] Built target myloader
安装
[root@localhost mydumper-0.2.3]# make install
[ 75%] Built target mydumper
[100%] Built target myloader
Install the project...
-- Install configuration: ""
-- Installing: /usr/local/bin/mydumper
-- Removed runtime path from "/usr/local/bin/mydumper"
-- Installing: /usr/local/bin/myloader
-- Removed runtime path from "/usr/local/bin/myloader"
安装完成之后，系统会多一个命令mydumper，mydumper支持多线程备份。但是也需要服务器硬件配置高
创建一个备份目录
[root@localhost ~]# mkdir -pv /tmp/sqlbak
mkdir: 已创建目录 “/tmp/sqlbak”
备份命令格式 -B备份库名 -u 用户 -p密码 -h 主机IP -r是切割多少行，慎用
mydumper -B databasename -u root -p password -h hostip -o /tmp/sqlbak -r 100000
因为我这里木有设置root用户名和密码，所以就省略了
[root@localhost ~]# mydumper -B niba -h localhost -o /tmp/sqlbak -r 100000
还原命令格式
myloader -u root -p password -d /tmp/sqlbak -B databasename
木有设置root密码，所以就省了。
myloader -u root -d /tmp/sqlbak -B niba
以上是使用第三方mydumper备份和还原~！
