Database

From Things and Stuff Wiki
Revision as of 09:20, 15 December 2015 by Milk (talk | contribs) (→‎to sort)
Jump to navigation Jump to search


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

SQL

  • SQL-DK – an batch/terminal client for relational databases

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]'; [6]

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

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"

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

Admin

mysqladmin create db_name

Backup

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

mysqldump
mysqldump [options] db_name [tbl_name ...]

mysqldump [options] --databases db_name ...

mysqldump [options] --all-databases



mysqldump -u username -p --all-databases --routines > outputfile.sql
mysqlbackup

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

Replication

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

Performance

Clients

phpMyAdmin
Other web
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


NoSQL

dbm

Redis

See also Server#Redis

Used in the BOA stack for caching.

CouchDB

Couchbase

MongoDB

JavaScript

MDBM


Other

to sort

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

gpu;


Graph

Distributed

Social

Other




Retrieved from "https://wiki.thingsandstuff.org/index.php?title=Database&oldid=32377"