Difference between revisions of "MySQL Replication"

From Kolmisoft Wiki
Jump to navigationJump to search
 
(100 intermediate revisions by 7 users not shown)
Line 1: Line 1:
<big><big><b>MySQL Replication works only on servers where MySQL 5.x is installed</b></big></big>
<big><big><b><span style="color:#ff0000">Do NOT use this manual for MOR X17 or M4</span></b></big></big>
 
 
<big><big><b>MySQL Replication manual works only on servers where MySQL 5.1, 5.5 or 5.6 is installed</b></big></big>
[[Image:mysql_logo.png|right]]
[[Image:mysql_logo.png|right]]
<br><br>
<br><br>


'''THIS TUTORIAL WILL GUIDE YOU THROUGH HOW TO IMPLEMENT MASTER<->MASTER REPLICATION'''
'''Before you start, make a backup of your database.'''
<br><br>
==Create user on Server 1==
==Create user on Server 1==


Line 9: Line 18:
* Host: 222.222.222.222
* Host: 222.222.222.222
* Grant all privileges to this user (on database mor)
* Grant all privileges to this user (on database mor)
Login as root and issue such commands:


  CREATE USER 'rep2'@''''222.222.222.222'''' IDENTIFIED BY 'rep2';
  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
  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 ;
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 ;
  GRANT ALL PRIVILEGES ON *.* TO 'rep2'@''''222.222.222.222'''' WITH GRANT OPTION ;


Go to Server 2 and test the connection:
mysql -h 111.111.111.111 -u rep2 -prep2
<br><br>


==Create user on Server 2==
==Create user on Server 2==
Line 24: Line 39:
* Host: 111.111.111.111
* Host: 111.111.111.111
* Grant all privileges to this user (on database mor)
* Grant all privileges to this user (on database mor)
Login as root and issue such commands:


  CREATE USER 'rep1'@''''111.111.111.111'''' IDENTIFIED BY 'rep1';
  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
  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 ;
MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
 
GRANT ALL PRIVILEGES ON *.* TO 'rep1'@''''111.111.111.111'''' WITH GRANT OPTION ;
 
Go to Server 1 and test the connection:
 
