The World of MySQL & MariaDB

MySQL Command Options: Complete Guide from Basics to Troubleshooting

Basic Operations
目次

1. Introduction: What are MySQL command options?

MySQL is a relational database management system (RDBMS) used in many web applications and business systems. To streamline its operation, many cases use the command-line interface (CLI). In this article, we explain MySQL commands and their options. This will enable readers to manage databases and execute queries efficiently. It is especially suitable for the following readers.
  • MySQL beginners who want to try operating from the command line
  • Intermediate users who already have basic knowledge and want to achieve more efficient operation
  • Those who want to learn options useful in real-world operational scenarios

Why are MySQL command options important?

MySQL also offers GUI tools, but using the CLI provides the following benefits.
  1. Fast operation – You can quickly perform specific tasks with a single command.
  2. Ease of automation – By embedding commands in scripts or batch files, you can streamline repetitive tasks.
  3. Remote management support – Direct connection and management of servers become possible, increasing flexibility.
By reading this article, you can master MySQL command-line operations and acquire knowledge to improve work efficiency. The next section will provide a detailed explanation of MySQL command syntax and its main options.

2. Basic Syntax and Usage of MySQL Commands

The MySQL command-line tool is a powerful utility that makes database management and query execution easy. This section explains the basic syntax and usage of MySQL commands.

Basic Syntax of MySQL Commands

The MySQL client tool is used with the following basic syntax.
mysql [options] [database_name]
Explanation:
  • mysql: Start of the command.
  • [options]: Settings such as username, password, host name, etc.
  • [database_name]: The name of the database to operate on (optional).

Basic Connection Commands

  1. Example of a local connection
   mysql -u root -p
  • -u: Specifies the username.
  • -p: Prompts for password entry (a prompt will appear).
  1. Example of a remote connection
   mysql -u root -p -h 192.168.1.10 -P 3306
  • -h: Specifies the host name or IP address.
  • -P: Specifies the port number (default is 3306).
  1. Example of connecting with a specified database
   mysql -u root -p testdb
  • testdb: Automatically uses this database upon connection.

Basic Query Execution Examples

  1. Show list of databases
   SHOW DATABASES;
  1. Select a database
   USE testdb;
  1. Show list of tables
   SHOW TABLES;
  1. Display table contents
   SELECT * FROM users;
  1. Exit MySQL session
   EXIT;

Useful Options List

OptionDescriptionExample
-uSpecify usernamemysql -u root
-pEnter password via promptmysql -u root -p
-hSpecify host namemysql -u root -h localhost
-PSpecify port numbermysql -u root -P 3306
-DSpecify the database to usemysql -u root -p -D testdb
--defaults-fileSpecify configuration filemysql --defaults-file=/path/to/my.cnf
--execute (-e)Execute query directlymysql -u root -p -e "SHOW DATABASES;"
--no-defaultsIgnore default settingsmysql --no-defaults -u root -p
--verbose (-v)Show detailed execution resultsmysql -u root -p --verbose

Advanced Examples of Basic Syntax

  1. Example: Export a specific table to CSV format
   mysql -u root -p -e "SELECT * FROM users;" > users.csv
  1. Example: Back up a database
   mysqldump -u root -p testdb > testdb_backup.sql
  1. Example: Restore a database from backup
   mysql -u root -p testdb < testdb_backup.sql

Summary

In this section we covered the basic syntax and key options of MySQL commands. These commands help streamline everyday database management tasks. In the next section we’ll provide more practical option usage and detailed explanations, so please continue reading.

3. Hands‑On: Detailed Guide to Useful Options

In this section, we provide a detailed explanation of handy MySQL command‑line options with concrete examples. Leveraging these options enables efficient and flexible database management.

Option to Execute SQL Queries Directly (-e or –execute)

This option lets you run SQL commands directly without logging into MySQL. Example:
  1. When displaying the list of databases
   mysql -u root -p -e "SHOW DATABASES;"
  1. When checking the contents of a specific table
   mysql -u root -p -e "SELECT * FROM users;" testdb
Key points:
  • You can separate multiple commands with semicolons.
   mysql -u root -p -e "USE testdb; SHOW TABLES;"
  • Convenient for batch processing and script integration.

Option to Specify a Configuration File (–defaults-file)

You can load a specific configuration file to simplify connection information and settings. Example:
  1. Connect by specifying a configuration file
   mysql --defaults-file=/etc/my.cnf -u root -p
