Introduction
In today’s article, I will show you how to How to Migrate MySQL database to another. There are a couple of reasons why you will want to migrate your records and tables from one database to the other, overall, migrating databases is very common with WordPress developers.
If you’re migrating a WordPress database, then be ready to battle with errors such as Invalid default value for ‘create_date’ during the migration process. Not to worry, I have provided below a working solution to this problem and a super guide that will help you migrate your database, its structure, and records at ease.
You want to migrate all of your information from your old database to the new one, here is how to do it without leaving any vital information out.
What you need to complete this process
- Navicat: I will be using Navicat for this guide, but you can use any other software that allows you to access and manipulate your database.
Note: you can also carry out this process from the console, but it is a lot easier to do it from a GUI application.
See Also
Learn OOP Javascript the Easy way
Artisan Commands not Working, No output returned.
Tips to become a good developer
Creating a mysqldump backup of your database
Open Navicat or your prefered app and connect it to your database, for this tutorial, I am using Navicat
- Right-click on your DB
- Click on the Dumb SQL file
- and click structure and data (pick structure only if you only want to back up the structure and all of the tables in the DB without its content)
- Enter a name and choose a location to save the SQL file.
- and the process begins
Restoring Mysql Data Backup to a new database
Wait for the backup process to complete and then proceed to restore data to your new DB.
- Connect to your DB on Navicat
- Right-click on the database you want to migrate this data into
- Click on Execute SQL File
- Browse and select the backup file we just created from the previous stage
- Uncheck Continue on error
- Click on start and wait for the process to complete. While data migration is going on, keep an eye on the error count. If Navicat runs into any error, the migration process will automatically stop.
Invalid default value for ‘create_date’ solution
If you run into this error while trying to Migrate MySQL database to another, which you may like to do if you’re migrating a WordPress database, here is a solution.
- Install sublime text 3 (if not already installed)
- Use Sublime to open the backup file you created from Navicat
- Add this code below at the top of the file and rerun the SQL file again.
SET SQL_MODE='ALLOW_INVALID_DATES';
By default, MySQL is strick mode is set to NO_ZERO_DATE, don’t allow '0000-00-00' as a valid date. The code above will instruct MySQL to allow Invalid dates.