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.
... View more
