How to script a database migration using SSH?
up vote
2
down vote
favorite
I am trying to create a sync.sh script that coordinates with our less-than-ideal version control for our website. This script will migrate a copy of the live database to our local development environment and a couple other things. I'm having trouble with the mysql migration portion.
This script is run on a development machine. remote is the live host.
# --------------
# database
# ssh tunnel
ssh -L 3307:remote:3306 user@remote
# mysql dump
mysqldump -u someuser -h remote -P 3307 -p"p4ssw0rd" db > localfile.sql
# somehow close ssh tunnel ???
# populate local db with sql dump file
mysql -ulocal db < localfile.sql
# -----------------
# other sync stuff
# ...
When I just run the mysql portion of the script, I'm getting this output:
Pseudo-terminal will not be allocated because stdin is not a terminal.
Enter password:
We have ssh keys setup and I can ssh to the remote properly. However, when run in the script context, things are going wrong.
ssh shell-script mysql
edited Dec 7 at 23:32
Rui F Ribeiro
38.7k1479128
asked Mar 14 '12 at 17:47
user633183
12317
add a comment |
up vote
2
down vote
favorite
I am trying to create a sync.sh script that coordinates with our less-than-ideal version control for our website. This script will migrate a copy of the live database to our local development environment and a couple other things. I'm having trouble with the mysql migration portion.
This script is run on a development machine. remote is the live host.
# --------------
# database
# ssh tunnel
ssh -L 3307:remote:3306 user@remote
# mysql dump
mysqldump -u someuser -h remote -P 3307 -p"p4ssw0rd" db > localfile.sql
# somehow close ssh tunnel ???
# populate local db with sql dump file
mysql -ulocal db < localfile.sql
# -----------------
# other sync stuff
# ...
When I just run the mysql portion of the script, I'm getting this output:
Pseudo-terminal will not be allocated because stdin is not a terminal.
Enter password:
We have ssh keys setup and I can ssh to the remote properly. However, when run in the script context, things are going wrong.
ssh shell-script mysql
edited Dec 7 at 23:32
Rui F Ribeiro
38.7k1479128
asked Mar 14 '12 at 17:47
user633183
12317
1
Note: using the same data in development environment is not the best idea especially when there are some sensitive data in your database like users passwords or users private data. There was few examples when that kind of data leaked from development servers as there was no security at all at devs machines...
– pbm
Mar 14 '12 at 18:07
add a comment |
up vote
2
down vote
favorite
up vote
2
down vote
favorite
I am trying to create a sync.sh script that coordinates with our less-than-ideal version control for our website. This script will migrate a copy of the live database to our local development environment and a couple other things. I'm having trouble with the mysql migration portion.
This script is run on a development machine. remote is the live host.
# --------------
# database
# ssh tunnel
ssh -L 3307:remote:3306 user@remote
# mysql dump
mysqldump -u someuser -h remote -P 3307 -p"p4ssw0rd" db > localfile.sql
# somehow close ssh tunnel ???
# populate local db with sql dump file
mysql -ulocal db < localfile.sql
# -----------------
# other sync stuff
# ...
When I just run the mysql portion of the script, I'm getting this output:
Pseudo-terminal will not be allocated because stdin is not a terminal.
Enter password:
We have ssh keys setup and I can ssh to the remote properly. However, when run in the script context, things are going wrong.
ssh shell-script mysql
edited Dec 7 at 23:32
Rui F Ribeiro
38.7k1479128
asked Mar 14 '12 at 17:47
user633183
12317
I am trying to create a sync.sh script that coordinates with our less-than-ideal version control for our website. This script will migrate a copy of the live database to our local development environment and a couple other things. I'm having trouble with the mysql migration portion.
This script is run on a development machine. remote is the live host.
# --------------
# database
# ssh tunnel
ssh -L 3307:remote:3306 user@remote
# mysql dump
mysqldump -u someuser -h remote -P 3307 -p"p4ssw0rd" db > localfile.sql
# somehow close ssh tunnel ???
# populate local db with sql dump file
mysql -ulocal db < localfile.sql
# -----------------
# other sync stuff
# ...
When I just run the mysql portion of the script, I'm getting this output:
Pseudo-terminal will not be allocated because stdin is not a terminal.
Enter password:
We have ssh keys setup and I can ssh to the remote properly. However, when run in the script context, things are going wrong.
ssh shell-script mysql
ssh shell-script mysql
edited Dec 7 at 23:32
Rui F Ribeiro
38.7k1479128
asked Mar 14 '12 at 17:47
user633183
12317
edited Dec 7 at 23:32
Rui F Ribeiro
38.7k1479128
asked Mar 14 '12 at 17:47
user633183
12317
edited Dec 7 at 23:32
Rui F Ribeiro
38.7k1479128
edited Dec 7 at 23:32
Rui F Ribeiro
38.7k1479128
edited Dec 7 at 23:32
Rui F Ribeiro
38.7k1479128
38.7k1479128
asked Mar 14 '12 at 17:47
user633183
12317
asked Mar 14 '12 at 17:47
user633183
12317
asked Mar 14 '12 at 17:47
user633183
12317
12317
1
Note: using the same data in development environment is not the best idea especially when there are some sensitive data in your database like users passwords or users private data. There was few examples when that kind of data leaked from development servers as there was no security at all at devs machines...
– pbm
Mar 14 '12 at 18:07
add a comment |
1
Note: using the same data in development environment is not the best idea especially when there are some sensitive data in your database like users passwords or users private data. There was few examples when that kind of data leaked from development servers as there was no security at all at devs machines...
– pbm
Mar 14 '12 at 18:07
1
1
Note: using the same data in development environment is not the best idea especially when there are some sensitive data in your database like users passwords or users private data. There was few examples when that kind of data leaked from development servers as there was no security at all at devs machines...
– pbm
Mar 14 '12 at 18:07
Note: using the same data in development environment is not the best idea especially when there are some sensitive data in your database like users passwords or users private data. There was few examples when that kind of data leaked from development servers as there was no security at all at devs machines...
– pbm
Mar 14 '12 at 18:07
add a comment |
1 Answer
1
active
oldest
votes
up vote
4
down vote
accepted
You could do it in one line without the tunnel using something like:
$ ssh user@remote "mysqldump -u user --password=password database_name" |
mysql -u local database_name
answered Mar 14 '12 at 17:54
kwarrick
2,404199
Holy toledo! this is awesome, thank you :)
– user633183
Mar 14 '12 at 18:00
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "106"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f34168%2fhow-to-script-a-database-migration-using-ssh%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
4
down vote
accepted
You could do it in one line without the tunnel using something like:
$ ssh user@remote "mysqldump -u user --password=password database_name" |
mysql -u local database_name
answered Mar 14 '12 at 17:54
kwarrick
2,404199
Holy toledo! this is awesome, thank you :)
– user633183
Mar 14 '12 at 18:00
add a comment |
up vote
4
down vote
accepted
You could do it in one line without the tunnel using something like:
$ ssh user@remote "mysqldump -u user --password=password database_name" |
mysql -u local database_name
answered Mar 14 '12 at 17:54
kwarrick
2,404199
Holy toledo! this is awesome, thank you :)
– user633183
Mar 14 '12 at 18:00
add a comment |
up vote
4
down vote
accepted
up vote
4
down vote
accepted
You could do it in one line without the tunnel using something like:
$ ssh user@remote "mysqldump -u user --password=password database_name" |
mysql -u local database_name
answered Mar 14 '12 at 17:54
kwarrick
2,404199
You could do it in one line without the tunnel using something like:
$ ssh user@remote "mysqldump -u user --password=password database_name" |
mysql -u local database_name
answered Mar 14 '12 at 17:54
kwarrick
2,404199
answered Mar 14 '12 at 17:54
kwarrick
2,404199
answered Mar 14 '12 at 17:54
kwarrick
2,404199
answered Mar 14 '12 at 17:54
kwarrick
2,404199
2,404199
Holy toledo! this is awesome, thank you :)
– user633183
Mar 14 '12 at 18:00
add a comment |
Holy toledo! this is awesome, thank you :)
– user633183
Mar 14 '12 at 18:00
Holy toledo! this is awesome, thank you :)
– user633183
Mar 14 '12 at 18:00
Holy toledo! this is awesome, thank you :)
– user633183
Mar 14 '12 at 18:00
add a comment |
Thanks for contributing an answer to Unix & Linux Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f34168%2fhow-to-script-a-database-migration-using-ssh%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Note: using the same data in development environment is not the best idea especially when there are some sensitive data in your database like users passwords or users private data. There was few examples when that kind of data leaked from development servers as there was no security at all at devs machines...
– pbm
Mar 14 '12 at 18:07