Multi MySQLDump

PACKAGE DUMPING ONE OR MORE MySQL DATABASES THROUGH ASYNCHRONOUS AJAX REQUESTS, EACH FOR A SINGLE DATABASE TABLE TO DUMP; ON THE FIRST DUMP, EACH AJAX REQUEST CALLS A PHP SCRIPT WHICH DUMPS A DATABASE TABLE TO A SQL FILE; ON FURTHER REQUESTS (CALLED ON DEMAND OR SCHEDULED PERIODICALLY), A TABLE IS RE-DUMPED ONLY IF THEIR DATA/STRUCTURE HAVE CHANGED SINCE THE LAST DUMP; YOU CAN ALSO EXCLUDE ONE OR MORE TABLES OR FORCE THE DUMP AT EACH RUN. WHEN THE DUMP IS IN PROGRESS, THE HOME PAGE DISPLAYS:

• THE NAME OF ALL THE DATABASES SET TO DUMP
• OPTIONALLY, THE LIST OF THE TABLES EXCLUDED OR FORCED TO DUMP FOR EACH DATABASE
• THE NUMBER OF AJAX REQUESTS STILL ACTIVE AND THE TOTAL NUMBER

WHEN ALL THE DUMP ARE SUCCESSFULLY COMPLETED, THE HOME PAGE ALSO DISPLAYS:

• HYPERLINKS TO DOWNLOAD EACH DATABASE COMPRESSED IN BOTH ZIP AND TGZ FORMAT
• FOR EACH TABLE DUMPED, HYPERLINKS TO DOWNLOAD THE SQL FILE UNCOMPRESSED OR COMPRESSED IN BOTH ZIP AND GZIP FORMAT PLUS THE INFORMATION ON WHETHER THE TABLE IS AT ITS FIRST DUMP OR NOT, ALONG WITH IF THE TABLE IS DUMPED AT EACH RUN OR ONLY ON CHANGING THEIR STRUCTURE/DATA.

*

AUTHOR

Alessandro Quintiliani <alex23rps at gmail dot com>

* LICENSE

GNU GPL (see file COPYING.txt)

*

PREREQUISITES

PHP >= 5.1

* DESCRIPTION

This package allows you to dump one or more MySQL databases, splitting the dump into a number of sql files, each for a table to dump.

The script *dbConnSettings.php* is the file where first you must define, for each database you want to dump:

• the type of the database (i.e. mysql). This parameter must always be the first to set for each database
• the hostname or IP address of the database server
• the port number. This parameter can also be omitted if the database port is the default port (3306 for mysql)
• the username to the database
• the password to the database

the order you define the hostname, port, login, password is not relevant, but they must always be defined after the type of the database.

If you want to exclude one or more tables from dump, you can add this optional information always in dbConnSettings.php, as well as if the whole database or only one or more tables must be dumped at each run (see example on USAGE section).

NOTICE: excluded tables from dump always have priority on the ones set to dump at each run. This means that does not matter the order which you define the list of the excluded tables and the list of the forced dump tables: if you accidentally put a table in both the lists, the table will be excluded from dump.

There is an extra file, *parameters.txt*, called by the procedure, that is still properly configured to dump MySQL databases. This file contains a list of pairs

parameter=value

where parameter and value MUST NOT BE MODIFIED. All the pairs are grouped by:

• GROUP OF FOLDERS<br>having the following pair

  folder_dump_files=dumpsql which defines the directory name (dumpsql) containing all the sql files created when dump databases are completed. THIS IS THE ONLY VALUE THAT CAN BE MODIFIED INSIDE parameters.txt. This parameter value can be a relative or absolute path. If it is a relative path (default), it is created if not exists inside the main package directory multidump; if you change it to an absolute path and place outside the webroot directory, make sure it has the right permission to make the procedure to create and read the sql files inside it

