For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:
RENAME TABLE old_db.table TO new_db.table;
You will need to adjust the permissions after that.
For scripting in a shell, you can use either of the following:
mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \
do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done
for table in `mysql -u root -ppassword -s -N -e "use old_db;show tables from old_db;"`; do mysql -u root -ppassword -s -N -e "use old_db;rename table old_db.$table to new_db.$table;"; done;
- There is no space between the option
-p and the password. If your database has no password, remove the
-u username -ppassword part.
If some table has a trigger, it cannot be moved to another database using above method (will result
Trigger in wrong schema error). If that is the case, use a traditional way to clone a database and then drop the old one:
mysqldump old_db | mysql new_db
If you have stored procedures, you can copy them afterwards:
mysqldump -R old_db | mysql new_db
Connecting to MYSQL with Python 2 in three steps
1 - Setting
You must install a MySQL driver before doing anything. Unlike PHP, Only the SQLite driver is installed by default with Python. The most used package to do so is MySQLdb but it's hard to install it using easy_install. Please note MySQLdb only supports Python 2.
For Windows user, you can get an exe of MySQLdb.
For Linux, this is a casual package (python-mysqldb). (You can use
sudo apt-get install python-mysqldb (for debian based distros),
yum install MySQL-python (for rpm-based), or
dnf install python-mysql (for modern fedora distro) in command line to download.)
For Mac, you can install MySQLdb using Macport.
2 - Usage
After installing, Reboot. This is not mandatory, But it will prevent me from answering 3 or 4 other questions in this post if something goes wrong. So please reboot.
Then it is just like using any other package :
db = MySQLdb.connect(host="localhost", # your host, usually localhost
user="john", # your username
passwd="megajonhy", # your password
db="jonhydb") # name of the data base
# you must create a Cursor object. It will let
# you execute all the queries you need
cur = db.cursor()
# Use all the SQL you like
cur.execute("SELECT * FROM YOUR_TABLE_NAME")
# print all the first cell of all the rows
for row in cur.fetchall():
Of course, there are thousand of possibilities and options; this is a very basic example. You will have to look at the documentation. A good starting point.
3 - More advanced usage
Once you know how it works, You may want to use an ORM to avoid writing SQL manually and manipulate your tables as they were Python objects. The most famous ORM in the Python community is SQLAlchemy.
I strongly advise you to use it: your life is going to be much easier.
I recently discovered another jewel in the Python world: peewee. It's a very lite ORM, really easy and fast to setup then use. It makes my day for small projects or stand alone apps, Where using big tools like SQLAlchemy or Django is overkill :
from peewee import *
db = MySQLDatabase('jonhydb', user='john', passwd='megajonhy')
author = peewee.CharField()
title = peewee.TextField()
database = db
book = Book(author="me", title='Peewee is cool')
for book in Book.filter(author="me"):
This example works out of the box. Nothing other than having peewee (
pip install peewee) is required.
So I'm not sure Matt Rogish's answer is going to help 100%.
The problem is that MySQL* has a mutex (mutually exclusive lock) around opening and closing tables, so that basically means that if a table is in the process of being closed/deleted, no other tables can be opened.
This is described by a colleague of mine here: http://www.mysqlperformanceblog.com/2009/06/16/slow-drop-table/
One excellent impact reduction strategy is to use a filesystem like XFS.
The workaround is ugly. You essentially have to nibble away at all the data in the tables before dropping them (see comment #11 on the link above).