How to Install MySQL 8.0 in Ubuntu 18.04

MySQL community server is a free open source, popular and cross-platform database management system. It supports both SQL and NoSQL, and has a pluggable storage engine architecture. Additionally, it also comes with multiple database connectors for different programming languages, allowing you to develop applications using any of the well known languages, and many other features.

It has many use cases under document storage, cloud, high availability systems, IoT (Internet of Things), hadoop, big data, data warehousing, LAMP or LEMP stack for supporting high-volume website/apps and much more.

In this article, we will explain a fresh installation of MySQL 8.0 database system on Ubuntu 18.04 Bionic Beaver. Before we move onto the actual installation steps, let’s look at a summary of:

What’s New in MySQL 8.0

  • The database now incorporates a transactional data dictionary.
  • Comes with Atomic DDL statement support.
  • Enhanced security and account management.
  • Improvements to resource management.
  • Several InnoDB enhancements.
  • New type of backup lock.
  • Default character set has changed to utf8mb4 from latin1.
  • A couple of JSON enhancements.
  • Comes with regular expression support using International Components for Unicode (ICU).
  • New error logging which now uses the MySQL component architecture.
  • Enhancements to MySQL replication.
  • Supports common table expressions(both non-recursive and recursive).
  • Has an enhanced optimizer.
  • Additional window functions and more.

Step 1: Add MySQL Apt Repository

Luckily, there is an APT repository for installing the MySQL server, client, and other components. You need to add this MySQL repository to your system’s package sources list; start by downloading the repository package using the wget tool from the command line.

$ wget -c https://dev.mysql.com/get/mysql-apt-config_0.8.10-1_all.deb 


Then install the MySQL repository package using the following dpkg command.

$ sudo dpkg -i mysql-apt-config_0.8.10-1_all.deb 

Note that in the package installation process, you will be prompted to choose MySQL server version and other components such as cluster, shared client libraries, or the MySQL workbench that you want to configure for installation.

MySQL server version mysql-8.0 will be auto-selected, then scroll down to the last option Ok and click [Enter] to finish the configuration and installation of the release package, as shown in the screenshot.

Configure MySQL APT Config

Configure MySQL APT Config

Step 2: Install MySQL Server in Ubuntu 18.04

Next, download the latest package information from all configured repositories, including the recently added MySQL repository.

$ sudo apt update

Then run the following command to install packages for the MySQL community server, client and the database common files.

$ sudo apt-get install mysql-server
Install MySQL 8.0 in Ubuntu 18.04

Install MySQL 8.0 in Ubuntu 18.04

Through the installation process, you will be asked to enter a password for the root user for your MySQL server, re-enter the password to confirm it and press [Enter].

Set MySQL Root Password

Set MySQL Root Password

Next, the MySQL server authentication plugin configuration message will appear, read through it and use the right arrow to choose Ok and press [Enter] to continue.

MySQL Authentication Configuration

MySQL Authentication Configuration

Afterwards, you will be asked to select the default authentication plugin to use, then use the right arrow to choose Ok and press [Enter] to complete the package configuration.

Select MySQL Authentication Plugin

Select MySQL Authentication Plugin

Step 3: Secure MySQL Server Installation

By default, the MySQL installation is unsecure. To secure it, run the security script which comes with the binary package. You will be asked to enter the root password you set during the installation process. Then also choose whether to use the VALIDATE PASSWORD plugin or not.

You can also change the root password you set before (as we have done in this example). Then enter yes/y to the following security questions:

  • Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
  • Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
  • Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
  • Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y

Launch the script by issuing the following command.

$ sudo mysql_secure_installation
Secure MySQL Server Installation

Secure MySQL Server Installation

To further secure your MySQL server, read our article 12 MySQL/MariaDB Security Best Practices for Linux.

Step 4: Managing MySQL Server via Systemd

On Ubuntu, after installing a package, it’s service(s) are usually started automatically once the package is configured. You can check if the MySQL server is up and running using following command.

