Changing MySQL datadir?

guizhou_donkey · #1 16th August 2009, 01:07 PM

Hello,

Has anyone been able to successfully change MySQL's default datadir in Fedora? I've tried on two machines now (F7 and F11), but run into the same problem on both.

Here is what I did:

1. shutdown MySQL (service mysqld stop)
2. create new mysql directory (e.g. ~/mysql/) and set ownership to mysql:mysql
3. move tables from /var/lib/mysql to the new directory (I've tried moving only one user-created table, and also moving system tables with same result).
4. edit my.cnf and change "datadir" to point to new location
5. start MySQL (service mysqld start)

The problem is, however, that MySQL won't start again after moving the datadir:

[user@localhost mysql-test]$ sudo service mysqld start
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL: [FAILED]

I tried running "myqld_safe -vvv", and checking /var/log/mysqld.log:

090816 07:45:34 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
090816 07:48:41 mysqld_safe Starting mysqld daemon with databases from /home/user/mysql-test
090816 7:48:41 [Warning] Can't create test file /home/user/mysql-test/localhost.lower-test
090816 7:48:41 [Warning] Can't create test file /home/user/mysql-test/localhost.lower-test
090816 7:48:41 [Note] Plugin 'ndbcluster' is disabled.
/usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist
090816 7:48:41 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
090816 7:48:41 InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.
090816 07:48:41 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
090816 07:52:55 mysqld_safe Starting mysqld daemon with databases from /home/user/mysql-test
090816 7:52:55 [Note] Plugin 'ndbcluster' is disabled.
/usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist
090816 7:52:55 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
090816 7:52:55 InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
090816 7:52:55 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
090816 7:52:55 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
090816 7:52:56 InnoDB: Started; log sequence number 0 0
090816 7:52:56 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
090816 07:52:56 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
090816 08:01:32 mysqld_safe Starting mysqld daemon with databases from /home/user/mysql-test
090816 8:01:32 [Note] Plugin 'ndbcluster' is disabled.
/usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist
090816 8:01:32 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
InnoDB: Log scan progressed past the checkpoint lsn 0 39548
090816 8:01:32 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 0 46409
090816 8:01:32 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
40 41 42 43 44 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
090816 8:01:32 InnoDB: Started; log sequence number 0 46409
090816 8:01:32 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
090816 08:01:32 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Finally, after searching the web some, it looked like many people who faced similar problems were able to resolve the issues by adjust SELinux. I tried temporarily disabling it, however, but the result was the same.

This is as far as I've gotten. If anyone has any suggestions, they would be greatly appreciated.

Thank you!

EnglandA · #2 16th August 2009, 01:56 PM

You have rebooted since disabling SELinux?

guizhou_donkey · #3 16th August 2009, 02:11 PM

Yep. same thing :\

EnglandA · #4 16th August 2009, 02:47 PM

So the problem is that mysql will need permissions to access /home/user/mysql-test, that means /home/user/ then /home/user/mysql-test all need their permissions adjusted.

guizhou_donkey · #5 16th August 2009, 04:34 PM

Thanks for the suggestion EnglandA,

I tried moving the tables into a subdirectory, and gave both the parent and sub-directories the correct ownership and privileges as you suggested, but still no luck:

90816 11:29:48 mysqld_safe Starting mysqld daemon with databases from /home/user/test-mysql/mysql-test
090816 11:29:48 [Note] Plugin 'ndbcluster' is disabled.
/usr/libexec/mysqld: Table 'plugin' is read only
090816 11:29:48 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
InnoDB: Log scan progressed past the checkpoint lsn 0 39548
090816 11:29:48 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 0 46409
090816 11:29:48 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 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
090816 11:29:48 InnoDB: Started; log sequence number 0 46409
090816 11:29:48 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'host' is read only
090816 11:29:48 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

I'm going to try looking into the second to last error ("090816 11:29:48 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'host' is read only") a little more. It looks like that is what is ultimately causing the server to fail to start.

If you have any other suggestions, feel free to let me know.

Thanks!

xtian · #6 13th October 2009, 04:52 AM

Cruz'n the forums while I wait for my post to get answered. THis prob sounds like a good one. What happened? C

guizhou_donkey · #7 13th October 2009, 05:02 PM

Quote:

What happened?

I gave up

I'd still be very interested to know though if anyone else has been able to figure this one out.

robertmaessen · #8 20th October 2009, 07:57 AM

I always move the DB's into another place, the steppes I follow are just a little bit easier.

## example
create a partition data (during partitioning or afterwords with new disks) chmod 755 /data
"# mv /var/lib/mysql /data"
"# ln -s /data/mysql /var/lib"

If SElinux is enabled you would need to relabel it, otherwise you are up and running.