mysql学习笔记

最新推荐文章于 2024-04-24 16:25:27 发布

weixin_30381793

最新推荐文章于 2024-04-24 16:25:27 发布

阅读量107

点赞数

原文链接：http://www.cnblogs.com/MeteorLocus/p/3337373.html

版权

---恢复内容开始---

环境win7x64 MySQL5.6

笔记的内容是MySQL5.0的

索引
1 查看并确保与服务器使用相同的字符集
2 批处理命令
3 数据表类型
4 数据类型
5 MySQL不支持使用函数设置默认值，也不允许为数据列定义合法性检查
6 设计中的技巧
7 解决方案
8 锁定
9 事务
10 安全
11 MySQL服务器的优化

1 查看并确保与服务器使用相同的字符集（windows下连服务器）
mysql> STATUS
注意Server characterset/Db characterset/Client characterset/Conn. 四个属性的值。

*这里服务器配置为utf-8
cmd 启动后先执行下面命令，效果是将字符集由默认的cp850转换为utf8
> CHCP 65001
之后启动mysql，mysql将使用utf8通信。

2 批处理命令
> mysql [option] databasename < file.sql
执行查询DML语句，每个语句以分号结束

> mysqladmin [option] admincomand
DDL

> mysqldump [option] dbname > backupfile.sql
备份数据库

> msyqladmin -u root -p create dbname
Password:******
> mysql -u root -p dbname < backupfile.sql
Password:******
还原数据库

3 数据表类型
在创建数据表的时候允许选择数据表类型，在其他数据库软件中很少见！！
MyISAM
MyISAM static:
如果所有字段都具有确定长度，则MySQL自动选择此类型。
特点是存取效率高，安全性高（即使数据文件损坏也容易提取和恢复）
MyISAM Dynamic:
如果数据表定义中有且只有一个VARCHAR，*TEXT,*BLOB字段，则MySQL自动选择此类型。
与静态MyISAM相比，突出优点是存储空间开销小（存储二进制数据实际长度+几个字节），
经常编辑此字段会导致碎片（同一条记录的各字段不一定存储在连续字节块中），可使用OPTIMIZE TABLE 或其他工具（myisamchk）进行碎片整理。
MyISAM Compressed:
动态和静态都可以用myisamchk压缩，一般可以节省一半以上的存储空间（与内容有关）。读取时要解压缩，但有时数据表的访问速度会变快--在“低速硬盘+高速CPU”的系统上尤为明显。
缺点是不能进行修改，是只读的。

InnoDB （可以看做是MyISAM的下一代产品）
支持事务：
ANSI-SQL/92标准的全部四种:READ UNCOMMITED/READ COMMITED/REPEATABLE READ/SERIALIZABLE
支持数据行级锁定：
InnoDB驱动程序内建的行锁机制，执行事务时不会锁定整个表。能自动识别死锁，杀死其中的一个以解除死锁。
外键：自动保证一致性，即使执行DELETE。
崩溃恢复：自动恢复到之前的一个稳定状态。
问题和缺点：
表空间管理
MyISAM数据表驱动程序把每个数据表分别保存到单独的文件，根据实际情况自动增大或缩小。
InnoDB数据表驱动程序把所有的数据和索引保存在一个表空间，形成虚拟的文件系统，只能增大。因此，想复制一个数据表，停止MySQL服务复制文件的方法不可行。
一般使用mysqldump.
数据记录长度。
InnoDB数据表中单条记录最大8000字节（不包括TEXT和BLOB，这两个只有前512字节是和其他存在一起的，剩下的存到别的地方）。
存储空间的占用量：
InnoDB数据表比同样内容的MyISAM数据表大，有时能达到2倍。
全文索引
InnoDB不支持全文索引（full-text index）
GIS数据
InnoDB不能用来保存二位地理数据
COUNT问题
执行SELECT COUNT（*） FROM TABLE时InnoDB比MyISAM慢很多。
数据表锁定
InnoDB有特定的锁定算法，不要用LOCK TABLE...READ/WRITE（表锁定）应该使用SELECT...IN SHARE MODE或SELECT...FOR UPDATE（行锁定）。
mysql数据表
用于管理MySQL访问权限的数据表不能转换为InnoDB，必须是MyISAM格式。
许可证费用。
商用MySQL许可证增加InnoDB支持将收取双倍费用。

HEAP
HEAP数据表只存在内存中，使用了散列索引，存取非常快，主要用途是充当临时表。MySQL服务停止时消失。
不能使用*TEXT和*BLOB,只允许用=和<=>,不能用< > <= >=
1<=>null 返回 0
null<=>null 返回 1
最大长度由配置文件的max_heap_table_size参数决定。
对访问同一数据库的其他连接可见，连接意外中断时不丢失。

临时数据表
使用CREATE TEMPORARY TABLE创建或MySQL为保存中间结果创建的数据表，在服务器意外掉电时不一定丢失，但正常关机或连接正常断开，连接意外断开时会丢失。
对访问数据库的其他连接不可见。不同的用户取同一个名字也不会冲突。
临时数据表不是新数据表类型，其数据表类型可以使前3种的任意一种。使用CREATE TEMPORARY TABLE的场合不多见，提到“临时数据表”时多指MySQL为保存SELECT中间结果自己创建的数据表。
临时数据表和其他数据表分开保存，通常是c:\windows\temp或/tmp /var/temp /usr/temp,可以在MySQL服务器启动时设置。

