mysql主备切换导致hive主键冲突问题

问题描述：

问题解决：
hive主键冲突，增加相关值的大小，跳过冲突解决：
update SEQUENCE_TABLE set NEXT_VAL=‘71000000’ where SEQUENCE_NAME=‘org.apache.hadoop.hive.metastore.model.MPartitionColumnStatistics’;
update SEQUENCE_TABLE set NEXT_VAL=‘43100825’ where SEQUENCE_NAME=‘org.apache.hadoop.hive.metastore.model.MSerDeInfo’;
update SEQUENCE_TABLE set NEXT_VAL=‘43100825’ where SEQUENCE_NAME=‘org.apache.hadoop.hive.metastore.model.MStorageDescriptor’;
update SEQUENCE_TABLE set NEXT_VAL=‘43100825’ where SEQUENCE_NAME=‘org.apache.hadoop.hive.metastore.model.MPartition’;
分析原因：
由于mysql，ranger，kdc 做HA共用vip导致，其他组件切换影响mysql数据插入。
mysql重新HA恢复：
1、两库 stop slave;
2、主库锁表 flush tables with read lock; 锁定为只读状态
3、主库备份：
导出所有数据库
mysqldump -uroot -p2D0snAY6GFCA6Pw2 --all-databases>all100.sql
导出db1、db2两个数据库的所有数据
mysqldump -uroot -p2D0snAY6GFCA6Pw2 --set-gtid-purged=OFF --databases ambari hive ranger --triggers --routines --events > dpall.sql
4、导入备库：
导入：source /home/test/database.sql
5、备库同步主库
查看10.209.24.100 mysql master 状态
mysql> show master status;
±-----------------±---------±-------------------±--------------------------------------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------------±--------------------------------------------±------------------+
| mysql-bin.000002 | 3087 | ambari,hive,ranger | mysql,information_schema,performance_schema | |
±-----------------±---------±-------------------±--------------------------------------------±------------------+
按照10.209.24.100 master的状态设置 slave 10.209.24.101 mysql–
change master to
master_host=‘10.209.24.100’,
master_user=‘replication’,
master_password=‘password’,
master_log_file=‘mysql-bin.000092’,
master_log_pos=924645737; #对端状态显示的值
start slave;
6、备库锁表 flush tables with read lock; 锁定为只读状态
7、keepalived切换vip
8、主库解锁：unlock tables; 同步备库
查看10.209.24.101 mysql master 状态(MySQL服务器二进制文件名与位置)
mysql> show master status;
±-----------------±---------±-------------------±--------------------------------------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------------±--------------------------------------------±------------------+
| mysql-bin.000002 | 3087 | ambari,hive,ranger | mysql,information_schema,performance_schema | |
±-----------------±---------±-------------------±--------------------------------------------±------------------+
slave上指定master同步信息(告知二进制文件名与位置)
—按照10.209.24.101 master的状态设置 slave 10.209.24.100 mysql–
change master to
master_host=‘10.209.24.101’,
master_user=‘replication’,
master_password=‘password’,
master_log_file=‘mysql-bin.000092’,
master_log_pos=162205334; #对端状态显示的值
start slave;
9、查看同步情况
SHOW SLAVE STATUS\G
10、备库解锁 unlock tables;