Complete guide to Install, Update & Disable strict SQL mode in MySQL 5.7.

share on:
Disable strict SQL mode in mysql 5.7 in CentOS 7

MySQL is an open source and one of the best RDBMS that can be used to run multiple databases and provide multi-user access to each created database on any single server.

 

Steps to Install, Update & Disable strict SQL mode in MySQL 5.7 in CentOS 7.

This article can be used as a reference to install the latest version of MySQL (currently 5.7.16) on RHEL7, CentOS 7 and Fedora 23, 24 using official MySQL repository.

This article will also work fine on Oracle Linux and Scientific Linux. In this article, we will install MySQL 5.7.16 in a fresh install of CentOS 7 and Fedora 24.

If you have already installed MySQL then you should consider upgrading it to the latest version. Before upgrading your MySQL server, Take complete backups of your Databases.

We will also take a look at how to upgrade MySQL to latest version later in this article.

 

Step 1:  Add the official MySQL Repository.

Download the official MySQL Yum repository and install it in system’s repository list.

--------------- On RHEL/CentOS 7 ---------------
shell> wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
shell> yum localinstall mysql57-community-release-el7-8.noarch.rpm

 

--------------- On Fedora 23 ---------------
shell> dnf install https://dev.mysql.com/get/mysql57-community-release-fc23-8.noarch.rpm

 

--------------- On Fedora 24 ---------------
shell> dnf install https://dev.mysql.com/get/mysql57-community-release-fc24-8.noarch.rpm

 

The official MySQL Yum software repository also provides RPM packages for installing the latest version of MySQL server, client, MySQL Utilities, MySQL Workbench and other related software.

 

Step 2: Verify that the MySQL Yum repository has been added successfully.

Use these commands to check repo list.

For RHEL 7/CentOS 7

shell> yum repolist enabled | grep "mysql.*-community.*"

 

For Fedora 23 and later versions

shell> dnf repolist enabled | grep "mysql.*-community.*"

 

 

Step 3: Install the Latest version of MySQL.

Search for the packages and then install.

Disable strict SQL mode in mysql 5.7 in CentOS 7
Complete Guide To Install, Update & Disable Strict SQL Mode In MySQL 5.7.
shell> yum install mysql-community-server

 

For Fedora 23 and later versions use:

shell> dnf install mysql-community-server

 

 

Step 4: Installing MySQL Release Series

You can use MySQL community server’s sub-directories to install different versions of MySQL.

You can also install different MySQL version using different sub-repositories of MySQL Community Server. The sub-repository for the recent MySQL series (currently MySQL 5.7.16) is activated by default, and the sub-repositories for previous versions are deactivated by default.

To install a specific version from specific sub-repository, you can use these commands.

------------------ For RHEL 7/CentOS 7 Versions ------------------
shell> yum-config-manager --disable mysql57-community
shell> yum-config-manager --enable mysql56-community

 

------------------ Fedora 23+ Versions ------------------
shell> dnf config-manager --disable mysql57-community
shell> dnf config-manager --enable mysql56-community

 


Step 4: Start the newly installed MySQL Server

Now that we have installed MySQL successfully, we can enable it to start at the reboot.

shell> systemctl enable mysqld

 

Start the MySQL process.

shell> systemctl start mysqld

 

 

Step 5: Verify the status and version number of the MySQL server.

Use the following command to check the status of MySQL server.

[[email protected] ~]# systemctl status mysqld
 mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2016-11-24 15:53:19 UTC; 14s ago
  Process: 21156 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited,
  Process: 21100 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 21160 (mysqld)
    Tasks: 27 (limit: 512)
   CGroup: /system.slice/mysqld.service
           └─21160 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Nov 24 15:53:15 DebYum-Fedora24 systemd[1]: Starting MySQL Server...
Nov 24 15:53:19 DebYum-Fedora24 systemd[1]: Started MySQL Server.

 

Check Mysql Version number.

[[email protected] ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using  EditLine wrapper

 

 

Step 6: Securing the MySQL Installation.

First copy the Temporary Password from /var/log/mysqld.log. You will require it in the next step while setting the root password.

(MySQL version 5.7 or higher generates a temporary random password in /var/log/mysqld.log after installation.)
shell> grep 'temporary password' /var/log/mysqld.log
2016-11-24T15:53:16.411237Z 1 [Note] A temporary password is generated for [email protected]: sl)=9dSg*eqI

 

