I need to change the name of my MySQL database but I’m not sure about the safest approach.
Right now I export the entire database and then import it back with the different name I want. This works fine for smaller databases but becomes really slow and impractical when dealing with large databases that have lots of data.
I heard there used to be a RENAME DATABASE command but from what I understand it caused problems and was removed from most MySQL versions because it wasn’t reliable.
My database uses InnoDB storage engine so I need a solution that works properly with InnoDB tables. I know InnoDB handles data storage differently compared to MyISAM so I want to make sure whatever method I use won’t corrupt my data or cause issues.
What would be the most efficient and safe way to rename a MySQL database without having to do a full dump and restore?
yep, i feel ya! a way to do it is to make a new db and run CREATE TABLE new_db.table_name AS SELECT * FROM old_db.table_name for all tables. way quicker than a full dump, and i haven’t had any issues with InnoDB. just remember to update your connection strings!
Hit this exact issue last year with a 50GB production database. Best approach I found was mysqldump with --single-transaction plus sed to swap database names on the fly. Run mysqldump --single-transaction --routines --triggers old_db | sed ‘s/old_db/new_db/g’ | mysql new_db - works great for InnoDB since single-transaction keeps everything consistent. Skips the table-by-table mess that breaks foreign keys and stored procedures. Slower than CREATE TABLE AS SELECT but way safer when you can’t afford data corruption.
This topic was automatically closed 4 days after the last reply. New replies are no longer allowed.