Archive for December 24th, 2013

24
Dec

mysql master master replication

   Posted by: admin    in Mẹo vặt của hiếu râu

MySQL Master-Master replication:
Master-1 IP: 0.0.0.1
Master-2 IP: 0.0.0.2

Master-1 my.cnf configuration:

log-bin

log_warnings

log_slow_queries = /var/lib/mysql/slow.log

long_query_time = 5

tmpdir = /tmp

server-id = 1

auto_increment_increment = 2

auto_increment_offset = 1

relay-log = mysql-relay-bin

Master-2 my.cnf configuration:

log-bin

log_warnings

log_slow_queries = /var/lib/mysql/slow.log

long_query_time = 5

tmpdir = /tmp

server-id = 2

auto_increment_increment = 2

auto_increment_offset = 2

relay-log = mysql-relay-bin

Replication procedure:

1st setup Master1 as Master and Master2 as slave for Master-1:

Follow below steps:
On Master-1:

Step 1: mysql> grant replication slave on *.* to ‘replicationuser1′@’0.0.0.2′ identified by ‘password’;

Step 2: mysql> show master status;
It shows file name and position, Use these records on Master-2 to run it as slave for Master-1.

Step 3: Now log on to master-2 and run the below query:

CHANGE MASTER TO MASTER_HOST=’0.0.0.1′, MASTER_USER=’replicationuser1′,MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’mysql-bin.000017′,MASTER_LOG_POS=751;

Step 4: start slave
Step 5: show slave status \G

On this status, the following 2 records should be as follows
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

These 2 records indicates Replication status. If these parameters show “Yes” that means replication is running successfully.

Setup Master2 as Master and Master1 as slave for Master-2:

On Master-2 server:

Step 1: mysql> grant replication slave on *.* to ‘replicationuser2′@’0.0.0.1′ identified by ‘password’;

Step 2: mysql> show master status;

Step 3: Now log on to master-1 and run the below query:
CHANGE MASTER TO MASTER_HOST=’0.0.0.2′, MASTER_USER=’replicationuser2′,MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’mysql-bin.000002′,MASTER_LOG_POS=536;
Step 4: start slave
Step 5: show slave status \G

The following parameters should show “Yes”, so that replication is running successfully

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

On both servers “slave_IO_Running” and “slave_SQL_Running” parameters should always be “Yes” for successful Master-Master Replication.

http://rajesh9333.wordpress.com/2013/09/12/mysql-master-master-replication-on-redhat-and-centos-servers/

[root@api01 ~]# cat /etc/snmp/mysql.sh

#!/bin/bash

if [ "$1" == "processlist" ]; then

echo -n “0″

echo “select count(id) from information_schema.processlist;” | mysql –defaults-extra-file=/root/my.key | tail -n 1

exit

fi

echo “update SYNC.SYNC set Dt=NOW() where id=1;” | mysql –defaults-extra-file=/root/my.key > /dev/null 2>&1

echo “show slave status\G;” | mysql –defaults-extra-file=/root/my.key | grep Read_Master | awk ‘{print 0$2}’

create database SYNC;

create table SYNC (id int, dt datetime);

exec mysqlslave /etc/snmp/mysql.sh

exec mysqlprocess /etc/snmp/mysql.sh processlist

===== mysql 8.x

root@mysql02:~# while true; do if [[ $(mysql -e "SHOW SLAVE STATUS\G" | grep "Last_SQL_Error:" | grep -c "test.sync") -gt 0 ]]; then mysql -e “STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;”; else break; fi; sleep 1; done

====

No Comments
24
Dec

recover mysql master slave replication

   Posted by: admin    in Mẹo vặt của hiếu râu

This is the full step-by-step procedure to resync a master-slave replication from scratch:

At the master:

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

And copy the values of the result of the last command somewhere.

Wihtout closing the connection to the client (because it would release the read lock) issue the command to get a dump of the master:

mysqldump -uroot -p --all-databases > /a/path/mysqldump.sql

Now you can release the lock, even if the dump hasn’t end. To do it perform the following command in the mysql client:

UNLOCK TABLES;

Now copy the dump file to the slave using scp or your preferred tool.

At the slave:

Open a connection to mysql and type:

STOP SLAVE;

Load master’s data dump with this console command:

mysql -uroot -p < mysqldump.sql

Sync slave and master logs:

RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;

Where the values of the above fields are the ones you copied before.

Finally type

START SLAVE;

And to check that everything is working again, if you type

SHOW SLAVE STATUS;

you should see:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

That’s it!

http://stackoverflow.com/questions/2366018/how-to-re-sync-the-mysql-db-if-master-and-slave-have-different-database-incase-o

No Comments
Back to top