Topic: Backup and restoring of mysql databases

[Arnaud]

Hi,

webapps very often use mysql databases.

Question: how to backup and restore these databases separately 1 by 1 without restoring the whole server?

Background: due to the breakdown of the power supply one of the database crashed today. I had to
- backup the whole server "today"
- save locally on the client machine the emails younger than "yesterday" (only possible for my account)
- restore the whole server "yesterday"
- copying the recent emails back
Problem: if existing, the most recent emails or files of other accounts are gone...

What I tried:
- restoring "yesterday"
- phpmyadmin: export of the database still running
- restoring "today"
- phpmyadmin: import of the database => stays not usable by the webapp. Why????
- phpmyadmin: delete the database and import again => error occured

Bye
Arnaud

[mmccarn]

I usually do it like this:

On the old server
* Determine the name of the database you're looking for (DBNAME)
* Create the backup file using the command below (taken from /etc/e-smith/events/pre-backup/S20mysql-dump-tables):

Code: [Select]

mysqldump --add-drop-table -QB DBNAME > DBBACKUP.sql

On the new server
* Look in the config file for your web app to get the DBUSERNAME and DBPASSWORD it is using
* Create the database and grant the required privileges

Code: [Select]

mysql -e "create database DBNAME;
grant all privileges on DBNAME.* to DBUSERNAME@localhost identified by 'DBPASSWORD' with grant option;
quit"

* Restore the backup created on the old server

Code: [Select]

mysql DBNAME < DBBACKUP.sql


Additional notes
* When creating your database you should make sure to use the correct database engine (InnoDB or MyISAM).  The SME default usually works.
* To backup from or restore to mysql55 you need to add a "connect" instruction such as "--socket=/var/lib/mysql/mysql55.sock":

Code: [Select]

mysqldump --socket=/var/lib/mysql/mysql55.sock --add-drop-table -QB DBNAME > DBBACKUP.sql
* Standard SME backups include backups of all databases in /home/e-smith/db/mysql/
* Lots of online instructions also include compression & decompression instructions - I've ignored those here.

[mmccarn]

- phpmyadmin: import of the database => stays not usable by the webapp. Why????

My guess would be that you need to create the webapp user in the new sql server using the correct password, and verify that the database engine and collate order have been set correctly.  SME defaults usually work, but might not for your specific app.

Problem: if existing, the most recent emails or files of other accounts are gone...

If the missing emails exist on the old server you could get them on the new server using:

Code: [Select]

# get the user files and folders from the old server
rsync -rltzq -e "ssh -p xxx" "root@legacySME_IP:/home/e-smith/files/users/" "/home/e-smith/files/users/"
#
# fix file ownership for all users
cd /home/e-smith/files/users
for f in *; do chown -R $f:$f $f; done


[Jean-Philippe Pialasse]

the wollowing would give you a backup per database :

https://wiki.contribs.org/AutoMysqlBackup

stored in /root/backup/db

[Arnaud]

Hi,
many thanks for this full detailed information! 

- I could identify at least 1 cause why what I tried didn't work:

My guess would be that you need to create the webapp user in the new sql server using the correct password

Yes, for the first trial, I didn't thought that maybe the user or its password is damaged.

For the second trial, I wanted to restore after having deleted the database => that was certainty the reason of the error at the import.

Question: most contribs "webapps" generate a random password for the owner of their database at the installation and this password gets crypted => not so easy to get the used password  (for re-entering it into the existing db or configuring a new db)

-

Standard SME backups include backups of all databases in /home/e-smith/db/mysql/

I'm surprised: by me this folder is empty inspite I have many databases!

- I was not aware that restoring the folder of the users is sufficient to get the emails back: I thought that restoring the internal database of SME were necessary too..... That is a very good news!

rsync -rltzq

I use "rsync -arPvH" for restoring from an archive of Affa and there is no need to chown the restored files. If I'm right this is due to the "-a" = "-rlptgoD" and "-o" preserve the owner, "g" the group and "p" the permissions.
=> would "rsync -arzq" be sufficient to avoid the fixing of ownership afterwards?

