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!