MySQL Survival Guide
(Free Web Tutorials)

by Michael Thomas

MySQL Home Page

This is my MySQL Survival Guide notes.

Note: On the MySQL Home page, you can download the whole MySQL site (all content, tutorials & examples) !!!

Prerequisites

Objectives

Unorganized Notes

Topic Notes
Starting the MySql Database Server. Windows Machines

Win NT/2000 - Start service from DOS

  • Start MySql server: c:\mysql\bin\mysqld-nt --standalone - You can leave the DOS window open or close it.
  • Stop MySql server: c:\mysql\bin\mysqladmin shutdown - This will shutdown the server.

Win NT/2000 - Installed as a "service"

  • Install as a "service".  The default will start the server when you boot up.
    C:\mysql\bin\mysqld-nt --install
  • c:\> net start mysql  - starts mysql database server.
  • c:\> net stop mysql - stops mysql database server.
  • Note:  I get the following error when installed as a service and trying to use the mysql monitor to create, drop tables (and other commands that need privileges):  ERROR 1: Can't create/write to file '.\test\test_table5.frm' (Errcode: 13)  Don't know what the solutions is yet.

Win 95/98 - see the docs in the directory c:\mysql\Docs

Other notes:

  • --ansi - MySQL runs in ANSI mode.  (See docs: 5.2)
MySql Admin Tables Use the MySql Monitor to view:  C:\mysql\bin\mysql mysql
  • Use the "mysql" Database
    • use mysql;
  • Show a list of Tables in the Database
    • show tables;
      Tables: host, db, user
  • List data from "user" table
    • select Host, User, Password from user;
    • select * from user;
  • List data from "db" table
    • select Host, Db, User from db;
    • select * from db;
  • list data from "host" table
    • select * from host;
MySql Admin Tools MySql Admin tool (mysqladmin)
  • c:\mysql\bin\mysqladmin - will give you a list of options.
  • mysqladmin version - shows the version, port, host, etc...
  • mysqladmin extended-status - shows the connections open, aborted, etc ...
  • mysqladmin variables - list of system variables and values.
  • mysqladmin processlist

Other admin tools:

  • mysql - SQL monitor used to execute SQL commands.
  • mysqlshow - show a list of databases.
  • mysqldump - <under construction>
  • mysqlimport - <under construction>
MySql Monitor MySql Monitor - access to Tables via Sql commands.
  • C:\mysql\bin\mysql <database name>
    • C:\mysql\bin\mysql - Launches the Monitor without connecting to a database.
    • C:\mysql\bin\mysql mysql - Loads Monitor & connects to MySql Admin tables.
    • C:\mysql\bin\mysql test - Loads Monitor & connects to the test database installed with MySql.
    • After the monitor is loaded here are some commands:
      • mysql> show databases - lists the databases.
      • mysql> use mysql - connects to the "mysql" admin database.
      • mysql> show tables - lists the tables in the database that you are using (connected to).
Running External SQL scripts:  from batch files or MySql Monitor & Source. Running External SQL scripts
  • Options: Batch file or MySql Monitor & Source.
    • Using source - Load MySql Monitor from the directory where the scripts reside:  C:\mysql\bin\mysql
      • Use the source command to run the Script.
        Example:  C:\mysql\bin\mysql> source myscript.sql
    • Using a batch file ( I haven't tested these!)
      • Option #1
        mysql -e "source sql-script-file.sql"
      • Option #2
        mysql < sql-script-file.sql > mysql.txt

         
Show Databases Show Databases
  • SQL command:
    SHOW DATABASES;
  • OS Command line:
    c:\mysql\bin\mysqlshow.exe
Show Tables SHOW TABLES;
Show Table's Fields EXPLAIN <table name>;
Create and Drop a Database Create a Database
  • Using OS command line:
    c:\mysql\bin\mysqladmin create <database name>
    If the database was created you should be able to see a new empty directory:  c:\mysql\data\<database name>
  • Using SQL code:
    CREATE DATABASE mytempdb;
  • Note:  I've just created an empty directory via Windows Explorer and things worked fine.

(Note:  When you install MySql, two databases (directories) are installed for you:  mysql & test.  "mysql" is the Admin database.  "test" is a blank database.)

Drop a Database:

  • Using OS command line:
    c:\mysql\bin\mysqladmin drop <database name>
  • Using SQL code:
    DROP DATABASE mytempdb;
  • Note:  I've just deleted the directory via Windows Explorer and things worked fine.
Create and Drop a Table Load MySql monitor (use the "test" database if you want to play).

Create a Table

  • CREATE TABLE testtable (mychar char(5), myint int);
  • DROP TABLE testtable;
Use a Database Use a database (connecting to a Database)
  • use test;
  • use test --host localhost --user root -password  - this will connect you to the "test" database with admin privilege with a default install of MySql.
Security & MySql Admin Tables. Steps to securing MySql's Admin Tables.  

The default privileges on Windows give all local users full privileges to all databases without specifying a password.  The following steps remove the anonymous users and specifies a password for the root user.

C:\> C:\mysql\bin\mysql mysql
mysql> DELETE FROM user WHERE Host='localhost' AND User='';
mysql> QUIT
C:\> C:\mysql\bin\mysqladmin reload
C:\> C:\mysql\bin\mysqladmin -u root password your_password

After you've set the password, if you want to take down the mysqld server, you can do so using this command:

C:\> mysqladmin --user=root --password=your_password shutdown 

Errors Errors:
  • ??? - List of errors and descriptions.
  • If your getting access denied for user: .... when accessing "mysql" from a localhost (local machine) you may need to do the following:
    • Modify c:\winnt\system32\drivers\etc\host
      Add the following entry:  127.0.0.1 localhost
    • Also see the MySQL docs: Section 4.13.8  and search for "access denied".  Docs say, to create a file called: \windows\hosts
  • ERROR 1: Can't create/write to file '.\test\test_table5.frm' (Errcode: 13) - When trying to create a table.  
    Solution: ???
Port # I think the default port is:  3306
MySql Feature Notes MySql Feature Notes
  • Indexing - Up to 32 indexes per table are allowed. Each index may consist of 1 to 16 columns or parts of columns. The maximum index length
    is 500 bytes (this may be changed when compiling MySQL). An index may use a prefix of a CHAR or VARCHAR field. 
  • MySql doc's say they know of companies with:
    Databases that contain:
    • 50,000,000 records
    • 60,000 tables
    • 5,000,000,000 rows
  • MySql is written in C and C++.
Comments Comments
  • # - Start of a comment.
  • /* */ - Not as popular.
  • -- If followed by a space. Not a good idea to use this as a comment.  Use # instead.
Functionality not supported by MySQL Not Supported
  • Sub-selects are not supported in MySQL (docs: 5.4.1) - has some work around suggestions.
  • Select into table (docs: 5.4.2)
  • Stored procedures and Triggers (docs: 5.4.4)
  • Foreign Keys (docs: 5.4.5)
  • Views (docs: 5.4.6)
My notes. Section: 6