How do i use mysqldump to create a copy of a database?

Instructor: Martin Gibbs Show bio

Martin has 20 years experience in Information Systems and Information Technology, has a PhD in Information Technology Management, and a master's degree in Information Systems Management. He is an adjunct professor of computer science and computer programming.

This lesson will cover the basics of copying MySQL databases. The syntax of copying a database will be detailed and explained, including the copying of databases from one machine to another.

Copying Databases

There are times when you need to make a copy of an existing database. The most common use is for data backups, but copies are also useful if major changes are planned to the structure of the original. Making a copy keeps the original intact.

Copying data to a new database in MySQL is a three-step process: First, the data is copied (dumped) to a temporary file that holds the SQL commands necessary to re-insert the data into the new database; next, the new database is created; finally, the SQL file is processed, and the data is inserted into the new database.

The MySQL commands used to carry out this process are (in order): mysqldump, mysqladmin, and mysql.

Syntax: Copying the Data

There are three steps needed to copy a database to another database:

1. Store the data to an SQL file (which will be loaded into the new database in subsequent commands). This filename can be any name you choose, but must end in .sql
2. Create the new database
3. Insert the data into the new database, using the .sql file created in the first command

The following example shows how data is copied from the Artists database to the Artists_New database. Note that these commands are entered at the command prompt of the MySQL interface:

mysqldump Artists > createcopy.sql mysqladmin create Artists_New mysql Artists_New < createcopy.sql

The first command copies (or dumps) the data into an SQL file (mysqldump), the second creates the new database (mysqladmin create), and the third runs the SQL command to insert the data/tables (mysql).

In order for this to work, you need to have at least SELECT (read/query) rights on the tables you are copying/dumping.

• Lesson
• Quiz
• Course

Copying a Database to Another Machine/Server

There may be times when you need to copy the database from one database to another machine entirely. A backup of a database is good, but for purposes of redundancy, sometimes having the copy on an entirely different system can provide more peace of mind. Additionally, you could be sharing databases with others and want to copy your version over to their machine.

Copying from one server to another also makes use of the mysqldump command.

mysqladmin -h other_server create Artists mysqldump Artists| mysql -h other_server Artists

The previous command creates the database Artists on the other server, then makes use of mysqldump to copy the current Artists database to the other server.

For larger databases, a combination of mysqldump and mysqlimport works best. It is a faster operation than only using mysqldump:

mkdir NEWDIR mysqldump --tab=NEWDIR Artists

In the previous example, NEWDIR would be the full directory path of the directory where the output of mysqldump will be saved.

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.

Become a Member

Already a member? Log In

 Back

Resources created by teachers for teachers

Over 30,000 video lessons & teaching resources‐all in one place.

Video lessons

Quizzes & Worksheets

Classroom Integration

Lesson Plans

I would definitely recommend Study.com to my colleagues. It’s like a teacher waved a magic wand and did the work for me. I feel like it’s a lifeline.

Back

Create an account to start this course today

Used by over 30 million students worldwide

Create an account