Live Backups

"Live backups", that means send all inserts, updates, deletes and other things from your Primary server to Second server as well (automatically). So in case of dissaster (like hdd failure or meteor rain) you will have identical or nearly identical database in secondary server. This manual will explain you how to do that. We will use popular MySQL's Master->Slave replication here. Feel free to spread this guide through internet.

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

CREATE USER 'slave'@'slaves ip' IDENTIFIED BY 'loyalty';

GRANT REPLICATION SLAVE ON * . * TO 'slave'@'slaves ip' IDENTIFIED BY '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 'slave'@'slaves ip';

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 --all-databases -uUser -pPassword >/root/alldatabases.sql
scp /root/alldatabases.sql root@SLAVE:/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/alldatabases.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.

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.

After 2 minutes check if everything is fine.

mysql -uroot -p -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: YES <-|
         Slave_SQL_Running: Yes <-| You have to see YES/YES here 

Now do some actions for testing:

On Master server run:

mysql -uroot -p

then paste this code block:

use mor;
CREATE TABLE `it works!` (
`yes?` VARCHAR( 4 ) NOT NULL 
) ENGINE = InnoDB;

INSERT INTO `it works!` (`yes?` ) VALUES ('yes!');

On Slave server:

mysql -uroot -p

then:

use mor;
select * from `it works!`;

If see such output:

+------+
| yes? |
+------+
| yes! |
+------+

That means everything is fine and now you have live backups :) Enjoy

If output is:

ERROR 1146 (42S02): Table 'mor.it works!' doesn't exist

That means replication is not running and you must find an error in /var/lib/mysql/mysqld.log

Troubleshooting

run mysql -uroot -p -e "SHOW SLAVE STATUS\G" and check for errors. Also look in /var/lib/mysql/mysqld.log for more info