Difference between revisions of "MySQL"
m (Added section re option file search order)
m (Fleshed out option file section with additional content from mysql.com.)
|Line 198:||Line 198:|
== References ==
== References ==
Revision as of 13:56, 12 October 2014
- 1 Character length restrictions
- 2 Using comments
- 3 Create a database
- 4 Create a user for that database with full privileges to it
- 5 Reset password for user account
- 6 Import or run SQL statements from file
- 7 Insert a new row
- 8 Update field
- 9 Delete row
- 10 Delete user account
- 11 Delete database
- 12 Resetting the root user account
- 13 Viewing privileges granted to a user
- 14 Show the character set for a database
- 15 Show the database engine type
- 16 MySQL config/option file search order
- 17 References
Character length restrictions
- 64 characters for database name
- 16 characters for user name
- ? characters for password length
- Seems to be dependent on the version
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
Insert a new row
INSERT INTO table_name ( `field_name1`, `field_name2`, `field_name3` ) VALUES ( 'Value1', 'Value2', 'Value3' );
UPDATE table_name SET table_field = 'Value' WHERE id = '1';
DELETE FROM virtual_domains WHERE id = '1' OR id = '7';
Delete user account
DROP USER 'my_test_db_usr'@'localhost';
DROP DATABASE 'my_test_db';
Resetting the root user account
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
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
select * from mysql.user;
- Parse results by eye (not pretty)
SHOW GRANTS FOR username@ipaddress;
- For current user
Show the character set for a database
SHOW CREATE DATABASE database_name;
Show the database engine type
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  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:
|defaults-extra-file||The file specified with --defaults-extra-file=path, if any|
~ 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 attempts to set
MYSQL_HOME as follows:
DATADIRrepresent the path names of the MySQL base directory and data directory, respectively.
- If there is a
DATADIRbut not in
- Otherwise, if
MYSQL_HOMEis not set and there is no
In MySQL 5.5, use of
DATADIR as the location for
my.cnf is deprecated.
/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.
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
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.
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
- 6.3.1 User Names and Passwords
- 9.2 Schema Object Names
- Are there multiline comment delimiters in SQL that are vendor agnostic?
- WikiBooks - MySQL CheatSheet
- Set / Change / Reset the MySQL root password on Ubuntu Linux
- MySQL :: MySQL 5.0 Reference Manual :: C.5.4.1 How to Reset the Root Password
- How can I show user's privileges in MySQL?
- How to show user privileges on mysql
- How do I see what character set a database / table / column is in MySQL?
- How can I check MySQL engine type for a specific table?
- MySQL 5.5 Reference Manual :: 4 MySQL Programs :: 4.2 Using MySQL Programs :: 4.2.6 Using Option Files