Mysql – How to backup big MySQL database on Linux

No, ytou don't have to take down the web site to do a MySQL backup. You not only can use mysqldump on a running MySQL database but in fact the server must be running for mysqldump to be able to connect to it. The fact that you're using InnoDB tables is a plus, as any locks will be brief and it's highly unlikely that your users will ever be aware of them.

If you are at all nervous or hesitant about performing the backup it is no big deal to run another instance of MySQL and set up master/slave replication, performing the backups on the slave. That way you can be completely certain the web site will be unaffected by the backups.

You can do it. Converting from MyISAM to InnoDB should not be a problem as InnoDB has more features which are not available in MyISAM such as transactions.

You can alter the tables while the database is running. No need for shutdown, but this operation will lock the tables.

For the time it will take, this depends on the server specs/load/db size, etc.

You can do it using a script to automate the process. Using a command show tables, you can get the list of tables. Then, using a simple script you can read this list and execute alter table commands.

Here is a simple perl script that will read the tables list from a file and write the SQL queries in another file. After invoking this script, you can feed the output file to mysql command to do the required changes.

open(INFILE, "< tables_list.txt");
open(OUTFILE, "> alter_tables.sql");
foreach my $name (<INFILE>) {
   chomp($name);
   print OUTFILE "ALTER TABLE $name engine=InnoDB;\n";
}
close(INFILE);
close(OUTFILE);