數(shù)據(jù)庫5.5.32編譯安裝和簡單的初步優(yōu)化

.數(shù)據(jù)庫5.5.32編譯安裝和簡單的初步優(yōu)化

1.環(huán)境：

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

CentOS release 6.6 (Final)

Ip：外網(wǎng)：10.0.0.31  內(nèi)網(wǎng)：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數(shù)據(jù)庫

/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編譯安裝

環(huán)境：

[root@mysql-5 soft]# ifconfig eth0|awk -F '[ :]+''NR==2{print $4}';ifconfig eth2|awk -F '[ :]+' 'NR==2{print $4}'

192.168.56.41外網(wǎng)

172.16.1.41內(nèi)網(wǎng)

[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.創(chuàng)建軟件安裝目錄并且解壓軟件等

[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前的準(zhǔn)備工作

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數(shù)據(jù)庫

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編譯參數(shù)解釋：

# /*  以下為配置參數(shù)注解   */ 

# /* -DCMAKE_INSTALL_PREFIX=/aliyun/server/mysql      # 指定mysql安裝位置 

# /* -DMYSQL_DATADIR=/data/mysql/data             # 數(shù)據(jù)文件存放位置 

# /* -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# 連接數(shù)據(jù)庫socket路徑 

# /* -DENABLED_LOCAL_INFILE=on                   # 允許從本地導(dǎo)入數(shù)據(jù) 

# /* -DEXTRA_CHARSETS=all                         # 安裝所有的字符集 

# /* -DDEFAULT_CHARSET=utf8                       # 默認(rèn)字符 

#/*  -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii          #設(shè)定其他額外的字符集

# /* -DDEFAULT_COLLATION=utf8_general_ci           # 指定服務(wù)器默認(rèn)的校對規(guī)則，默認(rèn)latin1_general_ci 

#/*-DWITH_ZLIB=bundled 

#/* -DWITH_EMBEDDED_SERVER=1               

額外的編譯參數(shù)：

# /* -DWITH_xxx_STORAGE_ENGINE            # 指定靜態(tài)編譯到mysql的存儲引擎，

MyISAM，MERGE，MEMORY以及CSV四種引擎默認(rèn)即被編譯至服務(wù)器，不需要特別指定[1.代表編譯] 

# /* -DWITHOUT_xxx_STORAGE_ENGINE     # 指定不編譯的存儲引擎

# /* -DWITH_EXTRA_CHARSETS:STRING=utf8,gbk  

5.3編譯安裝時報錯

1.編譯安裝的時候在63%處報錯：

make出現(xiàn)的報錯問題：編譯安裝到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參數(shù)并且重新進(jìn)行編譯

 -DWITH_PARTITION_STORAGE_ENGINE=1       # 安裝支持?jǐn)?shù)據(jù)庫分區(qū) 

2.編譯報錯

-bash: -DMYSQL_UNIX_ADDR=/tmp/mysql.sock: No suchfile or directory

去掉該參數(shù), 刪掉解壓目錄,重新接壓入軟件包，重新編譯

3.由于內(nèi)存不夠，編譯安裝的MySQL服務(wù)起不來

租用的服務(wù)器是阿里云的乞丐版（最低配置），單核，512M內(nèi)存，1M帶寬.

解決辦法就是修改或減少my.cnf中的 innodb_buffer_pool_size 的值

結(jié)果網(wǎng)友設(shè)置當(dāng)時設(shè)置了 1G，肯定爆了，后來設(shè)置成100M，還是不夠，最后設(shè)置成80M，最后淚流滿面的啟動成功了..

如果命令行提示 The server quit without updating PID file 請檢查data目錄的所屬組和所屬用戶是不是mysql，也請看清楚日志，是不是因?yàn)榕渲眠^高，導(dǎo)致無法啟動.

5.4 拍完錯繼續(xù)進(jìn)行編譯安裝

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會報錯權(quán)限不允許）

[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 服務(wù)沒有起來。

[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數(shù)據(jù)庫初步簡單優(yōu)化:

給數(shù)據(jù)庫設(shè)置密碼：

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

啟動報錯就檢查配置文件中參數(shù)的設(shè)置肯定存在問題.檢查配置文件原來是這得路徑不對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初始化顯示的內(nèi)容：

配置文件相關(guān)參數(shù)詳解：

[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