How to: Administration: Manage MySQL database¶
We install PhpMyAdmin to allow you to manage the MySQL database from your local browser.
Please find the access link and access details in the confirmation email.
You can do the following:
- Manage databases (create, delete, edit, and configure) using Root user of MySQL
- Manage database users (create, delete, and edit) using Root user of MySQL
- View and edit database table contents
- Export and import databases
We have created an individual user for each MySQL database so that you could provide access to developers without worrying about other databases. In other words, each project (store) has it own MySQL user, which improves data security.
Open the email with access details and find the Access to the database section.
Open the link to access PhpMyAdmin. The basic-auth window will open, where you need to input the access details from the email. It guarantees security.
The PhpMyAdmin panel will open. If you want to get full access, for example, create a new database or user, use the Superuser Root access. If you want to view the store database, use the Developer access.
That’s it. You are ready to manage the database.
MySQL database backup¶
Backing up MySQL¶
Restoring the database from the backup¶
- Open the database in the PhpMyAdmin panel.
- Open the Import section.
- Restore data from the backup file.
Managing large databases¶
Connect to the server via SSH.
To create a backup and archive it, run the following command:
mysqldump -u USER -pPASSWORD DATABASE --single-transaction | gzip > ./backup_DATABASE.sql.gz
To restore a backup from the .sql file, run the following command:
mysql -u USER -pPASSWORD DATABASE < /path/to/dump.sql
If the backup is compressed (the file has the .gz format), unzip the archive, using the following command:
tar -xvzf /path/to/archive.tar.gz
To restore the backup from the archive with one command, run this command:
gunzip < /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE
or this one:
zcat /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE