MySQL – Dumping 1TB database with SED to replace definer

MySQLmysqldump

Im going to dump a large database more than 1TB using MySQLDUMP (unfortunately I have to stick with mysqldump, I can't use mydumper or any other backup tools)

And I want to replace the definer on the fly during the backup.

So if I use sed with dump command, will it take more time than the normal mysqldump?

mysqldump db name | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > dump.sql

Best Answer

First, dump only the routines, not any of the data. That way it should be quite fast -- drop a routine, rebuild it. Rinse, repeat.

As for the regexp, the format should be very regular, so [ ]* (or \s* can probably be simplified to .

[^*]* -->  .*\*

With some guts, simply

mysqldump ... | set -e '...' | mysql ...

I'm not clear that the regexp does the right thing; please provide a sample.

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 – Why Does mysqldump Require DROP Command?

By default mysqldump will output commands to drop tables, if they exist. This is why you get the error. It doesn't come from the mysqldump command, but from the mysql command you are piping the commands to. The user doesn't have permission to drop the table.

You can append --skip-add-drop-table to the mysqldump command to remove the DROP TABLE commands, but then you will get errors if the tables exist.

mysqldump parameters

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 – Why Does mysqldump Require DROP Command?

Related Question

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

STEP04) Load `MySQLSlaveReload.sql` on the Slave