Running multiple instances of MySQL on the same machine

Main Steps
 
Considering already there is an instance of MySQL on this machine.

1) Create separate data and log directories and assign permissions

     mkdir /var/lib/mysql2

     chown -R mysql.mysql /var/lib/mysql2/

     mkdir /var/log/mysql2

     chown -R mysql.mysql /var/log/mysql2

2) Create a new mysql configuration file (Copy existing one and edit it)

     Edit this new configuration file and at least update the mysql port (default to 3306), the pid      and socket to be different than the default ones, and also point the data and log folders to the ones created before

3) Initializing and starting mysql

    mysql_install_db --user=mysql --datadir=/var/lib/mysql2/

    mysqld_safe --defaults-file=/etc/mysql2/my.cnf &
4) Connect to the new instance

     mysql -h 127.0.0.1 -P 3307

5) Stop mysql

     mysqladmin -S /var/run/mysqld/mysqld2.sock shutdown

MySQL replication server configuration

Main steps

Note:  Consider server1 as master and server2 as slave and replicating database as exampledb. 

On Server 1(Master)

      1)      Configuring the Master

ü  To make sure that the replication can work, we must make MySQL listen on all interfaces on the master (server1), therefore we comment out the line bind-address = 127.0.0.1 in /etc/mysql/my.cnf:

Eg: #bind-address           = 127.0.0.1

ü  We have to tell MySQL for which database it should write logs (these logs are used by the slave to see what has changed on the master), which log file it should use, and we have to specify that this MySQL server is the master. We want to replicate the database exampledb

Eg:

server-id               = 1

log_bin                 = /var/log/mysql/mysql-bin.log

expire_logs_days        = 10

max_binlog_size         = 100M

binlog_do_db            = exampledb

  2)   Restart MySQL server

3)   Set up a replication user slave_user that can be used by server2 to access the MySQL    database on server1:

mysql>  GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password';

mysql>   FLUSH PRIVILEGES;

4) Find out the master status of the server1 and create a SQL dump of the database.

            mysql>  USE exampledb;

            mysql>  FLUSH TABLES WITH READ LOCK;

            mysql>  SHOW MASTER STATUS;

           In another shell,

          Ex:

          Shell> mysqldump -u root -pyourrootsqlpassword --opt exampledb > snapshot.sql

5)     Unlock tables

mysql>   UNLOCK TABLES; 

 On server 2(Slave)

1)      Configure slave  server by changing  my.cnf file

         Ex:

        server-id=2

        master-connect-retry=60

        replicate-do-db=exampledb

2)  Restart MySQL server

3)  Import the SQL dump

        Ex:

        Shell> mysql -u root -pyourrootsqlpassword exampledb < snapshot.sq

4)  Give slave parameters

            Ex:

            mysql>  CHANGE MASTER TO MASTER_HOST='192.168.0.100',           
             MASTER_USER='slave_user',   MASTER_PASSWORD='slave_password',          
            MASTER_LOG_FILE='mysql-bin.000001',  MASTER_LOG_POS=19467;

5)  Start slave

              mysql>  START SLAVE;

 6)  Check  slave status (It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output. And also  Slave_IO_State should be Waiting for master to send event)

             mysql> SHOW SLAVE STATUS \G

Ex:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.100
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 19467
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: exampledb
          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: 19467
              Relay_Log_Space: 407
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error: