Home | 简体中文 | 繁体中文 | 杂文 | 打赏(Donations) | 云栖社区 | OSChina 博客 | Facebook | Linkedin | 知乎专栏 | Github | Search | About

Netkiller Database 手札

PostgreSQL, MySQL, Oracle ...

Mr. Neo Chan, 陈景峯(BG7NYT)


中国广东省深圳市龙华新区民治街道溪山美地
518131
+86 13113668890


文档初创于April 6, 2008

版权声明

转载请与作者联系,转载时请务必标明文章原始出处和作者信息及本声明。

http://www.netkiller.cn
http://netkiller.github.io
http://netkiller.sourceforge.net
微信订阅号 netkiller-ebook (微信扫描二维码)
QQ:13721218 请注明“读者”
QQ群:128659835 请注明“读者”

$Date: 2013-05-21 19:01:33 +0800 (Tue, 21 May 2013) $


目录

1. 自述
1.1. 系列文档
1.2. 作者简介
1.3. 写给读者
1.4. 文档下载
1.4.1. Netkiller 手札 2017版电子书(epub,kindle,chm,pdf)
1.5. 打赏(Donations)
1.5.1. 微信(Wechat)
1.5.2. 支付宝(Alipay)
1.5.3. PayPal Donations
1.5.4. 银行(Bank)
I. Database Relational
1. DB-Engines - DB-Engines Ranking
2. SysBench
3. SuperSmack
4. ETL (Extract-Transform-Load)
4.1. Kettle
4.2. suro
5. 数据迁移
5.1. Apache Sqoop
6. GreenSQL
7. Database design & E-R diagram
7.1. opensource database design tools
7.2. OpenSystemArchitect
7.3. SQL Power Architect
II. Search Engine
8. Elasticsearch
8.1. 安装 Elasticsearch
8.1.1. 6.x 安装
8.1.2. 单机模式 (适用于开发环境) 5.x
8.1.3. Elasticsearch Cluster 5.x
8.1.4. 负载均衡配置
8.1.5. 安装指定版本的 Elasticsearch
8.1.6. Plugin
8.1.6.1. elasticsearch-analysis-ik
8.1.6.2. elasticsearch-analysis-pinyin
8.2. 文档API
8.2.1. 快速上手
8.2.2. 写入 PUT/POST
8.2.3. 获取 GET
8.2.3.1. _source
8.2.4. 检查记录是否存在
8.2.5. 删除 Delete
8.2.6. 参数
8.2.6.1. pretty 格式化 json
8.3. 搜索
8.3.1. URL 搜索
8.3.2. 分页
8.4. Query DSL
8.4.1. match 匹配
8.4.2. multi_match 多字段匹配
8.4.3. Query bool 布尔条件
8.4.3.1. must
8.4.3.2. should
8.4.3.3. must_not
8.4.4. filter 过滤
8.4.5. sort 排序
8.4.6. _source
8.4.7. highlight 高亮处理
8.5. 集群管理
8.5.1. 节点健康状态
8.5.2. 节点http状态
8.5.3. 查看master节点
8.5.4. 查看索引的节点分布
8.5.5. 索引的开启与关闭
8.5.5.1. _open
8.5.5.2. _close
8.6. 中文分词插件管理
8.6.1. 通过 elasticsearch-plugin 命令安装分词插件
8.6.2. 手工安装插件
8.6.3. 创建索引
8.6.4. 删除索引
8.6.5. 配置索引分词插件
8.6.5.1. 测试分词效果
8.7. 索引管理
8.7.1. 查看索引
8.7.2. 删除索引
8.8. 映射
8.8.1. 查看 _mapping
8.8.2. 删除 _mapping
8.8.3. 创建 _mapping
8.8.4. 更新 mapping
8.8.5. 修改 _mapping
8.8.6. 数据类型
8.8.6.1. date
8.9. Alias management 别名管理
8.9.1. 查看索引别名
8.9.2. 创建索引别名
8.9.3. 修改别名
8.9.4. 删除别名
8.10. Example
8.10.1. 新闻资讯应用案例
8.10.2. 文章搜索案例
8.11. Migrating MySQL Data into Elasticsearch using logstash
8.11.1. 安装 logstash
8.11.2. 配置 logstash
8.11.3. 启动 Logstash
8.11.4. 验证
8.11.5. 配置模板
8.11.5.1. 全量导入
8.11.5.2. 多表导入
8.11.5.3. 通过 ID 主键字段增量复制数据
8.11.5.4. 通过日期字段增量复制数据
8.11.5.5. 指定SQL文件
8.11.5.6. 参数传递
8.11.5.7. 控制返回JDBC数据量
8.11.5.8. 输出到不同的 Elasticsearch 中
8.11.5.9. 日期格式转换
8.11.5.10. example
8.11.6. 解决数据不对称问题
8.11.7. 修改 Mapping
8.12. 安装 Elasticsearch 2.3
8.12.1. RPM 安装
8.12.2. YUM 安装
8.12.3. 测试安装是否正常
8.12.4. Plugin 插件管理
8.12.4.1. 手工安装插件
8.12.4.2. plugin 命令
8.12.4.3. 插件测试
8.13. FAQ
8.13.1. Plugin [analysis-ik] is incompatible with Elasticsearch [2.3.5]. Was designed for version [2.3.4]
8.13.2. plugin [analysis-ik] is incompatible with version [5.6.1]; was designed for version [5.5.2]
8.13.3. mapper_parsing_exception: failed to parse [ctime]
8.13.4. 配置 JAVA_HOME
9. Solr
9.1. 安装
9.2. Core Admin
9.2.1. Schema
9.3. 索引
9.3.1. Indexing a directory of "rich" files
9.3.2. Indexing Solr XML
9.3.3. Indexing JSON
9.3.4. Deleting Data
9.4. 接口
9.4.1. 查询
9.5. FAQ
9.5.1. NOTE: Please install lsof as this script needs it to determine if Solr is listening on port 8983.
9.6. Solr 1.3.0
9.6.1. Embedded Jetty
9.6.2. Jetty
9.6.3. Tomcat
9.6.4. solr-php-client
9.6.5. multicore
9.6.6. 中文分词
9.6.6.1. ChineseTokenizerFactory
9.6.6.2. CJK
9.6.6.3. mmseg4j
9.6.6.4. 中文分词“庖丁解牛” Paoding Analysis
10. Nutch
11. Sphinx
12. Lucene
13. MG4J
14. PhpDig
15. Mahout
16. Apache Hbase
16.1. 安装 Apache Hbase
16.1.1. 单机模式安装
16.1.2. 伪分布式模式
16.1.3. 分布式模式部署
16.1.4. 运维技巧
16.2. 配置 Apache Hbase
16.2.1. hbase-env.sh
16.2.2. hbase-site.xml
16.3. Hbase Shell
16.3.1. 表操作
16.4. Web UI
16.5. Phoenix
16.5.1. 安装 Phoenix
16.5.2. sqlline.py 命令行界面
16.5.2.1. 帮助信息
16.5.2.2. 创建表
16.5.3. SQuirreL SQL Client
16.6. FAQ
16.6.1. HBaseConfTool : Unsupported major.minor version 51.0
16.6.2. ignoring option PermSize=128m; support was removed in 8.0
17. Apache Hive
17.1. 安装 Apache Hive
17.1.1. MySQL
17.1.2. Hadoop
17.1.3. Hive
17.1.4. 启动 Hive
17.1.5. 访问 Hive
17.1.6. 配置 hiveserver2
17.2. beeline
17.3. 管理 Hive
17.3.1. 表管理
17.3.1.1. 创建表
17.3.1.2. 显示表
17.3.1.3. 删除表
17.3.1.4. 查看表结构
17.3.1.5. 为表增加字段
17.3.1.6. 修改表名称
17.3.1.7. 使用已有表结构创建新表
17.3.2. 分区表
17.3.2.1. 创建分区表
17.3.2.2. 显示分区情况
17.3.2.3. 增加分区
17.3.2.4. 向分区表导入数据
17.3.3. 视图管理
17.3.3.1. 创建视图
17.3.3.2. 删除视图
17.3.4. 数据管理
17.3.4.1. 从文本文件导入数据
17.3.4.2. 从其他表查询数据并创建新表
17.3.4.3. 从其他表查询数据然后插入指定表中
17.3.4.4. 从现有表中查询数据然后插入到新的分区表中
17.3.5. HDFS与本地文件系统管理
17.3.5.1. HDFS 目录迁移
17.3.5.2. 导出表数据到本地文件
17.3.5.3. 导出到HDFS
17.3.5.4.
17.4. HiveQL - Hive查询语言
17.4.1. JOIN 连接查询
17.4.2. 子查询
17.5. FAQ
17.5.1. adoop.security.authorize.AuthorizationException): User: hadoop is not allowed to impersonate anonymous
18. Apache Sqoop
18.1. 安装 Sqoop
18.2. sqoop2-tool
18.2.1. verify
18.2.2. upgrade
18.3. sqoop2-shell
18.3.1. show version
18.3.2. set
18.3.2.1. server
18.3.2.2. 要设置可查看具体出错信息
18.3.3. show connector
18.3.4. link
18.3.4.1. hdfs-connector
18.3.4.2. generic-jdbc-connector
18.3.5. job
18.3.5.1. create job
18.3.5.2. show job
18.3.5.3. start job
18.3.5.4. status job
18.3.6. update
18.3.6.1. link
18.4. FAQ
18.4.1. Unable to load native-hadoop library for your platform
III. MySQL
19. MySQL Server
19.1. MySQL Installation
19.1.1. Installation by apt under debian/ubuntu
19.1.1.1. mysql-5.5.21-debian6.0-i686.deb
19.1.2. Installation by source code
19.1.3. MySQL binary distribution
19.1.4. CentOS
19.1.4.1. CentOS 6.2 + MySQL 5.5.25 (RPM)
19.1.4.2. MySQL 8.0
19.1.5. mysql-admin
19.1.6. Installing MySQL on Linux Using the MySQL Yum Repository
19.1.6.1. MySQL 5.6
19.1.6.2. MySQL 5.7
19.1.7. Firewall
19.1.8. Limit 状态
19.1.9. 使用 Btrfs 文件系统存储mysql数据
19.1.10. Mac OS
19.2. MariaDB
19.2.1. CentOS YUM 安装 MariaDB
19.2.2. MariaDB Audit Plugin
19.3. Percona
19.3.1. Percona yum Repository
19.3.2. Percona XtraBackup
19.3.2.1. 安装 XtraBackup
19.3.2.2. innobackupex
19.3.2.2.1. 备份数据库
19.3.2.2.2. 恢复数据库
19.3.2.3. xbstream
19.3.2.4. xtrabackup
19.3.3. Percona Toolkit - MySQL Management Software
19.4. my.cnf
19.4.1. bind-address
19.4.2. 禁用TCP/IP链接
19.4.3. 配置字符集
19.4.4. 最大链接数 max_connections
19.4.5. 默认引擎 storage-engine
19.4.6. max_allowed_packet
19.4.7. skip-name-resolve
19.4.8. timeout
19.4.9. 与复制有关的参数
19.4.9.1. 用于主库的选项 Master
19.4.9.2. 用于从库的选项 Slave
19.4.9.3. 逃过错误
19.4.10. 与 InnoDB 有关的配置项
19.4.11. EVENT 设置
19.4.12. 日志
19.4.13. MySQL 5.7 my.cnf 实例
19.4.14. Example for my.cnf
19.5. MySQL Plugin
19.5.1. validate_password
19.5.2. MySQL Images manager
19.5.3. MySQL fifo
19.5.4. 内容输出到文本插件
19.6. Replication
19.6.1. Master Slave
19.6.1.1. Master
19.6.1.2. Slave
19.6.1.3. Testing
19.6.1.4. 将现有数据库迁移到主从结构数据库
19.6.1.5. 主从复制安全问题
19.6.2. Master Master(主主)
19.6.2.1. Master A
19.6.2.2. Master B
19.6.2.3. 将Master A 数据库 同步到 Master B 两端数据库内容保持一致
19.6.2.4. Master A - B 同步两端数据库
19.6.2.5. Master A 数据库解除只读权限
19.6.2.6. 查看主主的工作状态
19.6.3. Semisynchronous Replication
19.6.3.1. Master
19.6.3.2. Slave 配置
19.6.3.3. 卸载插件
19.6.3.4. my.cnf
19.6.4. multi-master replication
19.6.5. multi-source replication
19.6.6. 与复制有关的问题
19.6.6.1. 主从不同步问题
19.6.6.2. mysql-bin 清理问题
19.6.6.3. 跳过 Last_Errno
19.6.6.4. 重置Slave
19.6.7. GTID
19.6.7.1. Master
19.6.7.2. Slave
19.7. MySQL Custer
19.7.1. Management node (MGM node)
19.7.2. Data node
19.7.3. SQL node
19.7.4. Starting
19.7.5. Shutdown
19.7.6. Testing
19.8. MySQL Proxy
19.8.1. Ubuntu
19.8.2. CentOS
19.8.2.1. FAQ
19.9. MySQL Router
19.9.1. 安装 MySQL Router
19.9.2. 配置 MySQL Router
19.9.2.1. 主备配置
19.9.2.2. 负载均衡配置
19.9.3. MySQL Router , Haproxy,LVS 的选择
19.10. variables
19.10.1. time_zone
19.10.2. sql_mode
19.10.2.1. 设置 sql_mode
19.10.2.2. 查看 sql_mode
19.10.2.3. 兼容早起 MySQL 版本
19.10.2.4. 5.7.16
19.10.3. wait_timeout
19.10.4. table_lock_wait_timeout
19.10.5. low_priority_updates
19.10.6. collation_server
19.10.7. character_set
19.10.8. datadir
19.10.9. plugin_dir
19.10.10. storage_engine
19.10.11. timeout
19.10.12. max_connections
19.11. SHOW COMMAND
19.11.1. 查看版本
19.11.2. status
19.11.2.1. show status
19.11.2.2. show master status
19.11.2.3. show slave status
19.11.2.4. show plugins
19.11.3. show processlist
19.11.4. binary 日志
19.11.5. 线程的使用情况
19.11.6. DATABASES
19.11.7. TABLE
19.11.8. 临时表
19.11.9. 排序统计信息
19.11.10. Key 状态
19.11.11. FUNCTION
19.11.12. PROCEDURE
19.11.13. TRIGGERS
19.11.14. EVENTS
19.11.15. 引擎(ENGINES)
19.11.16. 字符集(Collation)
19.11.17. SHOW GRANTS
19.12. Monitoring
19.12.1. Analysis and Optimization
19.12.1.1. mytop - top like query monitor for MySQL
19.12.1.2. mtop - MySQL terminal based query monitor
19.12.1.3. innotop
19.12.1.4. mysqlreport - A friendly report of important MySQL status values
19.12.1.5. mysqltuner - MySQL configuration assistant
19.12.2. Munin
19.12.3. Cacti
19.12.4. Monitoring MySQL with SNMP
20. Client and Utility Programs
20.1. mysql - the MySQL command-line tool
20.1.1. ~/.my.cnf
20.1.2. 屏幕输出到文件
20.1.3. 终端编码
20.1.4. Unix Socket
20.1.5. 重定向巧用
20.1.6. --sigint-ignore 忽略 Ctrl + C
20.2. mysqldump - a database backup program
20.2.1. 备份数据库,无结构,只有数据
20.2.2. 备份数据库结构(不备份数据)
20.2.3. 使用完整的insert插入数据
20.2.4. --extended-insert / --skip-extended-insert
20.2.5. --skip-lock-tables
20.2.6. --skip-add-locks
20.2.7. --where
20.2.8. 注释信息--comments /--skip-comments
20.3. mysqladmin - client for administering a MySQL server
20.3.1. reload
20.3.2. 更改密码
20.3.3. status
20.3.4. process list
20.4. myisamchk — MyISAM Table-Maintenance Utility
20.5. mysqlcheck — A Table Maintenance and Repair Program
20.6. mysqlslap - load emulation client
20.7. mysqldumpslow - Parse and summarize the MySQL slow query log.
20.8. mysql log
21. Database Administration
21.1. User Account Management
21.1.1. Create User
21.1.2. Drop User
21.1.3. Rename User
21.1.4. SET PASSWORD
21.2. Access Privilege System
21.2.1. SHOW GRANTS
21.2.2. show privileges
21.2.3. Grant privileges
21.2.4. Revoke privileges
21.2.5. Show Privileges
21.2.6. MAX_QUERIES_PER_HOUR/MAX_UPDATES_PER_HOUR
21.2.7. Table Privileges
21.2.8. Column Privileges
21.3. Maintenance 数据库维护
21.3.1. CHECK 检查表
21.3.2. ANALYZE 分析表
21.3.3. CHECKSUM
21.3.4. OPTIMIZE 优化表
21.3.5. REPAIR 修复
21.4. INFORMATION_SCHEMA
21.4.1. 查询表字段
21.4.2. 列出所有触发器
21.5. Backup and Recovery
21.5.1. Import / Export
21.5.1.1. Export(Backup)
21.5.1.2. Import(Recovery)
21.5.1.3. xml
21.5.1.4. 备份表数据
21.5.1.5. source
21.5.1.6. 使用 mysqlhotcopy 备份 MyISAM 引擎的数据库
21.5.1.7. AutoMySQLBackup
21.5.1.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.
21.5.1.8.1. Percona yum Repository
21.5.1.8.2. Creating an Incremental Backup
21.5.2. Snapshot Backup
21.5.2.1. LVM Snapshot
21.5.2.2. Btrfs Snapshot
22. DDL - Data Definition Language
22.1. 数据库管理(Database)
22.1.1. create
22.1.2. drop
22.1.3. Alter
22.1.4. Rename
22.1.5. CHARACTER
22.1.6. show create database
22.2. 表管理(Table)
22.2.1. 数据类型
22.2.1.1. SET 集合类型
22.2.2. create table ... select
22.2.3. modifiy table
22.2.4. TEMPORARY Table
22.2.5. Collate
22.2.6. CHARACTER
22.2.7. DEFAULT
22.2.7.1. AUTO_INCREMENT
22.2.7.2. TIMESTAMP NULL DEFAULT NULL ON UPDATE
22.2.7.3. 表存储位置(DATA DIRECTORY)
22.2.8. KEY
22.2.8.1. PRIMARY KEY
22.2.9. COMMENT
22.2.10. Engine 存储引擎
22.2.10.1. 显示当前数据库支持引擎
22.2.10.2. 切换引擎
22.2.10.3. FEDERATED
22.2.10.4. BLACKHOLE
22.2.10.5. ARCHIVE
22.2.10.6. CSV
22.3. Partitioning
22.3.1. RANGE
22.3.2. LIST
22.3.3. HASH
22.3.3.1. LINEAR HASH
22.3.4. KEY分区
22.3.5. Subpartitioning
22.3.6. 分区管理
22.3.6.1. 新增分区
22.3.6.2. 删除分区
22.3.6.3. 重建分区
22.3.6.4. 分区维护
22.3.7. EXPLAIN PARTITIONS
22.3.8. SHOW CREATE TABLE
22.3.9. INFORMATION_SCHEMA.partitions 表
22.3.10. 分区数据操作
22.4. Index
22.4.1. SHOW INDEX
22.4.2. CREATE INDEX
22.4.3. DROP INDEX
22.4.4. rebuild
22.5. 外键(Foreign Key)
22.5.1. FOREIGN KEY (RESTRICT)
22.6. 视图(View)
22.7. 存储过程(PROCEDURE)
22.7.1. 存储程序
22.7.2. EXECUTE 执行 SQL
22.7.3. PREPARE 传递参数
22.7.4. 存储过程返回数据
22.7.5. 结果集转JSON
22.7.6. 例子·过程返回结果
22.8. 函数
22.8.1. TIMESTAMP TO ISO8601
22.9. 触发器(Trigger)
22.9.1. create trigger
22.9.1.1. Update 更新出发
22.9.1.2. Delete 删除出发
22.9.1.3. Insert 插入出发
22.9.2. drop trigger
22.9.3. show triggers
22.9.3.1. SHOW CREATE TRIGGER
22.9.4. EXAMPLE
22.9.4.1. BEFORE/AFTER
22.9.4.2. UUID
22.9.4.3. CALL PROCEDURE
22.10. 事件调度器(EVENT)
22.10.1. 启用 EVENT
22.10.2. 创建 EVENT
22.10.3. 禁用/启用
22.10.4. show events
22.10.5. 实例·每月创建一个表
23. DML (Data Manipulation Language)
23.1. INSERT
23.1.1. INSERT INTO ... SELECT
23.1.2. INSERT IGNORE
23.1.3. INSERT...ON DUPLICATE KEY UPDATE
23.2. REPLACE
23.3. DELETE
23.3.1. 删除重复数据
24. SQL Statement Syntax
24.1. DISTINCT
24.2. group by
24.3. HAVING
24.4. REGEXP
24.5. IN / NOT IN
24.6. ALL / Any
24.7. exists, not exists
24.8. UNION
24.8.1. UNION ALL
24.9. OUTFILE/LOAD DATA INFILE
24.9.1. Export data to CSV from MySQL
24.9.2. Import data from CSV file.
24.10. CASE Syntax
24.11. MySQL 专有命令
24.11.1. SQL_NO_CACHE
24.11.2. SIGNAL Syntax
24.12. SQL 92
25. Functions and Operators
25.1. COUNT
25.2. group_concat() 列传行
25.3. UUID()
25.4. String
25.4.1. LEFT/RIGHT
25.4.2. RPAD/LPAD
25.4.3. CONCAT
25.4.4. CONCAT_WS
25.4.5. 链接所有字段
25.4.6. GROUP_CONCAT
25.4.7. replace
25.4.8. SUBSTRING
25.4.9. SUBSTRING_INDEX
25.4.10. AES_ENCRYPT / AES_DECRYPT
25.5. Date and Time
25.5.1. year/month/day hour:minite:second
25.5.2. Unix time
25.5.3. DATE_FORMAT
25.5.4. DATE_SUB/DATE_ADD
25.5.4.1. DATE_ADD
25.5.5. datediff / timediff
25.6. 数值函数
25.6.1. cast 类型转换
25.6.2. truncate 保留小数位数
25.6.3. MOD 求余
25.7. Control Flow Functions
26. DCL (Data Control Language)
26.1. 锁
26.1.1. 共享锁
26.1.2. 排他锁
26.1.3. 锁
26.1.3.1. 表的加锁与解锁
26.1.3.2. 禁止查询
26.1.4. 锁等待与超时
26.1.4.1. 超时设置
26.1.4.2. select for update nowait
26.2. 事务处理和锁定语句
26.2.1. 事务隔离级别
26.2.2. 事务所用到的表
26.2.3. 解决更新冲突
26.2.4. SAVEPOINT
27. Optimization
27.1. 打开表的数量
27.2. Buffering and Caching
27.2.1. Query Cache SELECT Options
27.3. where 优化
27.4. SHOW PROFILE Syntax SQL性能分析器
27.5. PROCEDURE ANALYSE()
28. MySQL Connectors
28.1. JDBC
28.2. ODBC
28.3. MySQL native driver for PHP - mysqlnd
28.4. python-mysqldb
29. MySQL GUI/Web Manager
29.1. HeidiSQL
29.2. Toad for MySQL Freeware
29.3. phpMyAdmin - MySQL web administration tool
29.4. Maatkit Essential command-line utilities for MySQL
30. Miscellaneous
30.1. Multi-Master Replication Manager for MySQL
30.2. MHA
30.3. HandlerSocket
30.4. Maatkit
30.5. Mroonga
30.6. Amoeba
31. FAQ
31.1. Reset root password 重置MySQL root密码
31.2. 数据库内容替换
31.3. 查看错误代码
31.3.1. ERROR 1153 (08S01) at line 3168: Got a packet bigger than 'max_allowed_packet' bytes
31.3.2. ERROR 1129 (00000): Host 'XXXXXX' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
31.4. 临时表是否需要建索引
31.5. Kill 脚本
31.6. ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
31.7. ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
31.8. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
31.9. 重新整理AUTO_INCREMENT字段
31.10. 转换 latin1 到 UTF-8
31.11. this is incompatible with sql_mode=only_full_group_by
31.12. [Warning] Changed limits: max_open_files: 5000 (requested 20480)
31.13. ERROR 1364: 1364: Field 'id' doesn't have a default value
31.14. ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
31.15. ERROR 1086 (HY000): File '/var/lib/mysql-files/order.txt' already exists
31.16. ERROR 1415: Not allowed to return a result set from a trigger
31.17. Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such fileor directory
IV. NoSQL
32. Redis
32.1. Redis 安装
32.1.1. CentOS 7
32.1.2. CentOS 6
32.1.2.1. 主从同步
32.1.2.2. Sentinel
32.1.3. Ubuntu
32.1.4. 源码编译安装
32.1.5. Test Redis
32.2. /etc/redis.conf
32.2.1. 密码认证
32.2.2. maxmemory-policy TTL 过期策略配置
32.3. redis-cli - Command-line client to redis-server
32.3.1. 命令参数
32.3.1.1. password
32.3.1.2. raw
32.3.2. --latency Enter a special mode continuously sampling latency.
32.3.3. auth
32.3.4. MONITOR
32.3.5. info
32.3.6. save/bgsave/lastsave
32.3.7. config
32.3.8. keys
32.3.9. 字符串操作
32.3.9.1. set/get/del
32.3.9.2. setnx
32.3.10. expire/ttl
32.3.11. 获取 key 类型
32.3.12. LIST 数据类型
32.3.13. set 无序字符集合
32.3.14. zset (有序集合)
32.3.15. Pub/Sub 订阅与发布
32.3.16. flushdb 清空 Redis 数据
32.4. redis-benchmark 测试工具
32.5. Redis Cluster
32.6. Redis 通信协议
32.6.1. 切换DB
32.6.2. 监控
32.7. phpRedisAdmin
32.8. Redis 开发
32.8.1. 消息订阅与发布
32.9. A fast, light-weight proxy for memcached and redis
32.10. FAQ
32.10.1. 清空数据库
32.10.2. (error) MISCONF Redis is configured to save RDB snapshots
33. MongoDB
33.1. Install 安装MongoDB
33.1.1. Quickstart
33.1.1.1. 二进制tar包安装
33.1.1.2. Ubuntu MongoDB
33.1.1.3. CentOS MongoDB
33.1.1.4. 从官网安装最新版本的 MongoDB 3.4
33.1.1.4.1. Server
33.1.1.4.2. Client
33.1.1.4.3. 工具
33.1.1.5. MongoDB + Hadoop
33.1.2. Replication
33.1.2.1. Master
33.1.2.2. Slave
33.1.2.3. 测试
33.1.3. Drivers
33.1.3.1. Using MongoDB in PHP
33.2. 命令工具
33.2.1. mongo - MongoDB Shell
33.2.1.1. eval
33.2.1.2. help
33.2.1.3. 登陆认证
33.2.2. mongodump - Backup
33.2.2.1. 远程备份
33.2.2.2. 本地备份
33.2.3. mongorestore
33.2.3.1. 远程回复
33.2.3.2. 本地恢复
33.2.3.3. filter
33.2.4. mongostat
33.2.5. mongotop
33.2.6. mongofiles - Browse and modify a GridFS filesystem.
33.2.6.1. list 浏览文件
33.2.6.2. put 上传文件
33.2.6.3. get 下载
33.2.6.4. delete 删除
33.3. MongoDB Shell
33.3.1. show 查看命令
33.3.1.1. show dbs
33.3.1.2. show collections
33.3.1.3. show users
33.3.1.4. show profile
33.3.2. 切换数据库
33.3.3. save
33.3.4. insert
33.3.5. update
33.3.6. remove
33.3.6.1. 删除条件使用 _id
33.3.7. 删除 collection
33.3.8. count()
33.3.9. group()
33.3.10. find() MongoDB 2.x
33.3.11. find() MongoDB 3.x
33.3.11.1. Query
33.3.11.2. 包含字段
33.3.11.3. 排除字段
33.3.11.4. sort()
33.3.12. 管道操作
33.3.13. shutdownServer
33.3.14. aggregate
33.3.14.1. project
33.3.14.1.1. $split
33.3.14.1.2. substr
33.3.14.2. groupby + sum
33.4. Mongo Admin UI
33.4.1. RockMongo
33.4.2. MongoVUE
33.5. Map-Reduce
33.5.1. 使用 Map-Reduce 统计Web 服务器 access.log 日志文件
36. Cassandra
36.1. Getting Started
36.1.1. Downloading and Installation
36.1.2. Running Cassandra
36.1.3. cli tool
36.1.4. Testing Cassandra
36.2. Configure Cassandra
36.2.1. Envionment variables
36.2.2. log4j.properties
36.2.3. storage-conf.xml
36.3. Keyspace
36.3.1. Schema
36.3.1.1. Keyspace
36.3.1.2. Column family
36.3.1.2.1. Name
36.3.1.2.2. Column
36.3.1.2.3. Super column
36.3.1.2.4. Sorting
36.3.2. Keyspace example
36.4. Cluster
36.4.1. Running a cluster
36.4.2. Running a single node
36.4.3. nodetool
37. Hypertable
37.1. Hypertable 安装
37.1.1. Hypertable standalone 单机安装
37.1.2. Hypertable on HDFS(hadoop) 安装
37.1.3. MapR
37.1.4. Ceph
37.1.5. 检验安装
37.2. Code examples
37.2.1. PHP
37.3. HQL
37.3.1. namespace 命名空间管理
37.3.2. Table 表
37.4. FAQ
37.4.1. 切换 DFS Broker
38. CouchBase
38.1. 安装 CouchBase
38.1.1. Getting Started with Couchbase on PHP
38.2. couchbase 命令
38.2.1. couchbase-cli
39. Memcached
39.1. 安装 Memcached
39.1.1. CentOS 下编译
39.1.2. Ubuntu 下编译安装
39.1.3. debian/ubuntu
39.1.4. yum install
39.2. Memcached 代理
39.2.1. moxi
39.2.2. memagent
40. RethinkDB
41. TokyoCabinet/Tyrant
42. Flare
43. Voldemort
44. LevelDB
45. HyperDex
46. LeoFS
V. PostgreSQL
47. PostgreSQL 安装
47.1. Ubuntu 12.04.1 LTS 安装
47.2. YUM 默认源安装
47.3. PostgreSQL 官方 YUM 源安装
47.3.1. 9.2
47.3.2. 9.3
47.3.3. 9.4
47.3.4. 9.6
47.3.5. PostgreSQL 10
47.4. PostgreSQL 配置
47.4.1. postgresql.conf
47.4.2. pg_hba.conf
47.5. 创建dba用户
48. PostgreSQL 命令
48.1. psql - PostgreSQL interactive terminal
49. Database Administration
49.1. User Account Management
49.1.1. Create User
49.1.2. Drop User
49.1.3. Rename User
49.1.4. SET PASSWORD
49.2. Access Privilege System
49.2.1. SHOW GRANTS
49.2.2. show privileges
49.2.3. Grant privileges
49.2.4. Revoke privileges
49.2.5. Show Privileges
49.2.6. MAX_QUERIES_PER_HOUR/MAX_UPDATES_PER_HOUR
49.2.7. Table Privileges
49.2.8. Column Privileges
49.3. Maintenance 数据库维护
49.3.1. CHECK 检查表
49.3.2. ANALYZE 分析表
49.3.3. CHECKSUM
49.3.4. OPTIMIZE 优化表
49.3.5. REPAIR 修复
49.4. INFORMATION_SCHEMA
49.4.1. 查询表字段
49.4.2. 列出所有触发器
49.5. Backup and Recovery
49.5.1. Import / Export
49.5.1.1. Export(Backup)
49.5.1.2. Import(Recovery)
49.5.1.3. xml
49.5.1.4. 备份表数据
49.5.1.5. source
49.5.1.6. 使用 mysqlhotcopy 备份 MyISAM 引擎的数据库
49.5.1.7. AutoMySQLBackup
49.5.1.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.
49.5.1.8.1. Percona yum Repository
49.5.1.8.2. Creating an Incremental Backup
49.5.2. Snapshot Backup
49.5.2.1. LVM Snapshot
49.5.2.2. Btrfs Snapshot
50. DDL - Data Definition Language
50.1. 数据库管理(Database)
50.1.1. create
50.1.2. drop
50.1.3. Alter
50.1.4. Rename
50.1.5. CHARACTER
50.1.6. show create database
50.2. 表管理(Table)
50.2.1. 数据类型
50.2.1.1. SET 集合类型
50.2.2. create table ... select
50.2.3. modifiy table
50.2.4. TEMPORARY Table
50.2.5. Collate
50.2.6. CHARACTER
50.2.7. DEFAULT
50.2.7.1. AUTO_INCREMENT
50.2.7.2. TIMESTAMP NULL DEFAULT NULL ON UPDATE
50.2.7.3. 表存储位置(DATA DIRECTORY)
50.2.8. KEY
50.2.8.1. PRIMARY KEY
50.2.9. COMMENT
50.2.10. Engine 存储引擎
50.2.10.1. 显示当前数据库支持引擎
50.2.10.2. 切换引擎
50.2.10.3. FEDERATED
50.2.10.4. BLACKHOLE
50.2.10.5. ARCHIVE
50.2.10.6. CSV
50.3. Partitioning
50.3.1. RANGE
50.3.2. LIST
50.3.3. HASH
50.3.3.1. LINEAR HASH
50.3.4. KEY分区
50.3.5. Subpartitioning
50.3.6. 分区管理
50.3.6.1. 新增分区
50.3.6.2. 删除分区
50.3.6.3. 重建分区
50.3.6.4. 分区维护
50.3.7. EXPLAIN PARTITIONS
50.3.8. SHOW CREATE TABLE
50.3.9. INFORMATION_SCHEMA.partitions 表
50.3.10. 分区数据操作
50.4. Index
50.4.1. SHOW INDEX
50.4.2. CREATE INDEX
50.4.3. DROP INDEX
50.4.4. rebuild
50.5. 外键(Foreign Key)
50.5.1. FOREIGN KEY (RESTRICT)
50.6. 视图(View)
50.7. 存储过程(PROCEDURE)
50.7.1. 存储程序
50.7.2. EXECUTE 执行 SQL
50.7.3. PREPARE 传递参数
50.7.4. 存储过程返回数据
50.7.5. 结果集转JSON
50.7.6. 例子·过程返回结果
50.8. 函数
50.8.1. TIMESTAMP TO ISO8601
50.9. 触发器(Trigger)
50.9.1. create trigger
50.9.1.1. Update 更新出发
50.9.1.2. Delete 删除出发
50.9.1.3. Insert 插入出发
50.9.2. drop trigger
50.9.3. show triggers
50.9.3.1. SHOW CREATE TRIGGER
50.9.4. EXAMPLE
50.9.4.1. BEFORE/AFTER
50.9.4.2. UUID
50.9.4.3. CALL PROCEDURE
50.10. 事件调度器(EVENT)
50.10.1. 启用 EVENT
50.10.2. 创建 EVENT
50.10.3. 禁用/启用
50.10.4. show events
50.10.5. 实例·每月创建一个表
51. DML (Data Manipulation Language)
51.1. INSERT
51.1.1. INSERT INTO ... SELECT
51.1.2. INSERT IGNORE
51.1.3. INSERT...ON DUPLICATE KEY UPDATE
51.2. REPLACE
51.3. DELETE
51.3.1. 删除重复数据
52. SQL Statement Syntax
52.1. DISTINCT
52.2. group by
52.3. HAVING
52.4. REGEXP
52.5. IN / NOT IN
52.6. ALL / Any
52.7. exists, not exists
52.8. UNION
52.8.1. UNION ALL
52.9. OUTFILE/LOAD DATA INFILE
52.9.1. Export data to CSV from MySQL
52.9.2. Import data from CSV file.
52.10. CASE Syntax
52.11. MySQL 专有命令
52.11.1. SQL_NO_CACHE
52.11.2. SIGNAL Syntax
52.12. SQL 92
53. Functions and Operators
53.1. COUNT
53.2. group_concat() 列传行
53.3. UUID()
53.4. String
53.4.1. LEFT/RIGHT
53.4.2. RPAD/LPAD
53.4.3. CONCAT
53.4.4. CONCAT_WS
53.4.5. 链接所有字段
53.4.6. GROUP_CONCAT
53.4.7. replace
53.4.8. SUBSTRING
53.4.9. SUBSTRING_INDEX
53.4.10. AES_ENCRYPT / AES_DECRYPT
53.5. Date and Time
53.5.1. year/month/day hour:minite:second
53.5.2. Unix time
53.5.3. DATE_FORMAT
53.5.4. DATE_SUB/DATE_ADD
53.5.4.1. DATE_ADD
53.5.5. datediff / timediff
53.6. 数值函数
53.6.1. cast 类型转换
53.6.2. truncate 保留小数位数
53.6.3. MOD 求余
53.7. Control Flow Functions
54. PostgreSQL GUI
54.1. pgAdmin III
54.2. phpPgAdmin
54.3. Monitoring
54.3.1. PgBadger
54.4. pgModeler - PostgreSQL Database Modeler
55. Barman Backup & recovery for PostgreSQL
56. pgbouncer - lightweight connection pooler for PostgreSQL
56.1. 安装 pgbouncer
56.1.1. Ubuntu
56.1.2. CentOS
56.2. 配置 pgbouncer
57. Foreign data wrappers
58. Barman for PostgreSQL
59. Connector
59.1. Pomm
60. Replication
60.1. Bucardo
61. FAQ
61.1. Reset root password 重置MySQL root密码
61.2. 数据库内容替换
61.3. 查看错误代码
61.3.1. ERROR 1153 (08S01) at line 3168: Got a packet bigger than 'max_allowed_packet' bytes
61.3.2. ERROR 1129 (00000): Host 'XXXXXX' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
61.4. 临时表是否需要建索引
61.5. Kill 脚本
61.6. ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
61.7. ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
61.8. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
61.9. 重新整理AUTO_INCREMENT字段
61.10. 转换 latin1 到 UTF-8
61.11. this is incompatible with sql_mode=only_full_group_by
61.12. [Warning] Changed limits: max_open_files: 5000 (requested 20480)
61.13. ERROR 1364: 1364: Field 'id' doesn't have a default value
61.14. ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
61.15. ERROR 1086 (HY000): File '/var/lib/mysql-files/order.txt' already exists
61.16. ERROR 1415: Not allowed to return a result set from a trigger
61.17. Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such fileor directory
VI. Oracle
62. Oracle 12c 安装可
62.1. Server 安装
62.2. Desktop 安装
62.3. Oracle Net Services Configuration
62.3.1. Listener configuration
62.3.2. Local Net Service Name configuration
62.4. Oracle 12c Client for Windows 10
62.5. 卸载 Oracle
62.6. Linux 安装 sqlplus
63. Privilege
63.1. Account
63.1.1. show user
63.1.2. DEFAULT_TABLESPACE
63.1.3. unlock/lock
63.2. User
63.2.1. Create user
63.2.2. Drop user
63.2.3. Alter user
63.2.4. 修改密码
63.2.5. Reset Password 忘记密码重置密码
63.3. grant
63.3.1. 列出权限
63.3.2. 查看用户权限
63.3.3. 吊销权限
63.3.4. System Privileges 系统权限
63.3.4.1. EM 权限
63.3.4.2. Granting and Revoking SYSDBA and SYSOPER Privileges
63.3.5. Object Privileges 对象权限
63.4. Opening the Encrypted Wallet
64. 数据库管理
64.1. lsnrctl
64.2. listener.ora
64.3. TNS 配置
64.3.1. HOST
64.3.2. SID
64.4. parameter
64.4.1. db
64.4.2. instance_name
64.4.3. service_name
64.4.4. global_name
64.4.5. db_name
64.4.6. db_domain
64.4.7. sga
64.4.8. size
64.4.9. spfile
64.4.10. cache
64.4.11. Character Set
64.5. 进程
64.6. 查看用户
64.7. 显示表
64.8. 显示试图
64.9. PROCEDURE
64.10. FUNCTION
64.11. 视图、存储过程、函数
64.12. 查看存储过程源代码
64.13. 日期时间格式
64.13.1. Date
64.13.2. 修改系统日期格式
64.13.3. TIMESTAMP
64.13.4.
64.14. 切换字符集
64.14.1. 切换到 GBK
64.14.2. 切到 UTF-8
64.14.3. 切到 AL32UTF8
64.14.4. 切换过程实例
64.15. Oracle 表空间
64.15.1. 查询空闲表空间
64.15.2. 创建表空间
64.15.2.1. 临时表空间
64.15.3. 更改表空间属性
64.15.3.1. 修改表空间大小
64.15.4. 删除表空间
65. Oracle Gui
65.1. Create instance
65.2. Oracle Net Configuration Assistant
65.3. Oracle Enterprise Manager
65.3.1. EM Configuration issue. /opt/oracle/product/11.2.0/dbhome_1/orcl.example.com_orcl not found.
65.4. Other GUI - phpOraAdmin
66. CLI
66.1. SQL*Plus
66.1.1. conn
66.1.2. startup/shutdown
66.1.2.1. startup
66.1.2.2. shutdown
66.1.3. $ORACLE_HOME/sqlplus/admin/glogin.sql
66.1.4. @运行SQL
66.1.5. 链接数据库
66.2. exp/imp
66.2.1. exp
66.2.2. imp
66.2.2.1. A用户导出B用户导入
66.3. expdp/impdp
66.3.1. expdp
66.3.2. impdp
66.3.3. 数据泵演示
66.3.4. 查看dmp文件的表空间
66.4. RMAN
66.4.1. 数据库模式
66.4.2. 完全备份
66.4.3. 增量备份
66.4.4. 恢复数据库
66.4.5. 是用tar打包rman文件
66.4.6. 打包 rman 备份文件
67. DDL
67.1. 字段类型
67.1.1. Date
67.1.2. TIMESTAMP
67.2. 索引
67.3. 表
67.3.1.
67.3.2. 约束检查
67.4. SEQUENCE 序列
67.4.1.
68. 程序包
68.1. 执行包中的过程
69. 数据库链接
70. Oracle 监控
70.1. Oracle日志的路径
70.2. SQL 日志监控
70.2.1. 查询性能分析
70.2.2. IO性能分析
70.3. session
70.4. SQL Monitoring Report
71. Installing Oracle Database
71.1. Installing Oracle Database 10g Release 2 on Linux x86
71.2. 11gR2
71.3. Installing Oracle Client 11g
71.4. Silence Install - Database
71.4.1. Response File
71.4.2. OS 配置脚本
71.4.3. 运行 runInstaller
71.5. Silence Install - Client
71.6. oracle 817 script
71.7. Script for automatic startup on boot
71.8. Run level shell script to start Oracle 10g services on RedHat Enterprise Linux (RHAS 4)
72. FAQ
72.1. Reset root password 重置MySQL root密码
72.2. 数据库内容替换
72.3. 查看错误代码
72.3.1. ERROR 1153 (08S01) at line 3168: Got a packet bigger than 'max_allowed_packet' bytes
72.3.2. ERROR 1129 (00000): Host 'XXXXXX' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
72.4. 临时表是否需要建索引
72.5. Kill 脚本
72.6. ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
72.7. ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
72.8. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
72.9. 重新整理AUTO_INCREMENT字段
72.10. 转换 latin1 到 UTF-8
72.11. this is incompatible with sql_mode=only_full_group_by
72.12. [Warning] Changed limits: max_open_files: 5000 (requested 20480)
72.13. ERROR 1364: 1364: Field 'id' doesn't have a default value
72.14. ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
72.15. ERROR 1086 (HY000): File '/var/lib/mysql-files/order.txt' already exists
72.16. ERROR 1415: Not allowed to return a result set from a trigger
72.17. Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such fileor directory

表格清单

26.1. 更新丢失演示
26.2. 防止更新丢失加锁演示

范例清单

8.1. Elasticsearch master / slave
9.1. Solr - schema.xml
9.2. /etc/profile.d/java.sh
19.1. my.cnf
19.2. my.cnf
19.3. my.cnf
22.1. BEFORE/AFTER
22.2. uuid()
24.1. SQL ANY example
27.1. SQL_CACHE 测试
27.2. SHOW PROFILE Syntax
33.1. MongoDB Test
33.2. Using MongoDB in PHP
34.1. Oracle 字符集切换实例
34.2. 创建表空间实例
36.1. Twitter
36.2. Twissandra
39.1. /etc/init.d/memcached
47.1. Example for /var/lib/pgsql/10/data/pg_hba.conf
50.1. BEFORE/AFTER
50.2. uuid()
52.1. SQL ANY example
56.1. /etc/pgbouncer/pgbouncer.ini
64.1. Oracle 字符集切换实例
64.2. 创建表空间实例
66.1. expdp
66.2. impdp
66.3. full backup
67.1.
69.1. DB LINK