Introduction to MySQL

Introduction

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.

Setup

Minimal install

Customized minimum install

  1. 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.")
  2. In /data, can I just include /mysql/*.frm?
  3. What files do I need from /share
  4. What do I need to do to use InnoDB and/or BDB (transactions, ACID)?
  5. 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. ")
  6. What are those files for in /bin, and which ones do I need?
    1. comp-err.exe
    2. cygwinb19.dll : used to provide history support in mysql.exe?
    3. isamchk.exe
    4. libmysql.dll : client API file to connect to MySQL server programmatically
    5. myisamchk.exe
    6. myisamlog.exe
    7. myisampack.exe
    8. myisam_ftdump.exe
    9. mysql.exe : CLI client
    10. mysqladmin.exe : CLI client (similar to mysql.exe, but you can send commands to MySQLd directly, without loggin on first)
    11. mysqlbinlog.exe
    12. mysqlc.exe
    13. mysqlcheck.exe
    14. mysqld-max-nt.exe : Optimised binary for NT/2000/XP with support for symbolic links, InnoDB and BDB transactional tables, and named pipes
    15. mysqld-max.exe : Optimised binary with support for symbolic links, and InnoDB and BDB transactional tables
    16. mysqld-nt.exe : Optimised binary for NT/2000/XP with support for named pipes
    17. mysqld-opt.exe : Optimised binary with no support for transactional tables OR Optimised binary with InnoDB transactional tables ?
    18. mysqld.exe : Compiled with full debugging and automatic memory allocation checking, symbolic links, and InnoDB and BDB transactional tables
    19. mysqldump.exe
    20. mysqlimport.exe
    21. mysqlmanager.exe
    22. mysqlshow.exe
    23. mysqlshutdown.exe : Like mysqladmin, an easy way to send a command to MySQL without loggin on first?
    24. mysqlwatch.exe
    25. my_print_defaults.exe
    26. pack_isam.exe
    27. perror.exe
    28. replace.exe
    29. winmysqladmin.exe : GUI client (goes with winmysqladmin.cnt and winmysqladmin.hlp)
  7. How to install, start, and stop MySQLd on a 9x platform?
  8. BDB vs. Inno?

2.2.1.2 Installing a Windows Binary Distribution

Here are the files that seem to form a basic MySQL server on Windows:

bin/

data/

Note: *.frm = table structures, *.MYD or *.ISD = Data (MySQL,ISAM format), *.MYI or *.ISM = Indexes

share/

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:

http://groups.google.com/groups?q=mysql+minimum+install&start=10&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=b69lm1%24a5e%241%40forums.macromedia.com&rnum=12

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.

Ubuntu

  1. apt-get install mysql-server mysql-client
  2. update-rc.d mysql defaults
  3. /etc/init.d/mysql start

RPMs

Source

  1. Untar the source package into /usr/src/mysql/
  2. groupadd mysql
  3. useradd -g mysql mysql
  4. gunzip < mysql-VERSION.tar.gz | tar -xvf -
  5. cd mysql-VERSION
  6. ./configure --prefix=/usr/local/mysql
  7. make
  8. make install
  9. scripts/mysql_install_db
  10. chown -R root.mysql  /usr/local/mysql
  11. chown -R mysql /usr/local/mysql/var
  12. cp support-files/my-medium.cnf /etc/my.cnf
  13. /usr/local/mysql/bin/safe_mysqld --user=mysql &
  14. Shut down the server through /usr/local/mysql/bin/mysqladmin -u root shutdown

Commands

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:

  1. db_ID = mysql_init(db_ID)
  2. mysql_real_connect(db_ID, sHostName, sUserName, sPassword, sDB_Name, lPortNum, "", 0&)
  3. mysql_real_query(db_ID, sQuery, Len(sQuery))
  4. lRecords = mysql_store_result(db_ID)
  5. lNumRows = mysql_num_rows(lRecords)
  6. lNumFields = mysql_num_fields(lRecords)
  7. pRow = mysql_fetch_row(lRecords)
  8. pLen = mysql_fetch_lengths(lRecords)
  9. mysql_free_result(lRecords)

Two API's are available to analyse data returned by a  call to mysql_real_query :

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

List of APIs in C (as seen here)

Q&A

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?

PhpMyAdmin

How can I create a table with a primary key, and import records from an CSV file?

Import the records first, and alter the table to add a column to hold an auto-generated primary key to identify each record uniquely.

Temp

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 mysqladmin flush-privileges 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 mysqladmin shutdown 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 mysql-setpermission mysqlaccess Check how resolver returns hostname : Has to match grant statement Security: Delete anonymous account Delete from user where user=""; flush privileges To back up DBs:
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:
  1. mysql --skip-grant-tables
  2. restore
  3. mysqladmin flush-privileges
  4. 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 ~; /etc/my.cnf ~/.my.cnf [client] host=mysqlserver user=me password=oshienai 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 mysql mydb
  1. >lock table mytable READ; >flush tables;
  2. >[my]isamchk;
  3. >unlock table;
  4. safe_mysqld -u mysqladmn &

Resources

Tools

Sites