• GROUP OF DB TYPE QUERIES group of 4 parametric queries, executed by the procedure when the php page dumptable.php is requested via AJAX. The procedure executes 4 different queries, which are:

  dump_mysql_query_show_tables=SHOW FULL TABLES FROM @@@database.name@@@ WHERE Table_Type = 'BASE TABLE' AND tables_in_@@@database.name@@@ NOT IN (@@@excluded.tables.name@@@)

  dump_mysql_query_checksum=CHECKSUM TABLE @@@database.name@@@.@@@table.name@@@

  dump_mysql_query_engine_db=SELECT DISTINCT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA='@@@database.name@@@'

  dump_mysql_query_engine_table=SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = '@@@database.name@@@' AND TABLE_NAME = '@@@table.name@@@' the parameter names always must start with dump\_&lt;database type&gt; (for MySQL, they start with dump\_mysql). The words enclosed between @@@ in the queries are placeholders, replaced by the procedure with their current values; respectfully

  - @@@database.name@@@ is replaced by the name of the database that is being dumped - @@@excluded.tables.name@@@ is replaced by the list of tables excluded from dump and defined in the method setListIgnoredTables() called in the file dbConnSettings.php (see example on USAGE section) - @@@table.name@@@ is replaced by the name of the table that is being dumped

NOTICE: the third and the fourth query respectfully determine the engine type database and the engine type table to MySQL platform, which both result in MyISAM or InnoDB; on other database platforms, these two pairs might not defined

• GROUP OF DUMP COMMAND LINE OPTIONS<br>this is the group af all the options required to dump a table from the command line, called in the file dumptable.php. The pairs with the options are:

  dump_type=mysql

  dump_mysql_option_command_storage_engine_myisam=--lock-tables=TRUE

  dump_mysql_option_command_storage_engine_innodb=--single-transaction

  dump_mysql_option_command_host=-h

  dump_mysql_option_command_port=-P

  dump_mysql_option_command_user=-u

  dump_mysql_option_command_password=-p the first parameter dump_type defines the type of database to dump (MySQL). The value must always be written in lowercase. The second and third parameters are specifically defined to mysql database (on other types of database these parameters might not be defined).<br>The remaining four pairs are the options required to set a database connection in the dump command line (host, port, username, password)

When the main page index.php runs, accordingly to the database connections set in the script dbConnSettings.php, a first PDO connection is set to obtain the list of the database tables to dump by executing the first query from the group GROUP OF MySQL QUERIES (with the placeholders properly replaced by the procedure); for each table, an AJAX request to dumptable.php along with some POST data is called asynchronously.

When dumptable.php page is executed by an AJAX request, a PDO connection to a database is set; the data sent via the POST with the AJAX request are:

• the database name
• the table name
• the serialized database connection string
• a numeric flag which determines if the table must be dumped at each run or only if its content/structure have changed since the last dump

Whether the dump of the table is done at each run or not, the second query from the group GROUP OF MySQL QUERIES (with the placeholders properly replaced) is executed and the CHECKSUM value from the database table referred to an AJAX request is calculated; the checksum value, along with the database name and the table name are set together to see if the file

>&lt;database name&gt;\_&lt;table name&gt;\_&lt;checksum value&gt;.sql

which is the file format where each table is dumped, exists inside the directory containing all the dumped tables (defined in the GROUP OF FOLDERS). All possible cases are:

1. There is no file *&lt;database name&gt;\_&lt;table name&gt;\_&#42;.sql* This means the dump which is going to be executed is the first absolute dump of the table --> The dump command line of the table is promptly executed and the output redirected to &lt;database name&gt;\_&lt;table name&gt;\_&lt;checksum value&gt;.sql
2. A file in the format *&lt;database name&gt;\_&lt;table name&gt;\_&#42;.sql has been found* This means that the table has been previously dumped and there are two possible options:

2.1 - the table must be dumped at each run --> the dump command line of the table is executed and the output redirected to &lt;database name&gt;\_&lt;table name&gt;\_&lt;checksum value&gt;.sql

2.2 - the table must be dumped only on change of their content/structure --> Here comes up the CHECKSUM function!!! CHECKSUM is a MySQL built-in hashing function which applicates to a table and results in an integer; if something in a table changes (data/structure), consequently their CHECKSUM value changes. The checksum value calculated by the the second query from the group GROUP OF MySQL QUERIES is compared with the checksum value contained in the dump filename and:

- 2.2.1 - if the two checksum values are identical this means that the table structure/data have not changed since the last dump and no new dump of the table is required

