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 -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 -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
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 it will offline longer that 31 days, the logs in Master server will expire so slave trasfer not all data.
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