Database

MySQL

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

• Developer Zone

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

• • 5.1.2. Server Command Options

• [http://dev.mysql.com/tech-resources/articles/mysql-administrator-best-practices.html

Commands

• http://www.pantz.org/software/mysql/mysqlcommands.html 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 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

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

Web interfaces

• phpMyAdmin
  • docs
  • wiki

• Chive

• http://www.adminer.org/en

Admin

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

mysqladmin create db_name

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."

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

Other

• http://wekeroad.com/2012/07/19/postgresql-rising

• 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