Moving Data Between Live And Test Server

Allikas: Juhised

It is obligatory to only use latin characters in names. Otherwise, queries will not work.

If a bigger change must be done in the database structure then it is obligatory to follow these steps:

  1. go into PhpMyAdmin on the remote server and download the database on two ways: only the structure and only the data. Be cafeful that the checkbox for extended inserts is not checked! The naming convention is like tennis24_structure_20100802043445.sql whereas the first part separated by underscores tells us the server name, the second part the year, month, day, hour, minute and second of the download and the third part tells the type of the script;
  2. go into PhpMyAdmin on the local server and download the database as in the previous point;
  3. edit the database model in DB Designer and save the result as ikka_create_20100802043445.sql (the timestamp varies);
  4. delete all the tables from the local database;
  5. import the ikka_create...-schema into the local database;
  6. import the data from tennis24_data... into the local database by using
SET NAMES utf8;
  1. download all the image files from assets/images/newspics, assets/images/inquiry_logos, assets/images/school_cards, assets/images/uploads and assets/images/gallery into the same local folders! Please do the same with the folder originals outside the web directory;
  2. for gallery images
 sudo chown www-data:www-data * -R

After changing the local database structure and content, all the tables in the remote database must be removed and the local database must be imported into the remote database with the following command:

mysqldump -u root -p tennis24 > ikka_20100809031733.sql

Be aware that at least the fields containing Estonian texts must have the collation utf8_estonian_ci. This can be done with the following script:

$dbname = 'my_databaseName';
mysql_connect('localhost', 'root', 'Kehtima6');
mysql_query("ALTER DATABASE `$dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_estonian_ci");
$res = mysql_query("SHOW TABLES FROM `$dbname`");
while($row = mysql_fetch_row($res)) {
   $query = "ALTER TABLE {$dbname}.`{$row[0]}` CONVERT TO CHARACTER SET utf8 COLLATE utf8_estonian_ci";
   mysql_query($query);
   $query = "ALTER TABLE {$dbname}.`{$row[0]}` DEFAULT CHARACTER SET utf8 COLLATE utf8_estonian_ci";
   mysql_query($query);
}
echo 'all tables converted';