MySQL Tutorial/MySQL Utilities/mysqldump

Материал из SQL эксперт
Перейти к: навигация, поиск

Automatically drop any tables that exist before restoring the stored tables with the " --add-drop-table "

   <source lang="sql">

c:\mysql\bin\mysqldump -u root -p --add-drop-table myDatabase > myDatabase.sql</source>


Create the database if it doesn"t already exist with "--databases" option to specify the database we wish to back up

   <source lang="sql">

c:\mysql\bin\mysqldump -u root -p --databases myDatabase > myDatabase.sql</source>


mysqldump

mysqldump outputs the table structure and data in series of SQL commands stored in a text file.

The simplified syntax is



   <source lang="sql">
c:\mysql\bin\mysqldump -u <username> -p <database> [] > file.sql</source>

Optimising a dump

"--opt" is used override the mysql server"s normal method of reading the whole result set into memory giving a faster dump.



   <source lang="sql">

c:\mysql\bin\mysqldump -u root -p --opt myDatabase > myDatabase.sql</source>


Restoring a Dump

Restoring a dump depends on what you have actually dumped.



   <source lang="sql">

c:\mysql\bin\mysql -u root -p myDatabase < myDatabase.sql</source>


To make a copy of live data

To do this you would need to duplicate a local database.

First create the duplicate database:



   <source lang="sql">

mysql> CREATE DATABASE myDatabase2;</source>


Using mysqldump to copy databases

It is possible to combine a dump and a restore on one line by using a pipe "|" to pass the output of the dump directly to mysql basically bypassing the file.

We can use this method to copy a database to another server or even create a duplicate copy.

For example to copy the "myDatabase" database to a mysql server called "remote.server.ru":



   <source lang="sql">

c:\mysql\bin\mysqldump -u root -p --databases myDatabase | \ > mysql -u backup -p MyPassword -h remote.server.ru</source>


The "\" at the end of the first line means you wish to contine the command on another line before executing it.