MySQL Backup and Restore Commands for DBA

Commands for managing MySQL Data Migration

  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

  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)

Edit this Article
Was this page helpful?
Yes Yes! this solution to the problem was helpful. No No! this solution to the problem was not helpful.. Leave ErnesTech Feedback.Feedback
If you log in, you will be notified when someone leaves a comment.

Other users would like to know if this solution helped you.

Your Session is Ending
Login to Continue

© 2021 - ErnesTech - Privacy