Chinaunix首页 | 论坛 | 博客
  • 博客访问: 560180
  • 博文数量: 36
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 1749
  • 用 户 组: 普通用户
  • 注册时间: 2013-08-20 16:13
个人简介

中国科学院大学计算机硕士,曾在新浪爱彩数据库组带DBA团队,现居新加坡。wx: lihui_dba

文章分类

全部博文(36)

文章存档

2020年(2)

2019年(3)

2017年(7)

2016年(1)

2015年(7)

2014年(11)

2013年(5)

分类: Mysql/postgreSQL

2014-11-04 16:59:03

0.安全提示

 因root账号启动mysql数据库存在严重安全隐患,请在启动时使用mysql账号! 

 1.源码方式安装mysql5.5 GA

创建组和用户:

[root@mysqldb2 ~]# groupadd mysql 
[root@mysqldb2 ~]# useradd -g mysql mysql

解压缩安装包:

[root@mysqldb2 ~]# cd /home/mysql/Desktop 
[root@mysqldb2 Desktop]# tar xvfz mysql-5.5.24.tar.gz 
[root@mysqldb2 Desktop]# cd mysql-5.5.24

检查依赖的软件包,如果缺少相关的软件包,可通过yum install或rpm安装:

cmake gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* bison 
rpm -qa |grep 
需要root权限执行

进入mysql安装包目录,执行cmake并加载适当参数:

root>#cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql/ -DWITH_SERVER_SUFFIX=lihui_edition -DENABLED_LOCAL_INFILE=1 -DDEFAULT_CHARSET=utf8 
-DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DWITH_READLINE=1 -DWITH_SSL=yes -DWITH_EMBEDDED_SERVER=1 -DWITH_PTHREAD=1 
-DMYSQL_USER=mysql -DWITH_MYSQLD_LDFLAGS=-all-static -DWITH_INNOBASE_STORAGE_ENGINE=1 > /home/mysql/mysql_setuplogs_cmake.log 2>&1

MySQL 从5.5版本开始,通过./configure进行编译配置方式已经被取消,取而代之的是cmake 工具。

我相信大多数人都已经习惯了之前的configure方式,并且所使用的参数也是比较个性化的,换成cmake之后,这一方面会带来不少的麻烦。

还好,MySQL的官方网站提供了二者的参数对照表,我们可以尽可能的保留之前的参数,来编译配置新的MySQL 版本。

configure 与cmake 参数对照指南:

http://www.blogjava.net/kelly859/archive/2012/09/04/387005.html

提示:如果编译不成功,重新编译需要删除mysql安装包目录下的CMakeCache.txt文件

root>#rm CMakeCache.txt

继续执行编译:

root>#make > /home/mysql/mysql_setuplogs_make.log 2>&1
root>#$make install > /home/mysql/mysql_setuplogs_makeinstall.log 2>&1

PS:make时注意新开一个会话,用tail -200f /home/mysql/mysql_setuplogs_make.log查看编译日志,审查有无报错。但下述警告可忽略:

警告:提领类型双关的指针将破坏强重叠规则

修改相关路径权限:

root># cd /usr/local/ 
root># chown -R mysql:mysql mysql 

复制程序文件到/usr/bin目录下,方便调用:

[root@mysqldb2 local]# cp /usr/local/mysql/bin/mysql* /usr/bin/ 

至此,MySQL数据库软件安装完全,此时已可使用mysql命令行工具连接其它MySQL数据库服务器了。

 2.创建数据库

创建目录并修改权限:

[root@mysqldb2 local]# cd /data0 
[root@mysqldb2 data]# mkdir mysqldata 
[root@mysqldb2 data]# cd mysqldata 
[root@mysqldb2 mysqldata]# mkdir 3306 
[root@mysqldb2 mysqldata]# cd 3306 
[root@mysqldb2 3306]# mkdir data binlog tmp innodb_ts innodb_log 
[root@mysqldb2 3306]# cd /data0
[root@mysqldb2 data]# chown -R mysql:mysql mysqldata

创建数据库:

[root@mysqldb2 local]# cd /usr/local/mysql/ 
[root@mysqldb2 mysql]# scripts/mysql_install_db --user=mysql --datadir=/data0/mysqldata/3306/data

Installing MySQL system tables... OK Filling help tables... OK

To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands:

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

Alternatively you can run: /usr/local/mysql/bin/mysql_secure_installation

which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with: cd /usr/local/mysql ; /usr/local/mysql/bin/mysqld_safe &

You can test the MySQL daemon with  cd /usr/local/mysql/mysql-test ; perl 

Please report any problems with the /usr/local/mysql/scripts/mysqlbug script!

编辑和修改my.cnf配置文件:

[root@mysqldb2 mysql]# vi /etc/my.cnf

[client]

port = 3306

socket = /data/mysqldata/3306/mysql.sock

default-character-set = utf8

#Here follows entries for some specific programs
# The MySQL server

[mysqld]

port = 3306

user = mysql

socket = /data/mysqldata/3306/mysql.sock

pid-file = /data/mysqldata/3306/mysql.pid

