在运维工作中经常部署各种运维环境，涉及mysql数据库的安装也是时常需要的。mysql数据库安装可以选择yum在线安装，但是这种安装的mysql一般是系统自带的，版本方面可能跟需求不太匹配。
可以通过源码安装的方式，自定义安装自己所需版本的mysql。
---------------------------------------------------------------------------------------------------------------------------------
mysql数据库yum在线安装：
[root@host-test-huanqiu ~]# yum -y install mysql mysql-server
[root@host-test-huanqiu ~]# /etc/init.d/mysqld start
[root@host-test-huanqiu ~]# mysqladmin password "123456"
[root@host-test-huanqiu ~]# mysql -p123456
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.73 |
+-----------+
1 row in set (0.00 sec
---------------------------------------------------------------------------------------------------------------------------------
废话不多说，以下是centos6x系统下源码安装Mysql的操作记录，方便参考~
1、卸载旧版本
[root@host-test-huanqiu ~]# rpm -qa | grep mysql
mysql-5.1.73-7.el6.x86_64
mysql-libs-5.1.73-7.el6.x86_64
mysql-server-5.1.73-7.el6.x86_64
#普通删除模式
[root@host-test-huanqiu ~]# rpm -e mysql-5.1.73-7.el6.x86_64 mysql-libs-5.1.73-7.el6.x86_64 mysql-server-5.1.73-7.el6.x86_64
# 强力删除模式，如果使用上面命令删除时，提示有依赖的其它文件，则用该命令可以对其进行强力删除
[root@host-test-huanqiu ~]# rpm -e --nodeps mysql-5.1.73-7.el6.x86_64 mysql-libs-5.1.73-7.el6.x86_64 mysql-server-5.1.73-7.el6.x86_64
#删除/etc/my.cnf
[root@host-test-huanqiu ~]# rm /etc/my.cnf
2、安装编译代码需要的包
[root@host-test-huanqiu ~]# yum -y install make gcc-c++ cmake bison-devel ncurses-devel
3、创建mysql用户，不建立mysql用户家目录（加-M参数），也就是禁用mysql账号登陆系统
[root@host-test-huanqiu ~]# groupadd mysql
[root@host-test-huanqiu ~]# useradd -g mysql mysql -M -s /sbin/nologin
4、下载MySQL，源码编译安装
|
1
2
3
4
5
6
7
|
[root@host-
test
-huanqiu ~]
# cd /usr/local/src
[root@host-
test
-huanqiu src]
# wget -c http://ftp.ntu.edu.tw/MySQL/Downloads/MySQL-5.6/mysql-5.6.34.tar.gz
[root@host-
test
-huanqiu src]
# tar -zxvf mysql-5.6.34.tar.gz
[root@host-
test
-huanqiu src]
# cd mysql-5.6.34/
[root@host-
test
-huanqiu mysql-5.6.34]
# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql/data -DSYSCONFDIR=/etc -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
[root@host-
test
-huanqiu mysql-5.6.34]
# make && make install
|
5、配置MySQL
|
1
2
3
4
5
6
7
8
|
修改
/usr/local/mysql
权限
[root@host-
test
-huanqiu mysql-5.6.34]
# mkdir -p /data/mysql/data
[root@host-
test
-huanqiu mysql-5.6.34]
# chown -R mysql:mysql /usr/local/mysql
[root@host-
test
-huanqiu mysql-5.6.34]
# chown -R mysql:mysql /data/mysql/data
执行初始化配置脚本，创建系统自带的数据库和表
[root@host-
test
-huanqiu mysql-5.6.34]
# cd /usr/local/mysql
[root@host-
test
-huanqiu mysql]
# ./scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql/data --user=mysql //如果后续不小心删除了这个数据目录或这个目录下的文件被误操作删除了，还可以利用这个命令重新初始化
|
需要特别注意：
在启动MySQL服务时，会按照一定次序搜索my.cnf，先在/etc目录下找，找不到则会搜索"$basedir/my.cnf"，在本例中就是 /usr/local/mysql/my.cnf，这是新版MySQL的配置文件的默认位置！
另外：
在CentOS 6.4版操作系统最小安装完成后，默认会在/etc目录下存在一个my.cnf，需要将此文件更名为其他的名字，如：/etc/my.cnf.bak，否则，该文件会干扰源码安装的MySQL的正确配置，造成无法启动。
在使用"yum update"更新系统后，需要检查下/etc目录下是否会多出一个my.cnf，如果多出，将它重命名成别的。否则，MySQL将使用这个配置文件启动，可能造成无法正常启动等问题。
--------------------------------------------------------------------------------------------------------------------------------------------------------------
启动MySQL
添加服务，拷贝服务脚本到init.d目录，并设置开机启动
[root@host-test-huanqiu mysql]# cp support-files/mysql.server /etc/init.d/mysql
[root@host-test-huanqiu mysql]# chkconfig mysql on
[root@host-test-huanqiu mysql]# service mysql start //启动前，要先配置my.cnf文件（如下）， 并确保/data/mysql/data和/usr/local/mysql的mysql权限
|
1
2
3
4
5
6
7
8
9
10
11
|
如果启动报错：
[root@slave-server mysql]
# service mysql start
Starting MySQL.Logging to
'/data/mysql/data/mysql-error.log'
.
170416 12:55:28 mysqld_safe Directory
'/usr/local/mysql/var'
for
UNIX socket
file
don't exists.
ERROR! The server quit without updating PID
file
(
/data/mysql/data/mysql
.pid).
解决办法：
[root@slave-server mysql]
# mkdir ／usr/local/mysql/var
[root@slave-server mysql]
# chown -R mysql:mysql /usr/local/mysql
[root@slave-server mysql]
# service mysql start
Starting MySQL.. SUCCESS!
|
配置用户和环境变量
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
MySQL启动成功后，root默认没有密码，需要设置root密码，设置之前，需要先设置PATH，否则不能直接调用mysql
修改
/etc/profile
文件，在文件末尾添加
[root@host-
test
-huanqiu mysql]
# vim /etc/profile
........
export
PATH=$PATH:
/usr/local/mysql/bin
[root@host-
test
-huanqiu mysql]
# source /etc/profile
到此，就可以直接mysql登陆了
[root@host-
test
-huanqiu mysql]
# mysql -uroot
.......
mysql>
select
version();
+-----------+
| version() |
+-----------+
| 5.6.33 |
+-----------+
1 row
in
set
(0.00 sec)
|
登陆mysql，若是报错如下：
|
1
2
3
4
5
6
|
[root@test2-235 mysql]
# mysql
ERROR 2002 (HY000): Can
't connect to local MySQL server through socket '
/var/lib/mysql/mysql
.sock' (2)
解决办法
[root@test2-235 mysql]
# mkdir /var/lib/mysql/
[root@test2-235 mysql]
# ln -s /usr/local/mysql/var/mysql.sock /var/lib/mysql/mysql.sock
|
修改root密码，执行命令如下
|
1
2
3
4
5
6
7
8
9
10
|
mysql> SET PASSWORD = PASSWORD(
'123456'
);
若要设置root用户可以远程访问，执行
mysql> GRANT ALL PRIVILEGES ON *.* TO
'root'
@
'%'
IDENTIFIED BY
'password'
WITH GRANT OPTION;
使授权立即生效
mysql> FLUSH PRIVILEGES;
也可以使用mysqladmin命令设置mysql密码
[root@host-
test
-huanqiu mysql]
# mysqladmin password 123456 #安装mysql后第一次设置密码
[root@host-
test
-huanqiu mysql]
# mysqladmin -p 旧密码 password 新密码 #重置密码
|
最后配置防火墙
|
1
2
3
4
|
默认防火墙的3306端口默认没有开启，若要远程访问，需要开启这个端口.也可以做下白名单，比如只允许192.168.1.0
/24
网段的客户机访问本机的mysql。
[root@host-
test
-huanqiu mysql]
# vim /etc/sysconfig/iptables
.......
-A INPUT -s 192.168.1.0
/24
-p tcp -m tcp -m state --state NEW -dport 3306 -j ACCEPT
|
----------------------------------------------------------------------------------------------------------------------------------
下面贴下mysql6.x的my.cnf配置
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
|
[client]
port = 3306
socket =
/usr/local/mysql/var/mysql
.sock
[mysqld]
port = 3306
socket =
/usr/local/mysql/var/mysql
.sock
basedir =
/usr/local/mysql/
datadir =
/data/mysql/data
pid-
file
=
/data/mysql/data/mysql
.pid
user = mysql
bind-address = 0.0.0.0
server-
id
= 1
sync_binlog=1
log_bin = mysql-bin
skip-name-resolve
back_log = 600
max_connections = 3000
max_connect_errors = 3000
table_open_cache = 512
max_allowed_packet = 16M
binlog_cache_size = 16M
max_heap_table_size = 16M
tmp_table_size = 256M
read_buffer_size = 1024M
read_rnd_buffer_size = 1024M
sort_buffer_size = 1024M
join_buffer_size = 1024M
key_buffer_size = 8192M
thread_cache_size = 8
query_cache_size = 512M
query_cache_limit = 1024M
ft_min_word_len = 4
binlog_format = mixed
expire_logs_days = 30
log_error =
/data/mysql/data/mysql-error
.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file =
/data/mysql/data/mysql-slow
.log
performance_schema = 0
explicit_defaults_for_timestamp
skip-external-locking
default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 4096M
#这个参数一般设置为服务器最大内存的60%
innodb_write_io_threads = 1000
innodb_read_io_threads = 1000
innodb_thread_concurrency = 8
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 4M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 2G
myisam_repair_threads = 1
interactive_timeout = 28800
wait_timeout = 28800
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
port = 3306
|
针对上面的部署过程，为了便于以后安装mysql环境，编写了下面的安装脚本，直接运行这个脚本就能自动化构建mysql环境了。
[root@slave-server ~]# cat mysql.sh （下载地址：https://pan.baidu.com/s/1gfEKC9d 密码：6ec3）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
|
#!/bin/bash
#卸载系统自带的Mysql
/bin/rpm
-e $(
/bin/rpm
-qa |
grep
mysql|
xargs
) --nodeps
/bin/rm
-f
/etc/my
.cnf
#安装编译代码需要的包
/usr/bin/yum
-y
install
make
gcc
-c++ cmake bison-devel ncurses-devel
#编译安装mysql5.6
/usr/sbin/groupadd
mysql
/usr/sbin/useradd
-g mysql mysql -M -s
/sbin/nologin
cd
/usr/local/src
wget -c http:
//ftp
.ntu.edu.tw
/MySQL/Downloads/MySQL-5
.6
/mysql-5
.6.37.
tar
.gz
/bin/tar
-zxvf mysql-5.6.37.
tar
.gz
cd
mysql-5.6.37/
/usr/bin/cmake
-DCMAKE_INSTALL_PREFIX=
/usr/local/mysql
-DMYSQL_DATADIR=
/data/mysql/data
-DSYSCONFDIR=
/etc
-DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_UNIX_ADDR=
/var/lib/mysql/mysql
.sock -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
make
&&
make
install
#修改/usr/local/mysql权限
mkdir
-p
/data/mysql/data
/bin/chown
-R mysql:mysql
/usr/local/mysql
/bin/chown
-R mysql:mysql
/data/mysql/data
#执行初始化配置脚本，创建系统自带的数据库和表
/usr/local/mysql/scripts/mysql_install_db
--basedir=
/usr/local/mysql
--datadir=
/data/mysql/data
--user=mysql
#配置my.cnf
cat
>
/usr/local/mysql/my
.cnf << EOF
[client]
port = 3306
socket =
/usr/local/mysql/var/mysql
.sock
[mysqld]
port = 3306
socket =
/usr/local/mysql/var/mysql
.sock
basedir =
/usr/local/mysql/
datadir =
/data/mysql/data
pid-
file
=
/data/mysql/data/mysql
.pid
user = mysql
bind-address = 0.0.0.0
server-
id
= 1
sync_binlog=1
log_bin = mysql-bin
skip-name-resolve
#skip-networking
back_log = 600
max_connections = 3000
max_connect_errors = 3000
##open_files_limit = 65535
table_open_cache = 512
max_allowed_packet = 16M
binlog_cache_size = 16M
max_heap_table_size = 16M
tmp_table_size = 256M
read_buffer_size = 1024M
read_rnd_buffer_size = 1024M
sort_buffer_size = 1024M
join_buffer_size = 1024M
key_buffer_size = 8192M
thread_cache_size = 8
query_cache_size = 512M
query_cache_limit = 1024M
ft_min_word_len = 4
binlog_format = mixed
expire_logs_days = 30
log_error =
/data/mysql/data/mysql-error
.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file =
/data/mysql/data/mysql-slow
.log
performance_schema = 0
explicit_defaults_for_timestamp
##lower_case_table_names = 1
skip-external-locking
default_storage_engine = InnoDB
##default-storage-engine = MyISAM
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 4096M
innodb_write_io_threads = 1000
innodb_read_io_threads = 1000
innodb_thread_concurrency = 8
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 4M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 2G
myisam_repair_threads = 1
interactive_timeout = 28800
wait_timeout = 28800
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
port = 3306
EOF
#启动mysql服务
cd
/usr/local/mysql
/bin/mkdir
var
/bin/chown
-R mysql.mysql var
cp
support-files
/mysql
.server
/etc/init
.d
/mysql
/sbin/chkconfig
mysql on
service mysql start
#设置环境变量
echo
"export PATH=$PATH:/usr/local/mysql/bin"
>>
/etc/profile
source
/etc/profile
#设置mysql登陆密码,初始密码为123456
/bin/mkdir
-p
/var/lib/mysql
ln
-s
/usr/local/mysql/var/mysql
.sock
/var/lib/mysql/mysql
.sock
mysql -e
"SET PASSWORD = PASSWORD('123456');"
mysql -p123456 -e
"GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;"
mysql -p123456 -e
"FLUSH PRIVILEGES;"
|
