编译安装MySQL

为什么选择MySQL数据库?

  毫无疑问,绝大多数的使用linux操作系统的大中小型互联网网站都在使用MySQL作为其后端的数据库存储,从大型的BAT门户,到电商平台,分类门户等无一例都使用MySQL数据库。

My Sql 数据库优点:

1、性能卓越,服务稳定,很少出现异常宕机

2、开放源代码且无版权约束,自主性及使用成本低

3、历史悠久,社区及用户非常活跃,遇到问题,可以寻求帮助

4、软件体积小,安排使用简单,并且易于维护,安装及维护成本低

5、品牌口碑效应,使得企业无需考虑直接用之,LAMP,LEMP流行架构

6、支持多操作系统,提供多种API接口,支持多种开发语言,特别对流行的PHP语言有很好支持

linux软件的安装方式:

1、  yum/rpm:简单 快,无法定制。

2、  编译安装:比较复杂,速度慢,可定制。

./configure;make;make install   gmake;gmake insall  

3、  二进制包*****

直接解压就能用(类似于绿色软件,无需安装) 简单,快,不好定制。

测试环境:   

系统:Centos 6.5   mysql软件:mysql-5.1.72.tar.gz

下面我们选择编译安装方法:

1、创建mysql用户

[root@bqh-119 ~]# groupadd mysql
[root@bqh-119 ~]# useradd mysql -g mysql -M -s /sbin/nologin
[root@bqh-119 ~]# id mysql
uid=502(mysql) gid=502(mysql) 组=502(mysql)

2、下载或上传mysql软件包并解压 

获取mysql软件:https://dev.mysql.com/downloads/mysql/

[root@bqh-119 tools]# tarxf mysql-5.1.72.tar.gz 
[root@bqh-119 tools]# ll
总用量 13328
drwxrwxrwx 32  7155 wheel      4096 7月  18 21:55 mysql-5.1.72
-rw-r--r--  1 root  root   24044338 7月  18 21:41 mysql-5.1.72.tar.gz
[root@bqh-119 tools]# cd mysql-5.1.72
[root@bqh-119 mysql-5.1.72]# 

3、编译安装mysql  

[root@bqh-119 mysql-5.1.72]# 
./configure \
--prefix=/application/mysql5.1.72 \
--with-unix-socket-path=/application/mysql5.1.72/tmp/mysql.sock \
--localstatedir=/application/mysql5.1.72/data \
--enable-assembler \
--enable-thread-safe-client \
--with-mysqld-user=mysql \
--with-big-tables \
--without-debug \
--with-pthread \
--enable-assembler \
--with-extra-charsets=complex \
--with-readline \
--with-ssl \
--with-embedded-server \
--enable-local-infile \
--with-plugins=partition,innobase \
--with-mysqld-ldflags=-all-static \
--with-client-ldflags=-all-static 

此处省略......

checking for nl_langinfo and CODESET... yes
checking for wchar_t in wchar.h... yes
checking for wctype_t in wctype.h... yes
checking for wint_t in wctype.h... yes
checking for tgetent in -lncursesw... no
checking for tgetent in -lncurses... no
checking for tgetent in -lcurses... no
checking for tgetent in -ltermcap... no
checking for tgetent in -ltinfo... no
checking for termcap functions library... configure: error: No curses/termcap library found

上面报错解决方法:

[root@bqh-119 mysql-5.1.72]# yum -y install ncurses-devel

4、然后再从新编译一下,出现如下图就可以接着make && make install

5、然后再执行:

[root@bqh-119 mysql-5.1.72]# make && make install #make静态编译生成mysqld的执行文件;make install安装mysql

提示:也可以根据cpu核数来加-j参数来加快mysql的编译,例如:make -j 4

出现下图结果表示mysql安装完成:

6、做软连接(隐藏版本号安全)

[root@bqh-119 mysql-5.1.72]# ln -s /application/mysql5.1.72/ /application/mysql
[root@bqh-119 mysql-5.1.72]# ll /application/mysql
lrwxrwxrwx 1 root root 25 7月  18 22:27 /application/mysql -> /application/mysql5.1.72/
[root@bqh-119 mysql-5.1.72]# ll /application/mysql/
总用量 28
drwxr-xr-x  2 root root 4096 7月  18 22:22 bin
drwxr-xr-x  3 root root 4096 7月  18 22:22 include
drwxr-xr-x  3 root root 4096 7月  18 22:22 lib
drwxr-xr-x  2 root root 4096 7月  18 22:22 libexec
drwxr-xr-x 10 root root 4096 7月  18 22:22 mysql-test
drwxr-xr-x  6 root root 4096 7月  18 22:22 share
drwxr-xr-x  5 root root 4096 7月  18 22:22 sql-bench

7、初始化数据库:

[root@bqh-119 mysql-5.1.72]# /application/mysql/bin/mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data/ --user=mysql

解释:

/application/mysql/bin/mysql_install_db   #指定安装的命令

--basedir   #指定mysql安装的目录

--datadir   #存放数据文件的目录

--user     #mysql用户

8、授权mysql管理数据库文件:

