Mysql – Physical or logical backup takes more space when backing up same MySQL database

MySQL

Physical backup is copying the data files that belongs to database. Logical backup is using SQL statements.
So physical or logical backup takes more space when backing up same MySQL database?

Best Answer

If you backup the files by copying the directory tree, the backup is the same size as the original.

If you use mysqldump (or similar tool) to backup, that will contain CREATE and INSERT statements to recreate the database from scratch. This can be larger or smaller, depending on the data involved.

An example of "smaller": a BIGINT takes 8 bytes in a table, but if the value is 1, it will take about 2 bytes in a logical dump (1 and a comma).

Related Solutions

Mysql – For a MySQL query, how do you determine physical and logical I/O

I recommend using Percona Server, for the slow query log enhancements. I answered a similar thread to this on Stack Overflow:

https://stackoverflow.com/questions/3393206/what-is-the-equivalent-query-in-mysql/3443944#3443944

Take a look at this in particular (from http://www.percona.com/docs/wiki/percona-server:features:slow_extended?redirect=1#changes_to_the_log_format):

# User@Host: mailboxer[mailboxer] @  [192.168.10.165]
# Thread_id: 11167745  Schema: board
# QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: Yes  Disk_tmp_table: No
# Filesort: Yes  Disk_filesort: No  Merge_passes: 0
# Query_time: 0.000659  Lock_time: 0.000070  Rows_sent: 0  Rows_examined: 30  Rows_affected: 0  Rows_read: 30
#   InnoDB_IO_r_ops: 1  InnoDB_IO_r_bytes: 16384  InnoDB_IO_r_wait: 0.028487
#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000
#   InnoDB_pages_distinct: 5
select count(distinct author_id) from art87.article87 force index (forum_id) where forum_id = 240215 and thread_id = '710575'

Mysql – BACKUP and RESTORE MySQL Database

As from your question if the scenario is like that.

You are on your local machine and accessing the remote servers A and B.

Process is simple

Step1 : Connect to server A where A is username of server

ssh A@192.168.9.214

Step2 : Generate the database dump of the server A as

mysqldump -uroot -proot  db1  > database_db1.sql

Step3 : Copy the dump on the server B using SCP as

scp database_db1.sql B@192.168.9.215:/home/user/Desktop

Step4 : Connect to the Server B as

ssh B@192.168.9.215

Step5 : Restore the copied dump here using mysql client utility

mysql -uroot -proot db < /home/user/Desktop/database_db1.sql