Best way to clone DB

Hi,

I'm developing a SAAS, When a client sucribes for the service, a clone of the app DB is copied.

I'm asking about an efficient way to copy DB for him, should I rum MYSQL Dump for the file contating the SQL quries, or use clone DB, or wt may be better?

I'm using laravel 5.
Ramy MohsenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
AFAIK, this is easiest way to do this:

mysqldump SOURCE_DB -u username -p pass | mysql NEW_DB -u username -p password

With this, you do not even need to create SQL files. Assumption is same server though. You can find other examples here: http://dev.mysql.com/doc/refman/5.5/en/copying-databases.html  The --no-data option on the SOURCE_DB allows you to copy structure only. Other options are here: http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html
Dave BaldwinFixer of ProblemsCommented:
If you are running phpMyAdmin on that same server, under Operations for each database is a function to copy the current database to a new database.
Ramy MohsenAuthor Commented:
Dave Baldwin, I want the process to run automatically from PHP code.
MlandaT, About mysqldump, how to write it from PHP code?
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Julian HansenCommented:
Just to throw a spanner here - taking a step back and looking at your design. What is the long term expectation - what if you have a 100 or 1000 clients - are you going to be running that many databases?

Is it not possible to design the application to run off a single database that can support multiple clients such that they are ringfenced from each other?
Ramy MohsenAuthor Commented:
Thank Julian Hansen for your comment.

But I think in case of 1000 or 100000 client, If I have one DB that serves all of them and saves all of their data, every table will contain massive data, which may affect performance, So I think to save data of each client in his database will be more efficient and quires will be so fast.
MlandaTCommented:
You can use the following options:

system: system("mysqldump -h localhost -u ....");
exec: exec("mysqldump -u mysqluser -p my_database | mysq.....");
passthru (requires SAFE_MODE off... so last option)
Julian HansenCommented:
will be more efficient and quires will be so fast.
That's why databases have indexes - but I leave it to your better judgement ...
Ramy MohsenAuthor Commented:
Thank u MlandaT, But I want to know ur opinion about the security of using such a command:
exec: exec("mysqldump -u mysqluser -p my_database | mysq.....");

Is there any security threat doing it that way?

Thanks
Julian HansenCommented:
Is there any security threat doing it that way?
As long as you are not passing through parameters that could have originated from user input you are fine.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Julian HansenCommented:
Thanks for the points but in my view the selected answer is not the correct choice. Mlanda gave you the syntax for cloning the DB - if you are going to use that you should have accepted his answer and possibly selected mine as an assisted.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.