mysql恢复所有数据库_MySQL数据库-完全备份及恢复

最新推荐文章于 2024-07-25 16:50:23 发布

weixin_39770592

最新推荐文章于 2024-07-25 16:50:23 发布

阅读量230

点赞数

文章标签： mysql恢复所有数据库

本文链接：https://blog.csdn.net/weixin_39770592/article/details/113254728

版权

MySQL数据库-完全备份及恢复

数据库备份的分类

物理角度：

冷备份；也称脱机备份，特点：关掉数据库再备份

热备份：也称联机备份，特点：开启数据库再备份

温备份：数据库只有可读权限的状态下备份

逻辑角度：

对数据库的对象的备份，例如数据表，及数据表中的一些sql语句等

备份策略角度：

完全备份：每次对数据进行整体的备份

差异备份：在第一次完整备份a数据后，以后的每次的备份是a+b，b就是针对于a数据发生变化的数据，称之为‘差异数据’。缺点：b的数据内存会越来越大b+=b，导致数据恢复缓慢。恢复数据方法：只需恢复a和最后一次的b即可.(注意：这里的差异备份只能用a与b)

增量备份：

对象:固定的a数据+b1+b2+...+bn.

每次备份b都是独立的一次备份

恢复数据的方法：a+b1+b2+...+bn,缺点：若是b1次备份到bn次备份之间的任何一备份的数据出现问题，那么都无法恢复数据。

mysql的数据库，数据表的创建

[root@ns2~]#mysql -u root -p123456

Welcometo the MySQL monitor. Commands end with; or \g.

YourMySQL connection id is 2

Serverversion: 5.5.22-log Source distribution

Oracleis 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>

mysql>create database auth1;

QueryOK, 1 row affected (0.04 sec)

mysql>use auth1;

Databasechanged

mysql>create table user(name char(10) not null,ID int(30));

QueryOK, 0 rows affected (0.09 sec)

mysql>insert into user values('amber','123');

QueryOK, 1 row affected (0.01 sec)

mysql>select * from user;

+-------+------+

|name | ID |

+-------+------+

|amber | 123 |

+-------+------+

1row in set (0.00 sec)

冷备份：

[root@ns2~]# service mysqld stop

Shuttingdown MySQL.... [确定]

[root@ns2~]# yum -y install xz

[root@ns2~]# tar Jcf mysql_all-$(date +%F).tar.xz /usr/local/mysql/data/

tar:从成员名中删除开头的“/”

[root@ns2~]# mkdir bak

[root@ns2~]# mv /usr/local/mysql/data/* bak/

[root@ns2~]# tar xf mysql_all-2017-06-20.tar.xz

[root@ns2~]# cd usr/local/mysql/data/

[root@ns2data]# mv * /usr/local/mysql/data/

[root@ns2data]# /etc/init.d/mysqld start

StartingMySQL... [确定]

[root@ns2data]# mysql -uroot -p123456

Welcometo the MySQL monitor. Commands end with; or \g.

YourMySQL connection id is 1

Serverversion: 5.5.22-log Source distribution

Oracleis 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>select * from auth.user;

ERROR1146 (42S02): Table 'auth.user' doesn't exist

mysql>select * from auth1.user;

+-------+------+

|name | ID |

+-------+------+

|amber | 123 |

+-------+------+

1row in set (0.01 sec)

单个库完全备份

[root@ns2data]# mkdir /backup

[root@ns2data]# mysqldump -uroot -p auth1 >/backup/auth1-$(date +%Y%m%d).sql

对多个库进行完全备份

[root@ns2data]# mysqldump -uroot -p123456 --databases mysql auth1 >/backup/mysql+auth1-$(date +%Y%m%d).sql

对所有库进行备份

[root@ns2data]# mysqldump -uroot -p123456 --opt --all-databases >/backup/mysql_all-$(date +%Y%m%d).sql

对表进行完全备份

[root@ns2data]# mysqldump -uroot -p123456 auth1 user>/backup/auth1_user-$(date +%Y%m%d).sql

[root@ns2

data]# mysqldump -uroot -p123456 -d mysql user

>/backup/desc_mysql_user.$(date +%y%m%d)

[root@ns2

data]# egrep -Ev '^/|^--|^$' /backup/desc_mysql_user.170620 //

DROP

TABLE IF EXISTS `user`;

CREATE

TABLE `user` (

`Host` char(60) COLLATE utf8_bin NOT NULL

DEFAULT '',

`User` char(16) COLLATE utf8_bin NOT NULL

DEFAULT '',

`Password` char(41) CHARACTER SET latin1

COLLATE latin1_bin NOT NULL DEFAULT '',

`Select_priv` enum('N','Y') CHARACTER SET utf8

NOT NULL DEFAULT 'N',

`Insert_priv` enum('N','Y') CHARACTER SET

utf8 NOT NULL DEFAULT 'N',

`Update_priv` enum('N','Y') CHARACTER SET

utf8 NOT NULL DEFAULT 'N',

`Delete_priv` enum('N','Y') CHARACTER SET

utf8 NOT NULL DEFAULT 'N',

`Create_priv` enum('N','Y') CHARACTER SET

utf8 NOT NULL DEFAULT 'N',

`Drop_priv` enum('N','Y') CHARACTER SET utf8

NOT NULL DEFAULT 'N',

`Reload_priv` enum('N','Y') CHARACTER SET

utf8 NOT NULL DEFAULT 'N',

`Shutdown_priv` enum('N','Y') CHARACTER SET

utf8 NOT NULL DEFAULT 'N',

`Process_priv` enum('N','Y') CHARACTER SET

utf8 NOT NULL DEFAULT 'N',

`File_priv` enum('N','Y') CHARACTER SET utf8

NOT NULL DEFAULT 'N',

`Grant_priv` enum('N','Y') CHARACTER SET utf8

NOT NULL DEFAULT 'N',

`References_priv` enum('N','Y') CHARACTER SET

utf8 NOT NULL DEFAULT 'N',

`Index_priv` enum('N','Y') CHARACTER SET utf8

NOT NULL DEFAULT 'N',

`Alter_priv` enum('N','Y') CHARACTER SET utf8

NOT NULL DEFAULT 'N',

`Show_db_priv` enum('N','Y') CHARACTER SET

utf8 NOT NULL DEFAULT 'N',

`Super_priv` enum('N','Y') CHARACTER SET utf8

NOT NULL DEFAULT 'N',

`Create_tmp_table_priv` enum('N','Y')

CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Lock_tables_priv` enum('N','Y') CHARACTER