How to take MySQL Database backup from command line?
This article provides you step by step guide practice in application development
Introduction
If you are a developer or database administrator worked in databases. When working databases you might have taken database between client and server for reuse. These days moving from stand alone database to Cloud based database supporting multi server is very usual these days.
It is important that securing database either in client or server is a huge responsibility of administrator or a developer.
Why? Because data is the business
To an extent aside, these are three ways to take database (MySQL) back up.
Method 1: Generate backup using mysqldup utility
Method 2: Generate backup using Binary Log
Method 3: Generate backup using Replication of Slaves
This article covers not all but the method 1 which is mysqldump utitly.
What is Mysqldump Utility?
This utility helps generating a database backup file in command-line.
It produces the SQL Statements that can be used to recreate database, also be used to generate the output in xml, text, or csv format
Below section may not required if you have already MySql installed.
How do I ensure mysqldump utility available?
When you try to execute mysqldump utility in command line, error shown as not recognized.
How to I resolve this?
1. Go here download
2. You download the ZIP archive of your desired version
3. Open ZIP archive and go to "bin" folder
4. You need to extract MYSQLDUMP.EXE
5. Close the Zip Archive and open MySQl Workbench
6. In MySql Workbench goto to Edit > Preferences > Administration
7. Finally, select the file that you extracted in "Path to mysqldumptool"
Now let get into an action how to use utility.
Mysqldump Command Line
This is the command-line script to execute.
Command Description:
In command line > represents backup, whereas < represents restore actions.
-u represents database user name
-p represents database password
-h represents host where your database located, is it in localhost or server
[options] you may ignore
[backup.sql] is the location where you would like to save the file.
mysqldump -u [user] -p [pwd] -h [server] [options] [db_name] > [backup.sql]
Let's take an example
mysqldump -u root -p -h localhost DemoDB >C:\Pro\MySQL\DB_20201212.sql
If you noticed -h command followed by localhost, that means database is located in localhost, in your case if your database located in different server provide the ip address of server.
When you run above command in command prompt, you need to provide password, as below image
0 Comments