数据库5.5.32编译安装和简单的初步优化
.数据库5.5.32编译安装和简单的初步优化
1.环境：
[root@MySQL-master01 sbin]# cat/etc/redhat-release
创新互联是一家专业提供丹寨企业网站建设,专注与成都做网站、成都网站建设、H5场景定制、小程序制作等业务。10年已为丹寨众多企业、政府机构等服务。创新互联专业网站建设公司优惠进行中。
CentOS release 6.6 (Final)
Ip：外网：10.0.0.31 内网：172.16.1.31
2.安装步奏：
Useradd mysql -s /sbin/nologin -M
Id mysql
yum install cmake -y
yum install libaio-develncurses-devel -y
rpm -qalibaio-develncurses-devel
mkdir /server/soft -p
cd /server/soft/
rz -y
ls
tarxf mysql-5.5.32.tar.gz
ls
cd mysql-5.5.32
cmake . -DCMAKE_INSTALL_PREFIX=/aliyun/mysql-5.5.32-DMYSQL_DATADIR=/aliyun/mysql-5.5.32/data-DMYSQL_UNIX_ADDR=/aliyun/mysql-5.5.32/tmp/mysql.sock -DDEFAULT_CHARSET=utf8-DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii-DENABLED_LOCAL_INFILE=ON -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1-DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1-DWITHOUT_PARTITION_STORAGE_ENGINE=1 -DWITH_FAST_MUTEXES=1 -DWITH_ZLIB=bundled-DENABLED_LOCAL_INFILE=1 -DWITH_READLINE=1 -DWITH_EMBEDDED_SERVER=1 -DWITH_DEBUG=0
make
make install
echo $?
ln -s /aliyun/mysql-5.5.32/ /aliyun/mysql
ll /aliyun/mysql
/aliyun/mysql/scripts/mysql_install_db--basedir=/aliyun/mysql --datadir=/aliyun/mysql/data/ --user=mysql
ll /aliyun/mysql
chown -R mysql.mysql /aliyun/mysql/
\cp /aliyun/mysql/support-files/my-small.cnf/etc/my.cnf
sed -i 's#/usr/local/mysql#/aliyun/mysql#g'/aliyun/mysql/bin/mysqld_safe
sed -i 's#/usr/local/mysql#/aliyun/mysql#g'/aliyun/mysql/support-files/mysql.server
\cp /aliyun/mysql/support-files/mysql.server /etc/init.d/mysqld
3.启动MySQL数据库
/etc/init.d/mysqld start
/etc/init.d/mysqld stop
chkconfigmysqld on
ln -s /aliyun/mysql/bin/*/usr/local/sbin
或者：
cp /aliyun/mysql/bin/*/usr/local/sbin/
ll /usr/local/sbin/
/etc/init.d/mysqld start
mysql
二.mysql5.6.16编译安装
环境：
[root@mysql-5 soft]# ifconfig eth0|awk -F '[ :]+''NR==2{print $4}';ifconfig eth2|awk -F '[ :]+' 'NR==2{print $4}'
192.168.56.41外网
172.16.1.41内网
[root@mysql-5 soft]# cat /etc/redhat-release
CentOS release 6.5 (Final)
/etc/init.d/iptables stop
或者：
Vim /etc/sysconfig/iptable
-A INPUT -m state --state NEW -m tcp -p tcp--dport 3306 -j ACCEPT
grep -i "SELINUX=disabled"/etc/sysconfig/selinux
1.下载yum源
wget -O /etc/yum.repos.d/epel.repohttp://mirrors.aliyun.com/repo/epel-6.repo
yum install gccgcc-c++ gcc-g77autoconf automake make cmake bison make perl perl-devel ncurses ncurses-devel
2.创建软件安装目录并且解压软件等
[root@mysql-5 yum.repos.d]# cd /data/soft/
[root@mysql-5 soft]# ls
cmake-2.8.4.tar.gz mysql-5.6.16.tar.gz
tar xf mysql-5.6.16.tar.gz -C /aliyun/server/
tar xf cmake-2.8.4.tar.gz -C /aliyun/server/
75pxake 编译安装
cd /aliyun/server/
ls
cd cmake-2.8.4/
./configure--prefix=/aliyun/server/cmake
gmake
make install
echo $?
vim /etc/profile
PATH=/aliyun/server/cmake/bin:$PATH
export path
echo "PATH=/aliyun/server/cmake/bin:$PATH">>/etc/profile
source /etc/profile
cmake --version
[root@mysql-5 scripts]# cmake --version
cmake version 2.8.4
4.编译安装MySQL5.6前的准备工作
groupadd mysql
useradd -g mysql -s '/sbin/nologin' mysql
cd /aliyun/server/
mkdir -p /data/mysql/data
chown mysql.mysql -R /data/mysql/data/
cd /aliyun/server/mysql-5.6.16/
5.编译安装MySQL数据库
5.1MySQL5.6.16编译安装成功:
cmake .-DCMAKE_INSTALL_PREFIX=/aliyun/server/mysql-5.6.16 -DMYSQL_DATADIR=/data/mysql/data-DDEFAULT_CHARSET=utf8 -DSYSCONFDIR=/etc -DDEFAULT_COLLATION=utf8_general_ci-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii -DENABLED_LOCAL_INFILE=ON-DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1-DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITH_FAST_MUTEXES=1-DWITH_ZLIB=bundled -DENABLED_LOCAL_INFILE=1 -DWITH_READLINE=1-DWITH_EMBEDDED_SERVER=1 -DWITH_DEBUG=0
echo $?
make
make install
echo $?
5.2编译参数解释：
# /* 以下为配置参数注解 */
# /* -DCMAKE_INSTALL_PREFIX=/aliyun/server/mysql # 指定mysql安装位置
# /* -DMYSQL_DATADIR=/data/mysql/data # 数据文件存放位置
# /* -DSYSCONFDIR=/etc # 配置文件my.cnf的位置
# /* -DWITH_MYISAM_STORAGE_ENGINE=1 # 支持MyIASM引擎
# /* -DWITH_INNOBASE_STORAGE_ENGINE=1 # 支持InnoDB引擎
# /* -DWITH_MEMORY_STORAGE_ENGINE=1 # 支持Memory引擎
-DWITH_FEDERATED_STORAGE_ENGINE=1
-DWITH_BLACKHOLE_STORAGE_ENGINE=1
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1
# /* -DWITH_READLINE=1 # 快捷键功能(我没用过)
# /* -DMYSQL_UNIX_ADDR=/tmp/mysql.sock# 连接数据库socket路径
# /* -DENABLED_LOCAL_INFILE=on # 允许从本地导入数据
# /* -DEXTRA_CHARSETS=all # 安装所有的字符集
# /* -DDEFAULT_CHARSET=utf8 # 默认字符
#/* -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii #设定其他额外的字符集
# /* -DDEFAULT_COLLATION=utf8_general_ci # 指定服务器默认的校对规则，默认latin1_general_ci
#/*-DWITH_ZLIB=bundled
#/* -DWITH_EMBEDDED_SERVER=1
额外的编译参数：
# /* -DWITH_xxx_STORAGE_ENGINE # 指定静态编译到mysql的存储引擎，
MyISAM，MERGE，MEMORY以及CSV四种引擎默认即被编译至服务器，不需要特别指定[1.代表编译]
# /* -DWITHOUT_xxx_STORAGE_ENGINE # 指定不编译的存储引擎
# /* -DWITH_EXTRA_CHARSETS:STRING=utf8,gbk
5.3编译安装时报错
1.编译安装的时候在63%处报错：
make出现的报错问题：编译安装到63%报错
make[2]: ***[storage/perfschema/unittest/pfs_connect_attr-t] Error 1
make[1]: ***[storage/perfschema/unittest/CMakeFiles/pfs_connect_attr-t.dir/all] Error 2
解决方法：
cmake 去掉 -DWITH_PARTITION_STORAGE_ENGINE=1参数并且重新进行编译
-DWITH_PARTITION_STORAGE_ENGINE=1 # 安装支持数据库分区
2.编译报错
-bash: -DMYSQL_UNIX_ADDR=/tmp/mysql.sock: No suchfile or directory
去掉该参数, 删掉解压目录,重新接压入软件包，重新编译
3.由于内存不够，编译安装的MySQL服务起不来
租用的服务器是阿里云的乞丐版（最低配置），单核，512M内存，1M带宽.
解决办法就是修改或减少my.cnf中的 innodb_buffer_pool_size 的值
结果网友设置当时设置了 1G，肯定爆了，后来设置成100M，还是不够，最后设置成80M，最后泪流满面的启动成功了..
如果命令行提示 The server quit without updating PID file 请检查data目录的所属组和所属用户是不是mysql，也请看清楚日志，是不是因为配置过高，导致无法启动.
5.4 拍完错继续进行编译安装
make
make install
echo $?
做软连接：
ln -s mysql-5.6.16 mysql
cd /aliyun/server/
ll
lrwxrwxrwx. 1 root root 12 May 16 22:41mysql -> mysql-5.6.16
chown -R mysql.mysql /aliyun/server/mysql-5.6.16/
cd /aliyun/server/mysql-5.6.16/support-files/
cp my-default.cnf/etc/my.cnf
cp mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld （否I则下面初始化MySQL会报错权限不允许）
[root@mysql-5 scripts]# /aliyun/server/mysql/scripts/mysql_install_db--user=mysql --basedir=/aliyun/server/mysql --datadir=/data/mysql/data
-bash:/aliyun/server/mysql/scripts/mysql_install_db:Permission denied
[root@mysql-5 scripts]# ll mysql_install_db
-rw-r--r--. 1 mysql mysql 33046 May 16 23:38mysql_install_db
[root@mysql-5 scripts]# chmod +x mysql_install_db
5.5初始化MySQL启动MySQL
[root@mysql-5 scripts]# /aliyun/server/mysql/scripts/mysql_install_db--user=mysql --basedir=/aliyun/server/mysql --datadir=/data/mysql/data
/etc/init.d/mysqld start
/etc/init.d/mysqld stop
/etc/init.d/mysqld status
echo 'export PATH=/aliyun/server/mysql/bin:$PATH'>>/etc/profile
source /etc/profile
初始化后启动MySQL时报错：lsof -i:3306 服务没有起来。
[root@mysql-5 ~]# /etc/init.d/mysqld start
Starting MySQL... ERROR! The server quit withoutupdating PID file (/var/lib/mysql/mysql-5.6s1.pid).
原因是cp my-default.cnf /etc/my.cnf 时，没有cp成功
重新cp,后，重新初始化MySQL，再次启动MySQL，成功
5.6数据库初步简单优化:
给数据库设置密码：
mysqladmin -uroot password 123456
mysql> show databases;
mysql> select user,host from mysql.user;
mysql> drop user 'root'@'::1';
mysql> drop user ''@'localhost';
mysql> drop user ''@'mysql-5.6m1';
mysql> drop user 'root'@'mysql-5.6m1';
mysql> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| root | localhost |
+------+-----------+
2 rows in set (0.00 sec)
mysql> drop database test;
Query OK, 0 rows affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
修改密码：
mysqladmin -uroot -p'123456' password'111111'
编辑mysql5.6.16配置文件
[root@mysql-5 tmp]# cat /etc/my.cnf
[client]
socket = /tmp/mysqld.sock
port = 3306
[mysqld]
basedir = /aliyun/server/mysql
datadir = /data/mysql/data
socket = /tmp/mysqld.sock
port = 3306
server_id = 100
user = mysql
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
default_storage_engine = InnoDB
join_buffer_size = 128M
max_allowed_packet= 1M
net_buffer_length= 8K
skip-external-locking
skip-host-cache
skip-name-resolve
# InnoDB
innodb_buffer_pool_size = 128M
innodb_log_file_size = 48M
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
# MyISAM
key_buffer_size = 48M
character-set-server=utf8
collation-server=utf8_general_ci
# LOG
log_error = /data/mysql/data/mysql-error.log
long_query_time = 1
slow-query-log
slow_query_log_file = /data/mysql/data/mysql-slow.log
# Others
explicit_defaults_for_timestamp=true
max_connections = 500
open_files_limit = 65535
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
启动报错就检查配置文件中参数的设置肯定存在问题.检查配置文件原来是这得路径不对log_error = /data/mysql/data/mysql-error.log
[root@mysql-5 scripts]# /etc/init.d/mysqldstart
Starting MySQL. ERROR! The server quitwithout updating PID file (/data/mysql/data/mysql-5.6s1.pid).
mysql5.6初始化显示的内容：
配置文件相关参数详解：
[root@mysql-5 scripts]# /aliyun/server/mysql/scripts/mysql_install_db--user=mysql --basedir=/aliyun/server/mysql --datadir=/data/mysql/data
WARNING: The host 'mysql-5.6m1'could not be looked up with /aliyun/server/mysql/bin/resolveip.
This probably means that your libc libraries arenot 100 % compatible
with this binary MySQL version. The MySQL daemon,mysqld, should work
normally with the exception that host nameresolving will not work.
This means that you should use IP addresses insteadof hostnames
when specifying MySQL privileges !
Installing MySQL system tables...2016-05-1700:13:04 0 [Warning] TIMESTAMP with implicit DEFAULTvalue is deprecated. Please use --explicit_defaults_for_timestamp server option(seedocumentation for more details).
2016-05-17 00:13:04 9551 [Note] InnoDB: Usingatomics to ref count buffer pool pages
2016-05-17 00:13:04 9551 [Note] InnoDB: The InnoDBmemory heap is disabled
2016-05-17 00:13:04 9551 [Note] InnoDB: Mutexes andrw_locks use GCC atomic builtins
2016-05-17 00:13:04 9551 [Note] InnoDB: Compressedtables use zlib 1.2.3
2016-05-17 00:13:04 9551 [Note] InnoDB: Using CPUcrc32 instructions
2016-05-17 00:13:04 9551 [Note] InnoDB:Initializing buffer pool, size = 128.0M
2016-05-17 00:13:04 9551 [Note] InnoDB: Completedinitialization of buffer pool
2016-05-17 00:13:04 9551 [Note] InnoDB: The firstspecified data file ./ibdata1 did not exist: a new database to be created!
2016-05-17 00:13:04 9551 [Note] InnoDB: Settingfile ./ibdata1 size to 12 MB
2016-05-17 00:13:04 9551 [Note] InnoDB: Databasephysically writes the file full: wait...
2016-05-17 00:13:04 9551 [Note] InnoDB: Setting logfile ./ib_logfile101 size to 48 MB
2016-05-17 00:13:05 9551 [Note] InnoDB: Setting logfile ./ib_logfile1 size to 48 MB
2016-05-17 00:13:06 9551 [Note] InnoDB: Renaminglog file ./ib_logfile101 to ./ib_logfile0
2016-05-17 00:13:06 9551 [Warning] InnoDB: New logfiles created, LSN=45781
2016-05-17 00:13:06 9551 [Note] InnoDB: Doublewritebuffer not found: creating new
2016-05-17 00:13:06 9551 [Note] InnoDB: Doublewritebuffer created
2016-05-17 00:13:06 9551 [Note] InnoDB: 128 rollbacksegment(s) are active.
2016-05-17 00:13:06 9551 [Warning] InnoDB: Creatingforeign key constraint system tables.
2016-05-17 00:13:06 9551 [Note] InnoDB: Foreign keyconstraint system tables created
2016-05-17 00:13:06 9551 [Note] InnoDB: Creating tablespaceand datafile system tables.
2016-05-17 00:13:06 9551 [Note] InnoDB: Tablespaceand datafile system tables created.
2016-05-17 00:13:06 9551 [Note] InnoDB: Waiting forpurge to start
2016-05-17 00:13:06 9551 [Note] InnoDB: 5.6.16started; log sequence number 0
2016-05-17 00:13:06 9551 [Note] Binlog end
2016-05-17 00:13:06 9551 [Note] InnoDB: FTSoptimize thread exiting.
2016-05-17 00:13:06 9551 [Note] InnoDB: Startingshutdown...
2016-05-17 00:13:07 9551 [Note] InnoDB: Shutdowncompleted; log sequence number 1625977
OK
Filling help tables...2016-05-17 00:13:07 0 [Warning] TIMESTAMP with implicit DEFAULT value isdeprecated. Please use --explicit_defaults_for_timestamp server option (seedocumentation for more details).
2016-05-17 00:13:07 9574 [Note] InnoDB: Usingatomics to ref count buffer pool pages
2016-05-17 00:13:07 9574 [Note] InnoDB: The InnoDBmemory heap is disabled
2016-05-17 00:13:07 9574 [Note] InnoDB: Mutexes andrw_locks use GCC atomic builtins
2016-05-17 00:13:07 9574 [Note] InnoDB: Compressedtables use zlib 1.2.3
2016-05-17 00:13:07 9574 [Note] InnoDB: Using CPUcrc32 instructions
2016-05-17 00:13:07 9574 [Note] InnoDB:Initializing buffer pool, size = 128.0M
2016-05-17 00:13:07 9574 [Note] InnoDB: Completedinitialization of buffer pool
2016-05-17 00:13:07 9574 [Note] InnoDB: Highestsupported file format is Barracuda.
2016-05-17 00:13:07 9574 [Note] InnoDB: 128rollback segment(s) are active.
2016-05-17 00:13:07 9574 [Note] InnoDB: Waiting forpurge to start
2016-05-17 00:13:07 9574 [Note] InnoDB: 5.6.16started; log sequence number 1625977
2016-05-17 00:13:07 9574 [Note] Binlog end
2016-05-17 00:13:07 9574 [Note] InnoDB: FTSoptimize thread exiting.
2016-05-17 00:13:07 9574 [Note] InnoDB: Startingshutdown...
2016-05-17 00:13:09 9574 [Note] InnoDB: Shutdowncompleted; log sequence number 1625987
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place foryour system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQLroot USER !
To do so, start the server, then issue thefollowing commands:
/aliyun/server/mysql/bin/mysqladmin -u rootpassword 'new-password'
/aliyun/server/mysql/bin/mysqladmin -u root -h mysql-5.6m1 password'new-password'
Alternatively you can run:
/aliyun/server/mysql/bin/mysql_secure_installation
which will also give you the option of removing thetest
databases and anonymous user created bydefault. This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd . ;/aliyun/server/mysql/bin/mysqld_safe &
You can test the MySQL daemon withmysql-test-run.pl
cdmysql-test ; perl mysql-test-run.pl
Please report any problems athttp://bugs.mysql.com/
The latest information about MySQL is available onthe web at
http://www.mysql.com
Support MySQL by buying support/licenses athttp://shop.mysql.com
New default config file was created as/aliyun/server/mysql/my.cnf and
will be used by default by the server when youstart it.
You may edit this file to change server settings
WARNING: Default config file/etc/my.cnf exists on the system
This file will be read by default by the MySQLserver
If you do not want to use this, either remove it,or use the
--defaults-file argument to mysqld_safe whenstarting the server
chown mysql.mysql -R /aliyun/server/mysql
当前名称：数据库5.5.32编译安装和简单的初步优化
文章路径：http://myzitong.com/article/ghppci.html