Difference between revisions of "Live Backups"
Line 195: | Line 195: | ||
Q: How fast data is sent to Slave ?<br> | Q: How fast data is sent to Slave ?<br> | ||
A: The replication is asynchronous, MySQL | 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" | [root@localhost ~]# mysql -e "CREATE DATABASE TESTING_TIMING" ; mysql -h 192.168.0.146 -e "SHOW DATABASES" |
Revision as of 16:47, 30 March 2009
Keeping your data save 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 ;
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 -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='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.
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