How to connect to a MySQL database using the command line?
Jun 19, 2025 am 01:05 AM
The steps to connect to the MySQL database are as follows: 1. Use the basic command format mysql -u username-p -h host address to connect, enter the username and password to log in; 2. If you need to directly enter the specified database, you can add the database name after the command, such as mysql -u root -p myproject; 3. If the port is not the default 3306, you need to add the -P parameter to specify the port number, such as mysql -u root -p -h 192.168.1.100 -P 3307; In addition, if you encounter a password error, you can re-enter it. If the connection fails, you need to check the network, firewall or permission settings. If the client is missing, you can install mysql-client on Linux through the package manager. Master these commands to easily connect to MySQL database in a command-line environment.
It is actually not difficult to connect to MySQL database, especially in the command line environment. As long as you know the basic commands and parameters, you can do it in a few steps.
Basic command format
The most commonly used connection method is to use the mysql command, the format is roughly like this:
mysql -u Username -p -h Host Address
Among them, -u is followed by your database user name, -p means that you need to enter the password next (it can also be written directly in the command, but it is not recommended), -h is the address of the database server. If it is a local development environment, this parameter can usually be omitted, and the local database will be connected by default.
For example, if you want to use the root user to log in to the local MySQL service, you can directly enter:
mysql -u root -p
Then follow the prompts to enter your password.
Specify the database when logging in
Sometimes you don’t just want to connect to the database, but you want to operate a specific database as soon as you come up. At this time, you can add the database name after the command:
mysql -u Username -p Database name
In this way, after connecting, it will automatically switch to that database without manually executing USE database_name;
For example, suppose you want to connect to the myproject database, the command is:
mysql -u root -p myproject
After entering your password, you are already in the myproject database.
What if the port is not the default 3306?
Sometimes, MySQL may run on other ports, such as if you open a non-standard port on a cloud server, or you do port mapping locally. At this time, you need to add a -P parameter to specify the port number:
mysql -u Username -p -h Host Address -P Port Number
Note -P is capitalized, don't write it incorrectly. For example, if you want to connect to MySQL on remote host 192.168.1.100 , the port is 3307, and the command is:
mysql -u root -p -h 192.168.1.100 -P 3307
Several FAQ reminders
What should I do if I enter the password incorrectly?
Just press Enter to re-enter, but if you enter it wrong several times in a row, it may be limited for a period of time.Can't connect to the database?
Check whether the target IP can be pinged, or use telnet to test whether the port is open. It may also be a problem with firewall or permission configuration.Not having the mysql client installed?
You can use a package manager to install it on Linux, such as Ubuntu:sudo apt install mysql-client
Basically that's it. By mastering these commands, you can easily connect to the MySQL database on the command line.
The above is the detailed content of How to connect to a MySQL database using the command line?. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
How to repair Windows 11 using command prompt
Jul 05, 2025 am 02:59 AM
How to use command prompt to repair Windows 11? 1. Enter the command prompt through the recovery environment: enter the recovery environment through USB boot or three consecutive interrupts, and then select Troubleshooting > Advanced Options > Command Prompt; 2. Use DISM to repair the system image: Run DISM/Online/Cleanup-Image/CheckHealth to check for corruption, DISM/Online/Cleanup-Image/ScanHealth scanning problem, DISM/Online/Cleanup-Image/RestoreHealth to repair the problem, and specify an external source if necessary; 3. Run SF
Resetting the root password for MySQL server
Jul 03, 2025 am 02:32 AM
To reset the root password of MySQL, please follow the following steps: 1. Stop the MySQL server, use sudosystemctlstopmysql or sudosystemctlstopmysqld; 2. Start MySQL in --skip-grant-tables mode, execute sudomysqld-skip-grant-tables&; 3. Log in to MySQL and execute the corresponding SQL command to modify the password according to the version, such as FLUSHPRIVILEGES;ALTERUSER'root'@'localhost'IDENTIFIEDBY'your_new
Performing logical backups using mysqldump in MySQL
Jul 06, 2025 am 02:55 AM
mysqldump is a common tool for performing logical backups of MySQL databases. It generates SQL files containing CREATE and INSERT statements to rebuild the database. 1. It does not back up the original file, but converts the database structure and content into portable SQL commands; 2. It is suitable for small databases or selective recovery, and is not suitable for fast recovery of TB-level data; 3. Common options include --single-transaction, --databases, --all-databases, --routines, etc.; 4. Use mysql command to import during recovery, and can turn off foreign key checks to improve speed; 5. It is recommended to test backup regularly, use compression, and automatic adjustment.
Handling NULL Values in MySQL Columns and Queries
Jul 05, 2025 am 02:46 AM
When handling NULL values ??in MySQL, please note: 1. When designing the table, the key fields are set to NOTNULL, and optional fields are allowed NULL; 2. ISNULL or ISNOTNULL must be used with = or !=; 3. IFNULL or COALESCE functions can be used to replace the display default values; 4. Be cautious when using NULL values ??directly when inserting or updating, and pay attention to the data source and ORM framework processing methods. NULL represents an unknown value and does not equal any value, including itself. Therefore, be careful when querying, counting, and connecting tables to avoid missing data or logical errors. Rational use of functions and constraints can effectively reduce interference caused by NULL.
Establishing secure remote connections to a MySQL server
Jul 04, 2025 am 01:44 AM
TosecurelyconnecttoaremoteMySQLserver,useSSHtunneling,configureMySQLforremoteaccess,setfirewallrules,andconsiderSSLencryption.First,establishanSSHtunnelwithssh-L3307:localhost:3306user@remote-server-Nandconnectviamysql-h127.0.0.1-P3307.Second,editMyS
Analyzing the MySQL Slow Query Log to Find Performance Bottlenecks
Jul 04, 2025 am 02:46 AM
Turn on MySQL slow query logs and analyze locationable performance issues. 1. Edit the configuration file or dynamically set slow_query_log and long_query_time; 2. The log contains key fields such as Query_time, Lock_time, Rows_examined to assist in judging efficiency bottlenecks; 3. Use mysqldumpslow or pt-query-digest tools to efficiently analyze logs; 4. Optimization suggestions include adding indexes, avoiding SELECT*, splitting complex queries, etc. For example, adding an index to user_id can significantly reduce the number of scanned rows and improve query efficiency.
Aggregating data with GROUP BY and HAVING clauses in MySQL
Jul 05, 2025 am 02:42 AM
GROUPBY is used to group data by field and perform aggregation operations, and HAVING is used to filter the results after grouping. For example, using GROUPBYcustomer_id can calculate the total consumption amount of each customer; using HAVING can filter out customers with a total consumption of more than 1,000. The non-aggregated fields after SELECT must appear in GROUPBY, and HAVING can be conditionally filtered using an alias or original expressions. Common techniques include counting the number of each group, grouping multiple fields, and filtering with multiple conditions.
Managing Transactions and Locking Behavior in MySQL
Jul 04, 2025 am 02:24 AM
MySQL transactions and lock mechanisms are key to concurrent control and performance tuning. 1. When using transactions, be sure to explicitly turn on and keep the transactions short to avoid resource occupation and undolog bloating due to long transactions; 2. Locking operations include shared locks and exclusive locks, SELECT...FORUPDATE plus X locks, SELECT...LOCKINSHAREMODE plus S locks, write operations automatically locks, and indexes should be used to reduce the lock granularity; 3. The isolation level is repetitively readable by default, suitable for most scenarios, and modifications should be cautious; 4. Deadlock inspection can analyze the details of the latest deadlock through the SHOWENGINEINNODBSTATUS command, and the optimization methods include unified execution order, increase indexes, and introduce queue systems.
