Difference between revisions of "Database"

From Things and Stuff Wiki
Jump to navigation Jump to search
Line 9: Line 9:
 
* [http://dev.mysql.com/doc/ MySQL Reference Manuals]
 
* [http://dev.mysql.com/doc/ MySQL Reference Manuals]
 
** [http://dev.mysql.com/doc/refman/5.6/en/mysqld.html 4.3.1. mysqld — The MySQL Server]
 
** [http://dev.mysql.com/doc/refman/5.6/en/mysqld.html 4.3.1. mysqld — The MySQL Server]
** [http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html 4.6.7. mysqlbinlog] — Utility for Processing Binary Log Files
+
 
 
** [http://dev.mysql.com/doc/refman/5.6/en/server-options.html 5.1.2. Server Command Options]
 
** [http://dev.mysql.com/doc/refman/5.6/en/server-options.html 5.1.2. Server Command Options]
** [http://dev.mysql.com/doc/refman/5.6/en/server-logs.html 5.3. MySQL Server Logs]
 
*** [http://dev.mysql.com/doc/refman/5.6/en/error-log.html 5.3.1. The Error Log]
 
 
** [http://dev.mysql.com/doc/refman/5.6/en/privilege-system.html 6.2. The MySQL Access Privilege System]
 
*** [http://dev.mysql.com/doc/refman/5.6/en/privilege-changes.html 6.2.6. When Privilege Changes Take Effect]
 
*** [http://dev.mysql.com/doc/refman/5.6/en/access-denied.html 6.2.7. Causes of Access-Denied Errors]
 
* [http://hashmysql.org/wiki/Remote_Clients_Cannot_Connect Remote Clients Cannot Connect] - #mysql wiki
 
 
*** [http://dev.mysql.com/doc/refman/5.6/en/assigning-passwords.html 6.3.5. Assigning Account Passwords]
 
 
** [http://dev.mysql.com/doc/refman/5.6/en/describe.html 13.8.1. DESCRIBE Syntax]
 
** [http://dev.mysql.com/doc/refman/5.6/en/resetting-permissions.html C.5.4.1. How to Reset the Root Password]
 
  
 
=== Commands ===
 
=== Commands ===
Line 28: Line 16:
  
 
==== Account management ====
 
==== Account management ====
 +
* [http://dev.mysql.com/doc/refman/5.6/en/privilege-system.html 6.2. The MySQL Access Privilege System]
 +
** [http://dev.mysql.com/doc/refman/5.6/en/privilege-changes.html 6.2.6. When Privilege Changes Take Effect]
 +
 
* [http://dev.mysql.com/doc/refman/5.1/en/account-management-sql.html Account Management Statements
 
* [http://dev.mysql.com/doc/refman/5.1/en/account-management-sql.html Account Management Statements
  
Line 33: Line 24:
 
   
 
   
 
  [http://dev.mysql.com/doc/refman/5.6/en/grant.html GRANT] all on userdatabase.* TO 'username'; [http://www.softwareprojects.com/resources/programming/t-how-to-restrict-mysql-user-access-to-a-single-datab-1734.html]
 
  [http://dev.mysql.com/doc/refman/5.6/en/grant.html GRANT] all on userdatabase.* TO 'username'; [http://www.softwareprojects.com/resources/programming/t-how-to-restrict-mysql-user-access-to-a-single-datab-1734.html]
+
 
 
  [http://dev.mysql.com/doc/refman/5.6/en/show-grants.html 13.7.5.17. SHOW GRANTS Syntax]
 
  [http://dev.mysql.com/doc/refman/5.6/en/show-grants.html 13.7.5.17. SHOW GRANTS Syntax]
  
Line 40: Line 31:
  
 
  [http://dev.mysql.com/doc/refman/5.6/en/flush.html FLUSH] privileges;
 
  [http://dev.mysql.com/doc/refman/5.6/en/flush.html FLUSH] privileges;
 +
 +
* [http://dev.mysql.com/doc/refman/5.6/en/assigning-passwords.html 6.3.5. Assigning Account Passwords]
 +
* [http://dev.mysql.com/doc/refman/5.6/en/resetting-permissions.html C.5.4.1. How to Reset the Root Password]
  
 
==== DB management ====
 
==== DB management ====
 +
** [http://dev.mysql.com/doc/refman/5.6/en/describe.html 13.8.1. DESCRIBE Syntax]
 +
 
  [http://dev.mysql.com/doc/refman/5.6/en/show.html SHOW] databases;
 
  [http://dev.mysql.com/doc/refman/5.6/en/show.html SHOW] databases;
 
  SHOW tables;
 
  SHOW tables;
Line 53: Line 49:
 
==== Security ====
 
==== Security ====
 
* http://dev.mysql.com/doc/refman/5.1/en/default-privileges.html
 
* http://dev.mysql.com/doc/refman/5.1/en/default-privileges.html
 +
 +
=== Troubleshooting ===
 +
* [http://dev.mysql.com/doc/refman/5.6/en/server-logs.html 5.3. MySQL Server Logs]
 +
** [http://dev.mysql.com/doc/refman/5.6/en/error-log.html 5.3.1. The Error Log]
 +
 +
* [http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html 4.6.7. mysqlbinlog] — Utility for Processing Binary Log Files
 +
 +
* [http://dev.mysql.com/doc/refman/5.6/en/access-denied.html 6.2.7. Causes of Access-Denied Errors]
 +
* [http://hashmysql.org/wiki/Remote_Clients_Cannot_Connect Remote Clients Cannot Connect] - #mysql wiki
  
 
=== SQLite ===
 
=== SQLite ===

Revision as of 03:49, 21 July 2012


MySQL

Commands

Account management

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

GRANT all on userdatabase.* TO 'username'; [1]
13.7.5.17. SHOW GRANTS Syntax

Create command list for showing user grants [2]

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

DB management

SHOW databases;
SHOW tables;

USE [db name];

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 ;

Security

Troubleshooting

SQLite

MariaDB

Web interfaces

Admin

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

Other