$ sudo systemctl status mysql
Check MySQL Server Status

Check MySQL Server Status

If for one reason or the other, it isn’t auto-started, use the commands below to start and enable it to start at system boot time, as follows.

$ sudo systemctl status mysql
$ sudo systemctl enable mysql

Step 5: Install Extra MySQL Products and Components

In addition, you can install extra MySQL components that you feel you need in order to work with the server, such as mysql-workbench-community, libmysqlclient18 and many others.

$ sudo apt-get update
$ sudo apt-get install mysql-workbench-community libmysqlclient18

Finally, to access the MySQL shell, issue the following command.

$ sudo mysql -u root -p
Connect to MySQL Server

Connect to MySQL Server

For more information, read the MySQL 8.0 Release Notes.

That’s It! In this article, we have explained how to install MySQL 8.0 in Ubuntu 18.04 Bioni Beaver. If you have any questions or thoughts to share, use the comment form below to reach us.

Mytop – A Useful Tool for Monitoring MySQL/MariaDB Performance in Linux

Mytop is an open source and free monitoring program for MySQL and MariaDB databases was written by Jeremy Zawodny using Perl language. It is much similar in look and feel of the most famous Linux system monitoring tool called top.

Mytop program provides a command-line shell interface to monitor real time MySQL/MariaDB threads, queries per second, process list and performance of databases and gives a idea for the database administrator to better optimize the server to handle heavy load.

By default Mytop tool is included in the Fedora and Debian/Ubuntu repositories, so you just have to install it using your default package manager.

If you are using RHEL/CentOS distributions, then you need to enable third party EPEL repository to install it.


For other Linux distributions you can get mytop source package and compile it from source as shown.

# tar -zxvf mytop-<version>.tar.gz
# cd mytop-
# perl Makefile.PL
# make
# make test
# make install

In this MySQL monitoring tutorial, we will show you how to install, configure and use mytop on various Linux distributions.

Please note you must have running MySQL/MariaDB Server on the system to install and use Mytop.

Install Mytop in Linux Systems

To install Mytop, run the appropriate command below for your Linux distribution to install it.

$ sudo apt install mytop #Debian/Ubuntu
# yum install mytop #RHEL/CentOS
# dnf install mytop #Fedora 22+
# pacman -S mytop #Arch Linux # zypper in mytop #openSUSE
Sample Output :
Loaded plugins: changelog, fastestmirror
Loading mirror speeds from cached hostfile
* base: mirrors.linode.com
* epel: mirror.freethought-internet.co.uk
* extras: mirrors.linode.com
* updates: mirrors.linode.com
Resolving Dependencies
--> Running transaction check
---> Package mytop.noarch 0:1.7-10.b737f60.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
==============================================================================================================================================================================
Package Arch Version Repository Size
==============================================================================================================================================================================
Installing:
mytop noarch 1.7-10.b737f60.el7 epel 33 k
Transaction Summary
==============================================================================================================================================================================
Install 1 Package
Total download size: 33 k
Installed size: 68 k
Is this ok [y/d/N]: y

How to use Mytop to Monitor MySQL/MariaDB

Mytop needs MySQL/MariaDB login credentials to monitor databases and connects to the server with the root username by default. You can specify the necessary options for connecting to the database server on the command-line as you run it or in the file ~/.mytop (for convenience as explained later on).

Just run the following command to start the mytop and provide your MySQL/MariaDB root user password, when prompted. This will connect to the test database by default.

# mytop --prompt
Password:

Once you entered the MySQL root password you will see Mytop monitoring shell, similar to below.

MySQL Database Monitoring

MySQL Database Monitoring

If you would like to monitor specific database, then use the -d option as shown below. For example the below command will monitor database tecmint.

# mytop --prompt -d tecmint
Password:
Monitor MySQL Database

Monitor MySQL Database

If each of your databases has a specific admin (for example tecmint database admin), then connect using the database username and password like so.

