MySQL Utilities Explanation
MySQL
MySQL is the most commonly used command to manage MySQL databases. It helps connect to MySQL databases and perform all necessary database operations. This utility is included with the standard installation. When we run it in the command prompt, it prompts for the user password required for connection.
mysql -u<user> -p
Enter password:
MySQL Dump
MySQL Dump is a powerful tool that assists in creating backups and restoring them when necessary. Included in the standard installation, it offers a variety of features that administrators can use to tailor the backup process. With a single command, we can backup either a single database or all databases. Additionally, MySQL Dump aids in transferring databases between servers.
mysqldump -u [username] -p --databases [database_name] > [dumpfile.sql]
- How to Back Up a Single Database
mysqldump -u [username] -p --databases [database_name] > [dumpfile.sql]
- How to Back Up Multiple Databases
mysqldump -u [username] -p --databases [database_name] [database_name] > [dumpfile.sql]
- How to Back Up All the Databases
mysqldump -u [username] -p –all-databases > [dumpfile.sql]
- How to Restore Single MySQL Database
mysql -u [username] -p [database_name] < [dumpfile.sql]
## or using
mysql> source [dumpfile.sql]
- How to Restore All MySQL Databases
mysql -u [username] -p [database_name] < [dumpfile.sql]
| Command | Usage |
|---|---|
| # mysaldump –help | Use the help option to get all the options. |
| # mysqldump –no-data | Only backup structure, no data backup. |
| # mysaldump –no-create-info | Only backup data without structure. |
| # mysqldump –login-path | Database connects info shared in the file, instead of providing during the command line on the backup script. |
| # mysqldump table1 database1 | To back up a single table name table1 from database database1. |
| # mysaldump table1 table2 database1 | To back up multiple tables table1 and table2 from a database named database1. |
MySQL Pump
mysqlpump is a powerful tool for creating backups. It offers features not available in mysqldump, including parallel backups, streaming backup support, and compression. The features include: Multi-threaded, Parallel schema backups and Compression.
mysqlpump -u [username] -p --all-databases > [dumpfile.sql]
For MySQL 8.0, using mysqldump is highly recommended. However, for lower versions, mysqlpump is the preferred choice due to its advantages.
MySQL Backup
mysqlbackup is a tool that offers more features than mysqldump and mysqlpump. It enables compressed, encrypted, and user-friendly backups. Key features include:
- Incremental backup: This feature allows for incremental backups, which only backup data since the last full backup.
- Encryption: Safeguard your backups with encryption, enhancing security.
- Parallel backups: This feature aids in backing up large databases, minimizing the backup time.
- Validation: Now you can validate backups to ensure they’re suitable for restoration.
## backup
mysqlbackup --user=[username] --password --host=[hostname]--with-timestamp --backup-dir=[backup directory] backup
## validate
mysqlbackup --backup-image=/backup/mysql/mysql_data.mbi validate
mysqlbackup is available only in the Enterprise Edition.
MySQL Check
mysqlcheck is a tool for checking errors in MySQL databases and can be used to optimize and improve their performance. To proactively detect and repair errors, it’s important to run mysqlcheck periodically on our MySQL databases. This tool also allows us to check and repair individual tables.
mysqlcheck --databases [databasename] -u[username] -p
MySQL Binlog
mysqlbinlog is a tool used to read and process MySQL binary log files, which contain all changes made to a MySQL database. You can use mysqlbinlog to view the contents of binary log files, generate reports of the changes made, or apply those changes to another MySQL database.
We can utilize mysqlbinlog for the following purposes:
- Resolving database issues
- Restoring data from a backup
- Replicating data between MySQL database servers
- Conducting point-in-time recovery
mysqlbinlog /var/lib/mysql/binlog.000058”
MySQL Safe
mysqld_safe is the standard recommended method for restarting the mysqld server. Most of its options are identical to those in mysqld. It reads all options from the mysqld, server, and mysqld_safe sections in the configuration file. mysqld_safe also allows for passing additional parameters to mysqld when restarting the MySQL server. The most critical and widely used one is the –skip-grant-tables option, which is used for resetting the root password without needing the old password.
MySQL Dump Slow
mysqldumpslow is a useful utility for identifying performance issues. MySQL has a feature that logs all queries running beyond a defined threshold into a file for analysis. This slow query log, which will be discussed in later chapters, is not easily readable. mysqldumpslow assists in summarizing the information from the slow query log and presents it in a format conducive to debugging.
## Displays only the first five queries.
mysqldumpslow -t 5 /log/slow.log
## Displays output by sorting by count.
mysqldumpslow -s c /log/slow.log
MySQL Show
mysqlshow is a command-line utility that displays the details of databases, tables, and columns. It aids in quickly checking these details.
mysqlshow -uroot -p
mysqlshow dbname -uroot -p
mysqlshow -t dbname tablename -uroot -p
MySQL Secure Installation
Once the database installation is complete, you can use the Command Line Interface (CLI) to secure the database. This approach is more efficient than the previously mentioned manual methods. The following steps are optional but necessary for securing databases in production environments:
- Reset the root user’s password.
- Delete anonymous user accounts.
- Remove the test database, which was created by default during the installation.
- Disable remote root login.
- Enable password encryption.
- Reload the privilege tables. This ensures that all the changes take effect immediately.
mysql_secure_installation
MySQL Import
mysqlimport is a command-line interface tool. It allows you to import data into a MySQL table from various file formats such as TSV, CSV, and JSON. It can also import compressed files like ZIP and GZ files.
mysqlimport --local [schema]
MySQL Config
mysql_config is a shell script that displays information about the MySQL server and client libraries. It’s used for compiling and linking MySQL client programs.
mysql_config
MySQL Config Editor
The utility mysql_config_editor is a useful tool for storing and managing MySQL credentials. It allows for the creation, editing, and deletion of login paths, facilitating authentication. This offers a secure and efficient approach to managing database credentials. It’s crucial not to embed the login credentials of the root user in any backup script; instead, mysql_config_editor can be used to store these securely. This utility significantly bolsters security when automating scripts or applications interacting with MySQL, eliminating the need to expose plain text passwords.
mysql_config_editor set --login-path=backupUser --host=localhost --user=root --pass
MySQL Slap
mysqlslap is a tool that facilitates load testing for MySQL, crucial for evaluating the database’s performance and scalability. As a benchmarking tool, it generates comprehensive reports detailing performance metrics such as throughput and execution time.
mysqlslap --concurrency=20 --iterations=5 --query=/tmp/test.sql --create-schema=test -uroot -p
MySQL Router
MySQL Router is included in the Enterprise Edition of MySQL. This lightweight middleware software can be installed as either a container or a server. It serves to direct traffic within a clustered environment, thereby ensuring high availability.
mysqlrouter --help
MySQL Shell
MySQL Shell is a client utility for client-side connections. It’s available on both Linux and Windows platforms. MySQL Shell allows the interactive client to directly connect and access database objects via an interactive shell.
mysqlsh
Reference
- Mastering MySQL Administration - High Availability, Security, Performance, and Efficiency (Y V Ravi Kumar, Arun Kumar Samayam, Naresh Kumar Miryala)