Mysql – Moving two databases from MySQL 5.1 [Prod] to MySQL 5.6 [Stage], not working [Windows]

backupMySQLmysqldumprestoreupgrade

I'm in the process of upgrading our production MySQL from 5.1 to 5.6, on a Windows Server 2008 R2 server. What I'm trying to achieve is to move 2 databases [say abc and xyz] from Prod server to a newly created MySQL 5.6 stage environment.

At first, I tried –

C:\>mysqldump -uroot -p abc > abc.sql [from the 5.1 prod server]
C:\>mysql -uroot -p abc < abc.sql [on the 5.6 stage server]

Here, I got the error –

ERROR 1049 <42000>: Unknown database 'abc'

When I opened the abc.sql file, I didn't find the two lines –

CREATE DATABASE /*!32312 IF NOT EXISTS*/ 'abc' /*!40100 DEFAULT CHARACTER SET utf8 */;
USE 'abc';

I thought to export the db by specifying –database option, so I tried –

C:\>mysqldump -uroot -p --database abc > abc.sql [from the 5.1 prod server]
C:\>mysql -uroot -p abc < abc.sql [on the 5.6 stage server]

Now, the CREATE DATABASE and Use <dbname> are there, but still I'm getting the error –

ERROR 1049 <42000>: Unknown database 'abc'

Anyone has any idea what I am doing wrong? Same is the case with the database 'xyz'.

Best Answer

Doing this

C:> mysqldump -uroot -p abc > abc.sql

does not include the two lines you mentioned.

On the Prod Server, do this

C:> mysqldump -uroot -p --databases abc > abc.sql

Using --databases abc makes mysqldump write those two lines

On the Staging Server, do this

C:> mysql -uroot -p < abc.sql

Don't specify abc upon reload. Those two lines will create abc (if it is not already there) and make abc the default database before loading the data.

Related Question