# mytop -u tecmint -p password_here -d tecmintdb

However, this has certain security implications since the user’s password is typed on the command-line and can be stored in the shell command history file. This file can be viewed later on by an unauthorized person who might land on the username and password.

To avoid the risk of such a scenario, use the ~/.mytop config file to specify options for connecting to the database. Another advantage of this method is that you also do away with typing numerous command-line arguments each time you want to run mytop.

# vi ~/.mytop

Then add the necessary options below in it.

user=root
pass=password_here
host=localhost
db=test
delay=4
port=3306
socket=

Save and close the file. Then run mytop without any command-line arguments.

# mytop

It has a capability to show large amount of information on the screen and has many keyboard shortcut options too, check out “man mytop” for more information.

# man mytop

Read Also :

  1. Mtop (MySQL Database Monitoring) in RHEL/CentOS/Fedora
  2. Innotop to Monitor MySQL Performance

In this article, we have explained how to install, configure and use mytop in Linux. If you have any questions, use the feedback form below to reach us.

How to Change Default MySQL/MariaDB Port in Linux

In this guide we’ll learn how to change the default port that MySQL/MariaDB database binds in CentOS 7 and Debian-based Linux distributions. The default port that MySQL database server is running under Linux and Unix is 3306/TCP.

In order to change the default MySQL/MariaDB database port in Linux, open MySQL server configuration file for editing by issuing the below command.

# vi /etc/my.cnf.d/server.cnf [On CentOS/RHEL]
# vi /etc/mysql/mariadb.conf.d/50-server.cnf [On Debian/Ubuntu] 

Search for the line stat starts with [mysqld] and place the following port directive under [mysqld] statement, as shown in the below file excerpts. Replace the port variable accordingly.

[mysqld] port = 12345
Change MySQL Port on CentOS and Ubuntu

Change MySQL Port on CentOS and Ubuntu

After you’ve added the new MySQL/MariaDB port, save and close the configuration file and install the following package under CentOS 7 in order to apply the required SELinux rules to allow the database to bind on the new port.

# yum install policycoreutils-python


Next, add the below SELinux rule to bind MySQL socket on the new port and restart the database daemon to apply changes, by issuing the following commands. Again, replace MySQL port variable to match your own port number.

--------------- On CentOS/RHEL --------------- # semanage port -a -t mysqld_port_t -p tcp 12345
# systemctl restart mariadb
--------------- On Debian/Ubuntu ---------------
# systemctl restart mysql [On Debian/Ubuntu] 

In order to verify if the port configuration for MySQL/MariaDB database server has been successfully applied, issue netstat or ss command and filter the results via grep command in order to easily identify the new MySQL port.

# ss -tlpn | grep mysql
# netstat -tlpn | grep mysql
Verify MySQL Port

Verify MySQL Port

You can also display the new MySQL port by logging in to MySQL database with root account and issue the below command. However, be aware that all connections to MySQL on localhost are made via MySQL unix domain socket, not via the TCP socket. But the TCP port number must be explicitly specified in case of command line remote connections to MySQL database using the -P flag.

# mysql -h localhost -u root -p -P 12345
MariaDB [(none)]> show variables like 'port';
Check MySQL Port Variable

Check MySQL Port Variable

In case of remote connection to MySQL database, the root user must be explicitly configured to allow incoming connections form all networks or just an IP address, by issuing the below command in MySQL console:

# mysql -u root -p
MariaDB [(none)]> grant all privileges on *.* to 'root'@'192.168.1.159' identified by 'strongpass';
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> exit

Remotely log in to MySQL server via a command line client on the new port by issuing the below command.

# mysql -h 192.168.1.159 -P 12345 -u root -p 
Remote Login to MySQL on Port

Remote Login to MySQL on Port

Finally, once you’ve changed MySQL/MariaDB database server port, you need to update your distribution Firewall rules to allow incoming connections to the new TCP port so that remote clients can successfully connect to the database.

