Proper way to fully backup and restore a database including indexes in a low-resource shared web host with...
I'm rather new to database management and I would like some advice on better practices by using the following experience as an example.
Every time I update php software I do a quick export from phpMyAdmin (ended up with a ~250mb gzipped single sql file this time), then I store it somewhere and hope I don't ever need it.
But one day I needed to restore the backup, and this is my sad story of issues...
- phpMyAdmin has the import size capped as 50mb so I had to use
mysqlfrom ssh - MySQL server timed out at ~2minutes so I had to split the sql file
- Indexes were not exported so the CMS started issuing
CREATE INDEXon big tables, causing timeouts in the sql server - I executed the queries to make the index manually but it was timing out anyways so I had to drop the data, execute the
CREATE INDEXquery and re-import that table (in chunks, because of the timeouts) - The CMS finally loaded fine, rebuilding other smaller indexes
Now I naively thought I was done, but I checked some other pages...
(╯°□°）╯︵ ┻━┻ ®ƒÂ¼
- All the UTF8 was interpreted as extended ascii
- So I dropped the database and had to set client encoding to
utf8but only in SESSION. The server timed out andmysqlreconnected, losing the encoding settings and ƒÂ¼*ing up the import yet again. - I edited the sql files to set the encoding every time a new
sourcecommand was used and repeated the split import.
Now the question: How could I do this correctly?
mysql phpmyadmin percona
asked 2 mins ago
beppe9000beppe9000
1011
New contributor
beppe9000 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I'm rather new to database management and I would like some advice on better practices by using the following experience as an example.
Every time I update php software I do a quick export from phpMyAdmin (ended up with a ~250mb gzipped single sql file this time), then I store it somewhere and hope I don't ever need it.
But one day I needed to restore the backup, and this is my sad story of issues...
- phpMyAdmin has the import size capped as 50mb so I had to use
mysqlfrom ssh - MySQL server timed out at ~2minutes so I had to split the sql file
- Indexes were not exported so the CMS started issuing
CREATE INDEXon big tables, causing timeouts in the sql server - I executed the queries to make the index manually but it was timing out anyways so I had to drop the data, execute the
CREATE INDEXquery and re-import that table (in chunks, because of the timeouts) - The CMS finally loaded fine, rebuilding other smaller indexes
Now I naively thought I was done, but I checked some other pages...
(╯°□°）╯︵ ┻━┻ ®ƒÂ¼
- All the UTF8 was interpreted as extended ascii
- So I dropped the database and had to set client encoding to
utf8but only in SESSION. The server timed out andmysqlreconnected, losing the encoding settings and ƒÂ¼*ing up the import yet again. - I edited the sql files to set the encoding every time a new
sourcecommand was used and repeated the split import.
Now the question: How could I do this correctly?
mysql phpmyadmin percona
asked 2 mins ago
beppe9000beppe9000
1011
New contributor
beppe9000 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I'm rather new to database management and I would like some advice on better practices by using the following experience as an example.
Every time I update php software I do a quick export from phpMyAdmin (ended up with a ~250mb gzipped single sql file this time), then I store it somewhere and hope I don't ever need it.
But one day I needed to restore the backup, and this is my sad story of issues...
- phpMyAdmin has the import size capped as 50mb so I had to use
mysqlfrom ssh - MySQL server timed out at ~2minutes so I had to split the sql file
- Indexes were not exported so the CMS started issuing
CREATE INDEXon big tables, causing timeouts in the sql server - I executed the queries to make the index manually but it was timing out anyways so I had to drop the data, execute the
CREATE INDEXquery and re-import that table (in chunks, because of the timeouts) - The CMS finally loaded fine, rebuilding other smaller indexes
Now I naively thought I was done, but I checked some other pages...
(╯°□°）╯︵ ┻━┻ ®ƒÂ¼
- All the UTF8 was interpreted as extended ascii
- So I dropped the database and had to set client encoding to
utf8but only in SESSION. The server timed out andmysqlreconnected, losing the encoding settings and ƒÂ¼*ing up the import yet again. - I edited the sql files to set the encoding every time a new
sourcecommand was used and repeated the split import.
Now the question: How could I do this correctly?
mysql phpmyadmin percona
asked 2 mins ago
beppe9000beppe9000
1011
New contributor
beppe9000 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I'm rather new to database management and I would like some advice on better practices by using the following experience as an example.
Every time I update php software I do a quick export from phpMyAdmin (ended up with a ~250mb gzipped single sql file this time), then I store it somewhere and hope I don't ever need it.
But one day I needed to restore the backup, and this is my sad story of issues...
- phpMyAdmin has the import size capped as 50mb so I had to use
mysqlfrom ssh - MySQL server timed out at ~2minutes so I had to split the sql file
- Indexes were not exported so the CMS started issuing
CREATE INDEXon big tables, causing timeouts in the sql server - I executed the queries to make the index manually but it was timing out anyways so I had to drop the data, execute the
CREATE INDEXquery and re-import that table (in chunks, because of the timeouts) - The CMS finally loaded fine, rebuilding other smaller indexes
Now I naively thought I was done, but I checked some other pages...
(╯°□°）╯︵ ┻━┻ ®ƒÂ¼
- All the UTF8 was interpreted as extended ascii
- So I dropped the database and had to set client encoding to
utf8but only in SESSION. The server timed out andmysqlreconnected, losing the encoding settings and ƒÂ¼*ing up the import yet again. - I edited the sql files to set the encoding every time a new
sourcecommand was used and repeated the split import.
Now the question: How could I do this correctly?
mysql phpmyadmin percona
mysql phpmyadmin percona
asked 2 mins ago
beppe9000beppe9000
1011
New contributor
beppe9000 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 mins ago
beppe9000beppe9000
1011
New contributor
beppe9000 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 mins ago
beppe9000beppe9000
1011
New contributor
beppe9000 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 mins ago
beppe9000beppe9000
1011
asked 2 mins ago
beppe9000beppe9000
1011
1011
New contributor
beppe9000 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
beppe9000 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
beppe9000 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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
});
}
});
beppe9000 is a new contributor. Be nice, and check out our Code of Conduct.
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%2fdba.stackexchange.com%2fquestions%2f227602%2fproper-way-to-fully-backup-and-restore-a-database-including-indexes-in-a-low-res%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
beppe9000 is a new contributor. Be nice, and check out our Code of Conduct.
beppe9000 is a new contributor. Be nice, and check out our Code of Conduct.
beppe9000 is a new contributor. Be nice, and check out our Code of Conduct.
beppe9000 is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators 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.
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%2fdba.stackexchange.com%2fquestions%2f227602%2fproper-way-to-fully-backup-and-restore-a-database-including-indexes-in-a-low-res%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
