Backing up MySQL databases

By | May 9, 2020

WordPress uses MySQL as its database backend and as a result it is important to upgrade and patch MySQL database versions when possible. This is especially true for older versions of MySQL which get installed by default with older WordPress Installers like those found on Web Platform Installer. Before proceeding with any such changes it is critical to back up your database files. In this post we explore how to export the database using MySQL workbench.

Where are MySQL data files located on Windows?

MySQL data files can be found at “C:\ProgramData\MySQL” by default.

You can create a copy of the folder just in case but the best way to take a backup is to use Workbench Import / Export.

Backing Up databases using MySQL Workbench

Install MySQL Workbench corresponding to your version of MySQL. Download can be found here. There are a few prerequisites like VC++ redistributable that will also need to be downloaded. You can refer the link here for details.

Login to your MySQL instance via Workbench using the Root account. Details on how to reset the password for non-Root users can be found here. Visit the later section “Changing the root password” in case you have lost the password and want to reset it.

Click the data export button

In the windows select all the databases you want to protect

Scroll to the bottom of the page. Ensure Dump Structure and data is selected in the drop Down list.

Check all the boxes under Objects To Export

Under export Options Select Export to Self-Contained File, make a note of the path, make sure to include Create Schema.

Check the Box Create Dump in a Single Transaction in order to ensure all or nothing restore. Click Start Export on the bottom right.

Once the database export it completed the Export Progress should say Export to path…. Has finished.

You can navigate to the folder location to view the .sql files in which the commands to create the database objects and data can be found.

Changing the root password for MySQL

https://phoenixnap.com/kb/how-to-reset-mysql-root-password-windows-linux