How to Check MySQL Database Size in Linux

‘,
enableHover: false,
enableTracking: true,
buttons: { twitter: {via: ‘tecmint’}},
click: function(api, options){
api.simulateClick();
api.openPopup(‘twitter’);
}
});
jQuery(‘#facebook’).sharrre({
share: {
facebook: true
},
template: ‘{total}’,
enableHover: false,
enableTracking: true,
click: function(api, options){
api.simulateClick();
api.openPopup(‘facebook’);
}
});
jQuery(‘#googleplus’).sharrre({
share: {
googlePlus: true
},
template: ‘{total}’,
enableHover: false,
enableTracking: true,
urlCurl: ‘https://www.tecmint.com/wp-content/themes/tecmint/js/sharrre.php’,
click: function(api, options){
api.simulateClick();
api.openPopup(‘googlePlus’);
}
});
jQuery(‘#linkedin’).sharrre({
share: {
linkedin: true
},
template: ‘{total}’,
enableHover: false,
enableTracking: true,
buttons: {
linkedin: {
description: ‘How to Check MySQL Database Size in Linux’,media: ‘https://www.tecmint.com/wp-content/uploads/2017/12/Check-MySQL-Database-Size-in-Linux.png’ }
},
click: function(api, options){
api.simulateClick();
api.openPopup(‘linkedin’);
}
});
// Scrollable sharrre bar, contributed by Erik Frye. Awesome!
var shareContainer = jQuery(“.sharrre-container”),
header = jQuery(‘#header’),
postEntry = jQuery(‘.entry’),
$window = jQuery(window),
distanceFromTop = 20,
startSharePosition = shareContainer.offset(),
contentBottom = postEntry.offset().top + postEntry.outerHeight(),
topOfTemplate = header.offset().top;
getTopSpacing();
shareScroll = function(){
if($window.width() > 719){ var scrollTop = $window.scrollTop() + topOfTemplate,
stopLocation = contentBottom – (shareContainer.outerHeight() + topSpacing);
if(scrollTop > stopLocation){
shareContainer.offset({top: contentBottom – shareContainer.outerHeight(),left: startSharePosition.left});
}
else if(scrollTop >= postEntry.offset().top-topSpacing){
shareContainer.offset({top: scrollTop + topSpacing, left: startSharePosition.left});
}else if(scrollTop 1024)
topSpacing = distanceFromTop + jQuery(‘.nav-wrap’).outerHeight();
else
topSpacing = distanceFromTop;
}
});
]]>

How to Reset WordPress Admin Password via MySQL Command Prompt

Sometimes, a WordPress user, with one of the following capabilities, such as administrator, editor, author, contributor or subscriber, forgets its login credentials, especially the password.

WordPress password can be easily changed via “Lost password” WordPress login form. However, if the WordPress account has no way of accessing his email address, changing the password using this mechanism can be impossible. In such cases, the job of updating a WordPress account password can only be managed by a system administrator with full privileges to MySQL database daemon.

In this guide we will show you how to reset a WordPress account password via MySQL command line in Linux.

Before logging in to MySQL/MariaDB database service, first create a MD5 Hash version of the new password that will be assigned to the account, by issuing the below command.


Replace the “newpass” string used in this example with your own strong password. Copy the password MD5 hash to a file in order to later paste the hash to MySQL user password field.

# echo -n "newpass" | md5sum
Create MD5 WordPress Password

Create MD5 WordPress Password

After you’ve generated the new password MD5 hash, log in to MySQL database with root privileges and issue the below command in order to identify and select the WordPress database. In this case the WordPress database is named “wordpress”.

# mysql -u root -p
MariaDB [(none)]> show databases;
MariaDB [(none)]> use wordpress;
Connect and Select WordPress Database

Connect and Select WordPress Database

Next, execute the below command to identify the table responsible for storing WordPress user accounts. Usually the table that stores all user information is wp_users.

