Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
[SOLVED] mysql 5.5 migration - internal server databases
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Desktop Environments
View previous topic :: View next topic  
Author Message
Goverp
l33t
l33t


Joined: 07 Mar 2007
Posts: 638

PostPosted: Thu Sep 11, 2014 11:35 pm    Post subject: [SOLVED] mysql 5.5 migration - internal server databases Reply with quote

A question for mysql and/or akonadi and/or digikam experts, I suspect:

The eselect news item on mysql 5.5 upgrade points at a document that says to at least run mysql_upgrade, and preferably use mysqldump to create a backup and then restore that after the upgrade.
This appears relatively easy to do with a normal mysql server - for example:
Code:
/etc/init.d/mysql start
mysqldump -Ap > mybackup.sql
# upgrade mysql
mysql < mybackup.sql


However, KDE insists on storing stuff in an internal mysql database, as does digikam. So how can I connect to these databases using mysqldump? The backup from the previous step contains only my test database, not the akonadi stuff from KDE, nor anything from digikam. (OK, I know KDE will have changed the akonadi database in the next version, but that still leaves digikam using an internal database).

It appears that the applications connect to their internal servers by setting the UNIX_SOCKET environment variable; will that work with mysqldump? I presume that socket only exists while the application is running, in which case even if the backup works, I'll bet the restore fails! Is there a configuration file somewhere that will point /etc/init.d/mysql at the appropriate databases?

Maybe the best thing is to use the applications' tools to move their databases to the external mysql server, and then upgrade that. At least that would be possible from a command line, while KDE and the applications are closed.
_________________
Greybeard


Last edited by Goverp on Sat Sep 13, 2014 9:38 am; edited 1 time in total
Back to top
View user's profile Send private message
fernan82
n00b
n00b


Joined: 24 Jul 2014
Posts: 70

PostPosted: Fri Sep 12, 2014 12:33 am    Post subject: Reply with quote

See if this helps: https://forums.gentoo.org/viewtopic-t-997104-highlight-mysql+akonadi.html
Back to top
View user's profile Send private message
Goverp
l33t
l33t


Joined: 07 Mar 2007
Posts: 638

PostPosted: Fri Sep 12, 2014 8:57 am    Post subject: Reply with quote

fernan82 wrote:
See if this helps: https://forums.gentoo.org/viewtopic-t-997104-highlight-mysql+akonadi.html
Ah, thanks. There's a link in there to an article from stackoverflow that shows how to use
Code:
ps aux | grep mysql
to get the relevant startup parameters for mysql_upgrade. I'll see if it also works for mysqldump, and then try to restore it (after taking a full backup; this all feels a bit fragile).

I'm not too bothered about akonadi data, IIUC that's just a cache, but I think digikam's database is the only repository for the stuff I've done to my photo library.
_________________
Greybeard
Back to top
View user's profile Send private message
depontius
Advocate
Advocate


Joined: 05 May 2004
Posts: 3374

PostPosted: Fri Sep 12, 2014 11:57 am    Post subject: Reply with quote

I had a different problem a few weeks back. The 5.5 upgrade "happened to me", and I was unable to run mysql_upgrade. I forget if it was missing or something it depended on. In any case, I moved back to 5.1.

I know I need to move to 5.5, and probably soon. I think I want to go to mariadb-5.1, and then take that to mariadb-5.5.

I've done load-from-backup before - a few years back I even had to handle the partial-restore path on MythTV because of a corrupted database. But I do it so seldom that I have absolutely no comfort with it, and pretty much forget everything about it in between uses.
_________________
.sigs waste space and bandwidth
Back to top
View user's profile Send private message
Goverp
l33t
l33t


Joined: 07 Mar 2007
Posts: 638

PostPosted: Sat Sep 13, 2014 8:10 am    Post subject: Reply with quote

Well, I tried the backup/restore process, and now I'm picking up the pieces :-(

I assume it's possible to talk to the internal databases by running the application (akonadi or digikam), using
Code:
ps aux | grep mysql
as above to get the associated socket name, and then connecting to the internal server. So for example
Code:
mysql_upgrade --socket=/foo
However, I wasn't keen on trying to upgrade the database or worse restore from backup while the application is working.

Instead I logged out of KDE and ran a command-line session, started /usr/bin/mysqld using the parameters grepped from ps aux, so it pointed at the akonadi database, then dump, then close down the server:
Code:
/usr/sbin/mysqld --defaults-file=/home/paul/.local/share/akonadi/mysql.conf --datadir=/home/paul/.local/share/akonadi/db_data/ --socket=/tmp/akonadi-paul.OuHsmx/mysql.socket &
mysqldump --user=root --socket=/tmp/akonadi-paul.OuHsmx/mysql.socket -AER >backupakonadi.sql
mysqladmin --user=root --socket=/tmp/akonadi-paul.OuHsmx/mysql.socket shutdown

That ran OK, so now I had taken a dump Repeat for digikam. Next, emerge update mysql, @preserved_rebuild and revdep_rebuild as per the eselect news item (revdep_rebuild did nothing for me). Now restart the server, upgrade and restore.
Code:
/usr/sbin/mysqld --defaults-file=/home/paul/.local/share/akonadi/mysql.conf --datadir=/home/paul/.local/share/akonadi/db_data/ --socket=/tmp/akonadi-paul.OuHsmx/mysql.socket &
mysql_upgrade --user=root --socket=/tmp/akonadi-paul.OuHsmx/mysql.socket
mysql --user=root --socket=/tmp/akonadi-paul.OuHsmx/mysql.socket <backupakonadi.sql
mysqladmin --user=root --socket=/tmp/akonadi-paul.OuHsmx/mysql.socket shutdown
Hey, it all ran OK. Sadly, when I restarted KDE, kontact barfed all over and the akonadi configuration tester gave a host of warnings. :-( So much for dump/restore!
Perhaps if I'd just run mysql_upgrade in the above, it would have been happy. Too late to test that now. Oh well, I did take a backup of my home directory before I started, so I can rebuild and retry.

I guess this just proves a little knowledge is a dangerous thing.

<Edited to add -AER parameter to mysqldump call>
_________________
Greybeard


Last edited by Goverp on Sun Sep 14, 2014 9:07 am; edited 1 time in total
Back to top
View user's profile Send private message
Goverp
l33t
l33t


Joined: 07 Mar 2007
Posts: 638

PostPosted: Sat Sep 13, 2014 9:38 am    Post subject: Reply with quote

Argghhh! I simply mistyped one of the library names when I ran
Code:
revdep-rebuild --library=
. That's the trouble with being too cute. A simple revdep-rebuild without parameters cured the problem. So the backup/restore process works fine. Akonadi and digikam are working fine.
_________________
Greybeard
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Desktop Environments All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum