MySQL Replication – Problem After Export/Import on Working Replication

MySQLreplication

I have 2 databases in master – slave replication.

I had some errors in the replication due to inserts in the slave, so I corrected it and executed:

stop slave;
set global sql_slave_skip_counter=2;
start slave;

After it, I saw that my change wasn't correct so I decided to export the database and import it to the slave.

Now the show slave status seems ok:

 Slave_IO_State: Waiting for master to send event
                  Master_Host: ip
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 7218177
               Relay_Log_File: slave-relay-bin.009595
                Relay_Log_Pos: 252
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 7218177
              Relay_Log_Space: 553
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
 Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:

But if I change anything on the master I don't see the change in the slave.
Any suggestions of what can be wrong?

Best Answer

I would check the export process.

If you use mysqldump, create this script on the Slave and execute it on the Slave:

#
# Dump the Master
# Use the coordinates of the Master at the point the mysqldump starts
#
DUMPFILE=/path/to/dumpfile.sql
MYSQL_USER=masterroot
MYSQL_PASS=masterrootpassword
MYSQL_CONN="-u${MYSQL_USER} -p ${MYSQL_PASS}"
SQL="SELECT GROUP_CONCAT(schema_name) FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL}" | sed 's/,/ /g'`
echo "STOP SLAVE;" > ${DUMPFILE}
MYSQLDUMP_OPTIONS="--master_data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --add-drop-database"
mysqldump -h ipaddrofmaster ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} -B ${DBLIST} >> ${DUMPFILE}
echo "START SLAVE;" >> ${DUMPFILE}
#
# Load the Slave
#
MYSQL_USER=slaveroot
MYSQL_PASS=slaverootpassword
MYSQL_CONN="-u${MYSQL_USER} -p ${MYSQL_PASS}"
mysql -h localhost ${MYSQL_CONN} < ${DUMPFILE}

This script will move all user-defined database. It will not import the mysql schema from the Master. This is designed so that the Master can be from an earlier version of MySQL than the Slave and will still import the data. All grants should be left in tact.

You need to make sure of the following:

  • the Master and Slave are using the same binlog_format (Being Cautious)
  • the Master and Slave are using two different values for server_id

If Slave_IO_Running is Yes, Slave_SQL_Running is Yes, you are definitely writing on the Master, and the Relay_Log_Space is not moving, then one of the following might be happening

Related Question