Tuesday, May 17, 2016

MySQL Database Replication Setup

MASTER - Omegha-erp
SLAVE      - Omegha-bcp
Database Version -   MySQL 5.5

In the MASTER server


1. Enable binary logging

root@Omegha-erp:~#sudo vi /etc/mysql/my.cnf
---
[mysqld]
#log-bin=mysql-bin
log-bin=/var/log/mysql/mysql-bin.log
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
---
root@Omegha-erp:~#sudo /etc/init.d/mysql restart

2. Creation replication user account on the source database

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '*****';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

3. Take backup and note the position for slave replication

mysql> flush tables with read lock;
mysql> show master status ;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      330 |              |                  |
+------------------+----------+--------------+------------------+

$ sudo mysqldump -uroot -p*** --all-databases --master-data > dbdump.db

unlock tables;


In the SLAVE server

4. Set unique server id

root@Omegha-bcp:~#sudo vi /etc/mysql/my.cnf
---
server-id               = 2
---
root@Omegha-bcp:~#sudo /etc/init.d/mysql restart

5. Transfer backup file from the Master to Slave server

root@Omegha-bcp:~#scp -i ~/.ssh/pub-key.pem ubuntu@xx.xx.xx.xx:dbdump.db .

6. Import data

root@Omegha-bcp:~#mysql -uroot -p*** < dbdump.db

7. Point to master

mysql> change master to master_host='xx.xx.xx.xx', master_user='repl', master_password='****', master_log_file='mysql-bin.000001', master_log_pos=330;
mysql> start slave;
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: xx.xx.xx.xx
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 267
               Relay_Log_File: mysqld-relay-bin.000004
                Relay_Log_Pos: 413
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 267
              Relay_Log_Space: 716
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.01 sec)

mysql>




HAPPY LEARNING!

Remote Connect to MySQL Database Using 'MySQL Query Browser'

There was a recent requirement for giving remote access to the MySQL database to our ERP consultant for him to connect from home and do the work. Our MySQL database was running on our cloud “Omegha” and was by default restricted of any remote access.
Instead of asking the consultant to use remote SSH using PuttY, we decided to go with a better GUI client for MySQL databases.

Below are the steps used for enabling remote access to the MySQL database.

Note: - Strictly not recommended for any production environment with critical data. This is a temporary requirement and will be reverted by actual security settings once the work is done.

After writing my book on “Oracle SQL Developer 4.1”  I’ve understood non-DBAs are more comfortable with GUI clients like “Oracle SQL Developer” or “MySQL Query Browser”, In fact it is true that such beautiful free tools, which normally people don’t care about make the work quite faster with its rich features.

Step-1.  Download MySQL Query Browser using this link

Note: - Please note that development of MySQL Query Browser has been discontinued. 
MySQL Workbench provides and integrated GUI environment for MySQL database design, SQL development, administration and migration. Download MySQL Workbench »

I was ok with “MySQL Query Browser” as it served my temporary purpose



Step-2.  Start the “MySQL Query Browser” once it is installed, and create a new connection to your remote MySQL Database.
Fill in the details of your database like hostname, port, username pwd etc



Step-3.  Connect to the new connection using the login screen as shown below. Her comes my first error
Note- MySQL Error Number 2003, But my ping to the server was working fine.




Step 4.  Connected to my database using PuttY and checked if I am really able to connect to the database or not. It was perfect!!!

ubuntu@Omegha-bcp:~$ mysql -uroot -p***
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.5.47-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.02 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>


Step 5. Opened the my.cnf file under /etc/mysql directory as root user.
ubuntu@Omegha-bcp:~$ sudo su -
sudo: unable to resolve host bcp-instance
root@bcp-instance:~# cd /etc/mysql
root@Omegha-bcp:/etc/mysql# vi my.cnf

Step6. Changed the bind-address parameter to the public IP of the machine and commented the default entry of 127.0.0.1
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
bind-address            = xx.xx.xx.xxx

