目录
1、系统环境
2、准备用户及数据存储目录
3、编译安装mysql
4、mysql数据库的初始安全配置
5、mysql管理员密码丢失的处理
1、系统环境[[email protected]_server ~]# cat /etc/issue
CentOS release 6.4 (Final)
Kernel \r on an \m
[[email protected]_server ~]# uname -r
2.6.32-358.el6.x86_64
在安装系统时已安装过“Development tools”及“Server Platform Development”两个开发包组。
[[email protected]_server ~]# yum -y install cmake #mysql5.5不再是make来编译,而是采用cmake
[[email protected]_server software]# pwd
/root/software
[[email protected]_server software]# ls
mysql-5.5.36.tar.gz
[[email protected]_server software]# tar xf mysql-5.5.36.tar.gz
[[email protected]_server software]# ls
mysql-5.5.36 mysql-5.5.36.tar.gz
如果系统自带了rpm包安装过的mysql数据库那先卸载:[[email protected]_server software]# rpm -qa mysql
mysql-5.1.73-3.el6_5.x86_64
[[email protected]_server software]# rpm -e --nodeps mysql
2、准备用户及数据存放目录
2.1、创建用户[[email protected]_server software]# useradd -r mysql
useradd: user ‘mysql‘ already exists #mysql用户已存在
[[email protected]_server software]# id mysql
uid=27(mysql) gid=27(mysql) groups=27(mysql)
2.2、创建数据存放目录[[email protected]_server software]# mkdir -pv /mydata/data #在生产环境上就建立在LVM卷或存储设备映射过来的卷上
mkdir: created directory `/mydata/data‘
[[email protected]_server software]# chown -R mysql.mysql /mydata/data #修改数据目录的属主与属组
3、编译安装[[email protected]_server software]# cd mysql-5.5.36
[[email protected]_server mysql-5.5.36]# cmake . -DCMAKE_INSTALL_PREFIX=/opt/lamp/mysql55/ -DMYSQL_DATADIR=/mydata/data -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DSYSCONFDIR=/etc -DWITH_INNOBASE_STORAGE_ENGINE=ON -DWITH_ARCHIVE_STORAGE_ENGINE=ON -DWITH_BLACKHOLE_STORAGE_ENGINE=ON -DWITH_READLINE=ON -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_LIBWRAP=OFF -DDEFAULT_CHARSET=utf8 -DEXTRA=all -DDEFAULT_COLLATION=utf8_general_ci
[[email protected]_server mysql-5.5.36]# make
[[email protected]_server mysql-5.5.36]# make install
[[email protected]_server mysql-5.5.36]# cd /opt/lamp/mysql55/
[[email protected]_server mysql55]# chgrp -R mysql .
[email protected]_server mysql55]# cp support-files/my-large.cnf /etc/my.cnf #提供配置文件
cp: overwrite `/etc/my.cnf‘? y
[[email protected]_server mysql55]# vim /etc/my.cnf #在[mysqld]字段下加入下边的参数
datadir = /mydata/data
[[email protected]_server mysql55]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld 夹 #提供服务脚本
[[email protected]_server mysql55]# chmod +x /etc/rc.d/init.d/mysqld
[[email protected]_server mysql55]# ./scripts/mysql_install_db --user=mysql --datadir=/mydata/data #初始化数据库,看见两个“OK”就初始化成功
[[email protected]_server mysql55]# ls /mydata/data #在没有启动mysqld服务时此目录有以下这些文件
mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.index performance_schema test
[[email protected]_server mysql55]# service mysqld start #启动服务
Starting MySQL... SUCCESS!
[[email protected]_server mysql55]# netstat -tnl #查看3306端口是否监听
[[email protected]_server mysql55]# vim /etc/profile.d/mysql.sh #导出二进制文件
export PATH=/opt/lamp/mysql55/bin:$PATH
[[email protected]_server mysql55]# source /etc/profile.d/mysql.sh
[[email protected]_server mysql55]# mysql #测试能否连入mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.36-log Source distribution
Copyright (c) 2000, 2014, 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>
[[email protected]_server mysql55]# ls /mydata/data #启动mysqld服务后此目录下就会新增一些文件
db_server.err ibdata1 ib_logfile1 mysql-bin.000001 mysql-bin.000003 performance_schema
db_server.pid ib_logfile0 mysql mysql-bin.000002 mysql-bin.index test
4、mysql数据库的初始安全配置
4.1、删除匿名用户mysql> select user,host,password from mysql.user; #查看数据库中的用户信息
+------+------------+----------+
| user | host | password |
+------+------------+----------+
| root | localhost | |
| root | db\_server | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | db\_server | |
+------+------------+----------+
6 rows in set (0.01 sec)
mysql> drop user ‘‘@‘localhost‘; #删除一个匿名用户
mysql> drop user ‘‘@‘db\_server‘; #删除另一个匿名用户
如果此数据库没有规划让IPV6的地址来访问,那可删除root用户以IPV6来访问数据库,所以如下操作:mysql> drop user ‘root‘@‘::1‘;
mysql> select user,host,password from mysql.user; #用户删除后的用户信息
+------+------------+----------+
| user | host | password |
+------+------------+----------+
| root | localhost | |
| root | db\_server | |
| root | 127.0.0.1 | |
+------+------------+----------+
3 rows in set (0.00 sec)
4.2、修改root用户密码
以上操作后只剩下‘root’用户各三个主机名组合成了三种接入mysql的用户,这三种表示对mysql来说是三个不同的用户,如果这三个用户的接入密码都相同,那可直接用sql语言调用“password()”函数来统一修改user表中的“password”这个字段的值,如下操作:mysql> update user set password=password(‘111111‘) where user=‘root‘;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select user,host,password from mysql.user; #查看密码是否修改成功
+------+------------+-------------------------------------------+
| user | host | password |
+------+------------+-------------------------------------------+
| root | localhost | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
| root | db\_server | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
| root | 127.0.0.1 | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
+------+------------+-------------------------------------------+
3 rows in set (0.02 sec)
mysql> flush privileges; #刷新授权表,把用户修改后的信息加载到内存中
mysql> \q
Bye
[[email protected]_server mysql55]# mysql #测试
ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: NO)
[[email protected]_server mysql55]# mysql -uroot -p111111
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.36-log Source distribution
Copyright (c) 2000, 2014, 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>
4.3、为应用程序提供专门的授权帐号
mysql的root用户只能用于数据库的管理,不能用于应用,比如一个php的网站程序需要与mysql交互,那应该创建一个用户并赋予相应的权限,专门与网站进行交互,一定不要用root用户。
5、mysql管理员密码丢失的处理[[email protected]_server mysql55]# pwd
/opt/lamp/mysql55
[[email protected]_server mysql55]# bin/mysqld_safe --skip-grant --skip-networking &
解释:
--skip-grant #表示启用时跳过授权表
--skip-networking # 表示跳过网络,客户端与mysqld通信不用tcp/ip方式,即不监听本地的3306端口,而只能用uninx sock方式通信,如果不加此参数,那会监听本地的3306端口,从安全的角度考虑这是不安全的。
[[email protected]_server ~]# mysql #无密码登陆mysqld
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.36-log Source distribution
Copyright (c) 2000, 2014, 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> select user,host,password from mysql.user;
+------+------------+-------------------------------------------+
| user | host | password |
+------+------------+-------------------------------------------+
| root | localhost | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
| root | db\_server | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
| root | 127.0.0.1 | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
+------+------------+-------------------------------------------+
3 rows in set (0.01 sec)
mysql> update mysql.user set password=password(‘123456‘) where user=‘root‘; #修改密码
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
[[email protected]_server ~]# service mysqld restart #重新启动mysql
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
[[email protected]_server ~]# mysql -uroot -p123456 #之前是的密码是“111111”,现在已修改成了“123456”
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.36-log Source distribution
Copyright (c) 2000, 2014, 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>
原文:http://zhaochj.blog.51cto.com/368705/1627142