Difference between revisions of "MySQL Replication"
(27 intermediate revisions by 5 users not shown) | |||
Line 1: | Line 1: | ||
<big><big><b>MySQL Replication works only on servers where MySQL 5. | <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> | ||
Line 58: | Line 61: | ||
[mysqld] | [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 | 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 = mor_mnp #uncomment if there is mor_mnp database | ||
Line 79: | Line 81: | ||
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 | ||
slave-skip-errors=1061,1062,1213 | slave-skip-errors=1061,1062,1213,1060,1032 | ||
slave_transaction_retries=3600 | slave_transaction_retries=3600 | ||
expire_logs_days = 3 | expire_logs_days = 3 | ||
# | # innodb_flush_log_at_trx_commit = 1 | ||
# | # innodb_support_xa | ||
max_connections = 300 | max_connections = 300 | ||
slave_transaction_retries = 3600 | slave_transaction_retries = 3600 | ||
datadir=/var/lib/mysql | |||
socket=/var/lib/mysql/mysql.sock | |||
[mysqld_safe] | [mysqld_safe] | ||
log-error=/var/ | log-error=/var/log/mysqld.log | ||
pid-file=/var/run/mysqld/mysqld.pid | pid-file=/var/run/mysqld/mysqld.pid | ||
<br><br> | <br><br> | ||
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== | ==Server 2 my.cnf== | ||
[mysqld] | [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 | 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 | # binlog-do-db = mor_mnp #uncomment if there is mor_mnp database | ||
# binlog-do-db = opensips | # binlog-do-db = opensips #uncomment if there is opensips database | ||
server-id = 20 | server-id = 20 | ||
auto_increment_increment = 10 | auto_increment_increment = 10 | ||
auto_increment_offset = 2 | auto_increment_offset = 2 | ||
replicate-do-db = mor | replicate-do-db = mor | ||
# replicate-do-db = mor_mnp | # replicate-do-db = mor_mnp #uncomment if there is mon_mnp database | ||
# replicate-do-db = opensips | # replicate-do-db = opensips #uncomment if there is opensips database | ||
binlog_format=MIXED | 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 | ||
slave-skip-errors=1061,1062,1213 | slave-skip-errors=1061,1062,1213,1060,1032 | ||
slave_transaction_retries=3600 | slave_transaction_retries=3600 | ||
expire_logs_days = 3 | expire_logs_days = 3 | ||
# | # innodb_flush_log_at_trx_commit = 1 | ||
# | # innodb_support_xa | ||
max_connections = 300 | max_connections = 300 | ||
slave_transaction_retries = 3600 | slave_transaction_retries = 3600 | ||
datadir=/var/lib/mysql | |||
socket=/var/lib/mysql/mysql.sock | |||
[mysqld_safe] | [mysqld_safe] | ||
log-error=/var/ | 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 | |||
service httpd stop | |||
service asterisk stop | |||
service mor_server_loadstats stop | |||
service crond stop | |||
service mor_alerts stop | |||
service mor_aggregates stop | |||
==Server 2== | ==Server 2== | ||
Line 192: | Line 175: | ||
<br><br> | <br><br> | ||
==Server 1== | ==Server 1== | ||
Replace '''x15''' with your MOR version. | |||
/usr/src/mor/ | /usr/src/mor/x15/mysql/configure_mycnf.sh | ||
mysql -u root -p | mysql -u root -p | ||
SHOW MASTER STATUS; | SHOW MASTER STATUS; | ||
Remember '''File''' and '''Position'''. | Remember '''File''' and '''Position'''. | ||
quit; | quit; | ||
Line 218: | Line 197: | ||
Proceed if okay: | Proceed if okay: | ||
/usr/src/mor/ | Replace '''x15''' with your MOR version. | ||
/usr/src/mor/x15/mysql/configure_mycnf.sh | |||
mysql -u root -p | mysql -u root -p | ||
Line 231: | Line 210: | ||
START SLAVE; | START SLAVE; | ||
SHOW MASTER STATUS; | SHOW MASTER STATUS; | ||
Remember '''File''' and '''Position'''. | Remember '''File''' and '''Position'''. | ||
quit; | quit; | ||
Line 264: | Line 240: | ||
<br><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== | ||
Line 314: | Line 299: | ||
==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> | <br><br> | ||
Line 367: | Line 353: | ||
<br><br> | <br><br> | ||
=== Broken replication | === 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/ | ||
<br><br> | <br><br> | ||
=== What to do if you have errors with binlogs === | === 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. | Delete all binlog files from both servers (but leave database structures and data!), then reapply replication. | ||
Line 397: | Line 388: | ||
* http://dev.mysql.com/doc/refman/5.1/en/replication-features-truncate.html | * 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. | |||
<br><br> | <br><br> |
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
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.
- http://bugs.mysql.com/bug.php?id=36763
- http://dev.mysql.com/doc/refman/5.1/en/replication-features-truncate.html
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.