MySQL 基础总结

服务端：mysqld 客户端： mysql

常用管理命令：(注：所有要在服务端执行的命令都要以;结尾，以告诉服务器命令结束）

1. 登陆： mysql –uuser –ppassword –hhost 分别指定用户，密码和mysql主机即可登陆，也可以在家目录下创建个.my.cnf配置文件，把用户名和密码主机等配置在里面：假定root的密码是mypass

   [root@node1 ~]# cat << ‘EOF‘ > .my.cnf        
   > [client]        
   > user = root        
   > password = mypass        
   > host = localhost        
   > EOF

   当然如果在本机上登陆还是unix sock 的方式最快，消耗资源最小，因为这是在内核底层实现的，如果用IP地址登陆则还需要先送到内核的TCP/IP协议栈处理。

2. 给用户加密码：

   1. 在mysql命令行下执行 SET PASSWROD FOR ‘user’@’host’=PASSWORD(‘password’);

   2. Shell下用mysqladm修改： mysqladm –uroot –hlocalhost password ‘mypass’ –predhat （把root@localhost的密码由redhat改为mypass)

   3. 直接修改mysql库user表：UPDATE mysql.user SET PASSWORD=PASSWORD(‘redhat‘) WHERE User=‘root‘ AND Host=‘localhost‘;

3. 创建和删除用户：

   1. CREATE USER ‘root’@’172.16.100.100’ IDENTIFIED BY ‘mypass’;

   2. DROP USER ‘root’@’172.16.100.100’;

4. 忘记管理员密码咋整：

   1. 停掉mysqld服务后，mysqld_safe –-skip-grant(跳过授权表） –-skip-networking（不允许远程访问mysql，以保证安全），此方式启动后，无密码登陆后修改密码

5. 用户授权和取消授权：

   1. GRANT SELECT,UPDATE ON db_name.tb_name TO ’user’@’host’  [IDENTIFIED BY  PASSWORD ]; FLUSH PRIVILEGES;

   2. REVOKE SELECT,UPDATE ON db_name.tb_name FROM USER;

6. 查看已经授权用户信息：SHOW GRANTS\G

7. 查看mysql线程信息：SHOW PROCESSLIST;

8. 查看表索引信息：SHOW INDEXES FROM tb_name;

9. mysqld –help –verbose 可以查看mysql配置文件中可以使用的参数

10. 各种秀：SHOW DATABASES; SHOW TABLES; DESC tb_name; SHOW ERRORS; SHOW STATUS; SHOW BINARY LOGS; SHOW BINLOG EVENTS;

11. 查处某个库中的表的详细属性信息：SHOW TABLE STATUS RLIKE ‘%user%’\G (用RLIKE可以匹配正则表达式、\G以纵向的方式显示，当列比较多时候比较有效果，\g可以直接送到服务端，不用再加分号）

12. SHOW MASTER STATUS; 可查看当前二进制日志的position

13. SHOW GLOBAL | SESSION VARIABLES LIKE | RLIKE ‘%log%’; 查看服务器变量信息

14. SET GLOBAL.var_name=VALUE;

15. SET @@GLOBAL.var_name=VALUE;

16. SELECT DATABASE(); SELECT LAST_INSERT_ID();

17. SHOW CREATE TABLE user; SHOW ENGINE INNODB STATUS;

18. HELP Contents; –> SHOW Administrations; ….

19. mysql> source /tmp/backup.sql | mysql –uroot –pmypass < /tmp/backup.sql

20. mysql -uroot -predhat -e ‘SHOW DATABASES;‘

Tips: 由于缓存是对查询语句做哈希作为键存储的，而Hash是case-sensitive的，所以呢为了提高缓存命中率，SQL语句要保持一定的风格，例如把所以命令关键字全部大写。养成良好的编程习惯利人利已。

关于存储引擎：mysql是插件式存储引擎，可以按需随时切换（存储引擎可以理解为数据库的逻辑数据与磁盘上二进制数据的中间件，它完成两种数据格式之间的相互转换过程）

MyISAM: 是ISAM的改进版本，不支持热备，支持温备，支持B-tree indexes，不支持T-tree indexes 和Hash indexes，支持全文检索（full-text search indexes：可以索引到一个字段上的所有数据上)等，和InnoDB最大的区别在于不支持事务（Transaction), 支持表级别的锁，InnoDB支持行级别的锁，支持延迟更新索引键（delay key update)