其他数据表类型
BDB 早期支持事务的，基本不用了。
ARCHIVE 为保存海量数据设置，保存记录之前先压缩，只支持INSERT（不允许UPDATE和DELETE），不能建索引（只能全表扫描），适用于数据量大，访问量低的场合。
CSV 数据表保存在CSV文件中，不能建立索引。
NDB 支持事务，适合建设分布在大量计算机的网络数据库，前提是有许多安装了MySQL Max版本的联网计算机并配置支持集簇操作。
FEDERATED 外部数据表，数据库系统和数据表存储在不同的网络计算机上。不能用QueryCache优化，不支持ALTER TABLE，但可以执行INSERT UPDATE DELETE

4 数据类型
整数
UNSIGNED 数据做减法会得到 UNSIGNED类型的数据，结果可能令人困惑
各种整数类型最后的可选参数是SELECT结果的表示位数，对取值范围没有影响。
但是，当如果这个值设置不当，在MySQL执行复杂查询，需要创建保存中间结果的临时表时，数据可能会截断。
如int（4），可以存大于9999的数。
AUTO_INCREMENT整数
a 必须与 NOT NULL，PRIMARY KEY或UNIQUE同时使用
b 每个表最多只能有一个AUTO_INCREMENT数据列
c MySQL自动生成ID的这种机制只有在INSETE命令插入数据且没有为ID字段明确给出一个值或NULL是起作用，如果给定了值，并且这个值还没有出现过，就利用这个值生成ID。
d 在执行完INSERT命令之后（还在本次连接或本个事务里），立刻执行SELECT LAST_INSERT_ID()可以获得刚插入的AUTO_INCREMENT值
e 如果AUTO_INCREMENT计数器达到最大值，将不再递增，随后的插入也无法进行。
二进制数据（BIT和BOOL）
MySQL中BOOL是TINYINT的同义词，5.0.2以前BIT也是，但从5.0.3起，BIT变成了一种可以存储多达64位二进制的新数据类型。

浮点数（FLOAT和DOUBLE）
FLOAT(m,d) 8位精度，4字节，m和d是可选的，m是十进制数字的总数，d是小数点后数字的位数。插入数据时，数值将进行必要的舍入，超过范围则替换为允许范围内的最大值。可以用SHOW WARNING查看警告内容。
DOUBLE(m,d) 16位精度，8字节
REAL(m,d) DOUBLE的同义词。
参数m只影响显示的效果，对精度没有影响。

定点数
如果浮点数带来的误差不可接受（财务等），要用定点数
DECIMAL(p,s) 定点数，以字符串保存；位数不限，每个数字占一个字节，再加2个字节的开销。
NUMERIC，DEC 双精度浮点数，16位精度，8字节
参数p，s分别设置了数据值的数字总个数（表示范围，最大65）和小数点后面的数字个数（精确度，最大值是30）。
对于DECIMAL(6,3),MySQL内部把定点数保存为二进制格式：先分成小数点前面和后面两个部分，并为它们各自分配4字节，也即是DECIMAL(6,3)和DECIMAL(18,9)都实际占用8字节。如果还有多出来的数字就在分配一个字节（每个字节容纳两位数字，但每4字节容纳9个数字）。

日期与时间（DATE，TIME，DATETIME，TIMESTAMP）
日期合法性
使用sql_mode控制日期格式检查。
ALLOW_INVALID_DATES 允许使用显然不正确的数据作为日期值,如'2005-02-31'
NO_ZERO_DATE '0000-00-00'不再是一个合法的日期值
NO_ZERO_IN_DATE 不允许使用0作为月份值或日期值

字符串（CHAR，VARCHAR,*Text）
CHAR(n) 固定长度的字符串，最多255字符
VARCHAR(n) 可变长，MySQL4.1及以前最大255，MySQL5.0.3及以后最大65535
TINYTEXT 可变长，最多255字符
VARCHAR(n)与*TEXT区别，VARCHAR(n)长度必须在声明时设置，超长的字符将被截断；*TEXT不允许设置最大长度（唯一的限制是特定文本类型本身的最大长度）
silent column changes
对于CHAR(n) 和VARCHAR(n)
如果n<4，VARCHAR(n)被改成CHAR(n)
如果n>3，并且同一个数据表里还有其他的VARCHAR、TEXT或BLOB数据列，CHAR(n)将被修改成VARCHAR(n)。如果数据表只有固定长度的数据列，CHAR(n)不发生变化。

在MyISAM数据表里，VARCHAR数据列的最大长度是65535字节，但最大字符数取决于字符集。
VARCHAR值得前导空格和末尾空格可以存入数据表了。
BINARY属性，如果给CHAR和VARCHAR类型数据列加上BINARY属性，MySQL会把他们视同BLOB而不是字符。二进制管理简单，速度快。

二进制数据（BLOB与TEXT）
几乎相同，唯一的区别是TEXT按照文本模式进行比较和排序，而二进制数据按照二进制编码进行比较和排序。
BIT（n），n二进制位数，最大64
写二进制的语法b'0101'
SELECT bitcolumn+0把二进制转为整数
用SELECT BIN（bitcolumn+0）把整数显示为二进制
如果插入BIT列的值引起溢出（上，下）所有位存储为1，如-1，3插入BIT(3)会得到b'111',b'011'

5 MySQL不支持使用函数设置默认值，也不允许为数据列定义合法性检查（没有SQLSERVER的check约束）

6 设计中的技巧
外键的数据类型应尽可能与主键一致
设置外键是可以通过附加选项指定删除时的行为（仅限InnoDB）。
RESTRICT 默认行为，DELETE语句将引起一个错误，但不会使当前事务失败，还需要使用COMMIT或ROLLBACK来终止事务。
SET NULL 如果受影响的字段允许设置为NULL，则受影响字段设置为NULL
CASCADE table2数据表的记录会删除，table1受影响的记录也被删除
NO ACTION table2数据表的记录会删除，table1不变，也就是引用一致性被破坏是可以被接受的。

