Difference between revisions of "MySQL Replication"
Line 4: | Line 4: | ||
'''THIS TURIAL EXPLAINS HOW TO IMPLEMENT MASTER<->MASTER REPLICATION''' | |||
Revision as of 12:43, 30 March 2009
MySQL Replication works only on servers where MySQL 5.x is installed
THIS TURIAL EXPLAINS HOW TO IMPLEMENT MASTER<->MASTER REPLICATION
Create user on Server 1
- Username: rep2
- Password: rep2
- Host: 222.222.222.222
- Grant all privileges to this user (on database mor)
CREATE USER 'rep2'@'222.222.222.222' IDENTIFIED BY 'rep2';
GRANT REPLICATION SLAVE , REPLICATION CLIENT ON * . * TO 'rep2'@'222.222.222.222' IDENTIFIED BY 'rep2' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
GRANT ALL PRIVILEGES ON `mor` . * TO 'rep2'@'222.222.222.222' WITH GRANT OPTION ;
Create user on Server 2
- Username: rep1
- Password: rep1
- Host: 111.111.111.111
- Grant all privileges to this user (on database mor)
CREATE USER 'rep1'@'111.111.111.111' IDENTIFIED BY 'rep1';
GRANT REPLICATION SLAVE , REPLICATION CLIENT ON * . * TO 'rep1'@'111.111.111.111' IDENTIFIED BY 'rep1' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
GRANT ALL PRIVILEGES ON `mor` . * TO 'rep1'@'111.111.111.111' WITH GRANT OPTION ;
Note: Make sure you have applied REPLICATION CLIENT and REPLICATION SLAVE on both users.
Server 1 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 = 10 auto_increment_increment = 10 auto_increment_offset = 1 master-host = 222.222.222.222 master-user = rep1 master-password = rep1 master-port = 3306 master-connect-retry = 60 replicate-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
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-slow-queries = /var/lib/mysql/slow_query.log
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
NOTE: if you have file /etc/mysql/debian.cnf make sure you have correct socket location in it:
socket=/var/lib/mysql/mysql.sock
Or change correct location into mysql.cnf file (e.g. socket=/var/run/mysqld/mysqld.sock)
Server 2 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 = 20 auto_increment_increment = 20 auto_increment_offset = 2 master-host = 111.111.111.111 master-user = rep2 master-password = rep2 master-port = 3306 master-connect-retry = 60 replicate-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 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-slow-queries = /var/lib/mysql/slow_query.log
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
NOTE: if you have file /etc/mysql/debian.cnf make sure you have correct socket location in it:
socket=/var/lib/mysql/mysql.sock
Or change correct location into mysql.cnf file (e.g. socket=/var/run/mysqld/mysqld.sock
Server 2
Make sure data on Server 2 = data on Server 1. E.g. MySQL table structure and data are identical on both machines.
Server 1
Debian:
/etc/init.d/mysql restart
Centos/Fedora:
/etc/init.d/mysqld restart
mysql -u root -p
USE mor; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;
Remember File and Position.
UNLOCK TABLES; quit;
Server2
Check if you can connect to first erver:
mysql -h 111.111.111.111 -u rep2 -p
Proceed if ok:
Debian:
/etc/init.d/mysql restart
Centos/Fedora:
/etc/init.d/mysqld restart
mysql -u root -p
SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='111.111.111.111', MASTER_USER='rep2', MASTER_PASSWORD='rep2', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;
NOTE: Change MASTER_HOST, MASTER_LOG_FILE and MASTER_LOG_POS accordingly.
START SLAVE;
USE mor; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;
Remember File and Position.
UNLOCK TABLES; quit;
Server 1
Check if you can connect to first erver:
mysql -h 222.222.222.222 -u rep1 -p
Proceed if ok:
mysql -u root -p
SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='222.222.222.222', MASTER_USER='rep1', MASTER_PASSWORD='rep1', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;
NOTE: Change MASTER_HOST, MASTER_LOG_FILE and MASTER_LOG_POS accordingly.
START SLAVE; quit;
Log files
In order for MySQL Replication to function properly you need to check log files in /var/lib/mysql
Open master-log-bin.index, it should contain names of the files which really exist in same folder. If there's some mismatch - edit this file: enter missing names for files or delete file names which does not represent real files in this folder.
Same with file slave-relay-log.index
Testing
Login to MySQL console on both servers:
mysql -u root -p
Then:
SHOW SLAVE STATUS\G
If everything is ok you should see such lines between others on both servers:
Slave_IO_State: Waiting for master to send event Slave_IO_Running: Yes Slave_SQL_Running: Yes
If Replication is off you will see:
Slave_IO_State: Slave_IO_Running: No
Quick way to do this:
mysql -u root -e 'SHOW SLAVE STATUS\G' | grep 'Slave'
If root has psw:
mysql -u root -pPASSWORD -e 'SHOW SLAVE STATUS\G' | grep 'Slave'
Never use phpmyadmin to check if data is same on both servers, use mysql console and command like count. For example: SELECT COUNT(*) FROM destinations;
Troubleshooting
Check file: /var/lib/mysql/mysqld.log
'Could not find first log file name in binary log index file' from master when reading data from binary log
We go to the master and check /var/lib/mysql/master-log-bin.index file.
For example it can show:
/var/lib/mysql/mysql-bin.000001 /var/lib/mysql/mysql-bin.000002 /var/lib/mysql/mysql-bin.000003 /var/lib/mysql/mysql-bin.000005
In /var/lib/mysql/ we also have file mysql-bin.000004, so its' name is missing in index file, lets add it, so master-log-bin.index looks like:
/var/lib/mysql/mysql-bin.000001 /var/lib/mysql/mysql-bin.000002 /var/lib/mysql/mysql-bin.000003 /var/lib/mysql/mysql-bin.000004 /var/lib/mysql/mysql-bin.000005
Restart mysql on this server and try with slave again.
This is just an example - some other file's records can be missing. Make sure file has all related file's records which are in same folder.
Failed to open the relay log
- stop mysql on the slave server
- find the directory that contains the relay logs (usually /var/lib/mysql)
- delete all relay logs called hostname-relay-bin.XXX, hostname-relay-bin.index and relay-log.info
- restart mysql
Your mysql will start with fresh relay logs and is getting the missing data directly from the master server, as there will be probably still bin logs!
Couldn't find MySQL manager or server
Comment out basedir in my.cnf
- Default mysql.cnf - can be usefull to go back if something goes wrong and no backup is done
- If server is rebooted - it takes ~45s for database to start synchronizing after MySQL is up
Could not initialize master info structure, more error messages can be found in the MySQL error log
Please check this link: http://forums.mysql.com/read.php?26,163227,163241#msg-163241
Broken replication fix
http://rackerhacker.com/2008/01/09/mysql-replication-breakdown/
What to do, if you have errors with binlogs
Delete all binlog files from both servers (but leave databases structures and data!), then reapply replication. You have to test GUI on both servers after you apply MySQL replication.
No more MySQL Crash Safe Replication in 5.0 ?
http://www.mysqlperformanceblog.com/2008/01/29/no-more-mysql-crash-safe-replication-in-50/