title: How do I migrate new MySQL database structure from dev to production website using the command line?
body: <p>I have two website environments (separate servers, Media Temple DVs): Dev and Production. </p>
  
  <p>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: </p>
  
  <pre><code>$ mysqldump -a -u USERNAME -p DATABASE &gt; OUTPUT.mysql
  $ gzip OUTPUT.mysql
  </code></pre>
  
  <p>Then I created the Dev server website and database, moved OUTPUT.mysql over and set up the MySQL environment on Dev for importing: </p>
  
  <pre><code>$ mysql -u USERNAME -p DATABASE
  
  set up environment for large data import: 
  mysql&gt; set global net_buffer_length=1000000; 
  mysql&gt; set global max_allowed_packet=1000000000;
  mysql&gt; exit
  </code></pre>
  
  <p>And imported both data and schema to Dev with these commands:</p>
  
  <pre><code>$ gunzip -f OUTPUT.mysql.gz
  $ mysql -u USERNAME -p TARGET_DATABASE_NAME &lt; OUTPUT.sql
  </code></pre>
  
  <p>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. </p>
  
  <p>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.) </p>
  
  <p>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? </p>
  
  <p><strong>UPDATE</strong>: 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. </p>
  
  <p>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. </p>
  
  <p>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. </p>
  
  <p>Finally, the PHP script outputs everything as an SQL script for creating and altering the tables, which I ran in phpMyAdmin.  </p>