Ok so i had a huge amount of trouble trying to upload a sqldump file (large sql file) into the mysql database with phpmyadmin. It kept timing out, even when I increased the timeout and upload file size larger in the php.ini file. So logged in using Putty and used the following SSH commands.

Show how many current open connections there are to mysql
mysqladmin processlist |wc -l

MySQL Dump
mysqldump -u username -ppassword dbname > file.sql

so if my username = alice, password = madhatter database name = wonderlanddb and I wanted to write to a file called sqlbackup.sql and the folder http://www.lenatsu.co.uk/databackup/ I would type:
mysqldump -u alice -pmadhatter wonderlanddb > /var/www/vhosts/lenatsu.co.uk/httpdocs/databackup/sqlbackup.sql

If a file ends in .zip (for example, file.zip) type:
unzip file.zip

If a file ends in .tar (e.g., file.tar) type:
tar -zxvf file.tar.gz

If a file ends in .gz (e.g. file.gz) type:
gzip -d file.gz

If a file ends in .tar.gz (e.g. file.tar.gz) type:
gzip -d file.tar.gz
and then
tar -xvf file.tar

Importing MySQL database
mysql -uusername -ppassword database_name < file.sql
So i would type:
mysql -u alice -pmadhatter wonderlanddb < /var/www/vhosts/lenatsu.co.uk/httpdocs/databackup/sqlbackup.sql

Right if it comes up with some rubbish like
/*!40101 mysql

then it ismost likely you have put “mysqldump” at the beginning of the command instead of “mysql”, last time it happened to me it took a couple of hair pulling hours to figure out why.

Rate This Post: