How to use the command 'mysql' (with examples)

MySQL is a popular open-source relational database management system (RDBMS) that is widely used for managing databases. It provides a variety of tools for administrators and developers to manage, retrieve, and store data efficiently. The command-line tool mysql is crucial for executing SQL queries directly from the command line, interacting with databases, performing administrative tasks, automating database jobs, and managing backups and restorations. Below are several use cases illustrating how to use the mysql command effectively.

Use case 1: Connect to a database

Code:

mysql database_name

Motivation:

Connecting directly to a database from the command line allows developers and database administrators to quickly run queries and perform maintenance tasks. This is especially useful during development for testing SQL commands or in production for immediate troubleshooting.

Explanation:

• mysql: Invokes the MySQL command-line tool.
• database_name: Specifies the name of the database you want to connect to. This tells MySQL the target database where you wish to execute various SQL commands.

Example output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12345
Server version: 8.0.29

mysql>

Use case 2: Connect to a database, user will be prompted for a password

Code:

mysql -u user --password database_name

Motivation:

When security is a concern, it’s advisable not to use plain text passwords in command-line inputs. This command ensures a secure connection by prompting for the password separately, which prevents the password from being visible in the command line or in process listings.

Explanation:

• -u user: Specifies the username with which to connect to the database. This argument is required for identifying the user account that has access permissions.
• --password: Prompts the user to enter their MySQL password securely.
• database_name: Identifies the database you are connecting to, ensuring that your operations are targeted accurately.

Example output:

Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12345
Server version: 8.0.29

mysql>

Use case 3: Connect to a database on another host

Code:

mysql -h database_host database_name

Motivation:

When databases are hosted on remote servers, administrators and developers need to connect to them over the network. This use case allows users to specify the remote server, facilitating distributed database management and remote debugging.

Explanation:

• -h database_host: Specifies the hostname or IP address of the remote database server you wish to connect to. This option expands networking capabilities by allowing connections to external servers.
• database_name: Indicates which database on the specified host you intend to access.

Example output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 67890
Server version: 8.0.29

mysql>

Use case 4: Connect to a database through a Unix socket

Code:

mysql --socket path/to/socket.sock

Motivation:

Some configurations and environments might prioritize or require Unix socket connections over the default TCP/IP connections, such as when databases are hosted on the same server as the application or to increase security by avoiding TCP/IP overheads.

Explanation:

• --socket path/to/socket.sock: Specifies the path to the Unix socket file used for connecting to the local MySQL server. Sockets can provide performance advantages and added security by bypassing network interfaces.

Example output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 54321
Server version: 8.0.29

mysql>

Use case 5: Execute SQL statements in a script file (batch file)

Code:

mysql -e "source filename.sql" database_name

Motivation:

Automating repetitive database tasks and batch processing of SQL scripts enhances efficiency and reduces manual errors. This use case is perfect for applying mass updates, fixes, or deployment setups.

Explanation:

• -e "source filename.sql": Executes the SQL commands in the specified file. The source command reads SQL commands from the file and executes them as if they were typed directly into the terminal.
• database_name: Ensures that the SQL statements are executed in the correct database context, preventing accidental data modification.

Example output:

Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.02 sec)

Use case 6: Restore a database from a backup created with mysqldump (User will be prompted for a password)

Code:

mysql --user user --password database_name < path/to/backup.sql

Motivation:

Restoring databases from backups is crucial for data recovery and migration. This process is often employed after system failures, during migrations to a new server, or when rolling back changes to a previous state.

Explanation:

• --user user: Specifies the username who has privileges to restore the database. The user’s credentials are critical for ensuring security and successful restoration.
• --password: Prompts for the user’s password, safeguarding it from being exposed.
• database_name: Indicates which database should be restored, helping to avoid restoration errors.
• < path/to/backup.sql: Directs input to the mysql command from the specified backup file, which contains the dumped SQL statements for restoration.

Example output:

Enter password: 
Restored the database successfully.

Use case 7: Restore all databases from a backup (User will be prompted for a password)

Code:

mysql --user user --password < path/to/backup.sql

Motivation:

Global restorations are necessary when recovering all databases after a major system event such as a catastrophic failure or in scenarios where multiple databases are being migrated to a new server simultaneously.

Explanation:

• --user user: Identifies the administrator username capable of restoring all databases. Administrator permissions are needed for broad actions like full-system recoveries.
• --password: Prompts for a password to ensure the operation is secure.
• < path/to/backup.sql: Utilizes the SQL file created with mysqldump to restore all databases, effectively reversing the backup process.

Example output:

Enter password: 
All databases restored successfully.

Conclusion:

Using the MySQL command-line tool provides a powerful means of interacting with your databases. It is particularly advantageous for carrying out administrative tasks, executing scripts, connecting to either local or remote databases, and performing backups and restorations. Mastery of these use cases can greatly enhance your productivity and the safety of your data management operations.