Installation of MySQL (Oracle version)

To install MySQL 5.7 on Ubuntu 16:

sudo apt install -y mysql-server mysql-client

After installation:
Secure the installation
sudo mysql_secure_installation

Unstall MySQL
#sudo apt-get remove --purge mysql (remove the package and its configuration file)
#sudo apt-get autoremove (remove associated files)

Open MySQL ports on all interfaces

comment out bind
#bind-address =

for TCP/IP connetion, create a user from IP host:
mysql>create user 'mji'@'' identified by 'dataclub';

Or from anywhere:
mysql>create user 'mji'@'%' identified by 'dataclub';

mysql>GRANT ALL ON *.* TO 'mji'@'%';

Create a user
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Grant database access
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

Reset Root Password
MySQL 5.7.6 and later:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

MySQL 5.7.5 and earlier:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

Recover 'root' password for mysql

1. Shutdown mysql
sudo service mysql stop

If "No log directory":
sudo mkdir /var/log/mysql
sudo chown mysql:mysql /var/log/mysql

if "No /var/run/mysqld"
mkdir -p /var/run/mysqld
chown mysql:mysql /var/run/mysqld

2. Restart mysql with no password
sudo mysqld_safe --skip-grant-tables &

3. connect to mysql: mysql -u root

use mysql

update user set password=PASSWORD('dataclub') where User='root';

or in mysql 5.7

update user set authentication_string=password('dataclub') where user='root'

Show all the users
SELECT host, user, password FROM mysql.user;

Change password
SET PASSWORD FOR istelleadmin@localhost = PASSWORD('dataclub');

Show database in use

Display users
select * from mysql.user;

Grant permission to a user
GRANT ALL ON dataclub.* to mji@localhost

Install phpMyAdmin
sudo apt-get update
sudo apt-get install phpmyadmin php-mbstring php-gettext

The only thing we need to do is explicitly enable the PHP mcrypt and mbstring extensions, which we can do by typing:
sudo phpenmod mcrypt
sudo phpenmod mbstring

Create a symbolic link in /var/www/html/

ln -s /usr/share/phpmyadmin/

Remove the link
rm -r phpmyadmin

Show permissions and privileges

Remove mysql on Ubuntu 20.04
sudo apt-get remove --purge mysql* -y
sudo apt-get autoremove -y
sudo apt-get autoclean

Reinstall mysql
sudo apt-get install mysql-server  (tested Ver. 8.0.28 for
Note: If the user databases are not deleted they will show up automatically in the newly installed mysql

Reset Root Password

make sure the directory exists:
mkdir -p /var/run/mysqld
chown mysql:mysql /var/run/mysqld

1. service mysql stop
2. mysqld_safe --skip-grant-tables &
3. mysql (login with no password)
5. ALTER USER 'root'@'localhost' IDENTIFIED BY 'dataclub';
6. kill -9 pid (of mysqld_safe) or killall mysqld_safe
7. service mysql start



MySQL Password Requirements
Check: SHOW VARIABLES LIKE 'validate_password%';
or you can set the password policy level lower, for example:

SET GLOBAL validate_password_length = 6;

Change Root Password (distrib: 5.7.28, ver 14.14)
1. edit /etc/mysql/my.cnf
2. add the following to start mysql with no password required:
3. login mysql:
4. use mysql;
5. update user set authentication_string=PASSWORD("") where User='root';
6. update user set plugin="mysql_native_password" where User='root'; # THIS LINE
7. flush privileges;
8. quit;
9. kill the mysql serviced (kill PID)
10. Restart mysql;
11. $mysql -u root -p

Limit login attempts:
SET GLOBAL validate_password_number_count = 0;

CentOS – Install mysql

1. Find the latest repository and Update yum repository

2. Update yum repository
yum localinstall

3. Install MySQL
yum localinstall mysql-community-server-8.0.15-1.el7.x86_64.rpm

4. Enable and start mysql
sudo systemctl enable mysqld
sudo systemctl start mysqld

5. Check status
systemctl status mysqld

6. Get Temporary password
grep 'temporary password' /var/log/mysqld.log

7. Alter password
ALTER USER 'root'@'localhost' identified by 'your new password';

8. More - Reset Password

Tutorial – After Installing MySQL on Ubutnu 18.04, Fix “Access denied for user ‘root’@’localhost'” Error


Default logon
default username: phpmyadmin
password: dataclub

Notes about MySQL Usage

On the first "use" command after login, MySQL scans database, tables and columns name for auto completion. If you have many db, tables it could take a while.

To avoid that, launch your client with the -A option
$mysql -uroot -p -A


Turn off MySQL Scan for auto completion /etc/mysql/my.cnf.

mysql 8 version connecting problem from WordPress setup
Cannot login with error connecting database. Fix it by useing thte native password:
mysql>ALTER USER 'mji'@'localhost' IDENTIFIED WITH mysql_native_password BY 'dataclub';

Open mysql port for remote access:

In mysql:
1. create user 'mji'@'192.168.2.%' identified by 'dataclub';
2. grant all on bnbshopdev.* to 'mji'@'192.168.2.%';

MySQL Notes