Migration of the database is a crucial task involved in big tech firms. Although cloud providers have made the migration and replication of databases easy through automation, it is quite important to understand the migration tasks and how it works.
In this article, I have tried explaining it through a small POC, which can be easily done using docker. There are a lot more things that go behind database replication and migration, but this article shows a small but important part of it to understand.
Creating the first database:
docker container run --name database1 -v //c/mydata:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=password -e MYSQL_DATABASE=user mysql
Here spinning up the MySQL container we are creating our first database named database1. The important point to note is that we are referencing the SQL data directory created inside our container to the local drive path. Hence, we can get control of the SQL data files.
Creating table and populating data in the database:
As we have our MySQL container up, we can now connect to the container
docker exec -it 502 bash
Now logging in to our database we can create a table (employee) and populate the data
mysql -ppassword
Check data inside your local machine:
Now as we have referenced our local machine path to the MySQL data directory, we can view the schema and data files created by MySQL inside our folder
-v //c/mydata:/var/lib/mysql
Here user is the schema that we have created inside our database:
-e MYSQL_DATABASE=user
When we go inside this schema folder, we can view that the data file is generated for the table employee
Now as we have access to the data file, we can use it to migrate the database, or even create a replica out of it.
Let's try to scrap this database and create a new database, with this data migrated
Stopping our database container:
Now, we have destroyed our database container and think that our data is also scrapped and lost. But the catch here is that we have our data file safely in our local, using which we can create n number of databases with the data migrated.
Creating a new database:
docker container run --name database2 -v //c/mydata:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=password -e MYSQL_DATABASE=user mysql
We have created a new database named database2, and referencing the SQL data directory of the new database as well to the old data path in our local.
Login to the new database and check for data:
Now let's connect to the container and login into our database
docker exec -it 6a7 bash
mysql -ppassword
Now what we see is that we have our same old schema user and table employee inside our new database without us even creating it !
This shows that our data is already migrated to our database and is ready to access.
So using this small POC, we understood one of the ways to migrate the data if we have access to the data directory and data file of any of the databases.
Do follow me on LinkedIn for more such technical posts and discussions.