DROP Table不受外键约束影响。

创建外键失败时会返回Error1005:Can't create table ***.（拼写错误也可能返回这个错误）
设置外键的前提
a 设置外键的列table1.column1和table2.column2都必须至少有一个普通索引。FOREIGN KEY不会自动创建索引。如果是多字段索引，则必须出现在第一个字段，否则也需要单独建索引。
b 设置外键的列table1.column1和table2.column2必须匹配到无需类型转换的程度，设置为INT或BIGINT最有效，但符号要相同（都是SIGNED或UNJSIGNED）。
c 如果定义了可选规则ON DELETE/UPDATE SET NULL，就必须允许table1.column1设为NULL
d 外键约束必须一开始就满足，如果已存在的数据不满足外键约束，ALTER TABLE命令将返回1216错误，A foreign key constraint fails；
在创建外键出现问题时可以用SHOW INNODB STATUS命令取查看详细信息。

1216错误的解决方法
SELECT titleID，publID FROM titles
WHERE publID NOT IN （SELECT publID FROM publishers）

删除外键约束
ALTER TABLE tablename DROP FOREIGN KEY foreign_key_id
可以用SHOW CREATE TABLE 命令查出需要删除索引的foreign_key_id
如果正在使用者镜像功能，删除外键约束可能会引起问题。这是因为外键约束在镜像系统和原始系统的名字可能不一样。

临时禁用外键约束
SET forign_key_checks=0可以暂时关闭外键约束的自动检查。可以提高数据导入速度，但此期间对数据库改动造成不一致错误不会改正。

索引不是万能的，可以加快检索但是是修改变慢。DELAY_KEY_WRITE命令可以在一组数据插入之后在刷新索引。索引会占用相当大的空间。
MySQL中每个数据表的索引总数是16个。
InnoDB中索引比MyISAM重要，行锁定实际发生在索引级。
限制
a WRERE中有!=时，无法使用索引。
b 条件左侧使用函数时，无法使用索引。
c JOIN操作，只有主键和外键数据类型一致时才使用索引。
d 如果查询条件有LIKE和REGEXP，只有搜索模板的第一个字符不是通配符是才使用索引。"%abc"不会用索引。"abc%"会。
e 如果数据列包含大量重复的值，就没必要创建索引。

普通索引，唯一索引和主索引
要注意这些称呼和SQLSERVER的不同，不仅仅表现在称呼上，实现机制和使用也有些细微的差别。
普通索引和SQLSERVER的一般非聚集索引一致。
唯一索引和SQLSERVER的唯一索引一致，就是唯一非聚集索引。
主索引和SQLSERVER在主键上建立的索引差不多，推测相当于主键上的唯一非聚集索引。(在MyISAM格式下推测是聚集的)。

全文索引
文字字段上的普通索引只能加快对出现在内容最前面的字符串进行检索。如果字段里存放的是有几个，甚至是大段文字，类似LIKE '%word%'类型的检索，需要用全文索引。可以与数据表一同创建也可以在必要时添加
ALTER TABLE tablename ADD TULLTEXT（column1,column2）
查询的基本语法(查找column1，column2中包含单词'word1','word2','word3'的所有记录)
SELECT * FROM tablename
WHERE MATCH(column1,column2) AGAINST('word1','word2','word3')
InnoDB不支持全文索引。

其他的索引都是按照效果起名字，没什么意思。

查询和索引的优化
数据表数据大于1000，数据总量超过服务器内存时测试结果才是可靠的。
在不知道改在哪一列创建索引时，用EXPLAIN命令。效率从高到低system，const，eq_ref,ref,range,index,all
possible_keys给出了可选的索引，keys是实际选择的索引，key_len给出了索引的字节长度，一般越小越好，ref数据列给出了关联关系中另一个数据列的名字，row是从该数据表预计读出的行数，row列所有数据乘积可以大致了解这个查询需要处理多少种组合。extra提供了JOIN有关的更多信息，比如using temporary。

视图
视图是否可更新（INSERT，UPDATE，DELETE）取决于创建时的SELECT语句。
SELECT中不包含GROUP BY，DISTINCT，LIMIT，UNION，HAVING
如果视图来自一个以上的表，则几乎不可刷新。
视图应该包含主键索引，唯一索引，外键约束所涉及的全部列。如果视图没有或缺少，有updateable_views_with_limit来决定是允许刷新并返回信息（默认），还是不允许刷新并引发一个错误（设置为0）

不带WHERE的DELETE命令会删掉所有数据，但不会删表结构，MySQL没有恢复DELETE的数据的机制。

自动修改数据表设计
MySQL在一定条件下回自动修改数据表设计，理由是为了提高效率或者MySQL无法实现，没有任何提示，所以有必要使用SHOW CREATE TABLE命令去检查实际创建的表

可以从information_schema检索元数据，information_schema是一个虚拟的数据库，不能用SHOW DATABASE，SELECT * FROM information_schema.schemata，USE information_schema等命令无效。
information_schema数据表不允许修改（不能用INSERT，UPDATE），不区分字母大小写。允许使用SHOW TABALES FROM information_schema，SHOW COLUMNS information_schema.schemata.允许任何用户访问。

---恢复内容结束---

环境win7x64 MySQL5.6
1 查看并确保与服务器使用相同的字符集（windows下连服务器）
mysql> STATUS
注意Server characterset/Db characterset/Client characterset/Conn. 四个属性的值。

