第三节 配置MYSQL数据库
配置mysql数据库通常通过命令行选项、配置文件、和环境变量来进行,并且优先顺序也是命令行最高,环境变量优先级最低。
1、配置文件定位
mysql的配置文件可以在以下四个位置:(按照查找顺序)
1、/etc/my.cnf
2、DATADIR/my.cnf //DATADIR是存储数据库数据的目录。
3、通过 --default-extra-file=fielname设置指定的位置。
4、~/.my.cnf //用户的家目录
所以可以在用户的家目录下创建.my.cnf文件,并添加一下内容:
[client]
user=root
host=localhost
password=password
并设置相应权限,防止被其他用户查看
2、基本参数:
配置文件:/etc/my.cnf,集中式的多段配置文件
[mysqld]
datadir=/var/lib/mysql/ //用来定义数据库存储位置,要求存储目录必须是mysql用户以及msyql组所有。
back_log //要求mysql具有的链接数量,最大为50
max_connections //允许同时访问的数量。
[mysql]
[client]
实现开关机自动开启和关闭MySQL服务
通常情况下,如果使用rpm方式安装会自动安装此功能,可以通过查看是否存在/etc/rc.d/init.d/mysql来确定,如果通过二进制安装或者编译安装的,需要进入安装目录的mysql/support-files/目录查找mysql.server文件。
手动安装:
1、cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/
2、ln -s /etc/rc.d/init.d/mysql.server /etc/rc.d/rc3.d/s99mysql
3、ln -s /etc/rc.d/init.d/mysql.server /etc/rc.d/rc0.d/s01mysql
配置文件详解:http://www.cnblogs.com/toby/articles/2198697.html
常用命令
查看版本信息
select version();
显示服务器参数变量、当前数据库的详细配置信息
(有些变量可以修改,这些参数可以改变数据库的工作特性。有些立即生效,有些需要重启数据库生效。)
show [gloab|session] variables;
显示服务器状态变量,记录了当前包括过去时间内mysql的运行统计数据
show [gloab|session]status
3、MySQL安全
用户管理
1、创建用户:create user
实例: create user gongbing@192.168.1.0/24 IDENTIFIED BY ‘password’
create user gongbing@192.168.1.0.% IDENTIFIED BY ‘password’
可以使用通配符
%表示任意字符
_表示任意单个字符
2、用户授权创建和删除用户权限:GRANT REVOKE
用户授权: grant all privileges on dbname.tablename to username@host [identified by ‘password’]
授予用户username可以通过host对dbname.tablename数据库的表拥有所有权限
注意: 假如你在给用户'pig'@'%'授权的时候是这样的(或类似的):GRANT SELECT ON test.user TO 'pig'@'%', 则在使用REVOKE SELECT ON *.* FROM 'pig'@'%';命令并不能撤销该用户对test数据库中user表的SELECT 操作.相反,如果授权使用的是GRANT SELECT ON *.* TO 'pig'@'%';则REVOKE SELECT ON test.user FROM 'pig'@'%';命令也不能撤销该用户对test数据库中user表的Select 权限.
3、修改用户密码
a)、修改root密码,这种方式适用于知道root密码(mysql数据库的密码不要和linux的密码混淆了哦。)mysqladmin -u username -p password ‘newpassword’
--注意:当使用mysqladmin修改普通用户时
[root@gc ~]# [root@gc ~]# mysqladmin -uhive -phive1 password hive
mysqladmin: Can't turn off logging; error: 'Access denied; you need (at least one of) the SUPER privilege(s) for this operation'
b)、直接更改user表的password字段
适用于忘记root、普通用户密码。
编辑my.cnf(windows的是mysql.ini)文件,添加skip-grant-tables
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-name-resolve
skip-grant-tables
c)、登陆mysql数据库并修改用户名密码
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set password=password('123123') where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
注意:mysql5.6在通过上面修改密码后要求使用set password再设置一次密码。
d)、通过set命令设置
语法:SET PASSWORD FOR '用户名'@'主机' = PASSWORD('密码')
mysql> create database gongbing;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> set password for 'root'@'localhost'=password('123123');
Query OK, 0 rows affected (0.00 sec)
mysql> create database gongbing;
Query OK, 1 row affected (0.00 sec)
--允许root通过远程登录
mysql> set password for 'root'@'%'=password('123123');
注意:要使用password函数,但不用flush privileges命令。
修改密码摘自:http://blog.csdn.net/lichangzai/article/details/8626591
4、删除用户
drop user ‘username’@‘localhost‘
系统内部安全
1、建议将数据目录的权限设置为700
2、不要使用root启动MySQL
3、注意~/.bash.history和~/.mysql.history目录,防止记录被窃取。
4、登录时密码不要直接输入在-p后面。
mysql5.5数据库root账户被删除的处理方法:
1、关闭mysql,并使用mysqld_safe --skip-grant-tables &重启
2、使用grant all privileges on *.* to root@localhost identified by ‘root’ with grant option,创建用户并附权限。
问题:
MySQL报错:The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
处理方法:
mysql>set global read_only=0;
mysql>flush privileges
外部网络安全
1、修改用户密码
方法一、mysqladmin -u root -p password ‘newpassword’
方法二、set password for 'root'@'localhost'=password('root');
方法三、use mysql;
update user set password=password('new_password') where user='root';
flush privileges;
2、删除多余账户
delete from user where='';
小技巧:
1)、忘记MySQL的root密码(本地登录拒绝访问)
1、safe_mysqld --skip-grant-tables &
2、使用root账户登录指定mysql数据库 mysql -u root mysql
3、更改密码:update user set password=password(’newpassword’) where user=‘root’
4、更新数据库授权表:flush privileges;
5、重启数据库并登录。
2)、启动MYSQL服务器加上--skip-show-database,可以使数据库用户不能浏览其他数据库。
3)、启动mysql服务器加上--log-slow-queriesl=“”,这样mysql会把SQL执行时间超过long_query_time的写入file中。
4)、启动msyql服务器不监听任何TCP/IP协议--skip-network。
优化查询语句
./safe_mysqld --log-slow-queries=file //将sql语句查询超时的记录下来。
MYSQL用户管理视频来源:http://edu.51cto.com/lession/id-75676.html
第四节 MYSQL管理
一、使用命令工具
MYSQL客户端命令
帮助:help
quit,\q:退出
delimiter,\d:更换结束符;,默认服务器端命令是;作为结束符
go,\g:强制将命令发送到mysql上去,可以没有;
use,\u:用于设定默认数据库
mysql>use mysql
ego,\G:将命令送到mysql上面,并将结果竖向现实,对乱码有用。
mysql> select * from user\G
*************************** 1. row ***************************
Host: %
User: root
Password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string: NULL
*************************** 2. row ***************************
system,\!:不退出当前程序的情况下,执行shell命令。
mysql> system ls -l
total 151680
-rwxr-xr-x. 1 root root 25470 Mar 3 05:38 innochecksum
-rwxr-xr-x. 1 root root 1458 Mar 3 05:28 msql2mysql
-rwxr-xr-x. 1 root root 6162461 Mar 3 05:38 myisamchk
-rwxr-xr-x. 1 root root 5768008 Mar 3 05:38 myisam_ftdump
-rwxr-xr-x. 1 root root 5749127 Mar 3 05:38 myisamlog
-rwxr-xr-x. 1 root root 5833390 Mar 3 05:38 myisampack
status:获取当前服务器状态信息
mysql> status;
--------------
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
Connection id: 69
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.5.48 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 4 hours 20 min 52 sec
Threads: 3 Questions: 787 Slow queries: 0 Opens: 48 Flush tables: 1 Open tables: 41 Queries per second avg: 0.050
--------------
1、mysql命令支持交互式,批处理命令式。
交互式
-h --host=:指定主机名
-u --user=:用户名
-p --password=:密码
-D db_name,--database=: 直接设置某个数据库为默认数据库
-e ‘sql_statement;’:将sql语句外置。
实例:链接远程192.168.1.2主机,默认库为mysql。
mysql -h 192.168.1.2 -D mysql -uroot -p
2、批处理方式:
方法一、mysql options <scripts.sql
方法二、mysql> source /path/scripts.sql
服务器端命令
查看帮助,使用help后面跟随需要了解的命令,比如help create 来了解create后面可以接那些参数,再help create user来详细了解具体使用方法。
小技巧:
如果公司为了安全考虑,对mysql进行了身份验证,并且登陆时能够直接指定默认数据库可以编写一个my.cnf文件,并放在用户的家目录下。
[client]
user=mysql
passowrd=mysqlpassword
database=dtedu
3、mysqladmin管理性操作:直接通过mysqladmin [option] 来进行。
create databasename; //创建一个数据库
drop databasename; //删除一个数据库及所有包含的表
extended-status; //给出服务器的扩展状态
flush-hosts; //清空所有缓存主机
flush-logs; //清空所有日志
flush-tables; //清空所有表
flush-privileges; //重新加载授权表
password; //修改密码
ping; //检测mysql是否活动
processlist; //显示服务器中活动线程列表
refresh; //清空所有表并打开和关闭日志文件
shutdown; //关闭服务器
status; //显示状态信息
version; //显示mysql版本
4、mysqlbinlog
将二进制日志中的项目转换为可读的表单或者SQL语句。
5、mysqlcheck
用于修复、检查数据库及其中的表。
[root@localhost mysql]# mysqlcheck -o mysql
选项:
-a 分析指定的表
-c 检查库或者表
-r 修复库、表
-o 优化指定的表
-h 指定需要操作的主机
-u 指定用户名
-p 指定密码
-auto-repair 当检查表有错误的时候自动修复
-e 完全检查(修复方式)
-m 只检查程序
-q 快速检查
-F 检查没有正确关闭的表
-f 遇到错误sql,强制执行。
6、mysqlhotcopy
需要提前安装perl-DBI和perl-DBD-mysql
参见:http://search.cpan.org/dist/DBD-mysql/lib/DBD/mysql/INSTALL.pod
http://linux.fcbu.com/mysqlhotcopy.htm
perl-DBD-mysql源码安装包 http://search.cpan.org/dist/DBD-mysql/
用途:用于为活动的数据执行一次安全备份
选项:
--checkpoint 用于指定存放操作记录的数据库或表
--addtodest 增量备份、新的备份自动覆盖原来的备份。
示例:备份一个数据库到一个目录中
[root@localhost data]# /usr/local/mysql/bin/mysqlhotcopy mysql /root/
恢复数据库文件,只需将备份的文件复制到数据库指定的目录下即可
[root@localhost data]# cp -rf /root/mysql /usr/local/mysql/data/
mysql备份参考:http://www.linuxidc.com/Linux/2015-01/111340.htm
7、mysqldump备份数据库
参数:
-h:指定远程主机ip地址
-u:指定远程需要备份主机的用户名
-p:指定远程备份主机的密码,可以为空,然后手动输入,这样比较安全。
-a:完全备份所有数据库
常用命令格式:
mysqldump -u root -p databasename >/var/backupmysql/backupname.sql
恢复( 导入数据库)
前提:如果需要导入的数据库之前没有创建过,那么需要先建立一个同名数据库。create database ec_school,然后才能导入。
导入方法:
方法一、进入mysql后,如果是对数据库恢复直接使用 source /var/backupmysql/backupname.sql即可。
方法二、mysqldump -u root -p ec_school</root/ec_school.sql
二、建立和连接数据库
1、连接数据库
mysql sqlname -u username -p
2、显示数据库列表
show databases;
3、显示库中的数据表
use mysql; 指定使用的数据库
show tables;
mysql> show tables-> ;+---------------------------+| Tables_in_mysql |+---------------------------+| columns_priv || db || event || func || general_log || help_category || help_keyword || help_relation || help_topic || host || ndb_binlog_index || plugin || proc || procs_priv || proxies_priv || servers || slow_log || tables_priv || time_zone || time_zone_leap_second || time_zone_name || time_zone_transition || time_zone_transition_type || user |+---------------------------+24 rows in set (0.00 sec)
4、显示数据库中表的结构
mysql> describe user
-> ;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
5、创建数据库
create database databasename;
6、创建表
use databasename;
create table tablename;
7、删除数据库、表
drop database databasename;
drop table tablename;
8、清空表中的记录
delete from tablename;
9、显示表中的记录
select * from tablename;
三、用户权限管理
GRANT和REVOKE
1、revoke(撤销权限)
撤权和授权的格式基本一样,只是撤权使用的是from指向用户名,而不是to。
2、GRANT(授权)
语法格式:GRANT privileges (columns) ON what TO user IDENTIFIED BY "password" WITH GRANT OPTION
实例:
1、授予用户test对数据库mysql有读取并修改数据库内容,但不能创建新表或者删除表
grant select,insert,delete,update on mysql.* to test@localhost identified by ‘6776’;
2、授予用户只对表的某些列可以查看的权限。
grant select (treet,city,zip) on mysql.address to mysql@localhost identified by ‘123’;
对应privileges所指定的用户权限主要有一下几种表示方法:
权限指定符 | 含义 |
alter |
修改表和索引 |
create |
创建数据库和表 |
delete |
删除表中的记录 |
drop |
删除数据库和表 |
index |
创建和删除索引 |
insert |
向表中插入新行 |
select |
查找表中的数据 |
update |
更新表的记录,修改,编辑 |
file |
读写服务器上的文件 |
process | 查看服务器中执行的进程信息或者杀死进程 |
reload |
重载授权表或者清空日志,缓存 |
shutdown |
关闭服务器 |
all |
所有服务 |
usage |
特殊的“无权限”权限 |
columns:用于定义用户可以设置的表的“列”,多个“列”之间用“逗号”分割。
what:用于确定数据库及表的范围。
user:权限授予的用户,他由用户名、主机名组成,可以指定那个用户可以通过那个主机连接到数据库中来。
通过username@hostname的方式可以限制用户只能在指定的主机上登陆访问数据库,主机名可以使用%来表示任意主机,192.168.%表示一个网络地址范围,mysql3.23版本之后可以通过直接跟子网掩码号来确定ip的网络地址。
with grant option :被授权用户有再给其他用户授权的能力。
小技巧:通过测试发现,手动授权会造成各种问题,建议初始化mysql数据库后,通过phpmyadmin来完成其他用户的权限赋值过程,会比较稳定。
配置phpmyadmin可以参考后面的下面的文档,主要是copy config_sample_inc.php文件到htdocs/config_inc.php,并修改参数secret。