NOTES/MySQL

[ HOME | REFERENCE | FreeBSD | MySQL | PostgreSQL | Apache | qmail | PHP | Pure-FTPd ]

introduction

MySQL is the most popular open source database, and that is why I chose it. The more popular, the more support it normally has... more or less. This page is not to argue which database servers are better. This page does not teach you SQL commands at all. Like all other notes on twinwork.net, it is to get you running quickly. Get it up, running, and working now, and we will work on securing it later.

And by all means, please check the MySQL website for more information on how to use your new MySQL server. Like all other documents on this site, don't exclusively use NOTES to fully configure and understand your new server.

We are starting to use PostgreSQL as our production database. MySQL NOTES will stay on NOTES as a reference guide, but do not expect too many future updates.

These NOTES were done with MySQL 5.0.67.


track

  1. ports
  2. rc configuration
  3. start the server
  4. privileged user
  5. mysql-front
  6. backup
  7. references

i. ports

Install MySQL from the ports collection. As root

# cd /usr/ports/databases/mysql50-server
# make install clean

Building MySQL takes awhile.


ii. rc configuration

In order to start MySQL, you will need to add a few things to /etc/rc.conf

# echo 'mysql_enable="YES"' >> /etc/rc.conf
# echo 'mysql_dbdir="/usr/data/mydb"' >> /etc/rc.conf


iii. start the server

Start the MySQL server.

# cd /usr/local/etc/rc.d
# mv mysql-server mysql-server.sh
# ./mysql-server.sh start

The MySQL root user and FreeBSD's root user are two different accounts. MySQL handles all of its own accounts on its database called mysql. Just like FreeBSD's root user when first installed, there's a blank password. Log into the database and change root's password.

# mysqladmin -u root password my_new_password

Yes, the password is typed in plain text. Dont' forget about the semi-colon at the end of the line. Passwords stored in MySQL are not stored in plain text. The PASSWORD() function creates a hash of the password.

Now log back into the database as root

# mysql -u root -p

Note: That says start mysql with user root and prompt for root's password.

Enter your password at the prompt.

Here are a couple of commands to get you started once logged in.

mysql> SHOW DATABASES;

Shows all databases on the server.

mysql> USE test;

Uses the test database

mysql> SHOW TABLES;

Now shows all tables in the test database.

mysql> SHOW COLUMNS FROM this_table;

Note: The mysql> means you're in MySQL's command prompt.

Shows all columns from this_table specified from test database.

That should get you started what's in your MySQL server when you first install it. Teaching the rest of the MySQL commands are beyond the scope of this document, let's move on.


iv. privileged user

Next, create another user that has the same privileges as root does. You would do this if you want to connect to your MySQL server remotely.

mysql> GRANT ALL PRIVILEGES ON *.* TO rey@'%' IDENTIFIED BY 'reys_password' WITH GRANT OPTION;

Note: In case your browser wraps the above line, this command should all be on one line.

Again, password is shown in plain text. There are single quotation marks around the designated password. And yes, the password will be stored using a hash. This shows your new user, rey, will have all privileges for all databases on the MySQL server. The user is actually rey@'%', or user@hostname to be more precise. Using only the % as the hostname (surrounded by apostrophes), says rey from any IP or hostname (or machine anywhere in general) can access this database.

This is a generic access (and insecure I might add) for a user to have full access to your MySQL server. Instead of using %, narrow it down to a hostname or IP. (You do not need the apostrophes around IP addresses or hostnames.)


v. mysql-front

As mentioned in the introduction, this document does not go over SQL commands. Check the references for more information. (And it is highly recommended you do familiar yourself with MySQL and SQL in general.)

I prefer not to use the tiny command shell for looking over data, so I resorted to use a MySQL GUI client. This is the reasoning why we created a new fully privileged user that can connect from somewhere other than the localhost.

If your primary workstation is still a MS Windows machine, MySQL-front is a good compliment to MySQL server. I think it's as good as the MSSQL2k snap-in for the Microsoft Management Console.

Unfortunately, the author of MySQL-Front has discontinued his support to it. The good news is he left the forum open for discussion and support. You can find the latest MySQL-Front downloads at the forum.

Of course, MySQL-front still isn't complete, (there are still a couple of powerful features missing in MySQL-front using the GUI, but you can always use the CLI in the program to execute these commands), but it's still an awesome tool to quickly create users, databases, tables, and what not.

The program should be self-explanatory, so just experiment from there.

Note: It is not recommended to access a remote MySQL server over the Internet without encryption. Your MySQL username and password is sent over plaintext and can be sniffed out. Any data you send can also be sniffed. I still use MySQL-Front while connecting to a remote server over the Internet, but I do so over SSH.

But one thing you should make is the database you want to use for your website and the user who will access the database. For testing purposes, I created a sandbox database. I also created a user by the same name, sandbox. I gave this user SELECT, INSERT, UPDATE, DELETE privileges since those are the most common commands this user will use through PHP.

ie. A visitor to singlethread.org signs the non-existent guestbook. When they submit, it will INSERT an entry to our database. PHP would use the user sandbox to perform that operation.


vi. backup

...


vii. references

 


Valid XHTML 1.0!QUESTIONS/COMMENTS/CORRECTIONS? notes@twinwork.net
$NOTES: /mysql/, v.0.22 2010/12/11 13:44:35 PST /7277/ NkM$
Maintainer: Neafevoc K. Marindale