Query wp_users table to retrieve all users ID, login name and password and identify the username ID field of the account that needs the password changed.
The username ID value will be used to further update the password.

MariaDB [(none)]> show tables;
MariaDB [(none)]> SELECT ID, user_login, user_pass FROM wp_users;
List All WordPress Users in MySQL

List All WordPress Users in MySQL

After you’ve correctly identified the ID of the user that needs the password changed, issue the below command to update his password. Replace the user ID and password MD5 Hash accordingly.

In this case the user ID is 1 and the new password hash is: e6053eb8d35e02ae40beeeacef203c1a.

MariaDB [(none)]> UPDATE wp_users SET user_pass= "e6053eb8d35e02ae40beeeacef203c1a" WHERE ID = 1;
Reset WordPress Admin Password in MySQL

Reset WordPress Admin Password in MySQL

In case you don’t have an already MD5 hashed password, you can execute MySQL UPDATE command with the password written in plain text, as shown in the below example.

In this case we’ll use MySQL MD5() function to calculate the MD5 hash of the password string.

MariaDB [(none)]> UPDATE wp_users SET user_pass = MD5('the_new_password') WHERE ID=1;

After the password has been updated, query wp_users table with the ID of the user that you’ve changed the password in order to retrieve this user database information.

MariaDB [(none)]> SELECT ID, user_login, user_pass FROM wp_users WHERE ID = 1;

That’s all! Now, inform the user that his password has been updated and it should be able to log in to WordPress with the new password.

12 MySQL/MariaDB Security Best Practices for Linux

MySQL is the world’s most popular open source database system and MariaDB (a fork of MySQL) is the world’s fastest growing open source database system. After installing MySQL server, it is insecure in it’s default configuration, and securing it is one of the essential tasks in general database management.

Read Also: Learn MySQL/MariaDB for Beginners – Part 1

This will contribute to hardening and boosting of overall Linux server security, as attackers always scan vulnerabilities in any part of a system, and databases have in the past been key target areas. A common example is the brute-forcing of the root password for the MySQL database.

In this guide, we will explain useful MySQL/MariaDB security best practice for Linux.

1. Secure MySQL Installation


This is the first recommended step after installing MySQL server, towards securing the database server. This script facilitates in improving the security of your MySQL server by asking you to:

  • set a password for the root account, if you didn’t set it during installation.
  • disable remote root user login by removing root accounts that are accessible from outside the local host.
  • remove anonymous-user accounts and test database which by default can be accessed by all users, even anonymous users.
# mysql_secure_installation

After running it, set the root password and answer the series of questions by entering [Yes/Y] and press [Enter].

Secure MySQL Installation

Secure MySQL Installation

2. Bind Database Server To Loopback Address

This configuration will restrict access from remote machines, it tells the MySQL server to only accept connections from within the localhost. You can set it in main configuration file.

# vi /etc/my.cnf [RHEL/CentOS] # vi /etc/mysql/my.conf [Debian/Ubuntu] OR
# vi /etc/mysql/mysql.conf.d/mysqld.cnf [Debian/Ubuntu] 

Add the following line below under [mysqld] section.

bind-address = 127.0.0.1

3. Disable LOCAL INFILE in MySQL

As part of security hardening, you need to disable local_infile to prevent access to the underlying filesystem from within MySQL using the following directive under [mysqld] section.

local-infile=0

4. Change MYSQL Default Port

The Port variable sets the MySQL port number that will be used to listen on TCP/ IP connections. The default port number is 3306 but you can change it under the [mysqld] section as shown.

Port=5000

5. Enable MySQL Logging

Logs are one of the best ways to understand what happens on a server, in case of any attacks, you can easily see any intrusion-related activities from log files. You can enable MySQL logging by adding the following variable under the [mysqld] section.

log=/var/log/mysql.log

6. Set Appropriate Permission on MySQL Files

Ensure that you have appropriate permissions set for all mysql server files and data directories. The /etc/my.conf file should only be writeable to root. This blocks other users from changing database server configurations.

