Difference between revisions of "Live Backups"
Line 53: | Line 53: | ||
== Slave's configuration file (/etc/my.cnf) == | == Slave's configuration file (/etc/my.cnf) == | ||
[mysqld] | [mysqld] | ||
datadir=/var/lib/mysql | expire_logs_days = 7 | ||
socket=/var/lib/mysql/mysql.sock | query_cache_limit=47M | ||
thread_stack=3M | |||
key_buffer=94M | |||
skip-bdb | |||
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 | old_passwords=1 | ||
bind-address = 0.0.0.0 | bind-address = 0.0.0.0 | ||
server-id= | server-id=30 | ||
master-host = '''Masters.ip.address''' | master-host = '''Masters.ip.address''' | ||
master-user = slave | master-user = slave | ||
master-password = loyalty | master-password = loyalty | ||
master-port = 3306 | master-port = 3306 | ||
master-connect-retry = 60 | |||
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 | ||
query_cache_type=1 | query_cache_type=1 | ||
tmp_table_size = 512M | tmp_table_size = 512M | ||
max_heap_table_size = 512M | max_heap_table_size = 512M | ||
Line 86: | Line 87: | ||
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''' |
Revision as of 08:04, 10 May 2012
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] expire_logs_days = 7 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 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] expire_logs_days = 7 query_cache_limit=47M thread_stack=3M key_buffer=94M skip-bdb 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 master-user = slave master-password = loyalty master-port = 3306 master-connect-retry = 60 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