Create a new article
Write your page title here:
We currently have 3185 articles on s23. Type your article name above or click on one of the titles below and start writing!

Revision as of 10:33, 9 April 2005 by imported>mutante (giving user access)

The World's Most Popular Open Source Database

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

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.


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.

shell> mysql -u root -p
Enter password:
Welcome to the MySQL monitor.

Switching into a database.

mysql> use wikidb;
Database changed

Showing tables

mysql> show tables;
| Tables_in_wikidb |
| archive          |
| blobs            |
| imagelinks       |
| interwiki        |
| watchlist        |
23 rows in set (0.00 sec)

Getting field names

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

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

You can use wildcards like in:

mysql> select * from interwiki;

Conditions (WHERE-clause)

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%";



mysql> select rc_id,rc_title from recentchanges ORDER BY rc_id;


mysql> select rc_id,rc_title from recentchanges ORDER BY rc_id DESC;


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.

Giving access to users

mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
    -> IDENTIFIED BY 'goodsecret' REQUIRE SSL;

External Links