Connect to a MySQL Database Using the mysql Command
Estamos traduciendo nuestros guías y tutoriales al Español. Es posible que usted esté viendo una traducción generada automáticamente. Estamos trabajando con traductores profesionales para verificar las traducciones de nuestro sitio web. Este proyecto es un trabajo en curso.
This guide shows you how to connect to a MySQL database using mysql, the MySQL command-line client. This opens up a simple SQL shell environment, allowing you to perform SQL queries and commands on your database. If you require more advanced capabilities, consider using the MySQL Shell.
If you wish to connect to a Linode MySQL Managed Database, review the Connect to a MySQL Managed Database guide instead.
Before You Begin
- Obtain the connection details for the MySQL instance you wish to use. If you do not have a MySQL instance yet, you can create a Managed Database, deploy the MySQL Marketplace App, or install MySQL server (or MariaDB) on a Compute Instance. This instance must allow remote connections or you must run the mysql command from within same system.
- Ensure mysql is installed and is compatible with the MySQL version on your database server. Run the following command on the system you intend on using to verify that mysql is installed.
The steps in this guide are written for a non-root user. Commands that require elevated privileges are prefixed with sudo . If you’re not familiar with the sudo command, see the Linux Users and Groups guide.
General mysql Syntax
The main purpose of the mysql utility is to connect to a MySQL database server and open a simple SQL shell environment. The mysql command can be used to connect to either a local or remote database server. In the commands provided below, see the Common Command Options for information on each of the available options.
- Local database server: Use this command when connecting to a MySQL Server instance running on the same machine you are using.
mysql -h [host] -p [port] -u [username] -p If you wish to connect to a Linode MySQL Managed Database, review the Connect to a MySQL Managed Database guide instead.
Common Command Options
The following list is a collection of common options used with the mysqldump command. At minimum, the username and password is required. When connecting to a remote database server, the host (and perhaps the port) should be provided. For a full list of available options, reference the Command Options for Connecting to the Server documentation.
- Username ( —user=[] or -u [] ): The username of your MySQL user. This user must have proper grants to access the database.
- Password ( —password=[] or -p[] ): Specifies that the user’s password is required for the connection. The password can be entered directly in the command itself (though that is not recommended due to security concerns) or the password can be omitted (by just using the —password option with no value). In the password is omitted, mysql prompts you for the password before connecting to the database. For more details about password security, see MySQL’s End-User Guidelines for Password Security.
- Host ( —host=[] or -h [] ): The IP address or FQDN (fully qualified domain name) of the remote database server. You can omit this option from the command if you are connecting to a local MySQL instance on your same system.
- Port ( —port=[] or -P [] ): The port number of that the MySQL database instance uses. This can be omitted if your MySQL instance uses the default port of 3306 .
- SSL Settings ( —ssl-mode ): This controls if the connection should be encrypted. This can be set to DISABLED (unencrypted — not recommended), PREFERRED (tries an encrypted connection first before falling back to unencrypted), or REQUIRED (fails if an encrypted connection can’t be established. If omitted, this option is automatically set to PREFERRED . You can also set this to VERIFY_CA or VERIFY_IDENTITY to require an encrypted connection and either verify the CA certificate or both verify the CA certificate and the host name identity.
If you are frequently connecting to the same database, you can securely store many of these options (including the password). See the Securely Storing Credentials guide. Other options can be stored in an option file.
Configure the Database Server to Allow Remote Connections
If you have installed the MySQL server yourself (not through a managed service) and wish to connect to a database remotely without first logging in to the database server through SSH, you may need to modify a few settings. This can be useful if you want to limit SSH access but still permit database access.
Refer to our Create an SSH Tunnel for MySQL Remote Access to learn how to connect to your database using an SSH tunnel.
- Make sure your database has a user set up to allow connections from your local machine’s IP address. The example below displays a series of commands to create a new MySQL/MariaDB user named example_user . The user accepts connections from 192.0.2.0 and has SELECT , INSERT , UPDATE , and DELETE permissions on the example_db database:
CREATE user 'example_user'@'192.0.2.0' IDENTIFIED BY 'password'; GRANT SELECT,INSERT,UPDATE,DELETE ON example-db.* TO 'example_user' IDENTIFIED BY 'password'; . Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf . How to connect and create a database in MySQL?
After you’ve installed MySQL, you need to set mysql root password. To do so:
- Enter the next command in a terminal: mysql -u root
- Now it should open the mysql console. And type the following line: SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘yourpassword’);
To exit from the mysql console enter exit .
Now you should create the database with the root user. To do so:
- Open mysql from terminal: mysql -u root -p
- Enter the password created before.
- Enter the following line: CREATE DATABASE yourdatabasename;
If you enter SHOW DATABASES; you should see it in the list. If so, you have a database ready to use!
mysql -u root gives me ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)
That error means that you already have a root user and it has a password. If you forgot the root password, read the MySQL documentation.
You need to connect to MySQL using the root user and associated password. If you need to set them, use the following command: sudo mysqladmin -u root -h localhost password ‘mypassword’
From there, you can configure additional accounts by following this document: How to Create a New User and Grant Permissions in MySQL
I copied the sudo mysqladmin -u root -h localhost password ‘mypassword’ and pasted it into my terminal and I get the message Access denied for user ‘root’@’localhost’ (using password: NO) .
@Bert This code will not work. The password ‘mypassword’ is wrong, it should be -password ‘mypassword’
I executed sudo mysqladmin -u root -h localhost -password ‘mypassword’ , but I get Access denied for user ‘root’@’localhost’ (using password: YES) .
This is strange because since 12.04 (guessing you’re running Kubuntu 12.04), MySQL has been default. Looks like you’re missing a few steps in between, so let’s look over this:
First, as you mentioned, let’s do an installation,
sudo apt-get install mysql-server After you installed it, let’s try a little test,
sudo netstat -tap | grep mysql When you run this, you should see this reply,
tcp 0 0 localhost:mysql *:* LISTEN 2556/mysqld If this is not running correctly, run this restart command,
sudo service mysql restart Now to configure the server.
Let’s go to /etc/mysql/my.cnf to configure the basic settings. This includes the Log File, Port Number, Etc. For example, to configure MySQL to listen for connections from network hosts, change the bind-address directive to the server’s IP address:
After this, restart the MySQL daemon,
sudo service mysql restart If you want to change the MySQL root password, run this:
sudo dpkg-reconfigure mysql-server-5.5 The daemon will be stopped and you’ll be prompted for a new password.
Once you’re done, you should be configured and a few google searches will teach you how to create a database
sudo dpkg-reconfigure mysql-server-5.5 (5.5 is the version number, adapt if you have a different version)
This will allow you to set your MySQL root password, that you can then use with mysqladmin and mysql commands.
After you’ve installed MySQL, you need to set mysql root password. To do so:
Enter the next command in a terminal: mysql -u root -p Enter Password: (Enter your password here). I know this is 3 years old but I just had this problem as well and wanted to post my answer in case anyone else does the same search I did.
mysqladmin: connect to server at ‘localhost’ failed
This indicates that your user doesn’t have permission to connect to the database itself, before it even gets to logging in; not the database user, your actual linux user. Run everything as super user: sudo mysql -u root -p
Only root (the linux user, or a user with sudo privileges) can log in to mysql as root (the database user). I wasn’t able to find anywhere documenting this but experimentation proved it was the case. You can create a new user with all the same rights as the root user and that will not be restricted.
sudo mysql -u root SET PASSWORD FOR 'root'@'localhost' = PASSWORD('yourpassword'); CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost'; FLUSH PRIVILEGES; Now «newuser» (or whatever name you choose) can be logged-in-as without the need for sudo and you can use it for all your database administrative needs.