Introduction to MySQL
MySQL comes in two flavors: as two separate packages (server + client) or
a single package ("Overview
of the Embedded MySQL Server Library".) A description of the C API
is available here.
MySQL 4 now includes the extensions that used to be only available in InnoDB.
Customized minimum install
- OK to use mysqld-max-nt on a 98 host? ("InnoDB tables are included
in the MySQL source distribution starting from 3.23.34a and are activated
in the *MySQL-max* binary.")
- In /data, can I just include /mysql/*.frm?
- What files do I need from /share
- What do I need to do to use InnoDB and/or BDB (transactions, ACID)?
- C:\MY.CNF or $WINDIR\MY.INI? ("6. It is always safe to create the
my.ini file - mandatory in case you are not instaling mysql in the default
c:\mysql directory (c: being the drive on which the OS is installed) my.cnf
also works in most cases - but always safe to use my.ini file. 7. Create
the my.ini file in the /WINNT directory. ")
- What are those files for in /bin, and which ones do I need?
- cygwinb19.dll : used to provide history support in mysql.exe?
- libmysql.dll : client API file to connect to MySQL server programmatically
- mysql.exe : CLI client
- mysqladmin.exe : CLI client (similar to mysql.exe, but you can send
commands to MySQLd directly, without loggin on first)
- mysqld-max-nt.exe : Optimised binary for NT/2000/XP with support for
symbolic links, InnoDB and BDB transactional tables, and named pipes
- mysqld-max.exe : Optimised binary with support for symbolic links,
and InnoDB and BDB transactional tables
- mysqld-nt.exe : Optimised binary for NT/2000/XP with support for named
- mysqld-opt.exe : Optimised binary with no support
for transactional tables OR Optimised binary with InnoDB transactional
- mysqld.exe : Compiled with full debugging and automatic memory allocation
checking, symbolic links, and InnoDB and BDB transactional tables
- mysqlshutdown.exe : Like mysqladmin, an easy way to send a command
to MySQL without loggin on first?
- winmysqladmin.exe : GUI client (goes with winmysqladmin.cnt and
- How to install, start, and stop MySQLd on a 9x platform?
- BDB vs. Inno?
Installing a Windows Binary Distribution
Here are the files that seem to form a basic MySQL server on Windows:
- mysqld (server; 2.2MB)
- mysql.exe (command-line client; about 300KB)
- mysqladmin.exe (command-line utility to ease use of main commands; 300KB)
- mysql/ (the MySQL database with tables like user, host, etc.; Bunch
of .frm, .myd, .and myi; About 60KB)
- test/ (the test database; 8KB)
Note: *.frm = table structures, *.MYD or *.ISD = Data (MySQL,ISAM format),
*.MYI or *.ISM = Indexes
- charsets/ (charsets for a number of languages; I guess you can trim
it down to just the language you need to use; The whole set weighs about
- <language>/ (one directory per language, each containing errmsg.sys
and errmsg.txt to display error messages in the ad hoc language; Whole set
If you want to use transactions and/or InnoDB tables, you'll need to create
C:\IBDATA and C:\IBLOGS.
If you decide to install MySQL in another directory than C:\MYSQL, you'll
have to copy one of the .CNF files in the installer to C:\, and customize the
fields it contains so that MySQL knows where to find its files.
Looks like you can also rename this to my.ini, and move
it to %SYSTEMROOT%
Here's a working C:\MY.CNF file:
To install the MySQL server as a service, use the --install switch. If you're
using Windows 95, 98 or ME do not run mysqld --install. These operating systems
don't have the ability to host a "service." So, you need to run MySQL
as a standalone application by executing the command mysqld --standalone (must
--console be added?)
To install the MySQL server as a service, but have it start manually, use
the --install-manual switch. To start the service, you can either go to the
Services section of Windows, or use "NET START MySQL" in a DOS box.
To remove the MySQL server service, use the --remove switch.
- apt-get install mysql-server mysql-client
- update-rc.d mysql defaults
- /etc/init.d/mysql start
- Untar the source package into /usr/src/mysql/
- groupadd mysql
- useradd -g mysql mysql
- gunzip < mysql-VERSION.tar.gz | tar -xvf -
- cd mysql-VERSION
- ./configure --prefix=/usr/local/mysql
- make install
- chown -R root.mysql /usr/local/mysql
- chown -R mysql /usr/local/mysql/var
- cp support-files/my-medium.cnf /etc/my.cnf
- /usr/local/mysql/bin/safe_mysqld --user=mysql &
- Shut down the server through /usr/local/mysql/bin/mysqladmin -u root
- To display the list of databases: mysqlshow
- To display the list of tables inside a given database: mysqlshow mydb
- To change root password: mysqladmin -u root password
- To change access rights:
GRANT ALL PRIVILEGES ON myDatabase.*
to NewUser@localhost identified by 'newpassword';
GRANT ALL PRIVILEGES
ON myDatabase.* to NewUser@'%' identified by 'newpassword';
- To create a database: create database mydb;
- To create a table in the currently-selected database: CREATE TABLE pet (id
INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20), owner VARCHAR(20),
species VARCHAR(20), sex CHAR(1), birth DATE, death
- To create a user: grant all privileges on *.* to root@localhost identified by
- To insert values in a given table:
- From a file: LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;
- LOAD DATA INFILE 'C:\\mydata.csv' INTO TABLE pet FIELDS TERMINATED
- Live: INSERT INTO pet VALUES
- To update a set of records: update clients set internet="firstname.lastname@example.org"
- To add or remove columns in a table : alter table clients add id INT
NOT NULL AUTO_INCREMENT PRIMARY KEY;
- To remove all records from a table: delete from pet;
- To display the structure of a table: describe mytable;
- To index a column and speed up searches: CREATE INDEX myindex ON mytable
- To export data in a platform-independent format: mysqldump -u <username>
-p <password> -h <host> sample_db > /usr/backups/mysql/sample_db.2004-12-16
- To export only some tables: mysqldump sample_db articles comments
links > /usr/backups/mysql/sample_db.art_comm_lin.2004-12-16
- To restore tables in a database, first create that database, and then
import the data: mysql -u <username> -p <password> -h <hostname>
sample_db < sample_db.2004-12-16
Working with MySQL programmatically
Using the C API from Visual Basic, here's how to connect to MySQL, create
a DB and a table, add a record, query the table, and retrieve the rows:
- db_ID = mysql_init(db_ID)
- mysql_real_connect(db_ID, sHostName, sUserName, sPassword, sDB_Name,
lPortNum, "", 0&)
- mysql_real_query(db_ID, sQuery, Len(sQuery))
- lRecords = mysql_store_result(db_ID)
- lNumRows = mysql_num_rows(lRecords)
- lNumFields = mysql_num_fields(lRecords)
- pRow = mysql_fetch_row(lRecords)
- pLen = mysql_fetch_lengths(lRecords)
Two API's are available to analyse data returned by a call to mysql_real_query
- mysql_store_result() : returns the entire result set onto the
client host, and lets you move back and forth in the result set using mysql_data_seek()
since it is located locally using mysql_data_seek() and mysql_row_seek()
- mysql_use_result() : keeps the result set on the server,
which is good if the client host has little RAM, and is faster because due
to lower allocation overhead; On the minus side, you can only browse rows
sequentially, you won't know how many rows the query generates until you
have retrieved them all, and you cannot interrupt a retrieval halfway-through
even if you found the record(s) you were looking for
To extract rows, use mysql_fetch_row(); You can get the size of the data
in each row using mysql_fetch_lengths(). Remember to use mysql_free_result()
to free the RAM used to contain the rows returned by the server.
When using mysql_store_result() right after a mysql_real_query(), you can
tell what type of query was sent, how many rows were returned, and whether this
result is OK (ie. no rows returned because the query was an INSERT, UPDATE,
DELETE, etc.) or not (a SELECT that failed).
Some API's are available to analyse a result set if you need to obtain information
such as the number of fields, their names and types, etc. Those API's are mysql_fetch_field()
which you must call repeatedly to parse a given row, or by calling mysql_fetch_field_direct()
to access a field directly. Change the current field cursors position with mysql_field_seek().
An alternative to calling those APIs repeatedly is using mysql_fetch_fields()
once to retrieve information for fields in one go.
For security, make sure that you check how each command performed, using
the mysql_error() and mysql_errno() after each API call. API's
that return a pointer will return a NULL value in case of failure, and API's
that return an integer will return a zero for success and non-zero to indicate
List of APIs in C (as seen here)
When installing MySQL for Windows the first time, it complains that it cannot
find its INI file
Don't worry, this is perfectly normal. It will generate it from scratch if
it can't find this file.
How to I install MySQL as an NT service?
You must run winmysqladmin.exe at least once, or use the --install switch.
Can I install just the client and not the whole thing?
Download the ZIP version instead of the EXE version.
How can I manage MySQL from a web interface?
How can I create a table with a primary key, and import records from an
Import the records first, and alter the table to add a column to hold an
auto-generated primary key to identify each record uniquely.
Field varchar(20) not null;
Field varchar(20) null default "Default value";
Mysqlshow = shows DBs
mysqladmin -u root -ptest variables
Better to use safe_mysqld & : Creates log files AND restarts server if exits
mysql_installdb = script to init setup
mysqladmin -u root password "mynewpassword" if no password currently set
mysql -u root, update user set password=PASSWORD("mynewpassword") where user=root; flush privileges;
mysqladmin -u root reload
Use mysqladmn/mysqlgrp to install/run MySQL
chown -R mysqladmn:mysqlgrp /var/lib/mysql/*
chmod -R 700 /var/lib/mysql/*
Chkconfig to install boot script
Myisamchk/Isamchk: Checks DBs
Lost root password? mysqld --skip-grant-tables
Accesses are checked with
user (and host) tables = genera rights. Host table is not affected by grand/revoke, so not used much
db + tables_privileges + columns_privileges = DB-specific rights
Check how resolver returns hostname : Has to match grant statement
Security: Delete anonymous account
Delete from user where user="";
To back up DBs:
- tar cf - mydb | (cd /var/db/ ; tar xf -) : NOK if live + platform-dependent
Remember to also back up logs to re-create tables, etc. Watch out for DELETE and DROP in log files so as to avoid re-deleting restored data...
mysqldump mydb > ~mydb.`date`
mysqldump mytable > ~mytable.`date`
mysqldump | gzip > ~
mysqladmin -h remote.acme.com create samp_db
mysqldump samp_db | mysql -h remote.acme.com samp_db
To restore if mysql DB dead:
- mysql --skip-grant-tables
- mysqladmin flush-privileges
- check update log (remote dropped DB/tables!)
mysql --one-db ~ < update.X
Security = file system + grant, and log files
Sex enum("F","M") not null;
ID int unsigned not null auto-increment primary key;
~ order by myfield asc/desc limit 10,5; //Starts with 10th record, and shows the next 5 ordered in asc or desc order by myfield
select concat(Firstname, " ", Lastname) as Name from President; //Column label is "Name"
~ where monthname(birth)="March" and dayofmonth(birth)=29;
select count(*) from ~ where ~=~;
~ having count > 1 //Just like WHERE, but done after data are returned
Join = extracting infos from multiple tables. Left join = keeps valid data from join
As a safety measure, do NOT run DELETE immediately. Rather, run SELECT to check which records will be affected.
ALTER TABLE mytable ADD ~;
Cannot log on if password save in clear-text ?
Better to use DB- or table-specific users instead of granting more restrictive access to general users. Do not grant access to mysql DB other than admins.
To load TAB+CR delimited entries from a file: > load data (local) infile "~.txt" into table mytable; local = client host
Disable show DB/table with --skip-show-database
Connection can fail if leave anonymous account because must not enter a password -> mysql>delete from user where user="";
[my]isamchk --[safe]recover --quick/(rien) mytable
Back up DBs first before attempting to repair!
External locking = file system; Internal locking = mysql
- >lock table mytable READ; >flush tables;
- >unlock table;
- safe_mysqld -u mysqladmn &