Live Backups
From Kolmisoft Wiki
Jump to navigationJump to search
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 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 -uUser -pPassword mor >/root/mor.sql scp /root/mor.sql root@SLAVE's ip address:/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/mor.sql
If everything was fine you shouldn't get any errors