mysql数据库详解(续一)-阿里云开发者社区

开发者社区> 技术小阿哥> 正文

mysql数据库详解(续一)

简介:
+关注继续查看

第三节 配置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。



本文转自 blackstome 51CTO博客,原文链接:http://blog.51cto.com/137783/1968772,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,大概有三种登录方式:
2864 0
阿里云服务器ECS远程登录用户名密码查询方法
阿里云服务器ECS远程连接登录输入用户名和密码,阿里云没有默认密码,如果购买时没设置需要先重置实例密码,Windows用户名是administrator,Linux账号是root,阿小云来详细说下阿里云服务器远程登录连接用户名和密码查询方法
11065 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
10635 0
使用OpenApi弹性释放和设置云服务器ECS释放
云服务器ECS的一个重要特性就是按需创建资源。您可以在业务高峰期按需弹性的自定义规则进行资源创建,在完成业务计算的时候释放资源。本篇将提供几个Tips帮助您更加容易和自动化的完成云服务器的释放和弹性设置。
11960 0
windows server 2008阿里云ECS服务器安全设置
最近我们Sinesafe安全公司在为客户使用阿里云ecs服务器做安全的过程中,发现服务器基础安全性都没有做。为了为站长们提供更加有效的安全基础解决方案,我们Sinesafe将对阿里云服务器win2008 系统进行基础安全部署实战过程! 比较重要的几部分 1.
8911 0
如何设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云安全组设置详细图文教程(收藏起来) 阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程。阿里云会要求客户设置安全组,如果不设置,阿里云会指定默认的安全组。那么,这个安全组是什么呢?顾名思义,就是为了服务器安全设置的。安全组其实就是一个虚拟的防火墙,可以让用户从端口、IP的维度来筛选对应服务器的访问者,从而形成一个云上的安全域。
7354 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,云吞铺子总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系统盘、创建快照、配置安全组等操作如何登录ECS云服务器控制台? 1、先登录到阿里云ECS服务器控制台 2、点击顶部的“控制台” 3、通过左侧栏,切换到“云服务器ECS”即可,如下图所示 通过ECS控制台的远程连接来登录到云服务器 阿里云ECS云服务器自带远程连接功能,使用该功能可以登录到云服务器,简单且方便,如下图:点击“远程连接”,第一次连接会自动生成6位数字密码,输入密码即可登录到云服务器上。
21768 0
13694
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载