mysql数据库详解(续一)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

第三节 配置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,如需转载请自行联系原作者
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
5月前
|
SQL 存储 关系型数据库
SQL数据库
【7月更文挑战第30天】SQL数据库
52 8
|
存储 关系型数据库 MySQL
MySQL数据库的使用
MySQL数据库的使用
49 0
|
6月前
|
存储 SQL 关系型数据库
初步认识Mysql数据库
初步认识Mysql数据库
34 1
|
SQL 存储 关系型数据库
MYSQL数据库2
MYSQL数据库2
147 0
|
6月前
|
SQL 关系型数据库 MySQL
MySQL数据库
【6月更文挑战第14天】MySQL数据库
57 2
|
SQL 关系型数据库 MySQL
MYSQL数据库7
MYSQL数据库7
53 0
|
SQL 关系型数据库 MySQL
MYSQL数据库3
MYSQL数据库3
57 0
|
存储 关系型数据库 MySQL
|
存储 关系型数据库 MySQL
|
SQL 存储 监控
简单说一下使用SQL数据库的心得
简单说一下使用SQL数据库的心得
109 1