High Availability for MySQL Database

[#1]

When you install HDP cluster and if you select 'MySQL' database to store ambari or hive metastore or Ranger or oozie databases. The important aspect is to setup MYSQL replication and below step will illustrate how to setup MYSQL database replication.

Mysql Master-Master-Active-Passive Replication : ================================================ step 1- Configure Master-Slave Replication ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ On server A: ------------

1) Shutdown the mysql server process if its running. #mysqladmin -u root shutdown -p

2) Edit my.cnf file with following values:

# cat /etc/my.cnf [mysqld] datadir=/hadoop/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # New parameters for Ranger partitioned tables innodb_file_per_table=1 innodb_buffer_pool_size=4G innodb_flush_log_at_trx_commit=2 innodb_flush_method=O_DIRECT

log_bin=mysql-bin binlog_format=ROW server_id=10 innodb_support_xa=1

[mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid

3) Bring up mysql #service mysqld start

4) Verify by running below command that server is now logging bin-logs # mysql -u root -p mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 5810761 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)

5) Make sure root user is able to do dump of MYSQL database by running following command

select user,host from mysql.user; - check if user already present

mysql>CREATE USER ‘root’@’<serverA.FQDN>’ identified by ‘root’; mysql>GRANT ALL ON *.* to ‘root’@’<serverA.FQDN>’;

6) Create replication user that will be used in future replications:

mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'<server-B.FQDN>' IDENTIFIED BY 'xxxxx';

7) Run mysqldump to dump all tables from the master and load them onto the slave as follows:

mysqldump --single-transaction --all-databases --master-data=1 --host=<serverA.FQDN> > /home/hadoopMYSQL.out -p

😎 copy this dump file over to the serverB

on server B : -------------

1) shutdown the mysql server process if its running.

2) Edit my.cnf file with following values:

# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # New parameters for Ranger partitioned tables innodb_file_per_table=1 innodb_buffer_pool_size=4G innodb_flush_log_at_trx_commit=2 innodb_flush_method=O_DIRECT

log_bin = mysql-bin binlog_format = ROW server_id = 11 relay_log = mysql-relay-bin log_slave_updates = 1 read_only = 1

[mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid

3) Bring up mysql #service mysqld start

4) Create user which can load the prepared dump of the mysql database, by running the following command:

mysql>create user 'root'@'<server-B.FQDN>' identified by 'root'; mysql>grant all on *.* to 'root'@'<server-B.FQDN>';

5) Load the dump that was generated on serverA

mysql --host=<server-B.FQDN> -p < /home/hadoop/MYSQL.out

6)Verify that all the database was transferred from dump file

mysql> show databases

7) Get the File name and position from master server i.e serverA and put in master_log_file and position

mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 5810761 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)

Run below command on server B

mysql> CHANGE MASTER TO MASTER_HOST='<server-A.FQDN>', MASTER_USER='repl',MASTER_PASSWORD='xxxxx', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=5810761;

😎 Restart mysql server

9) check replication is working by verifying Seconds_Behind_Master as being 0.

mysql> SHOW SLAVE STATUS \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 9231663 Relay_Log_File: mysql-relay-bin.000012 Relay_Log_Pos: 6451675 Relay_Master_Log_File: mysql-bin.000003 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: 9231663 Relay_Log_Space: 6451830 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 <------- If replication is working it will show '0' Lag Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec)

10) Now we whave Master - slave replication working from server A--> serverB.

Step 2 - Master-Master Active-passive Replication ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

1) Now we need to configure Server-A's master as server-B as we configured server-B's master as server-A in above steps

2) On server B, create replication user

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'<server-A.FQDN>' IDENTIFIED BY 'xxxxx';

mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000007 | 6605279 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)

3) on server A, configure master as server B ( provide master log file & positions)

mysql> CHANGE MASTER TO MASTER_HOST='<server-B.FQDN>', MASTER_USER='repl',MASTER_PASSWORD='xxxxx',MASTER_LOG_FILE='mysql-bin.000007',MASTER_LOG_POS=6605279;

mysql>START SLAVE;

4) on server A,

mysql> SHOW SLAVE STATUS \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000007 Read_Master_Log_Pos: 6872411 Relay_Log_File: mysqld-relay-bin.000004 Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.000007 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: 6872411 Relay_Log_Space: 552 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 <------- If replication is working it will show '0' Lag Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec)

5) test bidirectional replication is working by creating test tables,databases

Switching Active and Passive Roles in the Master Master Active Passive Setup : ==============================================================================

1) shutdown Hive metastore server so it stops any new writes to the database.

2) switch the "active" master to read-only.

mysql> Flush tables with read lock; mysql> set global read_only=on;

3) Wait till "passive" master to be caught up to replicating all updates from the old "active" master

4) Now switch the passive master's read only flag to off. mysql> set global read_only=off; mysql> Flush tables with read lock;

6) Change the config in AMbari ( hive metastore/Ranger/oozie ) to point to new active mysql server.

7) Point ambari server to new active MYSQL server.

😎 Restart all Application.

[#2]

This won't actually work as the Hive and Ambari database structure doesn't support group replication. Several tables are missing primary keys and will lead to problems in replication. See Group Replication Requirements.

[#3]

• DONOT restart MySQL in next step (Step 2 - Master-Master Active-passive Replication --> Step#4), just start slave need to run. Do as mentioned.
• If below mentioned command doesn't run then run this command: STOP SLAVE; run the command and start the slave like this START SLAVE;
  CHANGE MASTER TO MASTER_HOST='<server-B.FQDN>', MASTER_USER='repl',MASTER_PASSWORD='xxxxx',MASTER_LOG_FILE='mysql-bin.000007',MASTER_LOG_POS=6605279;

[#4]

Doe this actually work. I have to do this in my current organization.

[#5]

Can you please provide me the actual working steps then. I need to enable MYSQL HA for my cluster here. @Shawn Weeks

[#6]

Also please let me know if we can have a common URL to automatically resolve the active MYSQL node. @jigar.patel

[#7]

@Abhilash Chandrasekharan ...were you able to enable HA for mysql database?

If yes, could you please help us by posting the steps you followed.