Category: MySQL – Page 3

MySQL Got error 24 Too many open files

Posted on January 8, 2019 (January 31, 2020) by ServerOk

For ubuntu, see Ubuntu 18.04 MariaDB 10.2 Too many open files On taking MySQL backup with mysqldump, i get following error

mysqldump: Got error: 1030: "Got error 24 "Too many open files" from storage engine MyISAM" when using LOCK TABLES
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_f68_2.MAD' (Errcode: 24 "Too many open files")' when trying to dump tablespaces

1 2	mysqldump: Got error: 1030: "Got error 24 "Too many open files" from storage engine MyISAM" when using LOCK TABLES mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_f68_2.MAD' (Errcode: 24 "Too many open files")' when trying to dump tablespaces

This is because open_files_limit limit in MySQL. To verify login to MySQL server and run

select @@open_files_limit;

1	select @@open_files_limit;

This will give you current value of open_files_limit. To increase, edit /etc/my.cnf file, add

vi /etc/my.cnf

1	vi /etc/my.cnf

Add under […]

MySQL Out of resources when opening file

Posted on January 1, 2019 by ServerOk

On a MySQL server got the error

Out of resources when opening file '/tmp/#sql_318d_0.MAD' (Errcode: 24 "Too many open files")

1	Out of resources when opening file '/tmp/#sql_318d_0.MAD' (Errcode: 24 "Too many open files")

This is because number of files allowed to open by MySQL is set to too low. To view current settings, in MySQL command prompt, run

select @@open_files_limit;

1	select @@open_files_limit;

show variables like "open%";

1	show variables like "open%";

To increase the value, edit MySQL configuration file, this normally located in /etc/my.cnf, on some servers, it will be […]

mysqldump packet bigger than max_allowed_packet

Posted on January 1, 2019 by ServerOk

When backing up a MySQL database using mysqldump command, got following error

[root@vps189 ~]# mysqldump sok_main > sok_main.sql
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' when dumping table `wp_options` at row: 7068
[root@vps189 ~]#

[root@vps189 ~]# mysqldump sok_main > sok_main.sql

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' when dumping table `wp_options` at row: 7068

[root@vps189 ~]#

To fix, you need to edit MySQL config file

vi /etc/my.cnf

1	vi /etc/my.cnf

Add following under [mysqld] section

max_allowed_packet=2G

1	max_allowed_packet=2G

Now restart MySQL with

systemctl restart mysql

1	systemctl restart mysql

You can verify the value is changed by running following SQL command in MySQL prompt

show variables like 'max_allowed_packet';

1	show variables like 'max_allowed_packet';

Or my running following command […]

Reset MySQL 5.7 root password on Ubuntu 16.04

Posted on December 29, 2018 by ServerOk

First stop MySQL with command

service mysql stop

1	service mysql stop

Now run

mysqld_safe --skip-grant-tables

1	mysqld_safe --skip-grant-tables

On Ubuntu 16.04 server with MySQL 5.7, when i run, i get following error.

root@sok-06:~# mysqld_safe --skip-grant-tables
2018-12-29T06:13:26.191918Z mysqld_safe Logging to syslog.
2018-12-29T06:13:26.195961Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2018-12-29T06:13:26.199396Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.
root@sok-06:~#

root@sok-06:~# mysqld_safe --skip-grant-tables

2018-12-29T06:13:26.191918Z mysqld_safe Logging to syslog.

2018-12-29T06:13:26.195961Z mysqld_safe Logging to '/var/log/mysql/error.log'.

2018-12-29T06:13:26.199396Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

root@sok-06:~#

To fix, this error, create a the folder and change ownership to user mysql

mkdir /var/run/mysqld
chown -R mysql:mysql /var/run/mysqld

1 2	mkdir /var/run/mysqld chown -R mysql:mysql /var/run/mysqld

Now start a new terminal, login to MySQL with command

mysql -u root -p

1	mysql -u root -p

You can press enter for […]

Extract Backup of one database from mysqldump all databases

Posted on August 12, 2018 (August 12, 2018) by ServerOk

When you backup MySQL Server with command

mysqldump  --all-databases > all-db.sql

1	mysqldump --all-databases > all-db.sql

You get a MySQL backup file containing all databases. If you want to generate MySQL backup for single database from this file, you can run

sed -n '/^-- Current Database: `DB_NAME`/,/^-- Current Database: `/p' all-db.sql > DB_NAME.sql

1	sed -n '/^-- Current Database: `DB_NAME`/,/^-- Current Database: `/p' all-db.sql > DB_NAME.sql

See mysqldump […]

MySQL Repair

Posted on May 2, 2018 by ServerOk

Fatal error: Can’t open and lock privilege tables […]

Fatal error: Can’t open and lock privilege tables

Posted on May 2, 2018 by ServerOk

On a Cpanel Server, MySQL did not start. I checked the error log in /var/lib/mysql folder, found following error 2018-05-02 12:08:34 30796 [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‘./mysql/db’ is marked as crashed and should be repaired It says “db” table in “mysql” database got corrupted. To fix, i run following […]

MySQL create user with root privileges

Posted on April 28, 2018 by ServerOk

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 […]

You must reset your password using ALTER USER

Posted on April 23, 2018 by ServerOk

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, […]

MySQL Permission denied

Posted on April 17, 2018 (April 17, 2018) by ServerOk

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 […]