Difference between revisions of "Database"
Jump to navigation
Jump to search
| Line 18: | Line 18: | ||
* [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-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/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] | ||
[http://dev.mysql.com/doc/refman/5.6/en/create-user.html CREATE USER ]'jeffrey'@'localhost' IDENTIFIED BY 'mypass'; | [http://dev.mysql.com/doc/refman/5.6/en/create-user.html CREATE USER ]'jeffrey'@'localhost' IDENTIFIED BY 'mypass'; | ||
CREATE DATABASE [databasename]; | CREATE DATABASE [databasename]; | ||
| − | + | ||
[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 | + | [http://dev.mysql.com/doc/refman/5.6/en/show-grants.html SHOW GRANTS] |
| + | SHOW GRANTS FOR 'user'@'localhost'; | ||
Create command list for showing user grants [http://www.pythian.com/news/1165/creative-sql-how-to-easily-show-grants-for-many-users/] | Create command list for showing user grants [http://www.pythian.com/news/1165/creative-sql-how-to-easily-show-grants-for-many-users/] | ||
Revision as of 02:56, 22 July 2012
MySQL
- Wikipedia:MySQL
- InnoDB is the default storage engine for MySQL
Commands
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;
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
- 4.6.7. mysqlbinlog — Utility for Processing Binary Log Files
SQLite
MariaDB
Web interfaces
Admin
mysqladmin create db_name
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."