Difference between revisions of "Live Backups"

From Kolmisoft Wiki
Jump to navigationJump to search
Line 1: Line 1:
Create user in 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'@''''slave's IP address'''' IDENTIFIED BY PASSWORD 'loyalty';
Line 9: Line 9:
  TO 'dataslave'@''''slave's IP address'''';
  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
== Master's config file (/etc/my.cnf) ==
== Master's config file (/etc/my.cnf) ==


Line 43: Line 44:
  basedir=/var/lib
  basedir=/var/lib
  [mysqld_safe]
  [mysqld_safe]
  log-error=/var/lib/mysql/mysqld.log
  '''Bold text'''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)
== Slave's configuration file (/etc/my.cnf) ==
== Slave's configuration file (/etc/my.cnf) ==


Line 89: Line 91:
  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'''

Revision as of 16:00, 27 March 2009

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                                                 
binlog-do-db = mor                                                     
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