Download the PHP package mrjgreen/db-sync without Composer
On this page you can find all versions of the php package mrjgreen/db-sync. It is possible to download/install these versions without Composer. Possible dependencies are resolved automatically.
Download mrjgreen/db-sync
More information about mrjgreen/db-sync
Files in mrjgreen/db-sync
Package db-sync
Short Description Efficient MySQL comparison and sync utility
License MIT
Informations about the package db-sync
DbSync
WARNING - This package modifies database tables. Use with extreme caution and back up databases before running.
Always perform a dry run (this is the default action) first before specifying the --execute (-e)
option.
What is it?
DbSync is a tool for efficiently comparing and synchronising two or more remote MySQL database tables.
In order to do this without comparing every byte of data, the tool preforms a checksum (MD5, SHA1, CRC32) over a range of rows on both the source and destination tables, and compares only the hash. If a block is found to have an inconsistency, the tool performs a checksum on each half of the block, recursively (down to a minimum block transfer size), until it finds the inconsistency.
Notes About Deletion
DbSync will only delete rows from the destination that no longer exist on the source when the --delete
option is specified.
Use this option with extreme caution. Always perform a dry run first.
If you use DbSync to synchronise a table which has row deletions on the source without using the --delete
option,
DbSync will find inconsistencies in any block with a deleted row on every run but will not be able to remove the rows from the target.
Installation
Via composer - run the following command in your project directory:
composer require mrjgreen/db-sync
Or use the packaged archive directly
wget https://github.com/mrjgreen/db-sync/raw/v3/db-sync.phar -O db-sync.phar
chmod a+x db-sync.phar
Optionally make the command available globally
sudo mv db-sync.phar /usr/bin/db-sync
Examples
Note - All of these commands will perform a "dry-run" only. To execute the insert/update statement against the target database, you must specify the --execute (-e) option
Example 1
Sync the table web.customers
from one host to another (non-standard port on target):
Example 2
Sync the table web.customers
from one host to another, deleting rows from the target that no longer exist on the source (using SHA1 hash for comparison):
Example 3
Sync the table web.customers
from one host to another using different credentials:
Example 4
Sync only the email
and name
fields from the table web.customers
:
NB. The primary key will automatically be included in the column set
Example 5
Sync every column except for the updated_at
fields from the table web.customers
:
Example 6
Sync every column from the table web.customers
but only use the updated_at
fields when calculating the hash:
Inconsistencies in other fields will not be detected. In the event of a hash inconsistency in fields which are included, the excluded fields will still be copied to the target host.
Example 7
Sync every column from the table web.customers
and use all fields except for the notes
or info
fields when calculating the hash:
Inconsistencies in excluded fields will not be detected. In the event of a hash inconsistency in fields which are included, the excluded fields will still be copied to the target host.
This is especially useful for tables with long text fields that don't change after initial insert, or which are associated with an
on update CURRENT_TIMESTAMP
field. For large tables this can offer a big performance boost.
Example 8
Sync the table web.customers
to a table under a different name in a different database web_backup.customers_2
:
Example 9
Sync the table web.customers
only including active records and delete records in the target table, that are no longer active:
Config File
To avoid having to specify options repeatedly, and to avoid exposing your password on the tty you can specify a config file.
By default DbSync will look for a file named dbsync.ini
in the current working directory.
Example:
Use library within project (non-commandline)
You can include the library within your project and use the components directly:
Roadmap
- [x] 100% test coverage via full stack integration tests
- [x] Allow option to delete data from target where not present on the source
- [x] Use symfony console command for sync
- [ ] Option to re-try with back-off on lock wait timeouts
- [ ] Option to create missing tables on target
- [ ] Option to skip duplicate key errors
- [ ] Speed up initial sync of empty table - Maybe offer combination with other tool for full fast outfile based replacement
Requirements
PHP 5.4 or above PDO MySQL Extension
License
DbSync is licensed under the MIT License - see the LICENSE file for details
Acknowledgments
- Inspiration for this project came from the Percona Tools
pt-table-sync
.
All versions of db-sync with dependencies
symfony/console Version ^3.2
mrjgreen/database Version ^2.5
symfony/debug Version ^3.2
ramsey/array_column Version ^1.1