Recommend this page to a friend! |
Download |
Info | Example | Files | Install with Composer | Download | Reputation | Support forum | Blog | Links |
Ratings | Unique User Downloads | Download Rankings | ||||
59% | Total: 564 | All time: 5,420 This week: 455 |
Version | License | PHP version | Categories | |||
multidump 1.0.8 | GNU General Publi... | 5.1 | PHP 5, Databases, Systems administration |
Description | Author | |||
This package can dump MySQL database tables for file download. Innovation Award
|
Good backup/restore MySQL
Script to backup and restore databases
<?php
|
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:
WHEN ALL THE DUMP ARE SUCCESSFULLY COMPLETED, THE HOME PAGE ALSO DISPLAYS:
*
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 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\_<database type> (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:
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
><database name>\_<table name>\_<checksum value>.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:
2.1 - the table must be dumped at each run --> the dump command line of the table is executed and the output redirected to <database name>\_<table name>\_<checksum value>.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
suppose you have several mysql databases to dump, such as:
and all of these databases are installed on the same server (but this is not relevant), having the same credentials:
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.:
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.:
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:
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 :
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 <dbtype> with the type of database (a list of allowable types is defined in the file parameters.txt), as well as replace <query...> 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
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:
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\_<dbname>\_\_table\_<tablename>.txt for each table dumped from <dbname>
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
Files (11) |
File | Role | Description |
---|---|---|
Class.DumpFileParameters.php | Class | Class containing methods to extract parameters and parse some keywords from a parameters file |
Class.MapQuery.php | Class | Class containing methods to execute PDO queries |
Class.MultiDump.php | Class | Main class containing methods to get database connections, parameters to execute MySQL command to dump tables asynchronously each to a SQL file |
Class.PDO.php | Class | Class containing methods to set a PDO connection to a MySQL database |
COPYING.txt | Lic. | GNU General Public License is a free, copyleft license for software and other kinds of works |
dbConnSettings.php | Conf. | configuration file to set all database connections to dump and optionally tables to exclude or to dump at each run of the main script |
downloaddump.php | Example | file to create dinamically zip, tgz archives to download all the sql dump tables database or one single sql dump table in both zip or gzip format |
dumptable.php | Example | Script called by an AJAX request and used to execute a mysql dump command line to a single database table |
index.php | Example | Main php script to dump MySQL databases through AJAX requests to php script asynchronously called |
parameters.txt | Data | file containing configuration parameters to execute MySQL queries and set option to MySQL dump command |
README.md | Doc. | File containing description and usage on the whole package Multi MySQLDump |
The PHP Classes site has supported package installation using the Composer tool since 2013, as you may verify by reading this instructions page. |
Install with Composer |
Version Control | Unique User Downloads | Download Rankings | |||||||||||||||
0% |
|
|
User Ratings | User Comments (2) | |||||||||||||||||||||||||||||||||||||
|
|
Applications that use this package |
If you know an application of this package, send a message to the author to add a link here.