亚洲在线久爱草,狠狠天天香蕉网,天天搞日日干久草,伊人亚洲日本欧美

為了賬號安全,請及時綁定郵箱和手機立即綁定

如何使用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:\temp\backup001.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:\temp\backup002.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:\temp\backup002.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]

原文链接:http://outofmemory.cn/mysql/administration/how-to-backup-database-using-mysqldump

點擊查看更多內容
TA 點贊

若覺得本文不錯,就分享一下吧!

評論

作者其他優質文章

正在加載中
  • 推薦
  • 評論
  • 收藏
  • 共同學習,寫下你的評論
感謝您的支持,我會繼續努力的~
掃碼打賞,你說多少就多少
贊賞金額會直接到老師賬戶
支付方式
打開微信掃一掃,即可進行掃碼打賞哦
今天注冊有機會得

100積分直接送

付費專欄免費學

大額優惠券免費領

立即參與 放棄機會
微信客服

購課補貼
聯系客服咨詢優惠詳情

幫助反饋 APP下載

慕課網APP
您的移動學習伙伴

公眾號

掃描二維碼
關注慕課網微信公眾號

舉報

0/150
提交
取消