Mysql安装步骤:
系统版本:
[root@db01 ~]# cat /etc/redhat-release
CentOS release 6.8 (Final)
1、上传MySQL二进制安装包:
https://pan.baidu.com/disk/home#category/type=6&vmode=list ###-网盘下载地址
[root@db01 ~]# cd /home/oldboy/tools/ [root@db01 tools]# rz -y [root@db01 tools]# ll -h 总用量 178M-rw-r--r-- 1 root root 178M 5月 16 2016 mysql-5.5.49-linux2.6-x86_64.tar.gz
2、检查当前系统中是否存在mysql用户,如果不存在就新建mysql用户,然后解压缩mysql的安装包:
[root@db01 tools]# useradd -s /sbin/nologin -M mysql [root@db01 tools]# id mysql uid=501(mysql) gid=501(mysql) 组=501(mysql) [root@db01 tools]# tar xf mysql-5.5.49-linux2.6-x86_64.tar.gz
# 由于是二进制安装包,不需要进行make和make install
# 只要把解压过后的目录移动到/application/下即可
[root@db01 tools]# mkdir -p /application [root@db01 tools]# mv mysql-5.5.49-linux2.6-x86_64 /application/mysql-5.5.49 [root@db01 tools]# cd /application/ [root@db01 application]# ln -s /application/mysql-5.5.49/ /application/mysql [root@db01 application]# ls -l 总用量 8l rwxrwxrwx 1 root root 26 5月 30 23:11 mysql -> /application/mysql-5.5.49/ drwxr-xr-x 13 root root 4096 5月 30 23:09 mysql-5.5.49
# 授权mysql管理数据库文件
# 授权到底是在初始化后还是在初始化前,需要进行测试
[root@db01 application]# chown -R mysql.mysql /application/mysql/
3、初始化mysql数据库
# 初始化过程中出现2个OK就表示成功,出现error要解决,出现warnnig可以忽略
# 如果初始化失败,需要删除/application/mysql/data/目录下所有数据文件
# 之后解决报错,再次初始化
# 如果/tmp目录权限不对,也会导致mysql初始化失败,/tmp目录权限为1777
[root@db01 application]# /application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/application/mysql/data/ --user=mysql Installing MySQL system tables...OK Fillinghelp tables...OK Tostart mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASEREMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! Todo so, start the server, then issue the following commands:
4、生成mysql配置文件
# mysql配置文件位置
# 一般虚拟机使用my-medium.cnf和my-small.cnf
# 学习参数使用my-innodb-heavy-4G.cnf
[root@db01 application]# cd /application/mysql/support-files/ [root@db01 support-files]# ll 总用量 96-rw-r--r-- 1 mysql mysql 4691 Jun 19 2013 my-huge.cnf # 巨大配置 -rw-r--r-- 1 mysql mysql 19759 Jun 19 2013 my-innodb-heavy-4G.cnf # 重大配置 -rw-r--r-- 1 mysql mysql 4665 Jun 19 2013 my-large.cnf # 比较大配置 -rw-r--r-- 1 mysql mysql 4676 Jun 19 2013 my-medium.cnf # 最小配置 -rw-r--r-- 1 mysql mysql 2840 Jun 19 2013 my-small.cnf # 比较小配置
# 覆盖系统自带的mysql配置文件
[root@db01 support-files]# cp my-small.cnf /etc/my.cnf cp:是否覆盖"/etc/my.cnf"? y
5、更改mysql默认启动文件里的目录位置
由于是我们自定义mysql二进制包安装路径在/application/mysql目录下,但是,二进制默认的安装路径是/usr/local/mysql,这可以通过mysql启动文件看出来:
那么我们需要将该启动文件里的/usr/local/mysql变成/application/mysql目录。
[root@db01 support-files]# sed -i ‘s#/usr/local/mysql#/application/mysql#g‘ /application/mysql/bin/mysqld_safe
之后,后台启动mysql
[root@db01 support-files]# /application/mysql/bin/mysqld_safe & [1] 1944 15102109:37:28 mysqld_safe Logging to ‘/application/mysql/data/db01.err‘. 15102109:37:28 mysqld_safe Starting mysqld daemon with databases from/application/mysql/data
# 按下回车键
检查mysql的3306端口:
[root@db01 support-files]# lsof -i :3306 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAMEmysqld 2162 mysql 10u IPv4 18922 0t0 TCP *:mysql (LISTEN)
6、配置环境变量
[root@db01 support-files]# tail -1 /etc/profile export PATH=/application/mysql/bin:$PATH [root@db01 support-files]# source /etc/profile [root@db01 support-files]# which mysql /application/mysql/bin/mysql
7、登录mysql
[root@db01 support-files]# mysql Welcome to the MySQL monitor. Commands end with ; or \g...mysql> exit Bye
8、安装过程的排错
# mysql的错误日志,在/application/mysql/data目录下以主机名命名的err文件
[root@db01 data]# pwd /application/mysql/data [root@db01 data]# ll总用量 28692-rw-r----- 1 mysql root 1995 5月 29 19:15 db01.err
9、配置科学启动mysql
[root@db01 support-files]# pwd /application/mysql/support-files
# mysql.server是一个启动脚本
[root@db01 support-files]# cp mysql.server /etc/init.d/mysqld
# 再次改变mysql的默认路径
[root@db01 support-files]# sed -i ‘s#/usr/local/mysql#/application/mysql#g‘/etc/init.d/mysqld
# 增加执行权限
[root@db01 support-files]# chmod +x /etc/init.d/mysqld
# 停止之前启动的mysql,直到提示“mysqld: no process killed”
# 使用killall停止mysql,生产环境不要使用pkill停止mysql,影响太大
[root@db01 support-files]# killall mysqld 151021 10:01:47 mysqld_safe mysqld from pid file/application/mysql/data/db01.pid ended [1]+ Done /application/mysql/bin/mysqld_safe [root@db01 support-files]# [root@db01 support-files]# killall mysqld mysqld:no process killed
# 确定mysql已经停止服务
[root@db01 support-files]# lsof -i :3306 [root@db01 support-files]#
# 重新启动mysql服务
[root@db01 support-files]# /etc/init.d/mysqld startStartingMySQL.. SUCCESS! [root@db01 support-files]#
# 检查端口
[root@db01 support-files]# lsof -i :3306 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 2872 mysql 10u IPv4 23529 0t0 TCP *:mysql (LISTEN) [root@db01 support-files]#
# 配置开机自启动mysql,也可以放在rc.local里
[root@db01 support-files]# chkconfig mysqld on [root@db01 support-files]# [root@db01 support-files]# chkconfig --list mysqld mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off [root@db01 support-files]#
# 再次登录mysql
[root@db01 support-files]# mysql Welcometo the MySQL monitor. Commands end with; or \g... mysql>
10、配置mysql登录密码
# 初始化时显示的设置密码和修改密码语句
/application/mysql/bin/mysqladmin-u root password ‘new-password‘ /application/mysql/bin/mysqladmin-u root -h db01 password ‘new-password‘
# 设置密码
[root@db01 support-files]# mysqladmin -uroot password 123456 [root@db01 support-files]#
# 不用密码登录报错
[root@db01 support-files]# mysql ERROR1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: NO) [root@db01 support-files]#
# 使用用户名和密码登录mysql
[root@db01 support-files]# mysql -uroot -p123456
# 如果有密码,修改现有的密码
[root@db01~]# mysqladmin -uroot -p123456 password 12345678
# 为了安全起见,登录采用如下方式
[root@db01 support-files]# mysql -uroot -p Enterpassword: Welcometo the MySQL monitor. Commands end with; or \g.YourMySQL connection id is 5 mysql>
11、安全优化mysql步骤
1)修改root密码:
# 设置密码
[root@db01 support-files]# mysqladmin -uroot password 123456 [root@db01 support-files]#
# 如果有密码,修改现有的密码
[root@db01~]# mysqladmin -uroot -p123456 password 12345678
2)删除test库:
mysql>show databases; +--------------------+ |Database | +--------------------+ |information_schema | # mysql数据库必备的库,自动维护 |mysql | # 创建用户等数据会存放在这里 |performance_schema | # mysql数据库必须,自动维护 |test | # 可以删除 +--------------------+ 4rows in set (0.00 sec) mysql># 删除test库 mysql>drop database test; Query OK, 0 rows affected (0.02 sec) mysql>show databases; +--------------------+ |Database | +--------------------+ |information_schema | |mysql | |performance_schema | +--------------------+ 3rows in set (0.00 sec) mysql>
3)删除无用的用户,删除用户需要主机,大写无法删除,特殊字符无法删除,此时需要使用特殊的SQL语句删除
(delete from mysql.user where user=’root’ andhost=’A用户’):
# 查询当前数据库里的用户
# 下面的显示里,root重复,在mysql里user +host确定一个唯一的用户
# root localhost和root db01不是一个用户
mysql>select user,host from mysql.user; +------+------------+ |user | host | +------+------------+ |root | 127.0.0.1 | |root | ::1 | | | localhost | |root | localhost | | | db01 ||root | db01 |+------+------------+6rows in set (0.00 sec) mysql>
# 删除无用的用户
mysql>drop user ""@localhost; QueryOK, 0 rows affected (0.00 sec) mysql>select user,host from mysql.user; +------+------------+ |user | host | +------+------------+ |root | 127.0.0.1 | |root | ::1 | # ::1表示IPV6的意思,这个用户无用,可以删除 |root | localhost | | | web-lnmp02 | |root | web-lnmp02 | +------+------------+ 5rows in set (0.00 sec)
# 有时候,使用双引号””会报错,所以使用单引号’’会好些
mysql>drop user ""@web-lnmp02; ERROR1064 (42000): You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to use near ‘-lnmp02‘at line 1 mysql> mysql> mysql>drop user ‘‘@‘web-lnmp02‘; QueryOK, 0 rows affected (0.00 sec) mysql> mysql>drop user ‘root‘@‘web-lnmp02‘; QueryOK, 0 rows affected (0.00 sec) mysql> mysql>drop user ‘root‘@‘::1‘; QueryOK, 0 rows affected (0.00 sec) mysql>
# 删除用户后,最终检查,保留如下用户
mysql>select user,host from mysql.user; +------+-----------+ |user | host | +------+-----------+ |root | 127.0.0.1 | |root | localhost | +------+-----------+ 2rows in set (0.00 sec) mysql>
# 如果错把root localhost用户删除,可以通过命令创建出来
mysql>grant all on *.* to ‘root’@localhost identified by ‘123456’ with grant option;
# 刷新mysql系统权限相关表
mysql>flush privileges;
12、mysql简单应用命令总结
1)Linux查看命令帮助使用man,内置命令使用help;在mysql中,使用help查看mysql的命令帮助:
mysql>help drop; Manyhelp items for your request exist. Tomake a more specific request, please type ‘help <item>‘, where<item> is one of the following topics: ALTER TABLE ALTER TABLESPACE DEALLOCATE PREPARE DROP DATABASE DROP EVENT DROP FUNCTION DROP FUNCTION UDF DROP INDEX DROP LOGFILE GROUP DROP PROCEDURE DROP SERVER DROP TABLE DROP TABLESPACE DROP TRIGGER DROP USER DROP VIEW mysql>help drop user; Name:‘DROP USER‘ Description: Syntax: DROPUSER user [, user] ... TheDROP USER statement removes one or more MySQL accounts and their privileges.It removes privilege rows for the account from all grant tables.To use this statement, you must have the global CREATE USER privilegeor the DELETE privilege for the mysql database. Each account nameuses the format described in http://dev.mysql.com/doc/refman/5.5/en/account-names.html.For example: # 帮助里的例子 DROP USER ‘jeffrey‘@‘localhost‘; Ifyou specify only the user name part of the account name, a host name partof ‘%‘ is used. URL:http://dev.mysql.com/doc/refman/5.5/en/drop-user.html mysql>
2)检查命令
mysql>show databases; # 显示所有库 mysql>use mysql; # 切换到mysql库 mysql>show tables; # 显示当前库中所有表 mysql>select user,host from mysql.user; # 查看用户列表 mysql>select user(); # 查看当前用户 mysql>select database(); # 查看当前所在库 mysql>drop database 库名; # 删除数据库 mysql>drop user ’用户’@’主机’ # 删除用户 # 删除用户 mysql>deletefrom mysql.user where user=”root” and host=“A用户”
本文出自 “Linuxgao” 博客,请务必保留此出处http://linuxgao.blog.51cto.com/11934904/1931007
原文:http://linuxgao.blog.51cto.com/11934904/1931007