- 2.2.2 - if the two checksum values are different this means that the table structure/data have changed since the last dump and a new dump of the table is required and hence executed

In any case, the dump is made by using a MySQL native command *mysqldumpwhich is composed using the database connection parameters set in thedbConnSettings.phpand the option set taken from the group GROUP OF DUMP COMMAND LINE OPTIONS in the fileparameters.txt* according to the syntax

mysqldump <dump_mysql_option_command_storage_engine_myisam|dump_mysql_option_command_storage_engine_innodb> <dump_mysql_option_command_host> <host name or IP>   <dump_mysql_option_command_port> <port number>  <dump_mysql_option_command_user> <login>  <dump_mysql_option_command_password><password>  <database name> <table name>   >  <path to sql dump file>

IMPORTANT NOTICE

• As pointed on the step 2.2, the checksum is the core function of the whole package which helps to determine if a table has changed or not between two consecutive runs of the main php script and hence if a new dump of a table is required. TAKE INTO ACCOUNT THAT CHECKSUM IS NOT GUARANTEED TO BE COLLISION-FREE: this means that there is a slight chance that if the content/structure of a table changes between to consecutive runs of the script, the checksum might not change, which could result in a skipped dump of the table the second time. But, as mentioned in the official MySQL documentation, the chances of such this collision are very few. Anyway you can always set the dump of a table (or all the database tables) at each run, even though this can reduce the total speed to complete a dump
• The main command mysqldump is defined inside the class Class.DumpFileParameters.php and not in the file parameters.txt only for safety reason, to avoid setting any command, even desruptive, inside the file parameters.txt
• Just before redumping a table, a dump file is always automatically backupped and the copy is deleted only when the new dump is completed with no error. So if a new dump fails, the original sql file is automatically restored and if the automatical restore fails too, a warning message of a required manual restore is displayed * USAGE

suppose you have several mysql databases to dump, such as:

• dbone
• dbtwo
• dbthree
• dbfour

and all of these databases are installed on the same server (but this is not relevant), having the same credentials:

• hostname: my\.hostdb\.com (or, its IP address, i.e. 10.20.30.40)
• port: 3307 (this is not the mysql default port)
• login: mylogin2db
• password: mypasswd2db

1) Uncompress the package multidump.zip and place the folder multidump and all of its content in the root directory of your web application or in any folder where permissions of creating folders and files are set properly. If you want to specify your own absolute path to the directory containing all the sql files (see the section of the group GROUP FOLDERS in the description of parameters.txt file), make sure the permission are set properly to allow the main php script to write to this folder

*

2) set in the file dbConnSettings.php one set connection for each database you want to dump. According to the above example ( dbone, dbtwo, dbthree, dbfour ) the settings are as it follows (NOTICE: the method call setTypeDbToDump must always be the first called on each set, while the order you call the other methods is not relevant):

# set connection to dbone mysql database
$odmp->setTypeDbToDump('dbone','mysql'); // !!! FIRST METHOD TO CALL ON dbone CONNECTION !!!
$odmp->setHostDbToDump('dbone','my.hostdb.com');  // or $odmp->setHostDbToDump('dbone','10.20.30.40');
$odmp->setPortDbToDump('dbone', 3307);   // the call on this method is not necessary if the db connection refers to the default port
$odmp->setLoginDbToDump('dbone', 'mylogin2db'); 
$odmp->setPasswordDbToDump('dbone', 'mypasswd2db'); 
 
# set connection to dbtwo mysql database
$odmp->setTypeDbToDump('dbtwo','mysql'); // !!! FIRST METHOD TO CALL ON dbtwo CONNECTION !!!
$odmp->setHostDbToDump('dbtwo','my.hostdb.com');  // or $odmp->setHostDbToDump('dbtwo','10.20.30.40');
$odmp->setPortDbToDump('dbtwo', 3307);   // the call on this method is not necessary if the db connection refers to the default port
$odmp->setLoginDbToDump('dbtwo', 'mylogin2db'); 
$odmp->setPasswordDbToDump('dbtwo', 'mypasswd2db');
 
