MySQL: mysqldump

The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.

Remote MyIsam backup

mysqldump -P3306 -q -C -F -h{hostname} -u{username} -p'{password}’ {database} | gzip > /tmp/{name}.sql.gz

Remote InnoDB backup

mysqldump –single-transaction -P3306 -q -C -F -h{hostname} -u{username} -p'{password}’ {database} | gzip > /tmp/{name}.sql.gz

Local MyIsam backup

mysqldump -q -F -u{username} -p'{password}’ {database} | gzip > /tmp/{name}.sql.gz

Local InnoDB backup

mysqldump –single-transaction -q -F -u{username} -p'{password}’ {database} | gzip > /tmp/{name}.sql.gz

Database restore

gunzip < /tmp/{name}.sql.gz | mysql -q -C -u{username} -p'{password}’ {database}

MySQL: /usr/bin/mysql
Hostname: the default host is localhost

Parameters

-q (quick): This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.

-C (compress): Compress all information sent between the client and the server if both support compression.

-F (flush-logs): Flush the MySQL server log files before starting the dump. This option requires the RELOAD privilege.

-P port_num: The TCP/IP port number to use for the connection.

–single-transaction: This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications. To dump large tables, combine the –single-transaction option with the –quick option.