AWK command in MySQL

To extract particular words in given pattern you can use AWK command of MySQL in following ways:-

Suppose you have a file named as MyOracleConstraints.sql

ALTER TABLE MY_DB.MY_TAB1 MODIFY  MY_COL1 NOT NULL;
ALTER TABLE MY_DB.MY_TAB2 MODIFY  MY_COL2 NOT NULL;

Now to run the above script in MySQL we need to have the datatype also of the columns in the script.
If you have 1000's of such lines, you need a short-cut way of doing it.

If the tables above are already created with their columns in MySQL then you can retrieve the datatypes of all the columns of all tables in your database using foll query:-

mysql> SHOW DATABASES;
mysql> USE information_schema;
mysql> SHOW TABLES;
mysql> DESC COLUMNS;
mysql> SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE FROM information_schema.columns WHERE TABLE_SCHEMA = 'MY_DB'; | awk 'BEGIN{FS=" "}{PRINT $2 "\t" $4 "\t" $5}' > /home/meena/Desktop/meena/MyTabColDT.txt

This gives the data in tab seperated file format:-

MY_DB.MY_TAB1     MY_COL1     varchar(20)
MY_DB.MY_TAB2     MY_COL2     double(2,3)
MY_DB.MY_TAB3     MY_COL3     date

Using awk command you can store the table name, column name & column type in .csv or .txt format in a file.

mysql> awk 'BEGIN{FS=" "} /MODIFY/ {PRINT $3 "\t" $5}'  /home/meena/Desktop/MyOracleConstraints.sql > /home/meena/Desktop/meena/MyTabCol.txt

This will store the data in output file MyTabCol.txt in tab seperated file format:-

MY_DB.MY_TAB1     MY_COL1
MY_DB.MY_TAB2     MY_COL2

Now create a table in MySQL with two columns tbl_name & col_name and then insert MyTabCol.txt data in the table.
Create another table with three columns viz., tbl_name, col_name, data_type & insert MyTabColDT.txt data in it.

Next step is to apply inner join operator in MySQL to retrieve the required matching datatypes alongwith the column & table names in a file.

Master to Slave Replication in MySQL server

This kind of replication is meant for replicating data from the master server to slave server.
Open my.cnf file & type server-id=1. It should be unique number for master server.

On master server open Mysql command prompt & type:

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT TO *.* TO 'slave_user'@ '%' IDENTIFIED BY 'slave_password';

mysql> SHOW MASTER STATUS;

It will show a bin log file name & its position. Note it.

On Slave server machine's mysql terminal:-
mysql> CHANGE MASTER TO MASTER_HOST='IP_OF_MASTER', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G;

Repeat the steps for each master server.