MyISAM数据文件存储形式：db_name.MYI index文件， db_name.MYD data文件， db_name.frm framwork表定义文件，存放表结构信息等

适合数据仓库、olap(online analytical processing)、写入少，查询多的场景，对大数据场景支持不好，数据库很大（超过500G)时一旦崩溃，很难恢复，MariaDB 启用aira引擎，是改良的myisam，提供了安全的崩溃恢复。

InnoDB: InnoDB公司开发的支持事务和外键的存储引擎，也吸收了非死不可、谷哥等公司大牛的贡献，后被假骨人收购后就走上了商业化的道路，不过这假骨头的人也对innodb做了n多的改进和优化。

InnoDB支持的行级别的锁并不是绝对的，当执行的语句所影响的行不确定时，是对全表进行锁定的，例如 SELECT * FROM db_name.tb_name WHERE Name LIKE ‘%mysql%’;

innodb支持 外键、事务、MVCC(Multi-Version Concurrency Control) 多版本并发控制、聚簇索引（索引和表数据放在一起并按一定的顺序排列，是有序数据，而myisam存储的是堆数据，heap,不支持聚簇索引）、间隙锁（Gap Lock:间隙锁，锁定一个范围，但不包含记录本身）、辅助索引、自适应hash索引、支持热备、行级锁

数据文件存放：ibdata1 表空间文件，建议开启innodb_file_per_table = 1,每表一个表空间，tb_name.ibd,否则所有表放在一个文件中不便于管理，但是，单独的表空间文件仅存储该表的数据、索引和插入缓冲等信息，其余信息还是存放在默认的表空间中。tb_name.frm 表定义文件

MEMORY 创建临时表时用到，内存存储引擎

BLACKHOLE 黑洞，在复制结构中，为减少master压力和二进制日志复制次数，只复制一份到一个slave上，其他slave全部到此slave上复制binlog, 而此slave并不存储数据，只提供binlog源，应用relaylog时全部扔到BLACKHOLE去避免不必要的IO。

CSV 可以把数据导出为csv文本格式，可以在不同数据库之间交换数据

ARCHIVE 5.5后支持索引，适用于存储日志等按时间序列的数据采集，仅支持insert, select， 支持很好的压缩功能

FEDRATED:

        能够访问远程服务器上的数据的存储引擎，FedratedX

MRG_MYISAM merge MySQL已经支持表分区，这个用处就不大了

        能够合并多个MyISAM表的存储引擎，

NDB：集群存储引擎，专用于MySQL Cluster

第三方存储引擎，

    OLTP类，在线事务处理

        XtraDB, InnoDB的增强版

        PBXT: 支持事务，社区不活跃，MariaDB 5.5.32后原生也不再支持

TokuDB 很牛逼，已经开源，MySQL插件，可以直接使用，支持分形树索引结构，适用于需要插入大量数据的分析型场景

    图示数据库，有着复杂连接的

    列式数据库，最适于存储海量数据，

列式存储引擎：

    Infobright: 最著名，真正在生产环境中使用，数十TB, 支持压缩，如果使用需要对mysql服务器定制

    InfiniDB

    MonetDB

    LucidDB

    社区存储引擎

        Aria

        Groona: 全文索引引擎

        QQGraph: 支持图操作，Open query 研发

        SphinxSE：狮身人面象，已经被Maria社区采用，MariaDB 5.5.32+

        Spider：支持分片（sharding）

        VPForMySQL：支持垂直分区，vertical partition, spider作者写的

关于MySQL日志：

    二进制日志：记录会引起数据库数据变化的语句：

