| View previous topic :: View next topic |
| Author |
Message |
Goverp
Advocate
Joined: 07 Mar 2007
Posts: 2006
|
 Posted: Thu Sep 11, 2014 11:35 pm Post subject: [SOLVED] mysql 5.5 migration - internal server databases |
 |
|
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 |
|
 |
fernan82
n00b
Joined: 24 Jul 2014
Posts: 70
|
|
| Back to top |
|
|
Goverp
Advocate
Joined: 07 Mar 2007
Posts: 2006
|
| Posted: Fri Sep 12, 2014 8:57 am Post subject: |
|
|
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 |
|
|
depontius
Advocate
Joined: 05 May 2004
Posts: 3509
|
| Posted: Fri Sep 12, 2014 11:57 am Post subject: |
|
|
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 |
|
|
Goverp
Advocate
Joined: 07 Mar 2007
Posts: 2006
|
| Posted: Sat Sep 13, 2014 8:10 am Post subject: |
|
|
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 |
|
|
Goverp
Advocate
Joined: 07 Mar 2007
Posts: 2006
|
| Posted: Sat Sep 13, 2014 9:38 am Post subject: |
|
|
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 |
|
|
|
|
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
|
|
Desktop Environments
