安装mysql

1.下载mysql

http://ftp.nchu.edu.tw/Unix/Database/MySQL/Downloads/MySQL-5.5/mysql-5.5.32.tar.gz

2. 解压

tar zxvf mysql-5.5.32.tar.gz
cd mysql-5.5.32

3.安装

./configure --prefix=/usr/local/mysql5.5 --with-mysqld-user=mysql  --without-debug --localstatedir=/opt/workspace/mysql --with-charset=utf8 --with-collation=utf8_general_ci --with-extra-charsets=complex --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

详解：

./configure 
--prefix=/usr/local/mysql5.5 //MySQL安装目录
--with-mysqld-user=mysql //mysqld运行用户
--without-debug //调试模式（默认禁用）
--localstatedir=/opt/workspace/mysql //数据库存放目录
--with-charset=utf8 //使用UTF8格式 
--with-collation=utf8_general_ci //默认字符校对
--with-extra-charsets=complex //安装所有的扩展字符集，默认：all 
--enable-assembler //汇编x86的普通操作符，可以提高性能 
--enable-thread-safe-client //启用客户端安全线程 
--with-client-ldflags=-all-static //静态链接提高13%性能
--with-mysqld-ldflags=-all-static //静态链接提高13%性能 
--with-big-tables //启用大表 
--with-plugins=all //数据库插件csv,myisam,myisammrg,heap,innobase,archive,blackhole

--with-ssl=/usr //使用SSL加密 
--with-embedded-server //编译成embedded MySQL library (libmysqld.a), 
--enable-local-infile //允许从本地导入数据 
--with-plugins=innobase //数据库插件 
--with-plugins=partition //分表功能，将一个大表分割成多个小表 

#make && make install //编译然后安装

4.配置文件
在/etc目录下，添加my.cnf
安装好mysql后，在/usr/local/mysql5.5/share/mysql目录下，会有my-huge.cnf,  my-medinum.cnf, my-small.cnf

# cp /usr/local/mysql5.5/share/mysql/my-huge.cnf /etc/my.cnf

如果你的内存≤64M，则复制/usr/local/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/local/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/local/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.

5. 生成mysql用户数据库和表文件

/usr/local/mysql5.5/bin/mysql_install_db --user=mysql

会自动在localstatedir定义的路径生成文件

6.添加帐号

groupadd mysql
useradd -g mysql mysql

7.修改权限

chown -R mysql:mysql /opt/workspace/mysql
chmod 775 /opt/workspace/mysql

8.启动mysql

/usr/local/mysql5.5/bin/mysqld_safe &

9.添加到系统的启动目录

cp /usr/local/mysql5.5/share/mysql/mysql.server  /etc/init.d/mysql

/etc/init.d/mysql start
/etc/init.d/mysql stop
/etc/init.d/mysql restart

10.运行客户端

/usr/local/mysql5.5/bin/mysql

启动成功
查询数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.01 sec)

11.设置密码

/usr/local/mysql5.5/bin/mysqladmin -uroot password '360buy'

参看密码是否生效
/usr/local/mysql/bin/mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
进不去了，说明已经生效

12.使用密码登录

/usr/local/mysql5.5/bin/mysql -uroot -p

输入刚刚新建的密码，就可以进去了

13.修改my.cnf

[mysqld]
port            = 3306  
socket          = /tmp/mysql.sock  
datadir         = /opt/workspace/mysql

wait_timeout=864000  
interactive-timeout=864000  
max_connections=2048 

user=mysql
old_passwords=1
default-character-set=utf-8
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=/tmp/mysql.sock 
#default-character-set=utf8

14.登录客户端查看
mysql> SHOW VARIABLES LIKE '%character%';
+--------------------------+----------------------------------------+
| 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/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+
8 rows in set (0.00 sec)
除了system为utf8，其他最好为latin1，否则可能出现异常com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes ）

数据库引擎
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)

15.远程访问mysql

mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "root";
mysql> flush privileges;

第一句中"%"表示任何主机都可以远程登录到该服务器上访问。如果要限制只有某台机器可以访问，将其换成相应的IP即可，如：
GRANT ALL PRIVILEGES ON *.* TO root@"192.168.105.83" IDENTIFIED BY "root";
第二句表示从mysql数据库的grant表中重新加载权限数据。因为MySQL把权限都放在了cache中，所以在做完更改后需要重新加载。

评论

1 楼 云子健 2011-12-06  

我的邮箱：[e-mail]
我的问题是hive 在drop table时出现下面的错误！
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask


我的字符集有改过， global binlog_format也改过 但是还是会出现下面的问题；