📖 Database Maintenance and Backup

Database maintenance is critical to the health of any application relying on data integrity. Applications need continuous surveillance for data anomalies, load balancing and data breaches.

Database Administrator Responsibilities

Maintenance
Configure and monitor the server, maintain log files.
Design
Design, normalize and create database
Security
Maintain user accounts, secure the server.
Backup
Backup the server regularly, restore or migrate to another server as needed.
Miscellaneous
Start, stop and optimize the server, update software, enable and manage replication.
Replication involves setting up 2 or more servers that are duplicates and synchronizing the data. Done to improve performance on large multi-user databases.

Database Files

Configuration file
my.ini for Windows, my.cnf for Mac and Unix.
Data file
Define tables, indexes and other database objects. Stores data for the database.
Log File
Contain information logged by the server.

Backup and Restore a Database

Backup strategy should include full backups made on a regular schedule using mysqldump and enabling the binary log for incremental backups between full backups.

  • Restoration strategy reverses the process. Recreate the database from the last full backup and apply changes from the incremental backup log. Backup log may be manually updated to remove problematic updates.
  • Backups should be stored on a different hard drive from the one where MySQL is running.
Creating a Backup Using mysqldump

mysqldump is a program included with the MySQL server that can be used to generate database backups. Is is run externally to the MySQL server, and therefore, must be run outside of the MySQL Workbench.

Start the MySQL Server
Start the server from the Workbench or open Windows Services and start mysql80 from there.
Open the Windows Terminal in Administrator mode
Right-click the Windows Terminal application and select Run as Administrator.
Navigate to the MySQL directory in the Terminal
At the Windows Terminal command prompt, type cd 'C:\Program Files\MySQL\MySQL Server 8.0\bin' then press Enter.
MySQL Server is normally installed in the C:\Program Files\MySQL\MySQL Server 8.0\bin directory. Your installation may vary.
You should see something like this: PS C:\Program Files\MySQL\MySQL Server 8.0\bin> It is your new PowerShell command prompt.
Type the command to run mysqldump backup
.\mysqldump --user=<username> --password --result-file=<filename.sql> --databases <database_name>
Where username is the administrative username of your MySQL installation and
password is the administrative password of your MySQL installation and
filename.sql is the name you want to assign to your new backup file and
database_name is the name of the available database you want to backup.
NOTE: You will be prompted to enter your password upon executing this command. Don't add your password in the command.
Retrieve your backup file
Check the bin folder for your backup file.
Restoring a Backup from a mysqldump script
Start the MySQL Server
Start the server from the Workbench or open Windows Services and start mysql80 from there.
Open the Windows Terminal in Administrator mode
Right-click the Windows Terminal application and select Run as Administrator.
Navigate to the MySQL directory in the Terminal
At the Windows Terminal command prompt, type cd 'C:\Program Files\MySQL\MySQL Server 8.0\bin' then press Enter.
Login to MySQL
PS C:\Program Files\MySQL\MySQL Server 8.0\bin> .\mysql -u <username> -p
Where username is the administrative username of your MySQL installation and
password is the administrative password of your MySQL installation.
NOTE: You will be prompted to enter your password upon executing this command. Don't add your password in the command.
Run the backup script
mysql> source filename.sql;
Where filename.sql is the name you want to assign to your new backup file.
NOTE: The source command will execute the backup script. Run SHOW DATABASES; to verify installation of backup.

Importing and Exporting Data Only

Importing is the process of inserting data to a MySQL database. Exporting is the process of copying the data in a MySQL database to a file format for use in another program.

Importing Data into a Database

A DBMS should include some way to import data that originates from other sources. In MySQL you can import a text file. A text file contains unformatted data. A comma-delimited file uses a comma to separate data into fields; a tab-delimited file uses a tab character to separate data into fields. To import a text file into MySQL, the text file must be tab-delimited and the data should not be enclosed in quotation marks.

The file will be stored in the data folder related to the database you are using. For INNODB tables in XXAMP installation, look for your file in C:\ProgramData\MySQL\MySQL Server 8.0\Data\databaseName where databaseName is the name of the database you are working in. You can check the path set for the file using the SHOW VARIABLES LIKE "secure_file_priv"; statement.

Format

LOAD DATA INFILE 'filename.txt'
Required. Identifies the name of the file to be imported.
INTO TABLE tableName
Required. Identifies the table that the data will be loaded into.
FIELDS TERMINATED BY '\t'
Required. Indicates that each field is separated by a tab.
LINES TERMINATED BY '[\r]\n'
Required. Indicates that each row ends with a hard return character.
; (semi-colon)
Required. Indicates the end of the SQL statement.
Exporting Data from a Table

A DBMS should provide some way to export data so it can be used by other programs. When you export data stored in a MySQL database, the rows in the table become rows in the text file and the columns are separated by tabs. To export a table, use the SELECT INTO OUTFILE command. The command includes the word SELECT followed by the name of the column to export, the words INTO OUTFILE, the name of the text file in single quotes, the word FROM and the table name. You can use an asterisk (*) to select all columns in a table.

Format

SELECT colname1, colname2 ... or *
Required. Identifies the columns to be exported.
INTO OUTFILE 'filename.txt'
Required. Identifies the name of the text file to be created with the data.
FROM tablename
Required. Identifies the table that the data will be loaded from.
; (semi-colon)
Required. Indicates the end of the SQL statement.