{"id":4469,"date":"2021-02-15T09:37:30","date_gmt":"2021-02-15T09:37:30","guid":{"rendered":"https:\/\/linuxways.net\/?p=4469"},"modified":"2021-02-15T09:37:34","modified_gmt":"2021-02-15T09:37:34","slug":"how-to-backup-and-restore-mysql-databases-on-red-hat-enterprise-linux-8","status":"publish","type":"post","link":"https:\/\/linuxways.net\/de\/red-hat\/how-to-backup-and-restore-mysql-databases-on-red-hat-enterprise-linux-8\/","title":{"rendered":"How to Backup and Restore MySQL Databases on Red Hat Enterprise Linux 8"},"content":{"rendered":"

The ability to restore databases when data failures occur is very important. Data failures can be as a result of an accidental or malicious modification, deletion or corruption of data.<\/p>\n

In this guide, you will learn how to backup an existing MySQL database on Red Hat Enterprise Linux (RHEL) 8 by using the mysqldump utility. Also, you will learn how to restore a MySQL database in order to recover from data failures.<\/p>\n

Backup a MySQL Database<\/h2>\n

You can use the mysqldump<\/strong> utility to backup your MySQL database. Firstly, run the command below to confirm that the mysql service is active (running.)<\/p>\n

$ sudo systemctl status mysqld<\/strong><\/pre>\n
<\/strong><\/p>\n
Press q<\/strong> to return to the terminal prompt.<\/p>\n
If you see a message which indicates that the mysql service is disabled or inactive (dead,) then run the next command to start the mysql service.<\/p>\n
$ sudo systemctl start mysqld<\/strong><\/pre>\n
Once the mysql service is active (running,) you may login.<\/p>\n
$ sudo mysql -u root -p<\/strong><\/pre>\n
Note: <\/strong>You may replace root<\/strong> with another mysql user as appropriate.<\/p>\n
Create a sample database and table<\/h3>\n
For the purpose of this tutorial, I have created a sample database and table by running the following queries. You may do the same.<\/p>\n
mysql> CREATE DATABASE cars;<\/strong><\/pre>\n
<\/strong><\/p>\n
mysql> USE cars;<\/strong><\/pre>\n
<\/strong><\/p>\n
mysql> CREATE TABLE suvs (make varchar(25), model varchar(25), VIN varchar(17));<\/strong><\/pre>\n
<\/strong><\/p>\n
mysql> INSERT INTO suvs VALUES ('Toyota', 'Rav4', '12345678901234567'), ('Honda', 'Crv', '09876543211234567'), ('Nissan', 'Pathfinder', '25162435453647567');<\/strong><\/pre>\n
<\/strong><\/p>\n
mysql> SELECT * FROM suvs;<\/strong><\/pre>\n
<\/strong><\/p>\n
Run the next command to quit mysql.<\/p>\n
mysql> quit<\/strong><\/pre>\n
Export database with mysqldump<\/h3>\n
The syntax of the mysqldump command is:<\/p>\n
mysqldump -u username -p database_to_backup > backup_file_name.sql<\/pre>\n
Replace:<\/p>\n
    \n
  1. username<\/strong> with an actual username that has appropriate permissions on the database<\/li>\n
  2. database_to_backup<\/strong> with the actual name of the database you intend to backup<\/li>\n
  3. backup_file_name<\/strong> with your desired name for the backup file<\/li>\n<\/ol>\n
    Using the sample cars database as an example, the following command will backup the database to the database_backups folder in my home directory.<\/p>\n
    $ mysqldump -u root -p cars > \/home\/shola\/database_backups\/cars_bk.sql<\/strong><\/pre>\n
    <\/strong><\/p>\n
    If no errors are displayed, then the operation completed successfully.<\/p>\n
    Verify database backup<\/h3>\n
    Next, you can quickly examine the content of the database backup file by running the command below. Remember to replace backup_file_name accordingly.<\/p>\n
    $ head backup_file_name.sql<\/strong><\/pre>\n
    See the figure below for a sample output.<\/p>\n
    <\/p>\n
    Restore a MySQL Database<\/h2>\n
    The only thing that is more important than taking backups is ensuring that you can actually restore those backups in the event of any data failure.<\/p>\n
    Imagine that the sample cars database which we created earlier has just been maliciously corrupted. Luckily, we have a backup of the cars database and we can restore it as follows.<\/p>\n
    Create a new database<\/h3>\n
    We would need to login to mysql and create a new database for the restore operation.<\/p>\n
    Login to mysql as follows.<\/p>\n
    $ mysql -u root -p<\/strong><\/pre>\n
    Next, run the query below to create a new database.<\/p>\n
    mysql> CREATE DATABASE cars2;<\/strong><\/pre>\n
    <\/strong><\/p>\n
    Once the query executes successfully (OK), we may quit mysql.<\/p>\n
    mysql> quit<\/strong><\/pre>\n
    Import database from backup<\/h3>\n
    The basic mysql syntax for restoring a mysql database is:<\/p>\n
    mysql -u username -p new_database_name < backup_file_name.sql<\/pre>\n
    Replace:<\/p>\n
      \n
    1. username<\/strong> with an actual username that has appropriate permissions on the database<\/li>\n
    2. new_database_name<\/strong> with the name of the new database created for the restore operation<\/li>\n
    3. backup_file_name<\/strong> with the name of the database backup file<\/li>\n<\/ol>\n
      For example, the following command restores the cars database from backup.<\/p>\n
      $ mysql -u root -p cars2 < \/home\/shola\/database_backups\/cars_bk.sql<\/strong><\/pre>\n
      <\/strong><\/p>\n
      If no errors are displayed, then the restore operation completed successfully.<\/p>\n
      Verify database restore<\/h3>\n
      We could login to mysql and verify the database restore operation as follows.<\/p>\n
      $ mysql -u root -p<\/strong><\/pre>\n
      Once you are logged in, run the query below to show available databases.<\/p>\n
      mysql> SHOW DATABASES;<\/strong><\/pre>\n
      <\/strong><\/p>\n
      The next query selects the cars2 database.<\/p>\n
      mysql> USE cars2;<\/strong><\/pre>\n
      <\/strong><\/p>\n
      Display tables in the cars2 database with the query below.<\/p>\n
      mysql> SHOW TABLES;<\/strong><\/pre>\n
      <\/strong><\/p>\n
      Finally, select all records from the suvs table as follows.<\/p>\n
      mysql> SELECT * FROM suvs;<\/strong><\/pre>\n
      <\/strong><\/p>\n
      Conclusion<\/h2>\n
      In this guide, we successfully backed up and restored a MySQL database on Red Hat Enterprise Linux 8. Do let us know if you have any questions or comments.<\/p>","protected":false},"excerpt":{"rendered":"
      The ability to restore databases when data failures occur is very important. Data failures can be as a result of an accidental or malicious modification, deletion or corruption…<\/p>","protected":false},"author":28,"featured_media":4484,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[165],"tags":[194,195],"class_list":["post-4469","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-red-hat","tag-backup","tag-restore"],"_links":{"self":[{"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/posts\/4469","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/users\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/comments?post=4469"}],"version-history":[{"count":0,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/posts\/4469\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/media\/4484"}],"wp:attachment":[{"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/media?parent=4469"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/categories?post=4469"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/tags?post=4469"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}