MySQL – Backup, Restore Copy Tables and Databases

Backing up tables and databases is very useful when you are learning to update records so that you can recover quickly when you do something wrong.

  • create table if not exists newTable like oldTable; – Copies schema of old table to new table (columns, data types, etc). IF NOT EXISTS makes sure you do not overwrite a table you already created.
  • insert newTable select * from oldTable; – copies all records from old table into new table
  • LINUX SHELL> mysqldump  -u username -p database > /location/dump.sql – backsup database to location specified with name specified. Make sure you have WRITE permission to the folder you point to
  • LINUX SHELL> mysqladmin -u username -p create newDatabase – creates database from linux shell
  • LINUX SHELL> mysql -u username -p newDatabase < /location/dump.sql – restores database from file to new database

Be the first to comment

Leave a Reply