How to Disable MySQL Strict Mode

To Disable MySQL Strict Mode, edit

Add under [mysqld] section

Verify MySQL Strict Mode

To check if MySQL is running in strict mode, run

You get empty result if MySQL is not running under strict mode.

Here is a MySQL server running under strict mode

After disabling MySQL strict mode

mysql

MySQL root can’t login to phpMyAdmin

On Debian/Ubuntu MySQL root user can only login using socket. This is done with Plugin.

To fix this, first set a password for user root.

Change MySQL root password

To disable plugin, run following commands in MySQL command prompt as user root.

MySQL
phpMyAdmin

MySQL InnoDB Optimise

MySQL Optimize

Optimize MariaDB

Edit file

Find

On this section, you will see “#skip-external-locking”, add below.

This is some basic settings. Now run MySQL tuner, make suggestions as required.

To make the settings live, you need to restart MariaDB server.

MySQL Tuner

innodb_buffer_pool_size

On a server with 32 GB RAM, set

Having more innodb_buffer_pool_size, make MySQL act like an in memory database.

innodb_buffer_pool_instances divide buffer pool into smaller instances. It is better use 1 GB per instance.

To see current innodb_buffer_pool values, run

MySQL InnoDB Optimise

See MySQL

MySQL

MySQL Installation

MySQL User Management

MySQL Optimize

MySQL Backup/Restore

MySQL GUI

MySQL Errors

Reset MySQL root Password

Reset MySQL 5.7 root password on Ubuntu 16.04

To reset MySQL root password, stop MySQL

Start MySQL with

Start another terminal, login to MySQL as root

Run following commands to change password.

Now you need to kill running MySQL processes and start MySQL as normal.

MySQL User

To allow a user to create another user

Now this user will be able to create new users.

Create a user with root privilages

For MySQL 8

Debian Allow MySQL root user to login using phpMyAdmin

On Debian/Ubuntu by default MySQL root user can only login from local accounts.

To enable login from phpMyAdmin, you need to set plugin to “mysql_native_password”.

To do this, login to MySQL as user root, then run

Configuring MySQL .my.cnf

Add following content

Replace DBPASSWORD with your MySQL root password.

Now set permission for this file to 600, so no user other than root can see it.