MySQL

From WhyAskWhy.org Wiki
Revision as of 10:34, 21 February 2014 by Deoren (talk | contribs) (Added additional notes from scratch files I've kept.)
Jump to: navigation, search


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.