mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table
Recently, i was trying to make a slave working by dumping via ssh the databases.
I had discart percona-xtrabackup because i liked to select which databases i'de like to sync.
For make the slave work, i used the next script, which connect to the master, makes the dump of the select database and restore it:
#!/bin/bash
sql_error_check()
{
if [ $? -ne 0 ]; then
echo "ERROR at mysql command!"
exit 1
fi
}
DBS=( db1 db2 db3 db4 db5... )
PASSW="SuperSecurePass"
for i in ${DBS[*]}
do
echo "- Deleting ${i}"
mysql -p${PASSW} -e "drop database IF EXISTS ${i}"
sql_error_check
echo "- Creating ${i}"
mysql -p${PASSW} -e "create database ${i}"
sql_error_check
echo "- Dumping & restoring ${i}"
ssh -C root@master.hostname "mysqldump -p${PASSW} -q --max_allowed_packet=1024M ${i}" | pv | mysql -p${PASSW} ${i}
sql_error_check
done
Every thing seems to work perfect, but in some queries (huge alter tables) i get the error:
mysqldump: Error 2013: Lost connection to MySQL server during query
when dumping table
mysql mariadb
asked Jan 17 at 10:18
WilliWonkaWilliWonka
486
add a comment |
Recently, i was trying to make a slave working by dumping via ssh the databases.
I had discart percona-xtrabackup because i liked to select which databases i'de like to sync.
For make the slave work, i used the next script, which connect to the master, makes the dump of the select database and restore it:
#!/bin/bash
sql_error_check()
{
if [ $? -ne 0 ]; then
echo "ERROR at mysql command!"
exit 1
fi
}
DBS=( db1 db2 db3 db4 db5... )
PASSW="SuperSecurePass"
for i in ${DBS[*]}
do
echo "- Deleting ${i}"
mysql -p${PASSW} -e "drop database IF EXISTS ${i}"
sql_error_check
echo "- Creating ${i}"
mysql -p${PASSW} -e "create database ${i}"
sql_error_check
echo "- Dumping & restoring ${i}"
ssh -C root@master.hostname "mysqldump -p${PASSW} -q --max_allowed_packet=1024M ${i}" | pv | mysql -p${PASSW} ${i}
sql_error_check
done
Every thing seems to work perfect, but in some queries (huge alter tables) i get the error:
mysqldump: Error 2013: Lost connection to MySQL server during query
when dumping table
mysql mariadb
asked Jan 17 at 10:18
WilliWonkaWilliWonka
486
add a comment |
Recently, i was trying to make a slave working by dumping via ssh the databases.
I had discart percona-xtrabackup because i liked to select which databases i'de like to sync.
For make the slave work, i used the next script, which connect to the master, makes the dump of the select database and restore it:
#!/bin/bash
sql_error_check()
{
if [ $? -ne 0 ]; then
echo "ERROR at mysql command!"
exit 1
fi
}
DBS=( db1 db2 db3 db4 db5... )
PASSW="SuperSecurePass"
for i in ${DBS[*]}
do
echo "- Deleting ${i}"
mysql -p${PASSW} -e "drop database IF EXISTS ${i}"
sql_error_check
echo "- Creating ${i}"
mysql -p${PASSW} -e "create database ${i}"
sql_error_check
echo "- Dumping & restoring ${i}"
ssh -C root@master.hostname "mysqldump -p${PASSW} -q --max_allowed_packet=1024M ${i}" | pv | mysql -p${PASSW} ${i}
sql_error_check
done
Every thing seems to work perfect, but in some queries (huge alter tables) i get the error:
mysqldump: Error 2013: Lost connection to MySQL server during query
when dumping table
mysql mariadb
asked Jan 17 at 10:18
WilliWonkaWilliWonka
486
Recently, i was trying to make a slave working by dumping via ssh the databases.
I had discart percona-xtrabackup because i liked to select which databases i'de like to sync.
For make the slave work, i used the next script, which connect to the master, makes the dump of the select database and restore it:
#!/bin/bash
sql_error_check()
{
if [ $? -ne 0 ]; then
echo "ERROR at mysql command!"
exit 1
fi
}
DBS=( db1 db2 db3 db4 db5... )
PASSW="SuperSecurePass"
for i in ${DBS[*]}
do
echo "- Deleting ${i}"
mysql -p${PASSW} -e "drop database IF EXISTS ${i}"
sql_error_check
echo "- Creating ${i}"
mysql -p${PASSW} -e "create database ${i}"
sql_error_check
echo "- Dumping & restoring ${i}"
ssh -C root@master.hostname "mysqldump -p${PASSW} -q --max_allowed_packet=1024M ${i}" | pv | mysql -p${PASSW} ${i}
sql_error_check
done
Every thing seems to work perfect, but in some queries (huge alter tables) i get the error:
mysqldump: Error 2013: Lost connection to MySQL server during query
when dumping table
mysql mariadb
mysql mariadb
asked Jan 17 at 10:18
WilliWonkaWilliWonka
486
asked Jan 17 at 10:18
WilliWonkaWilliWonka
486
asked Jan 17 at 10:18
WilliWonkaWilliWonka
486
asked Jan 17 at 10:18
WilliWonkaWilliWonka
486
asked Jan 17 at 10:18
WilliWonkaWilliWonka
486
486
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
The solution was prety easy. This problem happend becouse when the dump is passing throug an ssh canal, and an opperation like "alter" is running, the data transfer needs to be stoped wating for the querie to finish. The time that the connection will wait to new data is defined in my.cnf in a really low time (30 seconds by default).
For fix this error, the only thing needed is modify this two params on the mysql config:
net_read_timeout = 3600
net_write_timeout = 3600
This will prevent the connection to get a timeout and prompt this error.
Due to the docu, is defined as:
The number of seconds to wait for more data from a connection before
aborting the read. When the server is reading from the client,
net_read_timeout is the timeout value controlling when to abort. When
the server is writing to the client, net_write_timeout is the timeout
value controlling when to abort. See also slave_net_timeout.
answered Jan 17 at 10:18
WilliWonkaWilliWonka
486
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "89"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2faskubuntu.com%2fquestions%2f1110509%2fmysqldump-error-2013-lost-connection-to-mysql-server-during-query-when-dumping%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
The solution was prety easy. This problem happend becouse when the dump is passing throug an ssh canal, and an opperation like "alter" is running, the data transfer needs to be stoped wating for the querie to finish. The time that the connection will wait to new data is defined in my.cnf in a really low time (30 seconds by default).
For fix this error, the only thing needed is modify this two params on the mysql config:
net_read_timeout = 3600
net_write_timeout = 3600
This will prevent the connection to get a timeout and prompt this error.
Due to the docu, is defined as:
The number of seconds to wait for more data from a connection before
aborting the read. When the server is reading from the client,
net_read_timeout is the timeout value controlling when to abort. When
the server is writing to the client, net_write_timeout is the timeout
value controlling when to abort. See also slave_net_timeout.
answered Jan 17 at 10:18
WilliWonkaWilliWonka
486
add a comment |
The solution was prety easy. This problem happend becouse when the dump is passing throug an ssh canal, and an opperation like "alter" is running, the data transfer needs to be stoped wating for the querie to finish. The time that the connection will wait to new data is defined in my.cnf in a really low time (30 seconds by default).
For fix this error, the only thing needed is modify this two params on the mysql config:
net_read_timeout = 3600
net_write_timeout = 3600
This will prevent the connection to get a timeout and prompt this error.
Due to the docu, is defined as:
The number of seconds to wait for more data from a connection before
aborting the read. When the server is reading from the client,
net_read_timeout is the timeout value controlling when to abort. When
the server is writing to the client, net_write_timeout is the timeout
value controlling when to abort. See also slave_net_timeout.
answered Jan 17 at 10:18
WilliWonkaWilliWonka
486
add a comment |
The solution was prety easy. This problem happend becouse when the dump is passing throug an ssh canal, and an opperation like "alter" is running, the data transfer needs to be stoped wating for the querie to finish. The time that the connection will wait to new data is defined in my.cnf in a really low time (30 seconds by default).
For fix this error, the only thing needed is modify this two params on the mysql config:
net_read_timeout = 3600
net_write_timeout = 3600
This will prevent the connection to get a timeout and prompt this error.
Due to the docu, is defined as:
The number of seconds to wait for more data from a connection before
aborting the read. When the server is reading from the client,
net_read_timeout is the timeout value controlling when to abort. When
the server is writing to the client, net_write_timeout is the timeout
value controlling when to abort. See also slave_net_timeout.
answered Jan 17 at 10:18
WilliWonkaWilliWonka
486
The solution was prety easy. This problem happend becouse when the dump is passing throug an ssh canal, and an opperation like "alter" is running, the data transfer needs to be stoped wating for the querie to finish. The time that the connection will wait to new data is defined in my.cnf in a really low time (30 seconds by default).
For fix this error, the only thing needed is modify this two params on the mysql config:
net_read_timeout = 3600
net_write_timeout = 3600
This will prevent the connection to get a timeout and prompt this error.
Due to the docu, is defined as:
The number of seconds to wait for more data from a connection before
aborting the read. When the server is reading from the client,
net_read_timeout is the timeout value controlling when to abort. When
the server is writing to the client, net_write_timeout is the timeout
value controlling when to abort. See also slave_net_timeout.
answered Jan 17 at 10:18
WilliWonkaWilliWonka
486
answered Jan 17 at 10:18
WilliWonkaWilliWonka
486
answered Jan 17 at 10:18
WilliWonkaWilliWonka
486
answered Jan 17 at 10:18
WilliWonkaWilliWonka
486
486
add a comment |
add a comment |
Thanks for contributing an answer to Ask Ubuntu!
- 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%2faskubuntu.com%2fquestions%2f1110509%2fmysqldump-error-2013-lost-connection-to-mysql-server-during-query-when-dumping%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
