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

2332323

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的数据库，数据表的创建

[iyunv@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

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

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)

冷备份：

[iyunv@ns2~]# service mysqld stop

Shuttingdown MySQL....                                    [确定]

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

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

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

[iyunv@ns2~]# mkdir bak

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

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

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

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

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

StartingMySQL...                                          [确定]

[iyunv@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

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

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)

单个库完全备份

[iyunv@ns2data]# mkdir /backup

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

对多个库进行完全备份

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

对所有库进行备份

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

对表进行完全备份

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

[iyunv@ns2data]# mysqldump -uroot -p123456 -d mysql user>/backup/desc_mysql_user.$(date +%y%m%d)

[iyunv@ns2data]# egrep -Ev '^/|^--|^$' /backup/desc_mysql_user.170620  //

DROPTABLE IF EXISTS `user`;

CREATETABLE `user` (

  `Host` char(60) COLLATE utf8_bin NOT NULLDEFAULT '',

  `User` char(16) COLLATE utf8_bin NOT NULLDEFAULT '',

  `Password` char(41) CHARACTER SET latin1COLLATE latin1_bin NOT NULL DEFAULT '',

  `Select_priv` enum('N','Y') CHARACTER SET utf8NOT NULL DEFAULT 'N',

  `Insert_priv` enum('N','Y') CHARACTER SETutf8 NOT NULL DEFAULT 'N',

  `Update_priv` enum('N','Y') CHARACTER SETutf8 NOT NULL DEFAULT 'N',

  `Delete_priv` enum('N','Y') CHARACTER SETutf8 NOT NULL DEFAULT 'N',

  `Create_priv` enum('N','Y') CHARACTER SETutf8 NOT NULL DEFAULT 'N',

  `Drop_priv` enum('N','Y') CHARACTER SET utf8NOT NULL DEFAULT 'N',

  `Reload_priv` enum('N','Y') CHARACTER SETutf8 NOT NULL DEFAULT 'N',

  `Shutdown_priv` enum('N','Y') CHARACTER SETutf8 NOT NULL DEFAULT 'N',

  `Process_priv` enum('N','Y') CHARACTER SETutf8 NOT NULL DEFAULT 'N',

  `File_priv` enum('N','Y') CHARACTER SET utf8NOT NULL DEFAULT 'N',

  `Grant_priv` enum('N','Y') CHARACTER SET utf8NOT NULL DEFAULT 'N',

  `References_priv` enum('N','Y') CHARACTER SETutf8 NOT NULL DEFAULT 'N',

  `Index_priv` enum('N','Y') CHARACTER SET utf8NOT NULL DEFAULT 'N',

  `Alter_priv` enum('N','Y') CHARACTER SET utf8NOT NULL DEFAULT 'N',

  `Show_db_priv` enum('N','Y') CHARACTER SETutf8 NOT NULL DEFAULT 'N',

  `Super_priv` enum('N','Y') CHARACTER SET utf8NOT NULL DEFAULT 'N',

  `Create_tmp_table_priv` enum('N','Y')CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `Lock_tables_priv` enum('N','Y') CHARACTERSET utf8 NOT NULL DEFAULT 'N',

  `Execute_priv` enum('N','Y') CHARACTER SETutf8 NOT NULL DEFAULT 'N',

  `Repl_slave_priv` enum('N','Y') CHARACTER SETutf8 NOT NULL DEFAULT 'N',

  `Repl_client_priv` enum('N','Y') CHARACTERSET utf8 NOT NULL DEFAULT 'N',

  `Create_view_priv` enum('N','Y') CHARACTERSET utf8 NOT NULL DEFAULT 'N',

  `Show_view_priv` enum('N','Y') CHARACTER SETutf8 NOT NULL DEFAULT 'N',

  `Create_routine_priv` enum('N','Y') CHARACTERSET utf8 NOT NULL DEFAULT 'N',

  `Alter_routine_priv` enum('N','Y') CHARACTERSET utf8 NOT NULL DEFAULT 'N',

  `Create_user_priv` enum('N','Y') CHARACTERSET utf8 NOT NULL DEFAULT 'N',

  `Event_priv` enum('N','Y') CHARACTER SET utf8NOT NULL DEFAULT 'N',

  `Trigger_priv` enum('N','Y') CHARACTER SETutf8 NOT NULL DEFAULT 'N',

  `Create_tablespace_priv` enum('N','Y')CHARACTER SET utf8 NOT NULL DEFAULT 'N',

  `ssl_type` enum('','ANY','X509','SPECIFIED')CHARACTER SET utf8 NOT NULL DEFAULT '',

  `ssl_cipher` blob NOT NULL,

  `x509_issuer` blob NOT NULL,

  `x509_subject` blob NOT NULL,

  `max_questions` int(11) unsigned NOT NULLDEFAULT '0',

  `max_updates` int(11) unsigned NOT NULLDEFAULT '0',

  `max_connections` int(11) unsigned NOT NULLDEFAULT '0',

  `max_user_connections` int(11) unsigned NOTNULL DEFAULT '0',

  `plugin` char(64) COLLATE utf8_bin DEFAULT'',

  `authentication_string` text COLLATEutf8_bin,

  PRIMARY KEY (`Host`,`User`)

)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and globalprivileges';

使用MySQLdump备份后，恢复数据

数据库恢复

[iyunv@ns2data]# mysql -uroot -p123456 -e "drop database auth1;"

[iyunv@ns2data]# mysql -uroot -p123456 < /backup/mysql_all-20170620.sql

或

mysql> source/backup/mysql_all-20170620.sql

数据表恢复

[iyunv@ns2data]# mysql -uroot -p123456 -e 'drop table auth1.user;'

[iyunv@ns2data]# mysql -uroot -p123456 auth1 < /backup/auth1_user-20170620.sql