摘要:本文主要向大家介绍了MySQL数据库之如何使用mysqldump来备份mysql数据库或数据表 ,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助。
本文主要向大家介绍了MySQL数据库之如何使用mysqldump来备份mysql数据库或数据表 ,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助。
Summary: in this tutorial you will learn how to use mysqldump tool back up MySQL databases.
MySQL GUI tools such as phpMyAdmin, SQLyog and etc often provide features for backup MySQL databases with ease. However if your database is big, the backup process could be very slow because the backup file need to be transferred across the network to your client PC. As the result, the backup process increases locking time therefore MySQL unavailability.
MySQL provides a very useful tool for backup or dump MySQL databases locally on server very fast. The backup file is stored in the file system in the server so you just need to download it when needed. The tool to backup MySQL databases is mysqldump. It is located in the root/bin folder of MySQL installation folder. The mysqldump is a program provided by MySQL that can be used to dump databases for backup or transfer database to another database server. The dump file contains a set of SQL statements to create and populate tables. In addition, the mysqldump can be used to generate CSV, delimited or XML files. In this tutorial, we will focus only on how to backup MySQL database by using mysqldump tool.
How to Backup a MySQL Database
To backup a MySQL database, the database first has to exist in the database server and you have access to that server as well. You can use SSH or Telnet to login to the remote server if you do not have remote desktop to it. The command to backup a MySQL database as follows:
mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
The parameter of the command above as follows:
[username]: valid MySQL username.
[password]: valid password for the user. Note that there is no space between –p and the password.
[database_name]: database name you want to backup
[dump_file.sql]: dump file you want to generate.
By executing the above command all database structure and data will be exported into a single [dump_file.sql] dump file. For example, in order to back our sample database classicmodels, we use the following command:
mysqldump -u OutOfMemory –psecret classicmodels > c: emp ackup001.sql
How to Backup MySQL Database Structure Only
If you only want to backup database structure only you just need to add an option –no-data to tell mysqldump that only database structure need to export as follows:
mysqldump -u [username] –p[password] –no-data [database_name] > [dump_file.sql]
For example to backup our sample database with structure only, you use the following command:
mysqldump -u OutOfMemory –psecret -no-data classicmodels > c: emp ackup002.sql
How to Backup MySQL Database Data Only
There is a case that you want to refresh data in staging and development system so the data in those systems are the same as production system. In this case you just need to export data only from production system and import it to staging and development system. In order to backup data only, you use option –no-create-info of mysqldump as follows:
mysqldump -u [username] –p[password] –no-create-info [database_name] > [dump_file.sql]
For example to backup our sample database with data only, you use the following command:
mysqldump –u OutOfMemory –psecret –no-create-info classicmodels > c: emp ackup002.sql
How to Backup Multiple MySQL Databases into a Single File
If you want to backup multiple database just separate database name by command in the [database_name]. If you want to back up all databases in the database server use the option –all-database.
mysqldump -u [username] –p[password] [dbname1,dbname2,…] > [dump_file.sql] mysqldump -u [username] –p[password] –all-database > [dump_file.sql]
本文由职坐标整理并发布,希望对同学们学习MySQL有所帮助,更多内容请关注职坐标数据库MySQL数据库频道!
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了,明天再试试吧!
我们会在第一时间安排职业规划师联系您!
您也可以联系我们的职业规划师咨询:
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
沪公网安备 31011502005948号