Linux 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、  二进制包*****

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

下面我们选择二进制包方法：

1、创建mysql用户

[root@lamp01 tools]# id mysql
id: mysql：无此用户
[root@lamp01 tools]# groupadd mysql
[root@lamp01 tools]# useradd -s /sbin/nologin -g mysql -m mysql
[root@lamp01 tools]# id mysql
uid=503(mysql) gid=503(mysql) 组=503(mysql)
[root@lamp01 tools]#

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

[root@lamp01 tools]# tar xf ./mysql-5.5.32-linux2.6-x86_64.tar.gz
[root@lamp01 tools]# ll
总用量 182352
drwxr-xr-x. 13 root root      4096 1月  10 21:54 mysql-5.5.32-linux2.6-x86_64
-rw-r--r--.  1 root root 186722932 1月  10 21:51 mysql-5.5.32-linux2.6-x86_64.tar.gz
3、将解压的文件移动到安装目录下，并做软连接（隐藏版本号安全）

[root@lamp01 tools]# mv mysql-5.5.32-linux2.6-x86_64 /application/mysql-5.5.32
[root@lamp01 tools]# ln -s /application/mysql-5.5.32/ /application/mysql
[root@lamp01 tools]# ll /application/总用量 8l
rwxrwxrwx. 1 root root 26 1月 10 21:57 mysql -> /application/mysql-5.5.32/
drwxr-xr-x. 13 root root 4096 1月 10 21:54 mysql-5.5.32
lrwxrwxrwx. 1 root root 25 12月 19 03:30 nginx -> /application/nginx-1.6.3/
drwxr-xr-x. 11 root root 4096 12月 20 21:12 nginx-1.6.3
[root@lamp01 tools]#

操作到此步骤相当于编译安装make install 之后。

4、初始化数据库

[root@lamp01 tools]# /application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data/ --user=mysql
installing mysql system tables...
ok
filling help tables...
ok
解释：

/application/mysql/scripts/mysql_install_db   //指定安装的命令

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

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

--user     //mysql用户

5、授权mysql管理数据库文件 

[root@lamp01 tools]# chown -r mysql.mysql /application/mysql/
[root@lamp01 tools]# cd /application/mysql/
[root@lamp01 mysql]# ll support-files/*.cnf
-rw-r--r--. 1 mysql mysql  4691 6月  19 2013 support-files/my-huge.cnf
-rw-r--r--. 1 mysql mysql 19759 6月  19 2013 support-files/my-innodb-heavy-4g.cnf
-rw-r--r--. 1 mysql mysql  4665 6月  19 2013 support-files/my-large.cnf
-rw-r--r--. 1 mysql mysql  4676 6月  19 2013 support-files/my-medium.cnf
-rw-r--r--. 1 mysql mysql  2840 6月  19 2013 support-files/my-small.cnf
[root@lamp01 mysql]#
6、生成mysql配置文件

\cp /application/mysql/support-files/my-small.cnf  /etc/my.cnf
7、配置启动mysql

[root@lamp01 mysql]# sed -i 's#/usr/local/mysql#/application/mysql#g' /application/mysql/bin/mysqld_safe
[root@lamp01 mysql]# /application/mysql/bin/mysqld_safe &
[1] 1581
[root@lamp01 mysql]# 190110 22:29:53 mysqld_safe logging to '/application/mysql/data/lamp0
1.err'.190110 22:29:53 mysqld_safe starting mysqld daemon with databases from /application/mysql/
data
[root@lamp01 mysql]# lsof -i :3306
command  pid  user   fd   type device size/off node name
mysqld  1799 mysql   10u  ipv4  21901      0t0  tcp *:mysql (listen)
[root@lamp01 mysql]#

8、配置环境变量

vi /etc/profile

path="/application/mysql/bin:$path"

source /etc/profile

也可以把mysql命令放到已经有环境变量的路径里

[root@lamp01 mysql]# echo $path
/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
[root@lamp01 mysql]# cp /application/mysql/bin/* /usr/local/sbin/
[root@lamp01 mysql]# which mysql
/usr/local/sbin/mysql
[root@lamp01 mysql]#
9、登陆测试

[root@lamp01 mysql]# mysql
welcome to the mysql monitor.  commands end with ; or \g.
your mysql connection id is 1
server version: 5.5.32 mysql community server (gpl)

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>

出现以上提示符表示mysql已经安装ok了。如果安装时或者工作中有问题，可以看错误日志分析问题原因：

cat /application/mysql/data/mysql-server.err

10、设置及更改mysql密码

[root@lamp01 mysql]# mysqladmin -uroot password "123456"
[root@lamp01 mysql]# mysql
error 1045 (28000): access denied for user 'root'@'localhost' (using password: no)
[root@lamp01 mysql]# mysql -uroot -p123456
welcome to the mysql monitor.  commands end with ; or \g.
your mysql connection id is 4
server version: 5.5.32 mysql community server (gpl)

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@lamp01 mysql]# mysqladmin -uroot -p123456 password "bqh123"
[root@lamp01 mysql]# mysql -uroot -p123456
error 1045 (28000): access denied for user 'root'@'localhost' (using password: yes)
[root@lamp01 mysql]# mysql -uroot -pbqh123
welcome to the mysql monitor.  commands end with ; or \g.
your mysql connection id is 7
server version: 5.5.32 mysql community server (gpl)

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@lamp01 mysql]# mysql -uroot -p
enter password:
welcome to the mysql monitor.  commands end with ; or \g.
your mysql connection id is 8
server version: 5.5.32 mysql community server (gpl)

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>

11、安全优化：删除不必要的库和用户

删除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 '用户'@'主机';