A simple and easy method for creating MySQL backups is to use the mysqldump command. This command will create a simple .sql file of an existing database, which can then be restored to any other empty MySQL database. This article will work for any Linux distribution running MySQL.
1. Back up the database using the following command:
mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
The parameters of the said command as follows:
[username] – A valid MySQL username.
[password] – A valid MySQL password for the user.
[database_name] – A valid Database name you want to take backup.
[dump_file.sql] – The name of backup dump file you want to generate.
2. Restore the backup to a local database server – the mysql command will let you take the contents of a .sql file backup, and restore it directly to a database. This is the syntax for the command:
mysql -u [username] –p[password] [database_name] < [dump_file.sql]
3. Restore the backup to a remote database server – you can also use the mysql command to restore a .sql file backup to a remote MySQL server. If you have another server running MySQL, and you have the database credentials, you can define a hostname in the command by adding the -h flag to specify a hostname. This changes the syntax of the command to:
mysql –h [hostname] –u [username] –p[password] [database_name] < [dump_file.sql]
As long as you have the correct credentials and the remote server is running, you will be able to restore the database remotely.
How to Backup MySQL Database?
To take a backup of MySQL database or databases, the database must exist in the database server and you must have access to it. The format of the command would be.
# mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
The parameters of the said command as follows.
- [username] : A valid MySQL username.
- [password] : A valid MySQL password for the user.
- [database_name] : A valid Database name you want to take backup.
- [dump_file.sql] : The name of backup dump file you want to generate.
How to Backup a Single MySQL Database?
To take a backup of single database, use the command as follows. The command will dump database [rsyslog] structure with data on to a single dump file called rsyslog.sql.
# mysqldump -u root -ptecmint rsyslog > rsyslog.sql
How to Backup Multiple MySQL Databases?
If you want to take backup of multiple databases, run the following command. The following example command takes a backup of databases [rsyslog, syslog] structure and data in to a single file called rsyslog_syslog.sql.
# mysqldump -u root -ptecmint --databases rsyslog syslog > rsyslog_syslog.sql
How to Backup All MySQL Databases?
If you want to take backup of all databases, then use the following command with option –all-database. The following command takes the backup of all databases with their structure and data into a file called all-databases.sql.
# mysqldump -u root -ptecmint --all-databases > all-databases.sql
How to Backup MySQL Database Structure Only?
If you only want the backup of database structure without data, then use the option –no-data in the command. The below command exports database [rsyslog] Structure into a file rsyslog_structure.sql.
# mysqldump -u root -ptecmint -–no-data rsyslog > rsyslog_structure.sql
How to Backup MySQL Database Data Only?
To backup database Data only without structure, then use the option –no-create-info with the command. This command takes the database [rsyslog] Data into a file rsyslog_data.sql.
# mysqldump -u root -ptecmint --no-create-db --no-create-info rsyslog > rsyslog_data.sql
How to Backup Single Table of Database?
With the below command you can take backup of single table or certain tables of your database. For example, the following command only take backup of wp_posts table from the database wordpress.
# mysqldump -u root -ptecmint wordpress wp_posts > wordpress_posts.sql
How to Backup Multiple Tables of Database?
If you want to take backup of multiple or certain tables from the database, then separate each table with space.
# mysqldump -u root -ptecmint wordpress wp_posts wp_comments > wordpress_posts_comments.sql
How to Backup Remote MySQL Database
The below command takes the backup of remote server [172.16.25.126] database [gallery] into a local server.
# mysqldump -h 172.16.25.126 -u root -ptecmint gallery > gallery.sql
How to Restore MySQL Database?
In the above tutorial we have seen the how to take the backup of databases, tables, structures and data only, now we will see how to restore them using following format.
# # mysql -u [username] –p[password] [database_name] < [dump_file.sql]
How to Restore Single MySQL Database
To restore a database, you must create an empty database on the target machine and restore the database using msyql command. For example the following command will restore the rsyslog.sql file to the rsyslogdatabase.
# mysql -u root -ptecmint rsyslog < rsyslog.sql
If you want to restore a database that already exist on targeted machine, then you will need to use the mysqlimport command.
# mysqlimport -u root -ptecmint rsyslog < rsyslog.sql
In the same way you can also restore database tables, structures and data. If you liked this article, then do share it with your friends.
My Note 