copy MySql DB from one server to another

Question

Hi,

I need to copy a MySQL DB from a server on Linux to a server on Windows. I've tried using mysqldump but it doesn't seem to include Stored Procs. I want to copy everything, i.e. schema, data, stored procs, triggers, etc.

Thanks, Don

Answer 1

You could try to use phpmyadmin's export feature. Not sure if it does stored procs, but check it out.

Answer 2

You want the "--routines" option of mysqldump.

From the documentation for --routines flag:

Dump stored routines (procedures and functions) from the dumped databases. Use of this option requires the SELECT privilege for the mysql.proc table. The output generated by using --routines contains CREATE PROCEDURE and CREATE FUNCTION statements to re-create the routines. However, these statements do not include attributes such as the routine creation and modification timestamps. This means that when the routines are reloaded, they will be created with the timestamps equal to the reload time.

If you require routines to be re-created with their original timestamp attributes, do not use --routines. Instead, dump and reload the contents of the mysql.proc table directly, using a MySQL account that has appropriate privileges for the mysql database.

This option was added in MySQL 5.1.2. Before that, stored routines are not dumped. Routine DEFINER values are not dumped until MySQL 5.1.8. This means that before 5.1.8, when routines are reloaded, they will be created with the definer set to the reloading user. If you require routines to be re-created with their original definer, dump and load the contents of the mysql.proc table directly as described earlier.

Answer 3

mysqldump -u root -p --routines --databases io \
| sed -e "s/;;/\$\$/g" \
> io.sql

please try dump. and import command to :

mysql -u root -p --fource --databases io < io.sql