this is the way you can export/backup and import/restore a single/all databases:

backup and restore a single database (one by one):

mysqldump -u root --password=mysql-root-password database_name > dumpfilename.sql

restore:

(you will need to find out the password for the mysql-root user (not the linux/unix root user), because this is the only mysql-user that is allowed to perform restores:

mysql -u root --password=mysql-root-password database_name < dumpfilename.sql

# unzip and restore in one go
# (pipe through gzip)
gunzip < /path/to/backup.sql.gz | mysql -u root --password=mysql-root-password database_name

Dump only certain tables

mysqldump -u root --password=123uio1u database_name table1 table2 table3 > mydb_tables.sql

backup and restore all databases:

if you want to backup ALL databases BE AWARE THAT EXTACTING A SINGLE DATABASE OUT OF THE DUMP-OF-ALL IS TRICKY!

(basically you need a Test-Machine with phpmyadmin, import the DUMP-OF-ALL and EXPORT only the database that you need/want to transfer to remote host)

#backup all databases in one file (eventually add the option --add-locks):
mysqldump -u username -p --all-databases > file.sql

#backup all databases in one gzipped file:
mysqldump -u username -p --all-databases | gzip > file.sql.gz

#restore all databases:
mysql -u username -p < file.sql

liked this article?

  • only together we can create a truly free world
  • plz support dwaves to keep it up & running!
  • (yes the info on the internet is (mostly) free but beer is still not free (still have to work on that))
  • really really hate advertisement
  • contribute: whenever a solution was found, blog about it for others to find!
  • talk about, recommend & link to this blog and articles
  • thanks to all who contribute!
admin

Tags: