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.
Database CopyCopying 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:
- 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
- Create the new database
- 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.sqlThe 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 ArtistsThe 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 ArtistsIn 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