>> Friday, 14 November 2008
Chances are if you're building websites you'll use some kind of data along the way, but where to keep it? the obvious choice of course is a database. Now there are a ton of databases out there that you could use but to get us started I'm going to use MySQL community edition a fantastic open source (and free!) database engine used by many hundreds of thousand sites across the web for their data storage needs.
Go to mysql.com and download mysql 5.0 for mac os x 10.5 (x86_64)
Once downloaded mount the file and run the mysql installer using the provided defaults.
When the installer is finished run the other installer for the mysqlstartupitem and again install using the defaults.
In the disk image you will also see a file called mysql.prefpane which is a handy preference pane that goes into your system preferences and lets you stop and start the mysql server with the push of a button.
To install it copy the file to /Library/PreferencePanes
Open system preferences and you'll see the new pane in the bottom row. click it and start the mysql server.
Now we need to secure the default mysql login so open terminal and run the following commands
First we need to add mysql bin to the path
$ vi ~/.profile
Add this line
Save and quit then run the following script bundled with MySQL to secure the default installation
$ sudo /usr/local/mysql/bin/mysql_secure_installation
This will run you through securing your installation, select the following options:
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQLNow that we have MySQL Installed and pretty well secured you can access it from the command line using
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
Set root password? [Y/n] y
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
Remove anonymous users? [Y/n] Y
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] Y
By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] Y
- Dropping test database...
- Removing privileges on test database...
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
All done! If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!
$ mysql -u root -p [enter]
Once logged in you'll see a prompt like this
Welcome to the MySQL monitor. Commands end with ; or \g.into which you can then run any commands you want to create databases, users etc.
Your MySQL connection id is 2
Server version: 5.0.67 MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
However I would advise using a GUI tool so you can administer your databases in a much nicer and easier fashion, there are a number of them out there for the mac (like Aqua Data Studio, or MySQL.com's very own Mysql Administrator and Query Browser tools)
In the next post of this series we'll be configuring Apache to use PHP so we can install an extremely handy web-based MySQL administration script called phpMyAdmin which we can use on each of our sites to do pretty much any database task you may wish to think of.
Until then stay loose :)