Fedora Linux Support Community & Resources Center
  #1  
Old 16th August 2009, 01:07 PM
guizhou_donkey Offline
Registered User
 
Join Date: Jul 2006
Location: Baltimore, MD
Age: 31
Posts: 53
linuxfedorafirefox
Changing MySQL datadir?

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!
__________________
Keith
http://opennfo.wordpress.com
Reply With Quote
  #2  
Old 16th August 2009, 01:56 PM
EnglandA Offline
Registered User
 
Join Date: May 2005
Location: UK
Posts: 250
linuxfedorafirefox
You have rebooted since disabling SELinux?
__________________
Registered Linux User #221895
Reply With Quote
  #3  
Old 16th August 2009, 02:11 PM
guizhou_donkey Offline
Registered User
 
Join Date: Jul 2006
Location: Baltimore, MD
Age: 31
Posts: 53
linuxfedorafirefox
Yep. same thing :\
__________________
Keith
http://opennfo.wordpress.com
Reply With Quote
  #4  
Old 16th August 2009, 02:47 PM
EnglandA Offline
Registered User
 
Join Date: May 2005
Location: UK
Posts: 250
linuxfedorafirefox
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.
__________________
Registered Linux User #221895
Reply With Quote
  #5  
Old 16th August 2009, 04:34 PM
guizhou_donkey Offline
Registered User
 
Join Date: Jul 2006
Location: Baltimore, MD
Age: 31
Posts: 53
linuxfedorafirefox
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!
__________________
Keith
http://opennfo.wordpress.com
Reply With Quote
  #6  
Old 13th October 2009, 04:52 AM
xtian Offline
Registered User
 
Join Date: Oct 2009
Posts: 133
linuxfedorafirefox
Cruz'n the forums while I wait for my post to get answered. THis prob sounds like a good one. What happened? C
Reply With Quote
  #7  
Old 13th October 2009, 05:02 PM
guizhou_donkey Offline
Registered User
 
Join Date: Jul 2006
Location: Baltimore, MD
Age: 31
Posts: 53
linuxsafari
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.
__________________
Keith
http://opennfo.wordpress.com
Reply With Quote
  #8  
Old 20th October 2009, 07:57 AM
robertmaessen Offline
Registered User
 
Join Date: Sep 2009
Posts: 31
linuxfedorafirefox
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.
Reply With Quote
Reply

Tags
datadir, mysql

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Moving MySQL datadir MichaelS-R Servers & Networking 1 12th September 2008 01:08 PM
changing datadir for mysql 5.0.27 csfalcon Using Fedora 3 5th February 2007 11:11 PM
mysql setup need help changing the password crowds Servers & Networking 3 19th September 2006 04:07 AM
Changing apache run group makes mysql cry Zeromus Using Fedora 0 12th August 2006 02:38 PM
Install mail server from Postfix/CyrusSASL2/PAM-MySQL/MySQL/Dovecot/Amavisd/Clamav chengcsw Guides & Solutions (No Questions) 7 26th April 2005 08:38 PM


Current GMT-time: 12:15 (Friday, 29-08-2014)

TopSubscribe to XML RSS for all Threads in all ForumsFedoraForumDotOrg Archive
logo

All trademarks, and forum posts in this site are property of their respective owner(s).
FedoraForum.org is privately owned and is not directly sponsored by the Fedora Project or Red Hat, Inc.

Privacy Policy | Term of Use | Posting Guidelines | Archive | Contact Us | Founding Members

Powered by vBulletin® Copyright ©2000 - 2012, vBulletin Solutions, Inc.

FedoraForum is Powered by RedHat