Mysql – “Table doesn’t exist in engine” after moving tables from one VPS to another

debianmariadbMySQLphpmyadmin

I have two VPS's running right now. One, which I will refer to as the "old" server, runs on Debian 7 and gives this output for mysql --version:

mysql Ver 14.14 Distrib 5.5.44, for debian-linux-gnu (x86_64) using readline 6.2

The other, which I will refer to as the "new" server, is running on a brand new installation of Debian 8, and gives this output:

mysql Ver 15.1 Distrib 10.0.20-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

I am trying to move my databases & tables from the old server to the new server. I thought that I could do this by simply copying the /var/lib/mysql/ directory from the old server and overwriting the same directory on the new server. However after doing this, I now receive this error in phpMyAdmin on the new server when I click on any of the tables or databases:

#1932 - Table 'phpmyadmin.pma__tracking' doesn't exist in engine

However I can clearly see that it exists:

Is there a solution to this? I'm not sure what I did wrong. Thanks!

Best Answer

You can copy files from one server to the next to move your database. To do this, you must make sure that both instances of your DB are stopped. I also recommend using rsync to copy your database files. In addition to making sure that both database servers are stopped before moving the files, you will most likely need to change the ownership of your files. sudo chown --recursive mysql:mysql /var/lib/mysql before starting your new instance.

There are other options available to move your database. If your database isn't terribly large, mysqldump is probably the most simple. I have attached the documentation for mysqldump below as provided by MariaDB, as you are using MariaDB.

https://mariadb.com/kb/en/mariadb/mysqldump/

Another option to consider, a bit more complex but a little more flexible for size, would be Percona's xtrabackup.

https://www.percona.com/software/mysql-database/percona-xtrabackup

Related Solutions

MySQLDump wrong dump

Just reading the header you put in the question shows something interesting. In fact, the question shows three things:

MySQL dump 10.13 Distrib 5.1.34, for apple-darwin9.5.0 (i386) indicates you used mysqldump from apple-darwin9.5.0 (i386) binaries
Server version 5.0.51a-24+lenny2 shows the version of mysql you used mysqldump to dump from.
You wanted to load the mysqldump file into Ver 14.14 Distrib 5.1.57, for apple-darwin10.3.0 (i386) using readline 5.1

What a jumble of versions to do this with.

If you want to see if mysqldump has an issue with the line that has DATEDIFF, try dumping just the schema.

mysqldump --no-data --all-databases ... > MySQLSchema.sql

This will display ony the schema. No INSERTs will be in the output. You can then hunt down that lines. You may also want to dump the data onyl without the schema,

mysqldump --no-create-info --all-databases ... > MySQLData.sql

Splitting the dumps allows you to load the schema into an editor and see if there are any problems. If you do not see any problems, load the MySQLSchema.sql into the target server. If the error is reproduced, you can fix the schema file and reload. Once the schema is loaded, you can separate load MySQLData.sql

BTW you should use mysqldump binary whose version is 5.0.51a-24+lenny2. Use dumps from version as mysqld is usually better to port and may minimize problems like this.

Give it a Try !!!

Mysql – How to clean up tables that don’t belong into thesql/thesql database

No clean way...

USE MyDatabase;
RENAME TABLE mysql.mytable1 TO mytable1;
RENAME TABLE mysql.mytable2 TO mytable2;

etc.

You said "large number"... You could get the list thus:

SELECT CONCAT('RENAME TABLE mysql.', TABLE_NAME, ' TO ', TABLENAME, ';')
    FROM information_schema.TABLES
    WHERE schema_name = 'mysql';

But be very careful not to rename the system tables!

Best Answer

Related Solutions

MySQLDump wrong dump

Mysql – How to clean up tables that don’t belong into thesql/thesql database

Related Question