1

I am trying to use a bat file to do a complete copy of a database to a different database. There are procedures and functions which need to be transferred over which is my biggest problem.

When I use 

mysqldump -u user -p password db1 -v -R | mysql -u user -p password db2

It will copy all of the tables no problem, but hangs when it comes to the procedures and functions with this

..
-- Sending SELECT query...
-- Retrieving rows...

I have quite a few functions and procedures that needs to be copied over. Any help would be appreciated.

Conflict
  • 13
  • 3

1 Answers1

1

ROOT CAUSE

  • When you mysqldump stored procedures, it requires locking mysql.proc
  • You are loading the same table you are dumping from AND IT'S LOCKED !!!

SUGGESTION

Load the data first. Write Stored Procedures to a text file. The, load the code.

mysqldump -u user -p password db1 -v --skip-routines | mysql -u user -p password db2
mysqldump -u user -p password db1 -t -d --routines > stored_procs_from_db1.sql
mysql -u user -p password db2 < stored_procs_from_db1.sql

GIVE IT A TRY !!!

RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132