Mysql-5.5.13安装配置

  

由于Mysql的开源与免费，在PRD环境能够很稳定的运行，性能也很好，使其获得了很大的应用；同时mysql也在不间断的推出新的版本；
    以下为Mysql-5.5.13的安装配置；
    环境：
    OS Version：Red Hat Enterprise Linux Server release 6.0 (Santiago)
    Kernel Version：2.6.32-71.el6.x86_64
    Mysql Version：mysql-5.5.13.tar.gz

安装环境部署

1. 系统依赖包检查

   gcc gcc-c++ autoconf automake zlib-devel libxml2-devel ncurses-devel libmcrypt* libtool*(libtool-ltdl-devel*) cmake
     需要安装以上系统依赖包才能顺利的完成mysql的安装；camke包为编译mysql使用（不是经典的confiure了）

2. mysql环境配置

•  mysql用户创建

# id mysql

如果没有结果，新建mysql账号

# useradd -u xxx -s /sbin/nologin mysql （限制mysql用户远程登录系统的权限）

# id mysql

uid=503(mysql) gid=503(mysql) 组=503(mysql)

确定mysql用户已经建立OK

• 创建data数据存放目录

# mkdir /mysqldata/data

# chown –R mysql:mysql /mysqldata

3. 编译安装mysql

• 获取mysql源码包

通过以下路径下载mysql-5.5.13.tar.gz，也可在官网网站进行下载

• 编译安装

# tar zxvf mysql-5.5.13.tar.gz

# cd mysql-5.5.13

# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS:STRING=utf8,gbk -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_USER=mysql -DMYSQL_TCP_PORT=3306 -DMYSQL_DATADIR=/mysqldata/data

# make

# make install

编译安装完成以后，检查mysql安装是否已经安装成功；

在编译安装的过程中，可能会出现一些异常，主要的原因还是系统的依赖包没有安装上去，编译安装mysql之前，一定要把上述提到的包全部安装；

比如这个错误：

mysqld.cc: In function ‘void handle_connections_sockets()’:

主要是没有安装libmcrypt相关的包

直接使用yum install libmcrypt* -y安装完成后，在重新编译

在编译出现异常，在重新编译的时候，需要先删除mysql目录下的CMakeCache.txt这个文件，然后再进行编译！！！

# ll /usr/local/mysql

drwxr-xr-x.  2 root root  4096 11月 16 10:08 bin

-rw-r--r--.  1 root root 17987  5月 11 2011 COPYING

drwxr-xr-x.  4 root root  4096 11月 16 10:08 data

drwxr-xr-x.  2 root root  4096 11月 16 10:08 docs

drwxr-xr-x.  3 root root  4096 11月 16 10:08 include

-rw-r--r--.  1 root root  7370  5月 11 2011 INSTALL-BINARY

drwxr-xr-x.  3 root root  4096 11月 16 10:08 lib

drwxr-xr-x.  4 root root  4096 11月 16 10:08 man

drwxr-xr-x. 10 root root  4096 11月 16 10:08 mysql-test

-rw-r--r--.  1 root root  2552  5月 11 2011 README

drwxr-xr-x.  2 root root  4096 11月 16 10:08 scripts

drwxr-xr-x. 27 root root  4096 11月 16 10:08 share

drwxr-xr-x.  4 root root  4096 11月 16 10:08 sql-bench

drwxr-xr-x.  2 root root  4096 11月 16 10:08 support-files

4. 初始化mysql

# cd /usr/local/mysql/scripts

# ll mysql_install_db

-rwxr-xr-x. 1 root root 14453 11月 16 09:51 mysql_install_db

确认这个脚本有执行权限；

如果没有，

# chmod +x mysql_install_db

# ./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/mysqldata/data/

--user=user_name     The login username to use for running mysqld.（由于以后我们是通过mysql用户去启动mysql；

5. 修改配置文件

# cp /usr/local/mysql/support-files/ my-innodb-heavy-4G.cnf /etc/my.cnf

并修改对应的参数，满足需求；

以下是一些配置参考：

[client]

port            = 3306

socket          = /tmp/mysql.sock

[mysqld]

port            = 3306

socket          = /tmp/mysql.sock

datadir = /mysqldata/data （这个地方的datadir一定要与初始化数据库的datadir一致）

back_log = 50

max_connections = 100

max_connect_errors = 10

table_open_cache = 2048

max_allowed_packet = 16M

binlog_cache_size = 1M

max_heap_table_size = 64M

read_buffer_size = 2M

read_rnd_buffer_size = 16M

sort_buffer_size = 8M

join_buffer_size = 8M

thread_cache_size = 8

thread_concurrency = 8

query_cache_size = 64M

query_cache_limit = 2M

ft_min_word_len = 4

default-storage-engine = INNODB

thread_stack = 192K

