How to drop and re-populate mysql databases?

Question

I have a mysql database with 10 tables and need to drop and repopulate the database to run some performance tests. Using mysqldump, I can dump out the data. What steps should I follow next, to clear the database, and re-import? Will need to run this for different sizes of the database (i.e. tables with different number of rows) to calculate the db performance, so I need to make sure these steps can be replicated.

Solution 1

You might want to look at the options on mysqldump such as --add-drop-database and --add-drop-table. I'd probably go for --add-drop-database in this case.

Solution 2

1) take the dump

    drop database db_name
    create database db_name
    mysql -u user -p db_name < dump.sql

I am sure this can be repeated any number of times. Its idempotent

Solution 3

From the MySQL console:

mysql> DROP DATABASE [dbName]; 
mysql> CREATE DATABASE [dbName];
mysql> USE [dbName];
mysql> SOURCE [pathToSQLDump];