4,348
edits
MySQL: Difference between revisions
add Copying MySQL databases on the same server
add Copying MySQL databases on the same server |
|||
| Line 58: | Line 58: | ||
IMPORTANT: If your file is large, or you just have a lot of records, you may want to make sure you have something bigger than 16M for max_allowed_packet attribute in your my.cnf (usually found under /etc/mysql/ or /etc/mysql/mysql.conf.d/) on your new server where you're doing the import, else the server could hang on a large insert operation and your MySQL server may actually decide to go away, literally. On one of the servers I had it for 1024M just for this operation and brought it back low afterwards. | IMPORTANT: If your file is large, or you just have a lot of records, you may want to make sure you have something bigger than 16M for max_allowed_packet attribute in your my.cnf (usually found under /etc/mysql/ or /etc/mysql/mysql.conf.d/) on your new server where you're doing the import, else the server could hang on a large insert operation and your MySQL server may actually decide to go away, literally. On one of the servers I had it for 1024M just for this operation and brought it back low afterwards. | ||
== Copying MySQL databases on the same server == | |||
''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. | |||
<syntaxhighlight lang="mysql"> | |||
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 | | |||
+-------------------------------------------------------------------------------------------------------------------------------------------+ | |||
</syntaxhighlight> | |||
As you can see NO_ZERO_DATE exists. Copy paste the entire string w/o NO_ZERO_DATE. | |||
<syntaxhighlight lang="mysql"> | |||
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 | |||
</syntaxhighlight> | |||
Next, we will copy the database using mysqldbcopy utility. You may need to install mysql-utilities package if you don’t have it available. | |||
<syntaxhighlight lang="bash"> | |||
$ 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. | |||
</syntaxhighlight> | |||
That should do it! | |||
[[Category:System administration]] | [[Category:System administration]] | ||