Friday, August 26. 2005
Moving the MySQL's datadir directory.
Trackbacks
Trackback specific URI for this entry
No Trackbacks
Comments
Display comments as
(Linear | Threaded)
9. After the server is running for a few days properly, get rid of the old data.
10. Sleep
You mean if I follow these steps I don't get to sleep for a "few days"?
10. Sleep
You mean if I follow these steps I don't get to sleep for a "few days"?
Something's wrong I put in 3 > (angle brackets) followed by the text - "Meaning I don't get to sleep for days." But it didn't show up. Why?
I think the blog was stripping out the greater than angle brackets before displaying.
I think you should stop your server first!
...before step 3
...before step 3
ID - you stop mysql server before doing any of these steps.
DELETING THE IB_* files when doing the move was BAD ADVICE in my case FYI !!! I couldn't figure out forever why I couldn't get everything working again, kept getting the error below. But when I finally copied the original ib_* files back into the data dir, it all worked again GREAT. Here's the error I got without them:
070516 13:13:52 InnoDB error:
Cannot find table / from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
Look from section 15.1 of http://www.innodb.com/ibman.html
how you can resolve the problem.
070516 13:13:52 [ERROR] Slave: Error 'Got error 1 from storage engine' on query. Default database: ''. Query: 'replace into (groupid, ts, state) values (9,'2007-05-15 11:14:06',0)', Error_code: 1030
070516 13:13:52 InnoDB error:
Cannot find table / from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
Look from section 15.1 of http://www.innodb.com/ibman.html
how you can resolve the problem.
070516 13:13:52 [ERROR] Slave: Error 'Got error 1 from storage engine' on query. Default database: ''. Query: 'replace into (groupid, ts, state) values (9,'2007-05-15 11:14:06',0)', Error_code: 1030
All my databases are INNODB and I keep getting "#2003 - Can't connect to MySQL server on 'localhost' (10061) " after following this procedure...
Check the mysqld.err log to see what the problem is. From my guess, it would be permission problem
I found this question on Yahoo answers, but the question has since been deleted and as far as I know it it was not answered. I figured out an answer for it which solved the same problem I was having.
How to change MySQL datadir
Okay, so I want to change the datadir of my MySQL databases... but something doesn't seem to be working correctly for me.
***FYI: Operating System = Ubuntu 8.04***
What I have done:
created arbitrary directory /var/lib/mysql2 (as opposed to /var/lib/mysql .. I am just trying to test the method to change datadir)
sudo chown -R mysql:mysql /var/lib/mysql2
sudo /etc/init.d/mysql stop
sudo cp -r /var/lib/mysql /var/lib/mysql2
cd /var/lib/mysql2
sudo rm ibdata1
sudo rm ib_logfile0
sudo rm ib_logfile1
vi /etc/mysql/my.cnf
- corrected line to : datadir =/var/lib/mysql2
vi /etc/apparmor.d/usr.sbin/mysqld
- added lines: /var/lib/mysql2/ r, and /var/lib/mysql2/** rwk,
Now, when I try:
sudo /etc/init.d/mysql start
it fails to start, If I edit the /etc/mysql/my.cnf file and change the datadir back to /var/lib/mysql, the service starts up fine.
The solution is to restart apparmor with:
sudo /etc/init.d/apparmor restart
then start mysql:
sudo /etc/init.d/mysql start
How to change MySQL datadir
Okay, so I want to change the datadir of my MySQL databases... but something doesn't seem to be working correctly for me.
***FYI: Operating System = Ubuntu 8.04***
What I have done:
created arbitrary directory /var/lib/mysql2 (as opposed to /var/lib/mysql .. I am just trying to test the method to change datadir)
sudo chown -R mysql:mysql /var/lib/mysql2
sudo /etc/init.d/mysql stop
sudo cp -r /var/lib/mysql /var/lib/mysql2
cd /var/lib/mysql2
sudo rm ibdata1
sudo rm ib_logfile0
sudo rm ib_logfile1
vi /etc/mysql/my.cnf
- corrected line to : datadir =/var/lib/mysql2
vi /etc/apparmor.d/usr.sbin/mysqld
- added lines: /var/lib/mysql2/ r, and /var/lib/mysql2/** rwk,
Now, when I try:
sudo /etc/init.d/mysql start
it fails to start, If I edit the /etc/mysql/my.cnf file and change the datadir back to /var/lib/mysql, the service starts up fine.
The solution is to restart apparmor with:
sudo /etc/init.d/apparmor restart
then start mysql:
sudo /etc/init.d/mysql start
Morgan,
Do not delete the ibdata file, as that is where the innodb tables are stored.
Do not delete the ibdata file, as that is where the innodb tables are stored.
Thanks -- at last somebody actually did this before me in hardy herron
worked great, just like that on debian 5 with MySQL 5. I did it right after I had installed the MySQL server with apt-get install.
Once done I did a 'mysqlcheck -A -u root -p'
and everything was ok. GREAT & SIMPLE
Once done I did a 'mysqlcheck -A -u root -p'
and everything was ok. GREAT & SIMPLE
Calendar
|
July '09 | |||||
| Mon | Tue | Wed | Thu | Fri | Sat | Sun |
| 1 | 2 | 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 | ||
Contact me
Quicksearch
Categories
Syndicate This Blog
What else lives here
Copy-left
Everything written by me and placed on this site is in the public domain , except for any express restrictions included in them . Hack them further and enjoy :-)
Powered by
