[分享]mysql数据库的安装_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 1958 | 回复: 0

主题： [分享]mysql数据库的安装

yanpeng

注册用户

等级：上等兵
经验：127
发帖：68
精华：0
注册：2011-12-26
状态：离线
发送短消息息给yanpeng

加好友发送短消息息给yanpeng

发消息

发表于：

2014-11-20 10:57:34 | [全部帖] [楼主帖]

楼主

mysql的优化分为三个部分

1.物理服务器硬件的优化

mysql每秒钟都进行大量的，复杂的查询操作，对磁盘的1/O有很高的要求，通常认为1/O是制约mysql性能的最大因素。

2.mysql安装时的编译优化

--static 静态链接提高13%性能
--with-client-ldflags=-all-static
--with-mysqld-ldflags=-all-static
--pgcc 如果是Inter处理器，使用pgcc提高1%性能
CFLAGS="-O3 -mpentiumpro -mstack-align-double" CXX=gcc \
CXXFLAGS="-O3 -mpentiumpro -mstack-align-double \
--felide-constructors -fno-exceptions -fno-rtti"

Unix Socket 使用unix套接字链接提高7.5%性能，所以在windows下mysql性能肯定不如unix下面

--with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock
--enable-assembler允许使用汇编模式(优化性能)
--without-debug 编译安装时默认已debug模式生成二进制代码，而该方式会给mysql带来很大的性能损失

3.自身配置文件的优化见后面的配置文档

------------------------------------------------------------------------

操作系统及其mysql配置文件说明

系统：Centos5.5

mysql:mysql-5.1.58.tar.gz源码包

安装目录：/usr/local/mysql/

数据目录：/data/mysql/3306/data/

二进制日志：/data/msyql/3306/binlog/

relay日志：/data/mysql/3306/relaylog/

配置文件：/data/mysql/3306/my.cnf

mysql.sock文件：/data/mysql/3306/mysql.sock

------------------------------------------------------------------------

1.安装gcc ncurses-devel

yum -y install gcc ncurses-devel

2.创建用户

/usr/sbin/groupadd mysql
/usr/sbin/useradd -s /sbin/nologin -g mysql -M mysql

3.安装

tar zxvf mysql-5.1.58.tar.gz
cd mysql-5.1.58
./configure --prefix=/usr/local/mysql --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-big-tables --with-readline --with-ssl --with-embedded-server --enable-local-infile --with-plugins=partition,innobase --with-plugin-PLUGIN --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --with-unix-socket-path=/data/mysql/3306/mysql.sock
make

Entering directory `/root/mysql-5.1.48/mysql-test' 这里需要很长时候

make install

设置目录权限

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

创建mysql数据存放目录

mkdir -p /data/mysql/3306/data
mkdir -p /data/mysql/3306/binlog/
mkdir -p /data/mysql/3306/relaylog/
chown -R mysql:mysql /data/mysql/

初始化mysql数据库

/usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql/3306/data --user=mysql

创建mysql配置文件

vim /data/mysql/3306/my.cnf
[client]
default-character-set=utf8
port    = 3306
socket  = /data/mysql/3306/mysql.sock
[mysqld]
default-character-set=utf8
replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = information_schema
user    = mysql
port    = 3306
socket  = /data/mysql/3306/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql/3306/data
log-error = /data/mysql/3306/mysql_error.log
pid-file = /data/mysql/3306/mysql.pid
open_files_limit    = 10240
back_log = 600
max_connections = 3000
max_connect_errors = 6000
table_cache = 4096
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 10M
join_buffer_size = 2M
thread_cache_size = 300
thread_concurrency = 16
query_cache_size = 512M
query_cache_limit = 4M
query_cache_min_res_unit = 2k
default-storage-engine = InnoDB
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 2048M
max_heap_table_size = 256M
long_query_time = 1
log-slave-updates
log_long_format
log-short-format
log-slow-queries=/data/mysql/3306/slow-log.log
#slow_query_log=/data/mysql/3306/slow-log.log
log-bin = /data/mysql/3306/binlog/binlog
binlog_cache_size = 4M
binlog_format = MIXED
max_binlog_cache_size = 8M
max_binlog_size = 1G
relay-log-index = /data/mysql/3306/relaylog/relaylog
relay-log-info-file = /data/mysql/3306/relaylog/relaylog
relay-log = /data/mysql/3306/relaylog/relaylog
expire_logs_days = 30
key_buffer_size = 1024M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
interactive_timeout = 120
wait_timeout = 120
skip-name-resolve
#master-connect-retry = 10
slave-skip-errors = 1032,1062,126,1114,1146,1048,1396
#master-host     =   192.168.0.1
#master-user     =   username
#master-password =   password
#master-port     =  3306
server-id = 1
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2048M
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 64M
------------------------------------------------------------------------

创建mysql启动脚本文件

cp support-files/mysql.server.sh /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
/etc/init.d/mysqld start

查看mysql是否启动成功

[root@mail opt]# netstat -antl |grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN

为了安全起见，可以自己手动编写mysql启动脚本，上面步骤可以省略

----------------------------------------------------- -----------------

mysql 安装出错总结

1.在 CentOS 下编译安装MySQL时出错：

/bin/rm: cannot remove `libtoolt': No such file or directory

需要安装libtool软件包

2.[root@mail 3306]# /etc/init.d/mysqld restart

/etc/init.d/mysqld: line 272: @HOSTNAME@: command not found
/etc/init.d/mysqld: line 281: @HOSTNAME@: command not found

MySQL manager or server PID file could not be found! [失败]

需要把mysql配置文件my.cnf放到/etc/目录下面，并且在mysqld指定下面目录

basedir=/usr/local/mysql
datadir=/usr/local/mysql/3306/data/

登录数据库的时候报下面的错误：

[root@nagios]# /usr/local/mysql/bin/mysql -uroot -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

为了安全性，在编译数据库的时候修改了mysql.sock文件的存放位置，mysql.sock文件默认放在/tmp目录里面，所以在登录数据库的时候要指定修改后的mysql.sock文件的存放位置

[root@nagios]# locate mysql.sock
/etc/mysql/mysql.sock
[root@nagios]# /usr/local/mysql/bin/mysql -uroot -S /etc/mysql/mysql.sock -p

--转自