difference between 2 mysql backups

[pwired]

Hi

I have found a php script that makes a backup of a mysql database.

Simply upload or save the php script on the server, open an url to

the php file and download the backup.sql.

Gives me my own control to make database backups. So far so good.

I made a second sql backup of the same database simply through

the CPanel of the Hoster using phpmyadmin.

Thing is when I open this second backup.sql file in wordpad and compare it

with the first backup sql file made with the php script, it looks very different.

Also both of the backups differ in size.

Of course I could setup a test server and try both sql backups

and see if the website works the same.

My question is, is there another way to compare two sql backups

to check if they have the same number of tables, create table, insert into, etc. ?

[adrian]

There are a few reasons why the sql files might be different, for example some can be very verbose with comments etc.

Your simplest option might be to open them in a text editor and do a CTRL+F for CREATE TABLE and see how many matches there are. You could try again for INSERT INTO etc. I think in the end testing them might be the only way to be sure if they both work the same.

If you are just looking to backup the database from a PW site, Ryan has a new DB backup feature in PW 2.5, so you could just use that

[diogo]

import the two backups to two different tables on your own computer, export them with the same program and check if the results are the same.

[pwired]

Thanks adrian - yes that will be a good idea to count and compare create table, insert into, etc. etc.

I can make, lets say 10 different backups, see if everything is ok and then safely use the php backup script.

It is part of learning my self php and part of using autoit to make my own automated scheduled backups.

[pwired]

import the two backups to two different tables on your own computer, export them with the same program and check if the results are the same.

Thanks diogo but not fully follow, sorry. Do you mean import the two backups in the same database

but with different table prefix ?

Edit: I think I got it. Import and then export with the same used way and then compare.

[LostKobrakai]

I think he meant importing both files in two different databases and export them with the same settings, then they should be compareable.

[pwired]

Yes - thanks lostkobrakai for helping me out here.

I attached the php script, maybe it will be usefull for somebody.

It works fast and error free, even on restricted shared hosting.

backup_mysql.php

[diogo]

Sorry, I was on mobile and it came out a bit cryptic

I meant two databases of course.

[OrganizedFellow]

Yup. I agree with Diogo.

It's the best way to find the differences.

[Linda789]

Moderator note:

@Linda789

I am treating your post as spam unless you can prove otherwise.

You joined the forums today, and submitted 2 posts with a link to an outside resource which suspiciously looks like unauthorised advertising on your part.

Edited January 30, 2015 by kongondo
Possible spam

[szabesz]

Hi @pwired

Are you still using the technique of comparing two 2 (my)sql files? I have just turned my attention to it and spent half an hour googlin around on the topic. I found a few commercial GUI Windows applications that advertise themselves of being able to compare both schema and data, but I'm on a Mac and do not necessarily want to spend money on it. So this is the most promising solution I could find so far:

https://github.com/camcima/php-mysql-diff

I have not yet tested it, 'cos I wanted to save some time by asking first if there is anyone on the Forum who can recommend something else. I will surely try this one out too, but any alternatives are welcome.

Note, that I'm not looking for a tool to actually migrate anything. What I need is a quick way to find differences if any and preferably between 2 sql files, and not by connecting to two databases.

So, anyone?  Thanks in advance...

[pwired]

Hi Szabez,

Yeahh at times you are in a webjungle, same database - different backups. A CPanel mysql backup turned out to be different from my local mysql backup and different from a php script backup. Since I used Diogo,s way to compare them I learned that most of the times only the "header" in the sql file is different. Some CPanels, when exporting a database, put extra data in the sql "header" file to mark it where and how the sql file was generated. In my case I could not import a sql file on another hoster because of this extra marked data done by the cpanel of the previous hoster. Simply stripping out this marked data was the solution and I could import the sql file on the other hoster. Since then I learned where to look in the top of a sql file to check if there is any added hoster or cpanel data and strip it out.

Further an online database will always be different from your local dev database so I learned to keep both database backups.

Quote

recommend something else . . .

When needed I use toad or sqlyog to compare databases:

https://software.dell.com/products/toad-for-mysql/

