mysql-root-grants

MySQL create user with root privileges

To create a user with “root” privileges, you need to find what privileges root have, this can be done with command

You can use following commands to create new user

Replace USER_NAME with your desired MySQL username. PASSWORD_HERE with password you need.

localhost is to allow only connection from localhost, you can replace it with remote server hostname or IP. If you want to allow connection from any IP, use %

MySQL root

You must reset your password using ALTER USER

On MySQL 8, when you start MySQL, it generate a temporary root password, that you can find with command

When i try run some SQL commands after login with this temporary password, i get error

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

To fix this, you need to change your MySQL root password by running

MySQL 8 on CentOS Change MySQL Root Password

mysql-permission-denied

MySQL Permission denied

After changing MySQL data directory, i get error

Apr 17 03:01:20 hon-pc-01 mysqld[25298]: 180417 3:01:20 [Note] /usr/sbin/mysqld (mysqld 10.0.34-MariaDB-0ubuntu0.16.04.1) starting as process 25297 …
Apr 17 03:01:20 hon-pc-01 mysqld[25298]: 180417 3:01:20 [ERROR] mysqld: Can’t create/write to file ‘/mnt/backup/mysql/aria_log_control’ (Errcode: 13 “Permission denied”)
Apr 17 03:01:20 hon-pc-01 mysqld[25298]: 180417 3:01:20 [ERROR] mysqld: Got error ‘Can’t create file’ when trying to use aria control file ‘/mnt/backup/mysql/aria_log_control’

To fix error, you need following permissions.

Replace /var/lib/mysql with path to mysql data directory.

If this don’t resolve the problem, you need to check permission for parent folder.

What i did was to login as user “mysql” and see if i can change to MySQL data directory folder, if you have problem going to this folder, fix the permission that block you, mostly it is due to parent folder permission.

By default mysql user don’t have SSH access, to enable use vipw and change

To

Here /mnt/backup/mysql is where i store my MySQL data. Change it to whatever directory you store MySQL.

MySQL

Find MySQL Server Version

To find MySQL server version, connect to MySQL, then run

You can also use “mysql –version”

This only show MySQL client version. If you have multiple MySQL server installed or MySQL server is running on another host, this won’t help.

MySQL

Change MySQL root password

If you have MYSQL root password and want to change MySQL root password, first login to MYSQL with command

Method 1

Method 2

OR

mysql root mysql

ERROR 2006 (HY000) at line 348: MySQL server has gone away

When i try to restore a MySQL database, i get following error.

To fix the error, edit file

Add

Restart MySQL

if using mariadb, run

MySQL

MySQL Unknown collation: utf8mb4_unicode_520_ci

When i restore a MySQL database backup taken on MairaDB 10 on MySQL 5.5, i get error

To fix this, i changed all instance of utf8mb4_unicode_520_ci in the SQL backup file with utf8mb4_unicode_ci with sed.

mysql

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