Database

From Things and Stuff Wiki
Revision as of 00:36, 5 December 2012 by Milk (talk | contribs) (→‎Distributed)
Jump to navigation Jump to search


MySQL

Commands

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

Account management

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;
SET PASSWORD FOR 'user'@'localhost' = PASSWORD('mypass');
mysqladmin -u user_name -h host_name password "newpwd"

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

SQLite

MariaDB

Clients

Web interfaces

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

Admin

mysqladmin create db_name

mysqldump

Tools

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

Scripts

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

PostgreSQL

Redis

See also Server#Redis

Used in the BOA stack for caching.

CouchDB

Couchbase

MongoDB

Other

NewSQL

Distributed

Other