Database

General

• http://en.wikipedia.org/wiki/Relational_database
• http://en.wikipedia.org/wiki/Relational_database_management_system
• http://en.wikipedia.org/wiki/SQL

• http://en.wikipedia.org/wiki/Entity-relationship_model
• http://en.wikipedia.org/wiki/Unique_key

• http://en.wikipedia.org/wiki/NoSQL
  • http://blog.mongodb.org/post/119945109/why-schemaless

• Google I/O 2012 - SQL vs NoSQL: Battle of the Backends

• https://vimeo.com/56639635 - Michael 'Monty' Widenius - Author of the MySQL Server and MariaDB fork

MySQL

• Wikipedia:MySQL
  • InnoDB is the default storage engine for MySQL

• Developer Zone
• MySQL Forge

• MySQL Reference Manuals
  • 4.3.1. mysqld — The MySQL Server

• • 5.1.2. Server Command Options

Commands

mysql -u username -ppassword -h hostname databasename
  basic connection

• MySQL Administrator Best Practices

Account management

• 6.2. The MySQL Access Privilege System
  • 6.2.6. When Privilege Changes Take Effect
• Account Management Statements

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
CREATE DATABASE [databasename];

GRANT all on userdatabase.* TO 'username'; [1]

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 [2]

SELECT CONCAT("SHOW GRANTS FOR '",user,"'@'",host,"';") FROM mysql.user WHERE host!='localhost';

FLUSH privileges;

• 6.3.5. Assigning Account Passwords

SET PASSWORD FOR 'user'@'localhost' = PASSWORD('mypass');

mysqladmin -u user_name -h host_name password "newpwd"

• 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 &

DB management

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.

• http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

Security

• http://dev.mysql.com/doc/refman/5.1/en/default-privileges.html

Troubleshooting

• 5.3. MySQL Server Logs
  • 5.3.1. The Error Log

• 4.6.7. mysqlbinlog — Utility for Processing Binary Log Files

• 6.2.7. Causes of Access-Denied Errors
• Remote Clients Cannot Connect - #mysql wiki

SQLite

• SQLite
  • Categorical Index Of SQLite Documents
  • SQL As Understood By SQLite

MariaDB

• MariaDB Knowledge Base
  • Using mysqlbinlog

• https://kb.askmonty.org/en/mariadb-vs-mysql-compatibility/

Clients

• http://sql-workbench.net/

• http://squirrel-sql.sourceforge.net/

• http://heidisql.com/

• http://navicat.com/en/products/navicat_mysql/mysql_detail_linux.html

Web

phpMyAdmin

• phpMyAdmin
  • docs
  • wiki

other

• Chive

• http://sqlbuddy.com/

• Adminer - single php file
  • http://en.wikipedia.org/wiki/Adminer

wget http://www.adminer.org/latest-mysql-en.php -O adminer.php
wget http://www.adminer.org/latest-en.php -O adminer.php

Admin

• MySQL Administrator Best Practices
• MySQL Backup and Security Best Practices

• http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterSQLServerDatabases.aspx

mysqladmin create db_name

• http://serverfault.com/questions/9948/what-is-the-debian-sys-maint-mysql-user-and-more

Backup

• http://dev.mysql.com/doc/refman/5.0/en/backup-and-recovery.html

• http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html
• http://www.mysqldumper.net/
• http://zmanda.com/backup-mysql.html
• http://www.percona.com/doc/percona-xtrabackup/manual.html?id=percona-xtrabackup:xtrabackup_manual

Replication

• http://dev.mysql.com/doc/refman/5.0/en/replication.html

mysqldump

• mysqldump
  • http://stackoverflow.com/questions/104612/run-mysqldump-without-locking-tables
  • http://www.cyberciti.biz/faq/linux-unix-mysqldump-got-error1044-access-denied/

Tools

• dbdeploy is a Database Change Management tool.
  • http://davedevelopment.co.uk/2008/04/14/how-to-simple-database-migrations-with-phing-and-dbdeploy.html
• https://github.com/tanin47/php_db_migrate
• https://bitbucket.org/stepancheg/mysql-diff/wiki/Home

• 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://peric.github.com/GetCountries/
• https://github.com/geraldb/world.db

• http://sourceforge.net/projects/ajaxmytop/ - monitoring

• http://dbpatterns.com/

• https://github.com/victorstanciu/dbv/

• http://www.percona.com/software/percona-toolkit

• https://github.com/leftnode/dbmigrator

Scripts

• http://jetpackweb.com/blog/2009/07/20/bash-script-to-create-mysql-database-and-user/
  • createdb.sh

• 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.
  • http://interconnectit.com/124/search-and-replace-for-wordpress-databases/

./searchreplacedb2cli.php --host localhost --user root --database test --pass "pass"
     --charset utf\-8 --search "findMe" --replace "replaceMe"
--dry-run

Performance

• http://use-the-index-luke.com/welcome

PostgreSQL

• http://www.craigkerstiens.com/2012/04/30/why-postgres/
• http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL
• http://wekeroad.com/2012/07/19/postgresql-rising
• https://speakerdeck.com/u/craigkerstiens/p/postgres-demystified

• http://pgmodeler.com.br/?

Redis

See also Server#Redis

• http://redis.io/
  • http://en.wikipedia.org/wiki/Redis
  • http://www.mediawiki.org/wiki/Redis

Used in the BOA stack for caching.

• http://blog.grayproductions.net/articles/setting_up_the_redis_server

• https://github.com/andymccurdy/redis-py

CouchDB

• http://wiki.apache.org/couchdb/FrontPage

• http://pouchdb.com/
  • https://github.com/daleharvey/pouchdb

Couchbase

• http://www.couchbase.com/couchbase-server/overview
  • http://en.wikipedia.org/wiki/Couchbase_Server

• http://stackoverflow.com/questions/5578608/difference-between-couchdb-and-couchbase

MongoDB

• http://www.mongodb.org/

• https://www.mongohq.com/home

• http://snmaynard.com/2012/10/17/things-i-wish-i-knew-about-mongodb-a-year-ago/
• http://blog.serverdensity.com/does-everyone-hate-mongodb/

Other

• http://radar.oreilly.com/2012/02/nosql-non-relational-database.html - http://news.ycombinator.com/item?id=3610844

• http://developers.memsql.com/

• http://phppgadmin.sourceforge.net/doku.php

• http://datomic.com/

• http://highscalability.com/blog/2012/7/9/data-replication-in-nosql-databases.html

• http://www.youtube.com/watch?v=Cym4TZwTCNU

• http://labs.codernity.com/codernitydb/

NewSQL

• http://www.nuodb.com/

Distributed

• http://www.rethinkdb.com/

Social

• http://thebigdb.com/

Other

• http://highscalability.com/blog/2012/12/10/switch-your-databases-to-flash-storage-now-or-youre-doing-it.html

• http://craigkerstiens.com/2012/11/30/sharding-your-database/