mysql – A little tech blog

I want to run some mysql command from my terminal, but each time I run mysql -u root to log in, I get an error:

-bash: mysql: command not found

This is because mysql isn’t in my PATH. Your path is an environment variable that holds directories that your computer will search through to find executable files. You can review what’s in your path by running:

# echo $PATH

This will show you the list of directories that are in your path. This variable is stored in your .bash_profile. On most macs, the .bash_profile file is located in the root of your home directory. To view the current .bash_profile file, go to your home directory

# ~

From there, run the ls command to view all files.

# ls -alh

Near the top of the listing is the .bash_profile file. Cat out the contents to see what’s in your path.

# cat .bash_profile

To update the file, backup your current .bash_profile

# cp .bash_profile .bash_profile_backup

To locate your mysql executable, use the locate command.

# locate mysql | less

Since I’m running MAMP, my mysql executable is located in /Applications/MAMP/Library/bin/mysql To add this to the end my path, run the following command.

# echo 'export
PATH=$PATH:/Applications/MAMP/Library/bin' >> ~/.bash_profile

This takes the output of the echo command and puts it into your .bash_profile. Now, you make it persist by sourcing it. This alerts the current terminal to reload the file.

# source ~/.bash_profile

Now you can echo path again to confirm or cat out your file

# echo $PATH

Now, we can use the mysql command from our current location. Enter the password, if prompted.

# mysql -u root -p

Now, you can run your commands with ease.

To quit the mysql cli, run:

# \q

My system is as follows:

Ubuntu 16.04.4 LTS
Mysql 5.7.22

Stop the mysql process:

sudo /etc/init.d/mysql stop

Use mysqld_safe using the –skip-grant-tables option to start the process:

sudo mysqld_safe --skip-grant-tables &

If you get an error about: mysqld_safe Directory ‘/var/run/mysqld’ for UNIX socket file doesn’t exist, check 2 things.

If you have a /var/run/mysqld directory, if not, make one and change the permissions with chown.
In the /etc/mysql/ directory, there is a file called, my.cnf open it and add the following:

sudo chown mysql:mysql /var/run/mysqld

[mysqld]
port=3306
socket=/var/run/mysqld/mysql.sock

Save and close this file and re-run sudo mysqld_safe –skip-grant-tables &

You should see a message that reads: mysqld_safe Started mysqld daemon with databases from /var/lib/mysql

Open another terminal and log into mysql

mysql -u root

Now you can reset your password. Run these commands:

mysql> use mysql;
mysql> UPDATE user SET password=PASSWORD('pass123') WHERE user='root';
mysql> FLUSH PRIVILEGES;
mysql> quit

If you get an error when running the UPDATE user command that reads: ERROR 1054 (42S22): Unknown column ‘password’ in ‘field list’, do this next.
Assuming this is for the mysql database:

mysql> use mysql;
mysql> show tables;

If you don’t see a row called password, but there is a user field, run this:

mysql> describe user;

There is a row called ‘authentication_string’ that holds the password.

You will need to run this command to reset the password. Just like the command above, but referencing the correct field in the user table.

mysql> use mysql;
mysql> UPDATE user SET authentication_string=PASSWORD('pass123') WHERE user='root';
FLUSH PRIVILEGES;
quit

Log back in and test your new mysql password

 mysql -u root -p

A little tech blog

Tag: mysql

Command not found when using MYSQL from the command line on osx

How to reset lost or forgotten MYSQL password