Things and Stuff Wiki - An organically evolving personal wiki knowledge base with an on-the-fly taxonomy containing a patchwork of topic outlines, descriptions, notes and breadcrumbs, with links to sites, systems, software, manuals, organisations, people, articles, guides, slides, papers, books, comments, videos, screencasts, webcasts, scratchpads and more. Quality varies drastically. Use the Table of Contents menu to navigate long pages, use the header Small-ToC and Tiny-ToC links to reduce the menu size. Possibly not that mobile friendly atm. Feedback, general contacting me, and IRC idling in #tnswiki on Freenode (web chat). See About for login and other info. / et / em
- 1 General
- 2 SQL
- 2.1 MySQL
- 2.1.1 Connecting
- 2.1.2 Admin
- 2.1.3 Database management
- 2.1.4 User management
- 2.1.5 Table management
- 2.1.6 Troubleshooting
- 2.1.7 Backup and restore
- 2.1.8 Replication
- 2.1.9 Tools
- 2.1.10 Scripts
- 2.1.11 Performance
- 2.1.12 Native clients
- 2.1.13 Web clients
- 2.2 MariaDB
- 2.3 Percona Server
- 2.4 SQLite
- 2.5 PostgreSQL
- 2.6 Ingres
- 2.7 Other
- 2.8 IBM DB2
- 2.9 Oracle Database
- 2.10 IBM System R
- 2.1 MySQL
- 3 NoSQL
- 4 to sort
- 5 Time series
- 6 Graph
- 7 Replication
- 8 Distributed
- 9 Social
- 10 Other
- https://en.wikipedia.org/wiki/Database - an organized collection of data. The data are typically organized to model relevant aspects of reality in a way that supports processes requiring this information. For example, modeling the availability of rooms in hotels in a way that supports finding a hotel with vacancies.
Database management systems (DBMSs) are specially designed applications that interact with the user, other applications, and the database itself to capture and analyze data. A general-purpose database management system (DBMS) is a software system designed to allow the definition, creation, querying, update, and administration of databases. Well-known DBMSs include MySQL, PostgreSQL, SQLite, Microsoft SQL Server, Microsoft Access, Oracle, SAP, dBASE, FoxPro, IBM DB2, LibreOffice Base and FileMaker Pro. A database is not generally portable across different DBMS, but different DBMSs can inter-operate by using standards such as SQL and ODBC or JDBC to allow a single application to work with more than one database.
- https://en.wikipedia.org/wiki/Query_language - an be classified according to whether they are database query languages or information retrieval query languages. The difference is that a database query language attempts to give factual answers to factual questions, while an information retrieval query language attempts to find documents containing information that is relevant to an area of inquiry.
- https://en.wikipedia.org/wiki/Database_schema - computer languages used to make queries in databases and information systems
- https://en.wikipedia.org/wiki/Column_(database) - a set of data values of a particular simple type, one for each row of the table. The columns provide the structure according to which the rows are composed. When a column allows data values of a single type, it does not essentially mean it only has simple text values. In relational database terminology, column's equivalent is called attribute.
- https://en.wikipedia.org/wiki/Row_(database) - also called a record or tuple — represents a single, implicitly structured data item in a table. In simple terms, a database table can be thought of as consisting of rows and columns or fields. Each row in a table represents a set of related data, and every row in the table has the same structure.
- https://en.wikipedia.org/wiki/Field_(computer_science) - a record divided into fields. Relational databases arrange data as sets of database records, also called rows. Each record consists of several fields; the fields of all records form the columns. Examples of fields: name, gender, hair colour.
- https://en.wikipedia.org/wiki/View_(SQL) - the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object. This pre-established query command is kept in the database dictionary. Unlike ordinary base tables in a relational database, a view does not form part of the physical schema: as a result set, it is a virtual table computed or collated dynamically from data in the database when access to that view is requested. Changes applied to the data in a relevant underlying table are reflected in the data shown in subsequent invocations of the view. In some NoSQL databases, views are the only way to query data.
Database practitioners can define views as read-only or updatable. If the database system can determine the reverse mapping from the view schema to the schema of the underlying base tables, then the view is updatable. INSERT, UPDATE, and DELETE operations can be performed on updatable views. Read-only views do not support such operations because the DBMS cannot map the changes to the underlying base tables. A view update is done by key preservation.
- https://en.wikipedia.org/wiki/Materialized_view - a database object that contains the results of a query. For example, it may be a local copy of data located remotely, or may be a subset of the rows and/or columns of a table or join result, or may be a summary using an aggregate function.
- https://en.wikipedia.org/wiki/Database_index - a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.
- https://en.wikipedia.org/wiki/CAP_theorem - also named Brewer's theorem after computer scientist Eric Brewer, states that it is impossible for a distributed data store to simultaneously provide more than two out of the following three guarantees:
- Consistency: Every read receives the most recent write or an error
- Availability: Every request receives a (non-error) response – without guarantee that it contains the most recent write
- Partition tolerance: The system continues to operate despite an arbitrary number of messages being dropped (or delayed) by the network between nodes
In particular, the CAP theorem implies that in the presence of a network partition, one has to choose between consistency and availability. Note that consistency as defined in the CAP theorem is quite different from the consistency guaranteed in ACID database transactions.
- https://en.wikipedia.org/wiki/PACELC_theorem - an extension to the CAP theorem. It states that in case of network partitioning (P) in a distributed computer system, one has to choose between availability (A) and consistency (C) (as per the CAP theorem), but else (E), even when the system is running normally in the absence of partitions, one has to choose between latency (L) and consistency (C).
- https://en.wikipedia.org/wiki/ACID_(computer_science) - Atomicity, Consistency, Isolation, Durability, is a set of properties of database transactions intended to guarantee validity even in the event of errors, power failures, etc. In the context of databases, a sequence of database operations that satisfies the ACID properties (and these can be perceived as a single logical operation on the data) is called a transaction. For example, a transfer of funds from one bank account to another, even involving multiple changes such as debiting one account and crediting another, is a single transaction.
- https://en.wikipedia.org/wiki/Correlation_database - a database management system (DBMS) that is data-model-independent and designed to efficiently handle unplanned, ad hoc queries in an analytical system environment. Because a correlation DBMS stores each unique data value only once, the physical database size is significantly smaller than relational or column-oriented databases, without the use of data compression techniques. Above approximately 30GB, a correlation DBMS may become smaller than the raw data set.
- YouTube: Database as Filesystem
- https://en.wikipedia.org/wiki/Relational_database_management_system - a database management system (DBMS) that is based on the relational model as invented by E. F. Codd, of IBM's San Jose Research Laboratory. In 2017, many of the databases in widespread use are based on the relational database model.
- https://en.wikipedia.org/wiki/Relational_calculus - consists of two calculi, the tuple relational calculus and the domain relational calculus, that are part of the relational model for databases and provide a declarative way to specify database queries. This in contrast to the relational algebra which is also part of the relational model but provides a more procedural way for specifying queries.
- https://en.wikipedia.org/wiki/Relational_algebra - first created by E.F. Codd while at IBM, is a family of algebras with a well-founded semantics used for modelling the data stored in relational databases, and defining queries on it. The main application of relational algebra is providing a theoretical foundation for relational databases, particularly query languages for such databases, chief among which is SQL.
- Jailer - a tool for database subsetting, schema and data browsing. It exports consistent, referentially intact row-sets from relational databases. It removes obsolete data without violating integrity. It is DBMS agnostic (by using JDBC), platform independent, and generates DbUnit datasets, hierarchically structured XML, and topologically sorted SQL-DML.
- YouTube: SQL (Video versions) - Khan Academy
SELECT * from table;
- https://vimeo.com/56639635 - Michael 'Monty' Widenius - Author of the MySQL Server and MariaDB fork
- SQL-DK – an batch/terminal client for relational databases
- https://en.wikipedia.org/wiki/Information_schema - (information_schema) is an ANSI-standard set of read-only views which provide information about all of the tables, views, columns, and procedures in a database. It can be used as a source of the information which some databases make available through non-standard commands
MariaDB is compatible with MySQL, you probably want to use that, if not Postgres
- YouTube: MySQL - A series covering working with MySQL including managing databases, tables and data.
- YouTube: Mysql Database - playlist
mysql -p # connect with anonymous user, prompt for password
mysql -u username -ppassword -h nonlocalhost dbname # specify user, password and nonlocalhost address, USE dbname
mysqladmin - a client for performing administrative operations. You can use it to check the server's configuration and current status, to create and drop databases, and more.
- YouTube: 15 Mysql Database MySQL Admin
SHOW engines; SHOW processlist; Show variables;
USE [db name];
CREATE DATABASE [dbname];
CREATE DATABASE IF NOT EXISTS [dbname]
mysql -u username -p -e "CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci"; # drupal 7
select database(); # show which database is in use
DROP database dbname; # remove dbname from db;
- 6.2. The MySQL Access Privilege System
- Account Management Statements
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'newpassword';
SELECT User FROM mysql.user; # show all database users
USE mysql; SET PASSWORD FOR 'user-name-here'@'hostname-name-here' = PASSWORD('new-password-here');
UPDATE mysql.user SET Password=PASSWORD('new-password-here') WHERE User='user-name-here' AND Host='host-name-here';
SET PASSWORD FOR 'user'@'localhost' = PASSWORD('mypass');
mysqladmin -u user_name -h host_name password "newpwd"
- Resetting a forgotten MySQL root password, starting with --skip-grant-tables
Password=PASSWORD('NewPassword') WHERE User='root'; FLUSH PRIVILEGES;
SHOW GRANTS; # show permissions for current db user
SHOW GRANTS FOR user@localhost; # show permissions for a user
SELECT CONCAT("SHOW GRANTS FOR '",user,"'@'",host,"';") FROM mysql.user WHERE host!='localhost'; # Create command list for showing user grants 
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES ON databasename.* TO 'username'@'localhost' IDENTIFIED BY 'password'; # drupal 7
create database wikidb; grant index, create, select, insert, update, delete, alter, lock tables on wikidb.* to 'wikiuser'@'localhost' identified by 'password'; #mediawiki??
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
FLUSH privileges; # Reloads the privileges from the grant tables in the mysql database.
The server caches information in memory as a result of GRANT, CREATE USER, CREATE SERVER, and INSTALL PLUGIN statements. This memory is not released by the corresponding REVOKE, DROP USER, DROP SERVER, and UNINSTALL PLUGIN statements, so for a server that executes many instances of the statements that cause caching, there will be an increase in memory use. This cached memory can be freed with FLUSH PRIVILEGES.
SHOW tables; SELECT * from mysql.user; # return ascii table with rows from user table from mysql database SELECT * from mysql.user\G; # return vertical row information from user table from mysql database
pager less -SFX # info will now be returned via less, use arrow keys to navigate large tables, q to quit this mode
nopager # reset output from pager to stdout
SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;
13.8.1. DESCRIBE provides information about the columns in a table.
- 4.6.7. mysqlbinlog — Utility for Processing Binary Log Files
Backup and restore
mysql < database.sql # if database file was saved with CREATE DATABASE
mysql databasename < database.sql # specify database name to import database file
mysqldump [options] db_name [tbl_name ...]
mysqldump -u [username] -p -A -R -E --triggers --single-transaction > full_backup.sql # full database backup  # -A For all databases (you can also use --all-databases) # -R For all routines (stored procedures & triggers) # -E For all events # --single-transaction Without locking the tables i.e., without interrupting any connection (R/W).
mysqldump -u root -ppassword dbname | mysql -u root -ppassword --host=remote-server -C dbname # copy direct to new database instance, direct db connection # REMEMBER - zsh, space before the command to hide from history
mysqldump -u username -ppassword dbname | ssh email@example.com mysql -u username -ppassword dbname # copy direct to new database instance, via ssh connection # REMEMBER - zsh, space before the command to hide from history # fails for large DBs
mysqldump -u username -ppassword dbname | gzip -c | ssh USERNAME@YOUR_TO_HOST 'cat > ~/dump.sql.gz' # gzip across the wire to a compressed file on the other end # REMEMBER - zsh, space before the command to hide from history
- https://github.com/sadreck/mysqldbsplit - This script breaks down a mysqldump file into one-file-per-table. 
Install the mysql-client package to access.
For a non-busy server;
mysqlbackup --port=3306 --protocol=tcp --user=root --password --backup-dir=/home/user/backupdir backup-and-apply-log
- http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html - currently deprecated.
- Percona XtraBackup - Documentation - an open-source hot backup utility for MySQL - based servers that doesn’t lock your database during the backup. It can back up data from InnoDB, XtraDB, and MyISAM tables on MySQL 5.1, 5.5, 5.6 and 5.7 servers, as well as Percona Server with XtraDB.
- https://github.com/dbcli/mycli - A command line client for MySQL that can do auto-completion and syntax highlighting.
- dbdeploy is a Database Change Management tool.
- dBug - "PHP version of ColdFusion’s cfdump. Outputs colored and structured tabular variable information. Variable types supported are: Arrays, Classes/Objects, Database and XML Resources."
- anywhereindb - Sometime we need to find out a small piece of string in big Database. Like where is the configuration is saved, or where is Jon's Date of birth is saved. This code is search all the tables and all the rows and columns in a MYSQL Database. The code is written in PHP. For faster result, we are only searching in the varchar field.
- http://sourceforge.net/projects/ajaxmytop/ - monitoring
- Search Replace DB - This script was made to aid the process of migrating PHP and MySQL based websites. It has additional features for WordPress but works for most other similar CMSes.
./searchreplacedb2cli.php --host localhost --user root --database test --pass "pass" --charset utf\-8 --search "findMe" --replace "replaceMe" --dry-run
- SQL Workbench/J - a free, DBMS-independent, cross-platform SQL query tool. It is written in Java and should run on any operating system that provides a Java Runtime Environment. Its main focus is on running SQL scripts (either interactively or as a batch) and export/import features. Graphical query building or more advanced DBA tasks are not the focus and are not planned
SQuirreL SQL Client
- SQuirreL SQL Client - a graphical SQL client written in Java that will allow you to view the structure of a JDBC compliant database, browse the data in tables, issue SQL commands etc.
- HeidiSQL - a useful and reliable tool designed for web developers using the popular MySQL server, Microsoft SQL databases and PostgreSQL. It enables you to browse and edit data, create and edit tables, views, procedures, triggers and scheduled events. Also, you can export structure and data either to SQL file, clipboard or to other servers. 
- DBeaver - Free multi-platform database tool for developers, SQL programmers, database administrators and analysts. Supports all popular databases: MySQL, PostgreSQL, MariaDB, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Derby, etc.
- SQL Buddy - Web based MySQL administration
wget http://www.adminer.org/latest-mysql-en.php -O adminer.php wget http://www.adminer.org/latest-en.php -O adminer.php
Fork of MySQL, drop in replacement.
- DB Browser for SQLite - a high quality, visual, open source tool to create, design, and edit database files compatible with SQLite.It is for users and developers wanting to create databases, search, and edit data. It uses a familiar spreadsheet-like interface, and you don't need to learn complicated SQL commands.
- https://github.com/dbcli/pgcli - Postgres CLI with autocompletion and syntax highlighting
- LiteCLI - a user-friendly CommandLine client for SQLite database. It is based on the popular pgcli and mycli projects. LiteCLI is written in python using the wonderful prompt-toolkit library. It is cross-platform compatible and it is tested on Linux, MacOS and Windows. 
IBM System R
See also Server#Redis
Used in the BOA stack for caching.
- PouchDB was written to help web developers build applications that work as well offline as well as they do online, applications save data locally so the user can use all the features of an app even while offline and synchronise the data between clients so they have up to date data wherever they go.
- Kinto - a minimalist JSON storage service with synchronisation and sharing abilities.
- replikativ - an open, scalable and distributive infrastructure for a data-driven community of applications. It can serve as a storage backend for your applications and make your application state always accessible on all your endpoints. For our applications it radically simplifies frontend development by streaming state changes directly into our reactive UI pipelines.
- Fauna - the only Mission-Critical NoSQL Database that guarantees data correctness without operational complexity from the team that scaled Twitter.
- https://github.com/fastio/pedis - NoSQL data store using the SEASTAR framework, compatible with Redis
- ArangoDB - From the ground up, ArangoDB is designed as a native multi-model database, supporting key/value, document and graph models. This means you can model your data and application in a very flexible way. ArangoDB can operate as a highly scalable database cluster for all data models. An ArangoDB cluster can be configured to serve various types of loads and runs on container orchestration systems like Kubernetes & DC/OS.
- http://radar.oreilly.com/2012/02/nosql-non-relational-database.html - http://news.ycombinator.com/item?id=3610844
- Trousseau is a gpg encrypted key-value store designed to be a simple, safe and relient place for your data. It stores data in a single multi-recipients encrypted file and can supports both local and remote storage sources (S3 and ssh so far) import/export.
Virtuoso Universal Server
- http://en.wikipedia.org/wiki/Virtuoso_Universal_Server - a middleware and database engine hybrid that combines the functionality of a traditional Relational database management system (RDBMS), Object-relational database (ORDBMS), virtual database, RDF, XML, free-text, web application server and file server functionality in a single system. Rather than have dedicated servers for each of the aforementioned functionality realms, Virtuoso is a "universal server"; it enables a single multithreaded server process that implements multiple protocols. The free and open source edition of Virtuoso Universal Server is also known as OpenLink Virtuoso. The software has been developed by OpenLink Software with Kingsley Uyi Idehen and Orri Erling as the chief software architects.
- https://en.wikipedia.org/wiki/Time_series_database - a software system that is optimized for handling time series data, arrays of numbers indexed by time (a datetime or a datetime range). In some fields these time series are called profiles, curves, or traces. Ideally, repositories of time series are natively implemented using specialized database algorithms. However, it is possible to store time series as binary large objects (BLOBs) in a relational database or by using a VLDB approach coupled with a pure star schema. Efficiency is often improved if time is treated as a discrete quantity rather than as a continuous mathematical dimension.
- InfluxDB - an open source time series database with no external dependencies. It's useful for recording metrics, events, and performing analytics.
- Apache TinkerPop - a graph computing framework for both graph databases (OLTP) and graph analytic systems (OLAP).
- http://www.symmetricds.org/ - open source software for database and file synchronization with Multi-master replication, filtered synchronization, and transformation capabilities. It is designed to scale for a large number of nodes, work across low-bandwidth connections, and withstand periods of network outage. Data synchronization occurs asynchronously from a scheduled job, with data changes being sent over a push or pull operation.
- H-Store - an experimental main-memory, parallel database management system that is optimized for on-line transaction processing (OLTP) applications. It is a highly distributed, row-store-based relational database that runs on a cluster on shared-nothing, main memory executor nodes. The H-Store project is a collaboration between MIT, Brown University, Carnegie Mellon University, Yale University, and Intel.
- https://github.com/pingcap/tidb - an open source distributed scalable Hybrid Transactional and Analytical Processing (HTAP) database built by PingCAP. Inspired by the design of Google F1 and Google Spanner, TiDB features infinite horizontal scalability, strong consistency, and high availability. The goal of TiDB is to serve as a one-stop solution for both OLTP (Online Transactional Processing) and OLAP (Online Analytical Processing).
- https://github.com/yongman/tidis - a Distributed NoSQL database, providing a redis-protocal api(string,list,hash,set,sorted-set), written in Go. Tidis is like TiDB layer, providing protocol transform, powered by tikv backend distributed storage which use raft for data replication and 2PC for distributed transaction. 
- Scuttlebot - an open source peer-to-peer log store used as a database, identity provider, and messaging system. It features global replication, file-syncronization, and end-to-end encryption.
- https://github.com/amark/gun - a realtime, distributed, offline-first, graph database engine. Doing 20M+ ops/sec in just ~9KB gzipped.