Installing MySQL on OS X 10.4 Tiger
This article details how to setup MySQL 4.1 or
later on Mac OS X Tiger 10.4.x. Additionally it tells you how to add java
support and configure MySQL so that all new database tables use the InnoDB
transaction-safe database engine instead of the default MyISAM engine. This is
essential if you are writing applications with technologies such as WebObjects
that automatically utilize the ACID commit or rollback capabilities of a
transaction-safe database.
Installing
MySQL
This exercise used MySQL 4.1, but for MySQL 5.0 should work just as well.
Download MySQL binary from one of the following pages. You will see downloads for each processor type (PowerPC, 32-bit G4, PowerPC, 64-bit G5 and Intel x86). Also for each processor , version 4.1 has a Standard, Max and Debug version. The Standard download is all you need.
4.1 -> http://dev.mysql.com/downloads/mysql/4.1.html
5.0 -> http://dev.mysql.com/downloads/mysql/5.0.html
Install the mysql-blah-blah-blah.pkg from the MySQL downloaded .dmg
If this is a first time installation, install the MySQL Startup Item .pkg from the MySQL disk image.
Restart the Mac ..... this will cause the MySQL Startup Item to automatically start MySQL
MySQL Security Tasks
The default installation has a root user (mysql root, NOT your computer root) with no password, so we have to set a password for root to protect our server.
Type this in the terminal:
cd /usr/local/mysql/bin
If this is first time install, type
./mysql -u root (Log in as root (initial install has no password) and set a password for root )
At the mysql prompt, type the following replacing password with your password:
> grant all on *.* to root@localhost identified by 'password' with grant option;
> flush privileges;
now type:
> exit;
and test the login again with the password
./mysql -u root -p
enter the password you set for the mysql root user when prompted.
Next we need to delete the 'anonymous' user that usually exists by default in MySQL
After logging in as root, do the following
> use mysql;
> delete from user where user = '';
> flush privileges;
> exit
Binary Command Path Configuration
The next thing we need to do is configure our command path so that we refer to the mysql binaries that we installed without having to type full paths.
Using pico, vi or similar, open and edit /etc/profile to that the path variable has /usr/local/mysql/bin as the first element of the path like so:
# System-wide .profile for sh(1)
PATH="/usr/local/mysql/bin:/bin:/sbin:/usr/bin:/usr/sbin"
export PATH
if [ "${BASH-no}" != "no" ]; then
[ -r /etc/bashrc ] && . /etc/bashrc
fi
Configuring MySQL using my.cnf
The case of a new installation and before you create any databases is the best time to configure your my.cnf file. There are a few example my.cnf files provided at:
/usr/local/mysql/support-files
Also, the parameters are documented at:
$ mysqld --verbose --help (on the command line) or at
http://dev.mysql.com/doc/refman/4.1/en/server-options.html (on the web)
Here is suggested initial config file at /etc/my.cnf for typical development machine. Just use it for starters. This config sets InnoDB as the default engine for all new tables and configures InnoDB aswell. So go ahead and put this in a text file named my.cnf and put it in /etc directory. mysqld needs to be restarted for my.cnf changes to take effect. See after this listing how to properly stop and restart MySQL form the command line.
How to do it
$ cd /etc
$ sudo pico my.cnf
(next copy the listing form below and paste into pico in terminal
Save in pico with ctrl-O
## BEGIN /etc/my.cnf LISTING---------------------------------------
# CONFIG INFO
# ===========
# Development Machine
# server settings
[mysqld]
default-table-type=innodb
# Turn on MySQL query logging
#log=/var/log/mysql/mysql.log
# logging slow queries
log_slow_queries=/var/log/mysql/mysql_slow_query.log
long_query_time=1
# ======= BEGIN GLOBAL SETTINGS ===================================================
# Ideally key_buffer (aka key_buffer_size) should be as large as we can afford and
# ideally should be equal to the sum of all .MYI files' sizes.
# This is one of the most important performance tuning variables for queries that use indexes
key_buffer_size = 128M
# This variable should be as big as the biggest BLOB value we plan to use
max_allowed_packet = 16M
# The max number of opened tables for all threads
table_cache = 512
# This is buffer allocated by threads for sorting. Improves performance
# of ORDER BY and GROUP BY operations
sort_buffer_size = 32M
# This is a buffer used for sequential table scans (I guess where an index
# is not used for a search ... may help scans that create new indexes aswell)
read_buffer_size = 20M
# Since WebObjects uses EOF to cache database data, it is not so beneficial to have the mysql server caching aswell
# So setting it to zero allocates no cache size and type set to OFF
query_cache_type = 0
query_cache_size = 0
# Uncomment the following if you want to log all SQL updates
#log-bin
# Uncomment the following if you are NOT using BDB tables
skip-bdb
###############################################
# All MyISAM Specific Configuration here
###############################################
myisam_sort_buffer_size=32M
# 4 GB ...... disk must have more than this free!!!
myisam_max_sort_file_size=4G
bulk_insert_buffer_size=16M
###############################################
# All Innodb Configuration here
###############################################
# http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html
innodb
innodb_additional_mem_pool_size = 4M
innodb_autoextend_increment = 20
innodb_buffer_pool_size = 64M
innodb_data_home_dir = /usr/local/mysql/data/
innodb_file_per_table
# For the greatest possible durability and consistency in a replication setup using InnoDB with transactions
# masters in replication setup should use the following 3 params with settings as shown if applicable
#innodb_flush_log_at_trx_commit = 1
#sync_binlog=1
#innodb_safe_binlog
# For development on local machine, I use these 'data-loss risk' properties for performance since no replication is going on
# and worst case is that I lose 1 second (default flush interval) of development transactions
innodb_flush_log_at_trx_commit = 0
sync_binlog=0
innodb_log_group_home_dir = /usr/local/mysql/data/
innodb_log_arch_dir = /usr/local/mysql/data/
innodb_log_file_size = 16M
[mysqldump]
quick
[mysql]
# Remove the next comment character if you are not familiar with SQL
# safe-updates
prompt=(\\u@\\h)\\ \\d>\\_
## END /etc/my.cnf LISTING ---------------------------------------
Stopping and Restarting MySQL
To stop MySQL, do this in terminal
$ mysqladmin -u root -p shutdown
(It will ask for your mysql root password, NOT your machine's root password!)
To start MySQL, do this:
$ sudo echo
(enter your computer root password)
$ sudo mysqld_safe --user=mysql &
FYI: The & at the end launches it as a background shell process. This is important to release your terminal for your further use. The sudo echo beforehand is to prevent being asked for a password on the second line since we are sending to background where the password cannot be entered.
Adding Java Development Capability
For this, we need to simply download the latest driver (I suggest 3.0.XX latest ) and put the jar file in /Library/Java/Extensions as shown here
http://homepage.mac.com/kelleherk/iblog/C711669388/E70925289/index.html
Your mysql setup is now ready for java (and WebObjects) development.
Feedback to kieran_lists at mac dot com
This exercise used MySQL 4.1, but for MySQL 5.0 should work just as well.
Download MySQL binary from one of the following pages. You will see downloads for each processor type (PowerPC, 32-bit G4, PowerPC, 64-bit G5 and Intel x86). Also for each processor , version 4.1 has a Standard, Max and Debug version. The Standard download is all you need.
4.1 -> http://dev.mysql.com/downloads/mysql/4.1.html
5.0 -> http://dev.mysql.com/downloads/mysql/5.0.html
Install the mysql-blah-blah-blah.pkg from the MySQL downloaded .dmg
If this is a first time installation, install the MySQL Startup Item .pkg from the MySQL disk image.
Restart the Mac ..... this will cause the MySQL Startup Item to automatically start MySQL
MySQL Security Tasks
The default installation has a root user (mysql root, NOT your computer root) with no password, so we have to set a password for root to protect our server.
Type this in the terminal:
cd /usr/local/mysql/bin
If this is first time install, type
./mysql -u root (Log in as root (initial install has no password) and set a password for root )
At the mysql prompt, type the following replacing password with your password:
> grant all on *.* to root@localhost identified by 'password' with grant option;
> flush privileges;
now type:
> exit;
and test the login again with the password
./mysql -u root -p
enter the password you set for the mysql root user when prompted.
Next we need to delete the 'anonymous' user that usually exists by default in MySQL
After logging in as root, do the following
> use mysql;
> delete from user where user = '';
> flush privileges;
> exit
Binary Command Path Configuration
The next thing we need to do is configure our command path so that we refer to the mysql binaries that we installed without having to type full paths.
Using pico, vi or similar, open and edit /etc/profile to that the path variable has /usr/local/mysql/bin as the first element of the path like so:
# System-wide .profile for sh(1)
PATH="/usr/local/mysql/bin:/bin:/sbin:/usr/bin:/usr/sbin"
export PATH
if [ "${BASH-no}" != "no" ]; then
[ -r /etc/bashrc ] && . /etc/bashrc
fi
Configuring MySQL using my.cnf
The case of a new installation and before you create any databases is the best time to configure your my.cnf file. There are a few example my.cnf files provided at:
/usr/local/mysql/support-files
Also, the parameters are documented at:
$ mysqld --verbose --help (on the command line) or at
http://dev.mysql.com/doc/refman/4.1/en/server-options.html (on the web)
Here is suggested initial config file at /etc/my.cnf for typical development machine. Just use it for starters. This config sets InnoDB as the default engine for all new tables and configures InnoDB aswell. So go ahead and put this in a text file named my.cnf and put it in /etc directory. mysqld needs to be restarted for my.cnf changes to take effect. See after this listing how to properly stop and restart MySQL form the command line.
How to do it
$ cd /etc
$ sudo pico my.cnf
(next copy the listing form below and paste into pico in terminal
Save in pico with ctrl-O
## BEGIN /etc/my.cnf LISTING---------------------------------------
# CONFIG INFO
# ===========
# Development Machine
# server settings
[mysqld]
default-table-type=innodb
# Turn on MySQL query logging
#log=/var/log/mysql/mysql.log
# logging slow queries
log_slow_queries=/var/log/mysql/mysql_slow_query.log
long_query_time=1
# ======= BEGIN GLOBAL SETTINGS ===================================================
# Ideally key_buffer (aka key_buffer_size) should be as large as we can afford and
# ideally should be equal to the sum of all .MYI files' sizes.
# This is one of the most important performance tuning variables for queries that use indexes
key_buffer_size = 128M
# This variable should be as big as the biggest BLOB value we plan to use
max_allowed_packet = 16M
# The max number of opened tables for all threads
table_cache = 512
# This is buffer allocated by threads for sorting. Improves performance
# of ORDER BY and GROUP BY operations
sort_buffer_size = 32M
# This is a buffer used for sequential table scans (I guess where an index
# is not used for a search ... may help scans that create new indexes aswell)
read_buffer_size = 20M
# Since WebObjects uses EOF to cache database data, it is not so beneficial to have the mysql server caching aswell
# So setting it to zero allocates no cache size and type set to OFF
query_cache_type = 0
query_cache_size = 0
# Uncomment the following if you want to log all SQL updates
#log-bin
# Uncomment the following if you are NOT using BDB tables
skip-bdb
###############################################
# All MyISAM Specific Configuration here
###############################################
myisam_sort_buffer_size=32M
# 4 GB ...... disk must have more than this free!!!
myisam_max_sort_file_size=4G
bulk_insert_buffer_size=16M
###############################################
# All Innodb Configuration here
###############################################
# http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html
innodb
innodb_additional_mem_pool_size = 4M
innodb_autoextend_increment = 20
innodb_buffer_pool_size = 64M
innodb_data_home_dir = /usr/local/mysql/data/
innodb_file_per_table
# For the greatest possible durability and consistency in a replication setup using InnoDB with transactions
# masters in replication setup should use the following 3 params with settings as shown if applicable
#innodb_flush_log_at_trx_commit = 1
#sync_binlog=1
#innodb_safe_binlog
# For development on local machine, I use these 'data-loss risk' properties for performance since no replication is going on
# and worst case is that I lose 1 second (default flush interval) of development transactions
innodb_flush_log_at_trx_commit = 0
sync_binlog=0
innodb_log_group_home_dir = /usr/local/mysql/data/
innodb_log_arch_dir = /usr/local/mysql/data/
innodb_log_file_size = 16M
[mysqldump]
quick
[mysql]
# Remove the next comment character if you are not familiar with SQL
# safe-updates
prompt=(\\u@\\h)\\ \\d>\\_
## END /etc/my.cnf LISTING ---------------------------------------
Stopping and Restarting MySQL
To stop MySQL, do this in terminal
$ mysqladmin -u root -p shutdown
(It will ask for your mysql root password, NOT your machine's root password!)
To start MySQL, do this:
$ sudo echo
(enter your computer root password)
$ sudo mysqld_safe --user=mysql &
FYI: The & at the end launches it as a background shell process. This is important to release your terminal for your further use. The sudo echo beforehand is to prevent being asked for a password on the second line since we are sending to background where the password cannot be entered.
Adding Java Development Capability
For this, we need to simply download the latest driver (I suggest 3.0.XX latest ) and put the jar file in /Library/Java/Extensions as shown here
http://homepage.mac.com/kelleherk/iblog/C711669388/E70925289/index.html
Your mysql setup is now ready for java (and WebObjects) development.
Feedback to kieran_lists at mac dot com
Posted: Thursday - July 19, 2007 at 09:52 AM
Quick Links
Statistics
Total entries in this blog:
Total entries in this category:
Published On: Dec 29, 2008 02:27 PM
Total entries in this category:
Published On: Dec 29, 2008 02:27 PM