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.

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`.
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