Backing Up Views with Mysql Dump

In terms of answering this question, olliiiver's answer is the best for doing this directly. For my answer I will try to build that into a comprehensive full backup and restore solution.

With the help of the other answers in this question, and a few other resources, I came up with this script for easily replacing the database on my development server with a live copy from the production server on demand. It works on one database at a time, rather than all databases. While I do have a separate script for that, it is not safe to share here as it basically drops and recreates everything except for a select few databases, and your environment may vary.

The script assumes root system and MySQL user on both machines (though that can be changed), working passwordless SSH between servers, and relies on a MySQL password file /root/mysqlroot.cnf on each machine, which looks like this:

[client]
password=YourPasswordHere

File: synctestdb.sh, optionally symlinked to /usr/sbin/synctestdb for ease of use

Usage: synctestdb DBNAME DESTSERVER

Run it from the production server.

Here it is:

#!/bin/bash

if [ "${1}" != "" ] && [ "${1}" != "--help" ] && [ "${2}" != "" ] ; then

    DBNAME=${1}
    DESTSERVER=${2}
    BKDATE=$( date "+%Y-%m-%d" );
    SRCHOSTNAME=$( /bin/hostname )
    EXPORTPATH=/tmp
    EXPORTFILE=/tmp/${SRCHOSTNAME}_sql_${BKDATE}_devsync.sql
    CREDSFILE=/root/mysqlroot.cnf
    SSHUSER=root

    DBEXISTS=$( echo "SHOW DATABASES LIKE '${DBNAME}'" \
      | mysql --defaults-extra-file=${CREDSFILE} -NB INFORMATION_SCHEMA )

    if [ "${DBEXISTS}" == "${DBNAME}" ] ; then
        echo Preparing --ignore-tables parameters for all relevant views
        echo
        #build --ignore-table parameters list from list of all views in
        #relevant database - as mysqldump likes to recreate views as tables
        #we pair this with an export of the view definitions later below
        SKIPVIEWS=$(mysql --defaults-extra-file=${CREDSFILE} \
          -NB \
          -e "SELECT \
            CONCAT( '--ignore-table=', TABLE_SCHEMA, '.', TABLE_NAME ) AS q \
            FROM INFORMATION_SCHEMA.VIEWS \
            WHERE TABLE_SCHEMA = '${DBNAME}';" )

        if [ "$?" == "0" ] ; then

            echo Exporting database ${DBNAME}
            echo
            mysqldump --defaults-extra-file=${CREDSFILE} ${SKIPVIEWS}  \
              --add-locks --extended-insert --flush-privileges --no-autocommit \
              --routines --triggers --single-transaction --master-data=2 \
              --flush-logs --events --quick --databases ${DBNAME} > ${EXPORTFILE} \
              || echo -e "\n\nERROR: ${SRCHOSTNAME} failed to mysqldump ${DBNAME}"
            echo Exporting view definitions
            echo
            mysql --defaults-extra-file=${CREDSFILE} \
              --skip-column-names --batch \
              -e "SELECT \
                CONCAT( \
                'DROP TABLE IF EXISTS ', TABLE_SCHEMA, '.', TABLE_NAME, \
                '; CREATE OR REPLACE VIEW ', TABLE_SCHEMA, '.', TABLE_NAME, ' AS ', \
                VIEW_DEFINITION, '; ') AS TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS \
                WHERE TABLE_SCHEMA = '${DBNAME}';" >> ${EXPORTFILE} \
              || echo -e "\n\nERROR: ${SRCHOSTNAME} failed to mysqldump view definitions"
            echo Export complete, preparing to transfer export file and import
            echo
            STATUSMSG="SUCCESS: database ${DBNAME} synced from ${SRCHOSTNAME} to ${DESTSERVER}"
            scp \
              ${EXPORTFILE} \
              ${SSHUSER}@${DESTSERVER}:${EXPORTPATH}/ \
              || STATUSMSG="ERROR: Failed to SCP file to remote server ${DESTSERVER}"
            ssh ${SSHUSER}@${DESTSERVER} \
              "mysql --defaults-extra-file=${CREDSFILE} < ${EXPORTFILE}" \
              || STATUSMSG="ERROR: Failed to update remote server ${DESTSERVER}"
            ssh ${SSHUSER}@${DESTSERVER} \
              "rm ${EXPORTFILE}" \
              || STATUSMSG="ERROR: Failed to remove import file from remote server ${DESTSERVER}"
            rm ${EXPORTFILE}
            echo ${STATUSMSG}

        else
            echo "ERROR: could not obtain list of views from INFORMATION_SCHEMA"
        fi

    else
        echo "ERROR: specified database not found, or SQL credentials file not found"
    fi

else
    echo -e "Usage: synctestdb DBNAME DESTSERVER \nPlease only run this script from the live production server\n"
fi

So far it appears to work, though you may want to tweak it for your purposes. Be sure that wherever your credentials file is, it is set with secure access rights, so that unauthorized users cannot read it!

As it seems to be difficult to export views properly, I adapted olliiiver's answer to make it so that first we delete any tables or views with the exact names of valid views on the database we are importing into in case they exist, then importing all tables, which may erroneously create those views as tables, then delete those tables and define those views properly.

Basically here is how it works:

• verify existence of the database you specified on the command line
• use MYSQLDUMP to create a dump file
• SCP the dump file from production to the specified test server
• issue import commands on the specified test server over SSH and return output
• remove dump file from both servers after complete
• issue some reasonable output for most steps along the way