CODEWITHSUNDEEP
mysqlbash
Dan Lee
Dan Lee

Reputation: 704

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}

Upvotes: 1

Views: 3577

Answers (2)

qixiaobo
qixiaobo

Reputation: 1

/usr/local/mysql/bin/mysqldump -uroot -p{pwd} --skip-lock-tables  --databases f6dms $(mysql -uroot -p{pwd} -Df6dms -Bse "show tables like 'tm_monitor_avg_price_%'"|awk '{print "--ignore-table=f6dms."$1}'|xargs)| gzip > /data/backup/database_f6dms_`date '+%m-%d-%Y'`.sql.gz;

Upvotes: 0

Suman EStatic
Suman EStatic

Reputation: 81

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

general syntax

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

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

Thanks Suman W.

Upvotes: 1

Related Questions