http://blog.webyog.com/all-you-wanted-to-know-about-sqlyogs-schema-comparison-tool/

That camcima thing seems to need composer and unfortunately I dont know sh*t about composer so I put that on my to do list

 

[szabesz]

Thanks for the quick reply! I also noticed that sql dumps are not born to be equal, but I have not yet dived into the subject matter other than realizing that a simple text diff tool in just no enough

I'm not planning to compare online to local, but local to local and/or online to online, and only before and after installing modules, or upgrading modules/system. The idea is that one can quickly spot changes without examining any source code, and if I happen to find something to pay attention to, I might want to spend the time to take a closer look at code level.

I also found Toad and sqlyog, but they are running on Windows so not the kind I'm longing for. Nevertheless, can any of them be used to quickly see both schema an data changes?

A far as camcima/php-mysql-diff is concerned, do not be scared away by Composer. As long as you do not want to create your own projects, there is not too much to learn, just "use it"

Edited September 26, 2016 by szabesz
typos

[Doug G]

If your php script uses the common mysqldump command, there are a gazillion options that can affect the size of the resulting sql file.  For example, you can decide if you want to expand names to include full tablename.columnname or not,  Or you can simply backup the database structure without data, you can decide if you want to include CREATE_TABLE in the backup or not, etc.

For your reading enjoyment, you can look over the options available from this linux man page:  http://linuxcommand.org/man_pages/mysqldump1.html

I've never used phpmyadmin to create backups, but I'd imagine there are various options you can include/not include with pma too.

 

[szabesz]

6 hours ago, Doug G said:

you can look over the options available from this linux man page

Oh, I "love" man pages

[szabesz]

@pwired and all forum members  

I decided to put together my own bash script because I did not find what I was after. I am not a bash pro nor a linux/unix guru, so there might be better solutions using standard system tools, but I like the result so I thought I share it with you. You might also find it useful.

Here is a short demo:
https://youtu.be/V6eF4NBNDbc

Please read the instructions (located in the script itself) if you happen try it out!

Any constructive criticism is welcome.

pwdiff.sh 0.0.8, (2016-10-25), initial public release:

Spoiler

#!/bin/bash
set -e;
date

# The main feature of this scrip is to compare two MySQL databases.
#
# DISCLAIMER: Use at your own risk! Although the script has been tested and works as intended, 
# if not used with care one might easily erase/overwrite the wrong database resulting in data loss.
#
# HOW IT WORKS: "Comparing" actually means that tables and rows are compared by the means of iterating through
# all the tables found in BOTH databases, exporting them to temporary files which in turn
# are compared by using the standard Unix/Linux diff tool.
#
# The final result can be used as a quick overview of a BEFORE and an AFTER state of a databases.
# Typical uses cases:
# - What changes are made to the database by upgrading a given CMS?
# - What changes are made to the database by installing and activating a module/plugin of a given CMS?
# - Taking a look under the hood to examine how a given CMS/plugin interacts with the database.
#
# KNOWN ISSUES AND LIMITATIONS:
# - BASH 4.x is required. On macOS upgraded to BASH 4 you might need to edit the 1st line of this script; change it to #!/usr/local/bin/bash
# - Only supports localhost and one MySQL root user with ALL PRIVILEGES.
# - We bypass MySQL 5.6 security concerns in order to get a clean and verbose output...
# 	...the script is designed to be used in a development environment where password security is less of a concern.
# - Obviously, changes made to the database that are out of the scope of modifying tables/fields cannot be detected.
# - It is not yet possible to provide database NAMES via CLI arguments (but it is possible to provide SQL dumps)...
# 	...however, one can populate the DB_NAMES_ARRAY variable to define database NAMES for easy selection.
#
# TODO:
# - The possibility to provide database NAMES via CLI arguments.
# - The support of remote databases (coning them via SSH, so that DB_TEMP_SOURCE is used).
#
# USAGE:
# - IMPORTANT FIRST STEPS: Setup the script by adjusting the variables in the "Script configuration" section.
# - You can either:
#   - provide one or two MySQL dump files via first or second CLI arguments, or
#	- select from database names stored in DB_NAMES_ARRAY.
# - Or the script can optionally be used to clone an existing database to another existing one (overwriting the latter):
#	- provide -c as the first argument.
#
# EXAMPLES:
### To clone a database:
# pwdiff -c
#
### To compare two MySQL dumps:
# pwdiff /path/to/dump1.sql /path/to/dump2.sql
#
### To compare one MySQL dump to an existing database:
# pwdiff /path/to/dump1.sql
#
### To compare two existing databases:
# pwdiff

