MySQL数据库（三）——数据库配置管理

数据库配置管理

用户以及密码管理

新建用户：create user ‘username’@‘%’ identified by ‘password’

删除用户：drop user ‘zhangsan’@‘%’

重命名用户：rename user ‘old_user’@'host'  to 'new_user'@'host

设置当前登录用户的密码：set password=password('123456')

设置其他用户的密码：set password='username'@‘host’=password('123456')

补充：破解root密码

1.停止服务

service mysqld stop

2.使用mysqld_safe结合skip-grant-tables启动数据

mysqld_safe --skip-grant-tables &

3.无密码登录数据库，更新root密码

update mysql.user set authentication_string=password('123456') where user='root';

4.刷新数据库（修改的内容提取到内存马上生效）

flush privileges;

数据库日志管理

日志管理（六种不同的日志）

文件位置（源码安装模板的存放位置）：/etc/my.cnf

1.错误日志

作用：记录启动、运行或停止时出现的问题，一般也会记录警告信息

[mysqld]下编辑开启

log_error = /usr/local/mysql/data/mysql.err   \\指定日志的存放位置

或者log_warnings = 0/1       \\启用警告信息

2.通用查询日志

作用：记录建立的客户端连接和执行的语句

开启之后对服务器的压力很大（一般不开启，默认不开启）

general_log = ON/OFF    \\启动或关闭查询日志，默认为关闭

general_log_file = /usr/local/mysql/data/mysql.log      \\指定查询日志存放位置

log_output=TABLE\FILE

补充：为了调试数据库可以开启查询日志，生产环境不建议开启查询日志

3.慢查询日志

作用：记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询，可以帮我们定位服务器性能问题

slow_query_log = ON/OFF      \\开启或关闭慢查询日志，默认是关闭的

slow_query_log_file = /usr/local/mysql/data/mysql_slow.log     \\指定查询日志存放位置

log_query_time = 10          \\定义慢查询日志的记录市场，默认为10秒

4.二进制日志

精确的记录了用户对数据库中的数据进行操作的命令和操作的数据对象

做数据增量备份，提供数据基于时间点的恢复，还可以主从复制，主服务器开启

5.中继日志

作用：从主服务器的二进制文件中复制而来的时间，并保存为的日志文件

relay-log = master-relay-bin

reay-log-index = master-relay-bin.index

6.事务日志（存储引擎）

作用：记录lnnoDB等支持事务的存储引擎执行事务时产生的日志

事务性存储引擎用于保证（ACID）原子性、一致性、隔离性和持久性；其不会立即写到数据文件中，而是写到事务日志中

事务（MySQL事务默认是自动提交的，当SQL语句提交时事务便自动提交）

是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行

特点：原子性、一致性、隔离性、持久性

事务开始：begin;

提交：commit;

回滚：rollback;       \\执行之后回滚到begin位置，之后的操作全部不作数

定义回滚点：savepoint si;   \\定义了之后不会直接回滚到begin

设置事务为手动提交（关闭之后需要手动提交）：set autocommit=0/1;  \\0为关闭自动提交，1为开启

查看事务是否为自动提交：SHOW VARIABLES like '%autocommit%'

查看数据库当前进程：show processlist;

查看当前运行的所有事务：select * from information_schema.INNODB_TRX;

查看当前出现的锁：select * from information_schema.INNODB_LOCKs;

存储引擎

在数据保存到数据文件之前会先传输到存储引擎，再按照各个存储引擎的存储格式进行数据存储

最为知名的存储引擎：MyISAM和InnoDB存储引擎

MyISAM

特点：1.读写操作速度快，内存占用小

           2.读写过程相互阻塞

           3.不支持事务，不支持外键，只支持全文索引

           4.表级锁定，数据再更新时锁定整个表

           5.在磁盘上存储成三个文件（1、.frm文件存储定义   2、数据文件的扩展名为：.MYD   3、索引文件的扩展名为：.MYI）

MyISAM适用的场景应用

不需要事务的支持，单方面读写数据比较多，因为读写相互阻塞的特点，数据读写频繁的环境不适用，并发访问较低，数据修改较少

InnoDB

特点：1.支持事务，支持4个事务隔离级别

           2.读写阻塞跟事务隔离级别有关

           3.缓存特性非常高效，能缓存索引，也能缓存数据

           4.行级锁定，但是全表扫描仍然会是表级锁定

           5.表和主键以簇的方式存储

           6.支持分区，表空间，类似Oracle数据库

           7.支持外键约束，MySQL5.5以前不支持全文索引，5.5版本以后支持

InnoDB适用的场景应用

业务需要事务的支持，高并发量的适应能力，业务数据更新较为频繁，数据一致性要求较高，硬件设备内存较大

存储引擎管理

查看数据库可配置的存储引擎：mysql>show engines;

查看表正在使用的存储引擎：mysql>show table status from 库名 where name = '表名' 

                                               mysql>show create table 表名;

设置修改存储引擎：mysql>alter table table_name engine=引擎名;

配置文件修改默认的引擎选项（重启服务生效）

vim  /etc/my.cnf

default-storage-engine=引擎名

创建表指定新表的引擎：在后面跟上engine=引擎名就行

批量转换存储引擎：mysql>mysql_convert_table_format --user=root --password=密码 --socket=/temp/mysql.sock --engine=引擎名 库名 表名

命令选项

--force：碰到错误强制转换

--host：转换的主机名

--password：当前进行转换用户的密码

--port：如果不是本地连接，需要指定端口号

--socket：SOCKET文件存在的位置

--user：连接的用户名