MySQL 5.1.30 is the GA version of the MySQL 5.1 software version. The 5.1 release has some key features that users are going to like which include:
- Table and Index Partitioning
- Row-based and Mixed Replication
- The embedded libmysqld library
- Improved XML features with additional XPath support
- Event Scheduler
- Upgraded Advisors for the MySQL Enterprise Monitor
- mysqlslap - a load emulator is pat of the MySQL distribution software
- Server Log Tables - have more flexbility and more dynamic capability
- MySQL Cluster - is a separate distribution from the MySQL 5.1 distribution. MySQL Cluster 6.2 and 6.3 can be installed separately.
- The mysql_upgrade program offers a much easier upgrade process.
The MySQL Optimial Configuration Architecture (MOCA) is a set of guidelines and best practices I developed to achieve the following goals:
- Minimize points for failure.
- Support multiple software installs on the same machine.
- Support multiple database servers on the same machine.
- Separate software files, data files, binary logs and administration files.
- Reduce down time.
- Reduce administration costs.
- Provide a consistent configuration platform across an enterprise.
- Separate database configuration files from operating system configuration files.
Installing MySQL: Linux, Solaris, MacOS
One of the benefits of the MOCA installation is this set of guidelines provides a very consistent install on different Linux and Unix operating systems. There are a few subtle differences such as how to add a new user on MacOS versus adding a user on Linux/Unix. This installation will demonstrate a MOCA installation on the Mac OS. These steps can also be used to perform an installation on Linux or Unix system.
Installing MySQL 5.1.30 on Mac OS 10.5
These instructions will walk you through a 5.1.30 installation of MySQL using MOCA on the Mac OS 10.5. These instructions can be followed for any MySQL 5.1.xx install. The Mac OS installation will be similar to a Solaris or Linux installation. Go to the MySQL documentation to review instructions if you are using a different operating system.
Review my blog entry "The MySQL Optimial Configuration Architecture" for more details on MOCA.
The following list outlines the steps performed to install MySQL on Mac OS. I recommend reading the blog "Top Ten Things to do before installing MySQL" further down on this site before beginning this installation.
- Setup the mysql operating system user id. Define the user's home directory, default shell, password, etc. It may be easier to delete the preinstalled mysql user and recreate it.
- Set up the operating system and directory structures (physical storage) for running MySQL. Change all directories where MySQL files will reside to be owned by the mysql user.
- Login in as the mysql operating system user and perform the installation and configuration as the mysql user.
- Install the MySQL software in the MYSQL_HOME directory.
- Create a startup file (my.cnf). Set up the locations for all mysql database files.
- Run the mysql_install_db script to set up the MySQL data directory.
- Start the MySQL server instance by running mysqld_safe.
- Test the MySQL Server instance by running the perl script mysql-test-run.pl.
- Secure the mysql password environment with the mysql_secure_installation script.
- Login in using the mysql client and verify the installation. Review the data files, log files, binary log, error logs, etc.
- Define a server startup method. The script mysql.server is a likely option.
- Define a backup and recovery strategy. Test your back and recovery processes.
- Have fun with MySQL. :)
Hardware: MacBook Pro laptop , 2GB of memory, dual core
Operating System: Mac OS 10.5.5
Disk: 160 GB
MySQL: RC 5.1.30 for Mac OS X (TAR packages)
Remove any mysql files in the following directories (especially any my.cnf files):
Verify the setup of the mysql operating system user account. A mysql user may already exist in the /etc/passwd file. It's probably easiest to drop and recreate the mysql user. Mac OS did something weird with the mysql user in 10.5 by naming it _mysql. Dropping the user and recreating is as mysql cleans up a lot of little issues. Go to System Preferences | Accounts and drop the mysql user and recreate it. Make sure and set the proper group name, login shell, etc. for the new mysql user account.
Dependent on the default shell you pick for the mysql user (bash, ksh, etc) set the following environmental variables in the mysql user's profile file (ex: .bash_profile).
Define the directory structure for the MySQL environment:
Bring up a terminal window as the root or administrator userid.
Use the mkdir command to create the directories listed below. I have added little notes about the purpose of each of these directories. Do not include the notes when you type the commands.
Create the directory /opt/mysql for placing the MySQL software.
# mkdir -p /opt/mysql/software
Go to http://dev.mysql.com and choose the MySQL softwware to download (mysql-5.1.30-osx10.4-i686.tar.gz). Place this zip file in the /opt/mysql directory.
# mkdir -p /db01/mysql/mysql01/data # MySQL data directory
# mkdir -p /db02/mysql/mysql01/binlogs # location of binary log files
# mkdir -p /db03/mysql/mysql01/ # administration directory HOME and location of PID file
# mkdir /db03/mysql/mysql01/startup # location of my.cnf files
# mkdir /db03/mysql/mysql01/run # location of socket file
# mkdir /db03/mysql/mysql01/errors # location of error file
# mkdir /db03/mysql/mysql01/logs #location for general and slow logs
# mkdir /db03/mysql/mysql01/scripts # administration scripts
# mkdir /db03/mysql/mysql01/sql # generic sql code
# mkdir -p /db04/mysql/mysql01/backups # backup files
# mkdir /db04/mysql/mysql01/exports # exports
# mkdir /db04/mysql/mysql01/misc # miscellaneous backups (single tables, etc)
Login as your administrator userid or root userid to run the following commands. Be extremely careful you do this correctly!
Set up the MySQL software
Do NOT perform the MySQL installation as the root or OS admin user. Login in as the mysql operating system user id and verify.
# su - mysql
$ who am i
mysql ttyp1 Nov 10:19
Go to the /opt/mysql directory then run the following commands to set up the MySQL HOME directory (MYSQL_HOME).
The symbolic link will set /opt/mysql/5.1.30 as the software location for MySQL.
$ cd /opt/mysql
$ tar zxvf mysql-5.1.30-osx10.4-i686.tar.gz
Create a new my.cnf file
Create a new my.cnf file from one of the sample files in the $MYSQL_HOME/support-files.
$ cd $MYSQL_HOME
$ cp ./support-files/my-small.cnf my.cnf
Add the following parameters to the my.cnf file.
[client] # global options for every client:
Setup the MySQL environment
$ scripts/mysql_install_db --defaults-file=/db03/mysql/mysql01/startup/my.cnf
$ cd /db01/mysql/mysql01/data
$ ls -la
Start the MySQL daemon using the new startup my.cnf file.
There are multiple ways to start up and shutdown a MySQL server. Below I use the mysqld_safe and mysqladmin commands.
$ cd /opt/mysql/5.1.30
$ bin/mysqld_safe --defaults-file=/db03/mysql/mysql01/startup/my.cnf &
$ ln -s /db03/mysql/mysql01/startup/my.cnf my.cnf
Test the MySQL daemon with mysql-test-run.pl and review the output.
This test is optional. The script takes a long time to run. :)
$ cd /opt/mysql/5.1.30/mysql-test
$ perl mysql-test-run.pl > mysql-test-run.output
The mysqladmin command can be used to shutdown the database server.
$ mysqladmin -uroot -p shutdown
Login to the mysql server and secure the password environment.
# Login to mysql and see that no passwords have been setup yet.
$ mysql -uroot
mysql> select host, user, password from user;
# Run the following script and set the values listed below. When prompted enter new password for the mysql database userid. Do NOT set this to the same value as the operating system userid mysql.
Set root password? [Y/n] y
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] y
Remove test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] y
#Login to your new mysql server environment. Never type a database password on an operating system prompt. Verify a password now exists for the mysql userid on the localhost.
$ mysql -uroot -p
mysql> select host, user, password from mysql.user;
Post Installation Steps
Login in using the mysql client and verify the installation. Review the data files, log files, binary log, error logs, etc. Make sure you are comfortable with the new environment.
Define a server startup method. The script mysql.server is a likely option. Test the startup and shutdown processes.
Define a backup and recovery strategy. Test your back and recovery processes.
Remember there are the following userids in this environment:
- root - Linux/Unix operating system user id.
- root - MySQL database user id.
- mysql - Linux/Unix operating system user id.
This is the MySQL GUI Tools Bundle which includes the following:
- MySQL Administrator
- MySQL Query Browser
- MySQL Migration Toolkit (currently not available for Mac OS in bundle)
Before launching the MySQL Administrator or Query Browser, if you are using a non-default sock, click on advanced options on the login screen and enter the socket file you defined in your my.cnf file. Then login to either of these two tools.
These instructions walk through the basic steps for setting up a MySQL server on a Mac OS. Remember this article discusses a basic install. It does not include setting up memory, InnoDB server parameters, etc. This will allow you to have fun in the documentation. :)
Have fun with MySQL. :)