MySQL Replication

From Kolmisoft Wiki
Jump to navigationJump to search

MySQL Replication works only on servers where MySQL 5.x is installed

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 on Server 2

  • Username: rep1
  • Password: rep1
  • Host: 111.111.111.111
  • Grant all privileges to this user (on database mor)

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

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

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

/etc/init.d/mysql restart
mysql -u root -p 

USE mor;
FLUSH TABLES WITH READ LOCK; 
SHOW MASTER STATUS;

Remember File and Position. 

UNLOCK TABLES;
quit;



Server2

/etc/init.d/mysql 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.000010', MASTER_LOG_POS=1183703;

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

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.000011', 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



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!



  • 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
Retrieved from "https://wiki.kolmisoft.com/index.php?title=MySQL_Replication&oldid=1449"