Hive用MySQL做元数据保存的数据库时，mySQL设置

来自：http://blog.c114.net/html/71/482871-63880.html

安装MySQL：

# tar -zxvf mysql-5.1.37.tar.gz -C /usr/local

# cd /usr/local/mysql-5.1.37/

# ./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data/ --without-debug --with-charset=utf8 --with-extra-charsets=all --enable-assembler --with-pthread --enable-thread-safe-client --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --with-big-tables --with-plugins=all

# make && make install

# cp ./support-files/mysql.server /etc/init.d/mysql

# groupadd mysql

# useradd -g mysql mysql

# chmod 777 /etc/init.d/mysql

# /usr/local/mysql/bin/mysql_install_db --user=mysql

或cp -R ./win/data/mysql /usr/local/mysql/data/

# chown -R mysql:mysql /usr/local/mysql/data/

# chmod 777 /usr/local/mysql/data/

# vi /etc/my.cnf

[mysqld]

datadir=/usr/local/mysql/data/

socket=/usr/local/mysql/data/mysql.sock

user=mysql

old_passwords=1

default-character-set=latin1

skip-character-set-client-handshake

wait_timeout=864000

interactive-timeout=864000

max_connections=2000

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[client]

socket=/usr/local/mysql/data/mysql.sock

#default-character-set=utf8

# service mysql start

# /usr/local/mysql/bin/mysqladmin -u root -p password 'xxxxxx'

# mysql -uroot -p

mysql> SHOW VARIABLES LIKE '%character%';  （除了system为utf8，其他最好为latin1，否则可能出现异常com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes ）
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> show engines; （最好有InnoDB模式支持）
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)