Copy database1 as database2

Databases
1

I have a database named database1 in mySQL.

database1 has 2 tables. and each table has some records.

I like to copy database1 as database2 with the tables and the records.

As the result of the copy above, database2 has 2 tables and its records as same as database1.

How can I copy database1 as database2?

2

The easiest thing would be to use mysqldump. See https://dev.mysql.com/doc/refman/5.6/en/mysqldump-copying-database.html

1 Like
4 
shell> mysqldump db1 > dump.sql

(Q1) What does the code above mean?
(I think it means that to create a temporal db as same as db1)

(Q2) What is the meaning of dump literally?
(I think dump means temporal)

shell> mysqladmin create db2

(Q3) What does the code above mean?
I think it means that to create db2.

shell> mysql db2 < dump.sql

(Q4) What does the code above mean?
(I think it means that to change the all data in dump sql into the data in db2.)

5

The “dump.sql” is not a database, it is a file, typically with CREATE TABLE and INSERT INTO queries.

So essentially the series of commands are something like

  1. write a file of queries that can be used to replicate the database in the state it is in at the time of the dump.
  2. create a database so there will be something to import the dump file into.
  3. import the dump file into the database.

In case you haven’t understood, “db1” should be the name of the database you want to export. “dump” can be named anything, I usually include a date eg. “mydb20180624.sql” but you can name it whatever makes the most sense for you. “db2” should be the name of your new database. In other words, “db1”, “dump” and “db2” are used to represent the actual names you will be using.

6

Also these commands are to be ran from your command line, not from within MySQL. They are command line programs, not queries.

7

[quote]
Also these commands are to be ran from your command line, not from within MySQL. They are command line programs, not queries[/quote]
Can I change the commands to SQL with your help?

8

No, they are completely different things.

If you don’t have access to the command line but can only do queries I would suggest you get an application like MySQL workbench to do the copying for you. See for example http://www.cfjoe.com/index.cfm/2011/4/6/Copying-a-database-in-MySQL-Workbench

9

If you have MySQL installed the tools should be in its bin folder or there abouts. Not always so easy to find them and the system path can be a bit of a chore to type into the power shell to get to them.

If you already have the mysql shell CLI in your start menu you can look at its properties to see where it points to. Other tools, such as mysqldump are likely in the same location.

EDIT
obsolete info struck

10

These visuals may be some help.

As I posted, finding the bin folder might be a bit of a pain. Mines at

(notice the old dump-date sql file at the bottom of the image)

Then open the Command Prompt

and change directory to the bin folder

now you can enter and run the mysqldump command

11

Just a note, @Mittineague’s visualization applies only on windows.

12

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.