• • SHOW BINARY | MASTER LOGS;

  • SHOW MASTER STATUS;

  • SHOW BINLOG EVENTS [IN ‘log_name’] [FROM position] [LIMIT n] 偏移n条语句

  • FLUSH LOGS; 一般只滚动binlog 和 relaylog, 每次mysql服务的重启也会引起binlog滚动，日志大小达到上限也会自动滚动，一般默认1G

  • 清除binlog安全的方式：PURGE BINARY | MASTER LOGS TO ‘log_name’ | BEFORE ‘2012-10-10 12:09:09’;

  • log-bin = /path/to/some_log_file

  • expire_log_days = 100

  • sync_binlog 事务提交时，是否从cache刷写到磁盘，建议开启，对性能稍微有些影响，但是更可靠，worthful

  • mysqlbinlog –end-position=234 mysql_bin.000012 > /tmp/1.sql

  • mysql –uroot –pmypass < /tmp/1.sql

  • 在用二进制恢复数据库时，要把二进制日志关掉，不然重复记录操作，徒增二进制空间

innodb_flush_log_at_trx_commit = 0|1|2 日志刷写到磁盘频率，0 --> 1秒一次，1 --> 每次事务提交就写，2 --> 0+1

慢查询日志：建议开启，记录执行时间长的查询过程以方便分析优化

• slow_query_log 定义是否开启慢查询日志

• slow_query_log_file 定义慢查询日志文件存储位置

• long_query_time 定义查询时长达到多少被认为是慢查询

• 慢查询时长：从查询启动到查询完成，中间无论什么原因导致都计算在内，墙上挂钟时间而非CPU时间

错误日志

• 记录服务器启动和关闭过程中的信息

• 记录服务器运行中的错误信息

• 事件调度器运行一个事件时产生的信息

• 在从服务器上启动从服务器进程时产生的信息

查询日志：一般不建议开户，消耗服务器资源，影响性能

事务日志：把无序IO转化为有序IO,不宜过大，如果过大服务器从崩溃是恢复会消耗很大时长

中继日志：用于复制场景，slave从master复制过来的binlog先放到中继日志，然后再应用到本地

关于innodb,关于事务：(参考http://javabkb.iteye.com/blog/1441197内容）

• 事务自然要支持ACID

  • Atomicity 原子性：整个事务中的所有操作要么全部完成，要么全部不完成，如果执行过程中出现错误则回滚，数据和没发生这个事务一样

  • Consistency 一致性

  • Isolation 隔离性

  • Durability 持久性

• 重做日志：实例和介质失败，重做日志文件就能派上用场，如数据库掉电，InnoDB存储引擎会使用重做日志恢复到掉电前的时刻，以此来保证数据的完整性。参数innodb_log_file_size指定了重做日志文件的大小；innodb_log_file_in_group指定了日志文件组中重做日志文件的数量，默认为2，innodb_mirrored_log_groups指定了日志镜像文件组的数量，默认为1，代表只有一个日志文件组，没有镜像；innodb_log_group_home_dir指定了日志文件组所在路径，默认在数据库路径下。      
       二进制日志和重做日志的区别：首先，二进制日志会记录所有与Mysql有关的会引起数据变化的日志记录，包括InnoDB、MyISAM、Heap等其他存储引擎的日志。而InnoDB存储引擎重做日志只存储有关其本身的事务日志；其次内容不同，不管将二进制日志文件记录的格式设为STATEMENT还是ROW，又或者是MIXED，其记录的都是关于一个事务的具体操作内容。而InnoDB存储引擎的重做日志文件记录的关于每个页的更改的物理情况 。此外，写入时间不同，二进制日志文件是在事务提交前进行记录的，而在事务进行的过程中，不断有重做日志条目被 写入重做日志文件中。

• InnoDB 表结构

  • 表空间：innodb存储引擎逻辑结构的最高层

  • 段：表空间由各个段组成，如数据段、索引段、回滚段等

  • 区：64个连续的页组成，每个页16kb，即每个区1MB

  • 页：每页16kb,且不能更改，数据页、undo页、系统页、事务数据页、插入缓冲位图页、插入缓冲空闲列表页、未压缩的二进制大对象页、压缩的二进制大对象页

  • 行：innodb存储引擎是面向行的（row-oriented),每页最多允许存放7992行数据

  • 行记录格式：常见两种行记录格式Compact和Redundant，mysql 5.1版本后，主要是Compact，此格式不管CHAR还是VARCHAR, null不占存储空间，对于Redudant, VARCHAR的null不占空间，CHAR的null占存储空间。

