How to Backup and Restore MySQL Databases on Ubuntu Server
Edit Article

  • 1 Editor

MySQL is not a new subject to many of us. But, some concepts must have vanished from our minds without a proper update. Don't worry, if you think you want to rewind and get yourselves updated, and then I am there to help you.


I am just refreshing the basics before we step in to the major.

What is MySQL?

MySQL is a database solution that uses Structured Query Language to manipulate and access data. You can easily manage any website with the help of this database solution.


As all of us know how important it is to backup a data, it is the same for databases also. Now here I am going to discuss with you different ways in which you can backup and restore MySQL database.


Here, we will discuss about the Backup of MySQL Databases on an Ubuntu VPS Server. I believe MySQL most recent versions also function in the same way. Now I guess many of you will have several doubts like

  • What are the database backup options and database terminology?
  • How to install MySQL and how to backup it in our system?
  • How to connect with MySQL server for creating backup on client server or on local machine?
  • How contents are exported in MySQL format?
  • Copying, creation of database to other local machine
  • Database Manipulations like Adding, Editing, recover, etc.Don’t worry; here I have the answers to all your doubts.Now let’s start with the big question == How to Backup MySQL Database? == The answer is as simple as the question “how to start back up?” so here we go. The MySQL database must be on the database server for the backup, and you must have access to it. The format of the command for the MySQL database would be.
    # mysqldump -u [username] –p[password] [database_name] > [dump_file.sql] 
    
    The specifications of the above command are- a valid username and a valid password for user. A valid database name and backup dump file name you want to generate for backup. Once you log in, there is a need to execute few MySQL database backup command to create a backup. Starting with the command is:
    mysqldump -u username -p database-name > backup-name.sql 
    
    Here the important thing is to: Replace the username with your actual username. The database-name must be replaced with the name of the database where you want to back up, and the name of the backup file. After you execute this command, enter the password for the backup of the database username. For the copy of files to a different directory on your server command:
    rsync -Waq backup-name.sql /path/to/directory/ 
    
    here again replace the backup-name.sql with the backup file name, you are using for backup, as well as the /path/to/directory/ with the path to the directory where you are copying your backup file. For creating a copy of the backup to your local machine use the command:
    rsync -Waq -e 'ssh -p port-number' username@IP-Address:/backup-name.sql /path/to/local/directory 
    
    Here replace the port-number with your SSH listening port’s username with the username using for connecting to your server, the IP-Address with the IP address of server, the backup-name.sql with the name of the backup file and the /path/to/local/directory with the path directory to your local machine where you want to save the backup file. Hope that’s clear... Next we will see how to backup a single MySQL Database? For the backup of single database you should use the command as follows. The command will dump database [rsyslog] structure with data on to the dump file called rsyslog.sql.
    # mysqldump -u root -pgeekeasier rsyslog > rsyslog.sql 
    
    For example, if you want to create a backup file of on your WordPress VPS for your WordPressDB and save the file as wordpress.sql using WordPressUser, you can enter:
    mysqldump -u WordPressUser -p WordPressDB > wordpress.sql 
    
    Isn’t it simple....? Now as we have learned to back up single, now let’s look how to backup multiple MySQL Databases? Run the following command for the backup of multiple databases. The following instance command takes a backup of databases [rsyslog, syslog] structure and data into the file named as rsyslog_syslog.sql.
    # mysqldump -u root -pgeekeasier --databases rsyslog syslog > rsyslog_syslog.sql 
    
    === Hope you are clear with both single and multiple backups of MySQL Databases === Now we will understand how Backup of All MySQL Databases is done? Run the following command with option –all-database for the backup of all databases. The following command will take the backup of all databases with their structure and data into a file called all-databases.sql.
    # mysqldump -u root -pgeekeasier --all-databases > all-databases.sql 
    
    === How to Backup MySQL Database Structure Only? === Use the option –no-data in the command, If you only want the backup of database structure without data. Following command exports database Structure into rsyslog_structure.sql, file.
    # mysqldump -u root -pgeekeasier -–no-data rsyslog > rsyslog_structure.sql 
    
    === How to Backup MySQL Database Data Only? === For the backup of database Data without structure, uses the option “–no-create-info” with the command. Following command takes the database Data into a file rsyslog_data.sql.
    # mysqldump -u root -pgeekeasier -no-create-db -no-create-info rsyslog > rsyslog_data.sql 
    
    === How to Backup Single Table of Database? === With the following command take back-up of a particular table or single tables of your database. For instance, the following command only takes back-up of “wp_posts” table from the database.
    # mysqldump -u root -pgeekeasier wordpress wp_posts > wordpress_posts.sql 
    
    === How to Backup Multiple Tables of Database? === For the backup of multiple tables or certain tables from the database, then separate each and every table with space.
    # mysqldump -u root -pgeekeasier wordpress wp_posts wp_comments > wordpress_posts_comments.sql 
    
    == How to Backup Remote MySQL Database == If you want to take backup of remote MySQL Database, use following command to take the backup of remote server database into a local server.
    # mysqldump -h 172.16.25.126 -u root -pgeekeasier database > database.sql 
    
    == How to Restore MySQL Database? == ==== Now we have learned to backup MySQL Database in two ways: ==== · As Single and multiple table · As data and structure backup Now we will learn how restore of MySQL backup is done by using subsequent command.
    # # mysql -u [username] –p[password] [database_name] < [dump_file.sql] 
    
    === How to Restore Single MySQL Database === You have to create an empty database on the local machine and restore the database using msyql command for restore a database. For instance the subsequent command will restore the rsyslog.sql file to the rsyslog database.
    # mysql -u root -pgeekeasier rsyslog < rsyslog.sql 
    
    If you want to restore the database which already exists on local machine, then you will use the mysqlim port command.
    # mysqlimport -u root -pgeekeasier rsyslog < rsyslog.sql 
    
    You can also restore database tables, data and structures, In the same way. === Restore your MySQL database === Once you create backup file of your database, you can easily restore it in case of a misfortune. For restore the backup file you need to execute the subsequent format:
    mysql -u username -p database-name < backup-name.sql 
    
    Here you need to change the username, the database-name and the backup-name.sql with the actual names and values, after that enter your password and that’s it. Now you have effectively restored your database. Hope all you guys have refreshed your knowledge and learned to backup MySQL updates. If you still feel I have to add more points to it, don’t worry we will surely update you with more info. Stay connected to my page so that you update your knowledge with more topics.

Article Tools

Did this article help you?

YesNo

Become
an Author!

Write an Article