Difference between revisions of "MySQL"

From s23
Jump to navigation Jump to search
imported>mutante
imported>mutante
m
Line 311: Line 311:
  
  
You can do the same "a little" shorter,too ;) (''< LordFrith> mutante, du -ch *MYI'', *gg*)
+
==== normal way ====
  
 
+
You can do the same "a little" shorter,too ;) (''< LordFrith> mutante, du -ch *MYI''), *gg*
==== normal way ====
 
  
 
<tt>du -ch *MYI</tt>
 
<tt>du -ch *MYI</tt>

Revision as of 18:59, 5 July 2006

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:


  1. 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.
  2. 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.
  3. MySQL Pro is the commercially licensed version of the MySQL Standard database server, including InnoDB support.
  4. 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

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.
mysql>


Switching into a database.

mysql> use wikidb;
Database changed
mysql>


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


Sorting

ascending:

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

descending:

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

Limiting


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

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:

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');

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

  1. Take down the mysqld server by sending a kill (not kill -9) to the mysqld server.
  2. Restart mysqld with the --skip-grant-tables option.
  3. mysqladmin -u root password 'mynewpassword'
  4. Restart mysqld normally

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 strings behind --compatible=)

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

Get total size of .MYI files

weirdo way

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

You can do the same "a little" shorter,too ;) (< LordFrith> mutante, du -ch *MYI), *gg*

du -ch *MYI


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." [1]

External Links

IRC

Help channels on IRC:

  1. #mysql on EfNet
  2. #mysql on Freenode