MySQL Backup and Restore Commands for DBA

Commands for managing MySQL Data Migration

          BACKUP COMMANDS
  1. How to backup Mysql Database: These commands are to be run outside oMySQLql instance/shell command prompt
    # mysqldump -u [username] -p[password] [database_name] > [NameOfFileYouWantToDumpSQL_Data].sql
    you could even run the command with sudo if you don't want to put all the credentials, however, as a DBA you should always restrict access to your database with a strong password and should never allow accessing data with sudo privileges at least from another user.
  2. How to back up a single MySQL database, again you can skip -u [username] adn -p[password] instead use sudo or root
     # mysqldump -u [username] -p[password]  -ptecmint [database_name] > [NameOfFileYouWantToDumpSQL_Data].sql
  3. Backup muiltiple Mysql Databases: Pay attention to the (less than or greater than symbol)
     # mysqldump -u [username] -p[password]  -ptecmint --databases databaseName anotherDatabaseName > [NameOfFileYouWantToDumpSQL_Data].sql
  4. Backup all Mysql databases
     # mysqldump -u [username] -p[password]  -ptecmint --all-databases > [NameOfFileYouWantToDumpSQL_Data].sql
  5. Dump or Backup mysql dabase structure only
     # mysqldump -u [username] -p[password]  -ptecmint --no-data [database_name] > [NameOfFileYouWantToDumpSQL_Data].sql
  6. Backup mysql database Data only
     # mysqldump -u [username] -p[password]  -ptecmint  --no-create-db --no-create-info [database_name] > [NameOfFileYouWantToDumpSQL_Data].sql
  7. Backup single table of a Mysql database
     # mysqldump -u [username] -p[password]  -ptecmint [database_name]  [tableName] > [NameOfFileYouWantToDumpSQL_Data].sql
  8. Backup muiltiple tables of mysql datbase
     # mysqldump -u [username] -p[password]  -ptecmint [database_name]  [tableName] [tableName] > [NameOfFileYouWantToDumpSQL_Data].sql
  9. How to backup remote mysql databasw
     # mysqldump -h [remoteServerIP_Address] -u [username] -p[password]  -ptecmint [database_name] > [NameOfFileYouWantToDumpSQL_Data].sql

    RESTORE COMMANDS
  10. Restore Database: You must create an new database with no data and then restore the database
     # mysql -u [username] -p[password]  -ptecmint [database_name]
  11. Restore data to an existing mysql database
     # mysqlimport -u [username] -p[password]  -ptecmint [database_name]
  12. Restore or transfer data from sql file to existing database
    mysql NameOfDatabase < NameOfFileContainingData.sql  (This command can be typed outside of MySql Shell)