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

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 ?

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.

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)

Is it possible to have two mysql instance (diffrent port…) in the same server
and use replication between them ?

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.

^^^ Completely agree with Scott.nemes