Revision as of 08:32, 11 September 2012

MySQL Replication works only on servers where MySQL 5.x 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 `mor`.* 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 `mor`.* 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]
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   #comment out this line on MySQL 5.5
master-user = rep1              #comment out this line on MySQL 5.5
master-password = rep1          #comment out this line on MySQL 5.5
master-port = 3306              #comment out this line on MySQL 5.5
master-connect-retry = 60       #comment out this line on MySQL 5.5
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

slave-skip-errors=1061,1062

expire_logs_days = 3
# 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

max_connections = 300

;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 the correct socket location in it:

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

Or change the 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 = 10
auto_increment_offset = 2
master-host = 111.111.111.111 #comment out this line on MySQL 5.5
master-user = rep2            #comment out this line on MySQL 5.5
master-password = rep2        #comment out this line on MySQL 5.5
master-port = 3306            #comment out this line on MySQL 5.5
master-connect-retry = 60     #comment out this line on MySQL 5.5
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

slave-skip-errors=1061,1062

expire_logs_days = 3
# 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

max_connections = 300

;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 the correct socket location in it:

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

Or change the 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 server:

mysql -h 111.111.111.111 -u rep2 -p

Proceed if okay:

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

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

@@ Line 156: / Line 156: @@
   slave-skip-errors=1061,1062
-  expire_logs_days = 7
+  expire_logs_days = 3
   # '''sync_binlog = 1'''
   # '''innodb_flush_log_at_trx_commit = 1'''

Difference between revisions of "MySQL Replication"

Revision as of 08:32, 11 September 2012

Contents

Create user on Server 1

Create user on Server 2

Server 1 my.cnf

Server 2 my.cnf

Server 2

Server 1

Server2

Server 1

Log files

Testing

Troubleshooting

'Could not find first log file name in binary log index file' from master when reading data from binary log

Failed to open the relay log

Couldn't find MySQL manager or server

Could not initialize master info structure, more error messages can be found in the MySQL error log

Broken replication fix

What to do if you have errors with binlogs

No more MySQL Crash Safe Replication in 5.0?

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

TRUNCATE usage

See also

Navigation menu

Page actions

Page actions

Personal tools

Navigation

Search

Tools