MySQL Notes

Installation of MySQL (Oracle version)
https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/#apt-repo-fresh-install

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
/etc/mysql/mysql.conf.d/mysqld.cnf

comment out bind
#bind-address = 127.0.0.1

for TCP/IP connetion, create a user from IP host:
mysql>create user 'mji'@'192.168.2.2' 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

https://www.howtoforge.com/setting-changing-resetting-mysql-root-passwords

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
SELECT DATABASE() FROM DUAL;

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
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();