How to transfer a new MySQL database structure from a developer site to a production site using the command line?

Question

I have two website environments (separate servers, Media Temple DV): Dev and Production.

We started creating a site on Production, and then we got a Dev server, so I originally converted the Production database to Dev using the following commands:

$ mysqldump -a -u USERNAME -p DATABASE > OUTPUT.mysql
$ gzip OUTPUT.mysql

Then I created the Dev server site and database, moved OUTPUT.mysql and set up the MySQL environment on Dev to import:

$ mysql -u USERNAME -p DATABASE

set up environment for large data import: 
mysql> set global net_buffer_length=1000000; 
mysql> set global max_allowed_packet=1000000000;
mysql> exit

And imports both data and schema into Dev using these commands:

$ gunzip -f OUTPUT.mysql.gz
$ mysql -u USERNAME -p TARGET_DATABASE_NAME < OUTPUT.sql

Now I have made changes to the structure of the MySQL Dev database, such as adding / removing fields in existing tables and adding tables, and I would like to transfer my changes to Production.

Dev Production , , , ? ( Production, Dev.)

, phpMyAdmin ( , ). , , , script. , - ?

UPDATE. , ​​ , PHP script, , ( ) .

script , live local, "SHOW TABLES" MySQL . , , SHOW CREATE TABLE MySQL .

, script , , SHOW COLUMNS FROM $table . MySQL , ALTER TABLE .

, PHP script SQL script , phpMyAdmin.

Answer 1

SQL .

, mysqldump, , diff, , . create table alter table , , .

, - script, alter table, , , .

, ,

insert into mytable (col1, col2) select (col1, col2) from oldmytable;

"col1, col2" .. .

, , script .

( , db).

Answer 2

:

• , ALTER , , dev

• mysqldump DROP/CREATE TABLE ( --help ). dev, , . , - .

. ORM, Doctrine, ActiveRecord, -, "", , .

Answer 3

, , .

• , , - workbench mysql. EER, . mysql-workbench, . , -.

• - ALTER table , , // .

I think you should start some database migration tools next time, for example rake . If you do not want to use the tool, you should create some SQL scripts with all your changes so that you can easily update the database schema. In addition, the mysql workbench is very nice to perform such tasks, but still a bit buggy.