Difference between revisions of "MySQL"

From WhyAskWhy.org Wiki
Jump to: navigation, search
m (Added tips for showing the database character set and engine type.)
m (Added references)
Line 92: Line 92:
  
 
== Show the character set for a database ==
 
== Show the character set for a database ==
 +
 +
<ref name="mysql-database-character-set" />
  
 
<syntaxhighlight lang="mysql">
 
<syntaxhighlight lang="mysql">
Line 99: Line 101:
  
 
== Show the database engine type ==
 
== Show the database engine type ==
 +
 +
<ref name="mysql-database-engine-type" />
  
 
<syntaxhighlight lang="mysql">
 
<syntaxhighlight lang="mysql">
Line 117: Line 121:
 
<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-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>
 
<ref name="mysql-identifiers-2">[http://dev.mysql.com/doc/refman/5.1/en/identifiers.html 9.2 Schema Object Names]</ref>
 +
 +
<ref name="mysql-database-engine-type">[http://stackoverflow.com/questions/213543/how-can-i-check-mysql-engine-type-for-a-specific-table How can I check MySQL engine type for a specific table?]</ref>
 +
 +
<ref name="mysql-database-character-set">[http://stackoverflow.com/questions/1049728/how-do-i-see-what-character-set-a-database-table-column-is-in-mysql How do I see what character set a database / table / column is in MySQL?]</ref>
  
 
</references>
 
</references>

Revision as of 23:38, 5 March 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');


Import or run SQL statements from file

source my-sql-file.sql;


Delete user account

DROP USER 'my_test_db_usr'@'localhost';


Delete database

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

[5] [6]

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


Show the character set for a database

[7]

SHOW CREATE DATABASE database_name;


Show the database engine type

[8]

USE DATABASE database_name;
SHOW CREATE TABLE table_name;


References