[mythtv-users] mysql disaster

[R. G. Newbury]

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


-- 
         Please let me know if anything I say offends you.
          I may wish to offend you again in the future.

          Tux says: "Be regular. Eat cron flakes."