Bye
Arnaud

[Jean-Philippe Pialasse]

Standard SME backups include backups of all databases in /home/e-smith/db/mysql/
-I'm surprised: by me this folder is empty inspite I have many databases!

you need to issue signal-event pre-backup to populate it

I use "rsync -arPvH" for restoring from an archive of Affa and there is no need to chown the restored files. If I'm right this is due to the "-a" = "-rlptgoD" and "-o" preserve the owner, "g" the group and "p" the permissions.
=> would "rsync -arzq" be sufficient to avoid the fixing of ownership afterwards?

it might, take the time to read man rsync to see what you need

[janet]

Arnaud

I was not aware that restoring the folder of the users is sufficient to get the emails back: I thought that restoring the internal database of SME were necessary too..... That is a very good news!

While the above is correct, keep in mind that the webmail part of sme server uses mysql database to store user preferences.
So if you setup user preferences within webmail, then you do need to restore the appropriate mysql database (if you want those preferences restored).
Otherwise you will need to manually re-enter the user preference settings in webmail.

[mmccarn]

- I was not aware that restoring the folder of the users is sufficient to get the emails back: I thought that restoring the internal database of SME were necessary too..... That is a very good news!
I use "rsync -arPvH" for restoring from an archive of Affa and there is no need to chown the restored files. If I'm right this is due to the "-a" = "-rlptgoD" and "-o" preserve the owner, "g" the group and "p" the permissions.
=> would "rsync -arzq" be sufficient to avoid the fixing of ownership afterwards?

I believe that rsync restores ownership by user and group ID numbers, while the files are restored by username.  Allowing rsync to set the ownership on the restored files will only work if the users and groups on the new system have the same ID numbers that they had on the old system.

When I did my SME9 upgrade I re-created my user accounts manually, so the numbers didn't match, hence the extra step to reset ownership on the user folders.

Question: most contribs "webapps" generate a random password for the owner of their database at the installation and this password gets crypted => not so easy to get the used password  (for re-entering it into the existing db or configuring a new db)

I've never seen a webapp that didn't have the database password in plain text in the app's configuration file.

...
    'driver' => 'mysql',
    'database' => 'databasename',
    'username' => 'username',
    'password' => 'plain-text-password',
...

...
// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define('DB_NAME', 'wordpress');

/** MySQL database username */
define('DB_USER', 'wp_user');

/** MySQL database password */
define('DB_PASSWORD', 'plain-text-dbpasswordhere');

/** MySQL hostname */
define('DB_HOST', 'localhost');

/** Database Charset to use in creating database tables. */
define('DB_CHARSET', 'utf8');

/** The Database Collate type. Don't change this if in doubt. */
define('DB_COLLATE', '');
...

$conf['sql']['password'] = 'really-long-auto-generated-but-still-plain-text-password';
$conf['sql']['socket'] = '/var/lib/mysql/mysql.sock';
$conf['sql']['protocol'] = 'unix';
$conf['sql']['database'] = 'horde';
$conf['sql']['charset'] = 'utf-8';
$conf['sql']['splitread'] = false;
$conf['sql']['phptype'] = 'mysql';
$conf['sql']['params']['phptype'] = 'mysql';

[Jean-Philippe Pialasse]

I believe that rsync restores ownership by user and group ID numbers, while the files are restored by username.  Allowing rsync to set the ownership on the restored files will only work if the users and groups on the new system have the same ID numbers that they had on the old system.

rsync default is to match real username groupname

            --numeric-ids           don’t map uid/gid values by user/group name

            By  default rsync will use the username and groupname to determine what ownership to give files. The special uid 0 and the special group 0 are never
              mapped via user/group names even if the --numeric-ids option is not specified.

I think however that it will match to destination username mapping using the initial numeric id. Which indeed could cause mismatch

What I suggest to avoid this to use the internal backup restore or to use lazy admin tool so you will dump your users / numeric id and restore them

[Arnaud]

Hi,

https://wiki.contribs.org/AutoMysqlBackup

Excellent, this is exactly what I was looking for! 

