因为需要hive需要mysql的DB的支持,又因为我们尽量需要做到mysql的容灾备份,所以我们需要安装2套mysql
mysql安装。一台master ,一台slave
增加用户
userdel mysq
groupadd mysql
useradd -g mysql
安装:
yum install libaio-devel 安装postfix
yum安装 yum install mysql-server mysql-devel
或者
sudo rpm -ivh mysql-libs-5.1.47-4.el6.x86_64
sudo rpm -ivh MySQL-client-5.5.25a-1.el6.x86_64
sudo rpm -ivh MySQL-devel-5.5.25a-1.el6.x86_64
卸载mysql
rpm -qa|grep -i mysql
rpm -ev xxx
停止mysql
sudo service mysqld stop
----------------------
启动mysql
sudo /sbin/chkconfig -add mysql //加入启动服务
sudo /sbin/chkconfig -del mysql
sudo service mysqld restart //重起mysql
sudo service mysqld stop //停止mysql
sudo service mysqld start
我们需要授权访问权限
登陆 mysql -uroot -p 新系统没有密码
修改添加用户和密码
update mysql.user set password=PASSWORD('hive') where user='root';
update mysql.user set password=PASSWORD('hive') where user='hive';
flush privileges;
查看mysql 系统表
use mysql;
select * from user;
需要登陆的client都需要有这个授权
grant all on *.* to hive@192.168.137.118 identified by 'hive';
sudo /usr/bin/mysql_install_db --user=mysql --defaults-file=/etc/my.cnf --datadir=/var/lib/mysql //检查mysql db是否正常
启动mysql sudo service restart
登陆 mysql -h192.168.137.118 -uroot -phive 和 mysql -h192.168.137.118 -uhive -phive
>quit;
mysql的字符集需要改变
SHOW VARIABLES LIKE 'character_set_%';
>set character_set_client =utf8;
>set character_set_connection=utf8;
>set character_set_database =utf8;
>set character_set_filesystem=utf8;
>set character_set_results =utf8;
>set character_set_server =utf8;
>set character_set_system =utf8;
创建hive 的database
create databases hive;
alter database hive character set utf8; //改变db的字符集
找一个hive的配置表验证一下:show create table hive.TBLS;
配置自己的数据目录
cp -r -a /var/lib/mysql /home/mysql/ //带权限copy数据文件目录到自定义目录下
因为目录权限的问题,mysql的数据目录需要放置在/home/mysql 下,mysql安装过程中应该会自动建立这个目录和用户。
修改mysql配置文件,将数据文件目录移动到自定义的目录
配置文件 sudo vim /etc/rc.d/init.d/mysqld
修改 get_mysql_option mysqld datadir "/home/mysql/datadir/mysql"
配置文件 sudo vim /usr/bin/mysqld_safe
修改
else
#DATADIR=/var/lib/mysql
DATADIR=/home/mysql/datadir/mysql
fi
/etc/my.cnf 是mysql的配置文件
安装好mysql后,在/usr/local/mysql/share/mysql目录下,会有my-huge.cnf, my-medinum.cnf, my-small.cnf
如果你的内存≤64M,则复制/usr/share/mysql/my-small.cnf为/etc/my.cnf
# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it's important that the mysqld daemon
# doesn't use much resources.
如果内存是128M,则复制/usr/share/mysql/my-medium.cnf为/etc/my.cnf
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
如果内存是512M,则复制/usr/share/mysql/my-large.cnf为/etc/my.cnf
# This is for a large system with memory = 512M where the system runs mainly
# MySQL.
如果内存是1-2G,则复制/usr/local/share/mysql/my-huge.cnf为/etc/my.cnf
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
[mysqld]
datadir=/home/mysql/datadir/mysql //数据文件目录需要配置成自定义
socket=/home/mysql/datadir/mysql/mysql.sock
[mysqld_safe]
log-error=/var/log/mysqld.log //log目录使用系统默认
pid-file=/var/run/mysqld/mysqld.pid
启动之后在建立连接
ln -s /home/mysql/datadir/mysql/mysql.sock /var/lib/mysql/mysql.sock
mysql工具验证mysql是否正常
/usr/bin/mysqld_safe
/usr/bin/mysqlcheck
sudo /usr/bin/mysql_install_db --user=mysql --defaults-file=/etc/my.cnf --datadir=/home/mysql/datadir/mysql //检查mysql db是否正常
启动mysql sudo service restart
登陆 mysql -h192.168.137.118 -uroot -phive 和 mysql -h192.168.137.118 -uhive -phive
-------------------------------
mysql 主备配置
请注意:master,slave 配置后,master read,write。slave read。
master配置
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=hive
binlog-ignore-db=mysql
下面每一步骤执行需要间隔3s以上
>grant replication slave on *.* to 'hive'@'192.168.137.237' identified by 'hive' //授权slave 的复制权限
> show master status;
slave 配置
[mysqld]
server-id=2
master-host=192.168.137.118
master-user=hive
master-password=hive
master-port=3306
master-connect-retry=60 //表示如果主服务器中断,从服务器重新连接的时间差
replicate-do-db=hive //同步的数据库,不写本行 表示 同步所有数据库
下面每一步骤执行需要间隔5s以上
>slave stop;
>change master to master_host='192.168.137.118',master_user='hive',master_password='hive',master_log_file='log.000003',master_log_pos=98;
>slave start;
>show slave status\G;
注意:有时候可能slave并没有完全同步master,导致slave的
Slave_SQL_Running: No
Seconds_Behind_Master: NULL
Last_Error: Error 'Unknown table 'test_rep'' on query. Default database: 'hive'. Query: 'drop table test_rep'
这个时候在master上执行 show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 188 | hive | mysql |
+------------------+----------+--------------+------------------+
在slave上
重新slave stop;
change master to master_host='192.168.137.118',master_user='hive',master_password='hive',master_log_file='mysql-bin.000005 ',master_log_pos=188;
或者 set global sql_slave_skip_counter=1; 采用哪个步骤看实验情况
slave start;
在 show slave status\G;
Slave_IO_Running: YES
Slave_SQL_Running: YES
Seconds_Behind_Master: 0
master,slave配置成功
验证例子:在master上执行
CREATE TABLE test_repl2 (
id bigint(20) NOT NULL,
name varchar(767)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into test_repl2 values(1,'xyz');
在slave 观察是否表被同步
在master,slave成功配置后,这里需要将同步的sql 放在后台运行,需要写sql shell 。
** master,slave 不同步的错误处理 **
sudo /usr/bin/mysqlbinlog --start-position=353 ./mysql-bin.000008
如果找不到问题,导出到文件分析
sudo /usr/bin/mysqlbinlog --start-position=4 ./mysql-bin.000008 > /home/hadoop/test.txt
-----------------------
# at 189
#120719 15:34:05 server id 1 end_log_pos 270 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1342683245/*!*/;
drop database hive
/*!*/;
# at 270
#120719 15:55:12 server id 1 end_log_pos 353 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1342684512/*!*/;
create database hive
-----------------------
从日志里面找出没有错误的部分,我选择了 270
slave上执行
由于我的slave上有hive database,所以我首先drop database hive;
slave stop;
change master to master_host='192.168.137.118',master_user='hive',master_password='hive',master_log_file='mysql-bin.000008 ',master_log_pos=270;
slave start;
--
hive安装
cp hive-0.9.0/conf/hive-env.sh.template hive-0.9.0/conf/hive-env.sh
chmod +x hive-0.9.0/conf/hive-env.sh
修改
export JAVA_HOME=/usr/java/jdk1.6.0_26
export JRE_HOME=/usr/java/jdk1.6.0_26/jre
export HADOOP_HOME=/home/hadoop/hadoop-0.20.1-dev-facebook-namenode
#export HADOOP_HOME=/home/fotu/smp/hadoop-0.20.2-namenode
export HADOOP_CLASSPATH=$HADOOP_HOME/lib
export PATH=.:$HADOOP_HOME/bin:$PATH
export CLASSPATH=.:$JAVA_HOME/lib:$JRE_HOME/lib:$CLASSPATH
export PATH=.:$JAVA_HOME/bin:$JRE_HOME/bin:$PATH
export HIVE_HOME=/home/hadoop/hive-0.9.0
export PATH=$HIVE_HOME/bin:$PATH
export HADOOP_HEAPSIZE=1024
export HIVE_CONF_DIR=/home/hadoop/hive-0.9.0/conf
cp hive-default.xml.template hive-site.xml
修改
<property>
<name>javax.jdo.option.ConnectionURL</name>
<!--<value>jdbc:derby:;databaseName=metastore_db;create=true</value>-->
<value>jdbc:mysql://192.168.137.118:3306/hive?useUnicode=true&characterEncoding=UTF-8&createDatabaseIfNotExist=true</value>
<description>JDBC connect string for a JDBC metastore,update by wzt,这里需要配置master mysql</description>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<!--<value>org.apache.derby.jdbc.EmbeddedDriver</value>-->
<value>com.mysql.jdbc.Driver</value>
<description>Driver class name for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<!--<value>APP</value>-->
<value>hive</value>
<description>username to use against metastore database,update by wzt</description>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<!--<value>mine</value>-->
<value>hive</value>
<description>password to use against metastore database,update by wzt</description>
</property>
运行hive
hive-0.9.0\bin\hive
>show tables; //完成hive初始化
---------------------------------------------------
在确认mysql master ,slave正常工作后
启动hive 发生错误
hive> show tables;
FAILED: Error in metadata: javax.jdo.JDOException: Couldnt obtain a new sequence (unique id) : Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
这是由于 READ-COMMITTED需要把bin-log以mixed方式来记录
mysql> show global variables like 'binlog_format%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
master ,slave 上都执行 set global binlog_format='MIXED';
同时在 /etc/my.cnf也需要
[mysqld]
binlog_format=mixed
-----
启动mysql innodb 引擎报错 ,因为hive的表使用innodb ,所以本错误需要排除
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 67108864 bytes!
120720 9:30:46 [ERROR] Plugin 'InnoDB' init function returned error.
120720 9:30:46 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
这个错误是因为修改了产生的,因为默认<64M
[mysqld]
innodb_log_file_size = 64M
ib_logfile0,ib_logfile0是mysql启动时产生的,是innode的undo log和redo log,通过这2个错误日志可以恢复mysql innodb 表数据,在实验情况下停止mysql,删除这2个文件,重启mysql 就可以了
[mysqld]
innodb_log_files_in_group=2 //控制 ib_logfiile 的个数,循环写
----
mysql启动报错
[ERROR] /usr/libexec/mysqld: Can't create/write to file '/var/run/mysqld/mysqld.pid' (Errcode: 13)
这个错误是权限引起的,因为在有mysql 用户后,默认是mysql用户启动的 所以 /var/run/mysqd 目录改为 mysql 的属主,数据目录 chown -R mysql.mysql /home/mysql/datadir/mysql
mysql安装。一台master ,一台slave
增加用户
userdel mysq
groupadd mysql
useradd -g mysql
安装:
yum install libaio-devel 安装postfix
yum安装 yum install mysql-server mysql-devel
或者
sudo rpm -ivh mysql-libs-5.1.47-4.el6.x86_64
sudo rpm -ivh MySQL-client-5.5.25a-1.el6.x86_64
sudo rpm -ivh MySQL-devel-5.5.25a-1.el6.x86_64
卸载mysql
rpm -qa|grep -i mysql
rpm -ev xxx
停止mysql
sudo service mysqld stop
----------------------
启动mysql
sudo /sbin/chkconfig -add mysql //加入启动服务
sudo /sbin/chkconfig -del mysql
sudo service mysqld restart //重起mysql
sudo service mysqld stop //停止mysql
sudo service mysqld start
我们需要授权访问权限
登陆 mysql -uroot -p 新系统没有密码
修改添加用户和密码
update mysql.user set password=PASSWORD('hive') where user='root';
update mysql.user set password=PASSWORD('hive') where user='hive';
flush privileges;
查看mysql 系统表
use mysql;
select * from user;
需要登陆的client都需要有这个授权
grant all on *.* to hive@192.168.137.118 identified by 'hive';
sudo /usr/bin/mysql_install_db --user=mysql --defaults-file=/etc/my.cnf --datadir=/var/lib/mysql //检查mysql db是否正常
启动mysql sudo service restart
登陆 mysql -h192.168.137.118 -uroot -phive 和 mysql -h192.168.137.118 -uhive -phive
>quit;
mysql的字符集需要改变
SHOW VARIABLES LIKE 'character_set_%';
>set character_set_client =utf8;
>set character_set_connection=utf8;
>set character_set_database =utf8;
>set character_set_filesystem=utf8;
>set character_set_results =utf8;
>set character_set_server =utf8;
>set character_set_system =utf8;
创建hive 的database
create databases hive;
alter database hive character set utf8; //改变db的字符集
找一个hive的配置表验证一下:show create table hive.TBLS;
配置自己的数据目录
cp -r -a /var/lib/mysql /home/mysql/ //带权限copy数据文件目录到自定义目录下
因为目录权限的问题,mysql的数据目录需要放置在/home/mysql 下,mysql安装过程中应该会自动建立这个目录和用户。
修改mysql配置文件,将数据文件目录移动到自定义的目录
配置文件 sudo vim /etc/rc.d/init.d/mysqld
修改 get_mysql_option mysqld datadir "/home/mysql/datadir/mysql"
配置文件 sudo vim /usr/bin/mysqld_safe
修改
else
#DATADIR=/var/lib/mysql
DATADIR=/home/mysql/datadir/mysql
fi
/etc/my.cnf 是mysql的配置文件
安装好mysql后,在/usr/local/mysql/share/mysql目录下,会有my-huge.cnf, my-medinum.cnf, my-small.cnf
如果你的内存≤64M,则复制/usr/share/mysql/my-small.cnf为/etc/my.cnf
# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it's important that the mysqld daemon
# doesn't use much resources.
如果内存是128M,则复制/usr/share/mysql/my-medium.cnf为/etc/my.cnf
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
如果内存是512M,则复制/usr/share/mysql/my-large.cnf为/etc/my.cnf
# This is for a large system with memory = 512M where the system runs mainly
# MySQL.
如果内存是1-2G,则复制/usr/local/share/mysql/my-huge.cnf为/etc/my.cnf
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
[mysqld]
datadir=/home/mysql/datadir/mysql //数据文件目录需要配置成自定义
socket=/home/mysql/datadir/mysql/mysql.sock
[mysqld_safe]
log-error=/var/log/mysqld.log //log目录使用系统默认
pid-file=/var/run/mysqld/mysqld.pid
启动之后在建立连接
ln -s /home/mysql/datadir/mysql/mysql.sock /var/lib/mysql/mysql.sock
mysql工具验证mysql是否正常
/usr/bin/mysqld_safe
/usr/bin/mysqlcheck
sudo /usr/bin/mysql_install_db --user=mysql --defaults-file=/etc/my.cnf --datadir=/home/mysql/datadir/mysql //检查mysql db是否正常
启动mysql sudo service restart
登陆 mysql -h192.168.137.118 -uroot -phive 和 mysql -h192.168.137.118 -uhive -phive
-------------------------------
mysql 主备配置
请注意:master,slave 配置后,master read,write。slave read。
master配置
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=hive
binlog-ignore-db=mysql
下面每一步骤执行需要间隔3s以上
>grant replication slave on *.* to 'hive'@'192.168.137.237' identified by 'hive' //授权slave 的复制权限
> show master status;
slave 配置
[mysqld]
server-id=2
master-host=192.168.137.118
master-user=hive
master-password=hive
master-port=3306
master-connect-retry=60 //表示如果主服务器中断,从服务器重新连接的时间差
replicate-do-db=hive //同步的数据库,不写本行 表示 同步所有数据库
下面每一步骤执行需要间隔5s以上
>slave stop;
>change master to master_host='192.168.137.118',master_user='hive',master_password='hive',master_log_file='log.000003',master_log_pos=98;
>slave start;
>show slave status\G;
注意:有时候可能slave并没有完全同步master,导致slave的
Slave_SQL_Running: No
Seconds_Behind_Master: NULL
Last_Error: Error 'Unknown table 'test_rep'' on query. Default database: 'hive'. Query: 'drop table test_rep'
这个时候在master上执行 show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 188 | hive | mysql |
+------------------+----------+--------------+------------------+
在slave上
重新slave stop;
change master to master_host='192.168.137.118',master_user='hive',master_password='hive',master_log_file='mysql-bin.000005 ',master_log_pos=188;
或者 set global sql_slave_skip_counter=1; 采用哪个步骤看实验情况
slave start;
在 show slave status\G;
Slave_IO_Running: YES
Slave_SQL_Running: YES
Seconds_Behind_Master: 0
master,slave配置成功
验证例子:在master上执行
CREATE TABLE test_repl2 (
id bigint(20) NOT NULL,
name varchar(767)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into test_repl2 values(1,'xyz');
在slave 观察是否表被同步
在master,slave成功配置后,这里需要将同步的sql 放在后台运行,需要写sql shell 。
** master,slave 不同步的错误处理 **
sudo /usr/bin/mysqlbinlog --start-position=353 ./mysql-bin.000008
如果找不到问题,导出到文件分析
sudo /usr/bin/mysqlbinlog --start-position=4 ./mysql-bin.000008 > /home/hadoop/test.txt
-----------------------
# at 189
#120719 15:34:05 server id 1 end_log_pos 270 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1342683245/*!*/;
drop database hive
/*!*/;
# at 270
#120719 15:55:12 server id 1 end_log_pos 353 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1342684512/*!*/;
create database hive
-----------------------
从日志里面找出没有错误的部分,我选择了 270
slave上执行
由于我的slave上有hive database,所以我首先drop database hive;
slave stop;
change master to master_host='192.168.137.118',master_user='hive',master_password='hive',master_log_file='mysql-bin.000008 ',master_log_pos=270;
slave start;
--
hive安装
cp hive-0.9.0/conf/hive-env.sh.template hive-0.9.0/conf/hive-env.sh
chmod +x hive-0.9.0/conf/hive-env.sh
修改
export JAVA_HOME=/usr/java/jdk1.6.0_26
export JRE_HOME=/usr/java/jdk1.6.0_26/jre
export HADOOP_HOME=/home/hadoop/hadoop-0.20.1-dev-facebook-namenode
#export HADOOP_HOME=/home/fotu/smp/hadoop-0.20.2-namenode
export HADOOP_CLASSPATH=$HADOOP_HOME/lib
export PATH=.:$HADOOP_HOME/bin:$PATH
export CLASSPATH=.:$JAVA_HOME/lib:$JRE_HOME/lib:$CLASSPATH
export PATH=.:$JAVA_HOME/bin:$JRE_HOME/bin:$PATH
export HIVE_HOME=/home/hadoop/hive-0.9.0
export PATH=$HIVE_HOME/bin:$PATH
export HADOOP_HEAPSIZE=1024
export HIVE_CONF_DIR=/home/hadoop/hive-0.9.0/conf
cp hive-default.xml.template hive-site.xml
修改
<property>
<name>javax.jdo.option.ConnectionURL</name>
<!--<value>jdbc:derby:;databaseName=metastore_db;create=true</value>-->
<value>jdbc:mysql://192.168.137.118:3306/hive?useUnicode=true&characterEncoding=UTF-8&createDatabaseIfNotExist=true</value>
<description>JDBC connect string for a JDBC metastore,update by wzt,这里需要配置master mysql</description>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<!--<value>org.apache.derby.jdbc.EmbeddedDriver</value>-->
<value>com.mysql.jdbc.Driver</value>
<description>Driver class name for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<!--<value>APP</value>-->
<value>hive</value>
<description>username to use against metastore database,update by wzt</description>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<!--<value>mine</value>-->
<value>hive</value>
<description>password to use against metastore database,update by wzt</description>
</property>
运行hive
hive-0.9.0\bin\hive
>show tables; //完成hive初始化
---------------------------------------------------
在确认mysql master ,slave正常工作后
启动hive 发生错误
hive> show tables;
FAILED: Error in metadata: javax.jdo.JDOException: Couldnt obtain a new sequence (unique id) : Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
这是由于 READ-COMMITTED需要把bin-log以mixed方式来记录
mysql> show global variables like 'binlog_format%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
master ,slave 上都执行 set global binlog_format='MIXED';
同时在 /etc/my.cnf也需要
[mysqld]
binlog_format=mixed
-----
启动mysql innodb 引擎报错 ,因为hive的表使用innodb ,所以本错误需要排除
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 67108864 bytes!
120720 9:30:46 [ERROR] Plugin 'InnoDB' init function returned error.
120720 9:30:46 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
这个错误是因为修改了产生的,因为默认<64M
[mysqld]
innodb_log_file_size = 64M
ib_logfile0,ib_logfile0是mysql启动时产生的,是innode的undo log和redo log,通过这2个错误日志可以恢复mysql innodb 表数据,在实验情况下停止mysql,删除这2个文件,重启mysql 就可以了
[mysqld]
innodb_log_files_in_group=2 //控制 ib_logfiile 的个数,循环写
----
mysql启动报错
[ERROR] /usr/libexec/mysqld: Can't create/write to file '/var/run/mysqld/mysqld.pid' (Errcode: 13)
这个错误是权限引起的,因为在有mysql 用户后,默认是mysql用户启动的 所以 /var/run/mysqd 目录改为 mysql 的属主,数据目录 chown -R mysql.mysql /home/mysql/datadir/mysql