SCRIPT_NAME="pwdiff.sh"
SCRIPT_VERSION="v0.0.8"
SCRIPT_BUILD="b7"
CURRENT_DIR="${BASH_SOURCE%/*}";

ELED="    "
START="\e[0;95m :: \e[0m"
RLED="\e[0;41m :: \e[0m"
GLED="\e[0;47m :: \e[0m"
BLED="\e[0;36m :: \e[0m"
OKAY="\e[0;43m :: \e[0m"
DONE="\e[0;42m :: \e[0m"

printf "$START \e[1m"$SCRIPT_NAME"\e[0m $SCRIPT_VERSION launched from \e[1m"$CURRENT_DIR"\e[0m\n"
printf "$BLED BASH version: $BASH_VERSION\n"

ARGS_ARRAY=("$@")

## -------------------------------------------   Script configuration   ------------------------------------------- ##

# Point to a directory to store temporary files:
TEMP_DIR="/path/to/tmp";
# Your MySQL root user with ALL PRIVILEGES
DB_USER="root";
# Your MySQL root user's password to bypass MySQL 5.6 security concerns:
export MYSQL_PWD="password";
# Create two databases beforehand with settings similar to the databases you will work with. Provide their names here.
# These two databases will be overwritten by the script and used as temporary storage:
DB_TEMP_SOURCE="_temp_db1"
DB_TEMP_TARGET="_temp_db2"
# Names of database to choose from. These are existing databases you want to work with.
# Keep :$DB_TEMP_SOURCE $DB_TEMP_TARGET" intact so that you can choose the temporary databases when needed:
DB_NAMES_ARRAY=(\
	$DB_TEMP_SOURCE $DB_TEMP_TARGET \
	database_name_1 \
	database_name_2 \
	database_name_n \
)
# Names of tables to exclude from compare (space separated table names):
SKIP_TABLES_LIST="caches"

## -------------------------------------------    Utility Functions    ------------------------------------------- ##

function _script_end {
	printf "$DONE We have reached the end of the script, all operations are finished!\n"; date; exit 0
}

function _b { # To output text in Bold character style.
	printf "\e[1m"$1"\e[0m"
}
function _w { # To output text in a red background.
	printf "\e[0;41m"$1"\e[0m"
}

