{"id":22491,"date":"2023-10-15T04:51:06","date_gmt":"2023-10-15T04:51:06","guid":{"rendered":"https:\/\/linuxways.net\/?p=22491"},"modified":"2023-10-19T02:15:27","modified_gmt":"2023-10-19T02:15:27","slug":"back-up-restore-mysql-database","status":"publish","type":"post","link":"https:\/\/linuxways.net\/de\/scripting\/back-up-restore-mysql-database\/","title":{"rendered":"How to Back Up & Restore a MySQL Database"},"content":{"rendered":"

Data is critical to any organization, making the data security a primary issue. MySQL, a widely used and trusted relational database technology, is critical in data management. As databases get more complex and massive, the importance of implementing the solid backup and restore techniques cannot be overstated.<\/p>\n

This article provides a step-by-step instructions and necessary commands to efficiently backup and restore a MySQL database. With MySQL as the foundation and a solid backup and restoration strategy in place, the organizations can confidently manage their data, knowing that their information is secure and is quickly recoverable if necessary.<\/p>\n

Backup from the Command-Line with Mysqldump<\/strong><\/h2>\n

Before performing any operation, we should install MySQL in Linux.<\/p>\n

1. Understanding the Database Backup<\/h3>\n

It’s important to understand the various backup options before diving into the backup process. Logical backups and physical backups are MySQL’s two main options. Physical backups record the database’s binary representation, whereas logical backups produce a text-based version of the database.<\/p>\n

2. Performing a Logical Backup<\/h3>\n

The mysqldump software is frequently used to make a logical backup of a MySQL database.<\/p>\n

The basic syntax:<\/p>\n

sudo mysqldump -u [user] -p [database_name] > [filename].sql<\/div><\/div>\n
    \n
  • \u201cmysqldump\u201d:<\/strong> Mysqldump is a command-line tool in MySQL that is used to make logical backups by dumping a database’s contents into a file. Using this tool, we can make a portable SQL-format representation of the database.<\/li>\n
  • \u201c[database_name]\u201d:<\/strong> Enter the database name to backup. The name of our MySQL database should be substituted with [database_name].<\/li>\n
  • The\u00a0<\/strong>\u201c>\u201d\u00a0<\/strong>indicates the output.<\/li>\n
  • The \u201c[filename]\u201d is the file name of the output file.<\/li>\n<\/ul>\n

    Here are some more examples:<\/p>\n

    Use the following command to back up a whole database management system:<\/p>\n

    mysqldump --all-databases --single-transaction --quick --lock-tables=false > full-backup-$(date +%F).sql -u root -p<\/div><\/div>\n

    Multiple databases can be included in the output \u201csql\u201d file.<\/p>\n

    sudo mysqldump -u [user] -p [database_1] [database_2] [database_etc] > [filename].sql<\/div><\/div>\n

    3. File Restoration from a Physical Backup<\/strong><\/h3>\n

    Restoring from a physical backup require extracting the backup file and uploading it to the MySQL server. Run the following command to restore a physical backup:<\/p>\n

    tar -zxvf [backup_file.tar.gz] -C [target_directory]
    \n
    \ninnobackupex --apply-log [target_directory]
    \n
    \nsystemctl stop mysql
    \n
    \nrm -rf \/var\/lib\/mysql\/*
    \n
    \ninnobackupex --copy-back [target_directory]
    \n
    \nchown -R mysql:mysql \/var\/lib\/mysql\/
    \n
    \nsystemctl start mysql<\/div><\/div>\n

    How to Use Mysqldump to Restore MySQL<\/h2>\n

    1. Create a New Database on the System<\/h3>\n

    Create a new MySQL database on the system that hosts the database.<\/p>\n

    This generates the foundation file that mysqldump utilizes to load the data. Because the dump file contains the rebuilding procedures, we simply need to create an empty database.<\/p>\n

    2. Restore MySQL Dump<\/h3>\n

    The MySQL command-line client is used to recover a logical backup.<\/p>\n

    mysql -u [username] -p [database_name] < [backup_file.sql]<\/div><\/div>\n

    This command imports the backup file’s data into the selected database.<\/p>\n

    MySQL Backup and Restore with phpMyAdmin<\/strong><\/h2>\n

    1. Make a Backup of Our MySQL Database<\/h3>\n
      \n
    • Launch \u201cphpMyAdmin\u201d. Click the database in the directory tree on the left.<\/li>\n
    • The directory structure should now be visible in the right-hand pane. Also note that all of the assets under the primary database are highlighted.<\/li>\n
    • Now, select \u201cExport\u201d.<\/li>\n
    • There will be a section entitled “Export Method.” Select \u201cCustom\u201d to add the individual tables or other specific features. Leave the \u201cFormat\u201d field set to SQL.<\/li>\n
    • Press the \u201cGo\u201d button. If we choose \u201cQuick\u201d, a copy of database will be downloaded in the user-mentioned path. We should save it somewhere secure.<\/li>\n<\/ul>\n

      2. Clear the Old Database Information<\/h3>\n

      It is better to remove the old data. When we restore, any existing data is not overwritten. This can result in duplicate tables which can lead to errors and conflicts.<\/p>\n

        \n
      • Start the \u201cphpMyAdmin\u201d program. After that, select the desired database to restore.<\/li>\n
      • Near the bottom, click the \u201cCheck All\u201d box. Then, select \u201cDrop\u201d in the menu.<\/li>\n
      • The tool should ask us to confirm if we want to proceed. This deletes all current data, making room for our repair.<\/li>\n<\/ul>\n

        3. Restore Our Backup MySQL Database<\/h3>\n
          \n
        • Select \u201cImport\u201d from the menu at the top.<\/li>\n
        • The \u201cFile to import\u201d part is the first one. There is a button which is labelled as \u201cChoose File\u201d next to the “Browse our computer” line a few lines down. Toggle that switch.<\/li>\n
        • Locate the export file that we wish to recover using the dialogue box using the location where we saved it. Leave every set at its default value.<\/li>\n<\/ul>\n

          Conclusion<\/strong><\/h2>\n

          Backing up and restoring a MySQL database is a must-have skill for any database administrator or developer. We may assure the safety and recoverability of our valuable data by following the steps and utilizing the right commands provided in this detailed tutorial to save our organization from potential loss and downtime.<\/p>","protected":false},"excerpt":{"rendered":"

          Guide on the instructions and necessary commands to efficiently backup and restore a MySQL database to save your organization from potential loss and downtime.<\/p>","protected":false},"author":111,"featured_media":22494,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[168],"tags":[],"class_list":["post-22491","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting"],"_links":{"self":[{"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/posts\/22491","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\/111"}],"replies":[{"embeddable":true,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/comments?post=22491"}],"version-history":[{"count":0,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/posts\/22491\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/media\/22494"}],"wp:attachment":[{"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/media?parent=22491"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/categories?post=22491"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/tags?post=22491"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}