MySQL database backup methods

MySQL backup methods

I have been wanting to write some blogs recently, but I don't know what to write. I feel that my recent knowledge has not increased. Today I think of a blog that can be written. I used to back up MySQL based on the data folder, but never succeeded. I helped my friend restore MySQL a few days ago and finally successfully restored the backed up data folder.

MySQL databases are the best databases in common databases, and backup operations are no exception. So today I will share with you several backup methods for the MySQL database.

Method 1: use command line.

The command line method is quick. You only need to use the mysqldump command in the command line, by default, the command is in the bin directory of mysql (you must add the bin directory to the path), open the command line, and switch to a drive letter, enter "mysqldump-u root-p Database Name> Backup file name. SQL, click press enter, enter the password, and you will be able to back up the corresponding database. This backup has no database creation statement ,:

Method 2: Use client software such as SQLyog

I think using SQLyog to back up MySQL databases is easier than other backup methods, and the recovery method is also very simple. The database backed up with this software will carry the SQL statement for database creation, which facilitates database restoration. It also uses Navicat software as the MySQL client. SQLyog is easier to use than Navicat. I am used to using this software, but sometimes I need to open multiple windows and use the navicat software. The SQLyog software interface is as follows:

If you want to back up a database, right-click the database to be backed up and choose "backup/export" from the shortcut menu. Then, select "SQL file backup" from the context menu ,:

In the pop-up "SQL dump" window, enter the name of the database to be backed up to back up the database. :

Method 3: Back up the Data folder

The main reason for writing this blog is to talk about this method. Every time you need to reinstall the operating system, you need to back up all the MySQL databases in the system. If you use the above method to back up one by one, it is obviously not good. I did this before, because the restoration was always unsuccessful, so I gave up. In this way, I learned from the Internet and books that MySQL's InnoDB engine is required. This engine is installed by default. You need to back up the data folder in the MySQL directory during backup. However, if you find that this method is incorrect, you need to back up the data directory hidden in the MySQL folder on disk C, that is where mysql Data is actually stored.

The following is the MySQL installation directory. We can see that our database does not exist, as shown below:

Next, we will display the hidden file and find it. In the folder option, deselect the check box before hiding the file, as shown below:

In this way, the hidden folder "All Users" is displayed in the user folder of drive C. As follows:

In the data folder in the MySQL folder under its directory, we can see the files of our current database. :

This method can be used if you want to quickly remove or back up MySQL. Back up the corresponding folder of the database. If you want to restore the database, you only need to put it in this hidden directory.

Do not forget. When you finish the operation, it is best to hide the hidden directory again to avoid deleting files in the system in the future.