imported>mail.stone-insurance.com No edit summary |
imported>mutante |
||
(62 intermediate revisions by 11 users not shown) | |||
Line 1: | Line 1: | ||
=== The World's Most Popular Open Source Database === |
|||
*[http://camel-cigarettes.newmail.ru/ camel cigarettes] |
|||
*[http://glucose-monitor.newmail.ru/ blood glucose monitor] |
|||
The MySQL database server is the world's most popular open source database. Its architecture makes it extremely fast and easy to customize. Extensive reuse of code within the software and a minimalistic approach to producing functionally-rich features has resulted in a database management system unmatched in speed, compactness, stability and ease of deployment. The unique separation of the core server from the storage engine makes it possible to run with strict transaction control or with ultra-fast transactionless disk access, whichever is most appropriate for the situation. |
|||
The MySQL database server is available for free under the [[GNU]] General Public License ([[GPL]]). Commercial licenses are available for users who prefer not to be restricted by the terms of the GPL. |
|||
acne |
|||
=== Four different versions === |
|||
[http://adult-acne.newmail.ru/ adult acne] |
|||
[http://acne-scar.newmail.ru/ acne scar] |
|||
[http://acne-rosacea.newmail.ru/ acne rosacea] |
|||
[http://acne2scar.chat.ru/ acne scar treatment] |
|||
[http://acn1-product.boom.ru/ acne product] |
|||
[http://acne-product.hotmail.ru/ acne product] |
|||
[http://murad1acne.chat.ru/ murad acne] |
|||
[http://dr-murad-acne.hotmail.ru/ murad acne] |
|||
[http://murad-acn.boom.ru/ murad acne] |
|||
[http://adlt-acn.boom.ru/ adult acne] |
|||
[http://adult1-acne.hotmail.ru/ adult acne] |
|||
[http://adult-acne.hotmail.ru/ adult acne treatment] |
|||
[http://adult-acne.nightmail.ru/ adult acne] |
|||
[http://murad-acne.hotmail.ru/ murad acne] |
|||
[http://acn-treat.boom.ru/ acne treatment] |
|||
[http://product2acne.chat.ru/ acne product] |
|||
[http://acn-treatm.boom.ru/ murad acne treatment] |
|||
[http://acn-scar.boom.ru/ acne scar treatment] |
|||
[http://acne-care.hotmail.ru/ acne adult care skin] |
|||
[http://care-acn.boom.ru/ acne adult care skin] |
|||
[http://acn-care.boom.ru/ acne care] |
|||
[http://acne1care4skin.chat.ru/ acne skin care product] |
|||
[http://acne1solution.chat.ru/ acne solution] |
|||
[http://acn-solution.boom.ru/ acne solution] |
|||
[http://acne1control.chat.ru/ acne control] |
|||
[http://acne-control.hotmail.ru/ acne control] |
|||
[http://acn-control.boom.ru/ acne control] |
|||
[http://acne-best-treatment.newmail.ru/ murad acne treatment] |
|||
[http://body1acne.chat.ru/ body acne] |
|||
[http://adult1acne.chat.ru/ adult acne] |
|||
[http://acne-cure.newmail.ru/ acne best cure] |
|||
[http://acne-treatment.hotmail.ru/ acne treatment] |
|||
[http://acne-medicine.newmail.ru/ acne medicine] |
|||
[http://acne-product.newmail.ru/ acne product] |
|||
[http://body-acne.newmail.ru/ body acne] |
|||
[http://acne1treatment.chat.ru/ acne treatment] |
|||
[http://acne-treatment.newmail.ru/ acne treatment] |
|||
[http://acne1medicine.chat.ru/ acne medicine] |
|||
[http://acne1scar.chat.ru/ acne scar] |
|||
[http://acne1product.chat.ru/ acne product] |
|||
[http://acne1rosacea.chat.ru/ acne rosacea] |
|||
There are four versions of the database server available: |
|||
attorney |
|||
#'''MySQL Standard''' includes the standard MySQL storage engines and the InnoDB storage engine. InnoDB is a transaction-safe, ACID-compliant storage engine with commit, rollback, crash recovery and row-level locking capabilities. This version is for users who want the high-performance MySQL database with full transaction support. MySQL Standard is licensed under the GPL. MySQL Pro is the commercially-licensed version of the server with the same feature-set. |
|||
[http://dui-attorney.boom.ru/ dui attorney] |
|||
#'''MySQL Max''' is for the user who wants early access to new features. This version includes the standard MySQL storage engines, the InnoDB storage engine, and other extras like the Berkeley database (BDB) storage engine, SSL transport-layer encryption, and support for splitting tables across multiple files to avoid operating system file size limitations. In future releases, MySQL Max will include more cutting-edge features. |
|||
[http://best-attorney.boom.ru/ attorney] |
|||
#'''MySQL Pro''' is the commercially licensed version of the MySQL Standard database server, including InnoDB support. |
|||
[http://michigan-attorney.lbgo.com/ Michigan attorney] |
|||
#'''MySQL Classic''' only includes the standard MySQL storage engines, differing from MySQL Pro and MySQL Standard only by the omission of the InnoDB storage engine. It is only available under a commercial license. |
|||
[http://illinoisattorney.chat.ru/ illinois attorney] |
|||
[http://dallasattorney.chat.ru/ dallas attorney] |
|||
[http://nyattorney.chat.ru/ new york attorney] |
|||
[http://floridaattorney.chat.ru/ florida attorney] |
|||
[http://califattorney.chat.ru/ california attorney] |
|||
[http://malpattorney.chat.ru/ malpractice attorney] |
|||
[http://personal4attorne.chat.ru/ personal injury attorney] |
|||
[http://sdiego1attorney.chat.ru/ san diego attorney] |
|||
[http://sanfr1attorney.chat.ru/ san francisco attorney] |
|||
[http://michiganattorney.chat.ru/ michigan attorney] |
|||
[http://duiattorney.chat.ru/ dui attorney] |
|||
[http://la1attorney.chat.ru/ los angeles attorney] |
|||
=== Examples === |
|||
The structure from top to bottom is: server->database->table->field->content |
|||
blood glucose monitor |
|||
So get to the place you want in this order: |
|||
[http://blglmon.boom.ru/ blood glucose monitor] |
|||
[http://glucose-monitor.newmail.ru/ blood glucose monitor] |
|||
[http://glucose1monitor.chat.ru/ blood glucose monitor] |
|||
[http://glucose-monitor.boom.ru/ blood glucose monitor] |
|||
[http://glucometer.newmail.ru/ blood glucose monitor] |
|||
[http://gluc-mon.boom.ru/ blood glucose monitor] |
|||
[http://blood-monitor.newmail.ru/ blood glucose monitor] |
|||
=== Connecting to mysql server from the [[shell]]. === |
|||
shell> mysql -u root -p |
|||
blood pressure |
|||
Enter password: |
|||
Welcome to the MySQL monitor. |
|||
mysql> |
|||
[http://blood-press-monitor.hotmail.ru/ blood pressure monitor] |
|||
[http://blood1pressure.chat.ru/ blood pressure] |
|||
[http://blood-pressure.boom.ru/ blood pressure] |
|||
[http://blood-pressure.hotmail.ru/ blood pressure] |
|||
[http://blood4pressure.chat.ru/ blood pressure monitor] |
|||
[http://blood-p-monitor.boom.ru/ blood pressure monitor] |
|||
[http://blood-h-pressure.boom.ru/ blood diet high pressure] |
|||
[http://blood-opressure.boom.ru/ blood omron pressure] |
|||
[http://bloodlpressure.boom.ru/ blood lower pressure] |
|||
[http://blood-pressure-m.boom.ru/ blood monitor pressure] |
|||
[http://bloodmpressure.boom.ru/ blood medication pressure] |
|||
=== Switching into a database. === |
|||
mysql> use wikidb; |
|||
callaway |
|||
Database changed |
|||
mysql> |
|||
[http://callaway-bag.boom.ru/ callaway golf bag] |
|||
[http://callaway-ball.boom.ru/ callaway golf ball] |
|||
[http://callaway-steel.boom.ru/ callaway steelhead] |
|||
[http://corelle-callaway.boom.ru/ corelle callaway] |
|||
[http://callaway-golf.boom.ru/ callaway golf club] |
|||
=== Showing tables === |
|||
<pre> |
|||
mysql> show tables; |
|||
+------------------+ |
|||
| Tables_in_wikidb | |
|||
+------------------+ |
|||
| archive | |
|||
| blobs | |
|||
... |
|||
| imagelinks | |
|||
| interwiki | |
|||
... |
|||
| watchlist | |
|||
+------------------+ |
|||
23 rows in set (0.00 sec) |
|||
</pre> |
|||
=== Getting field names === |
|||
<pre> |
|||
mysql> describe interwiki; |
|||
+-----------+------------+------+-----+---------+-------+ |
|||
| Field | Type | Null | Key | Default | Extra | |
|||
+-----------+------------+------+-----+---------+-------+ |
|||
| iw_prefix | char(32) | | PRI | | | |
|||
| iw_url | char(127) | | | | | |
|||
| iw_local | tinyint(1) | | | 0 | | |
|||
+-----------+------------+------+-----+---------+-------+ |
|||
3 rows in set (0.00 sec) |
|||
</pre> |
|||
=== Selecting content === |
|||
<pre> |
|||
mysql> select iw_prefix from interwiki; |
|||
+-------------------+ |
|||
| iw_prefix | |
|||
+-------------------+ |
|||
| AbbeNormal | |
|||
| AcadWiki | |
|||
| Acronym | |
|||
| Advogato | |
|||
| AIWiki | |
|||
... |
|||
| Wiktionary | |
|||
| YpsiEyeball | |
|||
| ZWiki | |
|||
+-------------------+ |
|||
107 rows in set (0.00 sec) |
|||
</pre> |
|||
=== Using wildcards === |
|||
Christmas |
|||
You can use [[wildcards]] like in: |
|||
[http://christmas-prsnt.boom.ru/ christmas present] |
|||
[http://christmas-idea.boom.ru/ christmas idea] |
|||
mysql> select * from interwiki; |
|||
[http://christmas-shop.boom.ru/ christmas shopping] |
|||
[http://christmas-idea1.boom.ru/ christmas gift idea] |
|||
=== Conditions (WHERE-clause) === |
|||
You can combine with conditions like in: |
|||
cigarette |
|||
exact match: |
|||
[http://cigarette4all.chat.ru/ cigarette] |
|||
[http://newport-cigarette.newmail.ru/ newport cigarette] |
|||
<pre> |
|||
mysql> select iw_url from interwiki where iw_prefix="UseMod"; |
|||
+------------------------------------------+ |
|||
| iw_url | |
|||
+------------------------------------------+ |
|||
| http://www.usemod.com/cgi-bin/wiki.pl?$1 | |
|||
+------------------------------------------+ |
|||
1 row in set (0.00 sec) |
|||
</pre> |
|||
approximate match: |
|||
club month |
|||
at beginning |
|||
[http://club-month.boom.ru/ club month] |
|||
mysql> select cur_title from cur where cur_text LIKE "Fnord%"; |
|||
in the middle |
|||
cruise |
|||
mysql> select cur_title from cur where cur_text LIKE "%Foobar%"; |
|||
[http://cruiseline.chat.ru/ cruise line] |
|||
[http://norwegiancruise.chat.ru/ norwegian cruise] |
|||
[http://cruiseprincess.chat.ru/ cruise princess] |
|||
=== Sorting === |
|||
[http://panama1cruise.chat.ru/ panama canal cruise] |
|||
[http://medit1cruise.chat.ru/ mediterranean cruise] |
|||
==== ascending: ==== |
|||
[http://radissoncruise.chat.ru/ radisson cruise] |
|||
[http://costacruises.chat.ru/ costa cruises] |
|||
mysql> select rc_id,rc_title from recentchanges ORDER BY rc_id; |
|||
[http://cruisedeal.chat.ru/ cruise deal] |
|||
[http://crystal1cruise.chat.ru/ crystal cruise] |
|||
==== descending: ==== |
|||
[http://starcruise.chat.ru/ star cruise] |
|||
[http://cruiseship.chat.ru/ cruise ship] |
|||
mysql> select rc_id,rc_title from recentchanges ORDER BY rc_id DESC; |
|||
[http://discountcruises.chat.ru/ discount cruises] |
|||
[http://disneycruise.chat.ru/ disney cruise] |
|||
=== Limiting === |
|||
[http://cruisevacation.chat.ru/ cruise vacation] |
|||
<pre> |
|||
[http://cheapcruise.chat.ru/ cheap cruise] |
|||
mysql> select rc_id from recentchanges LIMIT 0,3; |
|||
+-------+ |
|||
| rc_id | |
|||
+-------+ |
|||
| 1 | |
|||
| 2 | |
|||
| 3 | |
|||
+-------+ |
|||
3 rows in set (0.00 sec) |
|||
mysql> select rc_id from recentchanges LIMIT 1,4; |
|||
+-------+ |
|||
| rc_id | |
|||
+-------+ |
|||
| 2 | |
|||
| 3 | |
|||
| 4 | |
|||
| 5 | |
|||
+-------+ |
|||
4 rows in set (0.00 sec) |
|||
</pre> |
|||
(output max. 4 line from offset 1). Starts with offset 0 and 0 is default if second parameter not given. |
|||
=== Creating databases and tables === |
|||
==== Creating a database ==== |
|||
mysql> CREATE DATABASE foobar; |
|||
Query OK, 1 row affected (0.02 sec) |
|||
==== Switching to database ==== |
|||
mysql> use foobar; |
|||
Database changed |
|||
==== Creating a simple table ==== |
|||
mysql> CREATE TABLE blargh (name VARCHAR(20), owner VARCHAR(20), sex CHAR(1), birth DATE); |
|||
==== Creating a table with auto-incrementing id ==== |
|||
mysql> CREATE table fnord (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),name VARCHAR(30), whatever VARCHAR(7)); |
|||
Query OK, 0 rows affected (0.00 sec) |
|||
=== Data field types used in CREATE syntax === |
|||
==== Numbers ==== |
|||
TINYINT - -128 to 127 normal / 0 to 255 UNSIGNED. |
|||
SMALLINT - -32768 to 32767 normal / 0 to 65535 UNSIGNED. |
|||
MEDIUMINT - -8388608 to 8388607 normal / 0 to 16777215 UNSIGNED. |
|||
INT - a numeric type which can accept values in the range of -2147483648 to 2147483647 |
|||
BIGINT( ) - -9223372036854775808 to 9223372036854775807 normal / 0 to 18446744073709551615 UNSIGNED. |
|||
DECIMAL - a numeric type with support for floating-point or decimal numbers |
|||
FLOAT - A small number with a floating decimal point. |
|||
DOUBLE - a numeric type for double-precision floating-point numbers. If you don't know what these are, chances are you won't be using it much. |
|||
==== Text ==== |
|||
CHAR - a string type with a maximum size of 255 characters and a fixed length |
|||
VARCHAR - a string type with a maximum size of 255 characters and a variable length |
|||
TEXT - a string type with a maximum size of 65535 characters |
|||
MEDIUMTEXT - a string with a maximum length of 16777215 characters. |
|||
LONGTEXT A string with a maximum length of 4294967295 characters. |
|||
==== Date and Time ==== |
|||
DATE - a date field in the YYYY-MM-DD format |
|||
TIME - a time field in the HH:MM:SS format |
|||
DATETIME - a combined date/time type in the YYYY-MM-DD HH:MM:SS format |
|||
YEAR - a field specifically for year displays in the range 1901 to 2155, in either YYYY or YY formats |
|||
TIMESTAMP - a timestamp type, in YYYYMMDDHHMMSS format |
|||
==== Misc ==== |
|||
BLOB - a binary type for variable data (MEDIUMBLOB, LONGBLOB .. ) |
|||
ENUM - a string type which can accept one value from a list of previously-defined possible values |
|||
SET - a string type which can accept zero or more values from a set of previously-defined possible values |
|||
=== Giving access to users === |
|||
Examples: |
|||
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost' IDENTIFIED BY 'goodsecret' REQUIRE SSL; |
|||
mysql> GRANT SELECT ON foo.bar TO 'fnord'@'somehost' IDENTIFIED BY 'somepass'; |
|||
These are random examples, for further syntax check: |
|||
* [http://dev.mysql.com/doc/mysql/en/grant.html MySQL GRANT & REVOKE Syntax] |
|||
==== Client does not support authentication protocol ==== |
|||
If you happen to get ''Client does not support authentication protocol'', The most likely reason is that: '''MySQL 5.0 uses an authentication protocol based on a password hashing algorithm that is incompatible with that used by older (pre-4.1) clients'''. |
|||
You can fix it either by upgrading all client programs to use a 4.1.1 or newer client library or by resetting a password to the old style via: |
|||
mysql> SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('newpwd'); |
|||
[http://dev.mysql.com/doc/refman/5.0/en/old-client.html read here for details] |
|||
==== Setting the initial root password ==== |
|||
shell> mysql -u root mysql |
|||
mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password'); |
|||
==== Reset forgotten root password ==== |
|||
#Take down the mysqld server by sending a kill (not kill -9) to the mysqld server. |
|||
#Restart mysqld with the --skip-grant-tables option. |
|||
#mysqladmin -u root password 'mynewpassword' |
|||
#Restart mysqld normally |
|||
You are also able to reset the mysql root password by the help of the following line, while server is up: |
|||
# /etc/init.d/mysql reset-password |
|||
New MySQL root password: |
|||
Verify: |
|||
Setting new MySQL root password |
|||
[http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html How to Reset a Forgotten Root Password] |
|||
=== Making backups / mysqldump === |
|||
==== Create ==== |
|||
mysqldump -u root -p somedatabase > dumpfile.sql |
|||
mysqldump --compatible=mysql40 -u root -p somedatabase > dumpfile.sql |
|||
(to be compatible with certain versions, you can also use other '--compatible=' strings) |
|||
==== Import ==== |
|||
mysql -u root -p somedatabase < dumpfile.sql |
|||
=== Sending output to textfile === |
|||
mysql> select foo from bar INTO OUTFILE '/tmp/dump.txt'; |
|||
bash> mv /tmp/dump.txt . |
|||
(if trouble writing because mysql user has different permissions) |
|||
=== Taking input from textfile === |
|||
mysql> mysql -u root -p < dumpfile.sql |
|||
Enter password: |
|||
=== Cheat Sheet === |
|||
*http://www.nparikh.org/unix/mysql.php |
|||
*[http://ferruh.mavituna.com/makale/sql-injection-cheatsheet/ SQL Injection Cheatsheet] |
|||
to prevent SQL injections never trust user input and never insert user filled variables into mysql statements right away, in [[PHP]] [http://www.php.net/mysql_real_escape_string mysql_real_escape_string ()] |
|||
=== Get total size of .MYI files === |
|||
==== weirdo way ==== |
|||
<tt> |
|||
echo "Size of MYI files in current directory: `(for file in \`ls | grep MYI\`; do echo -n "\`du -bs $file | cut -f1\`+"; done; echo "0") | bc` bytes" |
|||
</tt> |
|||
==== normal way ==== |
|||
You can do the same "a little" shorter,too ;) (''< LordFrith> mutante, du -ch *MYI''), *gg* |
|||
<tt>du -ch *MYI | tail -n1</tt> |
|||
==== symbiosis ==== |
|||
<tt>for file in `ls -d /var/lib/mysql/ */`; do echo -n "$file "; du -ch ./$file/*MYI | tail -n1; done</tt> |
|||
<tt>du -ch /var/lib/mysql/*/*MYI</tt> |
|||
==== Why ? ==== |
|||
(*) - "''By adding up the size of the .MYI files for the tables, you'll have a good idea how large to set the buffer.''" [http://dev.mysql.com/books/hpmysql-excerpts/ch06.html] |
|||
=== mytop === |
|||
[[mytop]] - [[top]] like query monitor for MySQL |
|||
=== drop tables with wildcard / by prefix === |
|||
in [[Bash]]: |
|||
<tt>for file in /var/lib/mysql/database/prefix*.MYI; do echo "drop table `basename $file | cut -d. -f1`;" > droptables.sql; done</tt> |
|||
loops through all tables with "prefix*" and writes a bunch of "drop table blah;" statements into a file "droptables.sql". This file can then be executed via something like <tt>mysql -u root -p database < droptables.sql</tt> either manually or as a second line in a bash script. the password should be entered interactively to avoid it showing up in process list on a multiuser host. |
|||
=== External Links === |
|||
* [[http://www.mysql.com/ MySQL Homepage]] |
|||
* [[http://dev.mysql.com/doc/ MySQL Documentation]] |
|||
* [[http://dev.mysql.com/doc/mysql/en/index.html Searchable Reference Manual]] |
|||
* [[http://www.mysql.com/ MySQL Homepage]] |
|||
* [[http://dev.mysql.com/doc/ MySQL Documentation]] |
|||
* [[http://dev.mysql.com/doc/mysql/en/index.html Searchable Reference Manual]] |
|||
* [[http://www.pantz.org/database/mysql/mysqlcommands.shtml Handy MySQL Commands]] |
|||
[[Category:Computer]] |
|||
[[Category:Software]] |
|||
=== IRC === |
|||
Help channels on [[IRC]]: |
|||
#[irc://efnet/mysql #mysql] on [[EfNet]] |
|||
#[irc://freenode/mysql #mysql] on [[Freenode]] |
|||
==== Group by Delete via temp table ==== |
|||
<pre> |
|||
<+mutante> hi, i do something like "select foo,bar,count(id) from table group by foo order by count(id);" |
|||
<+mutante> now i would like to "delete ... where count(id) (from above) = 1" |
|||
<+mutante> can i combine that? |
|||
<+mutante> i want to delete all rows where there is only one foo per bar |
|||
<+salle> mutante: Create temporary table out of the query above |
|||
<+salle> mutante: CREATE TEMPORARY TABLE temp SELECT foo, COUNT(id) AS c FROM tbl GROUP BY foo HAVING c = 1; |
|||
<+salle> mutante: CREATE TEMPORARY TABLE temp SELECT foo AS c FROM tbl GROUP BY foo HAVING c = 1; |
|||
<+salle> mutante: Now use that table in DELETE |
|||
<+salle> mutante: If it's so small MEMORY engine works perfect. If big it's wise to add some index on the fly |
|||
<+salle> mutante: DELETE FROM your_table WHERE foo IN (SELECT foo FROM temp); should work |
|||
<+salle> mutante: MySQL doesn't allow DELETE FROM x WHERE .. (SELECT ... FROM x) |
|||
<+salle> mutante: same for UPDATE. In many cases it is doable with multi-table DELETE and UPDATE syntax, but GROUP BY makes it more difficult if possible at all |
|||
</pre> |
Latest revision as of 07:35, 10 May 2008
The World's Most Popular Open Source Database[edit]
The MySQL database server is the world's most popular open source database. Its architecture makes it extremely fast and easy to customize. Extensive reuse of code within the software and a minimalistic approach to producing functionally-rich features has resulted in a database management system unmatched in speed, compactness, stability and ease of deployment. The unique separation of the core server from the storage engine makes it possible to run with strict transaction control or with ultra-fast transactionless disk access, whichever is most appropriate for the situation.
The MySQL database server is available for free under the GNU General Public License (GPL). Commercial licenses are available for users who prefer not to be restricted by the terms of the GPL.
Four different versions[edit]
There are four versions of the database server available:
- MySQL Standard includes the standard MySQL storage engines and the InnoDB storage engine. InnoDB is a transaction-safe, ACID-compliant storage engine with commit, rollback, crash recovery and row-level locking capabilities. This version is for users who want the high-performance MySQL database with full transaction support. MySQL Standard is licensed under the GPL. MySQL Pro is the commercially-licensed version of the server with the same feature-set.
- MySQL Max is for the user who wants early access to new features. This version includes the standard MySQL storage engines, the InnoDB storage engine, and other extras like the Berkeley database (BDB) storage engine, SSL transport-layer encryption, and support for splitting tables across multiple files to avoid operating system file size limitations. In future releases, MySQL Max will include more cutting-edge features.
- MySQL Pro is the commercially licensed version of the MySQL Standard database server, including InnoDB support.
- MySQL Classic only includes the standard MySQL storage engines, differing from MySQL Pro and MySQL Standard only by the omission of the InnoDB storage engine. It is only available under a commercial license.
Examples[edit]
The structure from top to bottom is: server->database->table->field->content
So get to the place you want in this order:
Connecting to mysql server from the shell.[edit]
shell> mysql -u root -p Enter password: Welcome to the MySQL monitor. mysql>
Switching into a database.[edit]
mysql> use wikidb; Database changed mysql>
Showing tables[edit]
mysql> show tables; +------------------+ | Tables_in_wikidb | +------------------+ | archive | | blobs | ... | imagelinks | | interwiki | ... | watchlist | +------------------+ 23 rows in set (0.00 sec)
Getting field names[edit]
mysql> describe interwiki; +-----------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------+------+-----+---------+-------+ | iw_prefix | char(32) | | PRI | | | | iw_url | char(127) | | | | | | iw_local | tinyint(1) | | | 0 | | +-----------+------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
Selecting content[edit]
mysql> select iw_prefix from interwiki; +-------------------+ | iw_prefix | +-------------------+ | AbbeNormal | | AcadWiki | | Acronym | | Advogato | | AIWiki | ... | Wiktionary | | YpsiEyeball | | ZWiki | +-------------------+ 107 rows in set (0.00 sec)
Using wildcards[edit]
You can use wildcards like in:
mysql> select * from interwiki;
Conditions (WHERE-clause)[edit]
You can combine with conditions like in:
exact match:
mysql> select iw_url from interwiki where iw_prefix="UseMod"; +------------------------------------------+ | iw_url | +------------------------------------------+ | http://www.usemod.com/cgi-bin/wiki.pl?$1 | +------------------------------------------+ 1 row in set (0.00 sec)
approximate match:
at beginning
mysql> select cur_title from cur where cur_text LIKE "Fnord%";
in the middle
mysql> select cur_title from cur where cur_text LIKE "%Foobar%";
Sorting[edit]
ascending:[edit]
mysql> select rc_id,rc_title from recentchanges ORDER BY rc_id;
descending:[edit]
mysql> select rc_id,rc_title from recentchanges ORDER BY rc_id DESC;
Limiting[edit]
mysql> select rc_id from recentchanges LIMIT 0,3; +-------+ | rc_id | +-------+ | 1 | | 2 | | 3 | +-------+ 3 rows in set (0.00 sec) mysql> select rc_id from recentchanges LIMIT 1,4; +-------+ | rc_id | +-------+ | 2 | | 3 | | 4 | | 5 | +-------+ 4 rows in set (0.00 sec)
(output max. 4 line from offset 1). Starts with offset 0 and 0 is default if second parameter not given.
Creating databases and tables[edit]
Creating a database[edit]
mysql> CREATE DATABASE foobar; Query OK, 1 row affected (0.02 sec)
Switching to database[edit]
mysql> use foobar; Database changed
Creating a simple table[edit]
mysql> CREATE TABLE blargh (name VARCHAR(20), owner VARCHAR(20), sex CHAR(1), birth DATE);
Creating a table with auto-incrementing id[edit]
mysql> CREATE table fnord (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),name VARCHAR(30), whatever VARCHAR(7)); Query OK, 0 rows affected (0.00 sec)
Data field types used in CREATE syntax[edit]
Numbers[edit]
TINYINT - -128 to 127 normal / 0 to 255 UNSIGNED.
SMALLINT - -32768 to 32767 normal / 0 to 65535 UNSIGNED.
MEDIUMINT - -8388608 to 8388607 normal / 0 to 16777215 UNSIGNED.
INT - a numeric type which can accept values in the range of -2147483648 to 2147483647
BIGINT( ) - -9223372036854775808 to 9223372036854775807 normal / 0 to 18446744073709551615 UNSIGNED.
DECIMAL - a numeric type with support for floating-point or decimal numbers
FLOAT - A small number with a floating decimal point.
DOUBLE - a numeric type for double-precision floating-point numbers. If you don't know what these are, chances are you won't be using it much.
Text[edit]
CHAR - a string type with a maximum size of 255 characters and a fixed length
VARCHAR - a string type with a maximum size of 255 characters and a variable length
TEXT - a string type with a maximum size of 65535 characters
MEDIUMTEXT - a string with a maximum length of 16777215 characters.
LONGTEXT A string with a maximum length of 4294967295 characters.
Date and Time[edit]
DATE - a date field in the YYYY-MM-DD format
TIME - a time field in the HH:MM:SS format
DATETIME - a combined date/time type in the YYYY-MM-DD HH:MM:SS format
YEAR - a field specifically for year displays in the range 1901 to 2155, in either YYYY or YY formats
TIMESTAMP - a timestamp type, in YYYYMMDDHHMMSS format
Misc[edit]
BLOB - a binary type for variable data (MEDIUMBLOB, LONGBLOB .. )
ENUM - a string type which can accept one value from a list of previously-defined possible values
SET - a string type which can accept zero or more values from a set of previously-defined possible values
Giving access to users[edit]
Examples:
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost' IDENTIFIED BY 'goodsecret' REQUIRE SSL;
mysql> GRANT SELECT ON foo.bar TO 'fnord'@'somehost' IDENTIFIED BY 'somepass';
These are random examples, for further syntax check:
Client does not support authentication protocol[edit]
If you happen to get Client does not support authentication protocol, The most likely reason is that: MySQL 5.0 uses an authentication protocol based on a password hashing algorithm that is incompatible with that used by older (pre-4.1) clients.
You can fix it either by upgrading all client programs to use a 4.1.1 or newer client library or by resetting a password to the old style via:
mysql> SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('newpwd');
Setting the initial root password[edit]
shell> mysql -u root mysql mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');
Reset forgotten root password[edit]
- Take down the mysqld server by sending a kill (not kill -9) to the mysqld server.
- Restart mysqld with the --skip-grant-tables option.
- mysqladmin -u root password 'mynewpassword'
- Restart mysqld normally
You are also able to reset the mysql root password by the help of the following line, while server is up:
# /etc/init.d/mysql reset-password New MySQL root password: Verify: Setting new MySQL root password
How to Reset a Forgotten Root Password
Making backups / mysqldump[edit]
Create[edit]
mysqldump -u root -p somedatabase > dumpfile.sql
mysqldump --compatible=mysql40 -u root -p somedatabase > dumpfile.sql
(to be compatible with certain versions, you can also use other '--compatible=' strings)
Import[edit]
mysql -u root -p somedatabase < dumpfile.sql
Sending output to textfile[edit]
mysql> select foo from bar INTO OUTFILE '/tmp/dump.txt';
bash> mv /tmp/dump.txt .
(if trouble writing because mysql user has different permissions)
Taking input from textfile[edit]
mysql> mysql -u root -p < dumpfile.sql Enter password:
Cheat Sheet[edit]
to prevent SQL injections never trust user input and never insert user filled variables into mysql statements right away, in PHP mysql_real_escape_string ()
Get total size of .MYI files[edit]
weirdo way[edit]
echo "Size of MYI files in current directory: `(for file in \`ls | grep MYI\`; do echo -n "\`du -bs $file | cut -f1\`+"; done; echo "0") | bc` bytes"
normal way[edit]
You can do the same "a little" shorter,too ;) (< LordFrith> mutante, du -ch *MYI), *gg*
du -ch *MYI | tail -n1
symbiosis[edit]
for file in `ls -d /var/lib/mysql/ */`; do echo -n "$file "; du -ch ./$file/*MYI | tail -n1; done
du -ch /var/lib/mysql/*/*MYI
Why ?[edit]
(*) - "By adding up the size of the .MYI files for the tables, you'll have a good idea how large to set the buffer." [1]
mytop[edit]
mytop - top like query monitor for MySQL
drop tables with wildcard / by prefix[edit]
in Bash: for file in /var/lib/mysql/database/prefix*.MYI; do echo "drop table `basename $file | cut -d. -f1`;" > droptables.sql; done
loops through all tables with "prefix*" and writes a bunch of "drop table blah;" statements into a file "droptables.sql". This file can then be executed via something like mysql -u root -p database < droptables.sql either manually or as a second line in a bash script. the password should be entered interactively to avoid it showing up in process list on a multiuser host.
External Links[edit]
IRC[edit]
Help channels on IRC:
Group by Delete via temp table[edit]
<+mutante> hi, i do something like "select foo,bar,count(id) from table group by foo order by count(id);" <+mutante> now i would like to "delete ... where count(id) (from above) = 1" <+mutante> can i combine that? <+mutante> i want to delete all rows where there is only one foo per bar <+salle> mutante: Create temporary table out of the query above <+salle> mutante: CREATE TEMPORARY TABLE temp SELECT foo, COUNT(id) AS c FROM tbl GROUP BY foo HAVING c = 1; <+salle> mutante: CREATE TEMPORARY TABLE temp SELECT foo AS c FROM tbl GROUP BY foo HAVING c = 1; <+salle> mutante: Now use that table in DELETE <+salle> mutante: If it's so small MEMORY engine works perfect. If big it's wise to add some index on the fly <+salle> mutante: DELETE FROM your_table WHERE foo IN (SELECT foo FROM temp); should work <+salle> mutante: MySQL doesn't allow DELETE FROM x WHERE .. (SELECT ... FROM x) <+salle> mutante: same for UPDATE. In many cases it is doable with multi-table DELETE and UPDATE syntax, but GROUP BY makes it more difficult if possible at all