MySQL

MySQL ERROR Access denied you need SUPER privilege for this operation

WHen restoring a MySQL database, i get following error

I opened the file in vim editor. On checking line 4382, i found following

This is because some stored procedures use old database user as definer. To fix you need to find and replace all entry for old username with new username.

In vim, i can run

Or delete the DEFINER line from SQL file.

Now restore BACKUP_FILE-modified.sql with

MySQL

MySQL Recovering after a crash using tc.log

After MySQL upgrade from MraiaDB 10.1 to 10.3, MySQL failed to start. On checking log, found

On /var/lib/mysql folder, there is a tc.log file with 0 size. The error is solved by removing this file.

MariaDB DEFAULT NULL ERROR 1064 (42000)

When trying to restore a MySQL databse bakcup taken on MySQL 5.7 server to MairaDB 10.1 server i get following error.

Source server

Destination server

I opend the SQL file in editor and checked line 287, i run the SQL in mysql, that resulted in error

I run the command on a MariaDB 10.2 server and it worked.

To fix the problem, upgrade MariaDB to version 10.2 or above. In this case, i went with MariaDB 10.04 and it worked fine.

MySQL

Install MySQL as Service in Windows

When you manually install MySQL from zip, no MySQL service get created.

To run MySQL Server as a service in windows, go to the folder where “mysqld.exe” is located and run following command

If you want a differnt name for service, run

You can delete service with command

MySQL 8 apt Error The following signatures were invalid

When trying to install MySQL 8 on Ubuntu 18.04, i get following error

To fix this error, run

Now apt update will work.

To list keys, run

MySQL Got error 24 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 [mysqld] section

Now restart MySQL server

MySQL open_files_limit

MySQL Out of resources when opening file

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 on /etc/mysql/my.cnf, add following under [mysqld] section.

Now restart MySQL

Verify it is changed with command

MySQL open_files_limit

MySQL

mysqldump packet bigger than max_allowed_packet

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 in command line

On a VPS, editing /etc/my.cnf did not changed the value in MySQL for some reason. I got it fixed by running mysqldump with –max_allowed_packet=2G option.

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 password, you will be logged in with no password.

To reset MySQL root password, run

Now kill the MySQL process with command

Start MySQL normally with

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