Difference between revisions of "Live Backups"

From Kolmisoft Wiki
Jump to navigationJump to search
 
(47 intermediate revisions by 2 users not shown)
Line 1: Line 1:
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 MySQL user in Primary (Master) server with name slave and password loyalty


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


  GRANT REPLICATION SLAVE ON * . * TO 'slave'@''''slave's IP address'''' IDENTIFIED BY PASSWORD 'loyalty' WITH MAX_QUERIES_PER_HOUR 0
  GRANT REPLICATION SLAVE ON * . * TO 'slave'@''''slaves ip'''' IDENTIFIED BY 'loyalty' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0
  MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 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 '''P'''rimary server's MySQL configuration file which is located in /etc/my.cnf
Now use this configuration in '''P'''rimary server's MySQL configuration file which is located in /etc/my.cnf
== Master's config file (/etc/my.cnf) ==
== Master's config file (/etc/my.cnf) ==


  [mysqld]                                                            
  [mysqld]
  datadir=/var/lib/mysql                                              
innodb_file_per_table=1
  socket=/var/lib/mysql/mysql.sock                                        
expire_logs_days = 2
  old_passwords=1                                                      
query_cache_limit=46M
  bind-address = 0.0.0.0                                                
thread_stack=3M
  server-id = 1                                                         
key_buffer=93M
  binlog-ignore-db="mysql"                                             
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 = /var/lib/mysql/mysql-bin.log
  log-bin-index = /var/lib/mysql/master-log-bin.index
  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 = /var/lib/mysql/slave-relay.log
  relay-log-index = /var/lib/mysql/slave-relay-log.index
  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_type=1
query_cache_limit=64M
query_cache_size=64M
join_buffer_size = 2M
  tmp_table_size = 512M
  tmp_table_size = 512M
  max_heap_table_size = 512M
  max_heap_table_size = 512M
Line 40: Line 41:
  table_cache = 128
  table_cache = 128
  log-error=/var/lib/mysql/mysqld.log
  log-error=/var/lib/mysql/mysqld.log
  [mysql.server]
  [mysql.server]
  user=mysql
  user=mysql
  basedir=/var/lib
  basedir=/var/lib
  [mysqld_safe]
  [mysqld_safe]
  '''Bold text'''log-error=/var/lib/mysql/mysqld.log
  log-error=/var/lib/mysql/mysqld.log
  pid-file=/var/run/mysqld/mysqld.pid
  pid-file=/var/run/mysqld/mysqld.pid




Use this configuration in '''S'''econd server (Slave)  
Use this configuration in '''S'''econd server (Slave)
 
== Slave's configuration file (/etc/my.cnf) ==
== Slave's configuration file (/etc/my.cnf) ==


  [mysqld]                        
  [mysqld]
  datadir=/var/lib/mysql          
innodb_file_per_table=1
  socket=/var/lib/mysql/mysql.sock
expire_logs_days = 3
# Default to using old password format for compatibility with mysql 3.x
query_cache_limit=47M
  # clients (those using the mysqlclient10 compatibility package).     
thread_stack=3M
old_passwords=1                                                      
key_buffer=94M
  bind-address = 0.0.0.0                                                                                                
thread_concurrency=16
  server-id=2                                                           
max_allowed_packet=100M
  master-host = '''Masters.ip.address'''                                        
innodb_buffer_pool_size=512M
  master-user = slave                                              
join_buffer_size=47M
  master-password = loyalty                                          
query_cache_size=380M
  master-port = 3306                                                    
  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 = /var/lib/mysql/mysql-bin.log
  log-bin-index = /var/lib/mysql/master-log-bin.index
  log-bin-index = /var/lib/mysql/master-log-bin.index
  relay-log = /var/lib/mysql/slave-relay.log
  relay-log = /var/lib/mysql/slave-relay.log
  relay-log-index = /var/lib/mysql/slave-relay-log.index
  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_type=1
query_cache_limit=64M
query_cache_size=64M
join_buffer_size = 2M
  tmp_table_size = 512M
  tmp_table_size = 512M
  max_heap_table_size = 512M
  max_heap_table_size = 512M
Line 84: Line 89:
  table_cache = 128
  table_cache = 128
  log-error=/var/lib/mysql/mysqld.log
  log-error=/var/lib/mysql/mysqld.log
  [mysql.server]
  [mysql.server]
  user=mysql
  user=mysql
  basedir=/var/lib
  basedir=/var/lib
  [mysqld_safe]
  [mysqld_safe]
  log-error=/var/lib/mysql/mysqld.log
  log-error=/var/lib/mysql/mysqld.log
  pid-file=/var/run/mysqld/mysqld.pid
  pid-file=/var/run/mysqld/mysqld.pid


'''Don't forget to change "Masters.ip.address" to PRIMARY servers IP address'''
'''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:
In '''Primary''' server:
'''Switch off Asterisk and Apache''' and dump MOR database:  
'''Switch off Asterisk and Apache''' and dump MOR database:  
  mysqldump -uUser -pPassword mor >/root/mor.sql
  mysqldump -uroot -p --databases '''db_name1 db2 my-database-3'''  >/root/alldatabases.sql
  scp /root/mor.sql root@'''SLAVE's ip address''':/root
  scp /root/alldatabases.sql root@'''SLAVE''':/root


Now in '''Second''' server:
Now in '''Second''' server:
  mysql -u root -p -e "CREATE DATABASE MOR"
  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.
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 real MOR database which is from Primary server:
Now import dump of databases which is from Primary server:
  mysql -D mor </root/mor.sql
  mysql -uroot -p </root/alldatabases.sql


If everything was fine you shouldn't get any errors
If everything was fine you shouldn't get any errors
In '''P'''rimary server:
mysql -uroot -p -e "SHOW MASTER STATUS\G;"
'''Remember file and position.'''
In '''S'''econdary 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?<br>
A: With this configuration - all databases except mysql .
Q: Will data appear in Master server when I insert something in Slave ?<br>
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?<br>
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 ?<br>
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 :)<br>
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

Latest revision as of 07:39, 13 January 2014

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