Step7. Restarted the mysql service
root@Omegha-bcp:/etc/mysql# service mysql restart
mysql stop/waiting
mysql start/running, process 1946
root@Omegha-bcp:/etc/mysql#


Step8. Here comes my next error when I tried connecting.
Note- MySQL Error Number 1130
                    



Step9. Again connected to my MySQL database and granted privileges to all the host (It includes any remote hosts too, Highly not recommended for prod envs) as shown below.
root@Omegha-bcp:/etc/mysql# mysql -uroot -p***
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.5.47-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant all privileges on *.* to 'root'@'%' identified by '***';
Query OK, 0 rows affected (0.03 sec)

mysql>




Step 10. Tried to make connection again using the “MySQL Query Browser” client, worked like charm J


HAPPY LEARNING!


Saturday, April 30, 2016

Loading Data From CSV File Into MySQL Table

This is my first ever post in MySQL, Reason is that I just started working in MySQL very recently. My requirement of the day was very simple, I had to load a set of data rows from a csv file into a table.

I was very sure that, there should be something similar to sqlldr in MySQL, and this is what I found and completed my work.

1)    There is a default data_dir from which this “LOAD DATA” command picks the data (This is found in data_dir parameter in /etc/mysql/my.cnf)


2)     Copy your CSV file in the /var/lib/mysql/ directory
Note:- Each schema in your database will have a directory of its own.


ubuntu@omegha-erp:~$ mysql -uroot -p***
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6784
Server version: 5.5.47-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use erp;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> LOAD DATA INFILE 'Stock1.csv'  INTO TABLE stockmaster;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`erp`.`stockmaster`, CONSTRAINT `stockmaster_ibfk_1` FOREIGN KEY (`categoryid`) REFERENCES `stockcategory` (`categoryid`))

3)    Since my data load failed with foreign key constraint and I was yet to prepare the master table data for load, I decided to disable to constraints for proceeding with my data load, and this is how I disabled the constraints check.

mysql> SET foreign_key_checks = 0;
Query OK, 0 rows affected (0.01 sec)

4)    LOAD DATA again and this time it was successful. But all my CSV values in the csv file got loaded into the first column itself, I decided to delete the rows and LOAD DATA again correctly.

mysql> LOAD DATA INFILE 'Stock1.csv'  INTO TABLE stockmaster;
Query OK, 161 rows affected, 4669 warnings (0.10 sec)
Records: 161  Deleted: 0  Skipped: 0  Warnings: 4669

mysql> delete from stockmaster;
Query OK, 164 rows affected (0.08 sec)

mysql> LOAD DATA INFILE 'Stock1.csv'  INTO TABLE stockmaster COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n';
Query OK, 161 rows affected, 214 warnings (0.10 sec)
Records: 161  Deleted: 0  Skipped: 0  Warnings: 214

mysql>


Thursday, April 21, 2016

How To Create Local YUM Repository On RHEL 6

In this post, I will by showing how to create a yum local repository on RHEL 6


1) You must be having a RHEL cd with you for creating the YUM repository
-- Load the physical CD to your CD ROM, In case its a physical server, Mount the CD into a mount point (/mnt/cdrom)
-- In my case it is a VM so I have an ISO image (Virtual CD), which is loaded and mounted into a mount point (/mnt/cdrom)

