Mysql Instructions

This document describes the various aspects of mysql administration and development. The following are the instructions for using the mysql data server

1) Copy the my.ini file from the <INSTALL_DIR> to <WINDIR> and make sure the basedir and dataddir property is setup for mysqld. For example:

basedir=C:\\Progra~1\\mysql-4.1.8-win

datadir=C:\\Progra~1\\mysql-4.1.8-win\\data

2) Start the server

mysqld-max-nt –console

3) Stop the server

mysqladmin -u root shutdown

4) Look at system

mysql -u root mysql

5) login

mysql -u dhanasingh –p

1) To change the password of root

shell> mysql -u root mysql

mysql> SET PASSWORD FOR ‘root’@’localhost’=PASSWORD(‘new_password’);

2) Create a user

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

ON simplepm.*

TO spmuser@localhost

IDENTIFIED BY ‘spmuser’;

If you need to give the user the rights for another database, do the same but with a different database name.

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘monty’@’localhost’

IDENTIFIED BY ‘some_pass’ WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘monty’@’%’

IDENTIFIED BY ‘some_pass’ WITH GRANT OPTION;

1) To dump a database use

mysqldump –u dhanasingh –p –-opt <database> >filename

2) Create table

Create table project (project_id int, project_name varchar(32) not null,

project_sh_desc varchar(64), project_full_desc varchar(128), project_owner varchar(32) not null, show_hide_flag varchar(1), date_created datetime, date_modified datetime);

3) Alter table

alter table project add primary key (project_id);

alter table activity add date_created datetime, add date_modified datetime;

alter table project change show_hide_flag show_flag char(1);

4) To execute a sql script use

source filename

This section describes some examples of DML statements in mysql

The following delete statement deletes 3 tables using a outer join

The following is an example of an outer join.  Please keep in mind the outer joins are slow