Logical Backups Using 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.

To dump a specific schema:

mysqldump --host=192.168.1.50 --user=admin --password --single-transaction ecommerce_db > ecommerce_full.sql

To backup specific tables within a schema:

mysqldump --host=192.168.1.50 --user=admin --password ecommerce_db orders customers > subset_backup.sql

For multiple schemas:

mysqldump --host=192.168.1.50 --user=admin --password --databases db_alpha db_beta > multi_db_backup.sql

To capture every data base on the server:

mysqldump --host=192.168.1.50 --user=admin --password --all-databases > global_backup.sql

Physical File Copying

Directly copying the data directory is viable for MyISAM tables but requires caution with InnoDB. The service should ideally be stopped, or tables locked to ensure consistency.

Typical data directories:

• Linux: /var/lib/mysql
• Windows: C:\Program Files\MySQL\MySQL Server X.X\data

Before copying, secure the data state:

LOCK TABLES;
FLUSH TABLES;

Rapid Copying with mysqlhotcopy

This Perl script copies database files directly. It works only on the local machine and supports only MyISAM and ARCHIVE tables.

mysqlhotcopy --user=root --password=secret --addlock log_archive /mnt/backup/mysql_hot

Restoring Data

SQL Dump Restoration If using a logical dump, feed the file into the client. Ensure the target database exists unless the dump includes creation statements.

mysql --user=admin --password --database=ecommerce_db < ecommerce_full.sql

Alternatively, within the MySQL shell:

SOURCE /path/to/ecommerce_full.sql;

Physical File Restoration

1. Halt the MySQL service.
2. Replace the data directory contents with the backup.
3. Adjust file ownership (e.g., chown -R mysql:mysql /var/lib/mysql).
4. Restart the service. Note: Ensure server versions match to avoid compatibility issues.

Database Migration

Same Version Transfer Pipe the dump directly from the source to the destination server.

mysqldump --host=src.example.com --user=root --password=pass source_db | mysql --host=dst.example.com --user=root --password=pass target_db

Version Upgrades

1. Perform a logical backup on the old version.
2. Uninstall the old server and install the new version.
3. Restore the logical backup.
4. Review character set settings (e.g., latin1 vs utf8) and user privileges.

Cross-Platform Migration Tools like MySQL Migration Toolkit or ODBC drivers facilitate moving data between MySQL and other RDBMS like SQL Server.

Table Data Export and Import

Exporting to Text Use SELECT ... INTO OUTFILE to write data directly to the server's filesystem. The file must not exist beforehand.

SELECT * FROM users.contacts INTO OUTFILE '/tmp/contacts_dump.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

mysqldump Tabular Output The --tab option generates both a .sql create file and a .txt data file.

mysqldump --tab=/var/backups --user=root --password=secret ecommerce_db users

Command Line Export Execute a query and redirect output.

mysql --user=root --password --execute="SELECT * FROM users.contacts;" ecommerce_db > /tmp/contacts_raw.txt

Importing Text Data Use LOAD DATA INFILE for high-speed ingestion.

LOAD DATA INFILE '/tmp/contacts_dump.csv'
INTO TABLE users.contacts
FIELDS TERMINATED BY ',' IGNORE 1 LINES;

Or use mysqlimport, which wraps LOAD DATA INFILE.

mysqlimport --user=root --password --local ecommerce_db /tmp/contacts_dump.csv

Binary Log Management and Point-in-Time Recovery

Configuration Enable binary logging in the configuration file to record all changes.

[mysqld]
log-bin=/var/log/mysql/mysql-bin
expire_logs_days=7
max_binlog_size=500M

Recovery Replay logs up to a specific timestamp to recover from accidental chenges.

mysqlbinlog --stop-datetime="2023-10-25 14:30:00" /var/log/mysql/mysql-bin.000015 | mysql --user=root --password

Log Inspection View active logs:

SHOW BINARY LOGS;
SHOW VARIABLES LIKE 'log_bin%';

Decode a log file:

mysqlbinlog /var/log/mysql/mysql-bin.000015

Log Purging Remove old logs to save space.

PURGE BINARY LOGS BEFORE '2023-10-01';
RESET MASTER; -- Clears all logs

Temporary Disabling Disable logging for the current session without restarting.

SET sql_log_bin = 0;