*这里服务器配置为utf-8
cmd 启动后先执行下面命令，效果是将字符集由默认的cp850转换为utf8
> CHCP 65001
之后启动mysql，mysql将使用utf8通信。

2 批处理命令
> mysql [option] databasename < file.sql
执行查询DML语句，每个语句以分号结束

> mysqladmin [option] admincomand
DDL

> mysqldump [option] dbname > backupfile.sql
备份数据库

> msyqladmin -u root -p create dbname
Password:******
> mysql -u root -p dbname < backupfile.sql
Password:******
还原数据库

5 MySQL不支持使用函数设置默认值，也不允许为数据列定义合法性检查（没有SQLSERVER的check约束）

DROP Table不受外键约束影响。

1216错误的解决方法
SELECT titleID，publID FROM titles
WHERE publID NOT IN （SELECT publID FROM publishers）

临时禁用外键约束
SET forign_key_checks=0可以暂时关闭外键约束的自动检查。可以提高数据导入速度，但此期间对数据库改动造成不一致错误不会改正。

其他的索引都是按照效果起名字，没什么意思。

不带WHERE的DELETE命令会删掉所有数据，但不会删表结构，MySQL没有恢复DELETE的数据的机制。

7 解决方案
字符串
CHAR_LENGTH() 字符数,LENGTH() 字节数
改变字符集
SELECT CONVERT(title USING utf8) FROM titles
SELECT HEX(CONVERT(title USING utf8)) FROM titles
服务器时区 SELECT @@global.time_zone 通常返回SYSTEM，表示和服务器一致。
客户端时区 SET time_zone = '+8:00'可以改变当前session.time_zone而不会改变global.time_zone，断开连接后消失。
MySQL中许多时间函数受时区影响，使用时应先确认。
SELECT CONVERT_TZ('2013-09-09 13:56:56','+8:00','+0:00');第一个参数是时间，后面的参数是服务器时区和目标时区，可以使用时区名或直接写时间差。时间格式支持2013-09-09T13:56:56.123456，ISO8601

变量赋值(注意和其他数据库的区别)
SET @var = 3;
SELECT @var:= 3;
SELECT title,subtitle FROM titles WHERE titleID=... INTO @t,@st;(MySQL5.0以上，且WHERE条件保证返回一条记录)

MySQL不支持OLAP函数
一般情况下，SELECT * FROM tablename WHERE coloum LIKE '%word%'是最耗时间的查询(更糟糕的情况只有应该只有一种，就是有系统里运行着好几个这样的查询。)

8 锁定
LOCK TABLE table1 locktype1,table2 locktype2,...
locktype可以使用下列之一
READ 对全体用户可读，不允许修改（包括发出LOCK命令的用户）。READ LOCK只在数据表上没有任何WRITE LOCK的时候才有效。
READ LOCAL 类似于READ LOCK，但允许不影响任何现有记录的插入操作。
WRITE 仅允许当前用户进行读和写。WRITE LOCK只在数据表上没有任何READ LOCK，也没有任何其他的WRITE LOCK时才能生效。
LOW PRIORITY WRITE 类似于WRITE LOCK，但在等待其他READ LOCK和WRITE LOCK全部结束的期间，允许其他用户先加一个新的READ LOCK。
UNLOCK TABLE 解除锁定，不需要任何参数。
GET_LOCK和RELEASE_LOCK函数
GET_LOCK(name,time) name是名字，time是持续时间，单位是秒。
RELEASE_LOCK(name)
本质上是对访问数据库的线程加锁(过程和VP原语一致)，MySQL服务器，数据库，和数据表不会锁定。

9 事务
MyISAM不支持，InnoDB支持
MySQL默认是自动提交事务模式。
可以用START TRANSACTION或BEGIN开始一个事务，用COMMIT或ROLLBACK结束事务，这将为本事务关闭自动提交模式。
InnDB不支持嵌套事务，如果上一个事务没有COMMIT或ROLLBACK又开始了一个新的事务，前一个事务按COMMIT方式结束。如果事务未结束是连接断开，则ROLLBACK。
事务的自动终止，事务还会因这些命令终止(视同执行了COMMIT):ALTER TABLE,CREATE INDEX,CREATE TABLE,DROP DATABASE,DROP TABLES,LOCK TABLES,RENAME TABLE,SET AUTOCOMMIT=1,TRUCATE,UNLOCK TABLES.但不会因事务内的某SELECT语句发生简单错误而终止。
当发出SET AUTOCOMMIT=0命令后，（有意或无意）与服务器的连接断开，所有没有COMMIT的命令都会被放弃。

