[mythtv-users] mysql disaster

Damian Haslam damian at dickson.st
Sun Nov 15 03:59:51 UTC 2009


On 15/11/2009, at 2:24 PM, R. G. Newbury wrote:
> Damian Haslam wrote:
>> On 15/11/2009, at 1:42 PM, Kevin Kuphal wrote:
>>> On Sat, Nov 14, 2009 at 8:17 PM, Damian Haslam <damian at dickson.st <mailto:damian at dickson.st>> wrote:
>>> 
>>>    Had a bit of a mishap yesterday, but I managed to save /var/lib/mysql
>>> 
>>>    Anyone got any hints on how I can go about restoring mysql on a
>>>    fresh ubuntu 9.10 install so I don't lose all my recordings?
>>> 
>>> 
>>> Maybe this can help you:
>>> 
>>> http://www.mythtv.org/wiki/Database_Backup_and_Restore#Database_Restore
>>> 
>>> Kevin
>> Thanks Kevin,
>> Unfortunately, I wasn't able to run a mysqldump before "the incident", and only managed to save the files from /var/lib/mysql, so that page isn't of much use I'm sad to say :(
> 
> You have not said, but it sounds like you have messed up your mysql installation. In that case you will have to:
> Stop mythbackend
> Stop the mysqld server (Service mysqld stop or equivalent)
> Copy or move the mythconverg database folder somewhere safe.
> If you have no other important mysql databases, you can delete the mysql database folder. (This presumes your setup is a pure BE+-FE machine, not an active desktop with VTiger, Nolapro etc running!)
> Remove and re-install mysql (Test it, to make sure it is running properly! Since it is a new install, remember to change the root password!)
> Start the mysqld server (service mysqld start)
> Use the mc.sql script to create an empty mythconverg database and grant permissions to the mythtv user.*
> Stop the mysqld service
> Copy your saved mythconverg database back into /var/lib/mysql.
> Start the mysqld service.
> Cross your fingers, sacrifice a chicken etc.
> Start mythbackend.
> Start mythfrontend.
> 
> * The mc,sql script is found a ....mythtv/database in an svn trunk checkout, and consists of this:
> 
> CREATE DATABASE IF NOT EXISTS mythconverg;
> GRANT ALL ON mythconverg.* TO mythtv at localhost IDENTIFIED BY "mythtv";
> FLUSH PRIVILEGES;
> GRANT CREATE TEMPORARY TABLES ON mythconverg.* TO mythtv at localhost IDENTIFIED BY "mythtv";
> FLUSH PRIVILEGES;
> ALTER DATABASE mythconverg DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
> 
> It is run from a console, by the mysyql root user as follows:
> 
> mysql -u root -pyourpasswordhere  < mc.sql
> 
> If your problem with mysql arose from a problem where /var filled up its partition, you may want to instead, after doing the above, and resurrecting things, consider moving the entire mysql directory to someplace like /home/mythtv and creating a symlink in /var/lib pointing to /home/mythtv/mysql. That way /var can fill up without affecting mysql. But /home needs to be on a different partition for this to work.
> 
> HTH
> Geoff
> 

Thanks Geoff - I ended up doing almost exactly what you said and I'm back up again - really appreciate your help.


More information about the mythtv-users mailing list