Article posted Sun Mar 12 2023 Updated Mon Mar 13 2023

Database: Maintaining a MySQL Database Without a GUI

MySQL is one of the more popular relational database management systems, used by developers and businesses to store and manage data. While many users may be familiar with using graphical user interfaces (GUIs) to manage their MySQL databases, the command-line interface (CLI) can be a powerful alternative for managing your MySQL databases, and provide a quick and easy approach, providing it is handled with care.

Connecting to MySQL

Log into the relevant server, and run the following commands to log you into a specified MySQL account.

mysql -u [username] -p

Enter your MySQL password when prompted. Once logged in, retrieve a list of databases;

SHOW DATABASES;

Switch to the database you want to check by typing the following command:

USE [database_name];

Latest Entries

To display the latest 10 entries in a table ordered by newest first, we can use the SELECT statement along with the ORDER BY and LIMIT clauses.

SELECT * FROM table_name ORDER BY date_column DESC LIMIT 10;

If the table does not have a date or timestamp column, we can use other columns to determine the order of the rows. For example, we can use an auto-incrementing primary key column (primary_key_column), assuming that the rows are added in chronological order.

SELECT * FROM table_name ORDER BY [primary_key_column] DESC LIMIT 10;

Largest Tables, In A Table

The below MySQL example is a query to retrieve the top 10 largest tables in a specified database, ranked by size in megabytes. This query can be useful for database administrators who want to identify the largest tables in their database, which can help with optimizing database performance and identifying potential storage issues.

SELECT table_name AS `Table`,
ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)`
FROM information_schema.TABLES
WHERE table_schema = '[your_database_name]'
ORDER BY (data_length + index_length) DESC
LIMIT 10;

Remember to replace [database_name] with the name of the database you want to check. The results will be displayed in the terminal, showing the table names and their sizes in megabytes.

The query is executed using the information_schema.TABLES table, which contains metadata about all tables in the MySQL server. The SELECT statement specifies the columns to retrieve from the table, which are the table_name and the size of the table in megabytes (MB). The ROUND function is used to round the size to two decimal places for better readability. The WHERE clause filters the results to only include tables from the specified database. The ORDER BY clause orders the results in descending order by the sum of the data_length and index_length columns, which gives the total size of each table. Finally, the LIMIT clause limits the number of results to 10.

Backing Up Databases

To back up a MySQL database using CLI and store it in /var/www, you can use the following command:

mysqldump --single-transaction -u [username] -p [database_name] > /var/www/[backup_filename].sql

Replace [username] with your MySQL username, [database_name] with the name of the database you want to back up, and [backup_filename] with the name you want to give your backup file. This example includes --single-transaction, creating a transaction around the entire dump process, ensuring that the data is not changed during the backup process.

After executing the command, the backup file will be created in /var/www with the name you specified. You can then copy this file to another location or server for safekeeping. Note that you will need to have write permissions to the /var/www directory in order to save the backup file there.

To ensure that the backup process is not interrupted and that the backup file contains correct data, you can follow these best practices:

• Check the database for errors before backing up It is always a good practice to check your database for any errors before creating a backup. You can use the mysqlcheck command to check for errors and repair any found issues.
• Use the --single-transaction option When using mysqldump to create a backup, use the --single-transaction option to ensure a consistent snapshot of the database is taken, even if the database is being updated during the backup process. This option creates a transaction around the entire dump process, ensuring that the data is not changed while the backup is being created.
• Use the --opt option The --opt option enables certain options that optimize the backup process, such as --add-drop-table, --add-locks, and --lock-tables, which can help ensure that the backup is accurate and complete.
• Check the backup file for errors After the backup process is complete, you can check the backup file for errors using the mysqlcheck command with the --check and --databases options.
• Test the backup file You should also test the backup file by restoring it to a test database and comparing the data to the original database to ensure that the backup is complete and accurate.

By following these best practices, you can help ensure that your backup process is reliable and that the backup file contains correct data.

Truncating Data

To truncate data from a specified table in MySQL, you can use the TRUNCATE TABLE command followed by the name of the table you want to truncate.

Here's the basic syntax:

TRUNCATE TABLE table_name;

This command will delete all data from the specified table and reset any auto-incrementing primary key values to their starting value. If you want to truncate data from a specified table within a certain date range or using ID ranges, you can do so using a WHERE clause in combination with the TRUNCATE TABLE command. Here are some examples:

• Truncate data from a specified table before a certain date:

  TRUNCATE TABLE table_name
  WHERE date_column < '2022-01-01';
  

• Truncate data from a specified table after a certain date:

  TRUNCATE TABLE table_name
  WHERE date_column > '2022-01-01';
  

• Truncate data from a specified table within a date range:

  TRUNCATE TABLE table_name
  WHERE date_column BETWEEN '2022-01-01' AND '2022-02-01';
  

• Truncate data from a specified table within an ID range:

  TRUNCATE TABLE table_name
  WHERE id BETWEEN 100 AND 200;