MySQL

From BenningtonWiki

Jump to: navigation, search

Contents

[edit] Reference

mysql is for MySQL 4. Use mysql5 for MySQL5.

[edit] Starting up mysql interactive 

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1639 to server version: 4.1.22
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> _

[edit] Dumb MySQL trick 

mysql> select version(), now();
+-----------+---------------------+
| version() | now()               |
+-----------+---------------------+
| 4.1.22    | 2007-09-21 08:41:39 |
+-----------+---------------------+
1 row in set (0.03 sec)

[edit] Accessing databases 

mysql> show databases;
+--------------+
| Database     |
+--------------+
| mysql        |
| test         |
| wikidb       |
+--------------+
3 rows in set (0.00 sec)
 
mysql> use wikidb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> show tables;
+-------------------+
| Tables_in_wikidb  |
+-------------------+
| archive           |
| categorylinks     |
| externallinks     |
| filearchive       |
| hitcounter        |
| image             |
| imagelinks        |
| interwiki         |
| ipblocks          |
| ipblocks_old      |
| job               |
| langlinks         |
| logging           |
| math              |
| objectcache       |
| oldimage          |
| page              |
| page_restrictions |
| pagelinks         |
| querycache        |
| querycache_info   |
| querycachetwo     |
| recentchanges     |
| redirect          |
| revision          |
| searchindex       |
| site_stats        |
| templatelinks     |
| text              |
| trackbacks        |
| transcache        |
| user              |
| user_groups       |
| user_newtalk      |
| validate          |
| watchlist         |
+-------------------+
36 rows in set (0.00 sec)

[edit] Dumping and restoring a database

Dumping: 

$ mysqldump -u root -p wikidb > wikidb.sql

If the wikidb doesn't exist, you need to create it first: 

mysql> create database wikidb;
Query OK, 1 row affected (0.01 sec)

Restoring: 

$ mysql -u root -p wikidb < wikidb.sql

The wikidb.sql file contains mysql instructions to recreate the database contents from scratch. When restoring the database is emptied first (that is, the dump is not merged into the existing database).

[edit] Creating a database 

mysql> CREATE DATABASE dbname DEFAULT CHARACTER SET 'utf8';
Query OK, 1 row affected (0.19 sec)

[edit] Creating an account 

mysql> grant all privileges on wikidb.* to 'wiki'@'localhost' identified by 'serve';
Query OK, 0 rows affected (0.02 sec)
Retrieved from "http://wiki.bennington.edu/wiki/MySQL"
Personal tools