事务与锁
大多数情况下，只要开始执行一个事务，InnoDB数据表驱动器就会把必要的锁定操作安排好，而在某些场合，这不一定是最好的。
SELECT... LOCK IN SHARE MODE
InnoDB的默认行为是SELECT 命令在被锁定的数据记录上也能立即执行，查询结果不受其他客户尚未提交或撤销的事务影响。好处是不会迟迟看不到查询结果，坏处是看到的结果可能已过时。
而加上LOCK IN SHARE MODE的查询语句会等到其他事务结束后进行查询。如果这个查询语句是某个事务的一部分，在它开始之后，这个事务结束之前，数据表有关数据将被锁定，其他客户将只能读取这些记录。
SELECT ... FOR UPDATE
加排他锁（exclusive lock）,删除修改和SELECT... LOCK IN SHARE MODE会被阻止。共享锁和排他锁的区别就是是否阻止其他连接发出SELECT... LOCK IN SHARE MODE。
放插入锁
InnoDB表驱动程序遇到带有条件范围的表达式（如WHERE id>100或WHERE id BETWEEN 100 AND 200）的SELECT... LOCK IN SHARE MODE，SELECT ... FOR UPDATE，UPDATE,DELETE时还会多加一把防插入锁（gap and next key lock）.符合现有条件的数据和符合现有条件的当前不存在的数据锁定。
如某事务内执行了SELECT ... FOR UPDATE WHERE id>100，那么在事务完成前，其他用户也无法插入id > 100的记录。
死锁
InnoDB能自动识别死锁，触发死锁的进程（后来的）会把所有未提交的命令回滚，另一个进程继续执行。
但是，如果死锁是一些及访问InnoDB又访问其他类型数据表的SQL命令引起的，InnoDB数据表驱动程序不一定能识别出来。可以通过设置参数innodb_lock_wait_timeout=n来设置最长等待时间（默认50秒）
事务隔离模式
开始事务前必须设置隔离模式。
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED | READ COMMITTED
REPEATABLE READ | SERIALIZABLE
不带关键字SESSION，GLOBAL的SET命令将只对下一个事务有效。
SET SESSION 为当前连接会话设置隔离模式，一致持续到下一条设置隔离模式的SET命令或者当前会话连接结束。
SET GLOBAL 为此后所有连接设置隔离模式，不包括当前连接。遇到下一条设置隔离模式的SET命令时失效。
READ UNCOMMITTED 事务中的SELECT命令能读到其他事务中尚未提交的修改。UPDATE会被隔离。
READ COMMITTED 事务中的SELECT命令能读到其他事务中已提交的修改。
REPEATABLE READ 事务中的SELECT命令不受其他事务影响，这是默认值。
SERIALIZABLE 与REPEATABLE READ类似，唯一区别是自动把所有的SELECT当做SELECT... LOCK IN SHARE MODE执行。
可以通过配置文件的transaction-isolation改变默认值。
通过SELECT @@tx_isolation,@@global.tx_isolation查看事务的隔离级别。
放插入锁发生的前提是SELECT中有范围条件表达式，在REPEATABLE READ | SERIALIZABLE隔离级别中会使用。

出错处理
事务结束之前相关数据会被锁定，小心客户端超时。
可能死锁。

10 安全
用户名最大16个字符，理论上可以用非ASCII字符，实际上为了避免不同操作系统处理特殊字符的方法不同，最好使用ASCII。
密码 45位加密串，只能读（无法直接修改）。
主机名可以使IP，否则必须能够识别这个名字。
USE mysql
SELCT user，host,password FROM user
设置root密码 UPDATE user SET password = PASSWORD('select') WHERE user = 'root';
重命名root用户 UPDATE user SET user = 'myroot' WHERE user = 'root';
删除匿名用户 DELETE FROM user WHERE user = ''
为用户设置密码 UPDATE user SET password = PASSWORD(secret) WHERE password = ''
处理可以从任何地方注册的用户 SELCET user,host,password FROM user WHERE host = '%'
使命令生效 PLUSH PRIVILEGES,因为MySQL在RAM中保存了一个mysql数据库副本。

创建数据库
CREATE DATEBASE forum
GRANT ALL ON forum.* TO forumadmin@localhost IDENTIFIED BY 'xxx'
GRANT Select,Insert,Update,Delete ON forum.* TO forumadmin@localhost IDENTIFIED BY 'xxx'
GRANT Lock Tables,Create Temporary Tables,Execute ON forum.* TO forumadmin@localhost
省略IDENTIFIED BY 'xxx'可以保持密码不变。

创建用户
授予 forumadmin2不加限制的访问权限。
GRANT ALL ON forum.* TO forumadmin2@uranus.sol IDENTIFIED BY 'xxx'
GRANT ALL ON *.* TO forumadmin2@uranus.sol IDENTIFIED BY 'xxx'
授予 forumadmin2等同于root的权限
GRANT ALL ON *.* TO forumadmin2@uranus.sol IDENTIFIED BY 'xxx' WITH GRANT OPTION

忘记root密码
首先要拥有安装MySQL服务器的操作系统的管理员权限。
终止MySQL运行。
在配置文件[mysqld]部分中([mysqld]下面第一行)，输入skip_grant_tables.UNIX/Linux:/etc/my.cnf,Windows:C:\Programs\MySQL\MySQL Server n.n\my.ini
重新启动MySQL
root# mysql -u root
mysql> USE mysql;
mysql> UPDATE user SET password = PASSWORD('new password')
> WHERE user = 'root' AND host = 'localhost';
下面这个仅在UNIX/Linux下可用。
mysql> UPDATE user SET password = PASSWORD('new password')
> WHERE user = 'root' AND host = 'computername';
停止MySQL运行。
删除配置文件的skip_grant_tables
重新启动MySQL。

MySQL的两级访问控制
连接权限用户名，密码，主机名
执行权限当前用户的权限配置
Grant Option 可以分配MySQL权限。管理数据库时，给权限表所有权限列输Y可能带来风险。
File 在文件系统允许的情况下，可以访问MySQL文件。可以执行SELECT...INTO OUTFILE或LOAD DATA，函数LOAD_FILE。
Process 使用SHOW PROCESSLIST获取所有连接的清单，包括其他用户。
Super 允许Kill结束其他用户进程。没有此权限则直接结束当期进程。还允许使用管理命令CHANGE MASTER执行镜像系统的客户配置程序，PURGE MASTER删除二进制文件，SET GLOBAL改变全局MySQL变量。
Global 允许访问所有MySQL对象（数据库，数据表，数据列）
Object 允许访问指定对象
information_schema 没有SELECT权限也可以访问。
为了安全，不要用操作系统的密码用作MySQL的密码。

