Install and Setup MySQL on Ubuntu

Install and Setup MySQL on Ubuntu

MySQL is an open-source relation database management system which is fairly easy to use and is the first choice for many to handle basic database requirement for their applications.

To setup MySQL on your ubuntu system follow the steps below.

• Install MySQL: To install MySQL update the ubuntu package index and install the package using Ubuntu package manager:
sudo apt-get update

  sudo apt-get install mysql-server

Setup Security: You will be promoted to setup root password during installation, set up a password you can remember as you are going to need it later. If you were not asked to set up your root password  or if you wish to change any of the security settings such as disallowing remote login, you can do so by running the command
sudo mysql_secure_installation utility

• Open Port: If you wish to connect to the MySQL database from another machine or from an application hosted on a different server, you need to open a port (default port used is 3306) on the MySQL server. You can do this using this
sudo ufw enable

  sudo ufw allow mysql

• Launch MySQL: Your installation is now complete. You can launch your MySQL service using the command
sudo systemctl start mysql

• Enable auto-launch at reboot: To ensure your MySQL is always up and running you can enable your MySQL service to automatically start after every reboot.
sudo systemctl enable mysql

• Restart MySQL Server: To restart your MySQL server at any given point you can run the command
sudo systemctl restart mysql

That's all with the installation of MySQL on the ubuntu server. Now to start the MySQL shell from the command line you can enter the command below and enter the password when asked, to log in as a root user.

/usr/bin/mysql -u root -p

Here is a list of few queries which might come handy at times.

• To list down all the users and the host
SELECT User, Host, authentication_string FROM mysql.user;

• Update the password for the root user in MySQL version 5.7 or above 
UPDATE mysql.user SET authentication_string = PASSWORD('password') WHERE User = 'root';

• Update the password for the root user in MySQL version below 5.7 
UPDATE mysql.user SET Password = PASSWORD('password') WHERE User = 'root';

• Reload the stored user information and to make the password changes take effect
FLUSH PRIVILEGES;

• To show all databases created so far.
SHOW DATABASES;

• To create a new database.
CREATE DATABASE testdb;

• Create a new user
INSERT INTO mysql.user (User,Host,authentication_string,ssl_cipher,x509_issuer,x509_subject) VALUES('testuser','localhost',PASSWORD('mypassword'),'','','');

  FLUSH PRIVILEGES;

• Grant access to new user created above.
GRANT ALL PRIVILEGES ON testdb.* to testuser@localhost;

  FLUSH PRIVILEGES;

• Grant all privileges to root user 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

  FLUSH PRIVILEGES;

• Change Host to '%' to allow access from any machine 
UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='testuser';

  FLUSH PRIVILEGES;