Last tested on Ubuntu 16.04.01 LTS (xenial) with MySQL Ver 14.14 Distrib 5.7.13

We had to make a copy of existing databases for development app instances.  For example, a database called xp_main was for the production and xpdev_main would be for development. This depends on how date strings were created, but if you have a lot of dates in the records you may want to turn off the NO_ZERO_DATE mode.  If you don’t turn it off, the copying process can be interrupted. Go into your MySQL console.

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+


As you can see NO_ZERO_DATE exists.  Copy paste the entire string w/o NO_ZERO_DATE.

mysql> set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit
Bye

Next, we will copy the database using mysqldbcopy utility.  You may need to install mysql-utilities package if you don’t have it available.

$ mysqldbcopy --drop-first --source=root:mypassword@localhost --destination=root:mypassword@localhost xp_main:xpdev_main
WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Copying database eh_bcbs renamed as ehdev_bcbs
# Copying TABLE xp_main.accesses
# Copying TABLE xp_main.accessflags
# Copying TABLE xp_main.activities
# Copying TABLE xp_main.activitytype_items
# Copying TABLE xp_main.encounter_goals
# Copying TABLE xp_main.files
# Copying TABLE xp_main.tester1_intake_subseqvisit_goals
# Copying TABLE xp_main.tester1_game_careplan_goals
# Copying TABLE xp_main.localgames
# Copying TABLE xp_main.roles
# Copying GRANTS from xp_main
# Copying data for TABLE xp_main.accesses
# Copying data for TABLE xp_main.accessflags
# Copying data for TABLE xp_main.activities
# Copying data for TABLE xp_main.activitytype_items
# Copying data for TABLE xp_main.encounter_goals
# Copying data for TABLE xp_main.files
# Copying data for TABLE xp_main.tester1_intake_subseqvisit_goals
# Copying data for TABLE xp_main.tester1_game_careplan_goals
# Copying data for TABLE xp_main.localgames
# Copying data for TABLE xp_main.roles
#...done.

That should do it!