Geek asks and answers

Percona pt-table-sync: how to work with multiple tables?

At the command prompt, this will successfully update table1:

pt-table-sync --execute h=host1,D=db1,t=table1 h=host2,D=db2

However, if I want to update more than one table, I am not sure how to write it. This also updates table1 and ignores other tables:

pt-table-sync --execute h=host1,D=db1,t=table1,table2,table3 h=host2,D=db2

And this gives me an error:

pt-table-sync --execute h=host1,D=db1 --tables table1,table2,table3 h=host2,D=db2

Does anyone have an example of how to list "-tables" ... so that it successfully updates all the tables in the list?

+5
source share
2 answers

The option --tablesseems incompatible with the DSN note, you get this error:

You specified a database, but not a table in h = localhost, D = test.
    Are you trying to synchronize only the tables in the test database?
    If so, use '--databases test' instead.

, --databases, --tables.

, test.foo test.bar, , test.bar dewey.

:

$ pt-table-sync h=huey h=dewey --databases test --tables foo,bar --execute --verbose

# Syncing h=dewey
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      0       0      3      0 Chunk     15:26:15 15:26:15 2    test.bar
#      0       0      0      0 Chunk     15:26:15 15:26:15 0    test.foo

test.bar.

test .

+3

, . pt-table-sync . , . , Live- , , Live Live... , : )

script, mysql_sync_live_to_stage.sh, :

#!/bin/bash
# sync db live to staging

error_log_file='./mysql_sync_errors.log'
echo $(date +"%Y %m %d %H:%M") > $error_log_file

function sync_table()
{
    pt-table-sync --no-foreign-key-checks --execute 
        h=DB_1_HOST,u=DB_1_USER,p=DB_1_PASSWORD,D=$1,t=$3
        h=DB_2_HOST,u=DB_2_USER,p=DB_2_PASSWORD,D=$2,t=$3 >> $error_log_file
}

# SYNC ALL TABLES IN name_of_live_database
mysql -h "DB_1_HOST" -u "DB_1_USER" -pDB_1_PASSWORD -D "DB_1_DBNAME" -e "SHOW TABLES" | 
        egrep -i '[0-9a-z\-\_]+' | egrep -i -v 'Tables_in' | while read -r table ; do
    echo "Processing $table"
    sync_table "name_of_live_database" "name_of_staging_database" $table
done

# FIX Config Settings For Staging
echo "Cleanup Queries..."
mysql -h "DB_2_HOST" -u "DB_2_USER" -pDB_2_PASSWORD -D "DB_2_DBNAME" 
    -e "UPDATE name_of_staging_database.nameofmyconfigtable SET value='bar' 
    WHERE config_id='foo'"
mysql -h "DB_2_HOST" -u "DB_2_USER" -pDB_2_PASSWORD -D "DB_2_DBNAME" 
    -e "UPDATE name_of_staging_database.nameofmyconfigtable SET value='bar2' 
    WHERE config_id='foo2'"
echo "Done"

live, do. , -no-foreign-key-check.

... , , "git pull -f origin master" .

0

More articles:


All Articles