Database Replication Master-Slave

Database replication is a strategy for maintaining multiple copies of data across different servers to ensure data availability and redundancy. In a master-slave setup, the master server handles all write operations while one or more slave servers handle read operations and act as a backup. This guide will take you through 10 simple steps to set up a master-slave replication in MySQL.

A. Master Server Configuration

Step 1: Configure MySQL Settings

Edit MySQL Configuration: Open /etc/my.cnf in a text editor and enter the following under the [mysqld] section:

[mysqld]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id = 1
log-bin=db-bin
relay-log=db-relay-bin

Step 2: Grant Replication Privileges
2. Grant Slave Access:
Access MySQL (mysql -u root -p) and execute:

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'SLAVE_IP' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;

Replace SLAVE_IP with your slave server's IP address and password with a secure password.

Step 3: Lock Tables and Note Master Status
3. Lock Tables:

FLUSH TABLES WITH READ LOCK;

4. Check Master Status:

SHOW MASTER STATUS;

Note the File and Position values.

5. Unlock Tables:

UNLOCK TABLES;

B. Slave Server Configuration

Step 4: Configure MySQL Settings

Edit MySQL Configuration:Open /etc/my.cnf in a text editor and enter the following under the [mysqld] section

server-id=2
master-host = 10.10.3.21
master-user = replication
master-password = password
master-port = 3306 

10.10.3.21 is the MASTER server IP. These information will be stored to /var/lib/mysql/master.info

Step 5: Setup Replication on Slave
2. Access MySQL:

mysql -u root -p

Stop Slave Threads:

STOP SLAVE;

Reset Previous Master Info:

RESET SLAVE;

Change Master:

CHANGE MASTER TO MASTER_HOST='MASTER_IP', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE='Master_Binlog_File', MASTER_LOG_POS=Master_Log_Position;

Replace MASTER_IP with your master server's IP address, Master_Binlog_File with the file noted earlier, and Master_Log_Position with the position noted earlier.

Start Slave Threads:

START SLAVE;

Step 6: Verify Slave Status Check Replication Status:

SHOW SLAVE STATUS \G;

Ensure Slave_IO_Running and Slave_SQL_Running are both set to Yes, and Seconds_Behind_Master is a non-negative number.


Conclusion
You've now successfully configured a basic master-slave replication setup. This configuration allows the slave to take over in case the master goes down, and it can also help with load balancing by directing read queries to the slave. Regularly check the replication status and backup your data to ensure everything operates smoothly. Remember, replication is just one part of a comprehensive disaster recovery plan.

Setting Up PostgreSQL on Your Linux System

PostgreSQL, also known as "Postgres," is a highly extensible and standards-compliant object-relational database management system (ORDBMS). It's renowned for its robustness, ACID-compliance, advanced features like multiversion concurrency control (MVCC), and a wide array of indexing methods, functions, and more. This guide will walk you through configuring your YUM repository, installing PostgreSQL, and setting up a basic database schema.

Step 1: Configure Your YUM Repository

Locate and Edit Your Distributions .repo File:

• Fedora: Edit /etc/yum.repos.d/fedora.repo and /etc/yum.repos.d/fedora-updates.repo, specifically the [fedora] sections.
• CentOS: Edit /etc/yum.repos.d/CentOS-Base.repo, focusing on the [base] and [updates] sections.
• Red Hat: Edit /etc/yum/pluginconf.d/rhnplugin.conf and look for the [main] section.

Append the Exclude Line: To each of the sections identified above, append the following line to prevent the default PostgreSQL package from being installed, as it may be outdated:

exclude=postgresql*

Step 2: Download and Install the PGDG RPM File

PGDG File: PostgreSQL Global Development Group (PGDG) provides an optimized and more up-to-date version of PostgreSQL.

1. Find the Correct RPM:

   • Browse PostgreSQL's YUM repository to find the RPM suitable for your distribution, architecture, and desired PostgreSQL version.

2. Download the RPM:

   • For example, to install PostgreSQL 9.3 on CentOS 6 64-bit:

   curl -O http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm
   

3. Install the RPM Distribution:

   rpm -ivh pgdg-centos93-9.3-1.noarch.rpm
   

Step 3: Install PostgreSQL

