Recently I had to alter some tables in our production database with minimal downtime. And also we had hardly any downtime by below process. The tables was around 90GB/400M+ rows table.
The steps I have followed were:
1) Take the initial dump from one of the slave by filtering the unwanted records/org
mysqldump -p -u <username> db_name table_name --no-create-info --skip-tz-utc --complete-insert --compact --skip-comments --skip-lock-tables --where " and id<10000 and auto_update_time>’2014-010-01’ " > temp.sql
Note: --skip-tz-utc is very important if you are having the timestamp fields
Use the appropriate filters need to be used. Please make sure the fields are indexed and you are running optimal queries
2) Create a new table with similar structure in data_cleanup database (if required add additional columns and/or indexes)
You can run below query to get the create schema of the current table
SHOW CREATE TABLE db_name.table_name;
OR you can create new table with same using below query
CREATE table data_cleanup.table_name like db_name.table_name;
## ( Auto increment id is flushed by the query )
3) Load the dump taken from the slave to new data_cleanup database
mysql -p -u data_cleanup -A < temp.sql
4) Load the delta data based on org_id or auto_update_time to temp database
## get the number of records
SELECT id into @temp_max_id from data_cleanup.table_name order by id desc limit 1;
SELECT @temp_max_id;
select count(*) from db_name.table_name
WHERE id > @temp_max_id;
## load the delta to temp table
INSERT INTO data_cleanup.table_name
SELECT * FROM db_name.table_name
WHERE id > @temp_max_id;
Note: If the number of records are more, please try to dump the delta from slave again and load the same and follow steps 1-3
5) Increase the auto_increment_id of the table if required
select count(*) from db_name.table_name
WHERE id > @temp_max_id;
SELECT @temp_max_id;
// replace new max id in the query properly
ALTER TABLE data_cleanup.table_name AUTO_INCREMENT=
<@temp_max_id + 1000>;
6) Swap the tables - Please make sure you are running this step as quick as possible
SELECT id into @temp_max_id from data_cleanup.table_name order by id desc limit 1;
SELECT @temp_max_id;
select count(*) from db_name.table_name
WHERE id > @temp_max_id;
INSERT INTO data_cleanup.table_name
SELECT * FROM db_name.table_name
WHERE id > @temp_max_id;
RENAME TABLE
`data_cleanup`.`table_name` to `data_cleanup`.`temp`,
`db_name`.`table_name` to `data_cleanup`.`table_name`,
`data_cleanup`.`temp` to `db_name`.`table_name` ;
7) You might need to take a delta of records which was inserted during the step 6, if required follow step 4. If required, you need to copy all updates to new table as well. Please do in the same connection, otherwise @temp_max_id wont be available.
INSERT INTO data_cleanup.table_name
SELECT * FROM db_name.table_name
WHERE id > @temp_max_id AND id < @temp_max_id+1000;
8)Test and run some random queries to warm up
Notes:
- During any such import, please make sure import is stopped and no import is running.
- Assumption made is, we are having a database named “data_cleanup” in dbmaster. If not create one using “CREATE DATABASE data_cleanup”