# chmod 644 /etc/my.cnf

7. Delete MySQL Shell History

All commands you execute on MySQL shell are stored by the mysql client in a history file: ~/.mysql_history. This can be dangerous, because for any user accounts that you will create, all usernames and passwords typed on the shell will recorded in the history file.

# cat /dev/null > ~/.mysql_history

8. Don’t Run MySQL Commands from Commandline

As you already know, all commands you type on the terminal are stored in a history file, depending on the shell you are using (for example ~/.bash_history for bash). An attacker who manages to gain access to this history file can easily see any passwords recorded there.

It is strongly not recommended to type passwords on the command line, something like this:

# mysql -u root -ppassword_
Connect MySQL with Password

Connect MySQL with Password

When you check the last section of the command history file, you will see the password typed above.

# history 
Check Command History

Check Command History

The appropriate way to connect MySQL is.

# mysql -u root -p
Enter password:

9. Define Application-Specific Database Users

For each application running on the server, only give access to a user who is in charge of a database for a given application. For example, if you have a wordpress site, create a specific user for the wordpress site database as follows.

# mysql -u root -p
MariaDB [(none)]> CREATE DATABASE osclass_db;
MariaDB [(none)]> CREATE USER 'osclassdmin'@'localhost' IDENTIFIED BY '[email protected]%!2';
MariaDB [(none)]> GRANT ALL PRIVILEGES ON osclass_db.* TO 'osclassdmin'@'localhost';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit

and remember to always remove user accounts that are no longer managing any application database on the server.

10. Use Additional Security Plugins and Libraries

MySQL includes a number of security plugins for: authenticating attempts by clients to connect to mysql server, password-validation and securing storage for sensitive information, which are all available in the free version.

You can find more here: https://dev.mysql.com/doc/refman/5.7/en/security-plugins.html

11. Change MySQL Passwords Regularly

This is a common piece of information/application/system security advice. How often you do this will entirely depend on your internal security policy. However, it can prevent “snoopers” who might have been tracking your activity over an long period of time, from gaining access to your mysql server.

MariaDB [(none)]> USE mysql;
MariaDB [(none)]> UPDATE user SET password=PASSWORD('YourPasswordHere') WHERE User='root' AND Host = 'localhost';
MariaDB [(none)]> FLUSH PRIVILEGES;

12. Update MySQL Server Package Regularly

It is highly recommended to upgrade mysql/mariadb packages regularly to keep up with security updates and bug fixes, from the vendor’s repository. Normally packages in default operating system repositories are outdated.

# yum update
# apt update

After making any changes to the mysql/mariadb server, always restart the service.

# systemctl restart mariadb #RHEL/CentOS
# systemctl restart mysql #Debian/Ubuntu

Read Also: 15 Useful MySQL/MariaDB Performance Tuning and Optimization Tips

That’s all! We love to hear from you via the comment form below. Do share with us any MySQL/MariaDB security tips missing in the above list.

12 MySQL/MariaDB Security Best Practices for Linux

MySQL is the world’s most popular open source database system and MariaDB (a fork of MySQL) is the world’s fastest growing open source database system. After installing MySQL server, it is insecure in it’s default configuration, and securing it is one of the essential tasks in general database management.

This will contribute to hardening and boosting of overall Linux server security, as attackers always scan vulnerabilities in any part of a system, and databases have in the past been key target areas. A common example is the brute-forcing of the root password for the MySQL database.

In this guide, we will explain useful MySQL/MariaDB security best practice for Linux.

1. Secure MySQL Installation

This is the first recommended step after installing MySQL server, towards securing the database server. This script facilitates in improving the security of your MySQL server by asking you to:

  • set a password for the root account, if you didn’t set it during installation.
  • disable remote root user login by removing root accounts that are accessible from outside the local host.
  • remove anonymous-user accounts and test database which by default can be accessed by all users, even anonymous users.