I've never seen a webapp that didn't have the database password in plain text in the app's configuration file.

First of all, many thanks for these examples. For webapps installed by hand I know that the password of the db must be entered at the installation.

My question was more concerning the webapps installed via a contrib.
I will certainly blame myself, but it doesn't matter: I had a look into the source file of the contrib "smeserver-kplaylist" some times ago and I saw into /root/etc/e-smith/db/configuration/migrate/85kplaylist:

Code: [Select]

{
    my $rec = $DB->get('kplaylist')
        || $DB->new_record('kplaylist', {type => 'service'});
    my $pw = $rec->prop('DbPassword');
    return "" if $pw;
    if (not $pw or length($pw) < 25)
    {
      use MIME::Base64 qw(encode_base64);
      $pw = "not set due to error";
      if ( open( RANDOM, "/dev/urandom" ) )
      {
          my $buf;
          if ( read( RANDOM, $buf, 25 ) != 25 )
          {
              warn("Short read from /dev/random: $!");
          }
          else
          {
              $pw = encode_base64($buf);
              chomp $pw;
          }
          close RANDOM;
      }
      else
      {
          warn "Could not open /dev/urandom: $!";
      }
      $rec->set_prop('DbPassword', $pw);
    }
}
/root/etc/e-smith/templates/etc/e-smith/sql/init/80kplaylist_mysql.sql:

Code: [Select]

# KPLAYLIST MySQL init template
#
# This files creates/updates KPLAYLIST's MySQL database infos

USE mysql;
CREATE DATABASE IF NOT EXISTS { $kplaylist{DbName} };
REPLACE INTO user (
                     host,
                     user,
                     password)
            VALUES (
                     'localhost',
                     '{ $kplaylist{DbUser} }',
                     PASSWORD ('{ $kplaylist{DbPassword} }'));
REPLACE INTO db (
                   host,
                   db,
                   user,
                   select_priv, insert_priv, update_priv, delete_priv,
                   create_priv, alter_priv, index_priv, drop_priv, create_tmp_table_priv,
                   grant_priv, lock_tables_priv, references_priv)
          VALUES (
                   'localhost',
                   '{ $kplaylist{DbName} }',
                   '{ $kplaylist{DbUser} }',
                   'Y', 'Y', 'Y', 'Y',
                   'Y', 'Y', 'Y', 'Y', 'Y',
                   'N', 'Y', 'Y');
FLUSH PRIVILEGES;
And into the spec file there is:

Code: [Select]

KPLPASS=$(/sbin/e-smith/db configuration getprop kplaylist DbPassword)
I interpreted this by a generation of a password with re-use in the config.

Arnaud

[Jean-Philippe Pialasse]

My question was more concerning the webapps installed via a contrib.
I will certainly blame myself, but it doesn't matter: I had a look into the source file of the contrib "smeserver-kplaylist" some times ago and I saw into /root/etc/e-smith/db/configuration/migrate/85kplaylist:

Code: [Select]

{
    my $rec = $DB->get('kplaylist')
        || $DB->new_record('kplaylist', {type => 'service'});
    my $pw = $rec->prop('DbPassword');
    return "" if $pw;
    if (not $pw or length($pw) < 25)
    {
      use MIME::Base64 qw(encode_base64);
      $pw = "not set due to error";
      if ( open( RANDOM, "/dev/urandom" ) )
      {
          my $buf;
          if ( read( RANDOM, $buf, 25 ) != 25 )
          {
              warn("Short read from /dev/random: $!");
          }
          else
          {
              $pw = encode_base64($buf);
              chomp $pw;
          }
          close RANDOM;
      }
      else
      {
          warn "Could not open /dev/urandom: $!";
      }
      $rec->set_prop('DbPassword', $pw);
    }
}
/root/etc/e-smith/templates/etc/e-smith/sql/init/80kplaylist_mysql.sql:

Code: [Select]

# KPLAYLIST MySQL init template
#
# This files creates/updates KPLAYLIST's MySQL database infos

