CODEWITHSUNDEEP
mysqlbashshell
papacico
papacico

Reputation: 169

Copying table from Server A to server B

We have a script which is working perfectly, we use it to copy a huge database from server A to server B. Now I want to copy just a table from server A to server B having the table name as a variable so the script should only ask for the table name and copy the table from A to B. This is the script I have made, I must confess I am not very experienced in shell scripting.

#!/bin/sh

#Run on Server A
TABLENAME=$1

echo $TABLENAME

_now=$(date +"%A %d-%m-%Y "at" %T")

#Copy table $TABLENAME  from server A to server B
#Dump table into /directory server A
mysqldump -u admin -p'*****' database_name $TABLENAME >             /directory/$TABLENAME.sql
# Copie table to server B
scp /directory/$TABLENAME.sql root@server_b.domain.com:/directory/
# Replace table in database on server B
ssh root@server_b.domain.com "mysql -f -u admin -p'******' database_name -e     'source /directory/$TABLENAME.sql'"  
#Remove file on server B
ssh root@server_b.domain.com "rm /directory/$TABLENAME.sql"
#Remove file on A
rm /directory/$TABLENAME.sql

this is the error i get:

.sql                                                                                                                                                                                                                                  
./script_file_name: line 19: unexpected EOF while looking for matching `"'
./script_file_name: line 22: syntax error: unexpected end of file

thank you

Upvotes: 1

Views: 126

Answers (4)

Paul-Beyond
Paul-Beyond

Reputation: 1737

Just to complete my input with the following solution that worked fine for me.

Except some changed directory names and , this is the same principle and problem solved.

Hope this is useful.

#!/bin/sh

        # Copy table $TABLENAME (passed as argument)
        # from server A to server B.

# Run on Server A
TABLENAME=$1
echo $TABLENAME

# Dump table into /root/bash on server A.
mysqldump -h localhost -u root -p'***' tablename $TABLENAME > /root/bash/$TABLENAME.sql

# Copy table to server B.
scp /root/bash/$TABLENAME.sql root@<ip address>:/root/bash/$TABLENAME.sql2

# Replace table in database on server B
ssh root@<ip address> "mysql -f -u root -p'***' tablename -e 'source /root/bash/$TABLENAME.sql2'" 

# Remove file on server B.
ssh root@<ip address> "rm /root/bash/$TABLENAME.sql2"

# Remove file on A
rm /root/bash/$TABLENAME.sql

Upvotes: 1

papacico
papacico

Reputation: 169

sorry to have bothered you, this is the solution of my problem: 1. i had 2 $ in my mysql password which needed to be escaped like 'fds\$fds\$gfds\$' i did not know this before. 2. The script will not ask for a table name but the table name must be typed as a parameter after the run command like this: ./filename table_name

Upvotes: 1

Paul-Beyond
Paul-Beyond

Reputation: 1737

Try this as your combined ssh and mysql statement:

ssh root@server_b.domain.com "mysql -h localhost -u admin -p'******' database_name -e 'source /directory/$TABLENAME'"
  • Added -h and the database host.
  • Removed the -f switch.

Let me know how it went.

Upvotes: 1

Swarnim Jaiswal
Swarnim Jaiswal

Reputation: 21

You are missing quotes (",') as part of the command.

ssh root@server_b.domain.com "mysql -f -u admin -p'******' database_name -e     'source /directory/$TABLENAME'"

Upvotes: 1

Related Questions