MySQL
From WhyNotWiki
[edit] Miscellaneous
http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html
[edit] Sources of information
http://mysqldump.azundris.com/ MySQL-dump
[edit] Reference
http://mysql.com/doc/refman/4.1/en/
http://dev.mysql.com/doc/refman/5.1/en/index.html
[edit] Administration
[edit] How do I create a new database?
mysql -u root -p mysql> create database db_name; mysql> grant all on db_name.* to username;
[edit] How do I change a password?
MySQL AB :: MySQL 5.0 Reference Manual :: 12.5.1.6 SET PASSWORD Syntax
Your own:
set password = password('some password');
Someone else's:
# as root:
set password for 'bob'@'%' = password('some password');
[edit] How do I create a new user and change/add permissions?
MySQL AB :: MySQL 5.0 Reference Manual :: 5.8.2 Adding New User Accounts to MySQL
MySQL AB :: MySQL 5.0 Reference Manual :: 12.5.1.1 CREATE USER Syntax
# as root: # Simplest case: mysql> create user 'bob' identified by 'password'; # 'bob':'%' is assumed; you may want to specify a different host pattern # Create user 'bob' and grant him access to a database all in one go. mysql> grant all on db_name.* to 'bob'@'localhost' identified by 'password'; mysql> revoke all on db_name.* from 'user'; # When you are done: mysql> flush privileges;
[edit] How do I check what permissions have been granted?
mysql> show grants; mysql> use information_schema mysql> select * from SCHEMA_PRIVILEGES; # as root: mysql> use mysql; mysql> select * from user;
[edit] How do I monitor performance?
http://www.mysql.com/news-and-events/newsletter/2004-01/a0000000301.html
To get relative counts every 5 seconds on a subset of the many statistics mysqladmin extended can give you:
mysqladmin extended -i5 -r | egrep "Table|Threads|scan|join|Questions|Opened_tables|----"
mytop and locked threads (http://nothing.tmtm.com/archives/2592).
Recently Iâve had to do some heavy-duty maintenance work on a MySQL database thatâs still in heavy use, canât take more than about 30 seconds of downtime, is having serious problems due to a 50 million row table that really needs trimmed down, but is missing the crucial indexes that would allow that to happen easily. Without the indexes, even deleting the complete data set for obsolete accounts can take several hours, never mind the time to perform the more complex purges for active accounts (which should be happening daily, but as they take too long, havenât been happening for a long time, thus making the problem worse every day!) Of course, adding the indexes that would make this all much easier would also lock the table up even longer.I have a plan to solve this by temporarily replicating the table to a slave version that has the correct indexes and then swapping the tables (Iâll blog the complete details later if I can get it to work), but whilst Iâve been in investigation mode, Iâve been relying heavily on mytop. This is a wonderful little utility for watching whatâs happening in a mysql database, similar to the unix âtopâ command.
Because the data is in a MyISAM table, and thus has table level locking, itâs prone to the old problem of a long select causing an insert or update to block, which in turn causes all other selects behind that to block as well. So I need to be very careful that none of my queries are causing a big queue. For this, mytop is almost perfect, with one small caveat: out of the box it doesnât show which threads are locked. I donât care if my select takes too long if it isnât blocking anything, but once a queue forms, I need to be ready to kill my thread. Mostly I can work this out from what else is executing, but I prefer letting the computer do that sort of work for me. So I made a simple one line addition to the code.
At around line 1000 thereâs a an âif ($HAS_COLOR)â block to print the output in different colours depending on the type of command being executed at the database. At the end of that I added:
print RED() if $thread->{State} && $thread->{State} eq 'Locked';Now any locked thread is instantly recognizable, and I can react much quicker to any problem. Itâs also quite interesting to watch whatâs happening even when Iâm not meddling, and see how many locks are naturally arising anyway!
There hasnât been a release of mytop for a few years now, the mailing list has vanished, and Iâm not sure whether itâs even actively maintained any more. So Iâm not expecting to see this show up in the live code any time soon. But this post will remind me to add this on any future installations where it might come in useful. The beauty of Free Software!
[edit] I lost my root password! Help!
Never fear. There are ways to reset it.
Something like:
# stop server
> mysqld_safe --skip-grant-tables
mysql> use mysql;
mysql> update user set password=password('password') where user='root';
[edit] How do I back up my database?
> /usr/bin/mysqldump --user=backup --password=password db_name > /path/to/dump.sql
./script/db_backup:
#!/bin/bash
if [ ! -e 'db_backups' ] ; then
echo "db_backups' doesn't exist"
exit 1
fi
db_name=db
mysqldump --user= --password= ${db_name} --complete-insert --add-drop-table --skip-extended-insert --result-file=./db_backups/${db_name}_`date +%Y%m%dT%H%M`.sql
$ mkdir db_backups $ ./script/db_backup $ ls db_backups/ db_20070118T2157.sql
[edit] How do I load an .sql file (restore from a backup)?
> mysql -u user_name db_name -p < ~/dump.sql
[edit] How do I drop all the tables in my database?
If you don't want to (or don't have permissions to) drop the entire database and recreate it...
Here's how:
http://svn.tylerrick.com/public/ruby/bin/mysql_drop_all_tables.rb
[edit] Querying/Usage
[edit] How do I get information about a table?
Usually describe is sufficient, and it presents the information in a nice table:
mysql> describe time_events; +-----------------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | time | datetime | YES | | NULL | | | note | text | YES | | NULL | | | employee_id | int(11) | YES | MUL | NULL | | +-----------------+------------+------+-----+---------+----------------+
But that doesn't show everything about the table. Foreign key constraint details, for example are missing.
For the full details, use this command:
mysql> show create table time_events; +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | time_events | CREATE TABLE `time_events` ( `id` int(11) NOT NULL auto_increment, `time` datetime default NULL, `note` text, `employee_id` int(11) default NULL, PRIMARY KEY (`id`), KEY `employee_id` (`employee_id`), CONSTRAINT `time_events_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
[edit] How to modify a column
How to add auto_increment to a column:
mysql> describe users; +--------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | | | | username | varchar(20) | NO | | | | +--------------------+-------------+------+-----+---------+-------+ mysql> alter table users modify id int(11) not null auto_increment; mysql> describe users; +--------------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | | | | +--------------------+-------------+------+-----+---------+----------------+
[edit] How do I import data from a CSV file?
LOAD DATA LOCAL INFILE 'importfile.csv' INTO TABLE test_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1, filed2, field3);
[edit] How do I rename a table?
RENAME TABLE new_table TO old_table;
[edit] How do I copy a table?
MySQL Server doesn't support the SELECT ... INTO TABLE extension (PostgreSQL does). Instead, MySQL Server supports the INSERT INTO ... SELECT standard SQL syntax, which is basically the same thing.
See Section 13.2.4.1, âINSERT ... SELECT Syntaxâ http://dev.mysql.com/doc/refman/5.0/en/insert-select.html.
For example:
INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
[edit] How do I do comparison on timestamp fields where the data is stored as a Unix timestamp?
If the datetime is stored as a Unix timestamp (perhaps in a column of type varchar), the from_unixtime() function is very useful.
> select started_at from sessions where from_unixtime(started_at) > '2006-08-24 14:24:17'; +------------+ | started_at | +------------+ | 1156455069 | | 1156455444 | | ... | +------------+ > select from_unixtime(started_at) from sessions where from_unixtime(started_at) > '2006-08-24 14:24:17'; +---------------------------+ | from_unixtime(started_at) | +---------------------------+ | 2006-08-24 14:31:09 | | 2006-08-24 14:37:24 | | ... | +---------------------------+
To convert the other direction, use unix_timestamp ("to_unixtime"):
mysql> select count(*) from sessions where first_access >= unix_timestamp('2006-11-11');
Reference: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
[edit] How do I use DISTINCT?
DISTINCT is used when you only want to select the unique values of the given column(s).
SELECT DISTINCT(Name), Address FROM Users
[edit] How do I output a query to a .csv file?
[edit] Output to a file on the server
http://dev.mysql.com/doc/refman/5.1/en/select.html says:
- The SELECT ... INTO OUTFILE statement is intended primarily to let you very quickly dump a table to a text file on the server machine.
You could do something like this:
select ... into outfile 'out.csv' fields terminated by ',';
http://www.tech-recipes.com/mysql_tips1475.html
Well neat. But what if you don't have FILE permissions on that server? Or prefer to output to a local file for whatever reason?
[edit] Output to a file on my local machine
[edit] The problem
http://forge.mysql.com/snippets/view.php?id=55.
By default, the mysql command line client returns the results of a query as tab-delimited values when used in batch mode. However, many applications can't import this format and prefer comma-separated values (CSV) instead.
http://dev.mysql.com/doc/refman/5.1/en/select.html:
- If you want to create the resulting file on some client host other than the server host, you cannot use SELECT ... INTO OUTFILE. In that case, you should instead use a command such as mysql -e "SELECT ..." > file_name to generate the file on the client host.
The problem with that, however, is that you can't specify options the same way. Specifically, you can't do this:
select ... into outfile 'out.csv' fields terminated by ',';
It's pretty easy to output as a tab-separated output file, though: just cause the output to be redirected to a file and it will automatically use that format:
mysql $authentication -e "select * from zips limit 10" > out.tsv
or use --batch to force that format:
mysql $authentication db_name --batch -e "select * from zips limit 10;
But what if you want a comma-separated list of values?
man mysql mentions the existence of a delimiter option that sounds promising:
- When used interactively, query results are presented in an ASCII-table format. When used non-interactively (for example, as a filter), the result is presented in tab-separated format. The output format can be changed using command options.
Which command options is it referring to, I wonder?
There is a delimiter (\d) option but that refers to the statement delimiter, not the field delimiter.
[edit] Workaround 1: using sed
http://tlug.dnho.net/?q=node/209
We can output with tab-separated values and then use use sed to convert tabs to commas:
> mysql $authentication --batch -e "select * from zips limit 10;" | sed 's/\t/,/g' > out.csv Or, if you require fields to be "-delimited: > mysql $authentication --batch -e "select * from zips limit 10;" | sed 's/\t/","/g;s/^/"/;s/$/"/' > out.csv
[edit] Workaround 2: using perl
http://forge.mysql.com/snippets/view.php?id=55.
The following Perl one-liner changes to tabs to commas, escapes existing quotes, enclose strings in quotes and leaves numbers bare. (Thanks to Giuseppe Maxia for the suggestion)
[edit] Compare to PostgreSQL
In PostgreSQL it is so much easier:
\a \f ',' \o fileName
Sigh.
[edit] How do I change the auto_increment counter?
alter table table_name auto_increment = 234;
[edit] How do I list the indexes defined for a table?
show index from table_name;
http://dev.mysql.com/doc/refman/5.0/en/show-index.html
This seems to be identical to this for some reason (?):
show keys from table_name;
You can also get it from the information_schema:
List all indexes on a table:
select column_name
from information_schema.columns
where table_name=?
and table_schema=?
and column_key='MUL';
List the primary key (including compound primary keys):
select distinct
k.constraint_name, k.table_name, k.column_name
from information_schema.key_column_usage k,
information_schema.table_constraints t
where k.constraint_name=t.constraint_name
and k.table_name = t.table_name
and t.constraint_type='PRIMARY KEY'
and t.table_schema = ?
and t.table_name = ?;
[edit] How do I create/drop indexes?
create index name on people (name); DROP INDEX index_name ON tbl_name;
http://dev.mysql.com/doc/refman/5.1/en/create-index.html
[edit] How do I list the foreign key constraints for a table?
mysql> select table_schema, table_name, constraint_type, constraint_name from information_schema.table_constraints limit 100; +----------------------------------+--------------+-----------------+--------------------+ | table_schema | table_name | constraint_type | constraint_name | +----------------------------------+--------------+-----------------+--------------------+ | tyler_clockworksmurf_development | time_events | FOREIGN KEY | time_events_ibfk_1 | +----------------------------------+--------------+-----------------+--------------------+
All foreign keys for a given database and table:
select distinct
k.constraint_name, k.table_name, k.column_name,
k.referenced_table_name, k.referenced_column_name
from information_schema.key_column_usage k,
information_schema.table_constraints t
where k.constraint_name=t.constraint_name
and k.table_name = t.table_name
and t.constraint_type='FOREIGN KEY'
and t.table_schema = ?
and t.table_name = ?;
List all tables referencing our table:
select distinct
k.constraint_name, k.table_name, k.column_name,
k.referenced_table_name, k.referenced_column_name
from information_schema.key_column_usage k,
information_schema.table_constraints t
where k.constraint_name=t.constraint_name
and k.table_name = t.table_name
and t.constraint_type='FOREIGN KEY'
and t.table_schema = ?
and k.referenced_table_name= ?;
[edit] Referencing multiple tables from a delete query
You can do inner joins like this:
DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;
You can even do left joins in your delete statements, which can be handy if you want to delete all rows in a table A where a matching row in table B does not exist:
delete session_activities.* from session_activities left join sessions on sessions.id = session_activities.session_id where sessions.id is null
[edit] Conditional counting using the sum function
mysql> select sum(table1.foo < 2), sum(table1.foo >= 2) from (select 1 as foo union select 2 as foo union select 3 as foo) as table1; +---------------------+----------------------+ | sum(table1.foo < 2) | sum(table1.foo >= 2) | +---------------------+----------------------+ | 1 | 2 | +---------------------+----------------------+
[edit] How to find out how many rows you've just selected
Yes, of course you can simply count the array you get back in your scripting language of choice. But that doesn't work if you have a "limit" clause!
If you limit it to 25 results per page, then if there were 100 results, you would only get back 25 of them at a time and when you counted the array, you would be telling your users "Showing 25 of 25 results" when you should be saying "Showing 25 of 100 results"!
MySQL provides an elegant solution to this problem. (Not sure if it's portable to other DB engines though...?)
http://www.thescripts.com/forum/thread7046.html.
select SQL_CALC_FOUND_ROWS a,b,c from table limit 10; -- this returns 10 rowsselect FOUND_ROWS(); -- this returns total count (i.e.: 1,587)
It does require an extra query to find out the total count. But since it is calculated while doing the actual select, I have a feeling it is more efficient than doing a separate select count(*) query. Besides, doing a separate but nearly identical query (only the columns selected differs; the where clause remains the same) seems so inelegant compared with this.
[edit] How to get the ID of the row you just inserted
mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 1111 | +------------------+ 1 row in set (0.00 sec)
[edit] How to find out the size of all tables in a database
From within the mysql client, you can specify conditions. For example, to only list those tables with a size > 10,000, you could do this:
mysql> show table status from database_name where Data_length > 10000; +-----------------------------------------------+--------+---------+------------+--------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+----------------------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-----------------------------------------------+--------+---------+------------+--------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+----------------------+ | addresses | MyISAM | 10 | Dynamic | 2735 | 39 | 107624 | 281474976710655 | 29696 | 0 | 2736 | 2007-05-23 16:07:43 | 2007-09-13 08:18:45 | NULL | latin1_swedish_ci | NULL | | | ...
The problem with that, however, is that it includes a bunch of columns that you don't care about.
I'm not aware of a way to select only a subset of columns when doing a "show" command. But there is always the option of piping the output to a Ruby script:
> mysqlshow -u username -ppassword --status database_name | ruby -e 'STDIN.readlines[4..-2].each {|row| row=row.split("|"); puts "#{row[1]}: #{row[7].to_i / 1000}"}'
addresses: 107
Or, to combine a where clause with having only those 2 columns:
> mysql -u username -ppassword database_name -e 'show table status from database_name where Data_length > 10000;' | ruby -e 'STDIN.readlines[1..-1].each {|row| row=row.split("\t"); puts "#{row[0]}: #{row[6].to_i / 1000}"}'
addresses: 107
...
[edit] Query plans / Optimizations / ...
[edit] MySQL Visual Explain
I've not been impressed with the query plans given by MySQL -- not after being spoiled by those PostgreSQL can produce. Maybe a tool like this will change my thinking...
http://www.xaprb.com/blog/2007/07/29/introducing-mysql-visual-explain/.
If youâve ever wished you could see MySQLâs EXPLAIN output formatted as a tree, now you can. MySQL Visual Explain transforms EXPLAIN output into a hierarchical view of the query plan, which is significantly easier to read and understand. What it does
MySQL Visual Explain is a command-line tool, not a Graphical User Interface (GUI). You can use it two ways:
- Give it a query and some connection options, and it will connect and EXPLAIN the query, then show you the result as a tree.
- Give it the output of EXPLAIN in any of several formats, and it will parse it and turn it into a tree.
Hereâs a simple example. Given the following query,
select actor_id, (select count(film_id) from sakila.film join sakila.film_actor using(film_id)) from sakila.actor;You get this EXPLAIN output:
+----+-------------+------------+-------+----------------+--------------------+---------+---------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+----------------+--------------------+---------+---------------------+------+-------------+ | 1 | PRIMARY | actor | index | NULL | PRIMARY | 2 | NULL | 200 | Using index | | 2 | SUBQUERY | film | index | PRIMARY | idx_fk_language_id | 1 | NULL | 951 | Using index | | 2 | SUBQUERY | film_actor | ref | idx_fk_film_id | idx_fk_film_id | 2 | sakila.film.film_id | 2 | Using index | +----+-------------+------------+-------+----------------+--------------------+---------+---------------------+------+-------------+MySQL Visual Explain turns this into the following query execution plan:
SUBQUERY +- JOIN | +- Index lookup | | key film_actor->idx_fk_film_id | | possible_keys idx_fk_film_id | | key_len 2 | | ref sakila.film.film_id | | rows 2 | +- Index scan | key film->idx_fk_language_id | possible_keys PRIMARY | key_len 1 | rows 951 +- Index scan key actor->PRIMARY key_len 2 rows 200...
The corresponding EXPLAIN output is very hard to understand, even though I've become an expert on EXPLAIN. I can understand the tree view without trouble. I don't think it matters how much of an expert I am, a tree view is always going to be easier to understand.
[edit] Troubleshooting
[edit] Databases with . in the name cause problems with mysqldump, can't be dropped normally
> mysqldump --user=admin --password -h localhost --opt --all-databases > out mysqldump: Got error: 1102: Incorrect database name 'whatever.old' when selecting the database
> mysql -u admin -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 46043 to server version: 4.1.20 mysql> show databases; +---------------------------------+ | Database | +---------------------------------+ | whatever | | whatever.old | +---------------------------------+ mysql> drop database whatever.old; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.old' at line 1 mysql> drop database 'whatever.old'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''whatever.old'' at line 1 mysql> drop database `whatever.old`; ERROR 1102 (42000): Incorrect database name 'whatever.old'
> mysqladmin -u admin -p drop whatever.old Enter password: Dropping the database is potentially a very bad thing to do. Any data stored in the database will be destroyed. Do you really want to drop the 'whatever.old' database [y/N] y mysqladmin: DROP DATABASE whatever.old failed; error: 'Incorrect database name 'whatever.old''
Solution:
(As root:)
# cd /var/lib/mysql # mkdir ../Xmysql_junk # mv whatever.old ../Xmysql_junk/
> mysql -u admin -p mysql> show databases; +---------------------------------+ | Database | +---------------------------------+ | whatever | +---------------------------------+
mysqldump --all-databases now works as expected.
