Please create this script

Fron the Linux prompt, open up an editor like vi

vi /root/CreateMySQLSlave.sh

and add these lines to it

MYSQL_MASTER_HOST=10.1.2.20
MYSQL_SLAVE_HOST=10.1.2.30
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQL_MASTER_CONN="-h${MYSQL_MASTER_HOST} ${MYSQL_CONN}"
MYSQL_SLAVE_CONN="-h${MYSQL_SLAVE_HOST} ${MYSQL_CONN}"

MYSQLDUMP_OPTIONS="--master-data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --flush-privileges"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --all-databases"

CREATE_REPL_USER="GRANT REPLICATION SLAVE ON *.* TO repluser@'%' IDENTIFIED BY 'replpass'"
mysql ${MYSQL_MASTER_CONN} -AN -e"${CREATE_REPL_USER}"

RELOAD_FILE=/root/MySQLData.sql
echo "STOP SLAVE;" > ${RELOAD_FILE}
echo "CHANGE MASTER TO master_host='${MYSQL_MASTER_HOST}'," >> ${RELOAD_FILE}
echo "master_port=3306," >> ${RELOAD_FILE}
echo "master_user='repluser'," >> ${RELOAD_FILE}
echo "master_password='replpass'," >> ${RELOAD_FILE}
echo "master_log_file='dummy-file'," >> ${RELOAD_FILE}
echo "master_log_pos=1;" >> ${RELOAD_FILE}
mysqldump ${MYSQL_MASTER_CONN} ${MYSQLDUMP_OPTIONS} >> ${RELOAD_FILE}
echo "START SLAVE;" >> ${RELOAD_FILE}

mysql ${MYSQL_SLAVE_CONN} -A < ${RELOAD_FILE}

Next, make the file executable and run it

chmod +x /root/CreateMySQLSlave.sh
/root/CreateMySQLSlave.sh

That's it !!!

Make sure you set the IP Address for MYSQL_MASTER_HOST and MYSQL_SLAVE_HOST

By the way, don't worry about the dummy-file in the master_log_file parameter for CHANGE MASTER TO. Line 29 of the dump file should have the correct setting there due to the --master-data=1. You can see it by doing this to the dump file

head -29 /root/MySQLData.sql | tail -1

Make sure binary logging is already enabled on the master. Here is how you can tell: run SHOW MASTER STATUS;. If you get nothing back, you need to enable it like this:

Add this to /etc/my.cnf

[mysqld]
log-bin=mysql-bin

then restart mysql

OK that takes care of binary logging for the Master.

You also need to make sure the Master and the Slave have different server IDs. Here is how you can tell:

Run SHOW VARIABLES LIKE 'server_id'; If the result comes up as 1, you must set it explicitly.

Goto the Master and add this to /etc/my.cnf

[mysqld]
server-id=1

Goto the Slave and add this to /etc/my.cnf

[mysqld]
server-id=2

The main thing here is to make sure server_id is explicitly set in my.cnf on both Master and Slave. You must also make sure the server_id values are different from all other servers involved in the replication topology.

If you are new to this, have a sysadmin join you to do this.

STEP01) Add this to /etc/my.cnf on ServerB

[mysqld]
log-bin=mysql-bin

STEP02) Restart mysql on ServerB without replication running

service mysql restart --skip-slave-start

STEP03) On ServerB, run this in mysql client

SHOW MASTER STATUS;

You should see something like this:

+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000001 | 590591464 |              |                  |
+------------------+-----------+--------------+------------------+

STEP04) Record the Log File and Position from STEP03

STEP05) Restart mysql on ServerB as normal

service mysql restart

STEP06) Setup Replication User on ServerB

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ...

STEP07) On ServerA, run the following

CHANGE MASTER TO
MASTER_HOST='IP Address of ServerB',
MASTER_PORT=3306,
MASTER_USER='replication username',
MASTER_PASSWORD='replication password',
MASTER_LOG_FILE='File From STEP04',
MASTER_LOG_POS=Position From STEP04;

Suppose ServerB's IP address is 10.1.2.30, replication user/pass is 'repluser'/'replpass'

CHANGE MASTER TO
MASTER_HOST='10.1.2.30',
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=590591464;

STEP08) Start Replication on ServerA by running

START SLAVE;

STEP09) Run SHOW SLAVE STATUS\G on ServerA

It should look something like this:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.89.147
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000109
          Read_Master_Log_Pos: 636515875
               Relay_Log_File: relay-bin.000038
                Relay_Log_Pos: 636509693
        Relay_Master_Log_File: mysql-bin.000109
             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: 636515875
              Relay_Log_Space: 636509885
              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:
1 row in set (0.01 sec)

If Slave_IO_Running and Slave_SQL_Running both say Yes, CONGRATULATIONS You've done it !!!

Give it a Try !!!