MySQL备份与恢复Mysql学习

《MySQL备份与恢复Mysql学习》要点：
本文介绍了MySQL备份与恢复Mysql学习，希望对您有用。如果有疑问，可以联系我们。

1. [root@localhost?~]#?mysql?
2. mysql>?create?database?auth;?
3. Query?OK,?1?row?affected?(0.00?sec)?
4. mysql>?use?auth;?
5. Database?changed?
6. mysql>?create?table?user(name?char(10)?not?null,ID?int(48));?
7. Query?OK,?0?rows?affected?(0.04?sec)?
8. mysql>?insert?into?user?values('crushlinux','123');?
9. Query?OK,?1?row?affected?(0.01?sec)?
10. mysql>?select?*?from?user;?
11. +------------+------+?
12. |?name?|?ID?|?
13. +------------+------+?
14. |?crushlinux?|?123?|?
15. +------------+------+?
16. 1?row?in?set?(0.00?sec)?
17. mysql>?exit?
18. Bye?

1. [root@localhost?~]#?service?mysqld?stop?
2. Shutting?down?MySQL..?[?OK?]?
3. [root@localhost?~]#?yum?-y?install?xz?
4. [root@localhost?~]#?tar?Jcf?mysql_all-$(date?+%F).tar.xz?/usr/local/mysql/data/?
5. tar:?Removing?leading?`/'?from?member?names?

MYSQL教程模拟数据丢失!
?

1. [root@localhost?~]#?mkdir?bak?
2. [root@localhost?~]#?mv?/usr/local/mysql/data/*?bak/?

MYSQL教程恢复数据：
?

1. [root@localhost?~]#?mkdir?restore?
2. [root@localhost?~]#?tar?xf?mysql_all-2016-12-08.tar.xz?-C?restore/?
3. [root@localhost?~]#?mv?restore/usr/local/mysql/data/*?/usr/local/mysql/data/?
4. [root@localhost?~]#?service?mysqld?start?
5. Starting?MySQL..?[?OK?]?
6. [root@localhost?~]#?mysql?
7. mysql>?select?*?from?auth.user;?
8. +------------+------+?
9. |?name?|?ID?|?
10. +------------+------+?
11. |?crushlinux?|?123?|?
12. +------------+------+?
13. 1?row?in?set?(0.00?sec)?

MYSQL教程2、使用专用备份工具 mysqldump
MySQL 自带的备份工具,相当方便对 MySQL 进行备份.通过该命令工具可以将数据库、
数据表或全部的库导出为 SQL 脚本,在需要恢复时可进行数据恢复.
(1)对单个库进行完全备份
格式：mysqldump -u 用户名 -p[密码] [选项] [数据库名] > /备份路径/备份文件名
示例：
?

1. [root@localhost?~]#?mkdir?/backup?
2. [root@localhost?~]#?mysqldump?-uroot?-p123123?auth?>?/backup/auth-$(date?+%Y%m%d).sql?
3. [root@localhost?~]#?echo?$??
4. 0?
5. [root@localhost?~]#?cat?/backup/auth-20161208.sql?

MYSQL教程(2)对多个库进行完全备份
格式：mysqldump -u 用户名 -p [密码] [选项] --databases 库名 1 [库名 2]… > /备份路径/备份
文件名
示例：

1. [root@localhost?~]#?mysqldump?-uroot?-p123123?--databases?mysql?auth?>?
2. /backup/mysql+auth-$(date?+%Y%m%d).sql?
3. [root@localhost?~]#?cat?/backup/mysql+auth-20161208.sql?

MYSQL教程(3)对所有库进行完全备份
格式：mysqldump -u 用户名 -p [密码] [选项] --all-databases > /备份路径/备份文件名
示例：
?

1. [root@localhost?~]#?mysqldump?-uroot?-p123123?--opt?--all-?
2. databases?>/backup/mysql_all.$(date?+%Y%m%d).sql?
3. ??
4. [root@localhost?~]#?cat?/backup/mysql_all.20161208.sql?
5. //--opt?加快备份速度,当备份数据量大时使用<br>[root@localhost?~]#?cat?/backup/mysql_all.20160505.sql?

MYSQL教程(4)对表进行完全备份
格式：mysqldump -u 用户名 -p [密码] [选项] 数据库名 表名 > /备份路径/备份文件名
示例：
?

1. [root@localhost?~]#?mysqldump?-uroot?-p123123?auth?user?>/backup/auth_user-$(date?
2. +%Y%m%d).sql?
3. [root@localhost?~]#?cat?/backup/auth_user-20161208.sql?

MYSQL教程(5)对表结构的备份
格式：mysqldump -u 用户名 -p [密码] -d 数据库名 表名 > /备份路径/备份文件名
示例：
?

1. [root@localhost?~]#?mysqldump?-uroot?-p123123?-d?mysql?user?>/backup/desc_mysql_user-?
2. $(date?+%Y%m%d).sql?
3. [root@localhost?~]#?cat?/backup/desc_mysql_user-20161208.sql?

MYSQL教程4、使用? mysqldump? 备份后,恢复数据库　　
1、source 命令
登录到 MySQL 数据库,执行 source 备份 sql 脚本路径
示例
?

1. [root@localhost?~]#?mysql?
2. mysql>?show?databases;?
3. +--------------------+?
4. |?Database?|?
5. +--------------------+?
6. |?information_schema?|?
7. |?auth?|?
8. |?mysql?|?
9. |?performance_schema?|?
10. |?test?|?
11. |?usr?|?
12. +--------------------+?
13. 6?rows?in?set?(0.00?sec)?
14. mysql>?drop?database?auth;?
15. Query?OK,?1?row?affected?(0.12?sec)?
16. mysql>?source?/backup/mysql_all.20161208.sql?
17. mysql>?show?databases;?
18. ??
19. +--------------------+?
20. |?Database?|?
21. +--------------------+?
22. |?information_schema?|?
23. |?auth?|?
24. |?mysql?|?
25. |?performance_schema?|?
26. |?test?|?
27. |?usr?|?
28. +--------------------+?
29. 6?rows?in?set?(0.00?sec)?

MYSQL教程2、mysql 命令
格式：mysql -u 用户名 -p [密码] < 库备份脚本的路径
mysql -u 用户名 -p [密码] 库名 < 表备份脚本的路径
示例：
?

1. [root@localhost?~]#?mysql?-uroot?-p123123?-e?'show?databases;'?
2. +--------------------+?
3. |?Database?|?
4. +--------------------+?
5. |?information_schema?|?
6. |?auth?|?
7. |?mysql?|?
8. |?performance_schema?|?
9. |?test?|?
10. |?usr?|?
11. +--------------------+?
12. [root@localhost?~]#?mysql?-uroot?-p123123?-e?'drop?database?auth;'?
13. [root@localhost?~]#?mysql?-uroot?-p123123?<?/backup/mysql_all.20161208.sql?
14. [root@localhost?~]#?mysql?-uroot?-p123123?-e?'show?databases;'?
15. +--------------------+?
16. |?Database?|?
17. +--------------------+?
18. |?information_schema?|?
19. |?auth?|?
20. |?mysql?|?
21. |?performance_schema?|?
22. |?test?|?
23. |?usr?|?
24. +--------------------+?
25. ?
26. [root@localhost?~]#?mysql?-uroot?-p123123?-e?'drop?table?auth.user;'?
27. [root@localhost?~]#?mysql?-uroot?-p123123?auth<?/backup/auth_user-20161208.sql?
28. [root@localhost?~]#?mysql?-uroot?-p123123?-e?'select?*?from?auth.user;'?

MYSQL教程+------------+------+
| name | ID |
| crushlinux | 123 |
+------------+------+
?

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!