transaction_isolation = REPEATABLE-READ

tmp_table_size = 64M

log-bin=/logdata/binlog

binlog_format=mixed

slow_query_log

long_query_time = 2

server-id = 1

key_buffer_size = 32M

bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

myisam_recover

innodb_additional_mem_pool_size = 16M

innodb_buffer_pool_size = 1G

innodb_data_file_path = ibdata1:100M:autoextend

innodb_data_home_dir = /mysqldata/innodb/data

innodb_write_io_threads = 8

innodb_read_io_threads = 8

innodb_thread_concurrency = 16

innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 8M

innodb_log_file_size = 256M

innodb_log_files_in_group = 3

innodb_log_group_home_dir = /mysqldata/innodb/log

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 512M

sort_buffer_size = 512M

read_buffer = 8M

write_buffer = 8M

[mysqlhotcopy]

interactive-timeout

[mysqld_safe]

open-files-limit = 8192

6. 启动测试mysql

# /usr/local/mysql/bin/mysqld_safe &

# 检查日志查看数据库是否成功，第一次运行的日志大致如下：

111116 13:50:38 mysqld_safe Starting mysqld daemon with databases from /mysqldata/data

111116 13:50:38 InnoDB: The InnoDB memory heap is disabled

111116 13:50:38 InnoDB: Mutexes and rw_locks use GCC atomic builtins

111116 13:50:38 InnoDB: Compressed tables use zlib 1.2.3

111116 13:50:38 InnoDB: Initializing buffer pool, size = 1.0G

111116 13:50:38 InnoDB: Completed initialization of buffer pool

InnoDB: The first specified data file /mysqldata/innodb/data/ibdata1 did not exist:

InnoDB: a new database to be created!

111116 13:50:38  InnoDB: Setting file /mysqldata/innodb/data/ibdata1 size to 100MB

InnoDB: Database physically writes the file full: wait...

111116 13:50:38  InnoDB: Log file /mysqldata/innodb/log/ib_logfile0 did not exist: new to be created

InnoDB: Setting log file /mysqldata/innodb/log/ib_logfile0 size to 256 MB

InnoDB: Database physically writes the file full: wait...

InnoDB: Progress in MB: 100 200

111116 13:50:41  InnoDB: Log file /mysqldata/innodb/log/ib_logfile1 did not exist: new to be created

InnoDB: Setting log file /mysqldata/innodb/log/ib_logfile1 size to 256 MB

InnoDB: Database physically writes the file full: wait...

InnoDB: Progress in MB: 100 200

111116 13:50:43  InnoDB: Log file /mysqldata/innodb/log/ib_logfile2 did not exist: new to be created

InnoDB: Setting log file /mysqldata/innodb/log/ib_logfile2 size to 256 MB

InnoDB: Database physically writes the file full: wait...

InnoDB: Progress in MB: 100 200

InnoDB: Doublewrite buffer not found: creating new

InnoDB: Doublewrite buffer created

InnoDB: 127 rollback segment(s) active.

InnoDB: Creating foreign key constraint system tables

InnoDB: Foreign key constraint system tables created

111116 13:50:46  InnoDB: Waiting for the background threads to start

111116 13:50:47 InnoDB: 1.1.7 started; log sequence number 0

111116 13:50:48 [Note] Event Scheduler: Loaded 0 events

111116 13:50:48 [Note] /usr/local/mysql/bin/mysqld: ready for connections.

Version: '5.5.13-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

          红色部分表示mysql已经启动ok了，3306的端口也已经处于监听状态；

          这时通过ps 查看进程，应该就能看到mysql的进程了！

          # ps -ef | grep mysql | grep –v grep

          # ps -ef | grep mysql | grep -v grep

root     22777 19477  0 13:50 pts/4    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe

mysql    23426 22777  0 13:50 pts/4    00:00:04 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mysqldata/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/mysqldata/data/test-file01.err --open-files-limit=8192 --pid-file=/mysqldata/data/test-file01.pid --socket=/tmp/mysql.sock --port=3306

 

MYSQL安全设置

• 为root帐户设置初始密码

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

或者限制只能通过本机登陆

# /usr/local/mysql/bin/mysqladmin -u root -h ‘hostname’ password 'new-password'

• 删除本机匿名连接的空密码帐号

本机登录mysql

mysql>use mysql; //选择默认数据库mysql

mysql>update user set password=’root123’ where user = '127.0.0.1';

mysql>delete from user where password="";//不允许root密码为空

mysql>flush privileges;

• 容许root用户是远程登录

对于root账号，如果考虑安全应该新建其他账号用于远程登录，root账号可以不必开启远程登录。不过对于一般使用，没有太多安全需求，允许root用户远程登录可以方便管理，毕竟使用专用管理软件的图形界面在操作方面要方便的多。