List Available Packages:

yum list postgres*

Install PostgreSQL Server:

• For a basic PostgreSQL 9.3 server installation:

  yum install postgresql93-server
  

Step 4: Accessing PostgreSQL

Switch to the PostgreSQL User:

su - postgres

Start Using PostgreSQL:

psql

You're now in the PostgreSQL command line. Here you can manage databases, execute SQL queries, and more.

Step 5: Setting Up a Basic Database Schema

Create a Schema Called test:

CREATE SCHEMA test;

Create a Role (User) with Password:

CREATE USER xxx PASSWORD 'yyy';

Grant Privileges on New Schema to New Role:

GRANT ALL ON SCHEMA test TO xxx;

Grant Privileges on Tables in the New Schema to the New Role:

GRANT ALL ON ALL TABLES IN SCHEMA test TO xxx;

Step 6: Disconnecting

Exit psql:

\q

Conclusion

Congratulations! You've successfully set up PostgreSQL on your Linux system. You've also created your first schema and user with privileges. PostgreSQL is a powerful tool with many more capabilities and features to explore. As you become more familiar with its workings, you'll be able to leverage its full potential in managing and analyzing your data effectively. Don't forget to regularly check for updates and maintain your PostgreSQL installation to ensure security and performance.

Mysql error : Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'))

