Recommend this page to a friend! |
Classes of Alessandro Quintiliani | PHP Multi MySQLDump | README.md | Download |
|
DownloadMulti MySQLDumpPACKAGE 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:
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
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
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):
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
According to the above example, if you want to skip the following tables, i.e.:
you must insert the following method calls:
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
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:
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:
<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
but if you place by mistake my\_important\_table also in the array of the excluded tables and call the method setListIgnoredTables, such as
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
or, if you place this package inside a webroot at my.domain.com, launch the URL
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:
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
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:
the two extra options
2) In the class Class.DumpFileParameters.php the following indexed array is defined
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:
prepending a hash (#) or a double shlash (//) at it |