Unable to move MySQL data to external drive

Question

I am not able to start mysql after moving the data to external harddrive.

My setup (Raspbian):

• Default MySQL data folder: /var/lib/mysql
• Target MySQL data folder: /media/exthdd/mysql_data

And this is how I tried to relocate my mysql data:

1. sudo service mysql stop
2. sudo mkdir /media/exthdd/mysql_data
3. sudo cp -adR /var/lib/mysql/ /media/exthdd/mysql_data
4. sudo chown mysql:mysql -R /media/exthdd/mysql_data
5. sudo chmod 771 -R /media/exthdd/mysql_data/
6. sudo nano /etc/mysql/my.cnf
7. datadir = /media/exthdd/mysql_data
8. sudo service mysql start

This is the error I got afterwards:

Job for mysql.service failed. See 'systemctl status mysql.service' and 'journalctl -xn' for details.

Running journalctl -xn I got:

No journal files were found.

For systemctl status mysql.service

mysql.service - LSB: Start and stop the mysql database server daemon

Loaded: loaded (/etc/init.d/mysql)

Active: failed (Result: exit-code) since Sat 2016年08月20日 18:58:48 CST; 2min 4s ago

Process: 27436 ExecStop=/etc/init.d/mysql stop (code=exited, status=0/SUCCESS)

Process: 373 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)

And error log sudo cat /var/log/mysql/error.log had this:

160820 19:05:50 mysqld_safe Starting mysqld daemon with databases from /media/exthdd/mysql_data

160820 19:05:50 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.

160820 19:05:50 [Note] /usr/sbin/mysqld (mysqld 5.5.49-0+deb8u1) starting as process 2403 ...

160820 19:05:50 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.

160820 19:05:50 [Note] Plugin 'FEDERATED' is disabled. /usr/sbin/mysqld: Table 'mysql.plugin' doesn't exist

160820 19:05:50 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

160820 19:05:50 InnoDB: The InnoDB memory heap is disabled

160820 19:05:50 InnoDB: Mutexes and rw_locks use GCC atomic builtins

160820 19:05:50 InnoDB: Compressed tables use zlib 1.2.8

160820 19:05:50 InnoDB: Using Linux native AIO

160820 19:05:50 InnoDB: Initializing buffer pool, size = 128.0M

160820 19:05:50 InnoDB: Completed initialization of buffer pool

160820 19:05:50 InnoDB: highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 49439

160820 19:05:50 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 1595675

160820 19:05:51 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed

160820 19:05:51 InnoDB:Waiting for the background threads to start

160820 19:05:52 InnoDB: 5.5.49 started; log sequence number 1595675

160820 19:05:52 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306

160820 19:05:52 [Note] - '127.0.0.1' resolves to '127.0.0.1';

160820 19:05:52 [Note] Server socket created on IP: '127.0.0.1'.

160820 19:05:52 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

160820 19:05:52 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

[UPDATE] I have updated mysql to 5.6.30-1, but the problem persists (although errors are different now). Also,dont be confused, I made a symlink from /var/lib/mysql -> /media/exthdd/mysql

orangepi@OrangePI:/var/lib/mysql$ sudo service mysql start orangepi@OrangePI:/var/lib/mysql$ sudo service mysql status

●くろまる mysql.service - MySQL Community Server

Loaded: loaded (/lib/systemd/system/mysql.service; enabled)

Active: inactive (dead) since Mon 2016年08月22日 03:55:30 CST; 40s ago

Process: 8151 ExecStartPost=/usr/share/mysql/mysql-systemd-start post>(code=exited, status=0/SUCCESS)

Process: 8150 ExecStart=/usr/bin/mysqld_safe (code=exited, status=0/SUCCESS)

Process: 8148 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre>(code=exited, status=0/SUCCESS) Main PID: 8150 (code=exited, status=0/SUCCESS)

Aug 22 03:55:00 OrangePI mysqld_safe[8150]: 160822 03:55:00 mysqld_safe Can't log to error log and syslog at the same time. Remove all --log-error configura...ke effect.

Aug 22 03:55:00 OrangePI mysqld_safe[8150]: 160822 03:55:00 mysqld_safe Logging to '/var/log/mysql/error.log'.