Error creating issue: Could not create workflow instance: root cause: while inserting: [GenericEntity:OSWorkflowEntry][id,null][name,jira][state,0] (SQL Exception while executing the following:INSERT INTO OS_WFENTRY (ID, NAME, INITIALIZED, STATE) VALUES (?, ?, ?, ?) (Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'))

 

Cause This is required by MySQL:
Statement based binlogging does not work in isolation level READ UNCOMMITTED and READ COMMITTED since the necessary locks cannot be taken.

 

Resolution
To change to row based binary logging, set the following in /etc/my.cnf (or your my.cnf if it's elsewhere):

binlog_format=row

Mysql HA with Haproxy and Master-Master replication

Once Mysql master -master replication is done we set HA with those using HAproxy

http://enekumvenamorublog.wordpress.com/2014/06/25/mysql-replication-master-master/

HAProxy

yum install haproxy

To use Haproxy with MYsql we need to create a user in mysql so that haproxy can access it .

GRANT ALL PRIVILEGES ON *.* TO 'haproxy'@'192.168.216.180' IDENTIFIED BY '';

Sample configuration  /etc/haproxy/haproxy.cfg

global
log 127.0.0.1 local0 notice
user haproxy
group haproxy

# turn on stats unix socket
stats socket /var/lib/haproxy/stats mode 777

defaults
log global
retries 2
timeout connect 1000
timeout server 5000
timeout client 5000
listen stats 192.168.255.180:80
mode http
stats enable
stats uri /stats
stats realm HAProxy\ Statistics
stats auth admin:password

listen MYSQL 192.168.255.190:3306
balance source
mode tcp
option mysql-check user haproxy
server controller1 192.168.216.130 check
server controller2 192.168.216.135 check
[root@HAPROXY ~]#

Mysql replication-Master-Master

MySQL Master-Master replication.

Master-1 my.cnf configuration:

mkdir /var/lib/mysql/log/
log-bin=/var/lib/mysql/log/mysql-bin
log_warnings
log_slow_queries = /var/lib/mysql/log/slow.log
long_query_time = 5
log_long_format
tmpdir = /tmp
server-id = 1
log_slave_updates
replicate-same-server-id = 0
auto_increment_increment = 10
auto_increment_offset = 1
relay-log = mysql-relay-bin

Master-2 my.cnf configuration:
mkdir /var/lib/mysql/log/
log-bin=/var/lib/mysql/log/mysql-bin
log_warnings
log_slow_queries = /var/lib/mysql/log/slow.log
long_query_time = 5
log_long_format
tmpdir = /tmp
server-id = 2
replicate-same-server-id = 0
auto_increment_increment = 2
auto_increment_offset = 2
relay-log = mysql-relay-bin

First setup Master1 as Master and Master2 as slave for Master-1:

Follow below steps:
On Master-1:

grant replication slave on *.* to 'root'@'192.168.216.135' identified by 'admin';
show master status;

It shows file name and position, Use these records on Master-2 to run it as slave for Master-1.
Step 3: Now log on to master-2 and run the below query:

CHANGE MASTER TO MASTER_HOST='192.168.216.130', MASTER_USER='root',MASTER_PASSWORD='admin', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=106;

Step 4: start slave
Step 5: show slave status \G

On this status, the following 2 records should be as follows
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

These 2 records indicates Replication status. If these parameters show “Yes” that means replication is running successfully.

Setup Master2 as Master and Master1 as slave for Master-2:

On Master-2 server:

grant replication slave on *.* to 'root'@'192.168.216.130' identified by 'admin

Step 2: mysql> show master status;

Step 3: Now log on to master-1 and run the below query:
CHANGE MASTER TO MASTER_HOST='192.168.216.135', MASTER_USER='root',MASTER_PASSWORD='admin', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=346;
Step 4: start slave
Step 5: show slave status \G

The following parameters should show “Yes”, so that replication is running successfully

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

On both servers “slave_IO_Running” and “slave_SQL_Running” parameters should always be “Yes” for successful Master-Master Replication.

MySql Server Cluster (Maria+galera)

Add MariaDB Repositories
========================
Create a mariadb repository /etc/yum.repos.d/mariadb.repo using following content in your system. Below repository will work on CentOS 6.x systems, For other system use repository generation tool and add to your system.

Disable Selinux in redhat sever's.

For CentOS 6 – 64bit

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
For CentOS 6 – 32bit

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-x86
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
Install MariaDB and Galera
==========================
Before installing MariaDB Galera cluster packages, remove any existing MySQL or MariaDB packages installed on system. After that use following command to install on all nodes.

# yum install MariaDB-Galera-server MariaDB-client galera

Initial MariaDB Configuration
=============================
After successfully installing packages in above steps do the some initial MariaDB configurations. Use following command and follow the instructions on all nodes of cluster. If will prompt to set root account password also.

# mysql_secure_installation
# service mysql start
After that create a user in MariaDB on all nodes, which can access database from your network in cluster.

# mysql -u root -p

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit
and stop MariaDB service before starting cluster configuration

# service mysql stop
Setup Cluster Configuration on database1
========================================
Lets start setup MariaDB Galera cluster from database1 server. Edit MariaDB server configuration file and add following values under [mariadb] section.

[root@database1 ~]# vim /etc/my.cnf.d/server.cnf
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.4,10.0.0.5
wsrep_cluster_name='cluster1'
wsrep_node_address='10.0.0.2'
wsrep_node_name='database1'
wsrep_sst_method=rsync
wsrep_sst_auth=root:password
Start cluster using following command.

[root@database1 ~]# /etc/init.d/mysql bootstrap
Bootstrapping the clusterStarting MySQL.... SUCCESS!
If you get any problem during startup check MariaDB error log file /var/lib/mysql/<hostname>.err

Add database2 in MariaDB Cluster
================================
After successfully starting cluster on database1. Start configuration on database2. Edit MariaDB server configuration file and add following values under [mariadb] section. All the settings are similar to database1 except wsrep_node_address, wsrep_cluster_address and wsrep_node_name.

[root@database2 ~]# vim /etc/my.cnf.d/server.cnf

query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.2,10.0.0.5
wsrep_cluster_name='cluster1'
wsrep_node_address='10.0.0.4'
wsrep_node_name='database2'
wsrep_sst_method=rsync
wsrep_sst_auth=root:password

Start cluster using following command.

[root@database2 ~]# /etc/init.d/mysql start
Starting MySQL..... SUCCESS!

Add database3 in MariaDB Cluster
================================
This server is optional, If you want only two server in cluster, you can ignore this step, but you need to remove third server ip from database1/database2 configuration files. To add this server make changes same as database2.

[root@database3 ~]# vim /etc/my.cnf.d/server.cnf
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.2,10.0.0.4
wsrep_cluster_name='cluster1'
wsrep_node_address='10.0.0.5'
wsrep_node_name='database3'
wsrep_sst_method=rsync
wsrep_sst_auth=root:password
Start cluster using following command.

[root@db3 ~]# /etc/init.d/mysql start
Starting MySQL..... SUCCESS!