user数据表的检查顺序可能导致无法预料的问题，在多个记录匹配时，选择最准确匹配，排序以Host为第一标准，User作为第二标准。没有通配符"_"和"%"的优先，空白和"%"在最后。从安全考虑，应删除User列为空白的条目。
限制MySQL的使用，数据列max_questions和max_updates可以规定每小时内至多允许多少次数据查询和修改。max_connections规定了每小时可以建立多少连接。

是否给主机加域名取决于服务器的解析方式，一般加上域名效果好。
REVOKE 解除权限，用法同GRANT
SHOW GRANTS FOR peter@localhost查看用户的权限
SHOW GRANTS FOR 'peter'@'%'
mysqladmin -u peter -p password newPW 改变密码，执行后需要输入当前密码
mysqladmin -u peter -p -h uranum.sol password newPW 使用TCP/IP远程连接UNIX/Linux上的MySQL

建立连接的问题，首先要使用mysql去连接。然后看是否执行了FLUSH PRIVILEGES。然后，只有在新的连接完成后，改变全局的权限才生效；只有执行USE table后，改变数据库的权限生效；在下一个SQL命令中，改变数据表和数据列权限才生效。
连接失败的可能原因
MySQL服务器没有运行。mysql会返回2002错误。
客户程序找不到套接字文件。UNIX/Linux上客户端和服务器装在同一机器上时几乎都用这种方式。发生这种问题时，首先检查配置文件/etc/my.cnf的[client]选项组是否有socket=filename,filename必须是套接字文件的真实路径，一般这个文件的名字是/var/lib/msyql /msyql.sock
客户程序不能访问套接字文件(SELinux)。Linux的一些发行版本(如Redhat4)上Apache能访问htdocs之外的目录。解决办法是改用TCP/IP通信或禁用Apache的SELinux功能（在RHEL发行版中，配置项是system-config-security）
网络连接中断。
不接受TCP/IP连接。可能是因为my.cnf中有 -skip-networking或类似的选项，该设置通常是为了提高安全性，这个问题可以通过2003错误识别。
MySQL拒绝接受来自正在使用的计算机的任何请求。通常会发生在MySQL服务器运行在网络服务供应商的计算机上的情况下。一般是因为服务器配置为只允许本地连接，可以使用telnet/ssh，或其他管理工具解决。
主机名解析不正确。mysql连接发生1130错误，解决方法是在user表的host列加上域名。
用户名或密码错误。
客户程序使用了一个过时的MySQL函数库来验证密码。4.0或更低版本和4.1或更高版本使用的验证机制不同。
在mysq.user中使用了错误的条目。精确匹配的条目会优先使用。
未给出MySQL的用户名。这时会使用操作系统的登录名。
连接成功但不能访问。通常出现错误1045，一般是权限问题。
无法创建本地TCP/IP连接。通常发生在UNIX/Linux下，最有可能的原因是主机名解析出现问题。
端口3306被阻塞。
升级MySQL服务器带来的问题。5.0以前的版本升级的5.0以上会出现问题。

检查错误的方法
如果配置正确但仍访问失败，首先停止服务，在[mysqld]加上skip-grant-table并重启，如果能连接则可以确定问题出在mysql数据库中，不要忘了删掉skip-grant-table。
因为MySQL会缓存最近的访问解析，停止服务在[mysqld]加上skip-host-cache并重启。

在高安全的行业（如，银行），不允许直接访问表，所有操作（即使是SELECT）都用存储过程做。看上去复杂，好处是数据库管理员可以监视每一个操作。
存储过程的缺点是很难移植，因为每种数据库的存储过程语法及其扩展语法都不同。
复杂的触发器可能对数据库性能造成严重的影响。
游标，只读的，只能前进，光标涉及的数据表不能变化，会引起难预料的行为。

备份数据库
InnoDB的默认行为
备份每一个数据表前加读操作锁
让备份结果文件尽可能的小。
在结果文件插入DROP TABLE命令，在恢复备份时删除现有的同名数据表。
把数据库的所有属性都保留下来。
使用UTF8字符集创建备份，并在备份结果文件里增加比要的SQL命令以确保在将来恢复备份时还能把字符集设置也恢复到原来的样子。
这些默认行为不适合这样的工作：
一次备份多个InnoDB数据表，除非都不发生变化；备份文件需要向后兼容MySQL的老版本或与其他的数据库系统保持兼容。
最稳妥的MyISAM数据库备份办法。默认设置下，可能有这样一种结果：数据表A引用了数据表B里的某条记录，但记录却在mysqldump程序备份数据表B之前删除了。解决方法是
> mysqldump -u root -p --lock-all-tables databasename > backup.sql
备份InnoDB数据库
在备份InnoDB数据表时，LOCK命令解决不了任何问题。mysqldump程序的--single-transaction选项可以把备份放到一个事务里完成。但是因为--single-transaction选项与默认设置不兼容，所以必须先用--skip-opt选项把默认设置全部禁用。
> mysqldump -u root -p --skip-opt --single-transaction --add-drop-table --create-options --quick --extended --extended-insert --set-charset -disable-keys databasename > backup.sql
视图与存储过程备份
单独备份存储过程
> mysqldump -u root -p "--where=db='dbname'" --no-create-info mysql proc > backup.sql
mybackup脚本，一个用Per语言写的备份工具。

