Difference between revisions of "MySQL"

From WhyAskWhy.org Wiki
Jump to: navigation, search
m (Added database category)
m (Added section re option file search order)
Line 165: Line 165:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
 +
 +
== MySQL config/option file search order ==
 +
 +
The MySQL configuration file (referred to as an <code>option</code> file) can be located in multiple places, but the order of precedence determines which configuration file will be used when you do not explicitly specify a file.
 +
 +
As mentioned on the ''Using Option Files'' <ref name="mysql-option-files"/> reference page running <code>mysql</code> with the <code>--help</code> option will show you the preconfigured locations that MySQL will search for the options file.
 +
 +
By default these are the locations on Unix, Linux and Mac OS X:
 +
 +
{| class="wikitable"
 +
|+MySQL option file search order
 +
|-
 +
!File Name
 +
!Purpose
 +
|-
 +
|/etc/my.cnf
 +
|Global options
 +
|-
 +
|/etc/mysql/my.cnf
 +
|Global options
 +
|-
 +
|SYSCONFDIR/my.cnf
 +
|Global options
 +
|-
 +
|$MYSQL_HOME/my.cnf
 +
|Server-specific options
 +
|-
 +
|defaults-extra-file
 +
|The file specified with --defaults-extra-file=path, if any
 +
|-
 +
|~/.my.cnf
 +
|User-specific options
 +
|}
  
 
== References ==
 
== References ==
Line 186: Line 219:
 
<ref name="mysql-valid-comment-styles">[http://stackoverflow.com/questions/728172/are-there-multiline-comment-delimiters-in-sql-that-are-vendor-agnostic Are there multiline comment delimiters in SQL that are vendor agnostic?]</ref>
 
<ref name="mysql-valid-comment-styles">[http://stackoverflow.com/questions/728172/are-there-multiline-comment-delimiters-in-sql-that-are-vendor-agnostic Are there multiline comment delimiters in SQL that are vendor agnostic?]</ref>
  
 +
<ref name="mysql-option-files">[http://dev.mysql.com/doc/refman/5.5/en/option-files.html MySQL 5.5 Reference Manual :: 4 MySQL Programs :: 4.2 Using MySQL Programs :: 4.2.6 Using Option Files]</ref>
 
</references>
 
</references>

Revision as of 13:45, 12 October 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


Using comments

[3]

These are all valid comment styles.

# Comment 1
-- Comment 2
/*
 
    Comment 3

*/


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;


Insert a new row

[4]

  INSERT INTO table_name (
      `field_name1`, 
      `field_name2`, 
      `field_name3`
  )
  VALUES (
      'Value1', 
      'Value2', 
      'Value3'
  );


Update field

[4]

UPDATE table_name SET table_field = 'Value' WHERE id = '1';


Delete row

DELETE FROM virtual_domains WHERE id = '1' OR id = '7';


Delete user account

DROP USER 'my_test_db_usr'@'localhost';


Delete database

DROP DATABASE 'my_test_db';


Resetting the root user account

[5] [6]

More Secure

1 nano /home/me/FILE
2 # Add SQL statement from section below
3 # Save & quit nano
4 sudo /etc/init.d/mysql stop
5 mysqld_safe --init-file=/home/me/FILE &
6 rm /home/me/FILE
7 sudo /etc/init.d/mysql restart

Less secure

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


Viewing privileges granted to a user

[7] [8]

  • 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

[9]

SHOW CREATE DATABASE database_name;


Show the database engine type

[10]

USE DATABASE database_name;
SHOW CREATE TABLE table_name;


MySQL config/option file search order

The MySQL configuration file (referred to as an option file) can be located in multiple places, but the order of precedence determines which configuration file will be used when you do not explicitly specify a file.

As mentioned on the Using Option Files [11] reference page running mysql with the --help option will show you the preconfigured locations that MySQL will search for the options file.

By default these are the locations on Unix, Linux and Mac OS X:

MySQL option file search order
File Name Purpose
/etc/my.cnf Global options
/etc/mysql/my.cnf Global options
SYSCONFDIR/my.cnf Global options
$MYSQL_HOME/my.cnf Server-specific options
defaults-extra-file The file specified with --defaults-extra-file=path, if any
~/.my.cnf User-specific options

References