Log in to mysql linux

How to connect and create a database in MySQL?

After you’ve installed MySQL, you need to set mysql root password. To do so:

1. Enter the next command in a terminal: mysql -u root
2. 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:

1. Open mysql from terminal: mysql -u root -p
2. Enter the password created before.
3. 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.

Источник

How to Install MySQL on Ubuntu 20.04

MySQL is one of the most popular and widely used open-source relational database management systems.

It’s a fast, simple, and scalable SQL-based system, and it represents an integral component of the LAMP stack that runs much of the Internet.

In this tutorial, you will learn how to install MySQL on Ubuntu 20.04.

• A system running Ubuntu 20.04
• A user account with sudo privileges
• Access to a terminal (Ctrl + Alt + T)
• Network connection

Install MySQL on Ubuntu 20.04

Hosting MySQL databases on Ubuntu 20.04 requires installing the MySQL Server package. You can also access the database from remote clients using the MySQL Client.

Follow these simple steps to install MySQL Server on Ubuntu 20.04.

Note: If you haven’t upgraded your system from 18.04 yet, take a look at our tutorial for installing MySQL on Ubuntu 18.04.

Step 1: Update/Upgrade Package Repository

Update the system package repository to ensure you are installing the latest MySQL release.

1. Open the terminal and run the following command:

2. Enter your password and wait for the update to finish.

4. Enter Y when prompted to continue with the upgrade and hit ENTER. Wait for the upgrade to finish.

Step 2: Install MySQL

1. After successfully updating the package repository, install MySQL Server by running the following command:

sudo apt install mysql-server

2. When asked if you want to continue with the installation, answer Y and hit ENTER.

The system downloads MySQL packages and installs them on your machine.

Note: If you only want to connect to a remote MySQL server instead of hosting a database on your machine, install only the MySQL Client by running:

sudo apt install mysql-client

3. Check if MySQL was successfully installed by running:

The output shows which version of MySQL is installed on the machine.

Note: If you are using Windows, learn how to install and configure MySQL on a Windows Server.

Step 3: Securing MySQL

The MySQL instance on your machine is insecure immediately after installation.

1. Secure your MySQL user account with password authentication by running the included security script:

sudo mysql_secure_installation

2. Enter your password and answer Y when asked if you want to continue setting up the VALIDATE PASSWORD component. The component checks to see if the new password is strong enough.

3. Choose one of the three levels of password validation:

• 0 — Low. A password containing at least 8 characters.
• 1 — Medium. A password containing at least 8 characters, including numeric, mixed case characters, and special characters.
• 2 — Strong. A password containing at least 8 characters, including numeric, mixed case characters, and special characters, and compares the password to a dictionary file.

Enter 0 , 1 , or 2 depending on the password strength you want to set. The script then instructs you to enter your password and re-enter it afterward to confirm.

Any subsequent MySQL user passwords need to match your selected password strength.

Note: Even though you are setting a password for the root user, this user does not require password authentication when logging in.

The program estimates the strength of your password and requires confirmation to continue.

4. Press Y if you are happy with the password or any other key if you want a different one.

5. The script then prompts for the following security features:

• Remove anonymous users?
• Disallow root login remotely?
• Remove test database and access to it?
• Reload privilege tables now?

The recommended answer to all these questions is Y . However, if you want a different setting for any reason, enter any other key.

For example, if you need the remote login option for your server, enter any key other than Y for that prompt.

Note: Check out our tutorial if you want to create a new MySQL user and grant privileges.

Step 4: Check if MySQL Service Is Running

Upon successfully installing MySQL, the MySQL service starts automatically.

Verify that the MySQL server is running by running:

sudo systemctl status mysql

The output should show that the service is operational and running:

Step 5: Log in to MySQL Server

Finally, to log in to the MySQL interface, run the following command:

Now you can execute queries, create databases, and test out your new MySQL setup. Take a look at our MySQL Commands Cheat Sheet for some important MySQL commands to know.

Note: You my find helpful our article on how to resolve an error ‘Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock (2)’ that can appear when logging into the MySQL interface.

You should now have a fully functioning MySQL server installed on your machine. Start exploring the vast possibilities of MySQL and test the various functions it has to offer.

Become an advanced MySQL user by reading our tutorials for MySQL Events, MySQL JOINS, MySQL Stored Procedures, and MySQL Triggers.

Источник

How to install and set up MySQL Database on Ubuntu 22.04

MySQL is the fastest, free, open-source relational database that manages, preserves, and manipulates data using SQL queries. Using stand-alone clients for MySQL, users can communicate with the MySQL database directly with the help of the Structured Query language. MySQL Cluster support also enables you to overcome the database issues of the newest web, cloud, and communications applications.

In this writeup, we will provide the method to install, set up, and use MySQL on Ubuntu 22.04.

Installing MySQL on Ubuntu 22.04

To install MySQL on Ubuntu 22.04, follow up on the below-mentioned steps.

Step 1: Update system packages

First update the system package. To do so, open the terminal using “CTRL+ALT+T” and run the given command:

Move on to the next step for MySQL installation.

Step 2: MySQL server installation

In the next step, start MySQL installation on Ubuntu 22.04 using the provided command:

$ sudo apt install mysql-server -y

Step 3: Activate MySQL service

Activate the MySQL services on Ubuntu 22.04:

Let’s move ahead and set up the MySQL database on Ubuntu 22.04.

Setting up MySQL on Ubuntu 22.04

To set up the MySQL database on Ubuntu 22.04, follow up on the below-mentioned steps.

Step 1: MySQL basic configuration

To configure MySQL in a secure environment, write out the given “mysql_secure_installation” command and follow the highlighted steps to answer the asked information:

$ sudo mysql_secure_installation

Then, set the following options:

• Password for root user
• Removing anonymous users
• Logging in remotely
• Test database and its access
• Reloading privileges

Step 2: Check MySQL service status

Check the MySQL service status:

$ systemctl status mysql.service

As you can see, we have successfully installed and set up the MySQL database on Ubuntu 22.04.

Using MySQL on Ubuntu 22.04

On Ubuntu 22.04, to use MySQL, follow the below-listed steps.

Step 1: Log in to MySQL

First execute the below-provided command to log in to the “root” user and provide that password you have set during MySQL configuration:

Let’s move ahead toward the next step.

Step 2: Set new password

Change the “root” password to a new password using the “ALTER USER” command. For instance, we have set “[email protected]!”:

Step 3: Save MySQL Database settings

Run “FLUSH PRIVILEGES;” command to save new settings and “QUIT;” to exit MySQL terminal:

Let’s move ahead toward the uninstallation of the MySQL database.

Removing MySQL from Ubuntu 22.04

To remove the MySQL database from Ubuntu 22.04, first stop the MySQL services:

$ sudo systemctl stop mysql

After stopping MySQL services, type the below-provided command:

$ sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-* mysql-client-core-*

In order to completely remove the MySQL directory, utilize the following command:

$ sudo rm -rf /etc/mysql /var/lib/mysql

We have compiled all the necessary steps to install, use, and remove MySQL on Ubuntu 22.04.

Conclusion

On Ubuntu 22.04, to install the MySQL database, first update the system packages. Then, run the “$ sudo apt install mysql-server -y” command for installing the MySQL server. For the basic configuration of the MySQL database, execute the MySQL security script and make a secure MySQL database. After doing so, create users and tables, and set their privileges. In this write-up, we have effectively elaborated on how to install, set up, use, and remove the MySQL database on Ubuntu 22.04.

TUTORIALS ON LINUX, PROGRAMMING & TECHNOLOGY

Источник