Exporting (dumping) VIEWs Using mysqldump

Published December 29th, 2008

Problem Statement

Recently, we dumped a live database using mysqldump command and installed it on our development environment to find out that the VIEWs were NOT dumped by the mysqldump tool. Checking the live database we found that there are hundreds of views so manually creating them was not an option. We needed a shell script solution. Here we will discuss how we developed such a shell script.

Investigating the Problem

As stated above, when mysqldump is run as follows:

$ mysqldump -u [user] -p --opt [db name] > [db name].sql

it ignores the VIEWs in the database and only exports the tables. Investigating the large dump file, we noticed that the dump DID include the CREATE VIEW statements but since the CREATE VIEW statement for the view explicitly included a DEFINER value of user@hostname, they VIEWs were not included when we imported the dump. A sample of the original dump statements for a view is shown below

/*!50001 DROP TABLE IF EXISTS `view_accounting_adjustment_worksheet`*/;
/*!50001 DROP VIEW IF EXISTS `view_accounting_adjustment_worksheet`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`username`@`192.168.1.1` SQL SECURITY DEFINER */
/*!50001 VIEW `view_acc_adjustment` AS select * from table X where full_payment != 1;

Creating a shell script to export VIEWS correctly

So the solution was to create a simple shell script as follows:

#!/bin/sh
SRC_USER=root
SRC_PASS=secret
SRC_DB=mydb
SRC_DB_HOST=10.10.10.1
DEST_DB_HOST=localhost
DEST_USER=$SRC_USER
DEST_PASS=$SRC_PASS
DEST_DB=$SRC_DB

# External tools needed used by this script
MYSQLDUMP=/usr/bin/mysqldump
MYSQL=/usr/bin/mysql
FGRES=/usr/bin/fgres

# Dump database
$MYSQLDUMP -u $SRC_USER -p$SRC_PASS --opt $DB -h $DB_HOST > $DB.sql

# Replace the DEFINER lines with appropriate user@host
$FGRES "$SRC_DB_HOST"  "$DEST_DB_HOST" $DB.sql

# Now insert the database in destination
$MYSQL -u $DEST_USER -p$DEST_PASS -D $DEST_DB < $DB.SQL

This script simply dumps the database and then uses fgres to search and replace the source hostname in the file. Be aware that this will replace ANY instance of $SRC_DB_HOST in the SQL dump. So if you have data that might match this string, you cannot use this script.

Category: DBA | Tags: