How to use the command "mysql" (with examples)
The MySQL command-line tool is a powerful utility for managing MySQL databases. It allows you to connect to databases, execute SQL statements, restore backups, and more.
Use case 1: Connect to a database
Code:
mysql database_name
Motivation:
Connecting to a specific database is essential when working with multiple databases or when you want to query and modify the data in a particular database.
Explanation:
mysql: The command to initiate the MySQL command-line tool.database_name: The name of the database you want to connect to.
Example output:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1234567
Server version: 8.0.26 MySQL Community Server - GPL
...
mysql>
Use case 2: Connect to a database, user will be prompted for a password
Code:
mysql -u user --password database_name
Motivation:
Entering the username and password every time you connect to a database can be tedious. This use case allows you to specify the username and be prompted for the password, providing an additional level of security.
Explanation:
mysql: The command to initiate the MySQL command-line tool.-u user: Specifies the username to connect with.--password: Prompts the user for the password.database_name: The name of the database you want to connect to.
Example output:
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1234567
Server version: 8.0.26 MySQL Community Server - GPL
...
mysql>
Use case 3: Connect to a database on another host
Code:
mysql -h database_host database_name
Motivation:
Sometimes, databases are hosted on remote servers. This use case allows you to connect to a specific database on another host, enabling you to manage databases hosted on different servers.
Explanation:
mysql: The command to initiate the MySQL command-line tool.-h database_host: Specifies the host where the database is located.database_name: The name of the database you want to connect to.
Example output:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1234567
Server version: 8.0.26 MySQL Community Server - GPL
...
mysql>
Use case 4: Connect to a database through a Unix socket
Code:
mysql --socket path/to/socket.sock
Motivation:
In some cases, MySQL databases communicate through a Unix socket instead of a network socket. This use case allows you to connect to a database using a Unix socket.
Explanation:
mysql: The command to initiate the MySQL command-line tool.--socket path/to/socket.sock: Specifies the path to the Unix socket.
Example output:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1234567
Server version: 8.0.26 MySQL Community Server - GPL
...
mysql>
Use case 5: Execute SQL statements in a script file (batch file)
Code:
mysql -e "source filename.sql" database_name
Motivation:
Executing SQL statements contained in a script file (also known as a batch file) can be useful when you want to automate database operations or perform complex tasks that require multiple queries.
Explanation:
mysql: The command to initiate the MySQL command-line tool.-e "source filename.sql": Specifies the SQL statement or script file to execute.database_name: The name of the database you want to connect to.
Example output:
+----------------+
| count(*) |
+----------------+
| 100 |
+----------------+
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 a database from a backup is a common task when recovering from a database failure or migrating data. This use case allows you to restore a specific database from a backup created with mysqldump.
Explanation:
mysql: The command to initiate the MySQL command-line tool.--user user: Specifies the username to connect with.--password: Prompts the user for the password.database_name: The name of the database you want to connect to.< path/to/backup.sql: Redirects the contents of the backup file as the input for the command.
Example output:
Query OK, 0 rows affected, 1 warning (0.00 sec)
...
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:
Restoring all databases from a backup is helpful when you need to restore an entire MySQL server after a failure or when migrating to a new server. This use case allows you to restore all databases from a backup file.
Explanation:
mysql: The command to initiate the MySQL command-line tool.--user user: Specifies the username to connect with.--password: Prompts the user for the password.< path/to/backup.sql: Redirects the contents of the backup file as the input for the command.
Example output:
Query OK, 0 rows affected, 1 warning (0.00 sec)
...
Conclusion:
The mysql command provides a wide range of capabilities for interacting with MySQL databases. Whether you need to connect to a specific database, execute SQL statements, or restore backups, the mysql command makes it possible to accomplish these tasks efficiently and effectively.
