How to Online Backup Innodb Tables

Backup the database inevitable cause MySQL server unavailable to applications because when exporting, all tables acquired a global read lock using FLUSH TABLES WITH READ LOCK at the beginning of the dump until finish. So although READ statements can proceed, all INSERT, UPDATE and DELETE statements will have to queue due to locked tables, as if MySQL is down or stalled. If you’re using InnoDB, –single-transaction is the way to minimize this locking time duration to almost non-existent as if performing an online backup. It works by reading the binary log coordinates as soon as the lock has been acquired, and lock is then immediately released.

mysqldump -u username –ppassword –all-databases –single-transaction > dump.sql

[Related posts]

How to Export, Backup Or Dump A MySQL Database
To export a MySQL database into a dump file, simply type the following command syntax in the shell. mysqldump -u username –ppassword database_name > dump.sql...
How to Backup Only Data of a MySQL Database
For exporting MySQL database’s only the data, use –no-create-info option. The dump will not re-create the database, tables, fields, and other structures when importing. mysqldump...
How to Backup MySQL Databases
There are two methods to backup MySQL, first method is by copying all files such have extentions *.frm, *.MYD, and *.MYI, and second is by...
Transferring a FreeBSD on new hard drive [Manual]
Follow this steps to transfer FreeBSD on new hard drive of any size. Connect the new hard disk in the system with a FreeBSD (/dev/ad1s1)...
How to Export A MySQL Database Structure
If You need to export only the MySQL database’s tables’ structures use –no-data switch. mysqldump -u username -ppassword –no-data database_name > dump.sql...