Category: MySQL

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

    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 as crashed and should be repaired
2023-03-10T18:13:26.405471Z 0 [ERROR] Fatal error: Failed to initialize ACL/grant/time zones structures or failed to remove temporary table files.
2023-03-10T18:13:26.405486Z 0 [ERROR] Aborting

    It says “db” table in “mysql” database got corrupted. To fix, I run following

    cd /var/lib/mysql/mysql
myisamchk -r db

    Now restart MySQL with the command

    systemctl restart mysqld

    Back to MySQL Repair

  • MySQL create user with root privileges

    MySQL create user with root privileges

    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 'USER_NAME'@'localhost' WITH GRANT OPTION;

    Replace USER_NAME with your desired MySQL username. PASSWORD_HERE with the 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 %

    Back to MySQL Password

  • 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

    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';

    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.

    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

    To

    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

    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 host, this won’t help.

    MySQL

  • mysql root

    Change MySQL Password

    Reset MySQL root password

    MySQL Password Reset Errors

    Back to MySQL Server

  • Change MySQL root password

    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

    Use command

    mysqladmin -u root -p password

    This command will ask for new and old MySQL password.

    MySQL Password mysql

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

    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

    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 's/utf8mb4_unicode_520_ci/utf8mb4_unicode_ci/g'  wp.sql

    mysql

  • How to Disable MySQL Strict Mode

    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;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@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

  • Find all MySQL databases using InnoDB engine

    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

    MySQL

  • MySQL root can’t login to phpMyAdmin

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

    MariaDB [(none)]> select User, Plugin from mysql.user;
+-----------+-------------+
| User      | Plugin      |
+-----------+-------------+
| root      | unix_socket |
| sserverok |             |
+-----------+-------------+
2 rows in set (0.00 sec)

MariaDB [(none)]>

    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.

    update mysql.user set Plugin='' where User="root";
flush privileges;

    MySQL
    phpMyAdmin