Difference between revisions of "MySQL"

From WhyAskWhy.org Wiki
Jump to: navigation, search
m (Fleshed out option file section with additional content from mysql.com.)
m (Rearranged content, emphasized specific items.)
Line 171: Line 171:
  
 
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.
 
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"
 
{| class="wikitable"
|+MySQL option file search order
+
|+Unix, Linux and Mac OS X MySQL option file search order
 
|-
 
|-
 
!File Name
 
!File Name
Line 199: Line 197:
 
|}
 
|}
  
<code>~</code> represents the current user's home directory (the value of $HOME).
+
=== Important ===
  
<code>SYSCONFDIR</code> represents the directory specified with the <code>SYSCONFDIR</code> option to CMake when MySQL was built. By default, this is the etc directory located under the compiled-in installation directory.
+
* '''On Unix platforms, MySQL ignores configuration files that are world-writable. This is intentional as a security measure.'''
 +
* MySQL looks for option files in the order listed in the table reads any that exist.
 +
* If multiple instances of a given option are found, the last instance takes precedence.
 +
** Exception: For <code>mysqld</code>, the first instance of the <code>--user</code> option is used as a security precaution, to prevent a user specified in an option file from being overridden on the command line.
  
<code>MYSQL_HOME</code> is an environment variable containing the path to the directory in which the server-specific <code>my.cnf</code> file resides. If <code>MYSQL_HOME</code> is not set and you start the server using the <code>mysqld_safe</code> program, <code>mysqld_safe</code> attempts to set <code>MYSQL_HOME</code> as follows:
+
=== Path variables ===
* Let <code>BASEDIR</code> and <code>DATADIR</code> represent the path names of the MySQL base directory and data directory, respectively.
+
 
* If there is a <code>my.cnf</code> file in <code>DATADIR</code> but not in <code>BASEDIR</code>, <code>mysqld_safe</code> sets <code>MYSQL_HOME</code> to  
+
* <code>~</code> represents the current user's home directory (the value of $HOME).
code>DATADIR</code>.
+
* <code>SYSCONFDIR</code> represents the directory specified with the <code>SYSCONFDIR</code> option to CMake when MySQL was built. By default, this is the etc directory located under the compiled-in installation directory.
* Otherwise, if <code>MYSQL_HOME</code> is not set and there is no <code>my.cnf</code> file in <code>DATADIR</code>, <code>mysqld_safe</code> sets <code>MYSQL_HOME</code> to <code>BASEDIR</code>.  
+
* <code>MYSQL_HOME</code> is an environment variable containing the path to the directory in which the server-specific <code>my.cnf</code> file resides. If <code>MYSQL_HOME</code> is not set and you start the server using the <code>mysqld_safe</code> program, <code>mysqld_safe</code> attempts to set <code>MYSQL_HOME</code> as follows:
 +
** Let <code>BASEDIR</code> and <code>DATADIR</code> represent the path names of the MySQL base directory and data directory, respectively.
 +
** If there is a <code>my.cnf</code> file in <code>DATADIR</code> but not in <code>BASEDIR</code>, <code>mysqld_safe</code> sets <code>MYSQL_HOME</code> to <code>DATADIR</code>.
 +
** Otherwise, if <code>MYSQL_HOME</code> is not set and there is no <code>my.cnf</code> file in <code>DATADIR</code>, <code>mysqld_safe</code> sets <code>MYSQL_HOME</code> to <code>BASEDIR</code>.  
  
 
'''In MySQL 5.5, use of <code>DATADIR</code> as the location for <code>my.cnf</code> is deprecated.'''
 
'''In MySQL 5.5, use of <code>DATADIR</code> as the location for <code>my.cnf</code> is deprecated.'''
  
 
Typically, <code>DATADIR</code> is <code>/usr/local/mysql/data</code> for a binary installation or <code>/usr/local/var</code> for a source installation. Note that this is the data directory location that was specified at configuration time, not the one specified with the --datadir option when mysqld starts. Use of --datadir at runtime has no effect on where the server looks for option files, because it looks for them before processing any options.
 
Typically, <code>DATADIR</code> is <code>/usr/local/mysql/data</code> for a binary installation or <code>/usr/local/var</code> for a source installation. Note that this is the data directory location that was specified at configuration time, not the one specified with the --datadir option when mysqld starts. Use of --datadir at runtime has no effect on where the server looks for option files, because it looks for them before processing any options.
 
MySQL looks for option files in the order just described and reads any that exist. If an option file that you want to use does not exist, create it with a plain text editor.
 
 
If multiple instances of a given option are found, the last instance takes precedence. There is one exception: For <code>mysqld</code>, the first instance of the <code>--user</code> option is used as a security precaution, to prevent a user specified in an option file from being overridden on the command line.
 
Note
 
 
'''On Unix platforms, MySQL ignores configuration files that are world-writable. This is intentional as a security measure.'''
 
  
 
''Any long option that may be given on the command line when running a MySQL program can be given in an option file as well.'' To get the list of available options for a program, run it with the <code>--help</code> option.
 
''Any long option that may be given on the command line when running a MySQL program can be given in an option file as well.'' To get the list of available options for a program, run it with the <code>--help</code> option.

Revision as of 14:03, 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.

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

Important

  • On Unix platforms, MySQL ignores configuration files that are world-writable. This is intentional as a security measure.
  • MySQL looks for option files in the order listed in the table reads any that exist.
  • If multiple instances of a given option are found, the last instance takes precedence.
    • Exception: For mysqld, the first instance of the --user option is used as a security precaution, to prevent a user specified in an option file from being overridden on the command line.

Path variables

  • ~ represents the current user's home directory (the value of $HOME).
  • SYSCONFDIR represents the directory specified with the SYSCONFDIR option to CMake when MySQL was built. By default, this is the etc directory located under the compiled-in installation directory.
  • MYSQL_HOME is an environment variable containing the path to the directory in which the server-specific my.cnf file resides. If MYSQL_HOME is not set and you start the server using the mysqld_safe program, mysqld_safe attempts to set MYSQL_HOME as follows:
    • Let BASEDIR and DATADIR represent the path names of the MySQL base directory and data directory, respectively.
    • If there is a my.cnf file in DATADIR but not in BASEDIR, mysqld_safe sets MYSQL_HOME to DATADIR.
    • Otherwise, if MYSQL_HOME is not set and there is no my.cnf file in DATADIR, mysqld_safe sets MYSQL_HOME to BASEDIR.

In MySQL 5.5, use of DATADIR as the location for my.cnf is deprecated.

Typically, DATADIR is /usr/local/mysql/data for a binary installation or /usr/local/var for a source installation. Note that this is the data directory location that was specified at configuration time, not the one specified with the --datadir option when mysqld starts. Use of --datadir at runtime has no effect on where the server looks for option files, because it looks for them before processing any options.

Any long option that may be given on the command line when running a MySQL program can be given in an option file as well. To get the list of available options for a program, run it with the --help option.

References