MySQL is a powerful and widely used SQL database server that plays a crucial role in web applications and services. MySQL is designed for mission-critical, heavy-load production systems, and mass-deployed software. As a fast, multi-threaded, and robust database server, it provides efficient data storage and retrieval capabilities. This MySQL Overview in Linux article will provide insight to important information and steps in general basic procedures that you could do with MySQL. A few key benefits are listed below for running MySQL on a webserver.
- Being free and open-source, MySQL provides this level of freedom without any cost.
- MySQL’s storage-engine framework supports demanding applications.
- Features like high-speed partial indexes, full-text indexes, and unique memory caches contribute to superior database performance.
- MySQL can handle more than 50 million rows, making it suitable for large-scale applications.
- MySQL runs on various operating systems, including Linux, Windows, and UNIX variants.
Database Engines
MySQL supports different storage engines for data storage. Two common ones are InnoDB and MyISAM. These engines handle data differently under the surface, but you interact with the database in the same way. Each engine has its own advantages and disadvantages, so choose based on your specific requirements.
| InnoDB | MyISAM | |
| Locking Mechanism | InnoDB uses row-level locking, allowing concurrent access to different rows. Ideal for scenarios with frequent updates and inserts. | MyISAM employs full table-level locking, which can hinder concurrent access during writes. Better suited for read-heavy, static tables. |
| Crash Recovery | InnoDB has robust crash recovery mechanisms. It ensures data consistency even after unexpected shutdowns. | MyISAM lacks advanced crash recovery features. Data integrity may be compromised after crashes. |
| Foreign Keys | InnoDB supports foreign keys and relationship constraints. – Ensures referential integrity. | MyISAM does not enforce foreign keys. No built-in support for maintaining relationships. |
| Transactions | InnoDB provides transaction support (commit, rollback). – Changes are treated as a single unit of work. | MyISAM lacks transactional capabilities. Each operation is independent, no atomic transactions. |
| Full-Text Search | InnoDB introduced full-text search indexes in MySQL 5.6. | MyISAM has supported full-text indexes for a long time. |
| Performance | InnoDB is designed for high-volume data processing. – Performs well under heavy loads. | MyISAM excels in read-heavy scenarios. Efficient for static data with minimal updates. |
Other Database Engines
In addition to InnoDB and MyISAM, there are several other database engines used in various systems.
- Oracle Database
- Microsoft SQL Server
- PostgreSQL
- MongoDB
- Redis
- Elasticsearch
- SQLite
- Cassandra
Basic Functions of MySQL Management in a Linux Server
Logging into MySQL
There are two methods to login to a MySQL user.
The first method involves logging into MySQL as root using the mysql command that will drop you right into the MySQL command line interface.
- If you are not aware of your MySQL root password, you could use the command my_print_defaults -s client in your server’s command line interface to print the password used by the root user.
The second method involves logging into a specific MySQL user, whether root or any other. This process is done using the command mysql -u root -h localhost -p, here the “root” portion is the username that would need to change depending on the account being used.
Viewing The Default and Available Storage Engines
To view all available storage engines and their support status, use query SHOW ENGINES;
The Support column indicates whether an engine can be used. If the Support value for InnoDB is DEFAULT, it means InnoDB is the default storage engine for your MySQL server.
Viewing Users List
Once you’re in the MySQL client, execute the following query to retrieve a list of usernames that have access to the server SELECT user FROM mysql.user; , for a list of distinct users use SELECT DISTINCT User FROM mysql.user;
Viewing Databases List
List all databases in MySQL, you can use the following query SHOW DATABASES;
Inspecting Database Health Status
For MySQL, you can use the mysqlcheck utility to monitor overall database health. It performs checks and repairs if needed. To check all databases on a system you could use the following command mysqlcheck –all-databases within the command line interface of the Linux system.
Backing Up Databases
To dump (backup) MySQL databases, you can use the mysqldump utility. This tool creates a set of SQL statements that can be used to recreate the original database. The command to use is mysqldump -u root -p –all-databases > all_databases_backup.sql which will create a dump of the databases in a file, “all_databases_backup.sql”, within the directory that you’ve ran the command in.
Thank you for reading and I hope that you enjoyed this MySQL Overview in Linux guide. For more guides, please check out our Knowledge Base which is updated weekly!
-Written by Pascal Suissa