Difference between revisions of "MySQL"
m (Fix closing tag syntax) |
m (Begun cleanup work for limits section (much more to do)) |
||
Line 16: | Line 16: | ||
* 64 characters for database name | * 64 characters for database name | ||
− | * 16 characters | + | |
− | * | + | * username |
− | ** | + | ** 16 characters <= 5.6 |
+ | ** 32 characters >= 5.7 | ||
+ | |||
+ | * 32 characters for password length | ||
+ | ** Specific to MySQL replication slave account | ||
+ | ** Resolved in MySQL 5.7 (Bug #11752299, Bug #43439) | ||
+ | |||
* Field comments | * Field comments | ||
** 255 before before MySQL 5.5.3 | ** 255 before before MySQL 5.5.3 | ||
** 1024 after MySQL 5.5.3 | ** 1024 after MySQL 5.5.3 | ||
+ | |||
* Table comments | * Table comments | ||
** 60 characters before MySQL 5.5.3 | ** 60 characters before MySQL 5.5.3 |
Latest revision as of 11:24, 31 October 2018
Note: Examples shown here were tested on a range of MySQL versions from v5.1 to v5.5 and MariaDB v10.0 with no regard to compatibility across versions.
Contents
- 1 TODO
- 2 Character length restrictions
- 3 Security tips
- 4 SELECT statement syntax
- 5 Using comments in SQL files
- 6 Create a database
- 7 Create a table
- 8 Display Tables or Views
- 9 How to determine the size of MySQL databases and tables
- 10 Create a View
- 11 Create a user for that database with full privileges to it
- 12 Connecting from a different host
- 13 Reset password for user account
- 14 Viewing the account you're logged in as (equivalent of UNIX whoami)
- 15 Import or run SQL statements from file
- 16 Rename table
- 17 Alter table
- 18 Inserting new rows
- 19 Update field
- 20 Delete row
- 21 Delete user account
- 22 Delete database
- 23 Resetting the root user account
- 24 Viewing privileges granted to a user
- 25 Show the character set for a database
- 26 Show the database engine type
- 27 Safely/gracefully restart MySQL/MariaDB
- 28 Restoring databases
- 29 MySQL config/option file search order
- 30 References
TODO
Consider breaking content into subpages? Perhaps a "Create" page for databases, tables, users? A "Drop" page, etc?
Character length restrictions
- 64 characters for database name
- username
- 16 characters <= 5.6
- 32 characters >= 5.7
- 32 characters for password length
- Specific to MySQL replication slave account
- Resolved in MySQL 5.7 (Bug #11752299, Bug #43439)
- 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
Security tips
- Rename root account
- Run
mysql_secure_installation
after a fresh installation
SELECT
statement syntax
A quick reminder of the proper keyword order [5]
SELECT FIELD(S) FROM TABLES JOIN OTHER TABLES WHERE CRITERIA GROUP BY ANY GROUPS HAVING FILTERS FOR GROUPED VALUES ORDER BY SORT ORDER
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
Simple example
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"
;
Display Tables or Views
- As of MySQL 5.0.1 the
SHOW TABLES
statement also displaysview
names. - As of MySQL 5.0.2 the (optional)
FULL
keyword may be given to display for each table whether the name refers to abase table
or aview
.
SHOW FULL TABLES FROM my_test_db;
or
USE my_test_db;
SHOW FULL TABLES;
How to determine the size of MySQL databases and tables
Credit: These recipes are from a a2hosting.com KB article [9]
List size of databases
SELECT table_schema AS "Database",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
GROUP BY table_schema;
List size of tables within a specific database
SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "database_name"
ORDER BY (data_length + index_length) DESC;
Create a View
[10] Syntax:
CREATE [OR REPLACE] [ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}] [DEFINER = definer_name] [SQL SECURITY = {DEFINER | INVOKER}] VIEW view_name [(col_list)] AS select_stmt [WITH [CASCADED | LOCAL] CHECK OPTION]
- [11] When the view is invoked, the
DEFINER
andSQL SECURITY
clauses determine the security context (the account to use for access checking). The default is to use the account for the user who executes theCREATE VIEW
statement.
- The view definition is "frozen" at creation time, so changes to the underlying tables afterward do not affect the view definition. For example, if a view is defined as
SELECT *
on a table, new columns added to the table later do not become part of the view.
CREATE VIEW `mailserver`.`user_access` AS
SELECT
virtual_users.email,
virtual_users.enabled,
service_access.smtp_receive_active,
service_access.smtp_send_active,
service_access.pop3_active,
service_access.chat_active,
service_access.webmail_active,
service_access.comments
FROM
service_access,
virtual_users
WHERE
virtual_users.id = service_access.user_id
;
This view
allows for querying the table like so to get all service access permissions for a user:
SELECT * from user_access;
Note: Actual queries from MySQL-enabled applications would select only particular fields that were necessary to process access checks.
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';
Connecting from a different host
Reset Hosts Value
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 mysql.user set Host = '192.168.2.25' WHERE User = 'my_test_db_usr' AND Host = '192.168.1.105';
UPDATE mysql.db set Host = '192.168.2.25' WHERE User = 'my_test_db_usr' AND Host = '192.168.1.105';
FLUSH PRIVILEGES;
Create replacement account
Another approach which phpMyAdmin uses is to just create a new account to replace the old. To do that, we'll need to replicate the settings of the existing account, only specifying a new host in place of the old. Once we're done, we'll delete the old account.
Get existing encrypted/hashed password
SELECT Host, User, Password from mysql.user WHERE User LIKE '%USER_ACCOUNT%' AND Host = 'OLD_HOST';
example results here
Create new account using old password
Here we'll use the literal encrypted/hashed password value taken directly from the mysql.user
table to create the new account:
# Create the replacement user with appropriate IP
CREATE USER 'USER_ACCOUNT'@'NEW_HOST' IDENTIFIED BY PASSWORD '*NOTREALPASSWORD';
Get privileges for old account
We get the privileges for the old account so we can replicate them on the new account:
SHOW GRANTS FOR 'USER_ACCOUNT'@'OLD_HOST'
example results here
Give the new account the same privileges as the old
Grant those privileges to the new account:
GRANT USAGE ON *.* TO 'USER_ACCOUNT'@'NEW_HOST' IDENTIFIED BY PASSWORD '*NOTREALPASSWORD';
GRANT SELECT, LOCK TABLES ON `DATABASE_NAME`.* TO 'USER_ACCOUNT'@'NEW_HOST';
example results here
Retire old account
Finally, once we confirm that we no longer need access from the old host, we delete the old account:
DROP USER 'USER_ACCOUNT'@'OLD_HOST';
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)
SELECT USER(),CURRENT_USER();
-
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
source my-sql-file.sql;
Rename table
RENAME TABLE `my_test_db`.`table_name` TO `my_test_db`.`my_new_table_name`;
Alter table
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;
Remove Unique Key/Index
ALTER TABLE `service_access` DROP INDEX `user_id_2`;
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'
);
Multiple rows
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 field
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
More Secure [20]
primarily because the command history is not saved
1 nano /home/me/reset_root_password.sql
2 # type the following:
3 # UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; exit;
4 # Save & quit nano
5 sudo /etc/init.d/mysql stop
6 mysqld_safe --init-file=/home/me/FILE &
7 rm /home/me/FILE
8 sudo /etc/init.d/mysql restart
Less secure [21]
command history IS saved
1 sudo /etc/init.d/mysql stop
2 sudo mysqld --skip-grant-tables --skip-networking &
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';
-
SHOW GRANTS;
- 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;
Safely/gracefully restart MySQL/MariaDB
This section is nearly verbatim from Michael - sqlbot from stackexchange.com
Note: if you are shutting down the server for an upgrade, then don't use this process; instead, follow the process detailed in What should be done before upgrading MySQL?.
Otherwise, if you're just restarting an otherwise-healthy server so that you can change a read-only global variable or something similar, here is a graceful path:
Enable innodb_fast_shutdown
Enable innodb_fast_shutdown
if it isn't already. This isn't directly related to the gracefulness of the shutdown, but it should bring your server back faster.
SHOW VARIABLES LIKE 'innodb_fast_shutdown';
If it's set to 0 as shown here:
+----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | innodb_fast_shutdown | 0 | +----------------------+-------+ 1 row in set (0.00 sec)
then run this query to enable it:
mysql> SET GLOBAL innodb_fast_shutdown = 1;
Query OK, 0 rows affected (0.01 sec)
Close all open tables
Next, instruct the server to close all open tables as soon as no currently-running queries are referencing them. This step also has nothing to do with the graceful shutdown, but it will make the subsequent step go faster:
mysql> FLUSH LOCAL TABLES;
Query OK, 0 rows affected (41.12 sec)
The FLUSH TABLES
statement (with the optional LOCAL
keyword, which avoids an unnecessary but otherwise harmless flush of any slaves) will block and your prompt won't return until all of the tables can be closed. Once each table has been "flushed" (closed), if a query subsequently references the table, it will be automatically reopened, but that's okay. What we're accomplishing with this step is making less work for the final step.
Lock tables read-only
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (13.74 sec) mysql>
This statement flushes all tables (hence the advantage of getting some of that out of the way less disruptively with the prior step) and acquires a global (server-wide) read-only lock on them.
You can't have a global read lock until every currently running "write" query (i.e., pretty much everything but SELECT
) is done. Issuing the lock request will allow existing queries to finish but won't allow new ones to start.
Your prompt doesn't return until you hold this global lock, so every query that is in progress when you request the lock is able to finish, and you know they're finished, because you get the prompt back. Any subsequent queries that try to write anything to any table will just stall, changing no data, waiting indefinitely for the lock, until...
- you change your mind about the restart and release the lock manually (
UNLOCK TABLES;
) - you restart the server, or
- you accidentally or intentionally disconnect the command line client from this thread (so don't do that). Keep this window connected and sitting at the mysql prompt:
Resist the temptation to close the mysql>
prompt
This idle console prompt is what's holding the global lock for you. Lose this, lose the lock.
From another console window, restart MySQL the way you normally would, either with initscripts or with mysqladmin shutdown
followed by a manual restart.
Restoring databases
Restoring views
When creating a view
the security model is one of Definer
or Invoker
. The problem is that this some_user@localhost
will always be hard-coded to the user account that was used to create the view in the original DB and NOT the user that you've used to export or import the database. And later, during the import, this user will be used to re-create the view.
So you can export/import as root, but if the original DB is running under another user and it has no CREATE VIEW
rights in the new database, the import will fail. In short, you will need to search/replace within the SQL file you're importing or you will need to create the applicable user account prior to the import attempt.
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.
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 |
As mentioned on the Using Option Files [29] reference page running mysql
with the --help
option will show you the preconfigured locations that MySQL will search for the options file.
Example:
$ 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
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.
- Exception: For
Path variables
-
~
represents the current user's home directory (the value of $HOME). -
SYSCONFDIR
represents the directory specified with theSYSCONFDIR
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-specificmy.cnf
file resides. IfMYSQL_HOME
is not set and you start the server using themysqld_safe
program,mysqld_safe
attempts to setMYSQL_HOME
as follows:- Let
BASEDIR
andDATADIR
represent the path names of the MySQL base directory and data directory, respectively. - If there is a
my.cnf
file inDATADIR
but not inBASEDIR
,mysqld_safe
setsMYSQL_HOME
toDATADIR
. - Otherwise, if
MYSQL_HOME
is not set and there is nomy.cnf
file inDATADIR
,mysqld_safe
setsMYSQL_HOME
toBASEDIR
.
- Let
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
- ↑ 6.3.1 User Names and Passwords
- ↑ 9.2 Schema Object Names
- ↑ How To Secure MySQL and MariaDB Databases in a Linux VPS
- ↑ MariaDB Knowledge Base > mysql_secure_installation
- ↑ Learning SQL by Guy Vaccaro>
- ↑ 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
- ↑ How to determine the size of MySQL databases and tables
- ↑ MySQL 5.5 Reference Manual :: 13 SQL Statement Syntax :: 13.1 Data Definition Statements :: 13.1.20 CREATE VIEW Syntax
- ↑ MySQL, 4th Edition by Paul Dubois
- ↑ MySql: Give Root User Logon Permission From Any Host
- ↑ Re-assign host access permission to MySQL user
- ↑ MySQL/MariaDB Server: Bind To Multiple IP Address - Add/update mysql sever user and permissions settings
- ↑ 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
- ↑ SQL UNIQUE Constraint
- ↑ 19.0 19.1 WikiBooks - MySQL CheatSheet
- ↑ MySQL :: MySQL 5.5 Reference Manual :: C.5.4.1 How to Reset the Root Password
- ↑ Set / Change / Reset the MySQL root password on Ubuntu Linux
- ↑ 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?
- ↑ Is there a graceful or safe restart for mysql like for apache httpd?>
- ↑ MySQL 5.5 Reference Manual :: 5 MySQL Server Administration :: 5.1 The MySQL Server :: 5.1.12 The Shutdown Process
- ↑ Can MySQL reliably restore backups that contain views or not?
- ↑ MySQL 5.5 Reference Manual :: 4 MySQL Programs :: 4.2 Using MySQL Programs :: 4.2.6 Using Option Files