How to setup multiple installations of MySQL & PostgreSQL
We need to use:
- different install dirs
- different data dirs
- different unix sockets
- different ports
MySQL
- Extract the MySQL binary release to /usr/local/mysql-version.number
Create my.cnf in $BASEDIR and set the appropiate port & socket:
[mysqld] port = 3306 socket = /tmp/mysql-5.1.sock
- Install the database files:
# scripts/mysql_install_db --user=mysql --datadir=data
- Start the database:
# ./bin/mysqld_safe --defaults-file=/usr/local/mysql-5.1/my.cnf &
- To connect to the database you will need to pass the correct socket (or port) to use:
# ./bin/mysql --socket=/tmp/mysql-5.1.sock -uroot -p
- Copy the startup file from support-files to /etc/init.d and modify as appropriate
# If you change base dir, you must also change datadir. These may get # overwritten by settings in the MySQL configuration files. basedir=/usr/local/mysql-5.1 datadir=/usr/local/mysql-5.1/data
postgresql
- change prefix:
--prefix=/usr/local/pgsql-8.0.12
- change default port
--with-pgport=5430
- create the user:
# useradd -r -m -d /var/lib/pgsql postgres
- set up the cluster directory:
# mkdir -p /var/lib/pgsql/8.0.12/data
# chown -R postgres: /var/lib/pgsql
# su postgres
bash-3.00$ /usr/local/pgsql-8.0.12/bin/initdb -W -A md5 -D /var/lib/pgsql/8.0.12/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale en_GB.UTF-8.
The default database encoding has accordingly been set to UNICODE.
fixing permissions on existing directory /var/lib/pgsql/8.0.12/data ... ok
creating directory /var/lib/pgsql/8.0.12/data/global ... ok
creating directory /var/lib/pgsql/8.0.12/data/pg_xlog ... ok
creating directory /var/lib/pgsql/8.0.12/data/pg_xlog/archive_status ... ok
creating directory /var/lib/pgsql/8.0.12/data/pg_clog ... ok
creating directory /var/lib/pgsql/8.0.12/data/pg_subtrans ... ok
creating directory /var/lib/pgsql/8.0.12/data/base ... ok
creating directory /var/lib/pgsql/8.0.12/data/base/1 ... ok
creating directory /var/lib/pgsql/8.0.12/data/pg_tblspc ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 1000
creating configuration files ... ok
creating template1 database in /var/lib/pgsql/8.0.12/data/base/1 ... ok
initializing pg_shadow ... ok
Enter new superuser password:
Enter it again:
setting password ... ok
enabling unlimited row size for system tables ... ok
initializing pg_depend ... ok
creating system views ... ok
loading pg_description ... ok
creating conversions ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
Success. You can now start the database server using:
/usr/local/pgsql-8.0.12/bin/postmaster -D /var/lib/pgsql/8.0.12/data
or
/usr/local/pgsql-8.0.12/bin/pg_ctl -D /var/lib/pgsql/8.0.12/data -l logfile start
- Edit and copy the start script:
# cp /usr/local/src/postgresql-8.0.12/contrib/start-scripts/linux /etc/init.d/postgresql-8.0 # chkconfig --add postgresql-8.0 # chmod +x /etc/init.d/postgresql-8.0