Key points:
  • Useful for switching configurations across multiple environments (production, testing).
  • The configuration file can predefine the username and host.
Example configuration (/etc/my.cnf):
[client]
user=root
password=yourpassword
host=localhost
port=3306
With this, you can connect using the simple command below.
mysql --defaults-file=/etc/my.cnf

Option to Ignore Default Settings (–no-defaults)

This option runs MySQL while ignoring its default settings. Example:
mysql --no-defaults -u root -p
Key point:
  • Convenient when the configuration file has issues or you need to operate temporarily with a different setting.

Option to Compress Communication (–compress)

Reduces data transfer volume during remote connections, improving speed. Example:
mysql --compress -u root -p -h 192.168.1.10
Key points:
  • Lowers network load and boosts communication speed.
  • Especially effective when transferring large amounts of data.

Option to Enable SSL Connections (–ssl)

Uses SSL connections to encrypt communication for enhanced security. Example:
mysql -u root -p --ssl-ca=/path/to/ca-cert.pem
Key points:
  • Essential when handling highly confidential data.
  • Server‑side SSL configuration may also be required.

Practical Scenario Examples

  1. Export specific data to CSV
   mysql -u root -p -e "SELECT * FROM users;" > users.csv
  1. Obtain a backup file
   mysqldump -u root -p --all-databases > backup.sql
  1. Import a backup file
   mysql -u root -p testdb < backup.sql
  1. Search for specific data via remote connection
   mysql -u root -p -h 192.168.1.10 -e "SELECT * FROM logs WHERE level='error';"

Summary

This section covered detailed MySQL command options and practical examples.
  • -e option to execute SQL directly.
  • --defaults-file to leverage a config file.
  • --compress to optimize communication speed.
  • --ssl to enhance security.
Combining these options enables workflow efficiency, automation, and stronger security.

4. Command Option Usage Examples (Practical Scenarios)

In this section, we present concrete scenarios that make use of MySQL command options. By reviewing operation examples useful for real-world tasks and database administration, you can enhance your command proficiency.

Scenario 1: Obtaining Database Backups

Data protection is one of the most important tasks in database management. MySQL provides commands that make it easy to obtain backups. Command Example:
mysqldump -u root -p --all-databases > backup.sql
Explanation:
  • mysqldump: Creates a dump (export) of the database.
  • --all-databases: Backs up all databases on the server.
  • >: Uses output redirection to save the result to backup.sql.
Key Points:
  • When backing up only a specific database:
  mysqldump -u root -p testdb > testdb_backup.sql
  • When compressing for storage:
  mysqldump -u root -p testdb | gzip > testdb_backup.sql.gz
  • You can create a batch file to automate scheduled backups.

Scenario 2: Restoring Data (Import)

Restoring data from a backup is also important. Command Example:
mysql -u root -p testdb < testdb_backup.sql
Explanation:
  • <: Imports data from a file.
  • testdb: Specifies the name of the database to restore.

Scenario 3: Exporting Specific Table Data to CSV Format

This describes how to export table data to CSV format for data analysis and report creation. Command Example:
mysql -u root -p -e "SELECT * FROM users;" testdb > users.csv
Explanation:
  • SELECT * FROM users;: Retrieves all data from the users table.
  • >: Saves the output result to a file in CSV format.

Scenario 4: Data Management via Remote Connection

When managing multiple servers, you can use remote connections to manage data directly. Command Example:
mysql -u root -p -h 192.168.1.10 -P 3306
Explanation:
  • -h: Specifies the hostname or IP address.
  • -P: Specifies the port number (default is 3306).

Scenario 5: Checking Database Status

By checking the server’s condition and performance, you can detect issues early. Command Example:
  1. List of connected users
   mysql -u root -p -e "SHOW PROCESSLIST;"
  1. Check server status
   mysql -u root -p -e "SHOW STATUS;"
  1. Check database size
   mysql -u root -p -e "SELECT table_schema 'DB Name', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 'Size (MB)' FROM information_schema.tables GROUP BY table_schema;"

Summary

In this section, we presented practical scenarios that leverage MySQL command options.
  1. Backup and Restore to ensure safe data management.
  2. Data Export and Import to streamline analysis and report creation.
  3. Remote Connection and Status Checks to maintain server performance.
Mastering these operations will make MySQL management and operation smoother.

5. Troubleshooting and Precautions

In this section, we explain how to resolve common errors and issues that occur when using the MySQL command line. We also cover precautions for safe and efficient database management.

Common Errors and Their Solutions

