If the answer is positive, you should introduce yourself PT-ONLINE-SCHEMA-CHANGE.
How the Magic is Done?
Actually Percona imitates MySQL behavior with a little tweak.
When modifying a table structure MySQL copies the original table structure, modifies it, copies the data and finally renames the table.
The only problem w/ this behavior that it locks the original table...
Percona is doing the same, but instead of locking the original table, it reviews the latest changes and implements them on the new table. That way the original table still serves the users, and changes replacement is done in a single atomic process.
Percona Toolkit Installation
Download the Percona toolkit and install it (the following is relevant for Ubuntu):
> wget http://www.percona.com/downloads/percona-toolkit/2.2.13/deb/percona-toolkit_2.2.13_all.deb
> sudo dpkg -i percona-toolkit_2.2.13_all.deb
Making a Change
Just call the tool with permissions, database name (D flag), table name (t flag), command to execute (--alter flag) and finally use the execute flag to implement the changes.
pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor -uuser -p"password" --execute
Things to Notice
- You must have a primary key on the table
- If you want only to verify the process before replacing the tables themselves, use --dry-run instead of execute (or just drop this parameter).
Bottom Line
Modifying your database will cause performance degradation, but it should not result in a downtime.
Keep Performing,
Moshe Kaplan
