hotswap_mysql_table's Introduction

Hotswap mysql table

To alter a mysql table with minimal downtime. I have used this to alter table with size more than even 400GB.

Assumptions :

- id is the auto_increment column of the table 
- indexes present in both id and autoUpdateCol separately
- exception handling can be better; As DDL cannot be reverted/rollbacked
- works for addition of column, changing the columns meta, adding/removing indices
- fails in case of dropping a column ( insert into temp table will fail )

hotswap_mysql_table's People

Contributors

Stargazers

Watchers

hotswap_mysql_table's Issues

Data loss during table swap

	oldTable = tempTable + "_" + str(int(time.time()))
	cursor.execute("RENAME TABLE  "+originalTable +" to  " + oldTable + ", "+ tempTable + " to "+ originalTable)
	print "Swapped the tables now"
	print ("*"*20) + "PHASE 5 : Delta dump (data changed phase 3) " + ("*"*20)
	deltaFile = "/mnt/hotswap/"+ tempTable + "_3.sql"
	subprocess.call(secondaryDump + " --where 'id > "+ str(max_values["max_id"]) + " '> " + deltaFile , shell=True)
            subprocess.call(secondaryDump + " --where '"+autoUpdateCol+" >  \""+ str(max_values["max_time"]) + "\" AND id < "+str(max_values["max_id"])+ "' >> " + deltaFile , shell=True)

While secondaryDump is

secondaryDump = "mysqldump -p"+ password +" -u "+ username+" " + dbname + " " + originalTable.replace(".", " ", 1) \
+ " --no-create-info --skip-tz-utc --complete-insert --compact --skip-comments --skip-lock-tables --replace  " `

For the final dump it should be:

"mysqldump -p"+ password +" -u "+ username+" " + dbname + " " + oldTable.replace(".", " ", 1) \
+ " --no-create-info --skip-tz-utc --complete-insert --compact --skip-comments --skip-lock-tables --replace  "

Recommend Projects