Database
to sort
General
- 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.
Relational
- http://en.wikipedia.org/wiki/Relational_database
- http://en.wikipedia.org/wiki/Relational_database_management_system
SQL
- 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
Connecting
mysql -u username -ppassword -h hostname databasename basic connection
Account management
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'; CREATE DATABASE [userdatabase]; GRANT all on [userdatabase].* TO '[username]'; [2]
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 [3]
SELECT CONCAT("SHOW GRANTS FOR '",user,"'@'",host,"';") FROM mysql.user WHERE host!='localhost';
FLUSH privileges;
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 &
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.
Security
Troubleshooting
- 4.6.7. mysqlbinlog — Utility for Processing Binary Log Files
Admin
mysqladmin create db_name
Backup
- 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
mysqldump
Tools
- dbdeploy is a Database Change Management tool.
- 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://sourceforge.net/projects/ajaxmytop/ - monitoring
Scripts
- 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
Performance
Clients
phpMyAdmin
Other web
- Adminer - single php file
wget http://www.adminer.org/latest-mysql-en.php -O adminer.php wget http://www.adminer.org/latest-en.php -O adminer.php
MariaDB
Fork of MySQL, drop in replacement.
SQLite
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
NoSQL
Redis
See also Server#Redis
Used in the BOA stack for caching.
CouchDB
- 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.
Couchbase
MongoDB
- http://snmaynard.com/2012/10/17/things-i-wish-i-knew-about-mongodb-a-year-ago/
- http://blog.serverdensity.com/does-everyone-hate-mongodb/
JavaScript
Other
to sort
- http://radar.oreilly.com/2012/02/nosql-non-relational-database.html - http://news.ycombinator.com/item?id=3610844