为什么选择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 PATH="/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 '用户'@'主机';