Things and Stuff Wiki - an organically evolving knowledge base wiki containing topic outlines, descriptions and breadcrumbs, with links to sites, systems, software, manuals, organisations, people, articles, guides, slides, papers, books, comments, screencasts, webcasts, scratchpads, and more. table of contents for navigation on long pages. see About for further information. / et / em
- 1 General
- 2 SQL
- 2.1 MySQL
- 2.2 MariaDB
- 2.3 SQLite
- 2.4 PostgreSQL
- 2.5 Ingres
- 2.6 Other
- 2.7 IBM DB2
- 2.8 Oracle Database
- 2.9 IBM System R
- 3 NoSQL
- 4 to sort
- 5 Replication
- 6 Distributed
- 7 Social
- 8 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_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/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.
- https://vimeo.com/56639635 - Michael 'Monty' Widenius - Author of the MySQL Server and MariaDB fork
- SQL-DK – an batch/terminal client for relational databases
mysql -u username -ppassword -h hostname databasename basic connection
- 6.2. The MySQL Access Privilege System
- Account Management Statements
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'; CREATE DATABASE [userdatabase]; GRANT all on [userdatabase].* TO '[username]'; 
SHOW GRANTS SHOW GRANTS FOR 'user'@'localhost';
create database wikidb; grant index, create, select, insert, update, delete, alter, lock tables on wikidb.* to 'wikiuser'@'localhost' identified by 'password';
Create command list for showing user grants 
SELECT CONCAT("SHOW GRANTS FOR '",user,"'@'",host,"';") FROM mysql.user WHERE host!='localhost';
SET PASSWORD FOR 'user'@'localhost' = PASSWORD('mypass');
mysqladmin -u user_name -h host_name password "newpwd"
- MySQL Change root Password
- C.5.4.1. How to Reset the Root Password
- http://www.debian-administration.org/articles/442 UPDATE mysql.user SET
Password=PASSWORD('NewPassword') WHERE User='root'; FLUSH PRIVILEGES;
mysqld_safe --init-file=rootreset &
SHOW databases; USE [db name]; SHOW tables; 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
mysqladmin create db_name
Backup and restore
If your tables are primarily InnoDB tables, or if you have a mix of InnoDB and MyISAM tables, consider using the mysqlbackup command of the MySQL Enterprise Backup product. (Available as part of the Enterprise subscription.) It provides the best performance for InnoDB backups with minimal disruption; it can also back up tables from MyISAM and other storage engines; and it provides a number of convenient options to accommodate different backup scenarios. See Section 25.2, “MySQL Enterprise Backup”.
If your tables are primarily MyISAM tables, consider using the mysqlhotcopy instead, for better performance than mysqldump of backup and restore operations. See Section 4.6.9, “mysqlhotcopy — A Database Backup Program”.
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 [options] --databases db_name ... mysqldump [options] --all-databases
mysqldump -u username -p --all-databases --routines > outputfile.sql
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
- 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
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.
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.
- 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.
- 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.