Заметки сисадмина » MySQL: How to Back Up Database?

Заметки сисадмина о интересных вещах из мира IT, инструкции и рецензии. Настраиваем Компьютеры/Сервера/1С/SIP-телефонию в Москве

MySQL: How to Back Up Database?

2024-10-28 · Posted in MySQL / PostgreSQL

You can back up a MySQL database using command-line tools, graphical user interfaces, and third-party tools. The most common methods are explained below.

Method 1: Back up MySQL Database Using mysqldump

mysqldump is the native MySQL backup solution. It allows the user to create a backup with a single command. The syntax is:

  • Replace username with your MySQL username.
  • dbname is the database name you want to back up.
  • Replace backup.sql with the desired name for the backup file.

For example:

Backing up a MySQL database using mysqldump.

When prompted, enter your password and press Enter to confirm. Wait for the process to complete.

Method 2: Back up MySQL Database Using phpMyAdmin

phpMyAdmin uses the Export function to create backups. Follow the steps below:

1)Open phpMyAdmin. In the directory tree on the left, click the database you want to back up.

Doing so opens the directory structure in the right-hand window. You will also notice that all the assets under the main database are highlighted in the directory tree.

2)Click the Export tab.

3)Select Quick in the Export Method section to save a copy of the whole database. Choose Custom to select individual tables or other special options. Leave the Format field set to SQL unless you have a good reason to change it.

Backing up MySQL databases using phpMyAdmin.

4)Click Go to start the process.

If you selected Quick, the web browser downloads a copy of the database into your specified downloads folder. You can copy it to a safe location.

Method 3: Back up MySQL Database Using Workbench

MySQL Workbench is a GUI that includes features for managing and performing backups of MySQL databases. Follow the steps below to back up a MySQL database using MySQL Workbench:

1)Launch MySQL Workbench and connect to your MySQL server.

2)In the MySQL Workbench home screen, select the Data Export option in the Management section in the sidebar.

Backing up a MySQL database using MySQL Workbench.

3)Select the database you want to back up from the list of available databases in the Data Export wizard.

4)Choose where you want to store the backup. Select a project folder or a self-contained file.

5.)Configure backup options such as the backup method (complete or partial backup), compression, and other settings.

6)Review the configured settings and click the Start Export button to start the backup process.

The Backup Wizard displays the backup progress and a confirmation when it is completed.

Completing the database backup using MySQL Workbench.

Method 4: Back up MySQL Database Using a File System Snapshot

Backing up a MySQL database using a file system snapshot involves creating a snapshot of the underlying file system that stores the MySQL data files. This method allows you to create a point-in-time copy of the entire database without interrupting the MySQL service.

Follow the steps below to back up a MySQL database using a file system snapshot:

1)Identify your MySQL data directory location. The data directory is where MySQL stores its databases, tables, and other data files. The default location varies depending on your operating system. On Linux, the data directory is typically /var/lib/mysql/, while on Windows, it is C:\Program Files\MySQL\MySQL Server X.X\data\.

2)Before taking the snapshot, it’s essential to pause or lock MySQL writes to ensure data consistency. In the MySQL prompt, use the following SQL command:

Locking MySQL writes to ensure data consistency.

The command flushes all tables and prevents further write operations.

3)Create a snapshot using the tools provided by your file system or storage solution. The commands depend on your file system. For example, if you are using LVM (Logical Volume Manager) on Linux, use the command below:

The command creates a snapshot of the MySQL data volume.

4)After taking the snapshot, unlock MySQL writes by running:

5)After creating the snapshot, you can back it up to another location or storage system, such as a different server, disk, or cloud storage service.

Method 5: Back up MySQL Database Using the Binary Log

MySQL binary logging allows users to log changes to their MySQL database. It provides a way to perform various tasks, including point-in-time recovery, replication, and backup. Follow the steps below to use binary logs for backup:

1) Make sure that binary logging is enabled in your MySQL configuration file (my.cnf or my.ini).

  • In Linux, the file path is usually /etc/mysql/my.conf
  • In Windows, the path is C:\ProgramData\MySQL\MySQL Server XX\my.ini.

2)Open the file using a text editor and look for the log_bin line. If it is not present or commented out, add it or uncomment it:

Replace /path/to/mysql-bin with the desired path for your binary log files.

3)Verify the binary log-related configuration options using the following SQL command to check the current binary log status:

Verifying binary log-related config options in MySQL.

4)Before you start binary logging, create a full database backup. You can use tools like mysqldump or other backup methods to create a baseline backup.

5)Start binary logging by running the following SQL command in the MySQL shell:

6)Manually copy the binary log files to another location as a backup. Alternatively, use tools like mysqlbinlog to extract the SQL statements from the binary logs and save them to a file. The syntax is:

Replace mysql-bin.xxxxxx with the binary log file name.

Binary logs consume disk space, so make sure to manage log rotation and retention policies. Keep in mind that binary logging is just one component of a comprehensive backup strategy. Full backups, incremental backups, and other methods should be considered for a robust backup plan.

Leave a Reply