Make MySQL database copy for development

Question

I have web application, which perfectly working at production server. But this application progressing every day and i frequently need to invite side developers and freelancers for upgrading it. So, every time i find someone, i have to send development version of my apllication and copy of production MySQL database, where i dropped all confidential data manually.

So, the question is: is it possible to make script for auto-making this develop version of database?

UPDATE: I'll try to draw some data. For example, i have Users table:

User table:
name  | email         | role         | phone   |
-----------------------------------------------
user2 | [email protected] | guest        | 1234567 |
user2 | [email protected] | admin        | 1234567 |
user2 | [email protected] | manager      | 1234567 |
user2 | [email protected] | topmanager   | 1234567 |

But, of course, this table has only real users data. And so on a little bit of simmilar tables. So, i want to make script, that dumps whole production database, drops some important and confidential tables, like Users, Orders, Payments and so on. After this script puts dummy data to those tables, like Users data, which i typed before.

Gérald Croës · Accepted Answer · 2011-05-26 07:05:11Z

4

You can use mysql in command line.

ssh -C user@newhost "mysql -uUSER -pPASS -e 'create database NEW_DB_NAME;'" 
&& mysqldump --force --log-error=mysql_error.log -uUSER -pPASS OLD_DB_NAME 
| ssh -C user@remotehost "mysql -uUSER -pPASS NEW_DB_NAME"

answered May 26, 2011 at 7:05

Gérald Croës

3,8892 gold badges20 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

niggles · Accepted Answer · 2011-05-26 07:24:43Z

1

Do you mean you want to give them a copy of the database with some sections anonymised?

Why not write something like:

UPDATE `mytable` SET `foo` = `bar`

on to anonymise those columns? You could write a PHP script which exports the database and then does this to any relevant tables and columns.

Or am I misinterpreting the question?

answered May 26, 2011 at 7:24

niggles

1,0321 gold badge9 silver badges21 bronze badges

Comments

Raj · Accepted Answer · 2011-05-26 07:09:08Z

0

If you want to share only the DDLs of your tables (CREATE TABLE statements)

Find the list of tables that you want to share.
Write a shell script to login to mysql and get the DDLs for each table one by one.

DESC TABLE_NAME_1;

DESC TABLE_NAME_2;
Redirect the output to a file.
Share this file.

answered May 26, 2011 at 7:09

Raj

23.1k14 gold badges105 silver badges148 bronze badges

1 Comment

Ocaso Protal Over a year ago

Why so cumbersome? There is the --no-data switch for mysqldump which does exactly that.

Collectives™ on Stack Overflow

Make MySQL database copy for development

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related