-2

In a quite old-versioned CentOS-Bash "shared server" environment, I ran the following commands:

current_date="$(date +%F-%T)"
db_user_name_and_db_name="db-username_db-name"
war="$HOME/public_html" # Web Application Root

mkdir -p "${war}/mediawiki_general_backups"

My problem is that If I make a quick export (all tables) of the relevant database from PHPMyAdmin GUI, I get a .sql file of 43MBs.
But, if I export the same database by a CLUI command such as the following, I get a .sql file of 23MBs:

mysqldump \
-u "${db_user_name_and_db_name}" \
-p "${db_user_name_and_db_name}" \
> "${war}/mediawiki_general_backups/${db_user_name_and_db_name}-${current_date}.sql"

I don't know how to explain this difference; do you?

|improve this question
  • 1
    Did you actually look at the dumps to try to figure out why they are of different size? Is one compressed? Is one using inserts for each value while the other does bulk inserts? – Kusalananda♦ Oct 10 at 9:13
  • I didn't open the files with a text editor fearing of very slow loading and seeing gibberish-like content I wouldn't understand but they were opened very fast and right on start I can see many (well presented) differences in documentation. In the smaller one I can indeed see the term compessed appearing lots of times near the starting of the file (in the larger file it is absent); because both files end with .sql I didn't imagine one would be compressed. The smaller file has tens of INSERT queries while the larger one has hundreds of it which might indicate bulk inserting. – JohnDoea Oct 10 at 9:31
  • I assume mysqldump as installed by the hosting provider (SiteGround) compresses by default, unlike PHPMyAdmin SQL Dump 4.7.3 that they use as well. – JohnDoea Oct 10 at 9:37
2

Do quick testing; export a table in PhpMyadmin and with the following command in terminal.

mysqldump -u $UserName -p database table > /tmp/table.sql

Then, open both SQL files with a text-editor; difference should be recognized immediately:
I recognized lots of comments which is exported from PHPMyAdmin and the way of INSERTing data. For example:

Exported SQL has the following header:

-- phpMyAdmin SQL Dump
-- version 4.0.10.20
-- https://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 10, 2019 at 03:48 AM
-- Server version: 5.1.73-log
-- PHP Version: 5.3.3

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

Dumped SQL has the following header:

-- MySQL dump 10.13  Distrib 5.1.73, for redhat-linux-gnu (x86_64)
--
-- Host: localhost    Database: testing
-- ------------------------------------------------------
-- Server version   5.1.73-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
|improve this answer
  • Indeed, by Kusalananda's comment I did that and noted the difference; I guess I should just relay on mysqldump as is; now I now that internal-compressing might be utilized without me knowing it (for about 23 years I knew archives should have external indication such as a tar or zip or rar file extension but today I learned this is not necessary... – JohnDoea Oct 10 at 11:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.