mysql 开发 生产,如何使用命令行将新的MySQL数据库结构从开发人员迁移到生产网站？...

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

We started building the site on Production, then got a Dev server, so I originally moved the Production database to Dev using commands like this:

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

$ gzip OUTPUT.mysql

Then I created the Dev server website and database, moved OUTPUT.mysql over and set up the MySQL environment on Dev for importing:

$ 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 imported both data and schema to Dev with these commands:

$ gunzip -f OUTPUT.mysql.gz

$ mysql -u USERNAME -p TARGET_DATABASE_NAME < OUTPUT.sql

Now I've made changes to the structure of the Dev MySQL database, such as adding/deleting fields in existing tables, and adding tables, and I'd like to migrate my changes to Production.

How do I migrate just the updated structure from the Dev database and import into Production using the command line, similar to what I did originally, but without overwriting the live data? (All of my live data is on Production, so there is no need to bring data over from Dev.)

The database has become too large to do this with phpMyAdmin (besides, the command line seems quicker and easier to work with). It would be nice to stay away from third party tools or open source software for what seems like it should be a simple process that I can throw into a script. Or am I going about it wrong and there is something better I'm missing?

UPDATE: So, based on the below answers and additional reading, I ended up writing a PHP script that examines both the live database and my local database (that includes some changes) and compares the differences between the tables.

The script finds which tables are missing from the live database vs. local using the MySQL "SHOW TABLES" function on both databases and comparing. For these tables that are missing I used SHOW CREATE TABLE to get the MySQL statements for creating the tables.

Similarly, the script finds which columns are missing by using SHOW COLUMNS FROM $table on every table in each database. This MySQL command returns the column names and attributes, so I could then build ALTER TABLE statements automatically for columns that were missing.

Finally, the PHP script outputs everything as an SQL script for creating and altering the tables, which I ran in phpMyAdmin.

解决方案

You need to produce some SQL statements to upgrade the structure of the database.

At the very least, you can use mysqldump to dump both databases with no data, just the structure, and use diff to see what has changed. That way you can include the create table statements for new tables, and write alter table statements for those tables that changed, to upgrade them.

Maybe there's a free script somewhere that can generate those alter table commands, it certainly sounds like a fun little project.

Alternatively, you can rename the old tables, import the development database, again without data, and insert the data from the old tables into the new tables by an

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

where "col1, col2" etc. are the columns from the old table.

Again, the database can be queried for what columns each table has so you can easily write a script to generate these insert statements.

For the second approach the database will have to go offline for a little while (or a long while, depending on the size of the db).