Difference between revisions of "MySQL"
m (Added example of adding fields to existing table.)
m (Updated the row insert directions to list an example for multiple rows.)
|Line 204:||Line 204:|
== new ==
<ref name="mysql-wikibooks-cheatsheet" />
<ref name="mysql-wikibooks-cheatsheet" />
INSERT INTO table_name (
== Update field ==
== Update field ==
Revision as of 14:18, 16 November 2014
Note: Examples shown here were tested on a range of MySQL versions from v5.1 to v5.5 with no regard to compatibility across versions.
- 1 Character length restrictions
- 2 Using comments in SQL files
- 3 Create a database
- 4 Create a table
- 5 Create a user for that database with full privileges to it
- 6 Update user account to allow connecting from a different host
- 7 Reset password for user account
- 8 Viewing the account you're logged in as (equivalent of UNIX whoami)
- 9 Import or run SQL statements from file
- 10 Rename table
- 11 Alter table
- 12 Inserting new rows
- 13 Update field
- 14 Delete row
- 15 Delete user account
- 16 Delete database
- 17 Resetting the root user account
- 18 Viewing privileges granted to a user
- 19 Show the character set for a database
- 20 Show the database engine type
- 21 MySQL config/option file search order
- 22 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
- Field comments
- 255 before before MySQL 5.5.3
- 1024 after MySQL 5.5.3
- Table comments
- 60 characters before MySQL 5.5.3
- 2048 characters after MySQL 5.5.3
Using comments in SQL files
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 table
CREATE TABLE pet ( name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE );
More complex example showing table and field comments
-- Holds per-user custom expiration settings for various IMAP mailboxes CREATE TABLE `mailserver`.`mailbox_custom_expiration_settings` ( `id` int(11) NOT NULL auto_increment, `user_id` int(11) NOT NULL COMMENT "The virtual user id", `imap_mailbox_name` text NOT NULL COMMENT "Examples: Newsletters, Trash, Spam, Facebook, Cuteoverload", `imap_search_key` varchar(20) NOT NULL DEFAULT 'SAVEDBEFORE' COMMENT "BEFORE or SAVEDBEFORE are supported choices", `date_specification_interval` VARCHAR(20) NOT NULL COMMENT "Should be set to a period shorter than the default to be effective", `last_modified` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `comments` text, PRIMARY KEY (`id`), -- Nuke the matching row from this table that corresponds to the virtual user id FOREIGN KEY (`user_id`) REFERENCES virtual_users(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT="Controls (optional) per-user expiration times for mail content" ;
Create a user for that database with full privileges to it
Depending on your needs, you'll setup the user in one of several ways.
Database server is on the same box as the application
Connecting via a UNIX socket
CREATE USER 'my_test_db_usr'@'localhost' IDENTIFIED BY 'INITIAL_PASSWORD'; GRANT ALL PRIVILEGES ON my_test_db.* TO 'my_test_db_usr'@'localhost';
Connecting via a TCP socket
Useful if the application is running inside of a chroot jail for instance
CREATE USER 'my_test_db_usr'@'127.0.0.1' IDENTIFIED BY 'INITIAL_PASSWORD'; GRANT ALL PRIVILEGES ON my_test_db.* TO 'my_test_db_usr'@'127.0.0.1';
Application is on a remote server
Remote host is specified via IP Address
CREATE USER 'my_test_db_usr'@'192.168.1.105' IDENTIFIED BY 'INITIAL_PASSWORD'; GRANT ALL PRIVILEGES ON my_test_db.* TO 'my_test_db_usr'@'192.168.1.105';
Remote host is specified by hostname
Valid DNS A record or /etc/hosts entry
CREATE USER 'my_test_db_usr'@'webserver1.example.org' IDENTIFIED BY 'INITIAL_PASSWORD'; GRANT ALL PRIVILEGES ON my_test_db.* TO 'my_test_db_usr'@'webserver1.example.org';
Update user account to allow connecting from a different host
If you created your MySQL user account and limited it to connections from a specific IP Address, at some point you'll need to create another account or update the host it is allowed to be used from (my choice) if the remote IP changes. One example would be moving the application server from one VLAN to another. In our example 192.168.2.25 is the new IP and the old IP was 192.168.1.105.
UPDATE user set Host = '192.168.2.25' WHERE User = 'my_test_db_usr' AND Host = '192.168.1.105'; FLUSH PRIVILEGES;
It's important to note however that you'll need to reapply database permissions for the affected user account however. What we've done so far has only adjusted the login or USAGE permission.
Reset password for user account
SET PASSWORD for 'my_test_db_usr'@'localhost' = PASSWORD('REAL_PASSWORD_HERE');
Viewing the account you're logged in as (equivalent of UNIX whoami)
USER()reports how you attempted to authenticate in MySQL
CURRENT_USER()reports how you were allowed to authenticate in MySQL
RolandoMySQLDBA notes: Sometimes, they are different
Import or run SQL statements from file
RENAME TABLE `my_test_db`.`table_name` TO `my_test_db`.`my_new_table_name`;
Insert new field
ALTER TABLE contacts ADD email VARCHAR(60);
Insert new field at specific location
ALTER TABLE contacts ADD email VARCHAR(60) AFTER name;
ALTER TABLE contacts ADD email VARCHAR(60) FIRST;
Inserting new rows
Note: For fields that have default values, you can skip including those field names and values in the
INSERT INTO and
VALUES statements if you wish to use the defaults.
A single row
INSERT INTO table_name ( `field_name1`, `field_name2`, `field_name3` ) VALUES ( 'Value1', 'Value2', 'Value3' );
INSERT INTO table_name ( `field_name1`, `field_name2`, `field_name3` ) VALUES ('Value1', 'Value2', 'Value3'), ('Value1', 'Value2', 'Value3'), ('Value1', 'Value2', 'Value3'), ('Value1', 'Value2', 'Value3'), ('Value1', 'Value2', 'Value3'), ('Value1', 'Value2', 'Value3') ;
Pay careful attention to the lack of a trailing comma for the last row we are inserting. I often forget and include it and have to remove it after MySQL complains.
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.
|defaults-extra-file||The file specified with --defaults-extra-file=path, if any|
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.
$ mysql --help --verbose | grep -A 1 'Default options'
Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
- 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
--useroption is used as a security precaution, to prevent a user specified in an option file from being overridden on the command line.
- Exception: For
~represents the current user's home directory (the value of $HOME).
SYSCONFDIRrepresents the directory specified with the
SYSCONFDIRoption to CMake when MySQL was built. By default, this is the etc directory located under the compiled-in installation directory.
MYSQL_HOMEis an environment variable containing the path to the directory in which the server-specific
my.cnffile resides. If
MYSQL_HOMEis not set and you start the server using the
mysqld_safeattempts to set
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.
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?
- MySQL 5.5 Reference Manual :: 3 Tutorial :: 3.3 Creating and Using a Database :: 3.3.1 Creating and Selecting a Database
- [MySQL 5.5 Reference Manual :: 13 SQL Statement Syntax :: 13.1 Data Definition Statements :: 13.1.17 CREATE TABLE Syntax]]
- MySql: Give Root User Logon Permission From Any Host
- how do I see which user I am logged in as in MySQL?
- MySQL 5.5 Reference Manual :: 13 SQL Statement Syntax :: 13.1 Data Definition Statements :: 13.1.7 ALTER TABLE Syntax
- Tech-Recipes - Add a column to an existing MySQL table
- 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