What is the Best Way to create MySQL Master-Slave Replication Setup and Troubleshoot it?

I am very new to Database Administration.

I face a lot of problems while setting up mysql master-slave replication.

I also face regular mysql replication troubleshooting issues.

Can anybody helps to understand how should i deal with all these?

I provided links to tutorials. Just keep mind that on Ubuntu, the my.cnf file is in /etc/mysql/my.cnf and not in /etc/my.cnf like in the howtoforge tutorial. In my setup, I didn't use FLUSH TABLES WITH READ LOCK; on the master. If your master server has a lot of write activity, you may need to lock your tables by running that command before backing up. If you use FLUSH TABLES WITH READ LOCK;, then after your backup, you will want to run UNLOCK TABLES. If you run into any problems, let me know.

Here is the tutorial that I found on howto forge, made for Redhat/CentOS :
http://www.howtoforge.com/mysql_database_replication

Another tutorial that looked ok for Ubuntu
http://www.srcnix.com/2010/10/14/simple-mysql-replication-with-ubuntu-master-to-slave/

Here is the configuration I used :

On the MASTER Server

Configure the master server:

vi /etc/mysql/my.cnf

[mysqld]

# bind-address = 127.0.0.1 (comment this out)
server_id           = 1
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
max_binlog_size     = 100M
expire_logs_days    = 1

Restart MySQL:

/etc/init.d/mysql restart

Connect to mysql's console: mysql -u root -ppassword

Create and grant permissions to replication user.

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'ipaddressofslave' IDENTIFIED BY 'replicationuserpassword';

Make sure to copy this information somewhere or leave it visible

SHOW MASTER STATUS \G;
mysql> show master status \G;
            File: mysql-bin.000001
        Position: 100
    Binlog_Do_DB: 
Binlog_Ignore_DB:

mysql> quit

Dump the database to a file:

mysqldump -u root -p databasename > /tmp/databasename-backup.sql

Copy the database dump to the slave server using scp or use ftp if you like:

scp /tmp/databasename-backup.sql root@ipaddressofslave:/tmp/

On the SLAVE Server

Edit the mysql configuration:

vi /etc/mysql/my.cnf
[mysqld]

# slave server configuration
server_id           = 2

# this is optional, but I find it useful to specify where the relay logs go to control.  
# Don't forget to create the /var/log/mysql directory and give mysql rights to it.  
# chown mysql:mysql -R /var/log/mysql
# disk space
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
relay_log_space_limit = 2000M

Restart MySQL: /etc/init.d/mysql restart

Restore the backup:

mysql -u root -ppassword nameofthedatabase < /tmp/databasename-backup.sql

Connect to MySQL:

mysql -u root -ppassword

stop slave;

# master log file and master_log_pos taken from show master status above
CHANGE MASTER TO master_host='ipaddressmaster', master_port=3306, master_user='replication', master_password='replicationuserpassword', master_log_file='mysql-bin.000001', master_log_pos=100;

start slave;

Run SHOW SLAVE STATUS\G:

mysql> show slave status\G;
             Slave_IO_State: Waiting for master to send event
                Master_Host: ipaddressmaster
                Master_User: replication
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.0000001
        Read_Master_Log_Pos: 100
             Relay_Log_File: mysql-relay-bin.000001
              Relay_Log_Pos: 1
      Relay_Master_Log_File: mysql-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: 17324288
            Relay_Log_Space: 17324425
            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
1 row in set (0.02 sec)

Afterwards, keep in mind that replication can fail for various reasons. On the slave, you can monitor the status by running the command SHOW SLAVE STATUS \G; Or setting up a cron job to monitor the status and send emails if it fails. Get familar with the output from this command. If replication is running correctly, you should see "Slave_IO_State: Waiting for master to send event".

Once you get this setup correctly, I can provide you with a script to monitor that replication.

Here is a script to monitor the error log in MySQL. If you add the line


[mysqld]


log-error = /var/log/mysql/mysql.err

restart mysql : /etc/init.d/mysql restart

Then you can use the following script to monitor the log file. If the log changes in any way, you will receive an email notifying you that an error occured on the slave server. If you want the error log checked

StackExchange Database Administrators Q#8680, answer score: 20