Copy this password.

 

Now run the mysql_secure_installation command.

shell> mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:     (Enter theTemporary Password here)

The existing password for the user account root has expired. Please set a new password.

New password:       (Enter the New Password here)


Re-enter new password:   (Repeat the New Password)
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 100 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n

... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

- Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

 

 

Step 7: Access the MySQL Server

shell> mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye

 

 

Step 8: Update the MySQL Server

For RHEL 7/CentOS 7

shell> yum update mysql-server

 

For fedora 23 and later distros, use

shell> dnf update mysql-server

 

 

Step 9: Reset MySQL 5.7 Password.

To reset your MySQL root password you need to follow these steps:

Stop the MySQL process.

shell> systemctl stop mysqld.service

 

Set the MySQL environment option

shell> systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"

 

Start the MySQL process.

shell> systemctl start mysqld.service

 

Login as root.

shell> mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> UPDATE mysql.user SET authentication_string = PASSWORD('Testing_1990#') WHERE User = 'root' AND Host = 'localhost';
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 1

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

 

Stop MySQL Process.

shell> systemctl stop mysqld.service

 

Unset the MySQL environment option to start normally.

shell> systemctl unset-environment MYSQLD_OPTS

 

Start MySQL Process.

shell> systemctl start mysqld.service

 

Log in using your New Password.

shell> mysql -u root -p

 

 

Step 10: Disable Strict SQL Mode in MySQL 5.7 in CentOS 7/RHEL 7 or Fedora 23/24.

If you have updated your database server from older versions of MySQL and your app is now not compatible with strict SQL mode in MySQL 5.7.16, then you can disable strict SQL mode.

By strict mode, we mean a mode where at least one of the modes TRADITIONAL, STRICT_TRANS_TABLES, or STRICT_ALL_TABLES is enabled. The intensive validation of input data that some settings require more time than if the validation is not done.

Although the performance impact is not that great and MySQL does give us an option of leaving strict mode disabled.

To disable STRICT MODE in MySQL we will use these steps.

STEP 1: First find out the configuration file our MySQL installation is using.

shell> which mysqld
/usr/sbin/mysqld

 

shell> /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"
Default options are read from the following files in the given order:
/etc/my.cnf  /etc/mysql/my.cnf  ~/.my.cnf

 

In my server /etc/my.cnf is the main config file but if for any reason this file is not present in your server then check for /etc/mysql/my.cnf or ~/.my.cnf.

One of them will be there and edit it.

STEP 2: Check the current SQL mode:

Run the following command to check the current SQL mode:

shell> mysql -u root -p -e "select @@sql_mode"

 

Disable strict SQL mode in mysql 5.7 in CentOS 7
Complete Guide To Install, Update & Disable Strict SQL Mode In MySQL 5.7.

 

STEP 3: Copy the string “ONLY_FULL…” and remove all the variables that we don’t like for e.g STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ONLY_FULL_GROUP_BY.

Here’s the updated string

sql_mode = "ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

 

You can also remove everything and use something like this to completely disable the strict mode.

sql_mode = ""

 

STEP 4: Now open the configuration file /etc/my.cnf (for my server) and add the following line into the [mysqld] section:

Disable strict SQL mode in mysql 5.7 in CentOS 7
Complete Guide To Install, Update & Disable Strict SQL Mode In MySQL 5.7.

 

STEP 5: Restart the MySQL process and check again if your new settings are working or not.

Disable strict SQL mode in mysql 5.7 in CentOS 7
Complete Guide To Install, Update & Disable Strict SQL Mode In MySQL 5.7.

 

Now you have successfully changed the MySQL strict mode.

 

References

 

That’s all you need to know about installing, updating and disabling SQL strict mode for MySQL 5.7 in Linux.

Peace. 🙂

share on:
engy

engy

Hello there, My name is Rishi Guleria and I work as a Linux system administrator. I have created this blog to share what I have learned so far and to learn new things. Don't forget to leave the feedback. Have a great day. :)

Leave a Response

share on: