How to migrate a large MySQL database?

We need to transfer a large database, about 94 meters in .sql format. The matter is complicated by the fact that the old hosting works on a prehistoric version of phpMyAdmin and does not know how to properly pack everything into an archive. (copied in archive 86k from 156k lines)

On the hosting where I put the database, the maximum file size is 64mb. How do I add the remaining lines?


Answer 1, authority 100%

If the database supports connection from non-localhost, I suggest using dbforge studio for mysql. I also had a headache with databases of several GB, I had to do this.


Answer 2, authority 100%

If the hosting is not maintaining a connection from the local host, then you can use the standard console utility mysqldump:

mysqldump --host=blablabla.com --port=3306 --user=user --password=password --compress databasename > databasename.sql

You access the database remotely, and you get the backup immediately in databasename.sqlon your computer.

UPD:To unpack a database from a backup:

mysql --host=blablabla.com --port=3306 --user=user --password=password --compress databasename < databasename.sql

Again, the dump is on your computer, and the database is deployed on the host.


Answer 3, authority 100%

<?
header('Content-Type: text/html; charset=utf-8');
$fn = 'new.sql';
$DBHOST = 'localhost';
$DBUSER = 'root';
$DBPASS = 'pass';
$DBNAME = 'db';
mysql_connect($DBHOST, $DBUSER, $DBPASS) or die('#1');
mysql_select_db($DBNAME) or die('#2');
mysql_query('SET NAMES UTF8;') or die('#3');
if (!$f = fopen($fn, 'rb')) die('#4');
$q = $qp = '';
$qOk = false;
$n = 0;
while (!feof($f)) {
  $n++;
  $qp = trim(fgets($f));
  if ($qp{0}=='#') continue;
  $q .= $qp;
  if ($q{strlen($q)-1}==';') $qOk = true;
  if ($qOk) {
    echo '| ';
    @flush();
    if (!mysql_query($q)) die('#5<pre> Line '.$n.': '.htmlspecialchars($q)."\n\n".mysql_error().'</pre>');
    $q = '';
    $qOk = false;
    }
  }
fclose($f);
echo '<hr />ok';
?>

My experience. Useful) Note that it is only suitable for uploading phpmyadmindumps to utf8without any processing. Run gently, once. Don’t refresh the page. Ideally, delete the script after pouring.

uploaded a 400-meter database, no glitches were noticed.


Answer 4, authority 50%

It is possible in parts, groups of tables.
Or ask the hoster about it..


Answer 5, authority 50%

Try using sypex dumper.


Answer 6, authority 50%

Get a sypex dumper, preferably a professional version. Although for 94 MB and light is suitable. Just prof. version can load tables separately.