[原创]MySQL 5.5.5-m3编译安装手记

2011年6月27日 | 标签: mysql, 原创, 安装

该版本继续对 InnoDB 存储引擎进行改进,主要有:
InnoDB 存储引擎升级到 1.1.1 版本,该版本也是最新开发版。
InnoDB 在恢复时使用哈希表存储 redo 日志,在64位系统中,哈希表的大小为 buffer pool 大小的1/8,为了降低内存的使用,哈希表调整为 1/64 的缓冲池大小,如果是32位系统则为 1/128。
在慢查询日志中,UPDATE 和 DELETE 语句的 Rows_examined 值不再显示为0
移除 mysql_fix_privilege_tables 脚本
增加新变量:skip_name_resolve,用于禁用对客户端连接进行机器名解析
新增 SHA2() 函数
所有基于数值运算的函数现在都会抛出数值越界的错误
其他还包括修复了不少的bug

本文参考了张宴老师的文章,结合自己遇到的问题记录下来。

cd /data/src
tar zxvf mysql-5.5.5-m3.tar.gz
cd mysql-5.5.5-m3/
./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,myisammrg

出现错误

@ndbcluster_includes@ -c perror.c
gcc: @ndbcluster_includes@: No such file or directory
make[3]: *** [perror.o] Error 1
make[3]: Leaving directory `/root/mysql-5.5.5-m3/extra'
make[2]: *** [all-recursive] Error 1
make[2]: Leaving directory `/root/mysql-5.5.5-m3/extra'
make[1]: *** [all] Error 2
make[1]: Leaving directory `/root/mysql-5.5.5-m3/extra'
make: *** [all-recursive] Error 1

网上查了下,跟”@ndbcluster_includes@”和”@NDB_SCI_LIBS@”相关,要去掉。

grep "@ndbcluster_includes@" . -r
grep "@NDB_SCI_LIBS@" . -r

找到下面4个文件包含,记录下来。

./extra/Makefile
./sql/Makefile
./libmysqld/Makefile
./libmysqld/examples/Makefile

cd ../
rm -rf  mysql-5.5.5-m3
tar zxvf mysql-5.5.5-m3.tar.gz
cd mysql-5.5.5-m3/

去除文件中的字符串@ndbcluster_includes@、@NDB_SCI_LIBS@

sed -i "s#@ndbcluster_includes@##g" ./extra/Makefile
sed -i "s#@ndbcluster_includes@##g" ./sql/Makefile
sed -i "s#@ndbcluster_includes@##g" ./libmysqld/Makefile
sed -i "s#@NDB_SCI_LIBS@##g" ./sql/Makefile
sed -i "s#@NDB_SCI_LIBS@##g" ./libmysqld/Makefile
sed -i "s#@NDB_SCI_LIBS@##g" ./libmysqld/examples/Makefile

./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,myisammrg
make
make install
cd ../

添加用户级相关组

/usr/sbin/groupadd mysql
/usr/sbin/useradd -g mysql mysql -s /sbin/nologin
chmod +w /usr/local/mysql
chown -R mysql:mysql /usr/local/mysql

创建MySQL数据库存放目录

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

以mysql用户帐号的身份建立数据表

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

创建my.cnf配置文件:

vi /data/mysql/my.cnf

输入以下内容:

[client]
default-character-set = utf8
port = 3306
socket = /tmp/mysql.sock

[mysqld]
character-set-server = utf8
user = mysql
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql/data
log-error = /data/mysql/mysql_error.log
pid-file = /data/mysql/mysql.pid
open_files_limit = 10240
back_log = 600
max_connections = 5000
max_connect_errors = 6000
table_cache = 614
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 300
query_cache_size = 512M
query_cache_limit = 2M
query_cache_min_res_unit = 2k
default-storage-engine = MyISAM
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 246M
max_heap_table_size = 246M
long_query_time = 3
log-slave-updates
log-bin = /data/mysql/binlog/binlog
binlog_cache_size = 4M
binlog_format = MIXED
max_binlog_cache_size = 8M
max_binlog_size = 1G
expire_logs_days = 30
key_buffer_size = 256M
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
slave-skip-errors = 1032,1062,126,1114,1146,1048,1396

server-id = 1

innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 512M
innodb_data_file_path = ibdata1:256M: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

log-slow-queries = /data/mysql/slow.log
long_query_time = 1

[mysqldump]
quick
max_allowed_packet = 32M

创建/etc/my.cnf链接,增加环境变量,系统随机启动等

ln -s /data/mysql/my.cnf /etc/my.cnf
ln -s /usr/local/mysql/bin/mysql /usr/bin
cp /usr/local/mysql/share/mysql/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on
/usr/local/mysql/bin/mysqld_safe --user=mysql &
service mysqld restart

通过命令行登录管理MySQL服务器(提示输入密码时直接回车):

/usr/local/mysql/bin/mysql -u root -p -S /tmp/mysql.sock

输入以下SQL语句,只保留root@127.0.0.1以及root@localhost用户,并修改root密码(123456):

DELETE FROM mysql.user WHERE user='' or host='::1';
UPDATE mysql.user SET password=PASSWORD("123456") WHERE user='root';

相关博文

  1. shao
    2011年6月27日18:16

    啊,好棒。

WordPress SEO fine-tune by Meta SEO Pack from Poradnik Webmastera