Not the answer you need?
Register and ask your own question!

How to sync on demand between two database in the same server mysql

saidassoumanisaidassoumani ContributorInactive User Role Beginner
I have one database for production and another for dev. All test will be on test. until here , there is no problem.
But i want to sync the dev database with prod database on demand (for example every night , or if there are issues...)
Is it possible ?

Comments

  • niljoshiniljoshi MySQL Sage Inactive User Role Beginner
    Hi,

    Can you tell us which storage engine you are using for prod / dev databases? also what is the size of database?
    If you are using MyISAM and then you can simply sync the database dirs but if its InnoDB then backup/restore can be a better option.
    Once you can sync with full backup and then every day you can take incremental backup and restore it in another database with Percona Xtrabackup.
  • saidassoumanisaidassoumani Contributor Inactive User Role Beginner
    Hi @niljoshi ,
    First , Thank for you reply.
    I use Innodb for the two databases.
    There is no tool or script that allow me to sync my dev database with my prod database on demand without backup?
    (to have the same state as the production)
  • saidassoumanisaidassoumani Contributor Inactive User Role Beginner
    Is it possible to have two mysql instance (diffrent port...) in the same server
    and use replication between them ?
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Yes saidassoumani that is possible. Only issue there is if you are replicating from prod to dev, and you modify data / schema in dev (the slave), it will break replication.

    Your best bet is to backup your prod instance nightly (or however often you want) and restore it into the dev instance.
  • niljoshiniljoshi MySQL Sage Inactive User Role Beginner
    ^^^ Completely agree with Scott.nemes
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.