Alot of the time I find myself backing up large database tables.
Trying to add those tables into a local environment is not always easy when the database is big.
There is an option of exporting and importing single single tables but there are downsides to this.
Problems with restoring MYSQL databases
- In a production environment on larger websites, a database is always active, by the time you backup 1 table, another table could be written too and on relationship databases this messes up the integrity of the data.
- Sometimes even single tables can be extremely large, there are times when you need to back up a single table with millions of rows. This on its own can take forever.
How to backup or restore MYSQL databases:
The following procedure makes it easier to import or restore MYSQL databases:
This procedure assumes you’ve already downloaded a backup of your database from your server or web host.
Using the command line on Windows
- Open up PhpMyAdmin and create a new database, I usually append the date as a reference like this mywebsitename_2019_feb.
- Open up the CMD terminal and cd to the mysql folder, in XAMPP this is usually located at: C://xampp/mysql/bin
- Once you are in that directory, run the following command
mysql -u root -p mywebsitename_2019_feb < C:\backup.sql
- Execute the command with “enter” and wait. It may ask for a password which is usually a blank value
-ustands for username which in this case is = “
-pstands for password, in this case it is blank so I left it blank, if there was a password it would be added like this: “
mywebsitename_2019_febis the name of the new database I created
C:\backup.sqlis the absolute path to the file I want restored