Can’t open and lock privilege tables: Table ‘./mysql/db’ is marked as crashed
MySQL Can’t create new tempfile
mysqlcheck
How to Repair MyISAM Tables
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
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 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.
-
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 root
Change MySQL 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 MySQL root password in Virtualmin
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.
-
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 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
-
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>
-
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