Migrating or upgrading mysql with a copy of the datadir

This is a wiki page. Be bold and improve it!

If you have any questions about the content on this page, don't hesitate to open a new ticket and we'll do our best to assist you.

Manipulating the datadir of a mysql installation is never recommended. Recommended ways to upgrade and/or migrate mysql are properly documented in the official documentation. In some situations, however, one may not have a choice but to recover the data from an older system by copying it into a newer system. But trying to start a new mysql installation with a copy of an older system, one may encounter many difficulties. Help us document here the difficulties you encounter and the solutions you find.

Failing to start mysql

Depending on how you start mysql, failing to start the server may display different symptoms. Here is a few of them.

error: 'Can't connect to local MySQL server through socket


# /usr/bin/mysqladmin -u root -h localhost -p
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!

See solution below.

No error output

Starting mysqld returns without any error output:

# mysqld --skip-grant-table
2016-12-18 23:14:26 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-12-18 23:14:26 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2016-12-18 23:14:26 0 [Note] mysqld (mysqld 5.6.34) starting as process 22132 ...

But mysql is still not running.

Solution

Check the mysql error logs, usually situated at /var/log/mysql/mysqld.err, for more detailed information on what's preventing mysql to run. See below for possible scenarios.

errno: 13 - Permission denied

Here are possible symptoms of failing to check the access permission of the datadir:

File './mysqld-bin.index' not found

# service mysql start
* Caching service dependencies ...               [ ok ]
* Executing: /lib64/rc/sh/openrc-run.sh /lib64/rc/sh/openrc-run.sh /etc/init.d/mysql start
* Checking mysqld configuration for mysql ...
2016-12-18 22:49:51 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2016-12-18 22:49:51 0 [Note] /usr/sbin/mysqld (mysqld 5.6.34-log) starting as process 21374 ...
2016-12-18 22:49:51 21374 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=mysqld-bin' to avoid this problem.
/usr/sbin/mysqld: File './mysqld-bin.index' not found (Errcode: 13 - Permission denied)
2016-12-18 22:49:51 21374 [ERROR] Aborting
2016-12-18 22:49:51 21374 [Note] Binlog end
* mysql config check failed           [ !! ]
* ERROR: mysql failed to start

See solution below.

Can't find file: './mysql/plugin.frm

/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13 - Permission denied)
2016-12-18 22:59:24 21595 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

See solution below.

InnoDB: ./ibdata1 can't be opened in read-write mode

/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13 - Permission denied)
2016-12-18 22:59:24 21595 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2016-12-18 22:59:24 21595 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-12-18 22:59:24 21595 [Note] InnoDB: The InnoDB memory heap is disabled
2016-12-18 22:59:24 21595 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-12-18 22:59:24 21595 [Note] InnoDB: Memory barrier is not used
2016-12-18 22:59:24 21595 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-12-18 22:59:24 21595 [Note] InnoDB: Using Linux native AIO
2016-12-18 22:59:24 21595 [Note] InnoDB: Not using CPU crc32 instructions
2016-12-18 22:59:24 21595 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-12-18 22:59:24 21595 [Note] InnoDB: Completed initialization of buffer pool
2016-12-18 22:59:24 21595 [ERROR] InnoDB: ./ibdata1 can't be opened in read-write mode
2016-12-18 22:59:24 21595 [ERROR] InnoDB: The system tablespace must be writable!
2016-12-18 22:59:24 21595 [ERROR] Plugin 'InnoDB' init function returned error.
2016-12-18 22:59:24 21595 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2016-12-18 22:59:24 21595 [Warning] /usr/sbin/mysqld: unknown option '--loose-federated'
2016-12-18 22:59:24 21595 [ERROR] Unknown/unsupported storage engine: InnoDB
2016-12-18 22:59:24 21595 [ERROR] Aborting

See solution below.

Error: Failed while fetching Server version!

                                                                                                                                # mysql_upgrade
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Error: Failed while fetching Server version! Could be due to unauthorized access.
FATAL ERROR: Upgrade failed

See solution below.

Solution

If you copy the datadir from another system, the mysql UID and GID may have changed.
Remember to assign the proper user and access permission to your datadir:

chown -R mysql:mysql /var/lib/mysql/
chmod -R 600 /var/lib/mysql/

InnoDB: auto-extending data file ./ibdata1 is of a different size

2016-12-18 23:21:48 22316 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-12-18 23:21:48 22316 [Note] InnoDB: The InnoDB memory heap is disabled
2016-12-18 23:21:48 22316 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-12-18 23:21:48 22316 [Note] InnoDB: Memory barrier is not used
2016-12-18 23:21:48 22316 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-12-18 23:21:48 22316 [Note] InnoDB: Using Linux native AIO
2016-12-18 23:21:48 22316 [Note] InnoDB: Not using CPU crc32 instructions
2016-12-18 23:21:48 22316 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-12-18 23:21:48 22316 [Note] InnoDB: Completed initialization of buffer pool                                                                                                             
2016-12-18 23:21:48 22316 [ERROR] InnoDB: auto-extending data file ./ibdata1 is of a different size 18048 pages (rounded down to MB) than specified in the .cnf file: initial 640 pages, max 8192 (relevant if non-zero) pages!
2016-12-18 23:21:48 22316 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
2016-12-18 23:21:48 22316 [ERROR] Plugin 'InnoDB' init function returned error.
2016-12-18 23:21:48 22316 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2016-12-18 23:21:48 22316 [Warning] mysqld: unknown option '--loose-federated'
2016-12-18 23:21:48 22316 [ERROR] Unknown/unsupported storage engine: InnoDB
2016-12-18 23:21:48 22316 [ERROR] Aborting

Solution:
check my.cnf and check the settings for ibdata1, like:

innodb_data_file_path = ibdata1:10M:autoextend:max:128M

The value should correspond to what existed on your old server. If the old server used the default values, commenting out the above setting might solve the problem.

See also:
https://bugs.mysql.com/bug.php?id=73521
This bug occurs when migrating straight from mysql 5.1 to 5.6, skipping 5.5. Such migrations are not officially supported.

mysql_upgrade: Table doesn't exist

gentoo-victoria mysql # mysql_upgrade -u root -p
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock'
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock'
Warning: Using a password on the command line interface can be insecure.
Error    : Table 'mydb.mytable1' doesn't exist
status   : Operation failed
Error    : Table 'mydb.mytable2' doesn't exist
status   : Operation failed
...

And in the error logs:
2016-12-18 23:37:32 22574 [Warning] InnoDB: Cannot open table mydb.mytable1 from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2016-12-18 23:37:32 22574 [Warning] InnoDB: Cannot open table mydb.mytable2 from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
...

Solution:
It is likely that during the migration, you left out the file ibdata1 in the datadir. This file is critical to read InnoDB tables. You have to migrate it together with the rest of your data.