Database Exercises
Backup(MySQLDump)

    MySQL database can be backed up with mysqldump application. It works simply, so that it creates the SQL code which will create all the database tables and insert all the data, which was on the tables. This code will be saved to file. And later you can execute the code from the file.

    So, example this command will create the backup-file named backupfile.sql:
    mysqldump -u root myDatabase -p > backupfile.sql

    And you can restore that backup with this command:
    mysql -u root -D myDatabase -p < backupfile.sql

    NOTE: You can not execute above commands in MySQLMonitor, you have to execute them on the Server Console.

  1. Start UniServerController and MySQL
  2. Open MySQL Console and check with the command SHOW DATABASES; that you still have a database named project.
  3. Open the Server Console, and enter the command "mysqldump -u root -p project > project_backup.sql" and enter the root password.
  4. In the ServerConsole give the command DIR, and check that project_backup.sql file has been created.
  5. Open the file project_backup.sql to some editor (Notepad++ etc.) and check the code. There is pobably nothing about the stored procedure "searcProject" which you have created in exercise 6.
  6. Try to find instructions how to "dump mysql stored procedures" and use MySQLDump to create a file named "project_with_procedures.sql"
  7. In MySQL Console give command DROP DATABASE project; and check with the command SHOW DATABASES; that the project database has been deleted.
  8. In MySQL Console give command CREATE DATABASE project;
  9. In MySQL Console give command SHOW TABLES FROM project; The database should be empty.
  10. In ServerConsole give command "mysql -u root -p -D project < project_backup.sql" and enter the password.
  11. In MySQL Console give command SHOW TABLES FROM project; Now you should again have tables person, project and hour
  12. In MySQL Console check also the data with command SELECT * FROM project.person;
  13. Check that there is no procedures in your database.
  14. In ServerConsole give command "mysql -u root -p -D project < project_with_procedures.sql" and enter the password.
  15. Check again if there is some procedures in your database.
Dump with Workbench

You can make the previous things also in MySQLWorkbench. If you are going to use the dumpfile between some MySQL in Linux and UniServer, it is better not to include Stored Procedures in the dumpfile. The problem is that the character encoding is different in Linux and Windows. So, it is better that you don't include the procedures in dumpfile and you will create them again in Linux MySQL. Another option is that you will install HeidiSQL to your Windows and make the dumpfile with Heidi. And then you will connect to Linux MySQL with MySQLWorkbench and import the database from the dumpfile.

Export Data (Create the Dumpfile)
  1. Click Server->Data Export
  2. Check database project
  3. Check Dump Stored Procedures and Functions
  4. Choose Export to Self-Contained File
  5. Rename the file to projectDump.sql
Click Start Export
Import Data (Execute the Dumpfile)

Import the data to local-database

  1. Create new database named new_project
  2. Click Server->Data Export
  3. Choose Import from Self-Contained File
  4. Choose the file projectDump.sql
  5. Choose target Schema = new_project
Click Start Import



Toggle Menu

Copyright © Pekka Alaluukas / 2024
email