This is Part I of a short two part series on learning MySQL. The second part can be seen <a here.
This README will go over the general commands of installing and uninstalling MySQL, creating users and granting privelages to them, seeing SSL related variables, etc. All in all, this readme is just a quick guide on how to utilize MySQL to fit the users needs.
</br>
</br>
</p>
shell> mysql or
shell> mysql --version
or
shell> mysql -V
Results of command when ran locally vs on a VPS,
mysql Ver 8.0.20-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
mysql Ver 14.14 Distrib 5.7.19, for Win64 (x86_64)
` sudo apt install mysql-server `
> sudo apt-get remove --purge *mysql\*
> sudo apt-get autoremove
> sudo apt-get autoclean
` mysql -u root -p -h localhost `
` winpty mysql -u root -p `
` mysql -u root -p -h127.0.0.1 –protocol=TCP `
` shell> mysql -u raza -p -h 168.235.86.169 `
` service mysql restart `
` mysql> SHOW GLOBAL VARIABLES LIKE ‘PORT’; `
` sudo netstat -plunt `
One line of the output draws interest:
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 5858/mysqld
` mysql> status ` or
` mysql> \s `
` Select * from mysql.user WHERE user=’root’\G `
See the difference between the statement above and this one: ` Select * from mysql.user WHERE user=’root’; `
General format:
SELECT * FROM sometable\G Source
` mysql> show databases; `
An alternative way is:
shell> mysqlshow
Source
` C:\wamp64\bin\mysql\mysql5.7.19\bin `
` shell> which mysqld `
` shell> ps -Af | grep mysqld `
</br>
</p>
SELECT user FROM mysql.user\G
SELECT user, host FROM mysql.user\G
SELECT USER(),CURRENT_USER();
DELETE FROM mysql.user WHERE user = ‘raza’;
UPDATE mysql.user SET host=’%’ WHERE user=’raza’;
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
GRANT ALL PRIVILEGES ON books.authors TO 'tolkien'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'tolkien'@'%';
If you have the following issue:
Access denied for user 'raza'@'localhost' (using password: NO)
Run:
grant all privileges on *.* to raza@localhost with grant option;
</br>
</p>
` mysql -h
` mysql> status ` or
` mysql> \s `
SHOW VARIABLES LIKE '%ssl%'
+--------------------+-----------------+
| Variable_name | Value |
+--------------------+-----------------+
| have_openssl | YES |
| have_ssl | YES |
| mysqlx_ssl_ca | |
| mysqlx_ssl_capath | |
| mysqlx_ssl_cert | |
| mysqlx_ssl_cipher | |
| mysqlx_ssl_crl | |
| mysqlx_ssl_crlpath | |
| mysqlx_ssl_key | |
| ssl_ca | ca.pem |
| ssl_capath | |
| ssl_cert | server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_fips_mode | OFF |
| ssl_key | server-key.pem |
+--------------------+-----------------+
GREAT SSL related links to learn more from.
DigitalOcean Source1, DigitalOcean Source2 and How to disable SSL Source3
</br>
</p>
This short section will go over how to connect to a remote MySQL service.
DISCLAIMER: Your local computer and remote server must have MySQL installed. Additionally, if your server is using MySQL 8.0+, make sure your local version is up to date!
When I initially tried to remote into a server via the command line. The server holds MySQL Ver 8.0.20 and my local computer MySQL Distrib was 5.7.19. Obviously, 5.7 does NOT match with version 8 and I got this error when I tried to connect with the command:
` shell> mysql -u raza -p -h 168.235.86.169 `
ERROR 2026 (HY000): SSL connection error: unknown error number
Now, to solve the problem I updated the local mysql server to version 8.
I followed this link and was able to connect to it. The commands I used were as follows:
wget https://dev.mysql.com/get/mysql-apt-config_0.8.10-1_all.deb
dpkg -i mysql-apt-config_0.8.10-1_all.deb
THEN
apt-get update
apt-get install mysql-server
Now, I figured this out by reading this Digital Ocean resource.
A user named Bobby commented:
Regarding the MySQL cli, as far as I know on OSX the default MySQL client is 5.7 and you can not use MySQL 5.7 client to connect toMySQL 8. I tested that and I’m getting the same SSL error as you do when using MySQL 5.7 client…
Let me know how it goes!
Regards,
Bobby
Now that we have matching MySQL versions, lets begin.
+——————+
Open the mysql config file.
$ nano /etc/mysql/my.cnf
OR
$ nano /etc/mysql/mysql.conf.d/mysqld.cnf
Comment out the bind address that only listens to localhost
#bind-address = 127.0.0.1
Go one line below where that line was found and add this:
bind-address = 0.0.0.0
Make sure that when you create the user, you specify % as the hostname, otherwise the user will only be able to connect from the localhost.
Create User accessible locally
` CREATE USER raza@localhost IDENTIFIED BY ‘pass’; `
Create User which can be remoted in
` CREATE USER raza@’%’ IDENTIFIED BY ‘pass’; `
` GRANT ALL PRIVILEGES ON . to raza@’%’ WITH GRANT OPTION; `
Following statement indicates that the account has no SSL or X.509 requirements. Source
grant all on *.* to raza@'%' REQUIRE NONE;
Reload the permissions ` FLUSH PRIVILEGES; `
Restart the service ` service mysql restart `
After that has been done, you should be able to have remote into the server.