[root@bqh-119 mysql-5.1.72]# chown -R mysql.mysql /application/mysql/

9、生成mysql配置文件:

[root@bqh-119 mysql-5.1.72]# ll /application/mysql/share/mysql/*.cnf
-rw-r--r-- 1 mysql mysql  4746 7月  18 22:22 /application/mysql/share/mysql/my-huge.cnf
-rw-r--r-- 1 mysql mysql 19779 7月  18 22:22 /application/mysql/share/mysql/my-innodb-heavy-4G.cnf
-rw-r--r-- 1 mysql mysql  4720 7月  18 22:22 /application/mysql/share/mysql/my-large.cnf
-rw-r--r-- 1 mysql mysql  4731 7月  18 22:22 /application/mysql/share/mysql/my-medium.cnf
-rw-r--r-- 1 mysql mysql  2499 7月  18 22:22 /application/mysql/share/mysql/my-small.cnf
[root@bqh-119 mysql-5.1.72]# \cp /application/mysql/share/mysql/my-small.cnf /etc/my.cnf

10、配置启动mysql:

[root@bqh-119 mysql-5.1.72]# sed -i 's#/usr/local/mysql#/application/mysql#g' /application/mysql/bin/mysqld_safe /application/mysql/share/mysql/mysql.server 
[root@bqh-119 mysql-5.1.72]# cp /application/mysql/share/mysql/mysql.server /etc/init.d/mysqld #将生成的启动脚本拷贝到init.d目录下
[root@bqh-119 mysql-5.1.72]# chmod +x /etc/init.d/mysqld  #授予可执行权限
[root@bqh-119 mysql-5.1.72]# lsof -i:3306
[root@bqh-119 mysql-5.1.72]# /etc/init.d/mysqld start #启动mysql服务
Starting MySQL. SUCCESS! 
[root@bqh-119 mysql-5.1.72]# lsof -i:3306 #查询mysql服务是否开启
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  51239 mysql   10u  IPv4  65387      0t0  TCP *:mysql (LISTEN)

11、配置环境变量:

[root@bqh-119 mysql-5.1.72]# echo -e '#mysql \nPATH="/application/mysql/bin:$PATH"' >>/etc/profile
[root@bqh-119 mysql-5.1.72]# source /etc/profile
[root@bqh-119 mysql-5.1.72]# tail -2 /etc/profile
#mysql
PATH="/application/mysql/bin:$PATH"
[root@bqh-119 mysql-5.1.72]# which mysql
/application/mysql/bin/mysql

12、登陆测试及设置、更改mysql密码:

[root@bqh-119 mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.72 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql> quit
Bye
[root@bqh-119 mysql]# mysqladmin -uroot password "123456" #设置密码
[root@bqh-119 mysql]# mysql -uroot -p123456  #密码登录
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.72 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> quit
Bye

13、为了安全起见,我们在登陆时,采用交互式登陆:

[root@bqh-119 mysql]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.72 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

如果忘记登录密码了可以用一下方法修改密码解决:

[root@bqh-119 ~]# killall mysqld
[root@bqh-119 ~]# killall mysqld -9
mysqld: 没有进程被杀死
[root@bqh-119 ~]# /application/mysql/bin/mysqld_safe --skip-grant-table &
[1] 51534
[root@bqh-119 ~]# 190719 00:41:07 mysqld_safe Logging to '/application/mysql5.1.72/data/bqh-119.err'.
190719 00:41:07 mysqld_safe Starting mysqld daemon with databases from /application/mysql5.1.72/data

[root@bqh-119 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.72 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> update mysql.user set password=PASSWORD("bqh123") where host='localhost' and user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[root@bqh-119 ~]# killall mysqld
[root@bqh-119 ~]# killall mysqld
mysqld: 没有进程被杀死
[root@bqh-119 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS! 
[root@bqh-119 ~]# mysql -uroot -pbqh123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.72 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

注:正常的杀死进程可以:

[root@bqh-119 ~]# mysqladmin -uroot -pbqh123 shutdown

[root@bqh-119 ~]# 
[root@bqh-119 ~]# netstat -lntup|grep mysql
[root@bqh-119 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS! 
[root@bqh-119 ~]# netstat -lntup|grep mysql
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      51970/mysqld 

14、安全优化:删除不必要的库和用户

删除test库:dro database test;

删除无用用户(保留root和localhost)

drop user '用户'@‘主机’;

注意:主机大写或者特殊字符删不了,需用

delete from mysql.user where user='用户' and host='主机大写或特殊字符';

如果不小心把这两个也给删除了,恢复方法:

grant all on *.* to ‘root’@localhostt identified by ‘密码’ with grant option;

flush privileges;    #刷新权限

mysql简单的命令:

查看所有库:show databases;   

切库:use mysql;

查看用户列表:select user,host from mysql.user 

查看当前用户:select user();

查看当前所在库:select database();

删除数据库:drop database 库名;

删除用户:drop user '用户'@'主机';

posted @ 2019-07-18 23:56  南清风  阅读(898)  评论(0编辑  收藏  举报