I have InnoDB databases and they are using the same ibdata file, the size of this ibdata is around 250G. now i have one of those DBs around 100G. My manager asked me to move this DB to another server and to use innodb_file_per_table.
now i can't leave the system off for a long time. so i need your advice please, which is faster:
- mysqldump -uusername -pmypassword ––extended-insert ––disable-keys ––quick MyDb>Mydb.sql and then on the second server mysql -uusername -ppass mydb
- OR to copy the ibdata file to my other server and go over all of the tables and do this:
- ALTER TABLE table_name DISABLE KEYS;
- ALTER TABLE table_name engine=myisam;
- ALTER TABLE table_name engine=innodb;
- ALTER TABLE table_name enable KEYS;
if you have better idea, please share it with us!
Thanks you for your help