Instances

linux:~ # ls -1d /usr/local/mysql*

/usr/local/mysql-max-4.0.25-pc-linux-gnu-i686

/usr/local/mysql-max-4.1.15-pc-linux-gnu-i686-glibc23

/usr/local/mysql-max-5.0.15-linux-i686-glibc23

I keep these configurations runnable, by maintaining a template my.cnf file inside their respective directories. But for customer instances, I use this handy shell script "mysql-instance" (Download mysql-instance.txt) that will create a runnable instance in a target directory for me.

linux:~ # mkdir /tmp/mysql

linux:~ # mysql-instance /tmp/mysql/master 5.0 3310

Creating directory: /tmp/mysql/master

Creating 5.0 instance

        from /usr/local/mysql-max-5.0.15-linux-i686-glibc23

        in /tmp/mysql/master,

        listening on port 3310.

linux:~ # mysql-instance

Create instance in: /tmp/mysql/slave

Creating directory: /tmp/mysql/slave

Version to use: 5.0

Port to use: 3311

Creating 5.0 instance

        from /usr/local/mysql-max-5.0.15-linux-i686-glibc23

        in /tmp/mysql/slave,

        listening on port 3311.

As you can see, the script can be called with or without parameters. The parameters are the target directory, the version you want (4.0, 4.1, 5.0 or anything else that matches "/usr/local/mysql-*what you request*") and the port where you want this instance to listen.

The script will then create the target directory, copy the mysql/ database and the my.cnf over, and will edit the my.cnf appropriately, adjusting the port and socket and the server-id (which will be the port number). It will also drop a script "start" and a script "stop" into the instance directory, and will create wrappers for mysql, mysqladmin, mysqldump, mysqlbinlog and mysqldumpslow by appending the port number. The wrapper scripts will also be symlinked into /usr/local/bin so that they are in the system search path, and the start and stop scripts will be in the search path as mysqlstart-portnumber and mysqlstop-portnumber

To start the new instance, just mysqlstart-portnumber it and use mysql-portnumber to connect to the instance. To stop the instance, just mysqlstop-portnumber it. To remove it, just remove the instance directory and all symlinks in /usr/local/bin ending in that port number ("rm /usr/local/bin/*portnumber").

And here is how you set up an example for replication for a customer:

1. Create a master server

linux:~ # mkdir /tmp/mysql

linux:~ # mysql-instance /tmp/mysql/master 5.0 3310

Creating directory: /tmp/mysql/master

Creating 5.0 instance

        from /usr/local/mysql-max-5.0.15-linux-i686-glibc23

        in /tmp/mysql/master,

        listening on port 3310.

linux:~ # mysqlstart-3310

Starting mysqld daemon with databases from /tmp/mysql/master/data

2. Create a slave server

linux:~ # mysql-instance /tmp/mysql/slave 5.0 3311

Creating directory: /tmp/mysql/slave

Creating 5.0 instance

        from /usr/local/mysql-max-5.0.15-linux-i686-glibc23

        in /tmp/mysql/slave,

        listening on port 3311.

linux:~ # mysqlstart-3311

Starting mysqld daemon with databases from /tmp/mysql/slave/data

3. Create a database on the master and insert a bit of data

linux:~ # mysqladmin-3310 create bla

linux:~ # cat <<EOF | mysql-3310

create table t ( id serial, d varchar(80) not null );

insert into t ( d ) values ( "data" ), ( "more data" ), ("even more data");

EOF

4. Create a replication slave account on the master

linux:~ # echo 'grant replication slave on *.* to "repl"@"127.0.0.1" identified by "1234' | mysql-3310

5. Make the slave aware of that account and the master servers location

linux:~ # echo 'change master to master_user="repl", master_password="1234", master_host="127.0.01", master_port=3310' | mysql-3311

6. Mirror the master to the slave

linux:~ # mysqladmin-3311 create bla

linux:~ # mysqldump-3310 --master-data bla | mysql-3311 bla

7. Go

linux:~ # echo "start slave; show slave status\G" | mysql-3311

*************************** 1. row ***************************

             Slave_IO_State: Waiting for master to send event

                Master_Host: 127.0.01

                Master_User: repl

                Master_Port: 3310

              Connect_Retry: 60

            Master_Log_File: linux-bin.000001

        Read_Master_Log_Pos: 733

             Relay_Log_File: linux-relay-bin.000002

              Relay_Log_Pos: 235

      Relay_Master_Log_File: linux-bin.000001

           Slave_IO_Running: Yes

          Slave_SQL_Running: Yes

            Replicate_Do_DB:

        Replicate_Ignore_DB:

         Replicate_Do_Table:

     Replicate_Ignore_Table:

    Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

                 Last_Errno: 0

                 Last_Error:

               Skip_Counter: 0

        Exec_Master_Log_Pos: 733

            Relay_Log_Space: 235

            Until_Condition: None

             Until_Log_File:

              Until_Log_Pos: 0

         Master_SSL_Allowed: No

         Master_SSL_CA_File:

         Master_SSL_CA_Path:

            Master_SSL_Cert:

          Master_SSL_Cipher:

             Master_SSL_Key:

      Seconds_Behind_Master: 0

And thus we get a replication demo running for a customer in less than 60 seconds.

8. Wreck it

linux:~ # mysqlstop-3310; mysqlstop-3311; rm -rf /tmp/mysql

STOPPING server from pid file /tmp/mysql/master/data/linux.pid

051206 18:08:09  mysqld ended



STOPPING server from pid file /tmp/mysql/slave/data/linux.pid

051206 18:08:11  mysqld ended



linux:~ # rm /usr/local/bin/*3310 /usr/local/bin/*3311