导入和导出文本文件
LOAD DATA
mysqlimport
SELECT ... INTO FILE
mysql程序。

mysqldump --tab=verz [options] databasename tablename
mysqldump -u root -p --tab=c:\tmp --fields-enclosed-by=\ exceptions exporttable
mysqldump -u root -p --xml mylibrary > /tmp/mylibrary.xml

日志
启用二进制变更日志
[mysqld]
log-bin [=name]
如果使用镜像机制，应该给出文件名，否则主机网络名发生变化会导致日志文件的名字也发生变化，以前建立的镜像机制将无法工作。
MySQL会把真实的变化记入日志，而SELECT和没有改数据的UPDATE不会。
重启MySQL服务器时，自动开始把日志数据写入下一个日志文件。不重启服务器可以用FLUSH LOGS或执行外部程序mysqladmin flush-logs切换日志文件。如果超过max_binlog_size(默认1GB)，会自动创建并使用新的日志文件。
不需要的日志应该用PURGE MASTER LOGS TO命令删除，如果想删除所有的日志文件，执行RESET MASTER命令即可。
利用日志恢复数据库
应该先用最近一次制作的完整备份数据库，在依次使用最近一次备份之后生成的日志文件把数据库恢复到最近的可用状态。
root# mysqlbinlog name-bin.000031 | mysql -u root -p
root# mysqlbinlog name-bin.000032 | mysql -u root -p
root# mysqlbinlog name-bin.000033 | mysql -u root -p
为日志指定路径，为了安全，不要与数据文件放在同一个硬盘上。
[mysqld]
log-bin =/var/log/mysql/myupdatelog-bin
出错日志
无法禁用，但可以指定另一个文件名和存放地点。
[mysqld]
log-error =/var/log/mysql/mysqlerrorlog
登录和操作日志
如果配置文件里有，则记录
[mysqld]
log
慢查询日志
[mysqld]
log-slow-queries
long_query_time=5
long-queries-not-using-indexes
会把超过5秒的的查询，和没有使用索引的查询记入慢日志。
对于慢查询和登录日志，由于只有一个文件，只能越来越大。解决办法是在MySQL仍在运行的同时先对这两个文件进行重命名，然后刷新log。FLUSH LOGS或执行外部程序mysqladmin flush-logs
如果可能，先关闭服务，在重命名日志文件，然后重启服务。

镜像机制
如果只是因为速度和性能的方面才打算使用镜像机制，应当首先考虑其他的性能改善措施。
最好在最新版本上用(书的作者是在写书时是5.0.3),要确认无缝镜像关系(fail-safe replication)是否可用,怎样保证AUTO_INCREMENT编号值得唯一性。

管理MyISAM数据表
每个MyISAM数据表对应两个文件dbname/tablename.MYD(存数据)，dbname/tablename.MYDI(存索引).
myisamchk,检查数据表的数据是否完好无损，修复受损的MyISAM数据表文件（比如在供电中断故障之后），释放MyISAM未使用的空间，为MyISAM数据表重新建立索引。

表空间管理
默认情况下，MySQL服务器会在首次创建时创建一个长度为10MB的ibdata1文件作为表空间，每次以8MB为单位扩张。
为每个InnoDB数据表分别创建一个表空间文件的做法不是InnoDB的默认行为，需要innodb_file_per_table明确启用这个功能。而且，在设置这项功能之后，只对新建的InnoDB数据表起作用。
[mysqld]
innodb_file_per_table
同一个InnoDB的数据表的所有数据和索引都包含在一个表空间文件里。单个表空间文件会变慢，但差距相当小，好处是使用DROP TABLE命令使会删除表空间文件并
MySQL会自动创建一个10MB，每次增量8MB的masterspace
如果InnoDB的响应速度至关重要，应该把InnoDB日志文件与表空间文件安排在不同硬盘上。如果InnoDB发现日志与innodb_log_xxx选项不符，启动失败，出错信息写在hostname.err中。创建日志的操作也会记入hostname.err

InnoDB日志文件同步
innodb_flush_log_at_trx_commit和innodb_flush_method分别控制着何时以及如何对InnoDB进行同步。
innodb_flush_log_at_trx_commit设为0 日志数据每隔一秒写入当前日志文件并对该文件进行同步。（写入：数据传递给OS的IO函数，同步：物理的写到硬盘上。）如果在同步之前系统崩溃，事务就丢失了。不能保证ACID中的D
innodb_flush_log_at_trx_commit设为1 每执行完一条COMMIT就写一次并同步（默认值）。每秒执行事务数量受硬盘限制，小事务多时影响明显。
innodb_flush_log_at_trx_commit设为2 折中方案，每执行完一条COMMIT就写入日志文件，每隔一秒进行一次同步。如果MySQL服务崩溃，数据不会丢失。但操作系统崩溃，就和设置为0效果一样。
innodb_flush_method 选项负责在由操作系统提供的fsync()函数(默认设置)和O_SYNC()函数（设为O_DSYNC）
即使MySQL做了最安全的设置，数据安全性也取决于操作系统如何具体完成InnoDB日志文件的操作的。
速度优化技巧参考InnoDB的"Performance Tuning tips" http://dev.mysql.com/doc/mysql/en/innodb_tuning.html
与InnoDB日志有关的缓冲区配置选项。
innodb_buffer_pool_size 默认8MB，在线文档推荐的做法是把一台专用的数据库服务器80%的内存用做缓冲区，可以显著提高SELECT的执行速度，InnoDB事务日志文件的总长度应该和这个一样大。
innodb_log_buffer_size 给InnoDB事务日志缓冲区设置长度，innodb_additional_mem_pool_size 需要在RAM里存的其他信息(如数据表的元数据)的空间，如果正在与大量的InnoDB数据表打交道，应该增加这个值。（默认1MB）
进行大量锁定操作时的技巧（如，导入一个有100万数据的表，正在把MyISAM数据表转换为InnoDB）
SET unique_check = 0 不检查UNIQUE数据列或主索引数据列里的唯一性。
SET foreign_key_checks=0 不进行一致性检查，只有绝对肯定数据一致性完好无损的前提下才允许使用这种设置（比如，正在恢复一个备份）
把许多INSERT语句放在同一个事务里执行。只有日志文件足够大的时候才有效。注意，大型事务如果发生回滚需要很长时间。CREATE TABLE有COMMIT效果，所以不可能在同一个事务里导入多个表。

