Import MySQL Locally

Replace URLs and Import MySQL database locally.

phpMyAdmin is a free software tool written in PHP that is intended to handle the administration of a MySQL or MariaDB database server.

There are numerous ways to import a database, the easiest by using phpMyAdmin, which comes with Xampp and can be accessed via http://localhost/phpmyadmin/.

However, we need to replace the server domain name (e.g., with the local virtual host one (e.g., wpbp.local) in the exported .sql file.

1. Search and replace URLs (with Powershell)

You can do this easily, just open Windows Powershell, navigate to the db_backups/ directory and run:

PS C:\Users\username\Dropbox\domain\db_backups> Get-Content database-2022-01-12.sql  | %{$_ -replace "", "https://domain.local"} > database-2012-01-12.local.sql

Note: You can do the above by opening VS Code and searching and replacing.

2. Fix encoding and line endings

There is only one more step we need to do before importing our database and testing the local site.

Note: If your Windows machine isn’t configured, then while replacing your URLs, your output file will not have the correct encoding and line endings, and you will get a MySQL error when importing the .sql file.

To fix this, open database-2012-01-12.local.sql file with VS Code, and be sure to convert it to UTF-8 and CLFR line endings from the bottom right corner of the app.

Then save and run the following command in your Powershell to import to database_local created with phpMyAdmin.

PS C:\Users\username\Dropbox\domain\db_backups> C:\Xampp\mysql\bin\mysql.exe -u root -p database_local < database-2012-01-12.local.sql