MySQL数据库-完全备份及恢复
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的数据库，数据表的创建# mysql -u root -p123456Welcometo the MySQL monitor.Commands end with; or \g.YourMySQL connection id is 2Serverversion: 5.5.22-log Source distributionCopyright(c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.Oracleis a registered trademark of Oracle Corporation and/or itsaffiliates.Other names may be trademarks of their respectiveowners.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;Databasechangedmysql>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)冷备份：# service mysqld stopShuttingdown MySQL.... [确定]# yum -y install xz# tar Jcf mysql_all-$(date +%F).tar.xz /usr/local/mysql/data/tar:从成员名中删除开头的“/”# mkdir bak# mv /usr/local/mysql/data/*bak/# tar xf mysql_all-2017-06-20.tar.xz # cd usr/local/mysql/data/# mv* /usr/local/mysql/data/# /etc/init.d/mysqld startStartingMySQL... [确定]# mysql -uroot -p123456Welcometo the MySQL monitor.Commands end with; or \g.YourMySQL connection id is 1Serverversion: 5.5.22-log Source distributionCopyright(c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.Oracleis a registered trademark of Oracle Corporation and/or itsaffiliates.Other names may be trademarks of their respectiveowners.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 existmysql>select* from auth1.user;+-------+------+|name| ID |+-------+------+|amber |123 |+-------+------+1row in set (0.01 sec)单个库完全备份# mkdir /backup# mysqldump -uroot -p auth1 >/backup/auth1-$(date +%Y%m%d).sql对多个库进行完全备份# mysqldump -uroot -p123456 --databases mysql auth1>/backup/mysql+auth1-$(date +%Y%m%d).sql对所有库进行备份# mysqldump -uroot -p123456 --opt --all-databases >/backup/mysql_all-$(date +%Y%m%d).sql对表进行完全备份# mysqldump -uroot -p123456auth1 user>/backup/auth1_user-$(date +%Y%m%d).sql# mysqldump -uroot -p123456 -d mysql user>/backup/desc_mysql_user.$(date +%y%m%d)# 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备份后，恢复数据数据库恢复# mysql -uroot -p123456 -e "drop database auth1;"# mysql -uroot -p123456 < /backup/mysql_all-20170620.sql或
mysql> source/backup/mysql_all-20170620.sql数据表恢复# mysql -uroot -p123456 -e 'drop table auth1.user;'# mysql -uroot -p123456 auth1 < /backup/auth1_user-20170620.sql
页:
[1]