MySQL Backup and Restore Procedure

We can provide a procedure to backup and restore properly since if you want to maintain current info there's no option but to increase the disk's size or transfer the information to another service/device, there is no such thing as a shrinking method in MySQL more than the database recreation.

Backup creation

  1. Open a Windows command prompt

  2. Change to the proper bin folder under the MySQL, e.g. cd C:\Program Files\MySQL\MySQL Server 5.5\bin

  3. Run the mysqldump.exe program using the following arguments: mysqldump.exe –e –u[username] -p[password] -h[hostname] [database name] > [filename].sql

  4. e.g. mysqldump.exe –e –u aiq -paiq aiq > C:\aiqbk.sql

  5. e.g. C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqldump -uroot -p aiq -t > aiqbk.sql
    Enter password: ****

Restoring backup

  1. Once the database has been installed in the desired environment that solves the disk space issue, proceed to recover the AIQ’s data by issuing the procedure as below

  2. Using MySQL Workbench please execute MySQLInitial.sql script file provided with the AIQ package so the user exists before the data is been restored

  3. Open a Windows command prompt in this new environment and change to the proper bin folder as required in step 2 and copy the previously created SQL backup file (step 3) to this folder and issue the command below

  4. mysql –u[user name] -p[password] [database name] < [filename].sql

  5. e.g. mysql –u aiq -paiq aiq < aiq.sql

  6. e.g C:\Program Files\MySQL\MySQL Server 8\bin>mysql -uroot -p aiq < aiqbk.sql
    Enter Password: *****

  7. When the control returns to the windows command prompt the backup is completed