# set connection to dbthree mysql database
$odmp->setTypeDbToDump('dbthree','mysql'); // !!! FIRST METHOD TO CALL ON dbthree CONNECTION !!!
$odmp->setHostDbToDump('dbthree','my.hostdb.com'); // or $odmp->setHostDbToDump('dbthree','10.20.30.40');
$odmp->setPortDbToDump('dbthree', 3307);  	// the call on this method is not necessary if the db connection refers to the default port
$odmp->setLoginDbToDump('dbthree', 'mylogin2db'); 
$odmp->setPasswordDbToDump('dbthree', 'mypasswd2db');

# set connection to dbfour mysql database
$odmp->setTypeDbToDump('dbfour','mysql'); // !!! FIRST METHOD TO CALL ON dbfour CONNECTION !!!
$odmp->setHostDbToDump('dbfour','my.hostdb.com');  //or $odmp->setHostDbToDump('dbfour','10.20.30.40');
$odmp->setPortDbToDump('dbfour', 3307);  	// the call on this method is not necessary if the db connection refers to the default port 
$odmp->setLoginDbToDump('dbfour', 'mylogin2db'); 
$odmp->setPasswordDbToDump('dbfour', 'mypasswd2db');

IMPORTANT NOTICE

the object reference variable name $odmp called in dbConnSettings.php must be the same as the instance name of MultiDump() in index.php (see $odmp = new MultiDump(); defined in index.php); so if you would like to use another object reference variable name such as $myDmpObjRef, you must replace in index.php the variable $odmp with $myDmpObjRef *

3) (optional) if you want to skip some tables from dumping, after setting all the db connections, you must call the method

$odmp->setListIgnoredTables(<dbname>, <array tables of dbname to exclude from dump>);

According to the above example, if you want to skip the following tables, i.e.:

• alpha, beta from dbtwo database
• gamma, delta, epsilon from dbfour database
• lambda from dbthree database

you must insert the following method calls:

$odmp->setListIgnoredTables('dbtwo', array('alpha', 'beta')); 
$odmp->setListIgnoredTables('dbfour', array('gamma', 'delta', 'epsilon'));
$odmp->setListIgnoredTables('dbthree', array('lambda'));

the order you list the excluded tables from dump in the array is not relevant, as well as the order you call the method setListIgnoredTables to each database

*

4) (optional) if you want to dump some tables (or all the tables) at each run and not only when their data/structure have changed, you must call the method

$odmp->setListForcedDumpTables(<dbname>,<array tables to force dump>|-1);

According to the above example, if you want to dump the following tables at each run, i.e.:

• zeta, eta, theta from dbone database
• iota, kappa from dbfour database

you must insert the following method calls:

$odmp->setListForcedDumpTables('dbone', array('zeta', 'eta', 'theta'));
$odmp->setListForcedDumpTables('dbfour', array('iota', 'kappa'));

the order you list the forced-dump tables in the array is not relevant, as well as the order you call the method setListForcedDumpTables to each database; instead, if you want to dump all the tables from i.e. dbthree at each run, you must insert the following method call:

$odmp->setListForcedDumpTables('dbthree', -1);

<br>

IMPORTANT NOTICE ON THE STEPS 3) AND 4)

As mentioned in the first NOTICE of the DESCRIPTION, the tables excluded from dump always have priority on the tables dumped at each run, independently whether you first call setListIgnoredTables() or setListForcedDumpTables(). So, if you want to dump a table (i.e. my\_important\_table) at each run from mydb database, you call

$odmp->setListForcedDumpTables('mydb', array('my_important_table'));

but if you place by mistake my\_important\_table also in the array of the excluded tables and call the method setListIgnoredTables, such as

$odmp->setListIgnoredTables('mydb', array('no_important_table','my_important_table')); 

the table my\_important\_table will be excluded from dumping instead of being dumped at each run of the main script *

5) launch the main page

http://localhost/multidump

or, if you place this package inside a webroot at my.domain.com, launch the URL

http://my.domain.com/multidump

CUSTOMIZATION

Currently, this package is configured to dump only MySQL and PostGreSQL databases, but the dump can be extended to other database platforms. This package uses the following steps to dump MySQL tables, which are:

