MENU
External Commands
In Windows, first, get access to various MySQL external commands by setting a path to the binary directory. For instance, enter in the command prompt (run as Administrator):> path=%path%;”c:\progra~1\MySQL\MySQL Server 5.6\bin”
| >mysqldump –uroot –pPASSWORD testDB > backup.sql | |
| This saves the database named testDB into the file backup.sql. Here the username is ‘root’ while the password is ‘PASSWORD’. As the host (-h) is not specified, localhost is assumed. To specify a port, use –P. | |
| >mysqldump –uroot –pPASSWORD testDB t1 t2> backup.sql | |
| This saves the tables named ‘t1’ and ‘t2’ from the database ‘testDB’ into ‘backup.sq’. | |
| >mysqldump -uroot -pPASSWORD -- databases db1 db2 db3 t1 t2> backup.sql |
|
| This saves the databases named ‘db1’, ‘db2’, and ‘db3’. ‘-- databases’ causes the CREATE DATABASE and USE statements to be included as well. |
|
| >mysqldump -uroot -pPASSWORD --all-databases > x.sql | |
| This dumps all databases. | |
| Other options | |
| --events | Dumps event schedulers. |
| --routines | Dumps stored procedures and functions. |
| --triggers | Dumps triggers. |
| --no-data | Dumps definitions only. |
| --no-create-info | Dumps data only. |
| --tab=dir_name | Dumps all output to the directory ‘dir_name”, using two files for each table. The .sql file contains the table definitions, while the .txt file contains the table data. |
| --fields-terminated-by=str | Specifies the fields separator for the .txt file. |
| --fields-enclosed-by=str | Specifies the fields enclosing character for the .txt file. |
| --fields-optionally-enclosed-by=str | Specifies the fields optional enclosing character for the .txt file. |
| --fields-escaped-by=str | Specifies the escape character for the .txt file. |
| --lines-terminated-by=str | Specifies the lines separator for the .txt file. |
If you try to read the .sql files saved above using a text editor, you will notice that they are runnable SQL commands that can be issued to recreate the databases. The following describes how to restore the databases using the .sql files.
| >mysql < backup.sql |
| This restores the databases, assuming backup.sql contains the CREATE DATABASE and USE statements. |
| >mysqladmin create db2 >mysql db2 < backup.sql |
| This restores the database in backup.sql to db2, even if it does not contain the CREATE DATABASE and USE statements. |
| >mysql db < backup.sql >mysqlimport db backup.txt |
| This restores the table by using the two files generated by: mysqldump --tab To restore the data, you may also issue within MySQL the command LOAD DATA INFILE… |
| SOURCE backup.sql |
| This restores the database from within MySQL. |