Mysql数据的备份与恢复

Mysql数据的备份与恢复
============备份数据库==========
============备份数据库==========
4.1准备工作：首先建表填充数据：
mysql> show tables;
Empty set (0.00 sec)
#数据恢复
mysql> system mysql -uroot -p'zxin10' zxin10_default < /tmp/zxin10.sqll
mysql> show tables;
+--------------------------+
| Tables_in_zxin10_default |
+--------------------------+
| test                     |
+--------------------------+
1 row in set (0.00 sec)
4.2填充中文数据解决乱码问题：
mysql> set names gbk;  #<--在数据插入时的编码问题


mysql> insert into test(name) values('白左');
Query OK, 1 row affected (0.00 sec)


mysql> select * from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | Baizuo |
|  2 | 白左 |
+----+--------+
2 rows in set (0.00 sec)
说明：
mysql> show variables like 'character_set%'
    -> ;
+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| character_set_client     | latin1                                 |
| character_set_connection | latin1                                 |
| character_set_database   | latin1                                 |
| character_set_filesystem | binary                                 |
| character_set_results    | latin1                                 |
| character_set_server     | latin1                                 |
| character_set_system     | utf8                                   |
| character_sets_dir       | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+
8 rows in set (0.00 sec)


mysql> set names utf8
    -> ;
Query OK, 0 rows affected (0.00 sec)


mysql> show variables like 'character_set%'
    -> ;
+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| character_set_client     | utf8                                   |
| character_set_connection | utf8                                   |
| character_set_database   | latin1                                 |
| character_set_filesystem | binary                                 |
| character_set_results    | utf8                                   |
| character_set_server     | latin1                                 |
| character_set_system     | utf8                                   |
| character_sets_dir       | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+
8 rows in set (0.00 sec)
set names utf8的作用是吧 character_set_system  character_set_client character_set_connection
全部改成uft8


#非交互式查看表内容
[root@c601 ~]# mysql -uroot -p'zxin10' --default-character-set=utf8 -e 'select * from zxin10_default.test'
+----+---------------+
| id | name          |
+----+---------------+
|  1 | Baizuo        |
|  2 | 白左        |
+----+---------------+
[root@c601 ~]# mysql -uroot -p'zxin10' -e 'select * from zxin10_default.test'
+----+--------+
| id | name   |
+----+--------+
|  1 | Baizuo |
|  2 | 白左 |
+----+--------+


[root@c601 ~]# cat /etc/sysconfig/i18n 
LANG="en_US.UTF-8"
SYSFONT="latarcyrheb-sun16"


4.3备份单个数据库
mysqldump -u USERNAME -p PASSWORD --default-character-set=latin1 DBNAME >BACKUPFILE
普通备份：
[root@c601 ~]# mkdir /server/bak
[root@c601 ~]# mysqldump -uroot -p'zxin10' zxin10_default >/server/bak/zxin10.sql
[root@c601 ~]# ll /server/bak/
total 4
-rw-r--r-- 1 root root 1903 Feb 16 15:37 zxin10.sql
压缩备份：
[root@c601 ~]# mysqldump -uroot -p'zxin10' zxin10_default|gzip >/server/bak/zxin10.sql.gz
[root@c601 ~]# ll /server/bak/
total 8
-rw-r--r-- 1 root root 1903 Feb 16 15:37 zxin10.sql
-rw-r--r-- 1 root root  750 Feb 16 15:38 zxin10.sql.gz
指定字符集备份:
[root@c601 ~]# mysqldump -uroot -p'zxin10' --default-character-set=utf8 zxin10_default|gzip >/server/bak/zxin10.sql_utf8.gz
[root@c601 ~]# ll /server/bak/
total 12
-rw-r--r-- 1 root root 1903 Feb 16 15:37 zxin10.sql
-rw-r--r-- 1 root root  751 Feb 16 15:39 zxin10.sql.gz
-rw-r--r-- 1 root root  749 Feb 16 15:40 zxin10.sql_utf8.gz
备份多个库
#参数-B
mysqldump -uroot -p'zxin10' -B zxin10_default mysql --default-character-set=utf8 >/server/bak/zxin10.utf8.sql
备份所有库
#参数-A
备份单个表
#不加-B参数，直接跟 '数据库名' '表名'，会备份表。
mysqldump -uroot -p'zxin10' -uroot -p'zxin10' zxin10_default test --default-character-set=utf8 >/server/bak/zxin10_default.test.sql 
备份多个表
#不加-B参数，直接跟 ‘数据库名’ ‘表名1’ ‘表名2’，会备份表1和表2。
备份完成！
@@@@@只备份表结构@@@@@@@
#-d参数只备份表结构
[root@c601 bak]# mysqldump -uroot -p'zxin10' -d zxin10_default test >/server/bak/zxin10.desc.sql
[root@c601 bak]# egrep -v "\*|--|^$" /server/bak/zxin10.desc.sql 
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
@@@@@只备份表结构@@@@@@@