> 1. query to get the list of the tables to dump from a database filtered on the excluded tables > 2. query to calculate the checksum of a table > 3. query to get the storage engine of a table (depending on the database platform) > 4. get the host option to use in the dump command line > 5. get the port option to use in the dump command line > 6. get the user option to use in the dump command line > 7. get the password option to use in the dump command line > 8. get the storage engine option to use in the dump command line (depending on the database platform) > 9. creation and execution of the dump command line according to the checksum table comparison between two consecutive runs of the main script

these are all the steps necessary to implement the whole procedure to dump MySQL database, but some of them may not be defined to other database platform (such as 3. and 8. valid for MySQL, but not to Oracle).

If you want to dump another database platform, make sure:

1. a hashing function applied to a table (such as CHECKSUM for MySQL) is defined
2. a dump command line outputting to a SQL file is defined

if both the two points are satisfied, you can configure Multi MySQLDump to dump other database doing the following two mainsteps:

1) you must add two groups pair inside the file parameters.txt :

• GROUP OF DB TYPE QUERIES

including the following pairs:

    
	dump_<dbtype>_query_show_tables=<query getting all tables from database name, filtered on excluded tables>
    dump_<dbtype>_query_checksum=<query getting a valid hashcode function to a database table>

replace &lt;dbtype&gt; with the type of database (a list of allowable types is defined in the file parameters.txt), as well as replace &lt;query...&gt; with the required query written in the right syntax and including placeholders); the extra pairs

    dump_<dbtype>_query_engine_db=<query getting the database engine type>
    dump_<dbtype>_query_engine_table=<query getting the table engine type>

which refer to the engine database and table and defined to MySQL database (MyISAM, InnoDB) must not be included to other database platform if not defined

• GROUP OF DUMP COMMAND LINE OPTIONS

including the following pairs:

    dump_type=<db type, one-word only, written in lowercase with no hyphens, no blanks, no empty spaces between words>
    dump_<db type>_option_command_host=<host option>
    dump_oracle_option_command_port=<port option>
    dump_oracle_option_command_user=<username option>
    dump_oracle_option_command_password=<password option>

the two extra options

	
	dump_<dbtype>_option_command_storage_engine_myisam=--lock-tables=TRUE 		
	dump_<dbtype>_option_command_storage_engine_innodb=--single-transaction

which refer to the storage engines as defined to MySQL dump command line, must not be included  to other database platform if not defined

2) In the class Class.DumpFileParameters.php the following indexed array is defined

private $_db_references = array("mysql" => array("dump_command" => "mysqldump",	
	                                             "dump_redirect_output"=> ">"
												),
								"postgresql" => array("dump_command" => "pg_dump",
													  "dump_redirect_output"=> ">"
													 )
							   );

each key of $\_db\_references is the db type (value of dump_type in parameters.txt) and their value is a two-elements indexed array, whose keys must always be the strings dump\_command and dump\_redirect\_output, and their values must be respectfully the main command to dump a database (mysqldump for MySQL, pg\_dump for PostGreSQL) and the redirect operator to output sql file (">").

If you want an extra database platform, you must add a third pair key,value, where the key is one of the allowed database type defined in parameters.txt and the value is a two-elements indexed array, having "dump\_command" and "dump\_redirect\_output" as keys, and each value must be the command and the output redirect operator written accordingly to the syntax of the database platform. If some database platform does not require such this operator, you must place an empty string ("")

LOGGING RESULTS

You can include a log class named LogDeltaTime to track steps on the call of dumptable.php by the AJAX requests; to activate the log:

• download the class Class.LogDeltaTime.php from its package LogDeltaTime and place inside multidump

• uncomment the following instruction in the php file Class.MultiDump.php (remove the heading #)

  `include_once("Class.LogDeltaTime.php");`

  after index.php runs, a new directory log is created under multidump directory if not exists, and it will contain

  - a txt log file named logmaindump.txt - one log file named log\_dump\_db\_&lt;dbname&gt;\_\_table\_&lt;tablename&gt;.txt for each table dumped from &lt;dbname&gt;

  if you want to deactivate the logs, just comment the instruction

  include_once("Class.LogDeltaTime.php");

prepending a hash (#) or a double shlash (//) at it