MySQL High Availability for Cloudera CDH installation

Date posted
10 August 2015
Reading time
10 Minutes
Pawel Niemiec

MySQL High Availability for Cloudera CDH installation

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

About the author

Pawel Niemiec