MySQL dump restore – how to retain comments

backupMySQLmysqldump

So I have that complex MySQL dump as a backup. Unfortunately, that backup is complex and we use evil triggers.

Some of our evil triggers had a comment lines starting with the # character.

When I backup the database, the resulting dump save the # lines. The problem is when I am doing the restore. The line starting with # are replaced by empty lines.

So I am looking for a way to restore the dump WITH the lines starting with the pound.

Best Answer

OMG I know your problem !!!

Here is the problem: The mysql client filters out comments.

-c, --comments      Preserve comments. Send comments to the server. The
                    default is --skip-comments (discard comments), enable
                    with --comments.

Just load the mysqldump file like this:

mysql -u... -p... --comments < dumpfile.sql

Give it a Try !!!

STEP01) Zap All Binary Logs on the Master (OPTIONAL)

Run this command on the Master:

mysql> RESET MASTER;

If this part scares you, you could either skip it or make a copy of the binary logs before doing it

STEP02) Create the mysqldump as follows:

echo "STOP SLAVE;" > MySQLSlaveReload.sql
mysqldump -v --master-data=1 --single-transaction --routines ....  >> MySQLSlaveReload.sql
echo "START SLAVE;" >> MySQLSlaveReload.sql

STEP03) Move the `MySQLSlaveReload.sql` to the Slave

STEP04) Load `MySQLSlaveReload.sql` on the Slave

On the Slave, load the script as follows:

mysql -u... -p... -A < MySQLSlaveReload.sql

The script will stop the slave, load the data, and start the slave. What about the log file and position? Before you do STEP02, look at line 22 of MySQLSlaveReload.sql

head -22 MySQLSlaveReload.sql | tail -1

The mysqldump option --master-data=1 recorded the log file and position as of the start of the mysqldump on line 22.

Give it a Try !!!

Mysql – Can’t restore thesqldump database backup in Ubuntu 12.04

Try

mysql -u ROOT -p DATABASE_NAME < DATABASE.SQL

instead of

mysqldump -u ROOT -p DATABASE_NAME < DATABASE.SQL

Best Answer

Related Solutions

Mysql – Restoring MySQL database with db dump and binlogs

STEP01) Zap All Binary Logs on the Master (OPTIONAL)

STEP02) Create the mysqldump as follows:

STEP03) Move the MySQLSlaveReload.sql to the Slave

STEP04) Load MySQLSlaveReload.sql on the Slave

Mysql – Can’t restore thesqldump database backup in Ubuntu 12.04

Related Question

STEP03) Move the `MySQLSlaveReload.sql` to the Slave

STEP04) Load `MySQLSlaveReload.sql` on the Slave