Keywords: MySQL command-line query | shell script automation | remote database access
Abstract: This article provides an in-depth exploration of executing single MySQL queries efficiently in command-line environments, with particular focus on scripted tasks involving remote servers. It details the core parameters of the mysql command-line tool, emphasizing the use of the -e option and its critical role in preventing shell expansion issues. By comparing different quotation mark usage scenarios, the article offers practical techniques to avoid wildcard misinterpretation, while extending the discussion to advanced topics such as connection parameters and output format control, enabling developers to execute database queries safely and reliably in automation scripts.
Fundamental Approach to MySQL Query Execution via Command Line
In automated scripting tasks, executing MySQL queries directly through the command line offers an efficient and flexible solution. The core command structure follows this pattern: mysql -u <username> -p -e 'query statement'. Here, the -u parameter specifies the database user, -p prompts for password input (though using -ppassword directly is possible, it poses security risks), and the -e parameter (short for execute) is crucial, allowing direct execution of subsequent SQL statements from the command line.
Critical Distinctions in Quotation Mark Usage
When specifying query statements, the choice of quotation marks is vital. As shown in the best answer, using single quotes 'select * from schema.table' instead of double quotes effectively prevents the shell from expanding wildcards like * into filenames. In Unix/Linux shell environments, * within double quotes is interpreted as a file-matching pattern for the current directory, potentially altering the query to something like select file1.txt file2.txt from schema.table, leading to syntax errors. Single quotes preserve the string's literal content, ensuring the SQL statement is passed intact to the MySQL client.
Remote Server Connection Configuration
For queries on remote servers, the host parameter must be included: mysql -u <user> -p -h <host_address> -e 'query statement'. For example, to connect to slavedb.mydomain.com, the command expands to mysql -uroot -p -hslavedb.mydomain.com -e 'select * from users;'. The -h parameter specifies the hostname or IP address of the remote MySQL server, forming the basis for cross-server operations.
Database Selection and Complete Command Examples
Before executing a query, specifying the target database is typically necessary. This can be achieved in two ways: first, by appending the database name directly at the end of the command, such as mysql -u root -p mydb_production -e 'select * from users;'; second, by using fully qualified table names like schema.table within the query statement. A complete best-practice command is: mysql -u root -p -hslavedb.mydomain.com mydb_production -e 'select * from users;'. This structure ensures integrated completion of connection, authentication, database selection, and query execution.
Advanced Techniques and Considerations
For complex queries or multiple statement executions, SQL statements can be saved in a file and executed via redirection: mysql -u user -p database < query.sql. Regarding output formatting, adding the -t parameter displays results in table format, while the -B (batch) parameter generates tab-separated output suitable for script processing. Security-wise, avoid plaintext passwords in commands; instead, use configuration files (e.g., ~/.my.cnf) or environment variables. Error handling can be implemented by checking exit status codes, e.g., in a bash script: if mysql -u user -p -e 'query'; then echo "Success"; else echo "Failed"; fi.