Backing up MySQL database
Backup and restoration of MySQL databases play a very important role in a production environment, so here is a simple method to backup a MySQL database.
To backup MySQL database(s), the following command can be used:
# mysqldump --user=username --password=password --opt DatabaseName > database.sql
The following command can be used to ensure that database(s) and tables are created and listed:
# mysql -u root -p Enter Password: mysql> SHOW DATABASES; mysql> SHOW TABLES;
The following example will list the MySQL databases with SHOW DATABASES, then backup the mysql database.
# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \\g. Your MySQL connection id is 3 to server version: 3.23.58 Type 'help;' or '\\h' for help. Type '\\c' to clear the buffer. mysql> SHOW DATABASES; +----------+ | Database | +----------+ | mysql | | test | +----------+ 2 rows in set (0.06 sec) mysql> exit;
# mysqldump --user=root --password=redhat --opt mysql > /backup/12042017_mysql.sql
Restoring the MySQL database
To restore a saved MySQL database, the following sequence can be used.
The following command can be used to ensure the database(s) are created and listed:
# mysql -u root -p Enter Password : mysql> SHOW DATABASES; mysql> exit;
If the database is created, restore the database using:
# mysql --user=username --password=password DatabaseName < backup_filename.sql
The following example will list the MySQL databases with SHOW DATABASES, then restore the test database from a directory called backup (created under / ) and a filename 12042017_mysql.sql.
# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \\g. Your MySQL connection id is 3 to server version: 3.23.58 Type 'help;' or '\\h' for help. Type '\\c' to clear the buffer. mysql> SHOW DATABASES; +----------+ | Database | +----------+ | mysql | | test | +----------+ 2 rows in set (0.06 sec) mysql> exit;
# mysqldump --user=root --password=redhat test < /backup/12042017_mysql.sql -- MySQL dump 8.23 -- -- Host: localhost Database: test --------------------------------------------------------- -- Server version 3.23.58
If you need to create the database and tables first, the following commands can help you. Note - The database structure at your environment can be very different. So the example given below is just a basic example and can not be used for every restoration out there.
# mysql -uroot -p Enter Password : mysql> CREATE DATABASE DatabaseName; mysql> USE DatabaseName; mysql> CREATE TABLE TableName (ColumnName1 Attributes1, ColumnName2 Attributes2);