Live Backups

Create MySQL user in Primary (Master) server with name slave and password loyalty

CREATE USER 'slave'@'slave's IP address' IDENTIFIED BY PASSWORD 'loyalty';

GRANT REPLICATION SLAVE ON * . * TO 'slave'@'slave's IP address' IDENTIFIED BY PASSWORD 'loyalty' WITH MAX_QUERIES_PER_HOUR 0  
MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

GRANT SELECT ON `mor` . * 
TO 'dataslave'@'slave's IP address';

Now use this configuration in Primary server's MySQL configuration file which is located in /etc/my.cnf

Master's config file (/etc/my.cnf)

[mysqld]                                                             
datadir=/var/lib/mysql                                               
socket=/var/lib/mysql/mysql.sock                                         
old_passwords=1                                                        
bind-address = 0.0.0.0                                                 
server-id = 1                                                          
binlog-ignore-db="mysql"                                               
log-bin = /var/lib/mysql/mysql-bin.log
log-bin-index = /var/lib/mysql/master-log-bin.index
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
expire-logs-days = 31
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
innodb_support_xa
max_allowed_packet      = 256M
key_buffer_size=256M
key_buffer=258M
innodb_buffer_pool_size = 100M
query_cache_type=1
query_cache_limit=64M
query_cache_size=64M
join_buffer_size = 2M
tmp_table_size = 512M
max_heap_table_size = 512M
thread_cache_size = 4
table_cache = 128
log-error=/var/lib/mysql/mysqld.log
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
Bold textlog-error=/var/lib/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Use this configuration in Second server (Slave)

Slave's configuration file (/etc/my.cnf)

[mysqld]                         
datadir=/var/lib/mysql           
socket=/var/lib/mysql/mysql.sock  
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).       
old_passwords=1                                                        
bind-address = 0.0.0.0                                                                                                  
server-id=2                                                            
master-host = Masters.ip.address                                          
master-user = slave                                                
master-password = loyalty                                            
master-port = 3306                                                     
log-bin = /var/lib/mysql/mysql-bin.log
log-bin-index = /var/lib/mysql/master-log-bin.index
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
expire-logs-days = 31
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
innodb_support_xa
max_allowed_packet      = 256M
key_buffer_size=256M
key_buffer=258M
innodb_buffer_pool_size = 100M
query_cache_type=1
query_cache_limit=64M
query_cache_size=64M
join_buffer_size = 2M
tmp_table_size = 512M
max_heap_table_size = 512M
thread_cache_size = 4
table_cache = 128
log-error=/var/lib/mysql/mysqld.log
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
log-error=/var/lib/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Don't forget to change "Masters.ip.address" to PRIMARY servers IP address

Now run

/etc/init.d/mysqld restart 

in both servers.

In Primary server: Switch off Asterisk and Apache and dump MOR database:

mysqldump -uUser -pPassword mor >/root/mor.sql
scp /root/mor.sql root@SLAVE's ip address:/root

Now in Second server:

mysql -u root -p -e "CREATE DATABASE MOR"

If you get noticed that DB already exists, make sure to DROP (DROP DATABASE MOR) it out and then try to create MOR database again. Now import dump of real MOR database which is from Primary server:

mysql -D mor </root/mor.sql

If everything was fine you shouldn't get any errors

In Primary server:

mysql -uroot -p -e "SHOW MASTER STATUS\G;"

Remember file and position bin logs.

In Secondary server:

mysql -uroot -p -e "STOP SLAVE; CHANGE MASTER TO MASTER_HOST='MASTER's', MASTER_USER='slave', MASTER_PASSWORD='loaylty', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98; START SLAVE;"

Change MASTER_LOG_FILE & MASTER_LOG_POS accordingly.

mysql -e "SHOW SLAVE STATUS\G"

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

            Slave_IO_State: Connecting to master             
               Master_Host: 192.168.0.145                    
               Master_User: slave                            
               Master_Port: 3306                             
             Connect_Retry: 60                               
           Master_Log_File: mysql-bin.000003
       Read_Master_Log_Pos: 5678
            Relay_Log_File: slave-relay.000001
             Relay_Log_Pos: 98
     Relay_Master_Log_File: mysql-bin.000003
          Slave_IO_Running: No
         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: 5678
           Relay_Log_Space: 98
           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: NULL