[root@ajithpathiyil_MT1 rhel64]# cd /mnt/cdrom
[root@ajithpathiyil_MT1 cdrom]# ls -ltr
total 723
-rw-r--r-- 1 root root   1397 Jan 20  2011 RPM-GPG-KEY-oracle
-rw-r--r-- 1 root root   7897 Jan 20  2011 README-en.html
-rw-r--r-- 1 root root  18390 Jan 20  2011 GPL
-rw-r--r-- 1 root root   1397 Jan 20  2011 RPM-GPG-KEY
-rw-r--r-- 1 root root  22343 Jan 20  2011 RELEASE-NOTES-en
-rw-r--r-- 1 root root   3547 Jan 20  2011 README-en
-rw-r--r-- 1 root root   3334 Jan 20  2011 eula.py
-rw-r--r-- 1 root root   7041 Jan 20  2011 eula.en_US
-rw-r--r-- 1 root root   6830 Jan 20  2011 EULA-rw
-r--r-- 1 root root   5165 Jan 20  2011 blafdoc.css
-rw-r--r-- 1 root root    105 Jan 20  2011 supportinfo
-rw-r--r-- 1 root root  53377 Jan 20  2011 RELEASE-NOTES-en.html
drwxr-xr-x 4 root root 583680 Jan 20  2011 Server
drwxr-xr-x 3 root root   2048 Jan 20  2011 Cluster
drwxr-xr-x 3 root root   4096 Jan 20  2011 ClusterStorage
drwxr-xr-x 3 root root   8192 Jan 20  2011 VT
drwxr-xr-x 2 root root   2048 Jan 20  2011 isolinux
drwxr-xr-x 4 root root   2048 Jan 20  2011 images
-r--r--r-- 1 root root   4436 Jan 20  2011 TRANS.TBL

[root@ajithpathiyil_MT1 cdrom]# cd Server
[root@ajithpathiyil_MT1 Server]# ls -ltr
total 3359313
-rw-r--r-- 1 root root   168842 Apr  9  2009 munzip-5.52-3.0.1.el5.x86_64.rpm
-rw-r--r-- 1 root root    21468 Aug 26  2010 mirqbalance-0.55-16.el5.x86_64.rpm
-rw-r--r-- 1 root root    31791 Aug 28  2010 mmcelog-0.9pre-1.30.el5.x86_64.rpm
-rw-r--r-- 1 root root  2017333 Aug 28  2010 mnet-snmp-devel-5.3.2.2-9.0.1.el5_5.1.i386.rpm
-rw-r--r-- 1 root root  1336489 Aug 28  2010 mnet-snmp-libs-5.3.2.2-9.0.1.el5_5.1.i386.rpm
-rw-r--r-- 1 root root   735231 Aug 28  2010 mnet-snmp-5.3.2.2-9.0.1.el5_5.1.x86_64.rpm
..
..
..
..
..
..
..
-rw-r--r-- 1 root root   312714 Nov 17  2010 mjdom-javadoc-1.0-4jpp.1.x86_64.rpm
-rw-r--r-- 1 root root    14449 Nov 17  2010 mfinger-server-0.17-32.2.1.1.x86_64.rpm
-rw-r--r-- 1 root root    74271 Nov 17  2010 mxorg-x11-xfs-1.0.2-4.x86_64.rpm
-rw-r--r-- 1 root root     9901 Nov 17  2010 mxorg-x11-drv-dmc-1.1.0-2.x86_64.rpm
-r--r--r-- 1 root root   805397 Jan 20  2011 TRANS.TBL
[root@ajithpathiyil_MT1 cdrom]#

2) Make a local directory and copy the contents of (/mnt/cdrom/Server) to this newly created local directory

[root@ajithpathiyil_MT1 cdrom]# mkdir -p /opt/yum/rhel64
[root@ajithpathiyil_MT1 cdrom]# cd /opt/yum/rhel64


[root@ajithpathiyil_MT1 Server]# cp -a *.rpm /opt/yum/rhel64/
[root@ajithpathiyil_MT1 Server]# uname -a
Linux ajithpathiyil_MT1.lab.com 2.6.32-100.26.2.el5 #1 SMP Tue Jan 18 20:11:49 EST 2011 x86_64 x86_64 x86_64 GNU/Linux

3) Copy the comps-rhel5-server-core.xml file to your local directory as (/opt/yum/rhel64/repodata/comps.xml)

[root@ajithpathiyil_MT1 Server]# cd ..
[root@ajithpathiyil_MT1 Server]# cd repodata
[root@ajithpathiyil_MT1 repodata]# mkdir -p /opt/yum/rhel64/repodata
[root@ajithpathiyil_MT1 repodata]# ls -ltr *comps*xml
-rw-r--r-- 1 root root 1067627 Jan 20  2011 comps-rhel5-server-core.xml
[root@ajithpathiyil_MT1 repodata]# cp *comps*xml /opt/yum/rhel64/repodata/comps.xml

