$Id: mysql.txt,v 1.5 2009/10/20 20:47:29 jerry Exp $

mysql.txt
---------

After installing mysql from a FreeBSD package

# create initial tables
cd /usr/local
bin/mysql_install_db --user=mysql

# start mysqld
bin/mysqld_safe --user=mysql &

# verify it is running
bin/mysqladmin version
bin/mysqladmin variables

# test it
bin/mysqlshow
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+

bin/mysql -e "SELECT Host,Db,User FROM db" mysql
+------+---------+------+
| Host | Db      | User |
+------+---------+------+
| %    | test    |      | 
| %    | test\_% |      | 
+------+---------+------+

# set root password

/usr/local/bin/mysqladmin -u root password 'apeldoorn'

mysql -u root -p

> UPDATE mysql.user SET Password = PASSWORD('<root password>') WHERE User = 'root';

# create user accounts, local and remote

> GRANT ALL PRIVILEGES ON *.* TO 'jerry'@'localhost' IDENTIFIED BY '<password>' WITH GRANT OPTION;

> GRANT ALL PRIVILEGES ON *.* TO 'jerry'@'%' IDENTIFIED BY '<password>' WITH GRANT OPTION;

# now you can login from another client and create databases.

mysql -h marles -u jerry -p
> create database ijdb;
> use ijdb;
> create table joke ( id int not null auto_increment primary key, joketext text, jokedate not null);

> describe joke;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | PRI | NULL    | auto_increment | 
| joketext | text    | YES  |     | NULL    |                | 
| jokedate | date    | NO   |     | NULL    |                | 
+----------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql -h marles -u jerry -p ijdb;

mysql> show tables;
+----------------+
| Tables_in_ijdb |
+----------------+
| author         |
| category       |
| joke           |
| jokecategory   |
+----------------+
4 rows in set (0.00 sec)

mysql> grant all on ijdb.*
    -> to jerry@trevor.internal
    -> identified by '<password>'
    -> with grant option;/etc/rc.d/mysql.server
Query OK, 0 rows affected (0.01 sec)


Now we just need to add mysql to system startup

add  mysql_enable="YES"  to /etc/rc.conf

startup script is /usr/local/etc/rc.d/mysql-server 

/*** end ***/