Aug 22 03:55:00 OrangePI mysqld_safe[8150]: 160822 03:55:00 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Aug 22 03:55:00 OrangePI mysqld_safe[8150]: 160822 03:55:00 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Aug 22 03:55:30 OrangePI systemd[1]: Started MySQL Community Server. Hint: Some lines were ellipsized, use -l to show in full.

And again error log:

orangepi@OrangePI:/var/lib/mysql$ sudo cat /var/log/mysql/error.log

160822 03:55:00 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

2016年08月22日 03:55:00 0 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.

2016年08月22日 03:55:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2016年08月22日 03:55:00 0 [Note] /usr/sbin/mysqld (mysqld 5.6.30-1) starting as process 8516 ...

2016年08月22日 03:55:00 8516 [Warning] Can't create test file /var/lib/mysql/OrangePI.lower-test

2016年08月22日 03:55:00 8516 [Warning] Can't create test file /var/lib/mysql/OrangePI.lower-test /usr/sbin/mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 13 - Permission denied)

2016年08月22日 03:55:00 8516 [ERROR] Aborting

2016年08月22日 03:55:00 8516 [Note] Binlog end

2016年08月22日 03:55:00 8516 [Note] /usr/sbin/mysqld: Shutdown complete

160822 03:55:00 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

It says it cannot create a test file, but mysql does have all the permissions and owns all the mysql folder/files.

• nijave: You may want to give rsync a try for the copying process instead of cp
• Tomas Stibrany: Copying wasn't the problem. I verified I copied all the files. I even tried to move the whole folder - didnt help either.

Answer 1

Your problem is that a mysql table from the main mysql database is missing or corrupt. More likely it didn't copy over. The table in question is mysql.host

so first, ensure every file, in /var/lib/mysql was copied over, specifically the files and directories that are not part of the databases you created. Since you are using MySQL 5.5.49, which is an old version. I reccomend in updating your server to a more stable version. Current up to date stable version is 5.7.14

Version 5.6.7 and above no longer use this table, therefore if you upgrade to this version or higher, you can just use mysql_upgrade command to upgrade your databases and it should return your server to working order.

But if for some reason you must use MySQL 5.5 versions, there is MySQL 5.5.51 which is the last stable version of the 5.5 branch. There is a couple of options you can try. You can use mysql_install_db to attempt to recreate the MySQL Database. Or if that does not work, you are going to have to remove the MySQL Database and then re-install your server to make it regenerate these tables.

Just to be clear, the database I am referring to is MySQL's main database that holds information about the server as well as the users etc. Not your Databases you have created.

• Tomas Stibrany: Thank you for your answer. I have updated mysql as you said (though only to v5.6.30-1), but now it says it cannot create test file even though it has all the permissions.
• nijave: Maybe su to the user mysql runs as and test to make sure you can read/write
• Frostalf: @TomasStibrany Ok, I researched the causes of this, and the common causes is, either you have SELinux enabled and needs to be disabled. Or your mysql server doesn't have read/write permissions to the temp directory that it is configured to use. Usually its /temp however it might be different for you.

Answer 2

Solved!

It wasn't the apparmor (SELinux, nor the permissions to /temp, it was permissions to parent folder of my external hdd.

So to be clear for the future readers:

I wanted to move mysql data from /var/lib/mysql to /media/exthdd/mysql_data.

After I moved the data, I correctly set mysql permissions for mysql_data folder

drwxrwx--- 7 mysql mysql 4096 Aug 22 20:52 mysql_data

BUT the parent folder's /media/exthdd permissions excluded mysql.

drwxrwx--- 4 orangepi www-data 4096 Aug 22 20:28 owncloud

So now I had 2 choices:

1. Allow access to exthdd for all users (chmod 777)
2. Add mysql to www-data group (www-data group owns the directory)

I went with the second option:

sudo usermod -a -G www-data mysql

And viola!

sudo service mysql start

sudo service mysql status

mysql.service - MySQL Community Server

Loaded: loaded (/lib/systemd/system/mysql.service; enabled)

Active: active (running) since Mon 2016年08月22日 21:03:52 CST; 3s ago

(Since @Frostalf has been most helpfull I accepted his answer)

• Alok Rajasukumaran: Second option did not help me