MySQL Got error 24 Too many open files

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

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

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

Add under […]

Read More…

MySQL Out of resources when opening file

MySQL open_files_limit

On a MySQL server got the error

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

Or

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

Read More…

mysqldump packet bigger than max_allowed_packet

MySQL

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

To fix, you need to edit MySQL config file

Add following under [mysqld] section

Now restart MySQL with

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

Or my running following command […]

Read More…

Reset MySQL 5.7 root password on Ubuntu 16.04

First stop MySQL with command

Now run

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

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

Now start a new terminal, login to MySQL with command

You can press enter for […]

Read More…

Extract Backup of one database from mysqldump all databases

When you backup MySQL Server with command

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

See mysqldump […]

Read More…

Fatal error: Can’t open and lock privilege tables

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

Read More…

MySQL create user with root privileges

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

You can use following commands to create new user

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

Read More…

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

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

Read More…

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

Read More…