Copy MySql DB from one server to another

Question

I need to copy a MySQL database from a server on Linux to a server on Windows. I tried using mysqldump, but it does not seem to contain Stored Procs. I want to copy everything, i.e. Schema, data, stored procedures, triggers, etc.

Thanks Don

Answer 1

You need an option - routines " mysqldump .

From the documentation for the -routines flag:

A dump stores procedures (procedures and functions) from dumped databases. To use this option, SELECT privileges are required for the mysql.proc table. The result generated with -routines contains the CREATE PROCEDURE and CREATE FUNCTION statements to recreate the routines. However, these statements do not include attributes such as the routine creation and modification of timestamps. This means that when routines are reloaded, they will be created using timestamps equal to the reload time.

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

This option was added in MySQL 5.1.2. Before that, stored procedures were not reset. Regular DEFINER values ​​are not reset until MySQL 5.1.8. This means that prior to 5.1.8, when the routines are reloaded, they will be created using the qualifier set for the user reload. If you require routines recreated with their original identifier, reset and load the contents of the mysql.proc table directly as described previously.

Answer 2

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

try to reset. and import command:

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