USE mysql;
CREATE DATABASE IF NOT EXISTS { $kplaylist{DbName} };
REPLACE INTO user (
                     host,
                     user,
                     password)
            VALUES (
                     'localhost',
                     '{ $kplaylist{DbUser} }',
                     PASSWORD ('{ $kplaylist{DbPassword} }'));
REPLACE INTO db (
                   host,
                   db,
                   user,
                   select_priv, insert_priv, update_priv, delete_priv,
                   create_priv, alter_priv, index_priv, drop_priv, create_tmp_table_priv,
                   grant_priv, lock_tables_priv, references_priv)
          VALUES (
                   'localhost',
                   '{ $kplaylist{DbName} }',
                   '{ $kplaylist{DbUser} }',
                   'Y', 'Y', 'Y', 'Y',
                   'Y', 'Y', 'Y', 'Y', 'Y',
                   'N', 'Y', 'Y');
FLUSH PRIVILEGES;
And into the spec file there is:

Code: [Select]

KPLPASS=$(/sbin/e-smith/db configuration getprop kplaylist DbPassword)
I interpreted this by a generation of a password with re-use in the config.

Arnaud

most of the contribs and the core do this way:
we search for an existing password in the config db, if not we generate one.
The same, we usually have migrate fragments for all config to correct previous version and match them to the new version.
In other words, if you restore a sme8 backup on a new sme9, after the post-upgrade and reboot all the migrate fragments will act to adapt obsolete values to the new version.

so for all passwrod you do not have to worry.
as long as you backup and restore mysql db almost all should be fine.

one last thing to know with contribs is that their main folder is out of SME backup targeted folders.
In other word, if the contribs store your music in /var/www/mymusic, this is not part of base backup and will not be restored, unless you make a separate backup.
The same apply for example for sme*admin db folder in /var/lib/sme*admin ... and almost all contribs.
We are moving toward integrate a backup for contribs, but it is still on ongoing work.
There is a wonderful work done at firewall service to help migrate contribs : https://wikit.firewall-services.com/doku.php/tuto/ipasserelle/divers/mise_a_jour_sme8_vers_sme9?s[]=sme9&s[]=sme8 (sorry French)

[mmccarn]

Code: [Select]

...
    my $pw = $rec->prop('DbPassword');
...
    if (not $pw or length($pw) < 25)
...
     if ( open( RANDOM, "/dev/urandom" ) )
...
          if ( read( RANDOM, $buf, 25 ) != 25 )
...
              $pw = encode_base64($buf);
...
      $rec->set_prop('DbPassword', $pw);
}

The installer is setting a random password for you... but the value stored in the SME db *is* the password.

You can verify this by:

* Display the kplaylist configuration using config show kplaylist

* login to mysql using the kplaylist user account and password

mysql -u kplaylist -p
... then copy/paste the password from when you ran config show...


If you have re-installed kplaylist on the new server it probably selected a new password for you -- but it should be using the new password unless you later restored the configuration database with the old kplaylist password in it.

[Arnaud]

The installer is setting a random password for you... but the value stored in the SME db *is* the password.

how could I ignore this! That is fantastic!

There is a wonderful work done at firewall service to help migrate contribs : https://wikit.firewall-services.com/doku.php/tuto/ipasserelle/divers/mise_a_jour_sme8_vers_sme9?s[]=sme9&s[]=sme8

I had a look on this page too when I've achieved the migration. The list of the folders to be backuped for each application is very great and useful.

Arnaud

[SchulzStefan]

I did an update of horde 5.2 as John suggested (https://forums.contribs.org/index.php/topic,50969.90.html). This leads to blank horde settings, means calendar, contacts, tasks... are gone.

I have an affa backup around 4 hours ago before the update. How can the horde tables be restored or exported and imported from the backup-server to the production-server?

As I don't know if this approach is making sense, alternativly restore only horde (with the database entries) from the backup to the production box.

Any help would be greatly appreciated.

Regards,
stefan

[Stefano]

restore the horde's db dump

then

Code: [Select]

mysql horde < /path/to/horde_db.dump

from CLI, logged as root

be sure the db name is horde