Home > Databases, IT Architecture > Mysql tricks – fix replication error

Mysql tricks – fix replication error

April 17th, 2011

Fixing MySQL service start problem:”manager of pid file could nor be found”

1. Check for any invalid entry in /apps/mysql/etc/my.cnf and correct it and restart service. If unsuccessfull, follow step#2 and below.

2. Delete all binary log, error log files under the directories below and start the service:

/apps/mysql/log
/apps/mysql/log.replicate
/apps/mysql/log.relay

3. If step#2 is successful and required to fix service startup, reset replication state using the command:

a) shell> mysql -h host -u user ?p
b) mysql> reset master;
c) mysql> stop slave;
d) mysql> reset slave;
e) mysql> start slave;

Note: Step#3 needs to be executed in both the servers in the replication pair

Get mysql version info and server variables from server

Shell> mysqladmin version ?h -u -p

Shell> mysqladmin variables ?h -u -p

Fix replication error (if slave is NOT beyond the current binary log file master is using)

This is to skip the current transaction that the replication thread is hung on and continue (at SLAVE server). You can issue the following command in mysql to skip a transaction:

Mysql> STOP SLAVE;

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

NOTE:

1.Use the value 1 for any SQL statement that does not use AUTO_INCREMENT or LAST_INSERT_ID(), otherwise you will need to use the value 2.

2.Statements that use AUTO_INCREMENT or LAST_INSERT_ID() take up 2 events in the binary log.

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G;

Continue repeating the steps until the replication error is cleared.

Fix replication error (if slave is beyond the current binary log file master is using)

You need to follow backup databases from master server and restore it to slave server to sync and reinitiate replication. Follow the steps below to do that:

Step#1: Stop slave thread at the slave server (on Slave server):

mysql> STOP SLAVE;

Step#2: Impose a global read lock (in Master Server). Start the command line client and flush all tables and block write statements by executing the FLUSH TABLES WITH READ LOCK statement:

mysql> FLUSH TABLES WITH READ LOCK;

Note: DO NOT close the connection console from where the command is executed, otherwise lock will be released

Step#3: Backup databases from Master server Backup the databases from the master server through executing the command below:

shell> mysqldump ?h -u -p –all-databases –opt –routines –master-data –single-transaction > dbdump.db

Step#4: Restore databases at Slave Server Restore the database at server with the backup taken at step#3, using the command below

Shell> mysql -h -u -p < dbdump.db Step#5: Start Slave thread (at slave server) Start slave thread at the slave server using the command: mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G;

Step#6: Release global read lock at Master Server Release the write block at master server imposed at step#2 using the command below at the same console used:

mysql> UNLOCK TABLES;

mysql> exit;

Reset replication

If the data are already synced between the master server and slave server and you only need to reset replication status to its initial state, execute the commands below in BOTH master and slave servers:

shell> mysql ?h -u -p mysql> reset master;

mysql> stop slave;

mysql> reset slave;

mysql> start slave;

Change replication configuration setting

If you change the replication configuration setting in the my.cnf configuration file and mysql service is not catching it up after a restart, follow any one of the step below:

1. Stop mysql server

2. Delete the file: /apps/mysql/log.replicate/master.info

3. Update mysql configuration file with the desired replication configuration setting (if not already done).

4. Start the mysql service

NOTE:

Replication configuration setting can also be changed in run-time through mysql command:

mysql> CHANGE MASTER TO -> MASTER_HOST=’master_host_name’, -> MASTER_USER=’replication_user_name’, -> MASTER_PASSWORD=’replication_password’, -> MASTER_LOG_FILE=’recorded_log_file_name’, -> MASTER_LOG_POS=recorded_log_position;

Point in time restore

mysql -uroot ?mypassword POSITIONING EXAMPLE

shell> mysqlbinlog –stop-position=”368312″ /var/log/mysql/bin.123456 \ | mysql -u root -p shell> mysqlbinlog –start-position=”368315″ /var/log/mysql/bin.123456 \ | mysql -u root -p

NOTE:

In this example, I am assuming that log-bin mode has been used to start the daemon. You can also start this using MySQL admin tool. The logfiles are usually stored in the base directory of the data folder. This example uses a stop date to specify when the log replay should stop. You can also use log positioning. To know which log to use, use SHOW MASTER STATUS.

Examining binary log

mysqlbinlog –start-date=”2008-05-01 13:49:00″ –stop-date=”2008-05-01 13:51:59″ binary_logfile.000004 > C:\drop\mysql_restore.sql

NOTE:

This will produce a file and give dates and positions on what changes occurred so you can more accurately specify what stop and start times to use.

Good Luck!


Categories: Databases, IT Architecture Tags:
Comments are closed.