command line sql db replacement help requested - what good are /data/disk/arch/sql backups if they can't be extracted to give one .sql file ?

[EdNett]

When we try to use the auto-generated sql nightly backups of sites (in /data/disk/arch/sql) to update db .tar file over existing db of newly created site, extracting tar of sql backup on d9, d8 and d7 sites doesn't just give us 1 .sql file, but many - 25 or more!

So using the command line
mysql dbname < path_to_.sql_backup_dump

is onerous, to say the least, with the long sql file names it would have to be done 15 or 20 times with different file names for each site. Is there a preferred or best practices method of using a nightly .sql backup to write over an existing db to move a site to another server or restore a db dump?

What good are the backups made nightly in /data/disk/arch/sql ? What purpose do they serve if I can't use them?

The only way I have been able to take a drupal site from one boa server and install it on another using the old-fashioned way of creating a blank site on the new server (after changing the dns, of course), uploading the files directory from the losing server and taking an adminer export of the site's db on the losing server, and then uploading the .sql file obtained from the adminer export into the gaining server's db either on the command line or through the gaining server's adminer interface.

I realize that this isn't the the best way to move a site from one boa server to another, but it works, and the xboa site move doesn't work for us. (must be a problem with the ssh keys).

Thanks,

Ed

[pricejn2]

@EdNett There is this undocumented system control file to bypass mydumper -- see

boa/aegir/tools/system/mysql_backup.sh

Line 278 in 181bdf8

&& [ ! -e "/root/.mysql.force.legacy.backup.cnf" ]; then

Alternatively, myloader is the restore utility for backups generated with mydumper -- see https://github.com/maxbube/mydumper/blob/9d5963b24c72484ef5ad429a232a6a27a8386ec7/docs/myloader_usage.rst

[omega8cc]

These faster backups can be used with myloader, like this:

myloader --database=db_name --host=localhost --user=db_user --password=db_pass --port=db_port --directory=/path/to/backup/db/ --threads=4 --less-locking --overwrite-tables --verbose=1

This method is way faster and allows you to restore only specific tables, also with standard mysql or drush commands.

We need to document this feature.