Setup XServe G5 with 10.4 Client as MySQL Server
Well, the new Quad Xeon 64-bit Intel XServes are
shipping November 2006, so there are deals to be found on PowerPC XServes on
eBay. We needed another XServe to add to our few G4 XServes, so we decided to
get the fastest machine we could on eBay and use it as our dedicated MySQL
server instead of the G4. We got a good deal on a cluster Dual 2.0 GHz G5 on
eBay recently. It came with 10.3 Panther Server, but we want to use Tiger and I
don't want to pay $499 for OS X Server Tiger right now (I'll wait a year when
they are going to be $149 long after Leopard Server has shipped). So we are
going to use Tiger 10.4 client on this headless G5 XServe. This article
documents some of the details in getting this set up......
This cluster node headless G5 had no video card
and so hooking up a monitor for initial setup was out of the question. Even
pulling out the removable drive module and configuring it in another XServe was
out of the question since the G5 had an SATA module that is incompatible with
the Parallel ATA modules in the older G4
XServes.
Installing OS X Tiger Client
To install OS X, we booted it in FireWire startup mode as a virtual external drive to a G4 Powerbook. From the Powerbook, we erased the boot drive on the G5 and installed OS X Client on the G5 XServe. After installation, we allowed the Powerbook to boot from the "G5 External Firewire Drive" and configured Apple Remote Desktop. (IMHO, this is pretty amazing that you can boot a machine from the startup drive of another machine). After that, just shutdown, unplug and let the XServe boot as normal. Then we used Apple Remote Desktop to control the machine. (OSXVnc could be used aswell instead of ARD if you don't have ARD).
After that we just did all the software upgrades to bring it up to 10.4.8 client.
Energy Saver and hard drive sleep has to be turned off in System preferences ..... don't want the server going to sleep!
If required, use NetInfo in /Applications/Utilities to enable the root user for this machine.
Installing MySQL
Although version 5 of MySQL is the general release, we still have not fully tested our apps on MySQL 5, so we are still using MySQL 4.1. For this exercise, I installed mysql-standard-4.1.21-apple-darwin8.6.0-powerpc-64bit. Here is the procedure:
Download MySQL binary.
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 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;
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 loggin in as root, do the following
> use mysql;
> delete from user where user = '';
> flush privileges;
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 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 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)
http://dev.mysql.com/doc/refman/4.1/en/server-options.html (on the web)
Here is my initial config file at /etc/my.cnf on this new server. mysqld needs to be restarted for my.cnf changes to take effect.
# CONFIG INFO
# ===========
# Kieran 10/30/2006
# Config for XServe Dual G5 2.0 GB RAM running OS X 10.4.8 CLIENT
# and MySQL 4.1.21 PowerPC 64bit
# The machine is a dedicated MySQL server, master that replicates.
[mysqld]
# ======= 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 = 512M
# 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 = 1024
# This is buffer allocated by threads for sorting. Improves performance
# of ORDER BY and GROUP BY operations
sort_buffer_size = 16M
# 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
# Number of threads to keep available for use
# Not really important for our situation where we use webobjects applications with persistent connections
# But since it is a dedicated MySQL server, let's decide to keep at least 8 alive
thread_cache_size = 8
# 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
# Log slow queries
# Needs a /var/log/mysql directory with chown mysql:wheel
log_slow_queries=/var/log/mysql/mysql_slow_query.log
# Slow queries to be logged are those greater than n seconds
long_query_time=2
# New tables are innodb unless specified otherwise (Before MySQL 4.1.2 use default-table-type)
default-storage-engine=innodb
# ======= BEGIN REPLICATION MASTER SETTINGS ===================================================
# Replication Master Server (default)
# binary logging is required for replication
# KK: This turns on binary logging and determines the pathname of the log
# Needs a /var/db/repl directory with chown mysql:wheel
log-bin = /var/db/repl/binary-log
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1141
# ======= BEGIN MYISAM SETTINGS ===================================================
# Buffer for REPAIR TABLE or CREATE INDEX operations on MyISAM tables
myisam_sort_buffer_size = 64M
# ======= INNODB SETTINGS ===================================================
# Create a separate idb file per table - allows for flexible database layouts on
innodb_file_per_table
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
# This is also one of the most important peformance variables since
# it allocates how much RAM is used for innodb index and record data. You
# can usually set this between 50% and 80% of the server's total RAM for
# a dedicated server
innodb_buffer_pool_size = 768M
innodb_additional_mem_pool_size = 20M
The last step was to migrate all the databases from the old server to the new server
This took master down for 90 minutes, so it was a case of middle of the night task :-(
Prepare the enterprise ...... I redirected our domain to a "System is down for maintenance. Please try in an hour" first. Then I shutdown webobjects app server.
On the old MySQL 4.0 server:
Shutdown mysql using mysqladmin shutdown
Run thorough maintenance checks using myisamchk
$ su root
$ myisamchk --force --update-state --key_buffer_size=128M --sort_buffer_size=128M --read_buffer_size=16M --write_buffer_size=16M /usr/local/mysql/data/*/*.MYI
To ensure all existing apps and programs connecting to the server work unchanged. The new server is going to get the same fixed IP address and bonjour hostname. So at this stage, change the hostname and IP address of the OLD server.
Launched MySQL again
$ sudo echo
$ sudo mysqld_safe &
Export ALL the databases.
$ mysqldump --all-databases --extended-insert --add-locks --first-slave --add-drop-table --all --quick --disable-keys --quote-names > serverdump.sql
Parameter Explanations
all-databases: all databases
extended-insert: insert multiple lines (big lines), faster dump and faster load
add-locks: add lock/unlock statements around each table dump for faster reload
first-slave: simply lock all tables in all databases for duration of the dump
add-drop-table: add a drop if exists before each table dump
all: same as --create-options for 4.1.2+
quick: read rows one at a time
disable-keys: faster reloading for MyISAM tables only
quote-names: quote all database, table and column names
Finally shutdown the old mysql server with mysqladmin shutdown
On the New MySQL 4.1 Server
Import the server dump (this may take 3x as long as the dump time)
$ mysql -u root -p < serverdump.sql
Immediately update the mysql privileges database since we imported from older version server
$ mysql_fix_privilege_tables --verbose --password=<root-password>
Shutdown the new mysql server
$ mysqladmin -u root -p shutdown
Change the bonjour hostname (System Preferences->Sharing) and the fixed IP address (System Preferences->Networking) to the same as your old mysql server used to be.
Restart the XServe
$ sudo shutdown -r now
Restart your WebObjects app server(s)
Redirect your domain to not offline anymore.
Done.
Installing OS X Tiger Client
To install OS X, we booted it in FireWire startup mode as a virtual external drive to a G4 Powerbook. From the Powerbook, we erased the boot drive on the G5 and installed OS X Client on the G5 XServe. After installation, we allowed the Powerbook to boot from the "G5 External Firewire Drive" and configured Apple Remote Desktop. (IMHO, this is pretty amazing that you can boot a machine from the startup drive of another machine). After that, just shutdown, unplug and let the XServe boot as normal. Then we used Apple Remote Desktop to control the machine. (OSXVnc could be used aswell instead of ARD if you don't have ARD).
After that we just did all the software upgrades to bring it up to 10.4.8 client.
Energy Saver and hard drive sleep has to be turned off in System preferences ..... don't want the server going to sleep!
If required, use NetInfo in /Applications/Utilities to enable the root user for this machine.
Installing MySQL
Although version 5 of MySQL is the general release, we still have not fully tested our apps on MySQL 5, so we are still using MySQL 4.1. For this exercise, I installed mysql-standard-4.1.21-apple-darwin8.6.0-powerpc-64bit. Here is the procedure:
Download MySQL binary.
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 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;
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 loggin in as root, do the following
> use mysql;
> delete from user where user = '';
> flush privileges;
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 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 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)
http://dev.mysql.com/doc/refman/4.1/en/server-options.html (on the web)
Here is my initial config file at /etc/my.cnf on this new server. mysqld needs to be restarted for my.cnf changes to take effect.
# CONFIG INFO
# ===========
# Kieran 10/30/2006
# Config for XServe Dual G5 2.0 GB RAM running OS X 10.4.8 CLIENT
# and MySQL 4.1.21 PowerPC 64bit
# The machine is a dedicated MySQL server, master that replicates.
[mysqld]
# ======= 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 = 512M
# 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 = 1024
# This is buffer allocated by threads for sorting. Improves performance
# of ORDER BY and GROUP BY operations
sort_buffer_size = 16M
# 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
# Number of threads to keep available for use
# Not really important for our situation where we use webobjects applications with persistent connections
# But since it is a dedicated MySQL server, let's decide to keep at least 8 alive
thread_cache_size = 8
# 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
# Log slow queries
# Needs a /var/log/mysql directory with chown mysql:wheel
log_slow_queries=/var/log/mysql/mysql_slow_query.log
# Slow queries to be logged are those greater than n seconds
long_query_time=2
# New tables are innodb unless specified otherwise (Before MySQL 4.1.2 use default-table-type)
default-storage-engine=innodb
# ======= BEGIN REPLICATION MASTER SETTINGS ===================================================
# Replication Master Server (default)
# binary logging is required for replication
# KK: This turns on binary logging and determines the pathname of the log
# Needs a /var/db/repl directory with chown mysql:wheel
log-bin = /var/db/repl/binary-log
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1141
# ======= BEGIN MYISAM SETTINGS ===================================================
# Buffer for REPAIR TABLE or CREATE INDEX operations on MyISAM tables
myisam_sort_buffer_size = 64M
# ======= INNODB SETTINGS ===================================================
# Create a separate idb file per table - allows for flexible database layouts on
innodb_file_per_table
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
# This is also one of the most important peformance variables since
# it allocates how much RAM is used for innodb index and record data. You
# can usually set this between 50% and 80% of the server's total RAM for
# a dedicated server
innodb_buffer_pool_size = 768M
innodb_additional_mem_pool_size = 20M
The last step was to migrate all the databases from the old server to the new server
This took master down for 90 minutes, so it was a case of middle of the night task :-(
Prepare the enterprise ...... I redirected our domain to a "System is down for maintenance. Please try in an hour" first. Then I shutdown webobjects app server.
On the old MySQL 4.0 server:
Shutdown mysql using mysqladmin shutdown
Run thorough maintenance checks using myisamchk
$ su root
$ myisamchk --force --update-state --key_buffer_size=128M --sort_buffer_size=128M --read_buffer_size=16M --write_buffer_size=16M /usr/local/mysql/data/*/*.MYI
To ensure all existing apps and programs connecting to the server work unchanged. The new server is going to get the same fixed IP address and bonjour hostname. So at this stage, change the hostname and IP address of the OLD server.
Launched MySQL again
$ sudo echo
$ sudo mysqld_safe &
Export ALL the databases.
$ mysqldump --all-databases --extended-insert --add-locks --first-slave --add-drop-table --all --quick --disable-keys --quote-names > serverdump.sql
Parameter Explanations
all-databases: all databases
extended-insert: insert multiple lines (big lines), faster dump and faster load
add-locks: add lock/unlock statements around each table dump for faster reload
first-slave: simply lock all tables in all databases for duration of the dump
add-drop-table: add a drop if exists before each table dump
all: same as --create-options for 4.1.2+
quick: read rows one at a time
disable-keys: faster reloading for MyISAM tables only
quote-names: quote all database, table and column names
Finally shutdown the old mysql server with mysqladmin shutdown
On the New MySQL 4.1 Server
Import the server dump (this may take 3x as long as the dump time)
$ mysql -u root -p < serverdump.sql
Immediately update the mysql privileges database since we imported from older version server
$ mysql_fix_privilege_tables --verbose --password=<root-password>
Shutdown the new mysql server
$ mysqladmin -u root -p shutdown
Change the bonjour hostname (System Preferences->Sharing) and the fixed IP address (System Preferences->Networking) to the same as your old mysql server used to be.
Restart the XServe
$ sudo shutdown -r now
Restart your WebObjects app server(s)
Redirect your domain to not offline anymore.
Done.
Posted: Mon - October 30, 2006 at 01:37 PM
Quick Links
Calendar
| Sun | Mon | Tue | Wed | Thu | Fri | Sat |
Categories
Archives
XML/RSS Feed
Statistics
Total entries in this blog:
Total entries in this category:
Published On: Nov 01, 2006 10:29 AM
Total entries in this category:
Published On: Nov 01, 2006 10:29 AM