Reboot

Replication is one of the MySQL technology to replicate the whole database, whether it will be used for load balancing, data backup, or High Availability (need 3rd party software or script to switch the connection).

This post sill show you a step-by-step installation of MySQL in Sun Solaris. I’m using MySQL 5.0.51a in this post, but it is applicable to other versions. First, download the MySQL from http://dev.mysql.com/downloads or your favorite mirror closest to you.

Some of these steps are optional, some of them are workarounds of bugs.

I use “shell >” prompt to indicate that the command executed in shell, and “mysql >” prompt to indicate the command executed in MySQL prompt.

So, these are the steps:

1. Add the system’s user and group “mysql”

   root# groupadd mysql
   root# useradd -g mysql -d /usr/local/mysql mysql

2. Install the package using pkgadd

   root# pkgadd -d mysql-5.0.51a-solaris10-i386.pkg

   and aswer all the questions asked in the installation process. When the installation has finished, please check the messages of installation process, whether the script mysql_install_db is successfully executed, because at the moment I wrrite this blog post, I found that the MySQL package I’m using has a problem on the script. The next steps are workarounds for this problem.

3. Configure MySQL using copy of the default configuration from the package. I was using my-medium.cnf as a template.

   root# cd /usr/local/mysql
   root# cp support-files/my-medium.cnf ./my.cnf

4. Edit the new my.cnf, you can tune any parameters here. The parameters that must be configured correctly in mysql-replication are server-id, log-bin, and datadir.server-id is the ID of the MySQL server in replication group, the number is between 1 – (2^32)-1, and there is no 2 or more MySQL server may have the same ID in one replication group. Consider this like an IP in TCP/IP network.log-bin is the filename that going to be used as binary log. This log is going to be used by slave to track changes on master. That’s why log-bin is a must be configured in Master, but not required for slave (except when the slave have a second role, as master too – multi master configuration).

   datadir is the directory path in which the database files will be saved (in my case datadir=/var/lib/mysql) .

5. If you found a same problem with me when the installation script executing mysql_install_db, it might be caused by old database that has been installed on the system. This will lead you into an authentication problem. The following are the workarounds:

   root# cd /usr/local/mysql/scripts
   root# rm -f /var/lib/mysql/mysql/*    -->
   root# ./mysql_install_db --user=mysql --datadir=/var/lib/mysql

6. To make MySQL started at boot-up, make link from /etc/init.d/mysql to the rc directory

   root# ln -s /etc/init.d/mysql /etc/rc3.d/S99mysql
   root# ln -s /etc/init.d/mysql /etc/rc0.d/K00mysql
   root#  ln -s /etc/init.d/mysql /etc/rc1.d/K00mysql
   root# ln -s /etc/init.d/mysql /etc/rc2.d/K00mysql
   root# ln -s /etc/init.d/mysql /etc/rcS.d/K00mysql

7. Start the MySQL server

   root# /etc/rc3.d/S99mysql start

   Change MySQL default password

   root# mysqladmin password passwordbaru

Do the same steps to install the slave.

After both master and slave installation process done, use MySQL client to configure replication.

in Master  :

GRANT REPLICATION SLAVE ON *.* TO slave@host.slave identified by ''slavepassword'';
FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

slave@host.slave is slave user, host, and password. Write down the result of SHOW MASTER STATUS. We need the information later.

In Slave :

CHANGE MASTER TO
MASTER_HOST="10.10.10.10",
MASTER_USER="slave",
MASTER_PASSWORD="slavepassword",
MASTER_LOG_FILE="logfilename",
MASTER_LOG_POS=123;

Change logfilename and 123 with the file name and the position of from the result of SHOW MASTER STATUS in master

Start the slave and check its status.

START SLAVE
SHOW SLAVE STATUS\G

If SHOW SLAVE STATUS at the first field is says “Slave_IO_State: Waiting for master to send event”, that’s mean the master/slave installation is successful.

In multi-master (master/master) model, both the machines are playing role as master and slave at the same time, so in whichever database the update/insert made, the other will also be updated automatically.