How to Migrate MySQL database to another

How to Migrate MySQL database to another

by

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)
Restoring Mysql Data Backup
  • 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
Restoring Mysql Data Backup to a new database
  • 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.

Leave a Comment