Our Thinking
MySQL High Availability for Cloudera CDH installation
10 August 2015 | Posted by Pawel Niemiec

Big Data Infrastructure
All today’s solutions (including almost all Hadoop stack services) use underlying databases for their speed, usefulness and ease of creation. One of the most widely used databases is MySQL. It’s Open Source, free for commercial use with decent performance. For these reasons all Hadoop stack services support MySQL as their databases, even though the original individual projects were created on top of other DB technologies (Sqlite3, PostgreSQL, Oracle, Derby, etc.).
Creation of robust solution requires that all crucial elements are deployed in High Availability mode. This sentence is especially true for databases. Today I would like to present the way to install and configure MySQL v5.6 in master-slave HA configuration on CentOS servers. I have chosen this method of MySQL HA due to internal Cloudera Manager and Hadoop technology requirements: manual failover, installation and maintenance simplicity, good control over data flow.


 

MySQL installation
What you need:
– 2 servers (VMs or physical) seeing each other on port 3306 (default one or the port of your choice) with forward and revers name resolution working properly (lets name them master and slave)
– Access to internet from these boxes (or your own local repository where you put all required files)
– A will and some time to get through the following steps
We will install MySQL service on 2 servers master and slave. We’ll configure both to use InnoDB engine (for high write throughput) and binary logging for replication. Please note below mentioned configuration is related to Hadoop cluster technology stack and might not reflect requirements of yours. Also please bear in mind there is no TLS communication setup between MySQL instances and the password is sent over the network in clear text.


 

1. Setup MySQL repository:

a. Using RPM (preferred):
wget http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
rpm -Uvh mysql-community-release-el6-5.noarch.rpm

b. Using repo files:
vim /etc/yum.repos.d/mysql-community.repo
[mysql-connectors-community]
name=MySQL Connectors Community
baseurl=http://repo.mysql.com/yum/mysql-connectors-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[mysql-tools-community]
name=MySQL Tools Community
baseurl=http://repo.mysql.com/yum/mysql-tools-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
# Enable to use MySQL 5.6
[mysql56-community]
name=MySQL 5.6 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql


 

2. Install MySQL:

a. On Master:
yum clean all
yum install -y mysql mysql-server
We need to make sure that MySQL service is bind to port, therefore we need to start rpcbind service and make it start during server boot to avoid sleepless nights spent on troubleshooting and fixing:
http://linux.die.net/man/8/rpcbind
service rpcbind start
chkconfig rpcbind on
Initialize MySQL data directory:
mysql_install_db
Stop MySQL service and prepare for InnoDB engine configuration:
service mysqld stop
Prepare directory for logs and databases:
mkdir -p /var/log/mysql
chown mysql:mysql -R /var/log/mysql
chown mysql:mysql -R /var/lib/mysql

b. On Slave:
yum clean all
yum install -y mysql mysql-server
service rpcbind start
chkconfig rpcbind on
mysql_install_db
service mysqld stop
mkdir -p /var/log/mysql
chown mysql:mysql -R /var/log/mysql
chown mysql:mysql -R /var/lib/mysql


 

3. Configure services:

a. On Master:
vim /etc/my.cnf
[mysqld]
#skip-networking
bind_address=”0.0.0.0″
transaction-isolation = READ-COMMITTED
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links = 0
key_buffer = 16M
key_buffer_size = 32M
max_allowed_packet = 32M
thread_stack = 256K
thread_cache_size = 64
query_cache_limit = 8M
query_cache_size = 64M
query_cache_type = 1
max_connections = 550
log-bin=mysql-bin
log_bin=/var/lib/mysql/mysql_binary_log
expire_logs_days = 10
max_binlog_size = 100M
server_id = 1
binlog_format = mixed
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
default-storage-engine = InnoDB
innodb = ON
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_buffer_pool_size = 4G
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT
innodb_log_file_size = 512M
#Keep master info in Table instead of file
master-info-repository=TABLE
[mysqld_safe]
log-error=/var/log/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

