master slave互为主从
思路:hive客户端及元数据库 mysql安装在一台服务器上(master),为防止单点故障,选取slave作为备份节点,作为小spark集群的master节点,安装hive客户端及元数据库
master节点与slave节点均安装mysql作为各自hive客户端的元数据库,两mysql互为主从节点,互为备份节点。
配置过程如下:
- mysql命令行界面,在两台机器上添加一个用于从机访问的帐号, 赋予REPLICATION SLAVE权限.为hive用户赋予任何数据库中任何表上的REPLICATION SLAVE权限, 此用户可以在网络任意位置访问,
例子
mysql>GRANT REPLICATION SLAVE ON *.* TO 'hive'@'slave' IDENTIFIED BY '123456';
mysql>flush privileges;
master允许slave访问:
GRANT REPLICATION SLAVE ON *.* TO 'hive'@'slave' IDENTIFIED BY '123456';
flush privileges;
slave允许master访问:
GRANT REPLICATION SLAVE ON *.* TO 'hive'@'master' IDENTIFIED BY '123456';
flush privileges;
- 配置文件
vim /etc/mysql/mysql.conf.d/mysqld.cnf
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
打开这两行的注释, 注意这里的server-id是服务器编号,两台服务器上的值要设置的不一样. 分别修改为1和2
- 3、重启mysql,配置修改生效:
sudo /etc/init.d/mysql restart 或 service mysql restart
- 将两台数据库服务器的mysql都锁定
在mysql命令模式下:
加只读锁 FLUSH TABLES WITH READ LOCK;
解锁: UNLOCK TABLES
SHOW MASTER STATUS;
查看 Master-Server, binlog File 文件名称和 Position值位置 并且记下来
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 2021 | test | mysql | |
+------------------+----------+--------------+------------------+-------------------+
mysql命令行配置并执行以下命令
master机器
CHANGE MASTER TO
MASTER_HOST='slave',
MASTER_USER='hive',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=14531613;
slave机器
CHANGE MASTER TO
MASTER_HOST='master',
MASTER_USER='hive',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=1721;
- 开启同步
MySQL命令行执行START SLAVE;
查看复制状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: slave
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: datanode12-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
能看到最后两个yes即成功
此时两mysql不能写,需要解锁,mysql命令行执行
UNLOCK TABLES; 就可以了。