Error 1: ‘Access denied for user’ (access denial error)

Example error message:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
Cause:
  • The username or password is incorrect.
  • The user does not have the required privileges.
Solution:
  1. Reconfirm the username and password
   mysql -u root -p
  1. Check user privileges
   SELECT user, host FROM mysql.user;
  1. Grant privileges
   GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
   FLUSH PRIVILEGES;

Error 2: ‘Can’t connect to MySQL server’ (connection error)

Example error message:
ERROR 2002 (HY000): Can't connect to MySQL server on 'localhost' (10061)
Cause:
  • The MySQL server is not running.
  • The target hostname or port number is incorrect.
Solution:
  1. Verify that the MySQL server is running
   systemctl status mysql
or
   sudo service mysql status
If the server is stopped, start it with:
   sudo service mysql start
  1. Check the hostname and port number
   mysql -u root -p -h 127.0.0.1 -P 3306
  1. Verify firewall settings
   sudo ufw allow 3306

Security and Performance Considerations

Ensuring Secure Password Management

  • Do not write passwords directly on the command line
  mysql -u root -p
You will be prompted to enter the password when the command runs.
  • Manage using a configuration file
  [client]
  user=root
  password=securepassword
Save the above configuration to ~/.my.cnf and change the file permissions.
  chmod 600 ~/.my.cnf

Thorough Data Backup and Restoration

  • Take regular backups to prevent data loss.
  • Verify data integrity before backing up.

Strengthening Security for Remote Connections

  • Always use SSL connections.
  • Close unnecessary ports and configure the firewall.
  • Restrict access on a host basis.

Optimizing Database Performance

Index Optimization

  • Set appropriate indexes to speed up queries.
  CREATE INDEX idx_users_name ON users(name);

Configuring Query Cache

  • Enabling the query cache improves the processing speed of identical queries. Example configuration (my.cnf):
  query_cache_size = 16M
  query_cache_type = 1

Summary

This section detailed error handling and precautions for the MySQL command line.
  • Error handling: Understand how to address connection and permission errors.
  • Security management: Secure password handling and protection of remote connections.
  • Performance improvement: Optimize indexes and cache settings.
By implementing these practices, you can maintain a stable operating environment and respond quickly when issues arise.

6. Summary and Next Steps

In the previous articles, we have covered MySQL command options from basics to advanced applications. In this section, we’ll review what you’ve learned and provide guidance for deepening your knowledge in the next steps.

Key Points Summary

  1. Introduction: The Importance of MySQL Command Options
  • The MySQL command is an essential tool for efficient database management and operation.
  • Leveraging command options enhances flexibility and automation of operations.
  1. Basic Syntax and Usage
  • We explain basic connection commands and how to execute queries.
  • Details of commonly used options (e.g., -u, -p, -h) are presented.
  1. In-Depth Look at Handy Options
  • Direct execution of SQL commands and configuration management using -e and --defaults-file.
  • Enhance security and performance with SSL connections and data compression options.
  1. Applying Options in Practical Scenarios
  • Examples of backup and restore, exporting in CSV format, and remote connections.
  1. Troubleshooting and Considerations
  • We discuss solutions to common errors and key points for strengthening security.
  • Examples of index and query cache settings for performance optimization.

Next Steps: Elevating Your Skills

Now that you understand the basics and applications of MySQL command options, we recommend advancing your learning with the following topics.

1. Advanced MySQL Configuration and Tuning

  • Server Performance Optimization
  • Improving data management efficiency through index design and partitioning.
  • Query Optimization
  • Analyzing queries using the EXPLAIN command.
  • Identifying and fixing issues with the slow query log.

2. Automation and Script Management

  • Automation with Shell Scripts
  • Automated scheduling of backups and log management.
  • Periodic execution using CRON jobs.
  • Implementing Batch Processing
  • Optimizing bulk processing by combining SQL files.

3. Security Hardening and Monitoring Systems

  • User Management and Access Control
  • Granular privilege separation and minimum-privilege policy settings.
  • Deploying Monitoring Tools
  • Strengthening performance and fault monitoring with MySQL Enterprise Monitor and Percona Monitoring and Management.

Conclusion

Mastering MySQL command options enables more efficient database management and automated operations. This article covered a wide range of topics—from basic commands to advanced scenarios and troubleshooting—for beginners to intermediate users. Going forward, practice the commands to become comfortable with them and aim to deepen your knowledge and skills. We hope this guide serves as a practical resource to support your MySQL operations.