Live Backups

Keeping your data safe should be your No.1 priority.

"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 ;

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]
innodb_file_per_table=1
expire_logs_days = 2
query_cache_limit=46M
thread_stack=3M
key_buffer=93M
thread_concurrency=8
max_allowed_packet=100M
innodb_buffer_pool_size=512M
join_buffer_size=46M
query_cache_size=376M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#old_passwords=1
bind-address = 0.0.0.0
server-id = 10
binlog-do-db = mor
log-bin = /var/lib/mysql/mysql-bin.log
log-bin-index = /var/lib/mysql/master-log-bin.index
# sync_binlog = 1               #increases reliability, but decreases performance
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
query_cache_type=1
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

Use this configuration in Second server (Slave)

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

[mysqld]
innodb_file_per_table=1
expire_logs_days = 3
query_cache_limit=47M
thread_stack=3M
key_buffer=94M
thread_concurrency=16
max_allowed_packet=100M
innodb_buffer_pool_size=512M
join_buffer_size=47M
query_cache_size=380M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#old_passwords=1
bind-address = 0.0.0.0
server-id=30
#master-host = Masters.ip.address #Uncomment on pre MySQL 5.5
#master-user = slave                    #Uncomment on pre MySQL 5.5
#master-password = loyalty              #Uncomment on pre MySQL 5.5
#master-port = 3306                     #Uncomment on pre MySQL 5.5
#master-connect-retry = 60              #Uncomment on pre MySQL 5.5
replicate-do-db = mor
slave-skip-errors=1061,1062
binlog-do-db = mor
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
query_cache_type=1
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 -uroot -p --databases db_name1 db2 my-database-3  >/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 AND_THE_NAME_OF_DATABASE_WHICH_ALREADY_EXISTS) it out and then try to create database again. Now import dump of databases which is from Primary server:

mysql -uroot -p </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='MASTERs IP', MASTER_USER='slave', MASTER_PASSWORD='loyalty', 
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

Questions

Q: Only MOR database or all databases will be synchronized to Second server?
A: With this configuration - all databases except mysql .

Q: Will data appear in Master server when I insert something in Slave ?
A: No. Use MASTER<->MASTER replication for that. MySQL Replication

Q: If Slave will be down for some time, when It boot up again, will it resync old+new data?
A: Yes, but if slave will not show up in 31 days, the logs in Master server will expire so slave will not trasfer all data.

Q: How fast data is sent to Slave ?
A: The replication is asynchronous, MySQL didn't support synchronous replication mode at the time this manual was written. But, it doesn't mean the data will apear after some hours or so. The data should appear instantly or nearly instantly, everything depends on distance between servers and load. These are 2 most important factors. I have performed 1 test to check how fast data will appear from Master to Slave. The servers was 2 virtual machines running on same PC.

[root@localhost ~]# mysql -e "CREATE DATABASE TESTING_TIMING" ; mysql -h 192.168.0.146 -e "SHOW DATABASES"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| TESTING_TIMING     |
| antras             |
| aosdpoasd          |
| mor                |
| mysql              |
| pirmas             |
| test               |
| trecias            |
+--------------------+
[root@localhost ~]#

I have made a database in one server and then instantly checked another server, the result was, that database has appeared instantly :)
Ps. servers were without any load.

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