• 锁：

  • 共享锁(S Lock)：允许事务读一行数据；

  • 排他锁(X Lock)：允许事务删除或者更新一行数据。          
         当一个事务已经获得了行r的共享锁，那么另外的事务可以立即获得行r的共享锁，因为读取没有改变行r的数据，我们称这种情况为锁兼容。但如果有事务想获得行r的排他锁，则它必须等待事务释放行r上的共享锁--这种情况称为锁不兼容。

  • 在InnoDB Plugin之前，只能通过SHOW FULL PROCESSLIST，SHOW ENGINE INOODB STATUS等命令来查看当前的数据库请求，然后再判断当前事务中的锁的情况。新版本的InnoDB Plugin中，在INFORMATION_SCHEMA架构下添加了INNODB_TRX、INNODB_LOCKS、InnoDB_LOCK_WAITS。通过这三张表，可以更简单地监控当前的事务并分析可能存在的锁的问题。

  • 一致性的非锁定读：InnoDB存储引擎通过行多版本控制的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行Delete、update操作，这时读取操作不会因此而会等待行上锁的释放，相反，InnoDB存储引擎会去读取行的一个快照数据。快照数据是指该行之前版本的数据，该实现是通过Undo段来实现。而Undo用来事务中回滚数据，因此快照本身是没有额外开销的。此外，快照数据是不需要上锁的，因为没有必要对历史的数据进行修改。一个行可能有不止一个快照数据，所以称这种技术为行多版本技术。由此带来并发控制，称之为多版本并发控制(Multi VersionConcurrency Control, MVCC)。

• 锁的算法：      
       Record Lock:单行记录上的锁      
       Gap Lock:间隙锁，锁定一个范围，但不包含记录本身      
       Next-Key Lock:Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身。

• 锁的问题：

  • 丢失更新：经典的数据库问题，当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所做的更新，这将导致数据丢失。          
             例:          
                 事务A和事务B同时修改某行的值，          
                  1.事务A将数值改为1并提交          
                  2.事务B将数值改为2并提交。          
                  这时数据的值为2，事务A所做的更新将会丢失。          
                  解决办法：事务并行变串行操作，对更新操作加排他锁

  • 脏读：一个事务读到另一个事务未提交的更新数据，即读到脏数据。          
              例:          
                  1.Mary的原工资为1000, 财务人员将Mary的工资改为了8000(但未提交事务)       
                  2.Mary读取自己的工资 ,发现自己的工资变为了8000，欢天喜地！          
                  3.而财务发现操作有误，回滚了事务,Mary的工资又变为了1000, 像这样,Mary记取的工资数8000是一个脏数据。          
                  解决办法：脏读只有在事务隔离级别是Read Uncommitted的情况下才会出现，innoDB默认隔离级别是Repeatable Read，所以生产环境下不会出现脏读。

  • 不可重复读：在同一个事务中,多次读取同一数据,返回的结果有所不同。换句话说就是,后续读取可以读到另一个事务已提交的更新数据。相反"可重复读"在同一事务多次读取数据时,能够保证所读数据一样,也就是后续读取不能读到另一事务已提交的更新数据。脏读和不可重复读的主要区别在于，脏读是读到未提交的数据，不可重复读是读到已提交的数据。          
              例:          
                  1.在事务1中，Mary 读取了自己的工资为1000,操作并没有完成          
                  2.在事务2中，这时财务人员修改了Mary的工资为2000,并提交了事务.          
                  3.在事务1中，Mary 再次读取自己的工资时，工资变为了2000          
                  解决办法：读到已提交的数据，一般数据库是可接受的，因此事务隔离级别一般设为Read Committed。Mysql InnoDB通过Next-Key Lock算法避免不可重复读，默认隔离级别为Repeatable Read。

• 事务的隔离级别：Read uncommitted、Read committed、Repeatable read、serializable。在Read Committed和Repeatable Read下，InnoDB存储引擎使用非锁定一致性读。然而，对于快照的定义却不同。在Read Committed事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。在Repeatable事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。  

本文出自 “不懂IT的中医不是好IT” 博客，请务必保留此出处http://zhishen.blog.51cto.com/1612050/1531580

MySQL 基础总结,古老的榕树,5-wow.com