MySQL

Can’t open and lock privilege tables: Table ./mysql/db is marked as crashed

by

On a Cpanel Server, MySQL did not start. I checked the error log in /var/lib/mysql folder, and found the following error in MySQL log file /var/log/mysqld.log 2023-03-10T18:13:26.405453Z 0 [ERROR] /usr/sbin/mysqld: Table ‘./mysql/db’ is marked as crashed and should be repaired 2023-03-10T18:13:26.405465Z 0 [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‘./mysql/db’ is marked … Read more

MySQL create user with root privileges

by

To create a user with “root” privileges, you need to find what privileges root has, this can be done with the command SHOW GRANTS FOR ‘root’@’localhost’; You can use the following commands to create a new user GRANT ALL PRIVILEGES ON *.* TO ‘USER_NAME’@’localhost’ IDENTIFIED BY ‘PASSWORD_HERE’ WITH GRANT OPTION; GRANT PROXY ON ”@” TO … Read more

You must reset your password using ALTER USER

by

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 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. … Read more

MySQL Permission denied

by

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 … Read more

Find MySQL Server Version

by

To find MySQL server version, connect to MySQL, then run 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 ~]# This only show MySQL client version. If you have multiple MySQL server installed or MySQL server is running on another … Read more

mysql root

by

Change MySQL Password Change MySQL root password MySQL create user with root privileges Change MariaDB user password Reset MySQL root password Reset MySQL root Password Ubuntu 16.04 MySQL 5.7 Reset root password CentOS 7 MySQL 5.7 root password reset How to Reset Root Password in MySQL 8.0 Reset MySQL root password on Bitnami server Change … Read more

Change MySQL root password

by

If you have MYSQL root password and want to change the MySQL root password, first log in to MYSQL with the command mysql -u root -p Method 1 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’; OR ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘MYSQL_ROOT_PASSWORD’; Method 3 … Read more

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

by

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 ~]# To fix the error, edit file /etc/my.cnf.d/my.cnf Add max_allowed_packet=64M Restart MySQL service mysql restart if using mariadb, run service mariadb restart MySQL

MySQL Unknown collation: utf8mb4_unicode_520_ci

by

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# 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 ... Read more

How to Disable MySQL Strict Mode

by

To Disable MySQL Strict Mode, edit vi /etc/my.cnf Add under [mysqld] section sql_mode = “” Verify MySQL Strict Mode To check if MySQL is running in strict mode, run 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; … Read more