Hive Metastore HA - MySQL Replication For Failover Protection - Hortonworks
Hive Metastore HA - MySQL Replication For Failover Protection - Hortonworks
Hive Metastore HA - MySQL Replication For Failover Protection - Hortonworks
com)
ANSWERS (/ANSWERS/INDEX.HTML)
Search across Answers, Knowledge Base, Code Hub...
CREATE
TRACKS
Login (/users/login.html)
SEARCH
Home (/) / Data Processing (/spaces/66/data-processing-track_2.html) /
Question by rxu (/users/273/rxu.html) Oct 27, 2015 at 12:06 AM Hive (/topics/Hive.html) mysql (/topics/mysql.html) metastore
(/topics/metastore.html)
VOTES (/QUESTIONS/2172/HIVE-METASTORE-HA-MYSQL-REPLICATION-FOR-FAILOVER-P.HTML?SORT=VOTES)
CREATED (/QUESTIONS/2172/HIVE-METASTORE-HA-MYSQL-REPLICATION-FOR-FAILOVER-P.HTML?SORT=NEWEST)
OLDEST (/QUESTIONS/2172/HIVE-METASTORE-HA-MYSQL-REPLICATION-FOR-FAILOVER-P.HTML?SORT=OLDEST)
Best Answer
@rxu@hortonworks.com (mailto:rxu@hortonworks.com)
It will Active-Passive setup
(/users/140/nsabharwal.html)
(/users/578/eddiejohnson.html)
Replication Models
MySQL supports two main forms of replication:
2. Edit /etc/my.cnf and add the following -- be sure to add between the [mysqld] and the [mysqld_safe] headings or the settings won't
work:
#ServerIDforthisnode,mustbedifferentfromtheslavenode
server_id=10
#Enterthenameforthebinlogfile
log_bin=mysqlbin
#Setthebinlogtorowformat
binlog_format=ROW
#Thenextlineisthedefaultvalueinnewerversions,uncommentforolderversions
#innodb_flush_logs_at_trx_commit=1
#Thenextlineisthedefaultvalueinnewerversions,uncommentforolderversions
#innodb_support_xa=1
3. Bring up the mysql process again -- this step may vary depending on your installation. In a typical RHEL setup, the command is:
>servicemysqldstart
4. Verify the server is now logging bin-logs -- you should see a binlog filename and position:
>mysql
mysql>showmasterstatus\G
or
mysql>showmasterstatus;
***************************1.row***************************
File:mysqlbin.000002
Position:120
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1rowinset(0.00sec)
5. Create a replication user that will be used for all future replications:
mysql>createuser'repl'@'ServerB.my.example.com'identifiedby'Pa$w0rd';
mysql>grantreplicationslave,replicationclienton*.*to'repl'@'ServerB.my.example.com';
6. Run mysqldump to dump all the tables from the master into a dump file in your local directory:
7.
>cd/tmp
>mysqldumpsingletransactionalldatabasesmasterdata=1>dump.out
Enterpassword:
>lsdump.out
dump.out
8. Copy this dump file over to the slave node, Server-B.my.example.com, using scp or another tool:
>scpdump.outroot@ServerB.my.example.com:/tmp
root@ServerB.my.example.com'spassword:
dump.out
Now on Server-B.my.example.com:
1. Shut down the MySQL process if it is running:
>mysqladminshutdown
or
>servicemysqldstop
2. Edit the /etc/my.cnf and add the following -- be sure to add between the [mysqld] and the [mysqld_safe] headings or the settings won't
work:
#ServerIDforthisnode,mustbedifferentfromthemasternode
server_id=20
#Enterthenameforthebinlogfile
log_bin=mysqlbin
#Setthebinlogtorowformat
binlog_format=ROW
#Enterthenamefortherelaybinlogfile
relay_log=mysqlrelaybin
#Setthisnodetoreadonly
read_only=1
#Logupdatesfortheslavenodeturnthisoff
log_slave_updates=0
#Thenextlineisthedefaultvalueinnewerversions,uncommentforolderversions
#innodb_flush_logs_at_trx_commit=1
#Thenextlineisthedefaultvalueinnewerversions,uncommentforolderversions
#innodb_support_xa=1
4. Load the dump file from the master server. From the directory with the dump file:
5.
>mysql</tmp/dump.out
6. Verify the metastore databases were copied across properly. Run the following command and look for your metastore databases:
>mysql
mysql>showdatabases;
7. Back on Server-A.my.example.com, we need to gather some information. Run the command below and make note of the filename and
the position number:
>mysql
mysql>showmasterstatus\G
or
mysql>showmasterstatus;
8. And switching to Server-B.my.example.com, we can now start replication. Run the following command, substituting in the values for your
filename and position gathered in the previous step:
mysql>changemastertomaster_host='ServerA.my.example.com',master_user='repl',\
master_password='Pa$w0rd',master_log_file='mysqlbin.000001',master_log_pos=0;
mysql>quit
10. Check that replication is running properly by issuing the following command. Verify you see correct values for Master_User and
Master_Host. Also, once replication is caught up, you will see Seconds_Behind_Master=0. If you don't see this, check your hostnames,
user names, passwords, and verify you aren't being blocked by iptables or network firewalls:
>mysql
mysql>showslavestatus\G
or
mysql>showslavestatus;
3. Verify the slave io_thread process stopped by checking the status. Slave_IO_Running will be No
mysql>showslavestatus\G
***************************1.row***************************
Slave_IO_State:
Master_Host:master.test.com
Master_User:repl
Master_Port:3306
Connect_Retry:60
Master_Log_File:mysqlbin.000004
Read_Master_Log_Pos:120
Relay_Log_File:mysqlrelaybin.000013
Relay_Log_Pos:283
Relay_Master_Log_File:mysqlbin.000004
Slave_IO_Running:No
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:120
Relay_Log_Space:619
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:NULL
Master_SSL_Verify_Server_Cert:No
Last_IO_Errno:2003
Last_IO_Error:errorconnectingtomaster'repl@master.test.com:3306'retrytime:60re
tries:1
Last_SQL_Errno:0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:10
Master_UUID:d2067804487111e5a5ec080027fb3c8b
Master_Info_File:/var/lib/mysql/master.info
SQL_Delay:0
SQL_Remaining_Delay:NULL
Slave_SQL_Running_State:
Master_Retry_Count:86400
Master_Bind:
Last_IO_Error_Timestamp:15082220:24:19
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position:0
1rowinset(0.00sec)
4. Wait for the slave to process the remaining entries in the relay log. Run the command below until you see "has read all relay log" as
shown below
mysql>showprocesslist;
+++++++
++
|Id|User|Host|db|Command|Time|State
|Info|
+++++++
++
|1|systemuser||NULL|Connect|354|Slavehasreadallrelaylog;waitingforthesla
veI/Othreadtoupdateit|NULL|
|4|root|localhost|NULL|Query|0|init
|showprocesslist|
+++++++
++
2rowsinset(0.00sec)
5. Exit the MySQL client and edit to /etc/my.cnf file. Change read_only to 0
mysql>quit
>vi/etc/my.cnf
read_only=0
11. At this point the MySQL Slave server is the active node. You will need to configure any client systems to use the new server. Please see
the Related Articles below for next steps.
Add comment Share
(https://na9.salesforce.com/articles/en_US/How_To/How-to-Failover-MySQL-with-Master-Slave-Replication?
id=kA1E0000000LZnm&popup=true&sfdcIFrameOrigin=https%3A%2F%2Fna9.salesforce.com&isWsVw=true&nonce=996dfa5f7b88e
50629aabb9f3d9c6bdef12e8a38afb7049132b26735cc534a6b)
2. You might also need to failover your Ambari server to the Slave node by following the steps in the article How to Failover the Ambari
Metastore to the MySQL Slave Node (https://na9.salesforce.com/articles/en_US/How_To/How-to-Failover-the-Ambari-Metastoreto-the-MySQL-Slave-Node?
id=kA1E0000000LZnr&popup=true&sfdcIFrameOrigin=https%3A%2F%2Fna9.salesforce.com&isWsVw=true&nonce=996dfa5f7b88e5
0629aabb9f3d9c6bdef12e8a38afb7049132b26735cc534a6b)
3.
4.
5.
6.
Open up a web browser and log on to your Ambari server as an administrative user
Click into Services, then Hive, then Configs
Scroll down and expand the section for Hive Metastore
Change the Database Host to the name of your Slave node (Ex: Change from mysql1.test.com to mysql2.test.com). Verify the Database
URL automatically updates with the new name as well
7. Scroll up and click on Save and enter a description for your change
8. Scroll up and locate the "Restart Required" box, and click on Restart All
Restart Required
9. Once the services restart, you are up and running on the MySQL Slave node. To test this, log on to the Hive server and issue the
commands below. The show databases command should return a list. If it locks up, then you still have an issue with connectivity to the
Slave node.
>hive
Logginginitializedusingconfigurationinfile:/etc/hive/conf/hivelog4j.properties
SLF4J:ClasspathcontainsmultipleSLF4Jbindings.
SLF4J:Foundbindingin[jar:file:/usr/hdp/2.2.6.02800/hadoop/lib/slf4jlog4j121.7.5.jar!/org/slf4j/imp
l/StaticLoggerBinder.class]
SLF4J:Foundbindingin[jar:file:/usr/hdp/2.2.6.02800/hive/lib/hivejdbc0.14.0.2.2.6.02800standalon
e.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J:Seehttp://www.slf4j.org/codes.html#multiple_bindingsforanexplanation.
SLF4J:Actualbindingisoftype[org.slf4j.impl.Log4jLoggerFactory]
hive>showdatabases;
OK
default
Timetaken:6.837seconds,Fetched:1row(s)
hive>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://mysql2.test.com/hive?createDatabaseIfNotExist=true</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hive</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>hive</value>
</property>
Your answer
Fillinthedetails...
Hint: You can notify a user about this post by typing @username
POST ANSWER
FOLLOW THIS
QUESTION
Answers
(/Feed/2172/Answers.Rss)
Answers And Comments
(/Feed/2172/CommentsAnd-Answers.Rss)
(/users/273/rxu.html)
(/users/12/mcarter.html)
(/users/68/gliu.html)
(/users/213/mmadan.html)
(/users/132/abajwa.html)
(/users/218/rmani.html)
(/users/138/bbukacek.html)
(/users/334/mkhandekar.html)
(/users/356/jabbott.html)
(/users/229/bdurai.html)
(/users/412/ddas.html)
(/users/191/bsaini.html)
(/users/149/skumpf.html)
(/users/186/sshaw.html)
(/users/248/ashish.html)
(/users/175/dstreever.html)
(/users/231/aahn.html)
(/users/232/scasey.html)
(/users/56/bryan.html)
(/users/152/jp.html)
(/users/298/sseth.html)
(/users/139/pardeepkumar.html)
(/users/210/mjohansson.html)
(/users/397/ppruski.html)
(/users/203/csankaraiah.html)
(/users/429/jplayer.html)
(/users/11/gvetticaden.html)
(/users/112/rgarcia.html)
(/users/113/jstraub.html)
(/users/578/eddiejohnson.html)
RELATED QUESTIONS
With Hive View what causes:
"H100 Unable to submit
statement show databases
like '*':
org.apache.thrift.transport.TTransportException:
java.net.SocketException:
Broken pipe"?
(/questions/1288/with-hiveview-what-causes-h100unable-to-submit-s.html) 3
Answers
What is the best approach for
migrating (no data loss) the
Hive metastore database
from MySQL to Oracle?
(/questions/4343/what-isthe-best-approach-formigrating-no-data-lo.html) 1
Answer
how to migrate Hive data
over to new cluster?
(/questions/4496/how-tomigrate-hive-data-over-tonew-cluster.html) 3 Answers
Cause of DBVisulizer client
error after turning on
LZO/snappy compression?
(/questions/38/cause-ofdbvisulizer-client-error-afterturning-on.html) 1 Answer
Beeline sample with -u -f -n p (/questions/3147/beelinesample-with-u-f-n-p.html) 3
Answers
HCCGuidelines(http://community.hortonworks.com/page/community+guidelines)|HCCFAQs
(http://community.hortonworks.com/page/faq.html)|HCCPrivacyPolicy(http://hortonworks.com/wp
content/uploads/2015/11/HortonworksCommunityConnectionBetaTerms.pdf)
20112015HortonworksInc.AllRightsReserved.
Hadoop,Falcon,Atlas,Sqoop,Flume,Kafka,Pig,Hive,HBase,Accumulo,Storm,Solr,Spark,Ranger,Knox,Ambari,
ZooKeeper,OozieandtheHadoopelephantlogoaretrademarksoftheApacheSoftwareFoundation.(http://www.apache.org/)
PrivacyPolicy(/agreements/privacypolicy/)|TermsofService(/agreements/termsofservice/)