Notes

Hi All,

Good Evening!

Here is the POL on Database Migration:
I learned about database migration, Database migration is a process of transferring data from one database system to another, or from one version of a database to another version.
I learned to export tables using commands:
I need to create a database and table inside the database. We need to create a schema and insert some entries to export in the csv file.
I can use this command to create a csv file : "select * into outfile 'users.csv' fields terminated by ',' lines terminated by 'n' from users".
If you get an error after running this you have to run this command : "show variables like "secure_file_priv", you will get a table and you will get a path and update the path in the previous command.
After updating the path run the command : "select * into outfile '/var/lib/mysql-files/users.csv' fields terminated by ',' lines terminated by 'n' from users".
To view that file change the permissions of folder and file, you will see there mysql table is copied to that .csv file.
Learned to import that csv file in mongodb.
First, we need to go to the path of the file where the csv file is present.
To import the csv file in mongodb use the command: "mongoimport -d test -c users --type csv --file users.csv --headerline".
I learned to export mongodb tables by using commands:
To export the database from mongodb use this command: "mongoexport --db=test --collection=users --type=csv --fields=_id,id,name,email,created_at --out=mongo.csv".
Then go to the mysql console using "sudo mysql" and run the command "SET GLOBAL local_infile=1" to enable the local data load.
To import in mysql use this commands: "mysql -u root -p --local_infile=1 -e "CREATE DATABASE IF NOT EXISTS test; USE test; CREATE TABLE IF NOT EXISTS users (_id varchar(30), id int(32), name varchar(50), email varchar(50), created_at varchar(50)); LOAD DATA LOCAL INFILE 'mongo.csv' INTO TABLE users FIELDS TERMINATED BY ',' IGNORE 1 LINES;"
I learned to automate migrate mysql to mongodb using bash script:
#!/bin/bash

#MYSQL credentials
DB_USER="root"
DB_PASSWORD="password"
DB_NAME="test"
DB_FILE="users1.csv"
DB_TABLE="users"

#MONGODB credentials
MONGO_USER="root"
MONGO_PASSWORD="password"
MONGO_NAME="test"
MONGO_TABLE="users"

#PATH
BACKUP_DIR="/home/kunal"
TIMESTAMP=$(date +"%y-%m-%d %H:%M:%S")
BACKUP_FILE="$BACKUP_DIR/$DB_NAME_$TIMESTAMP.sql"

#EXPORT mysql database
mysql -u root -p$DB_PASSWORD -e "(select 'id','name','email','created_at') union all (select id,name,email,created_at into outfile
'/var/lib/mysql-files/$DB_FILE' fields terminated by ',' lines terminated by 'n' from $DB_NAME.$DB_TABLE)"
cd /var/lib/mysql-files
sudo chmod 777 $DB_FILE
cd
mongoimport -d $MONGO_NAME -c $MONGO_TABLE --type csv --file /var/lib/mysql-files/$DB_FILE --headerline
I learned to automate migrate mongodb to mysql using bash script:
#!/bin/bash

#MYSQL credentials
DB_USER="root"
DB_PASSWORD="password"
DB_NAME="test"
DB_FILE="users1.csv"
DB_TABLE="users"

#MONGODB credentials
MONGO_USER="root"
MONGO_PASSWORD="password"
MONGO_NAME="test"
MONGO_TABLE="users"

mongoexport -d $MONGO_NAME -c $MONGO_TABLE --type csv --fields id,name,email,created_at --out $DB_FILE

mysql -u $DB_USER -p$DB_PASSWORD --local-infile=1 -e "use $DB_NAME; CREATE TABLE IF NOT EXISTS $DB_TABLE(id int(10), name varchar(30),
email varchar(32), created_at varchar(30)); LOAD DATA LOCAL INFILE '$DB_FILE' into table $DB_TABLE fields terminated by ',' ignore 1 lines;"
I also learned about some shell scripts commands like 'awk' BEGIN block and END block and also learned about 'sed' command.