mySQL Configuration Guide

 

  1. I recommend reading some of the online manual for the mySQL database. It's located at http://www.mysql.com/doc/en/index.html
  2. More specifically I HIGHLY recommend reading chapter 3 of the manual called the Tutorial section - http://www.mysql.com/doc/en/Tutorial.html This will teach you how to log on to the mysql database, create databases, create tables, and insert data into the tables.
  3. Chapter 4 (more specifically 4.1 - 4.3) is also very important - http://www.mysql.com/doc/en/MySQL_Database_Administration.html
  4. You will need to know how to change your root password and add other uses. To change your root password go to a terminal command prompt and type:

 

shell> mysql -u root mysql

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

mysql> quit

Where new_password is a password that you choose and remember that IS Case SenSiTive! Figure 3.1 shows exactly what this looks like in a terminal command window.

 


Figure 3.1

 

  1. Chapter 4.3.4 has more details - http://www.mysql.com/doc/en/Default_privileges.html
  2. Next, you will need to create a database and a table to store your information.

 

shell> mysql -u root -p

mysql> CREATE DATABASE menagerie;

mysql> use menagerie

mysql> CREATE TABLE pet(name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

 

*Note: The -> will appear if you press the return key in the middle of my command. The mySQL database will not execute most commands until a ; appears. You do NOT have to press the return key in the middle of this statement. You can type it in all on the same line and it will have the same effect.

 

  1. Next type the following to show all the tables on the database:

 

mysql> SHOW TABLES;

This will show a table named pet in the menagerie database.

 

  1. Next show the structure of the pet table:

 

mysql> DESCRIBE pet;

 

This will give a description of the table called pet. It will list all of the fields in the table as well as their types (integers, character,date, etc).

 

  1. Then type the following to quit the sql database:

 

mysql> quit

 

Figure 3.2 will show a screen shot of all the above commands entered.

 

Figure 3.2

 

10.   When creating your php files, you do not want to log on to the database using your root password because anybody who gets their hands on the source of your .php file will know your root password and be able to do a lot of damage.

11.   To solve this problem, we need to create a new mySQL user who has access only to a certain database or to certain tables that we decide on!

 

shell> mysql -u root -p

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON database_name.* TO new_user@localhost IDENTIFIED BY 'new_users_password';

mysql> quit

 

*Note: database_name and new_user and new_users_password need to be replaced by your own information.

*Note: if you replace database_name with *.* you will effectively allow the new_user access to all databases, but without all the privileges! Also if you don't want to list out the privileges, you can grant them all privileges by replacing them with ALL PRIVILEGES. Be extremely careful with these options!

 

  1. For more information go to: http://www.mysql.com/doc/en/Adding_users.html

13.   Figure 3.3 shows a screen capture of the above commands executed in a terminal window.

 


Figure 3.3

 

14.   Many administration needs can be handled by phpMyAdmin, which will be discussed in another tutorial. These are the basics, but the graphical phpMyAdmin will be easier to remember.

15.   If you can't find information on the mysql webpage, then you can do a search at any time to locate the information you need on the webpage - this is the advantage over the printed edition of the manual.

 

 

Reference