# mysql_secure_installation

After running it, set the root password and answer the series of questions by entering [Yes/Y] and press [Enter].

Secure MySQL Installation

Secure MySQL Installation

2. Bind Database Server To Loopback Address

This configuration will restrict access from remote machines, it tells the MySQL server to only accept connections from within the localhost. You can set it in main configuration file.

# vi /etc/my.cnf	                   [RHEL/CentOS]	
# vi /etc/mysql/my.conf                    [Debian/Ubuntu] 
OR
# vi /etc/mysql/mysql.conf.d/mysqld.cnf    [Debian/Ubuntu] 

Add the following line below under [mysqld] section.

bind-address = 127.0.0.1

3. Disable LOCAL INFILE in MySQL

As part of security hardening, you need to disable local_infile to prevent access to the underlying filesystem from within MySQL using the following directive under [mysqld] section.

local-infile=0

4. Change MYSQL Default Port

The Port variable sets the MySQL port number that will be used to listen on TCP/ IP connections. The default port number is 3306 but you can change it under the [mysqld] section as shown.

Port=5000

5. Enable MySQL Logging

Logs are one of the best ways to understand what happens on a server, in case of any attacks, you can easily see any intrusion-related activities from log files. You can enable MySQL logging by adding the following variable under the [mysqld] section.

log=/var/log/mysql.log

6. Set Appropriate Permission on MySQL Files

Ensure that you have appropriate permissions set for all mysql server files and data directories. The /etc/my.conf file should only be writeable to root. This blocks other users from changing database server configurations.

# chmod 644 /etc/my.cnf

7. Delete MySQL Shell History

All commands you execute on MySQL shell are stored by the mysql client in a history file: ~/.mysql_history. This can be dangerous, because for any user accounts that you will create, all usernames and passwords typed on the shell will recorded in the history file.

# cat /dev/null > ~/.mysql_history

8. Don’t Run MySQL Commands from Commandline

As you already know, all commands you type on the terminal are stored in a history file, depending on the shell you are using (for example ~/.bash_history for bash). An attacker who manages to gain access to this history file can easily see any passwords recorded there.

It is strongly not recommended to type passwords on the command line, something like this:

# mysql -u root -ppassword_

Connect MySQL with Password

Connect MySQL with Password

When you check the last section of the command history file, you will see the password typed above.

# history 

Check Command History

Check Command History

The appropriate way to connect MySQL is.

# mysql -u root -p
Enter password:

9. Define Application-Specific Database Users

For each application running on the server, only give access to a user who is in charge of a database for a given application. For example, if you have a wordpress site, create a specific user for the wordpress site database as follows.

# mysql -u root -p
MariaDB [(none)]> CREATE DATABASE osclass_db;
MariaDB [(none)]> CREATE USER 'osclassdmin'@'localhost' IDENTIFIED BY 'osclass@dmin%!2';
MariaDB [(none)]> GRANT ALL PRIVILEGES ON osclass_db.* TO 'osclassdmin'@'localhost';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit

and remember to always remove user accounts that are no longer managing any application database on the server.

10. Use Additional Security Plugins and Libraries

MySQL includes a number of security plugins for: authenticating attempts by clients to connect to mysql server, password-validation and securing storage for sensitive information, which are all available in the free version.

You can find more here: https://dev.mysql.com/doc/refman/5.7/en/security-plugins.html

11. Change MySQL Passwords Regularly

This is a common piece of information/application/system security advice. How often you do this will entirely depend on your internal security policy. However, it can prevent “snoopers” who might have been tracking your activity over an long period of time, from gaining access to your mysql server.

MariaDB [(none)]> USE mysql;
MariaDB [(none)]> UPDATE user SET password=PASSWORD('YourPasswordHere') WHERE User='root' AND Host = 'localhost';
MariaDB [(none)]> FLUSH PRIVILEGES;

12. Update MySQL Server Package Regularly

