Empty / Broken MySQLs Backup

If your MySQL backups are empty, really small or just broken this guide may be for you.

If you are on MySQL <=5.7 then this guide is for you. If you are on >=8 it is not (see Checking MySQL Version).

Migrating from other hosting providers to MDOQ

It's possible that when migrating to MDOQ, your MySQL dump files could include references to users which will not exist on MDOQ, so it's important to identity these since they could cause issues. 

If you are running into issues backing up your new data in MDOQ and are using MySQL 8+ you can try searching your original MySQL backup file for 'DEFINER', here you may see code for triggers that reference MySQL users from the previous server. If this is the case you can use the following SED command to update these references before importing into MDOQ.

sed -i 's/OLDUSER/NEWUSER/g' database-backup.sql

Please ensure you update the OLDUSER and NEWUSER to match accordingly, and if the OLDUSER is something specifically generic you may wish to review this if there is a risk of other matches in the dump file. NEWUSER will typically be 'magento'.

Checking MySQL Version

You can detmerine your version of MySQL by

• Checking the version specified in the MDOQ UI. Select the production instance, then navigate to MySQL > Version
• From ssh, connect to MySQL (using the credentils from app/etc/env.php) then run show variables like "%version%".

The Cause

Newer PHP images include newer default software. This includes the `mysqldump` tool, the new default is for mysqldump version 8. However mysqldump v8 is not fully backwards compatible with previous versions of MySQL.

The Fix

The solution here is to install mysqldump version 5.7 (until you upgrade MySQL to 8 or newer)

1. Create an instance.
2. Make the directory `mdoq/php-fpm` if it doesn't already exist
3. Run the following
   

   rm -rf /tmp/mysql-install || true \
       && mkdir /tmp/mysql-install \
       && wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz -O /tmp/mysql-install/mysql-5.7.42.tgz \
       && cd /tmp/mysql-install/ \
       && tar -xvf mysql-5.7.42.tgz \
       && cp /tmp/mysql-install/mysql-5.7.42-linux-glibc2.12-x86_64/bin/mysqldump ~/htdocs/mdoq/php-fpm/ \
       && rm -rf /tmp/mysql-install || true
       

   This will download and extract mysqldump v5.7 and move it to `mdoq/php-fpm/mysqldump`.
   N.B if the url doesn't exist, check https://downloads.mysql.com/archives/community/ for newer version.
4. If the file `mdoq/php-fpm/configure` doesn't exist, create is and make it executable.
5. Add the following to `mdoq/php-fpm/configure`
   

   cp mdoq/php-fpm/mysqldump /usr/bin/mysqldump

6. Ensure that the following php-fpm settings are configured
   
   • Post rollup commands: cd /home/magento/htdocs; ./mdoq/php-fpm/configure
   • Run command as: root
7. You should then add `mdoq/php-fpm/configure` and `mdoq/php-fpm/mysqldump` to source control
8. Follow the normal deployment process to get these two file changes to production. (If this is all you are releasing you can do a zero downtime deployment).
9. Once live, you can then sync PHP-FPM with the following seetings
   
10. Once the sync is complete, you should be able to SSH into production and run `mysqldump --version` this should report 5.7.
    You can then retry your backups