In this blog, you will study how to use mysqldump tool back up MySQL databases.
MySQL GUI tools such as phpMyAdmin, SQLyog and etc, normally deliver features for backup MySQL databases with ease. But, if your database is big, the backup process could be very slow for the reason that the backup file needs to be transported across the network to your client PC. As the result, the backup process surges the locking and available time of the MySQL database server.
MySQL delivers a very valuable tool for backing up or dumping MySQL databases locally on the server very fast. The backup file is kept in the file system in the server, so you just need to download it when wanted.
The instrument to backup MySQL databases is mysqldump. It is in the root/bin folder of MySQL installation folder.
The mysqldump is a program if by MySQL that can be used to dump databases for backup or handover database to another database server.
The dump file comprises a set of SQL statements to create database objects. In accumulation, the mysqldump can be used to produce CSV, delimited or XML files. In this lesson, we will focus only on how to backup MySQL database by means of mysqldump tool.
In this blog, we will emphasis only on how to backup MySQL database by means of mysqldump tool.
Backup MySQL database
To backup a MySQL database, the database primary must be in the database server and you have admission to that server as well. You can use SSH or Telnet to log in to the distant server if you do not have remote desktop to it. The facility to backup a MySQL database as follows:
You can use the SSH or Telnet to log in to the distant server if you cannot remote desktop to it. The expertise to backup a MySQL database as follows:
The parameter of the command overhead 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 performing the above command, all database structure and data will be transferred into a single [dump_file.sql] dump file. For example, to back our sample database classicmodels, we use the subsequent command:
Backup MySQL database structure only
If you only need to backup database structure only you just essential to add an option –no-data to tell mysqldump that only database structure wants to export as follows:
For instance, to backup our example database with structure only, you use the subsequent command:
Backup MySQL database data Only
There is a situation that you want to refresh data in performance and development system so the data in those systems are the similar as the making system.
In this situation, you just need to transfer data only from the production system and ingress it to staging and development system. To backup data only, you use option –no-create-info of mysqldump as follows:
For example to backup our sample database with data only, you use the following command:
In what way to backup multiple MySQL databases into a single file
If you want to backup several databases just distinct database name by the command in the [database_name]. If you want to backup all databases in the database server use the option –all-database.
In this blog, you have educated how to use the mysqldump tool to backup MySQL databases with several options.
Commenti