## 7.4.5 mysqldump Tips 

This section surveys techniques that enable you to use mysqldump  to solve specific problems: 

•How to make a copy a database 

•How to copy a database from one server to another 

•How to dump stored programs (stored procedures and functions, triggers, and events) 

## 7.4.5.1 Making a Copy of a Database 

•How to dump definitions and data separately 

shell> mysqldump db1 > dump.sql shell> mysqladmin create db2 shell> mysql db2 < dump.sql 

Do not use --databases  on the mysqldump  command line because that causes USE db1  to be included in the dump file, which overrides the effect of naming db2  on the mysql  command line. 

## 7.4.5.2 Copy a Database from one Server to Another 

On Server 1: 

shell> mysqldump --databases db1 > dump.sql 

Copy the dump file from Server 1 to Server 2. 

On Server 2: 

shell> mysql < dump.sql 

Use of --databases  with the mysqldump  command line causes the dump file to include CREATE DATABASE  and USE  statements that create the database if it does exist and make it the default database for the reloaded data. 

Alternatively, you can omit --databases  from the mysqldump  command. Then you need to create the database on Server 2 (if necessary) and specify it as the default database when you reload the dump file. 

On Server 1: 

shell> mysqldump db1 > dump.sql 

On Server 2: 

shell> mysqladmin create db1 shell> mysql db1 < dump.sql 

You can specify a different database name in this case, so omitting --databases  from the mysqldump command enables you to dump data from one database and load it into another. 

## 7.4.5.3 Dumping Stored Programs 

Several options control how mysqldump  handles stored programs (stored procedures and functions, triggers, and events): 

• --events : Dump Event Scheduler events 

• --routines : Dump stored procedures and functions 