It is highly recommended to upgrade mysql/mariadb packages regularly to keep up with security updates and bug fixes, from the vendor’s repository. Normally packages in default operating system repositories are outdated.

# yum update
# apt update

After making any changes to the mysql/mariadb server, always restart the service.

# systemctl restart mariadb		#RHEL/CentOS
# systemctl restart mysql		#Debian/Ubuntu

How to Test PHP MySQL Database Connection Using Script

‘,
enableHover: false,
enableTracking: true,
buttons: { twitter: {via: ‘tecmint’}},
click: function(api, options){
api.simulateClick();
api.openPopup(‘twitter’);
}
});
jQuery(‘#facebook’).sharrre({
share: {
facebook: true
},
template: ‘{total}’,
enableHover: false,
enableTracking: true,
click: function(api, options){
api.simulateClick();
api.openPopup(‘facebook’);
}
});
jQuery(‘#googleplus’).sharrre({
share: {
googlePlus: true
},
template: ‘{total}’,
enableHover: false,
enableTracking: true,
urlCurl: ‘https://www.tecmint.com/wp-content/themes/tecmint/js/sharrre.php’,
click: function(api, options){
api.simulateClick();
api.openPopup(‘googlePlus’);
}
});
jQuery(‘#linkedin’).sharrre({
share: {
linkedin: true
},
template: ‘{total}’,
enableHover: false,
enableTracking: true,
buttons: {
linkedin: {
description: ‘How to Test PHP MySQL Database Connection Using Script’,media: ‘https://www.tecmint.com/wp-content/uploads/2017/08/Test-PHP-MySQL-Database-Connection.png’ }
},
click: function(api, options){
api.simulateClick();
api.openPopup(‘linkedin’);
}
});
// Scrollable sharrre bar, contributed by Erik Frye. Awesome!
var shareContainer = jQuery(“.sharrre-container”),
header = jQuery(‘#header’),
postEntry = jQuery(‘.entry’),
$window = jQuery(window),
distanceFromTop = 20,
startSharePosition = shareContainer.offset(),
contentBottom = postEntry.offset().top + postEntry.outerHeight(),
topOfTemplate = header.offset().top;
getTopSpacing();
shareScroll = function(){
if($window.width() > 719){ var scrollTop = $window.scrollTop() + topOfTemplate,
stopLocation = contentBottom – (shareContainer.outerHeight() + topSpacing);
if(scrollTop > stopLocation){
shareContainer.offset({top: contentBottom – shareContainer.outerHeight(),left: startSharePosition.left});
}
else if(scrollTop >= postEntry.offset().top-topSpacing){
shareContainer.offset({top: scrollTop + topSpacing, left: startSharePosition.left});
}else if(scrollTop 1024)
topSpacing = distanceFromTop + jQuery(‘.nav-wrap’).outerHeight();
else
topSpacing = distanceFromTop;
}
});
]]>

cPanel Server : Restoring MySQL SimLink.

If you’re unable to restart mysqld and troubleshooting the cause: if u log into SSH and go to /tmp you may see that there is no simlink

To create a simlink you can do the following:

cd /var/lib/mysql/
touch mysql.sock
chmod 1777 mysql.sock
chown mysql.mysql mysql.sock

Create a symlink for the sock file :-
ln -s /var/lib/mysql/mysql.sock /tmp

Restart mysql :- service mysql restart

Then your MySQL service should be back to normal.

How To Set Up A Load-Balanced MySQL Cluster With MySQL 5.1

This tutorial is based on Falko Timme’s tutorial for MySQL Cluster 5.0. It shows how to configure a MySQL 5.1 cluster with five nodes: 1 x management, 2 x storage nodes and 2 x balancer nodes. This cluster is load-balanced by an Ultra Monkey package which provides heartbeat (for checking if the other node is still alive) and ldirectord (to split up the requests to the nodes of the MySQL cluster).
Continue reading How To Set Up A Load-Balanced MySQL Cluster With MySQL 5.1