1.Introduction
You have 1 Database A not a lot of data has been created since the start of the project up to now. During the code task, the customer has to update the column, change the position of the columns but it is not updated properly with the current DDL of the project.
On a nice day, customers deploy in a new environment and they want to create Database B that is migrate
from the above DDL file. That’s ok, we can do it normally, but the problem here is that we have to get all the data from Database A above to insert it into the newly created Database B structure from migrate
.
We will export the structure and data of Db A, then change the columns and data position in the export file to insert into Db B. If you have 50 tables, how?
We will use DbSync a tool to compare and synchronize two or more remote MySQL database tables very effectively.
Note:
Before proceeding with data synchronization, you should back up 1 precaution later!
2.Prerequisites
Install the package in any directory of your computer:
1 2 3 | composer require mrjgreen/db-sync |
3.Getting Started
Config File
By default, DbSync will find a file named dbsync.ini in the current directory to get the necessary parameters for connecting the Database.
1 2 3 4 5 | user=root (username Db A) password= (password Db A) target.user=(username Db B) target.password=(password Db B) |
If you do not have this file, when running the command you must enter the following parameters: --user --password
to connect to the Database.
4.Testing
Assume I have a blogs.tags
table on the server with 5 records:
At localhost I also have blogs.tags
table with this database structure:
You can see the location of the column tags
of the table tags
at localhost and the server are not the same.
If you import with the .sql
file, you must .sql
the order of columns and data of Db A to match Db B under localhost. But with DbSync , you don’t have to!
You just need to run the command:
1 2 | db-sync {host}:{port} 127.0.0.1 blogs.tags --execute |
- {host}: Host address of Db A, where data is being stored.
- {post}: The port of Mysql will usually be 3306.
- Database and table names on server and localhost are:
blogs.tags
Database B on localhost has been updated, very convenient and fast!
If you want to synchronize only the name column of the blogs.tags
table, run:
1 2 | db-sync {host}:{port} 127.0.0.1 blogs.tags -c name --execute |
There are also a lot of other interesting options, hope the article is useful for those who are experiencing similar situations to me!
Reference github link of the article here: DbSync