Dan Lee Dan Lee - 11 days ago 8
MySQL Question

Ignoring table in mysqldump?

Hi I'm doing a database

copy and paste
from a master to a slave. However there is a table on the slave that I don't want to be overwritten by the master.

I have been trying out the following bash script which is ran via cron job - but it keeps overwriting the slave table I want to ignore. What am I doing wrong?

#!/bin/bash

#Database login credentials - need to be changed accordingly
dbHost="localhost"

#Master (Staging)
dbMastUser="admin_site"
dbMastName="admin_site_pineapple_master"

#Slave (Live)
dbSlavUser="admin_sync"
dbSlavName="admin_site_pineapple_slave"

dbPass="ExamplePassword"


EXCLUDED_TABLES=(
forms-responses
)

IGNORED_TABLES=''
for TABLE in "${EXCLUDED_TABLES[@]}"
do :
IGNORED_TABLES+=" --ignore-table=${dbMastName}.${TABLE}"
done

#Update the database from the Master to the Slave
mysqldump -h ${dbHost} -u ${dbMastUser} -p${dbPass} ${dbMastName} ${IGNORED_TABLES} | mysql -h ${dbHost} -u ${dbSlavUser} -p${dbPass} ${dbSlavName}

Answer

For ignoring tables you must use this syntax

mysqldump -h {hostname} -u {username} -p{password} --ignore-table test.votes test > E:/db_backups/test_1480080906.sql

Note : we need to set the options to ignore some tables

Thanks Suman W.