4) Install the createrepo-0.4.11-3.el5.noarch rpm if not installed already.

[root@ajithpathiyil_MT1 repodata]# cd /opt/yum/rhel64/
[root@ajithpathiyil_MT1 rhel64]# rpm -ivh create*
warning: createrepo-0.4.11-3.el5.noarch.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing..#################################################################################[100%]
       package createrepo-0.4.11-3.el5.noarch is already installed
[root@ajithpathiyil_MT1 rhel64]# createrepo -g repodata/comps.xml .
                                                                               
1/3247 - cdparanoia-alpha9.8-27.2.x86_64.rpm
                                                                               
2/3247 - libXfixes-4.0.1-2.1.i386.rpm
                                                                               
3/3247 - libdmx-1.0.2-3.1.i386.rpm
                                                                               
4/3247 - smartmontools-5.38-2.el5.x86_64.rpm
                                                                                
5/3247 - nss_ldap-253-37.el5.i386.rpm
..                                                                             
..
..
..
..
..
..
..
3242/3247 - libsoup-devel-2.2.98-2.el5_3.1.x86_64.rpm
                                                                               
3243/3247 - perl-5.8.8-32.0.1.el5_5.2.x86_64.rpm
                                                                               
3244/3247 - mesa-libGL-devel-6.5.1-7.8.el5.x86_64.rpm
                                                                               
3245/3247 - eel2-devel-2.16.1-1.el5.x86_64.rpm
                                                                               
3246/3247 - adaptx-javadoc-0.9.13-3jpp.1.x86_64.rpm
                                                                               
3247/3247 - ipa-pmincho-fonts-003.02-2.1.el5.noarch.rpm
Saving Primary metadata
Saving file lists metadata
Saving other metadata

5) Create a local repository file

[root@ajithpathiyil_MT1 rhel64]# cat /etc/yum.repos.d/rhel-local.repo
name=RHEL 6.4 local repository
baseurl=file:///opt/yum/rhel64/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-redhat-release
[root@ajithpathiyil_MT1 rhel64]#

[root@ajithpathiyil_MT1 rhel64]# yum clean all
Loaded plugins: rhnplugin, security
Cleaning up Everything
root@ajithpathiyil_MT1:/opt/yum/rhel64
[root@ajithpathiyil_MT1 rhel64]#

6) Load the local repository with all the RPMs, Beautifully loading the local repository.


[root@ajithpathiyil_MT1 rhel64]# yum repolist
Loaded plugins: rhnplugin, security
This system is not registered with ULN.
ULN support will be disabled.
rhel6.4-local                                                  | 1.1 kB     00:00    
rhel6.4-local/primary                                          | 1.4 MB     00:00    

rhel6.4-local: [                                                          ] 1/3247
rhel6.4-local: [#                                                         ] 33/3247
rhel6.4-local: [###                                                       ] 99/3247
rhel6.4-local: [#####                                                     ] 166/3247
rhel6.4-local: [########                                                  ] 266/3247
rhel6.4-local: [##########                                                ] 332/3247
rhel6.4-local: [############                                              ] 398/3247
rhel6.4-local: [##############                                            ] 464/3247
rhel6.4-local: [################                                          ] 531/3247
....................................................................................
....................................................................................
....................................................................................
rhel6.4-local: [######################################################    ] 3114/3247
rhel6.4-local: [########################################################  ] 3181/3247
rhel6.4-local: [##########################################################] 3246/3247
rhel6.4-local                                                               3247/3247
repo id                                   repo name                         status
rhel6.4-local                             RHEL 6.4 local repository         enabled: 3,247
repolist: 3,247
root@ajithpathiyil_MT1:/opt/yum/rhel64
[root@ajithpathiyil_MT1 rhel64]#