b. On Slave:
vim /etc/my.cnf
[mysqld]
#skip-networking
bind_address=”0.0.0.0″
transaction-isolation = READ-COMMITTED
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links = 0
key_buffer = 16M
key_buffer_size = 32M
max_allowed_packet = 32M
thread_stack = 256K
thread_cache_size = 64
query_cache_limit = 8M
query_cache_size = 64M
query_cache_type = 1
max_connections = 550
log-bin=mysql-bin
log_bin=/var/lib/mysql/mysql_binary_log
expire_logs_days = 10
max_binlog_size = 100M
server_id = 2
binlog_format = mixed
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
default-storage-engine = InnoDB
innodb = ON
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_buffer_pool_size = 4G
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT
innodb_log_file_size = 512M
#Keep master info in Table instead of file
master-info-repository=TABLE
[mysqld_safe]
log-error=/var/log/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


 

4. Start services, enable them at boot time on both servers:

a. On master
chkconfig mysqld on
mkdir -p /root/backup
mv -f /var/lib/mysql/ib_logfile* /root/backup/
service mysqld start
Make sure mysql daemon has started properly before proceeding to security settings:
– Set root password
– Remove anonymous users
– Disallow root login remotely
– Remove test database and access to it
– Reload privilege tables
/usr/bin/mysql_secure_installation

b. On slave
chkconfig mysqld on
mkdir -p /root/backup
mv -f /var/lib/mysql/ib_logfile* /root/backup/
service mysqld start
/usr/bin/mysql_secure_installation


 

5. Login to MySQL to both servers using root user and password you have just set:

a. On master
mysql -u root –p

b. On slave
mysql -u root –p


 

6. Prepare for replication

a. On Master
use mysql;
GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ IDENTIFIED BY ‘<root_pass>’ WITH GRANT OPTION;
GRANT REPLICATION SLAVE ON *.* TO ‘rep’@’slave’ IDENTIFIED BY ‘<rep_user_pass>’;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
+————————-+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+————————-+———-+————–+——————+
| mysql_binary_log.000004 | 846 | | |
+————————-+———-+————–+——————+

b. On slave using information from master:
use mysql;
stop slave;
CHANGE MASTER TO MASTER_HOST = ‘master’, MASTER_USER = ‘rep’, MASTER_PASSWORD = ‘<rep_user_pass>’, MASTER_LOG_FILE=’mysql_binary_log.000004′, MASTER_LOG_POS=846;
start slave;
SHOW SLAVE STATUS \G;
You should get following lines among other:
Slave_IO_State: Checking master version
Master_Host: master
Master_User: rep
Master_Port: 3306
Master_Log_File: mysql_binary_log.000004
Read_Master_Log_Pos: 846

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Last_Errno: 0
Last_Error:

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
ERROR:
No query specified


 

7. Test database creation, table creation, row insert replication

a. Create test DB and table on master and insert some data to it:
UNLOCK TABLES;
CREATE DATABASE testdb DEFAULT CHARACTER SET utf8;
USE testdb;
CREATE TABLE testtb (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, text VARCHAR(30) NOT NULL);
SHOW TABLES;
SHOW TABLE STATUS;
INSERT INTO testtb SET text=’It works!’;
INSERT INTO testtb SET text=’It works2!’;
SELECT * FROM testtb;

b. Check if this has been replicated on slave
SHOW DATABASES;
USE testdb;
SELECT * FROM testtb;


 


Manual Failover

a. On master simulate failure:
service mysqld stop

b. On slave
STOP SLAVE IO_THREAD;
SHOW PROCESSLIST; –(until you see “Slave has read all relay log”)
STOP SLAVE;
RESET MASTER;
c. Test
USE testdb;
INSERT INTO testtb SET text=’It works3!’;
SELECT * FROM testtb;


 

Fail back

You should be able to insert/update/delete/drop from ex-slave, current master now. You need to reconfigure your clients (or load-balancer) to point into the new master IP. Should your requirements state you need to recover master and move the whole database back to original IP/server you need to perform the following actions:
– Restore original master
– Configure original master to be a slave of original slave
– Wait for databases are in sync
– Backup databases and stop MySQL service on original slave
– Promote original master to become new master
– Reconfigure original slave to be slave again
– Start MySQL service on original slave

No comments
Leave a Comment