Difference between revisions of "Database"
Jump to navigation
Jump to search
(→MySQL) |
|||
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/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] | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
=== 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
- Wikipedia:MySQL
- InnoDB is the default storage engine for MySQL
Commands
Account management
- [http://dev.mysql.com/doc/refman/5.1/en/account-management-sql.html Account Management Statements
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
- 4.6.7. mysqlbinlog — Utility for Processing Binary Log Files
SQLite
MariaDB
Web interfaces
Admin
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."