InnoDB的日志选项
innodb_flush_log_at_trx_commit=2 如果希望在每一秒钟执行尽可能多的小事务，同时万一发生崩溃时可能蒙受的数据损失有心理准备，这个设置就很合适。、
只要条件允许，就应该把InnoDB日志文件与表空间文件分别存在不同的硬盘上。
根据操作系统不同，innodb_flush_method=O_DSYNC可以加快InnoDB日志功能。

11 MySQL服务器的优化
优化是指通过调整MySQL服务器的配置参数得到一个最佳配置方案的过程。
优化过程要解决以下问题（一个大型解决方案的一小部分问题）
如何优化数据库的设计方案才能保证使用最频繁的命令以最高的效率执行？如何创建效果最佳的索引？（设计糟糕的数据库，无论怎样调整数据库也没有好效果）
给定预算范围内，什么样的硬件最适合这项任务？
什么样的操作系统最适合这个任务（如果有选择的话）
哪种数据表格最适合这个任务
如果SELECT查询的密度，规模很大，能不能把它们分散到多台计算机上去？怎样才能以最佳方式做到这一点。

优化内存管理
key_buffer_size 默认8MB，索引缓冲区的长度，这个值越大，对数据表里有索引的数据列访问越快。在专用服务器上，可以根据具体情况把这个值设置为RAM内存值的四分之一。
table_cache 默认64，可以同时打开数据表的个数，打开关闭数据表需要花费时间，这个值越大，能同时打开的数据表越多，实际使用时打开数据表的个数受内存限制。SHOW STATUS，open_tables显示实际打开的表数。
sort_buffer 默认2MB，排序缓冲区长度，如果没有索引可用，带ORDER BY或GROUP BY的SELECT命令将使用缓冲区进行排序。2MB的设置可以满足大部分需要。如果值过小会严重影响性能。
read_buffer_size 默认128KB，需要为每个线程保留多少内存供它从数据表的连续排列数据时使用。这个值不必很大，因为每个连接都有这个缓冲区，在需要的时候用SET SESSION read_buffer_size=n 临时修改一下即可。
read_rnd_buffer_size 默认256KB,与read_buffer_size类似，只是针对按特定顺序读取记录的情况（如ORDER BY），把这个值设大一些有助于减少读硬盘动作。也是在需要时用SET SESSION临时设置一下。
bulk_insert_buffer_size 默认8MB，一次性插入多条数据的INSERT（如 INSERT...SELECT...）使用的缓冲区长度。必要时用SET SESSION命令临时改变。
join_buffer_size 默认128KB，为没有索引可用的JOIN操作分配缓冲区。
temp_table_size 默认32MB，HEAP类型的临时数据表最大长度。实际长度超过这个值的HEAP临时数据表将被转换为MyISAM类型并被保存到一个临时文件。
max_connections 默认值100,同时打开连接的最大数。这个参数不必很大，因为每个连接要占用一些内存和一个文件描述符。这个值越大，可以打开的永久连接就越多。
如果使用InnoDB，千万不要忘记对有关InnoDB做出必要的修改。用SHOW VARIABLES LIKE '%size%'可以查看许多重要命令的当前值。详细内容参考在线文档http://dev.mysql.com/doc/mysql/en/show-variables.html

查询缓存区
查询缓存区只适用于数据修改操作相对非常小并且同样的查询操作经常重复操作的场合。（基于Web的数据库应用往往属于这种情况）
SELECT命令必须精确的相同（包括空格和字母大小写）才能让查询缓冲区知道它们是重复的。
SELECT命令不得包含用户定义变量，也不能使用某些特定的函数如RAND(),NOW(),CURTIME(),CURDATE(),LAST_INSERT_ID(),HOST()等。
如果这些条件得不到满足，查询缓存区的SELECT命令和结果会给系统带来负面影响。
启用查询缓存区（默认禁止）
[mysqld]
query_cahce_size=32M
query_cache_type=1 # 0=off,1=ON,2=Demand
query_cache_limit=50K
这些设置的效果是，查询缓存区32MB并启用，SELECT查询结果小于50K才能进入缓存区。
上述配置后，重新启动MySQL服务器就启用查询缓存区。
查询缓存区的demand模式，只有包含SQL_CACHE关键字的SELECT查询命令（SELECT SQL_CACHE * FROM authos）才会进入查询缓存区。如果想控制哪些命令进入缓存区，这个模式将很有用。
在使用了query_cache_type=1选项的前提下，如果不想让某条SELECT命令使用已被启用的查询缓存区，可以在查询命令里加上SQL_NO_CACHE.
SHOW STATUS LIKE "%qcache%" 查看缓存区的工作状态

MySQL的SQL
默认情况下，字符串比较不区分大小写

转载于:https://www.cnblogs.com/MeteorLocus/p/3337373.html