basedir = /usr/local/mysql

datadir = /data/mysqldata/3306/data

tmpdir = /data/mysqldata/3306/tmp

open_files_limit = 10240

server-id = 303306

lower_case_table_names = 1

character_set_server = utf8

skip-name-resolve

max_connections = 500

max_connect_errors = 100000

max_allowed_packet = 512M

max_heap_table_size = 1024M

max_length_for_sort_data = 4096

back_log=100

interactive_timeout = 600

wait_timeout = 600

default_storage_engine = InnoDB

net_buffer_length = 8K

sort_buffer_size = 2M

join_buffer_size = 4M

read_buffer_size = 2M

read_rnd_buffer_size = 16M

query_cache_size = 128M

query_cache_limit = 2M

query_cache_min_res_unit = 2k

thread_cache_size = 300

table_open_cache = 1024

tmp_table_size = 256M

#*********** Logs related settings ***********

log_bin = /data/mysqldata/3306/binlog/mysql-bin

binlog_format=row

binlog_cache_size=32m

max_binlog_cache_size=512m

max_binlog_size=512m

long_query_time = 1

log_output = FILE

log_error = /data/mysqldata/3306/mysql-error.log

slow_query_log = 1

slow_query_log_file = /data/mysqldata/3306/slow_statement.log

#log_queries_not_using_indexes

general_log = 0

general_log_file = /data/mysqldata/3306/general_statement.log

expire-logs-days = 14

#*********** MyISAM Specific options ***********

key_buffer_size = 32M

bulk_insert_buffer_size = 32M

myisam_sort_buffer_size = 64M

myisam_max_sort_file_size = 2G

myisam_repair_threads = 1

myisam_recover

#*********** INNODB Specific options ***********

innodb_file_per_table

transaction-isolation = READ-COMMITTED

innodb_additional_mem_pool_size = 16M

innodb_buffer_pool_size = 512M

innodb_data_home_dir = /data/mysqldata/3306/innodb_ts

innodb_data_file_path = ibdata1:2048M:autoextend:max:8192M

innodb_thread_concurrency = 0

innodb_log_buffer_size = 16M

innodb_log_file_size = 256M

innodb_log_files_in_group = 2

innodb_log_group_home_dir = /data/mysqldata/3306/innodb_log

innodb_flush_log_at_trx_commit = 2

innodb_max_dirty_pages_pct = 80

innodb_lock_wait_timeout = 120

innodb_flush_method=O_DIRECT

[mysqldump]

quick

max_allowed_packet = 512M

[mysql]

no-auto-rehash

prompt="\\u@\\h \\R:\\m:\\s \\d> "

# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]

key_buffer_size = 32M

sort_buffer_size = 20M

read_buffer_size = 2M

write_buffer_size = 2M

[mysqlhotcopy]

interactive-timeout

[mysqld_safe]

open-files-limit = 8192

启动数据库:

mysql>$ mysqld_safe --defaults-file=/etc/my.cnf & 

设置超级用户密码:

mysql>$mysqladmin -uroot password 'xxx' -S /data0/mysqldata/3306/mysql.sock

可以通过netstat或ps等命令查看mysqld是否正常加载,由于默认指定mysqld端口号为3306,因此通过netstat查看是否存在3306端口即可,例如:

mysql>$netstat -lnt 

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address Foreign Address State

tcp 0 0 127.0.0.1:2208 0.0.0.0:* LISTEN

tcp 0 0 0.0.0.0:864 0.0.0.0:* LISTEN

tcp 0 0 0.0.0.0:11111 0.0.0.0:* LISTEN

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN

tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN

tcp 0 0 0.0.0.0:16851 0.0.0.0:* LISTEN

0 0 0.0.0.0:21 0.0.0.0:* LISTEN

使用mysql命令行连接:

[mysql@mysqldb2 mysql]$ mysql -uroot -proot123.. -S /data/mysqldata/3306/mysql.sock

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.24-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

 3、后续动作

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

/usr/local/mysql/bin/mysql -uroot -proot123.. 
mysql>use mysql; //选择系统数据库mysql 
mysql>select Host,User,Password from user; //查看所有用户 
mysql>delete from user where password=""; 
mysql>flush privileges; 
mysql>select Host,User,Password from user; //确认密码为空的用户是否已全部删除 
mysql>exit; 

进入mysql安装目录下的支持文件目录

[root@mysqldb2 mysql]# cd /usr/local/mysql/support-files/

复制服务文件以使MYSQL能用service直接启动

[root@mysqldb2 mysql]# cp ./mysql.server /etc/init.d/mysqld

再拷贝已配置好的cnf参数文件到/etc/my.cnf,方便service启动时的读取

[root@mysqldb2 mysql]# cp /data/mysqldata/3306/my.cnf /etc/my.cnf

现在就可以很方便的用service命令来启动、关闭MYSQL服务器了

[mysql@mysqldb2 mysql]$ service mysqld restart

Shutting down MySQL....[ OK ]

Starting MySQL.[ OK ]

到这里,MYSQL的源码安装就结束了.

阅读(1826) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~