4.3.1 mysqldump命令在做什么呢？
[root@c601 ~]# egrep -v "\*|--|^$" /server/bak/zxin10.sql
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,'Baizuo'),(2,'白左');
UNLOCK TABLES;


在做SQL的逻辑备份，恢复是执行SQL语句命令。








============恢复数据库==========
============恢复数据库==========
5恢复数据库
5.1 source命令恢复
[root@c601 bak]# mysql -uroot -p'zxin10'
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 5.1.62 Source distribution


Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> use zxin10_default
Database changed
mysql> show tables;
+--------------------------+
| Tables_in_zxin10_default |
+--------------------------+
| test                     |
+--------------------------+
1 row in set (0.00 sec)


mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)


mysql> show tables;
Empty set (0.00 sec)


mysql> source /server/bak/zxin10.sql 
Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.01 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.01 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.01 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


备份成功！
mysql> show tables;
+--------------------------+
| Tables_in_zxin10_default |
+--------------------------+
| test                     |
+--------------------------+
1 row in set (0.00 sec)


mysql> select * from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | Baizuo |
|  2 | 白左 |
+----+--------+
2 rows in set (0.00 sec)


5.2使用mysql命令恢复（标准）
[root@c601 bak]# mysql -uroot -p'zxin10' -e "use zxin10_default;drop table test;"
[root@c601 bak]# mysql -uroot -p'zxin10' -e "use zxin10_default;select * from test"
ERROR 1146 (42S02) at line 1: Table 'zxin10_default.test' doesn't exist
[root@c601 bak]# mysql -uroot -p'zxin10' -e "use zxin10_default;show tables;"


开始备份
[root@c601 bak]# mysql -uroot -p'zxin10' zxin10_default < /server/bak/zxin10.sql
[root@c601 bak]# mysql -uroot -p'zxin10' -e "use zxin10_default;select * from test"
+----+--------+
| id | name   |
+----+--------+
|  1 | Baizuo |
|  2 | 白左 |
+----+--------+
备份成功！


===========补充==============
查看当前系统进程数
[root@c601 bak]# mysql -uroot -p'zxin10' -e "show processlist;"
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 48 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
查看mysql的系统变量设置
[root@c601 bak]# mysql -uroot -p'zxin10' -e "show variables;"|head -5Variable_name Value
auto_increment_increment 1
auto_increment_offset 1
autocommit ON
automatic_sp_privileges ON
查看mysql的当前状态（全局）
[root@c601 bak]# mysql -uroot -p'zxin10' -e " show global status;"|head -5
Variable_name Value
Aborted_clients 2
Aborted_connects 10
Binlog_cache_disk_use 0
Binlog_cache_use 0
查看mysql的当前状态（局部）
[root@c601 bak]# mysql -uroot -p'zxin10' -e " show session status;"|head -5
Variable_name Value
Aborted_clients 2
Aborted_connects 10
Binlog_cache_disk_use 0
Binlog_cache_use 0