selectively restoring a renamed database from a chunky sql dump file.

with a  multi GB dump.sql.gzip file, and the requirement is to restore one database from the multiple in the file, the first instinct is to break out some grep/sed/awk and generate a sub-set sql file that just contains the "CREATE DATABASE" and insert statements and apply that to the main database.

However if you have some requirement to rename the imported database, or some other import time alterations, this is something of ball-ache.

one way to un fuck this situation is to start another mysqld instance on another port, and then use the mysqldump/mysql tools to do the work of selecting and renaming the databases and tables as required...







first off you are going to need to needs loads of disk space to do the operations.

1) create a folder to hold the 2nd mysqld instance datadir

mkdir /vol/lib/restore


2) have mysql create a minimal mysql system in that folder;

/bin/sh mysql_install_db --skip-name-resolve --datadir=/vol/lib/restore --socket=/var/lib/mysql/restart.sock --log-error=/var/log/restore-mysqld.log --pid-file=/var/run/mysqld/restore-mysqld.pid --user=mysql

(probably dont need all those options, but that worked for me)


3) start the second mysqld instance using the restore data directory using some suitable alternative port.

/bin/sh /usr/bin/mysqld_safe   --no-defaults  --datadir=/vol/lib/restore --socket=/var/lib/mysql/restart.sock --log-error=/var/log/restore-mysqld.log --pid-file=/var/run/mysqld/restore-mysqld.pid --user=mysql --port 3307 



4) check the new instance is not mental


# mysql -uroot --host=127.0.0.1 --port=3307 -e "show databases"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

5) import the dump into the temporary instance

# mysql -f -uroot --port=3307 --host=127.0.0.1 < /vol/backups/mysql.sql

(or force the binlog disabled per session)
(echo "SET SESSION SQL_LOG_BIN=0;"; cat dump.sql) > dump_nobinlog.sql

6) login to the temporary instance and make any alterations, such as rename database

mysql -uroot --host=127.0.0.1 --port=3307 -e "rename database etce etc"

7) and the final somewhat cavalier step to migrate the new un fucked database to the main instance


mysqldump -uroot --host=127.0.0.1 --port=3307 --databases mydatabase | mysql -f -uroot





notes

the purpose of --no-defaults is because you don't want to use the my.cnf settings such as bin-log for the 2nd instance, however if it is the case that you have some settings in my.conf that are required, then you are probably going to have to not use --no-defaults or/and contruct the mysqld_safe options appropriately


To improve the speed of the inserts you can do the following;

http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html
increase bulk insert buffer size;
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_bulk_insert_buffer_size
which has a session scope

increase the ke_buffer size onyl has global scope



load data infile is supposed to be faster;
http://dev.mysql.com/doc/refman/5.0/en/load-data.html