Author Topic: Recreate Roundcube address books from Mysql backup  (Read 685 times)

Offline pto

  • Newbie
  • *
  • Posts: 3
Recreate Roundcube address books from Mysql backup
« on: July 05, 2020, 02:47:27 PM »
I have had a roundcubemail-1.2-beta running on Debian with Mysql for several years with ZERO problems until the server had to upgraded.
I have now installed an updated server with 1.4.x on Sqlite3 - so nice! I went to Sqlite3 since Mysql got removed by Debian.
I can see that I have lost my (and much worse) my wifes address book.

I am planning now to make a virtual machine of the old server and restore the whole mysql database from a backup.
But where should I start to dig for the address books?

I have read https://github.com/roundcube/roundcubemail/wiki/Upgrade, https://github.com/roundcube/roundcubemail/wiki/FAQ and tried to read through the existing issues for this project.
I have seen https://github.com/JaimeObregon/export-roundcube-sql - have not tried it.

Note: I originally posted this (wrongly?) in https://github.com/roundcube/roundcubemail/issues/7472

Offline JohnDoh

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 2,511
Re: Recreate Roundcube address books from Mysql backup
« Reply #1 on: July 05, 2020, 03:19:06 PM »
The GitHub ossie tracker is for bugs and feature requests not support. This forum or the mailing lists are the place for support.

Once you get your old server restored then you'll find a 'contacts' table in the Roundcube database that is keyed on 'user_id' from the 'users' table and that should give you the info you are looking for.

PS. This is off topic for this forum but Debian replaced MySQL with MariaDB and provided an upgrade path when they did so. For the purposes of Roundcube MariaDB and MySQL are the same so there was no specific need to change database provider.
Roundcube Plugins: Contextmenu, SpamAssassin Prefs, and moreā€¦

Offline pto

  • Newbie
  • *
  • Posts: 3
Re: Recreate Roundcube address books from Mysql backup
« Reply #2 on: July 05, 2020, 05:30:54 PM »
I have tried to recreate the server today - but it got quite ugly, since quite a few Debian 8 packages have disappered after this distro is EOL (June 30 2020).

Offline pto

  • Newbie
  • *
  • Posts: 3
Re: Recreate Roundcube address books from Mysql backup
« Reply #3 on: July 06, 2020, 04:02:21 PM »
I won (YES)
I reinstalled a Debian 8 - had problems with the NET-IDNA2 package, but manually installed this in /usr/share/php - the rest via apt install.
I then got a roundcube webmail in the old version with dovecot and postfix.
Then I restored the config.php and the whole /etc/mysql and /var/lib/mysql
After adding user accounts I could log in and see the old address books and identities.

Phew....

Offline sw2090

  • Jr. Member
  • **
  • Posts: 10
Re: Recreate Roundcube address books from Mysql backup
« Reply #4 on: October 20, 2020, 08:34:58 AM »
well

addressbooks are stored in the Database as contacts and connected to a user via user_id.

So if you want to go from mysql to sqlite you would have to find the correct user_id in the mysql roundcube db table "users".
Then export all contacts from table contacts and collected_concats (if you want collected contacts too) related to that user_id.
Then import this to your sqlite roundcoube db and rewrite the user_id of the (collected) contacts to the new one.

This will of course require some knowledge in SQL.

I just did similar but within one db to transfer an addressboo and collected contacts from one user to annother...

Keep in mind that if you use  - like we do - different login methods (with password or with certificate or dovecot admin user(s)) then roundcube will create a seperate user for each one upon first login. You might be able to differ them via the suffix (*something) in the username!

hth
Sebastian