How should I configure mySQL 5.7 on Ubuntu for a physical Hot Backup?
I recent upgraded my Ubuntu 14.04 server to Ubuntu 16.04. I managed to get mySQL upgraded to 5.7. I don't currently have anything in the mySQL instance. Since I have been an Oracle DBA for almost 20 years, I want to configure mySQL with some of the same features. I want to turn on logging for all transactions, and setup hot backups, hopefully using open source software. I'm also not sure which database engine to use.
I have been looking through the documentation, and I am just not finding the steps for setting up a mySQL instance to work they way I think it should work. I know I can do a logical backup, but that doesn't allow me to do point in time recovery. I know I can use all of the default settings, but if I am able to market my skills as a mySQL DBA, I want to use as many of the advanced features as possible.
Thanks.
mysql mysql-5.7
asked Jun 20 '16 at 13:55
Gandolf989Gandolf989
1,228512
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I recent upgraded my Ubuntu 14.04 server to Ubuntu 16.04. I managed to get mySQL upgraded to 5.7. I don't currently have anything in the mySQL instance. Since I have been an Oracle DBA for almost 20 years, I want to configure mySQL with some of the same features. I want to turn on logging for all transactions, and setup hot backups, hopefully using open source software. I'm also not sure which database engine to use.
I have been looking through the documentation, and I am just not finding the steps for setting up a mySQL instance to work they way I think it should work. I know I can do a logical backup, but that doesn't allow me to do point in time recovery. I know I can use all of the default settings, but if I am able to market my skills as a mySQL DBA, I want to use as many of the advanced features as possible.
Thanks.
mysql mysql-5.7
asked Jun 20 '16 at 13:55
Gandolf989Gandolf989
1,228512
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Have you set up LVM?
– Rick James
Jul 1 '16 at 18:46
"Same features as Oracle"? There are sooooo many differences.
– Rick James
Jul 1 '16 at 18:47
"I know I can do a logical backup, but that doesn't allow me to do point in time recovery." -- Actually, it does. See the help for mysqldump, and refer to --master-data and --flush-logs options. Be sure to capture binlog coordinates while holding table locks with any hot backup solution that you use. MEB will put them in backup_variables.txt in the meta subdir if you don't use the --no-locking option.
– user118129
Feb 26 '17 at 16:59
add a comment |
I recent upgraded my Ubuntu 14.04 server to Ubuntu 16.04. I managed to get mySQL upgraded to 5.7. I don't currently have anything in the mySQL instance. Since I have been an Oracle DBA for almost 20 years, I want to configure mySQL with some of the same features. I want to turn on logging for all transactions, and setup hot backups, hopefully using open source software. I'm also not sure which database engine to use.
I have been looking through the documentation, and I am just not finding the steps for setting up a mySQL instance to work they way I think it should work. I know I can do a logical backup, but that doesn't allow me to do point in time recovery. I know I can use all of the default settings, but if I am able to market my skills as a mySQL DBA, I want to use as many of the advanced features as possible.
Thanks.
mysql mysql-5.7
asked Jun 20 '16 at 13:55
Gandolf989Gandolf989
1,228512
I recent upgraded my Ubuntu 14.04 server to Ubuntu 16.04. I managed to get mySQL upgraded to 5.7. I don't currently have anything in the mySQL instance. Since I have been an Oracle DBA for almost 20 years, I want to configure mySQL with some of the same features. I want to turn on logging for all transactions, and setup hot backups, hopefully using open source software. I'm also not sure which database engine to use.
I have been looking through the documentation, and I am just not finding the steps for setting up a mySQL instance to work they way I think it should work. I know I can do a logical backup, but that doesn't allow me to do point in time recovery. I know I can use all of the default settings, but if I am able to market my skills as a mySQL DBA, I want to use as many of the advanced features as possible.
Thanks.
mysql mysql-5.7
mysql mysql-5.7
asked Jun 20 '16 at 13:55
Gandolf989Gandolf989
1,228512
asked Jun 20 '16 at 13:55
Gandolf989Gandolf989
1,228512
asked Jun 20 '16 at 13:55
Gandolf989Gandolf989
1,228512
asked Jun 20 '16 at 13:55
Gandolf989Gandolf989
1,228512
asked Jun 20 '16 at 13:55
Gandolf989Gandolf989
1,228512
1,228512
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Have you set up LVM?
– Rick James
Jul 1 '16 at 18:46
"Same features as Oracle"? There are sooooo many differences.
– Rick James
Jul 1 '16 at 18:47
"I know I can do a logical backup, but that doesn't allow me to do point in time recovery." -- Actually, it does. See the help for mysqldump, and refer to --master-data and --flush-logs options. Be sure to capture binlog coordinates while holding table locks with any hot backup solution that you use. MEB will put them in backup_variables.txt in the meta subdir if you don't use the --no-locking option.
– user118129
Feb 26 '17 at 16:59
add a comment |
Have you set up LVM?
– Rick James
Jul 1 '16 at 18:46
"Same features as Oracle"? There are sooooo many differences.
– Rick James
Jul 1 '16 at 18:47
"I know I can do a logical backup, but that doesn't allow me to do point in time recovery." -- Actually, it does. See the help for mysqldump, and refer to --master-data and --flush-logs options. Be sure to capture binlog coordinates while holding table locks with any hot backup solution that you use. MEB will put them in backup_variables.txt in the meta subdir if you don't use the --no-locking option.
– user118129
Feb 26 '17 at 16:59
Have you set up LVM?
– Rick James
Jul 1 '16 at 18:46
Have you set up LVM?
– Rick James
Jul 1 '16 at 18:46
"Same features as Oracle"? There are sooooo many differences.
– Rick James
Jul 1 '16 at 18:47
"Same features as Oracle"? There are sooooo many differences.
– Rick James
Jul 1 '16 at 18:47
"I know I can do a logical backup, but that doesn't allow me to do point in time recovery." -- Actually, it does. See the help for mysqldump, and refer to --master-data and --flush-logs options. Be sure to capture binlog coordinates while holding table locks with any hot backup solution that you use. MEB will put them in backup_variables.txt in the meta subdir if you don't use the --no-locking option.
– user118129
Feb 26 '17 at 16:59
"I know I can do a logical backup, but that doesn't allow me to do point in time recovery." -- Actually, it does. See the help for mysqldump, and refer to --master-data and --flush-logs options. Be sure to capture binlog coordinates while holding table locks with any hot backup solution that you use. MEB will put them in backup_variables.txt in the meta subdir if you don't use the --no-locking option.
– user118129
Feb 26 '17 at 16:59
add a comment |
1 Answer
1
active
oldest
votes
Use Innodb unless you have a specific reason not to. If your coming from Oracle you will find its internal behaviour familiar therefore learning curve is shorter.
For what you described you probably will want to enable binary logs (check which format suits your use case better: row, statement or mixed).
For hot backups I recommend to use percona-xtrabackup. It can stream data directly to a remote server so you don't even have to have double the free space available on the backup host. The documentation is very good and detailed and you can also find many source online.
I hope this helps getting you started. Good luck!
answered Jun 21 '16 at 11:35
Károly NagyKároly Nagy
2,5201611
add a comment |
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
});
}
});
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%2f141720%2fhow-should-i-configure-mysql-5-7-on-ubuntu-for-a-physical-hot-backup%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
Use Innodb unless you have a specific reason not to. If your coming from Oracle you will find its internal behaviour familiar therefore learning curve is shorter.
For what you described you probably will want to enable binary logs (check which format suits your use case better: row, statement or mixed).
For hot backups I recommend to use percona-xtrabackup. It can stream data directly to a remote server so you don't even have to have double the free space available on the backup host. The documentation is very good and detailed and you can also find many source online.
I hope this helps getting you started. Good luck!
answered Jun 21 '16 at 11:35
Károly NagyKároly Nagy
2,5201611
add a comment |
Use Innodb unless you have a specific reason not to. If your coming from Oracle you will find its internal behaviour familiar therefore learning curve is shorter.
For what you described you probably will want to enable binary logs (check which format suits your use case better: row, statement or mixed).
For hot backups I recommend to use percona-xtrabackup. It can stream data directly to a remote server so you don't even have to have double the free space available on the backup host. The documentation is very good and detailed and you can also find many source online.
I hope this helps getting you started. Good luck!
answered Jun 21 '16 at 11:35
Károly NagyKároly Nagy
2,5201611
add a comment |
Use Innodb unless you have a specific reason not to. If your coming from Oracle you will find its internal behaviour familiar therefore learning curve is shorter.
For what you described you probably will want to enable binary logs (check which format suits your use case better: row, statement or mixed).
For hot backups I recommend to use percona-xtrabackup. It can stream data directly to a remote server so you don't even have to have double the free space available on the backup host. The documentation is very good and detailed and you can also find many source online.
I hope this helps getting you started. Good luck!
answered Jun 21 '16 at 11:35
Károly NagyKároly Nagy
2,5201611
Use Innodb unless you have a specific reason not to. If your coming from Oracle you will find its internal behaviour familiar therefore learning curve is shorter.
For what you described you probably will want to enable binary logs (check which format suits your use case better: row, statement or mixed).
For hot backups I recommend to use percona-xtrabackup. It can stream data directly to a remote server so you don't even have to have double the free space available on the backup host. The documentation is very good and detailed and you can also find many source online.
I hope this helps getting you started. Good luck!
answered Jun 21 '16 at 11:35
Károly NagyKároly Nagy
2,5201611
answered Jun 21 '16 at 11:35
Károly NagyKároly Nagy
2,5201611
answered Jun 21 '16 at 11:35
Károly NagyKároly Nagy
2,5201611
answered Jun 21 '16 at 11:35
Károly NagyKároly Nagy
2,5201611
2,5201611
add a comment |
add a comment |
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%2f141720%2fhow-should-i-configure-mysql-5-7-on-ubuntu-for-a-physical-hot-backup%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
Have you set up LVM?
– Rick James
Jul 1 '16 at 18:46
"Same features as Oracle"? There are sooooo many differences.
– Rick James
Jul 1 '16 at 18:47
"I know I can do a logical backup, but that doesn't allow me to do point in time recovery." -- Actually, it does. See the help for mysqldump, and refer to --master-data and --flush-logs options. Be sure to capture binlog coordinates while holding table locks with any hot backup solution that you use. MEB will put them in backup_variables.txt in the meta subdir if you don't use the --no-locking option.
– user118129
Feb 26 '17 at 16:59