Difference between revisions of "MySQL"

From WhyAskWhy.org Wiki
Jump to: navigation, search
(Adding MySQL page. Other tips should include creating accounts and setting privileges on a database)
 
m (Added additional notes from scratch files I've kept.)
Line 1: Line 1:
 
[[Category:NeedsCleanup]]
 
[[Category:NeedsCleanup]]
 
[[Category:Software]]
 
[[Category:Software]]
 +
 +
== Character length restrictions ==
 +
 +
<ref name="mysql-view-privileges-1" />
 +
<ref name="mysql-view-privileges-2" />
 +
 +
* 64 characters for database name
 +
* 16 characters for user name
 +
* ? characters for password length
 +
** Seems to be dependent on the version
 +
 +
 +
== Create a database ==
 +
 +
<syntaxhighlight lang="mysql">
 +
CREATE DATABASE my_test_db CHARACTER SET utf8;
 +
</syntaxhighlight>
 +
 +
 +
== Create a user for that database with full privileges to it ==
 +
 +
<syntaxhighlight lang="mysql">
 +
CREATE USER 'my_test_db_usr'@'localhost' IDENTIFIED BY 'INITIAL_PASSWORD';
 +
GRANT ALL PRIVILEGES ON my_test_db.* TO 'my_test_db_usr'@'localhost';
 +
</syntaxhighlight>
 +
 +
 +
== Reset password for user account ==
 +
 +
<syntaxhighlight lang="mysql">
 +
SET PASSWORD for 'my_test_db_usr'@'localhost' = PASSWORD('REAL_PASSWORD_HERE');
 +
</syntaxhighlight>
 +
 +
 +
== Delete user account ==
 +
 +
<syntaxhighlight lang="mysql">
 +
DROP USER 'my_test_db_usr'@'localhost';
 +
</syntaxhighlight>
 +
 +
 +
== Delete datbase ==
 +
 +
<syntaxhighlight lang="mysql">
 +
DROP DATABASE 'my_test_db';
 +
</syntaxhighlight>
 +
  
 
== Resetting the root user account ==
 
== Resetting the root user account ==
Line 23: Line 70:
 
UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; exit;
 
UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; exit;
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
  
 
== Viewing privileges granted to a user ==
 
== Viewing privileges granted to a user ==
Line 34: Line 82:
 
* <syntaxhighlight lang="mysql">SHOW GRANTS;</syntaxhighlight>
 
* <syntaxhighlight lang="mysql">SHOW GRANTS;</syntaxhighlight>
 
** For current user
 
** For current user
 +
  
 
== References ==
 
== References ==
Line 40: Line 89:
 
<ref name="mysql-root-reset-1">[http://ubuntu.flowconsult.at/en/mysql-set-change-reset-root-password/ Set / Change / Reset the MySQL root password on Ubuntu Linux]</ref>
 
<ref name="mysql-root-reset-1">[http://ubuntu.flowconsult.at/en/mysql-set-change-reset-root-password/ Set / Change / Reset the MySQL root password on Ubuntu Linux]</ref>
 
<ref name="mysql-root-reset-2">[http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html MySQL :: MySQL 5.0 Reference Manual :: C.5.4.1 How to Reset the Root Password]</ref>
 
<ref name="mysql-root-reset-2">[http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html MySQL :: MySQL 5.0 Reference Manual :: C.5.4.1 How to Reset the Root Password]</ref>
 
  
 
<ref name="mysql-view-privileges-1">[http://serverfault.com/questions/117525/how-can-i-show-users-privileges-in-mysql How can I show user's privileges in MySQL?]</ref>
 
<ref name="mysql-view-privileges-1">[http://serverfault.com/questions/117525/how-can-i-show-users-privileges-in-mysql How can I show user's privileges in MySQL?]</ref>
 
<ref name="mysql-view-privileges-2">[http://www.geekpuppy.com/how-to-show-user-privileges-on-mysql/ How to show user privileges on mysql]</ref>
 
<ref name="mysql-view-privileges-2">[http://www.geekpuppy.com/how-to-show-user-privileges-on-mysql/ How to show user privileges on mysql]</ref>
 +
 +
<ref name="mysql-identifiers-1">[http://dev.mysql.com/doc/refman/5.1/en/user-names.html 6.3.1 User Names and Passwords]</ref>
 +
<ref name="mysql-identifiers-2">[http://dev.mysql.com/doc/refman/5.1/en/identifiers.html 9.2 Schema Object Names]</ref>
 +
 
</references>
 
</references>

Revision as of 09:34, 21 February 2014


Character length restrictions

[1] [2]

  • 64 characters for database name
  • 16 characters for user name
  •  ? characters for password length
    • Seems to be dependent on the version


Create a database

CREATE DATABASE my_test_db CHARACTER SET utf8;


Create a user for that database with full privileges to it

CREATE USER 'my_test_db_usr'@'localhost' IDENTIFIED BY 'INITIAL_PASSWORD';
GRANT ALL PRIVILEGES ON my_test_db.* TO 'my_test_db_usr'@'localhost';


Reset password for user account

SET PASSWORD for 'my_test_db_usr'@'localhost' = PASSWORD('REAL_PASSWORD_HERE');


Delete user account

DROP USER 'my_test_db_usr'@'localhost';


Delete datbase

DROP DATABASE 'my_test_db';


Resetting the root user account

[3] [4] The safest approach is to add the statement containing the password to a file and run

mysqld_safe --init-file=/home/me/FILE &

followed by:

sudo /etc/init.d/mysql stop
sudo mysqld --skip-grant-tables &
mysql -u root mysql
UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; exit;


Viewing privileges granted to a user

[1] [2]

  • select * from mysql.user;
    
    • Parse results by eye (not pretty)
  • SHOW GRANTS FOR username@ipaddress;
    
  • SHOW GRANTS;
    
    • For current user


References

Cite error: <ref> tag with name "mysql-identifiers-1" defined in <references> is not used in prior text.
Cite error: <ref> tag with name "mysql-identifiers-2" defined in <references> is not used in prior text.