Coding

Some quick MySQL commands for database creation ( For WordPress & Rails )

Some systems like Ubuntu, mysql is using by default the UNIX auth_socket plugin.

Basically means that: db_users using it, will be “auth” by the system user credentias. You can see if your root user is set up like this by doing the following:

$ sudo mysql -u root # I had to use "sudo" since is new installation

mysql> USE mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;

+------------------+-----------------------+
| User             | plugin                |
+------------------+-----------------------+
| root             | auth_socket           |
| mysql.sys        | mysql_native_password |
| debian-sys-maint | mysql_native_password |
+------------------+-----------------------+

As you can see in the query, the root user is using the auth_socket plugin

There are 2 ways to solve this:

  1. You can set the root user to use the mysql_native_password plugin
  2. You can create a new db_user with you system_user (recommended)

Option 1:

$ sudo mysql -u root # I had to use "sudo" since is new installation

mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;

$ service mysql restart

Option 2: (replace YOUR_SYSTEM_USER with the username you have)

$ sudo mysql -u root # I had to use "sudo" since is new installation

mysql> USE mysql;
mysql> CREATE USER 'YOUR_SYSTEM_USER'@'localhost' IDENTIFIED BY '';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'YOUR_SYSTEM_USER'@'localhost';
mysql> UPDATE user SET plugin='auth_socket' WHERE User='YOUR_SYSTEM_USER';
mysql> FLUSH PRIVILEGES;
mysql> exit;

$ service mysql restart

Remember that if you use option #2 you’ll have to connect to mysql as your system username (mysql -u YOUR_SYSTEM_USER)

Note: On some systems (e.g., Debian stretch) ‘auth_socket’ plugin is called ‘unix_socket’, so the corresponding SQL command should be: UPDATE user SET plugin='unix_socket' WHERE User='YOUR_SYSTEM_USER';

Set / change / reset the MySQL root password on Ubuntu Linux. Enter the following lines in your terminal.

  1. Stop the MySQL Server: sudo /etc/init.d/mysql stop
  2. Start the mysqld configuration: sudo mysqld --skip-grant-tables &
  3. Login to MySQL as root: mysql -u root mysql
  4. Replace YOURNEWPASSWORD with your new password:UPDATE mysql.user SET Password = PASSWORD('YOURNEWPASSWORD') WHERE User = 'root'; FLUSH PRIVILEGES; exit;

Note: on some versions, if password column doesn’t exist, you may want to try:
UPDATE user SET authentication_string=password('YOURNEWPASSWORD') WHERE user='root';

Note: This method is not regarded as the most secure way of resetting the password, however, it works.


To get started, log into the MySQL root (administrative) account by issuing this command:

mysql -u root -p

You will be prompted for the password you set for the MySQL root account when you installed the software.

First, we can create a separate database that WordPress will control. You can call this whatever you would like, but we will be using wordpress in this guide to keep it simple. Create the database for WordPress by typing:

CREATE DATABASE wordpress DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Note: Every MySQL statement must end in a semi-colon (;). Check to make sure this is present if you are running into any issues.

Next, we are going to create a separate MySQL user account that we will use exclusively to operate on our new database. Creating one-function databases and accounts is a good idea from a management and security standpoint. We will use the name wordpressuser in this guide. Feel free to change this if you’d like.

We are going to create this account, set a password, and grant access to the database we created. We can do this by typing the following command. Remember to choose a strong password here for your database user:

GRANT ALL ON wordpress.* TO 'wordpressuser'@'localhost' IDENTIFIED BY 'password';

You now have a database and user account, each made specifically for WordPress. We need to flush the privileges so that the current instance of MySQL knows about the recent changes we’ve made:

FLUSH PRIVILEGES;

Exit out of MySQL by typing:

EXIT;

To create another new user:

Also to create another info :

Only the root user needs sudo requirement to login to mysql. I resolved this by creating a new user and granting access to the required databases:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost';

now newuser can login without sudo requirement:

mysql -u newuser -p

How to connect to MySQL without Sudo.

first login to your mysql with sudo.

then use this code to change “plugin” coloumn value from “unix_socket” to “mysql_native_password” for root user.

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'root' AND plugin = 'unix_socket';

FLUSH PRIVILEGES;

finally restart mysql service. that’s it.

if you want more info, check this link.

There is another way of obtaining the same thing as above by doing this :

Having done this I thought about how to access without having to do the sudo, which is just a matter of running these mysql queries

GRANT ALL PRIVILEGES on *.* to 'root'@'localhost' IDENTIFIED BY '<password>';
FLUSH PRIVILEGES;

Leave a Reply

Your email address will not be published. Required fields are marked *