Category: MySQL – Page 3

MySQL create user with root privileges

April 28, 2018

by ServerOk with No Comment MySQL

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

SHOW GRANTS FOR 'root'@'localhost';

1	SHOW GRANTS FOR 'root'@'localhost';

You can use following commands to create new user

GRANT ALL PRIVILEGES ON *.* TO 'USER_NAME'@'localhost' IDENTIFIED BY 'PASSWORD_HERE' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'USER_NAME'@'localhost' WITH GRANT OPTION;

1 2	GRANT ALL PRIVILEGES ON . TO 'USER_NAME'@'localhost' IDENTIFIED BY 'PASSWORD_HERE' WITH GRANT OPTION; GRANT PROXY ON ''@'' TO 'USER_NAME'@'localhost' WITH GRANT OPTION;

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

April 23, 2018

by ServerOk with No Comment MySQL

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

grep 'temporary password' /var/log/mysqld.log

1	grep 'temporary password' /var/log/mysqld.log

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

ALTER USER 'root'@'localhost' IDENTIFIED  BY 'MYSQL_ROOT_PASSWORD';

1	ALTER USER 'root'@'localhost' IDENTIFIED BY 'MYSQL_ROOT_PASSWORD';

MySQL 8 on CentOS Change MySQL Root Password

MySQL Permission denied

April 17, 2018

by ServerOk with No Comment MySQL

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.

chown -R mysql:mysql /var/lib/mysql
chmod -R 700 /var/lib/mysql

1 2	chown -R mysql:mysql /var/lib/mysql chmod -R 700 /var/lib/mysql

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

mysql:x:122:130:MySQL Server,,,:/nonexistent:/bin/false

1	mysql:x:122:130:MySQL Server,,,:/nonexistent:/bin/false

mysql:x:122:130:MySQL Server,,,:/mnt/backup/mysql:/bin/bash

1	mysql:x:122:130:MySQL Server,,,:/mnt/backup/mysql:/bin/bash

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

MySQL

Find MySQL Server Version

March 28, 2018

by ServerOk with No Comment MySQL

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

select version();

1	select version();

You can also use “mysql –version”

[root@server ~]# mysql --version
mysql  Ver 14.14 Distrib 5.6.38, for Linux (x86_64) using  EditLine wrapper
[root@server ~]#

[root@server ~]# mysql --version

mysql Ver 14.14 Distrib 5.6.38, for Linux (x86_64) using EditLine wrapper

[root@server ~]#

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

mysql root

March 28, 2018

by ServerOk with No Comment MySQL

MySQL

Change MySQL root password

March 28, 2018

by ServerOk with No Comment MySQL

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

mysql -u root -p

1	mysql -u root -p

Method 1

UPDATE mysql.user SET Password=PASSWORD('MYSQL_ROOT_PASSWORD') WHERE User='root';
FLUSH PRIVILEGES;

1 2	UPDATE mysql.user SET Password=PASSWORD('MYSQL_ROOT_PASSWORD') WHERE User='root'; FLUSH PRIVILEGES;

Method 2

ALTER USER 'root'@'localhost' IDENTIFIED  BY 'MYSQL_ROOT_PASSWORD';

1	ALTER USER 'root'@'localhost' IDENTIFIED BY 'MYSQL_ROOT_PASSWORD';

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'MYSQL_ROOT_PASSWORD';

1	ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'MYSQL_ROOT_PASSWORD';

mysql root mysql

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

March 19, 2018

by ServerOk with No Comment MySQL

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

[root@server2 ~]# mysql sok_wp < /var/www/sok_wp.sql
ERROR 2006 (HY000) at line 348: MySQL server has gone away
[root@server2 ~]#

[root@server2 ~]# mysql sok_wp < /var/www/sok_wp.sql

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

[root@server2 ~]#

To fix the error, edit file

/etc/my.cnf.d/my.cnf

1	/etc/my.cnf.d/my.cnf

Add

max_allowed_packet=64M

1	max_allowed_packet=64M

Restart MySQL

service mysql restart

1	service mysql restart

if using mariadb, run

service mariadb restart

1	service mariadb restart

MySQL

MySQL Unknown collation: utf8mb4_unicode_520_ci

March 4, 2018

by ServerOk with No Comment MySQL

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

root@48b55e4d9b35:/home/boby# mysql -u root -pflashwebhost wp < wp.sql
ERROR 1273 (HY000) at line 356: Unknown collation: 'utf8mb4_unicode_520_ci'
root@48b55e4d9b35:/home/boby#

root@48b55e4d9b35:/home/boby# mysql -u root -pflashwebhost wp < wp.sql

ERROR 1273 (HY000) at line 356: Unknown collation: 'utf8mb4_unicode_520_ci'

root@48b55e4d9b35:/home/boby#

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

sed -i 's/utf8mb4_unicode_520_ci/utf8mb4_unicode_ci/g'  wp.sql

1	sed -i 's/utf8mb4_unicode_520_ci/utf8mb4_unicode_ci/g' wp.sql

mysql

How to Disable MySQL Strict Mode

February 20, 2018

by ServerOk with No Comment MySQL

To Disable MySQL Strict Mode, edit

vi /etc/my.cnf

1	vi /etc/my.cnf

Add under [mysqld] section

sql_mode = ""

1	sql_mode = ""

Verify MySQL Strict Mode

To check if MySQL is running in strict mode, run

SELECT @@sql_mode;

1	SELECT @@sql_mode;

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

Here is a MySQL server running under strict mode

mysql> SELECT @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

mysql> SELECT @@sql_mode;

+-------------------------------------------------------------------------------------------------------------------------------------------+

| @@sql_mode |

+-------------------------------------------------------------------------------------------------------------------------------------------+

| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

+-------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql>

After disabling MySQL strict mode

mysql> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.01 sec)

mysql>

mysql> SELECT @@sql_mode;

+------------+

| @@sql_mode |

+------------+

| |

+------------+

1 row in set (0.01 sec)

mysql>

mysql

Find all MySQL databases using InnoDB engine

February 14, 2018

by ServerOk with No Comment MySQL

To find all MySQL database using InnoDB MySQL engine, run

mysql -N mysql -e "SELECT table_schema FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';" | cut -d" " -f2 | sort | uniq

1	mysql -N mysql -e "SELECT table_schema FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';" \| cut -d" " -f2 \| sort \| uniq

MySQL

ServerOK

Posts in category: MySQL

MySQL create user with root privileges

You must reset your password using ALTER USER

MySQL Permission denied

Find MySQL Server Version

mysql root

Change MySQL root password

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

MySQL Unknown collation: utf8mb4_unicode_520_ci

How to Disable MySQL Strict Mode

Find all MySQL databases using InnoDB engine