function _create_menu () {
  select OPTION; do # in "$@" is the default
    if [ "$REPLY" -lt $(($#+1)) ]; then
      	break;
    else
     	echo "Incorrect Input: Select a number 1-$#"
    fi
  done
}

function _choose_source {
	printf "$BLED Choose the $(_b Source) database: \n"
	_create_menu "${DB_NAMES_ARRAY[@]}"
	DB_SOURCE=$OPTION
}
function _choose_target {
	printf "$BLED Choose the $(_b Target) database: \n"
	_create_menu "${DB_NAMES_ARRAY[@]}"
	DB_TARGET=$OPTION
}

## ---------------------------------------------- Asking the user  ----------------------------------------------- ##

ARG_1=${ARGS_ARRAY[0]}
ARG_2=${ARGS_ARRAY[1]}

if [ "$ARG_1" == "-c" ]; then
	printf "$GLED ::: $(_b CLONING-A-DATABASE) :::\n"
	printf "$BLED First argument is $(_b $ARG_1). Source database will overwrite the Target database.\n"
	_choose_source
	_choose_target
else
	printf "$GLED ::: $(_b COMPARING-DATABASES) :::\n"
	SOURCE_DUMP=$ARG_1
	TARGET_DUMP=$ARG_2

	if [ "$SOURCE_DUMP" == "" ]; then	
		printf "$BLED No previous dump file was provided in the 1st argument.\n"
		_choose_source
	else
		if [[ ! -f "$SOURCE_DUMP" ]]; then
			printf "$RLED Source dump was not found. Script terminated and no operation was carried out!\n"; exit 1
		fi
		printf "$BLED The Source database called $(_b $DB_TEMP_SOURCE) will be overwritten with:\n"
		printf "$ELED $(_b $SOURCE_DUMP)\n"
		DB_SOURCE=$DB_TEMP_SOURCE
	fi

	if [ "$TARGET_DUMP" == "" ]; then	
		printf "$BLED No previous dump file was provided in the 2nd argument.\n"
		_choose_target
	else
		if [[ ! -f "$TARGET_DUMP" ]]; then
			printf "$RLED Target dump was not found. Script terminated and no operation was carried out!\n"; exit 1
		fi
		printf "$BLED The Target database called $(_b $DB_TEMP_TARGET) will be overwritten with:\n"
		printf "$ELED $(_b $TARGET_DUMP)\n"
		DB_TARGET=$DB_TEMP_TARGET
	fi
fi

## ------------------------------------- Validation and Confirmation dialogs ------------------------------------- ##

if [ "$DB_SOURCE" == "$DB_TARGET" ]; then
	printf "$RLED The $(_b Source) and the $(_b Target) cannot be the same. Script terminated and no operation was carried out!\n"; exit 1; 
fi
printf "$GLED DB_SOURCE is: $(_b $DB_SOURCE) - DB_TARGET is: $(_b $DB_TARGET)\n"

printf "$BLED Proceed (y/n)? "
read PROCEED_YN
if [ "$PROCEED_YN" != "y" ]; then
	printf "$RLED You did not choose yes. Script terminated and no operation was carried out!\n"; exit 1 ;
fi

## ----------------------------------------- Working out the differences ----------------------------------------- ##

if [ "$ARG_1" == "-c" ]; then
	printf "$BLED Trying to drop all the tables of the $(_b Target) database called $(_b $DB_TARGET) \n"	
	MYSQL="mysql -u $DB_USER -D $DB_TARGET"
	$MYSQL -BNe "show tables" | awk '{print "set foreign_key_checks=0; drop table `" $1 "`;"}' | $MYSQL
	unset MYSQL
	
	printf "$BLED Trying to clone $(_b $DB_SOURCE) to $(_b $DB_TARGET)... \n"
	mysqldump -u $DB_USER $DB_SOURCE | mysql -u $DB_USER $DB_TARGET
	_script_end
else
	if [[ ! -d "$TEMP_DIR" ]]; then
		printf "$GLED Temporary directory defined in the script was not found. Trying to create it... \n"
		mkdir $TEMP_DIR
	fi

	if [ "$SOURCE_DUMP" != "" ]; then
		printf "$BLED Trying to drop all the tables of the $(_b Source) database called $(_b $DB_SOURCE) \n"
		mysqldump -u $DB_USER --add-drop-table --no-data $DB_SOURCE | grep ^DROP | mysql -u $DB_USER $DB_SOURCE
		printf "$BLED Trying to import the database...\n"
		mysql -u $DB_USER $DB_SOURCE < $SOURCE_DUMP
	fi

	if [ "$TARGET_DUMP" != "" ]; then
		printf "$BLED Trying to drop all the tables of the $(_b Target) database called $(_b $DB_TARGET) \n"
		mysqldump -u $DB_USER --add-drop-table --no-data $DB_TARGET | grep ^DROP | mysql -u $DB_USER $DB_TARGET
		printf "$BLED Trying to import the database...\n"
		mysql -u $DB_USER $DB_TARGET < $TARGET_DUMP
	fi
fi

TEMP_DUMP_SOURCE="$DB_SOURCE"--table".sql"
TEMP_DUMP_TARGET="$DB_TARGET"--table".sql"
DIFF_DUMP=$DB_SOURCE"--"$DB_TARGET".diff"
rm -f $TEMP_DIR"/"$DIFF_DUMP

dump () {
 	mysqldump --opt --compact --skip-extended-insert --skip-comments -u $DB_USER $1 $DB_TABLE -r$2
}

function _build_array_of_tables {
	DB_NAME=$1; TABLES_ARRAY=$2
	for DB_TABLE in `mysql -u $DB_USER $DB_NAME -N -e "show tables" --batch`; do
		eval $TABLES_ARRAY+="($DB_TABLE)"
	done
}

_build_array_of_tables $DB_SOURCE "SOURCE_TABLES_ARRAY"
_build_array_of_tables $DB_TARGET "TARGET_TABLES_ARRAY"

while read -r; do # merge the arrays
	ALL_TABLES_ARRAY+=("$REPLY")
done < <( printf '%s\n' "${SOURCE_TABLES_ARRAY[@]}" "${TARGET_TABLES_ARRAY[@]}" | sort -u )

# Compare two databases
for DB_TABLE in "${ALL_TABLES_ARRAY[@]}"; do
	if [ "`echo $SKIP_TABLES_LIST | grep $DB_TABLE`" = "" ]; then
 		printf "$BLED Comparing '$DB_TABLE'... \n"
 		set +e;
 		dump $DB_SOURCE $TEMP_DIR"/"$TEMP_DUMP_SOURCE
 		dump $DB_TARGET $TEMP_DIR"/"$TEMP_DUMP_TARGET
		TABLE_INFO="-- table: $DB_TABLE"
 		diff --unified=0 -b --label="SOURCE $DB_SOURCE $TABLE_INFO" --label="TARGET $DB_TARGET $TABLE_INFO" $TEMP_DIR"/"$TEMP_DUMP_SOURCE $TEMP_DIR"/"$TEMP_DUMP_TARGET >> $TEMP_DIR"/"$DIFF_DUMP
 		set -e;
 	else
 		printf "$BLED $(_b Ignoring): '$DB_TABLE'... \n"
	fi
done
rm -f $TEMP_DIR"/"$TEMP_DUMP_SOURCE $TEMP_DIR"/"$TEMP_DUMP_TARGET

less $TEMP_DIR/$DIFF_DUMP;
#open $TEMP_DIR/$DIFF_DUMP # works on macOS, opens it in the default editor (Brackets is a good choice);

## ---------------------------------------------- Closing the script --------------------------------------------- ##

_script_end

# CHANGE-LOG

### 0.0.8 (2016-10-25)
# initial public release

#doc end

 

 

[pwired]

Hi. Szabez, thanks for putting this script together and the youtube. Lets see how to get this to work on windows but the code already is great for me to learn from.

[szabesz]

8 minutes ago, pwired said:

but the code already is great for me to learn from.

Should you have any questions, do not hesitate to ask  

[FrancisChung]

I highly recommend MySQL Compare for this exact purpose.  And it's free to for Non Enterprise use according to their new licensing agreement.

http://www.red-gate.com/products/mysql/mysql-compare/

Unfortunately only Windows at the moment, but for a one off task like this I would strongly consider getting a windows environment running and run this tool.

I've used their SQL Server version extensively and it saved the day many a times.

P/S For Data differences, you have to use this instead. Above is for Schema differences.
http://www.red-gate.com/products/mysql/mysql-data-compare/

[szabesz]

@FrancisChung 

Thanks for the info! We've been already discussing it in the forum, and I will find the time to take a look at it on my virtual machine windows setup, but I wanted a tool to quickly spot any changes that happen to a database in a "short period of time" (e.g. module install / system upgrade like changes, tracking other similar changes "manually", etc...). On a Mac doing such tusks with windows GUI applications is a bit tedious, even if it is a decent tool to perform such a tusk. Two separate GUI apps for looking for both schema and data changes probably "slows things down" a bit too.

I forgot to mention that my bash script "detects" added/missing tables too by also appending the added/missing tables in question to the final text output, so even this sort of change can be spotted. Also, when the names of the databases are already in the script, it takes only about 10-15 seconds to perform the comparing operation, so I am less likely to skip it due to laziness

As I pointed out, my script is not meant to be any sort of migrating tool, whereas the RedGate apps are different beasts for sure.

Edited October 28, 2016 by szabesz
typo

[Jeroen]

Comparing two files can be done by the opensource program WinMerge as well, if you're running a Windows machine