Some times MySQL replication creates problems and slave could not sync properly from master. It may cause with lots of reason. Here is a way to fix it.
Warning: After using this tutorial, All of your bin-log files will be deleted, So if you want, you may take a backup of bin-log files first and then follow the instructions.
At Slave Server:
At first we need to stop slave on slave server. Login to mysql server and execute following command.
mysql> STOP SLAVE;
At Master Server:
After stopping slave go to master server and reset the master state using following command.
mysql> RESET MASTER;
mysql> FLUSH TABLES WITH READ LOCK;
Take a dump of database is being replicated using following command.
# mysqldump -u root -p mydb > mydb-dump.sql
After taking backup unlock the tables at master server.
mysql> UNLOCK TABLES;
At Slave Server:
Restore database backup taken on slave server using following command.
# mysql -u root -p mydb < mydb-dump.sql
Login to mysql and execute following commands to reset slave state also.
mysql> RESET SLAVE;
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1;
After resetting slave start slave replication
mysql> START SLAVE;
Now your replication has been re sync same as newly configured. you can verify it using following commands.
mysql> show slave status \G
- 76Here we are going to create a Master -> Master -> Slave -> Slave replications architecture. I am expecting here , you have MySQL installed and set-up as normal. Master 1 will be known as Master 1 and Slave 2 with IP 10.1.1.1 Master 2 will be known as…
- 65Birth Of MySQL MySQL started out with the intention of using the mSQL database system to connect to tables using fast low-level (ISAM) routines. However, after some testing, They conclude that mSQL was not fast enough or flexible enough. This resulted in a new SQL interface to our database but with…
- 62Here we will help you to setup master-slave replication between MySQL servers. Setup Details: Master Server: 10.0.10.12 Slave Server: 10.0.10.18 Database: empmaster 1. Setup MySQL Master Server Create an mysql account on Master server with REPLICATION SLAVE privileges to which replication client will connect to master. Block write statement on…
- 60Here we will help you to set up Master-Master replication between MySQL servers. In this setup if any changes made on either server will update on an other one. Setup Details: Master-1: 10.0.10.12 Master-2: 10.0.10.18 Database: empmaster Step 1. Set Up MySQL Master-1 Server Edit MySQL configuration file and add the following…
- 4890% of the time, this error is due either to the MySQL Server not running, or else firewall configuration on the Windows server blocking access on port 3306 (or whatever port your MySQL instance is configured to use). Error: 2003 (CR_CONN_HOST_ERROR) Message: Can't connect to MySQL server on '%s' (%d)