数据库5.5.32编译安装和简单的初步优化

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介:

.数据库5.5.32编译安装和简单的初步优化

1.环境:

[root@mysql-master01 sbin]# cat/etc/redhat-release

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 eth1|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的存储引擎,

MyISAMMERGEMEMORY以及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(see documentation 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

 



 本文转自 wjw555 51CTO博客,原文链接:http://blog.51cto.com/wujianwei/1934086

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
24天前
|
缓存 数据库 索引
面试题17: 数据库优化
面试题17: 数据库优化
|
1月前
|
缓存 监控 NoSQL
请解释一下云数据库的性能监控和优化。
请解释一下云数据库的性能监控和优化。
24 0
|
1月前
|
存储 监控 关系型数据库
Mysql数据库优化
Mysql数据库优化
|
1月前
|
缓存 数据库 索引
高效后端开发:数据库优化策略详解
数据库优化是后端开发中不可或缺的一部分。本文将从多个方面详细阐述数据库优化策略,包括数据表设计、索引优化、查询性能优化等。通过本文的学习,读者可以了解如何优化数据库,提升后端应用性能和稳定性。
23 1
|
2天前
|
SQL 监控 Java
Java Web应用中数据库连接池的配置与优化
Java Web应用中数据库连接池的配置与优化
|
4天前
|
存储 缓存 NoSQL
《优化数据库性能的关键技巧》
在当今信息爆炸的时代,数据库扮演着至关重要的角色。本文将分享一些关键的技巧,帮助开发人员优化数据库性能,提升系统的响应速度和稳定性。
|
17天前
|
监控 关系型数据库 MySQL
MySQL技能完整学习列表12、性能优化——1、性能指标和监控——2、优化查询和数据库结构——3、硬件和配置优化
MySQL技能完整学习列表12、性能优化——1、性能指标和监控——2、优化查询和数据库结构——3、硬件和配置优化
58 0
|
19天前
|
缓存 NoSQL PHP
百度搜索:蓝易云【如何使用PHP进行数据库索引优化?】
通过以上方法,你可以使用PHP进行数据库索引优化,提高数据库查询性能和整体应用性能。同时,定期维护数据库和优化查询语句也是保持数据库高性能的关键。
39 11
|
21天前
|
机器学习/深度学习 算法 数据库
深入浅出:利用Python与机器学习优化数据库性能
本文介绍了一种创新的方法,结合Python编程语言和机器学习技术,来优化数据库性能。传统的数据库性能优化方法往往依赖于数据库管理员(DBA)的经验和直觉,而本文所提出的方法通过自动化的方式,利用机器学习模型对数据库查询进行分析和优化,从而实现更高效、更智能的数据库性能管理。本文首先介绍了使用Python进行数据库操作的基础知识,然后详细阐述了如何应用机器学习算法来预测和改善数据库查询性能,最后通过一个实际案例展示了该方法的有效性。本文旨在为数据库管理员、开发者以及对数据库性能优化感兴趣的读者提供一种全新的视角和工具。
|
22天前
|
存储 大数据 数据库
深入理解数据库索引优化技术
本文将深入探讨数据库索引优化技术,包括索引的基本概念、常见的索引类型以及如何选择和创建适当的索引。通过了解索引的原理和优化策略,读者可以提高数据库查询性能并优化数据访问效率。

热门文章

最新文章