mysql -h 222.222.222.222 -u rep1 -prep1
 


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. '''
''' Note: Make sure you have applied REPLICATION CLIENT and REPLICATION SLAVE on both users. '''


 
<br><br>


==Server 1 my.cnf==
==Server 1 my.cnf==


  [mysqld]
  [mysqld]
  datadir=/var/lib/mysql
  slow_query_log=1
  socket=/var/lib/mysql/mysql.sock  
  slow_query_log_file=/var/log/mysql-log-slow-queries.log
  # Default to using old password format for compatibility with mysql 3.x
  long_query_time=20
  # clients (those using the mysqlclient10 compatibility package).
  skip-name-resolve
  old_passwords=1
secure_file_priv=
 
  sql_mode=
  innodb_file_per_table=1
  bind-address = 0.0.0.0
  bind-address = 0.0.0.0
  binlog-do-db = mor
  binlog-do-db = mor
# binlog-do-db = mor_mnp          #uncomment if there is mor_mnp database
# binlog-do-db = opensips        #uncomment if there is opensips database
  server-id = 10
  server-id = 10
  auto_increment_increment = 10
  auto_increment_increment = 10
  auto_increment_offset = 1
  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
  replicate-do-db = mor
# replicate-do-db = mor_mnp      #uncomment if there is mon_mnp database
# replicate-do-db = opensips      #uncomment if there is opensips database
binlog_format=MIXED
  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
sync_binlog = 1              #increases reliability, but decreases performance
  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
 
  '''expire-logs-days = 31'''
  slave-skip-errors=1061,1062,1213,1060,1032
  sync_binlog = 1
  slave_transaction_retries=3600
  max_allowed_packet      = 256M
   
  key_buffer_size=256M
  expire_logs_days = 3
  key_buffer=258M
  # innodb_flush_log_at_trx_commit = 1
 
  # innodb_support_xa
innodb_buffer_pool_size = 100M
   
 
  max_connections = 300
query_cache_type=1
  slave_transaction_retries = 3600
  query_cache_limit=64M
   
query_cache_size=64M
  datadir=/var/lib/mysql
 
  socket=/var/lib/mysql/mysql.sock
  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]
  [mysqld_safe]
  log-error=/var/lib/mysql/mysqld.log
  log-error=/var/log/mysqld.log
  pid-file=/var/run/mysqld/mysqld.pid
  pid-file=/var/run/mysqld/mysqld.pid


<br><br>
Stop services:


NOTE: if you have file '''/etc/mysql/debian.cnf''' make sure you have correct socket location in it:
service elasticsearch stop
 
service httpd stop
  socket=/var/lib/mysql/mysql.sock
  service asterisk stop
 
service mor_server_loadstats stop
Or change correct location into mysql.cnf file (e.g. socket=/var/run/mysqld/mysqld.sock)
service crond stop
 
service mor_alerts stop
<br>
service mor_aggregates stop


==Server 2 my.cnf==
==Server 2 my.cnf==


  [mysqld]
  [mysqld]
  datadir=/var/lib/mysql
  slow_query_log=1
  socket=/var/lib/mysql/mysql.sock  
  slow_query_log_file=/var/log/mysql-log-slow-queries.log
  # Default to using old password format for compatibility with mysql 3.x
  long_query_time=20
  # clients (those using the mysqlclient10 compatibility package).
  skip-name-resolve
  old_passwords=1
secure_file_priv=
 
  sql_mode=
  innodb_file_per_table=1
  bind-address = 0.0.0.0
  bind-address = 0.0.0.0
  binlog-do-db = mor
  binlog-do-db = mor
# binlog-do-db = mor_mnp          #uncomment if there is mor_mnp database
# binlog-do-db = opensips        #uncomment if there is opensips database
  server-id = 20
  server-id = 20
  auto_increment_increment = 20
  auto_increment_increment = 10
  auto_increment_offset = 2
  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
  replicate-do-db = mor
# replicate-do-db = mor_mnp      #uncomment if there is mon_mnp database
# replicate-do-db = opensips      #uncomment if there is opensips database
binlog_format=MIXED
  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
sync_binlog = 1              #increases reliability, but decreases performance
  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
   
   
  '''expire-logs-days = 31'''
  slave-skip-errors=1061,1062,1213,1060,1032
  sync_binlog = 1
  slave_transaction_retries=3600
max_allowed_packet      = 256M
   
  key_buffer_size=256M
  expire_logs_days = 3
  key_buffer=258M
  # innodb_flush_log_at_trx_commit = 1
 
  # innodb_support_xa
  innodb_buffer_pool_size = 100M
   
 
  max_connections = 300
query_cache_type=1
  slave_transaction_retries = 3600
  query_cache_limit=64M
   
  query_cache_size=64M
  datadir=/var/lib/mysql
 
  socket=/var/lib/mysql/mysql.sock
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]
  [mysqld_safe]
  log-error=/var/lib/mysql/mysqld.log
  log-error=/var/log/mysqld.log
  pid-file=/var/run/mysqld/mysqld.pid
  pid-file=/var/run/mysqld/mysqld.pid
<br><br>
Stop services:


 
service elasticsearch stop
NOTE: if you have file '''/etc/mysql/debian.cnf''' make sure you have correct socket location in it:
service httpd stop
 
service asterisk stop
  socket=/var/lib/mysql/mysql.sock
  service mor_server_loadstats stop
 
service crond stop
Or change correct location into mysql.cnf file (e.g. socket=/var/run/mysqld/mysqld.sock
service mor_alerts stop
 
service mor_aggregates stop
<br>


==Server 2==
==Server 2==
Line 170: Line 172:
Make sure data on Server 2 = data on Server 1. E.g. MySQL table structure and data are identical on both machines.
Make sure data on Server 2 = data on Server 1. E.g. MySQL table structure and data are identical on both machines.


<br>


<br><br>
==Server 1==
==Server 1==
 
Replace '''x15''' with your MOR version.
Debian:
  /usr/src/mor/x15/mysql/configure_mycnf.sh
  /etc/init.d/mysql restart
 
Centos/Fedora:
/etc/init.d/mysqld restart


  mysql -u root -p  
  mysql -u root -p  


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


Remember '''File''' and '''Position'''.
Remember '''File''' and '''Position'''.


UNLOCK TABLES;
  quit;
  quit;




<br>
<br><br>


==Server2==
==Server2==


Check if you can connect to first erver:
Check if you can connect to first server:


  mysql -h 111.111.111.111 -u rep2 -p
  mysql -h 111.111.111.111 -u rep2 -p


Proceed if ok:
Proceed if okay:


Debian:
Replace '''x15''' with your MOR version.
  /etc/init.d/mysql restart
  /usr/src/mor/x15/mysql/configure_mycnf.sh
 
Centos/Fedora:
/etc/init.d/mysqld restart


  mysql -u root -p  
  mysql -u root -p  
Line 212: Line 204:
  SLAVE STOP;  
  SLAVE STOP;  


  CHANGE MASTER TO MASTER_HOST='111.111.111.111', MASTER_USER='rep2', MASTER_PASSWORD='rep2', MASTER_LOG_FILE='mysql-bin.000001',  
  CHANGE MASTER TO MASTER_HOST='111.111.111.111', MASTER_USER='rep2', MASTER_PASSWORD='rep2', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=205;  
MASTER_LOG_POS=98;  


'''NOTE: Change MASTER_HOST, MASTER_LOG_FILE and MASTER_LOG_POS accordingly.'''
'''NOTE: Change MASTER_HOST, MASTER_LOG_FILE and MASTER_LOG_POS accordingly.'''
Line 219: Line 210:
  START SLAVE;
  START SLAVE;


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


Remember '''File''' and '''Position'''.
Remember '''File''' and '''Position'''.


UNLOCK TABLES;
  quit;
  quit;


<br>
 
<br><br>


==Server 1==
==Server 1==


Check if you can connect to first erver:
Check if you can connect to first server:


  mysql -h 222.222.222.222 -u rep1 -p
  mysql -h 222.222.222.222 -u rep1 -p


Proceed if ok:  
Proceed if okay:  


  mysql -u root -p  
  mysql -u root -p  
Line 242: Line 231:
  SLAVE STOP;  
  SLAVE STOP;  


  CHANGE MASTER TO MASTER_HOST='222.222.222.222', MASTER_USER='rep1', MASTER_PASSWORD='rep1', MASTER_LOG_FILE='mysql-bin.000001',  
  CHANGE MASTER TO MASTER_HOST='222.222.222.222', MASTER_USER='rep1', MASTER_PASSWORD='rep1', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=205;  
MASTER_LOG_POS=98;  


'''NOTE: Change MASTER_HOST, MASTER_LOG_FILE and MASTER_LOG_POS accordingly.'''  
'''NOTE: Change MASTER_HOST, MASTER_LOG_FILE and MASTER_LOG_POS accordingly.'''  
Line 251: Line 239:




<br>
<br><br>
Start services on both servers '''Server 1''' and '''Server 2''':
 
service elasticsearch start
service httpd start
service asterisk start
service mor_server_loadstats start
service crond start
service mor_alerts start
service mor_aggregates start


==Log files==
==Log files==


In order for MySQL Replication to function properly you need to check log files in /var/lib/mysql
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.
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 do not represent real files in this folder.


Same with file ''slave-relay-log.index''
Do the same with the file ''slave-relay-log.index''.




<br>
<br><br>
==Testing==
==Testing==


Line 273: Line 270:
  SHOW SLAVE STATUS\G
  SHOW SLAVE STATUS\G


If everything is ok you should see such lines between others on both servers:
If everything is okay, you should see lines between others on both servers:


  Slave_IO_State: Waiting for master to send event
  Slave_IO_State: Waiting for master to send event
Line 280: Line 277:




If Replication is off you will see:
If Replication is off, you will see:


  Slave_IO_State:
  Slave_IO_State:
Line 286: Line 283:




Quick way to do this:
The quick way to do this is:


  mysql -u root -e 'SHOW SLAVE STATUS\G' | grep 'Slave'
  mysql -u root -e 'SHOW SLAVE STATUS\G' | grep 'Slave'
Line 297: Line 294:




'''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;'''
'''Never use phpmyadmin to check if data is same on both servers. Use mysql console and a command like count. For example: SELECT COUNT(*) FROM destinations;'''
 


<br><br>
==Troubleshooting==
==Troubleshooting==


Check file: /var/lib/mysql/mysqld.log
Check file: /var/lib/mysql/mysqld.log<br>
Check GUI production logs and look for error messages beginning with: "Replication exception:".


<br><br>
==='Could not find first log file name in binary log index file' from master when reading data from binary 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.
We go to the master and check ''/var/lib/mysql/master-log-bin.index'' file.


For example it can show:
For example, it can show:


  /var/lib/mysql/mysql-bin.000001
  /var/lib/mysql/mysql-bin.000001
Line 323: Line 322:
  /var/lib/mysql/mysql-bin.000005
  /var/lib/mysql/mysql-bin.000005


Restart mysql on this server and try with slave again.
Restart mysql on this server and try with the 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.
This is just an example - some other file's records can be missing. Make sure the file has all related files' records, which are in same folder.


<br><br>
=== Failed to open the relay log ===
=== Failed to open the relay log ===


Line 334: Line 334:
* restart mysql
* 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!
Your mysql will start with fresh relay logs and is getting the missing data directly from the master server, as there will probably still be binlogs!


<br><br>
=== Couldn't find MySQL manager or server ===
=== Couldn't find MySQL manager or server ===


Line 344: Line 345:
<br>
<br>


* [[Default mysql.cnf]] - can be usefull to go back if something goes wrong and no backup is done
* [[Default mysql.cnf]] - can be useful 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
* If server is rebooted - it takes ~45s for the database to start synchronizing after MySQL is up.


<br><br>
===Could not initialize master info structure, more error messages can be found in the MySQL error log===
===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
Please check this link: http://forums.mysql.com/read.php?26,163227,163241#msg-163241


=== Broken replication fix ===
<br><br>
=== Broken replication ===
 
Database replication is broken and should be rebuilt. This usually happens when mysqld daemon is not closed cleanly, for example, the server was reset due to a power interruption.
 
To prevent broken replication, make sure that you either do software shutdown or stopping MySQL service before powering down one of the servers or cutting network connection between servers.


http://rackerhacker.com/2008/01/09/mysql-replication-breakdown/
http://rackerhacker.com/2008/01/09/mysql-replication-breakdown/


=== What to do, if you have errors with binlogs ===
<br><br>
Delete all binlog files from both servers (but leave databases structures and data!), then reapply replication.
 
=== What to do if you have errors with binlogs ===
Delete all binlog files from both servers (but leave database structures and data!), then reapply replication.
'''You have to test GUI on both servers after you apply MySQL replication.'''
'''You have to test GUI on both servers after you apply MySQL replication.'''


<br><br>
=== No more MySQL Crash Safe Replication in 5.0? ===
http://www.mysqlperformanceblog.com/2008/01/29/no-more-mysql-crash-safe-replication-in-50/
<br><br>
=== Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' ===
Error looks like this:
# mysql -h 222.222.222.222 -u rep1 -prep1
ERROR 2003 (HY000): Can't connect to MySQL server on '222.222.222.222' (113)
Please check firewalls and make sure that port TCP 3306 is not blocked.
<br><br>
=== TRUNCATE usage ===
Do not use TRUNCATE statement because it can ruin Replication.
* http://bugs.mysql.com/bug.php?id=36763
* http://dev.mysql.com/doc/refman/5.1/en/replication-features-truncate.html
<br><br>
=== Maintenance ===
To avoid database synchronization issues during maintenance, please do software shutdown on servers or at least stop MySQL service before procedures.
The command to shutdown server is:
poweroff
or
shutdown -h now
The command to stop MySQL service is:
service mysqld stop
<br><br>
===How long I can keep servers offline without breaking replication?===
By default, MySQL binlogs expire within 3 days.
That means, that replication gets broken if servers stay without connection for more than 3 days.


=== No more MySQL Crash Safe Replication in 5.0 ? ===
<br><br>
http://www.mysqlperformanceblog.com/2008/01/29/no-more-mysql-crash-safe-replication-in-50/
 
= See also =
* [http://www.ducea.com/2008/02/13/mysql-skip-duplicate-replication-errors/ slave-skip-errors=1062] more details
* [http://mysqldatabaseadministration.blogspot.com/2006/12/mysql-replication-stopped-lock-wait.html Replication stopped: Lock wait timeout exceeded]

Latest revision as of 12:03, 28 July 2022

Do NOT use this manual for MOR X17 or M4


MySQL Replication manual works only on servers where MySQL 5.1, 5.5 or 5.6 is installed

Mysql logo.png




THIS TUTORIAL WILL GUIDE YOU THROUGH HOW TO IMPLEMENT MASTER<->MASTER REPLICATION

Before you start, make a backup of your database.



Create user on Server 1

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

Login as root and issue such commands: 

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 *.* TO 'rep2'@'222.222.222.222' WITH GRANT OPTION ;

Go to Server 2 and test the connection: 

mysql -h 111.111.111.111 -u rep2 -prep2



Create user on Server 2

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

Login as root and issue such commands: 

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 *.* TO 'rep1'@'111.111.111.111' WITH GRANT OPTION ;

Go to Server 1 and test the connection: 

mysql -h 222.222.222.222 -u rep1 -prep1


Note: Make sure you have applied REPLICATION CLIENT and REPLICATION SLAVE on both users.



Server 1 my.cnf

[mysqld]
slow_query_log=1
slow_query_log_file=/var/log/mysql-log-slow-queries.log
long_query_time=20
skip-name-resolve
secure_file_priv=
sql_mode=
innodb_file_per_table=1
bind-address = 0.0.0.0
binlog-do-db = mor
# binlog-do-db = mor_mnp          #uncomment if there is mor_mnp database
# binlog-do-db = opensips         #uncomment if there is opensips database
server-id = 10
auto_increment_increment = 10
auto_increment_offset = 1
replicate-do-db = mor
# replicate-do-db = mor_mnp       #uncomment if there is mon_mnp database
# replicate-do-db = opensips      #uncomment if there is opensips database
binlog_format=MIXED
log-bin = /var/lib/mysql/mysql-bin.log
log-bin-index = /var/lib/mysql/master-log-bin.index
sync_binlog = 1               #increases reliability, but decreases performance
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

slave-skip-errors=1061,1062,1213,1060,1032
slave_transaction_retries=3600

expire_logs_days = 3
# innodb_flush_log_at_trx_commit = 1
# innodb_support_xa

max_connections = 300
slave_transaction_retries = 3600

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid



Stop services: 

service elasticsearch stop
service httpd stop
service asterisk stop
service mor_server_loadstats stop
service crond stop
service mor_alerts stop
service mor_aggregates stop

Server 2 my.cnf

[mysqld]
slow_query_log=1
slow_query_log_file=/var/log/mysql-log-slow-queries.log
long_query_time=20
skip-name-resolve
secure_file_priv=
sql_mode=
innodb_file_per_table=1
bind-address = 0.0.0.0
binlog-do-db = mor
# binlog-do-db = mor_mnp          #uncomment if there is mor_mnp database
# binlog-do-db = opensips         #uncomment if there is opensips database
server-id = 20
auto_increment_increment = 10
auto_increment_offset = 2
replicate-do-db = mor
# replicate-do-db = mor_mnp       #uncomment if there is mon_mnp database
# replicate-do-db = opensips      #uncomment if there is opensips database
binlog_format=MIXED
log-bin = /var/lib/mysql/mysql-bin.log
log-bin-index = /var/lib/mysql/master-log-bin.index
sync_binlog = 1               #increases reliability, but decreases performance
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

slave-skip-errors=1061,1062,1213,1060,1032
slave_transaction_retries=3600

expire_logs_days = 3
# innodb_flush_log_at_trx_commit = 1
# innodb_support_xa

max_connections = 300
slave_transaction_retries = 3600

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid



Stop services: 

service elasticsearch stop
service httpd stop
service asterisk stop
service mor_server_loadstats stop
service crond stop
service mor_alerts stop
service mor_aggregates stop

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

Replace x15 with your MOR version. 

/usr/src/mor/x15/mysql/configure_mycnf.sh

mysql -u root -p 

SHOW MASTER STATUS;

Remember File and Position. 

quit;




Server2

Check if you can connect to first server: 

mysql -h 111.111.111.111 -u rep2 -p

Proceed if okay:

Replace x15 with your MOR version. 

/usr/src/mor/x15/mysql/configure_mycnf.sh

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=205;

NOTE: Change MASTER_HOST, MASTER_LOG_FILE and MASTER_LOG_POS accordingly. 

START SLAVE;

SHOW MASTER STATUS;

Remember File and Position. 

quit;




Server 1

Check if you can connect to first server: 

mysql -h 222.222.222.222 -u rep1 -p

Proceed if okay: 

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=205;

NOTE: Change MASTER_HOST, MASTER_LOG_FILE and MASTER_LOG_POS accordingly. 

START SLAVE;
quit;




Start services on both servers Server 1 and Server 2: 

service elasticsearch start
service httpd start
service asterisk start
service mor_server_loadstats start
service crond start
service mor_alerts start
service mor_aggregates start

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 do not represent real files in this folder.

Do the same with the 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 okay, you should see 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


The quick way to do this is: 

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 a command like count. For example: SELECT COUNT(*) FROM destinations;



Troubleshooting

Check file: /var/lib/mysql/mysqld.log
Check GUI production logs and look for error messages beginning with: "Replication exception:".



'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 the slave again.

This is just an example - some other file's records can be missing. Make sure the file has all related files' 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 probably still be binlogs!



Couldn't find MySQL manager or server

Comment out basedir in my.cnf



  • Default mysql.cnf - can be useful to go back if something goes wrong and no backup is done.
  • If server is rebooted - it takes ~45s for the 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

Database replication is broken and should be rebuilt. This usually happens when mysqld daemon is not closed cleanly, for example, the server was reset due to a power interruption.

To prevent broken replication, make sure that you either do software shutdown or stopping MySQL service before powering down one of the servers or cutting network connection between servers.

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 database 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/



Can't connect to MySQL server on 'xxx.xxx.xxx.xxx'

Error looks like this: 

# mysql -h 222.222.222.222 -u rep1 -prep1
ERROR 2003 (HY000): Can't connect to MySQL server on '222.222.222.222' (113)

Please check firewalls and make sure that port TCP 3306 is not blocked.



TRUNCATE usage

Do not use TRUNCATE statement because it can ruin Replication.



Maintenance

To avoid database synchronization issues during maintenance, please do software shutdown on servers or at least stop MySQL service before procedures.

The command to shutdown server is: 

poweroff

or 

shutdown -h now

The command to stop MySQL service is: 

service mysqld stop



How long I can keep servers offline without breaking replication?

By default, MySQL binlogs expire within 3 days. That means, that replication gets broken if servers stay without connection for more than 3 days.



See also

Retrieved from "https://wiki.kolmisoft.com/index.php?title=MySQL_Replication&oldid=27523"