MySQL Survival Guide
(Free Web Tutorials)

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)

• 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;

• 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>

• 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).

• 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
       

• SQL command:
  SHOW DATABASES;
• OS Command line:
  c:\mysql\bin\mysqlshow.exe

• 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 a Table

• CREATE TABLE testtable (mychar char(5), myint int);
• DROP TABLE testtable;

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

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 

• ??? - 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: ???

• 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++.

• # - Start of a comment.
• /* */ - Not as popular.
• -- If followed by a space. Not a good idea to use this as a comment.  Use # instead.

• 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)