MySQL Quick Tutorial

MySQL Quick Tutorial

This is a MySQL Cookbook / Crash Course / Quick Tutorial for FreeBSD.

Install MySQL in FreeBSD
----------------------------
Build MySQL from ports:

cd /usr/ports/databases/mysql51-server
make install clean
rehash

Edit /etc/rc.conf (to enable MySQL at boot and start the service) and add:

  mysql_enable="YES"

Start MySQL:

  /usr/local/etc/rc.d/mysql-server start


Set a password for MySQL server
---------------------------------------
The password is set for MySQL root account. Please note that this root account is different from FreeBSD's system root account. It's a password only for MySQL, used by MySQL, it has nothing to do with FreeBSD's root account.

  mysqladmin password mypass

(where mypass is our password).

To change root password on a mysql server use:

  mysqladmin -u root -p password newpass


Login to MySQL client
-------------------------
To login to MySQL command line client as root we use:

  mysql -u root -p


Create a database in MySQL
--------------------------------
Login to MySQL client from command line in FreeBSD and run:

  mysql>   create database myproject;


Delete a database in MySQL
--------------------------------
Login to MySQL client from command line in FreeBSD and run:

  mysql>  drop database myproject;


Use a database
------------------
To use a database for creating tables or working with tables we first use the database with command:

  mysql>  use database myproject;


Create tables in our database
---------------------------------
First we connect to our database from MySQL client with:

  mysql> use myproject;

Then we create myfriends table with fields namid, firstname and lastname (first field is integer the second and third are strings of characters):

  mysql> create table myfriends ( nameid int, firstname varchar(40), lastname varchar(40));


Add a record to our table with insert command:

  mysql>  insert into myfriends( nameid, firstname, lastname) values (1, 'john', 'last');


MySQL Logging
-------------------
To log mysql in a binary log file edit my.cnf and add at tag [mysqld]:
log-bin

To see the log go to mysql console and run:
mysql>  show binlog events;


MySQL Tips
--------------
Other useful commands:
  show status;
show status\G;

To see querries and other commands from mysql console use: show processlist;

You can kill a querry with kill command from mysql console:  kill 700; where 700 is Id column from show processlist command.

To see table status of a database (useful) run from mysq console:
mysql>  use database;
mysql>  show table status like '%';