How should I configure mySQL 5.7 on Ubuntu for a physical Hot Backup?












1















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.










share|improve this question














bumped to the homepage by Community 10 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


















1















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.










share|improve this question














bumped to the homepage by Community 10 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
















1












1








1








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.










share|improve this question














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






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Jun 20 '16 at 13:55









Gandolf989Gandolf989

1,228512




1,228512





bumped to the homepage by Community 10 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 10 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





















  • 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












1 Answer
1






active

oldest

votes


















0














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!






share|improve this answer























    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
    });


    }
    });














    draft saved

    draft discarded


















    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









    0














    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!






    share|improve this answer




























      0














      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!






      share|improve this answer


























        0












        0








        0







        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!






        share|improve this answer













        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!







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Jun 21 '16 at 11:35









        Károly NagyKároly Nagy

        2,5201611




        2,5201611






























            draft saved

            draft discarded




















































            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.




            draft saved


            draft discarded














            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





















































            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







            Popular posts from this blog

            Liste der Baudenkmale in Friedland (Mecklenburg)

            Single-Malt-Whisky

            Czorneboh