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

Deploy MySQL Server in Kubernetes using Helm

To install MySQL server in Kubernetes, run

Once install is completed, you will get something like

To connect to this MySQL, you need to create a temporary Ubuntu server in Kubernetes as MySQL is only available inside the cluster.

To create a server, run

Install MySQL client inside this server with

To connect to MySQL, use the command provided after install, in my case

You can get password by running

You can list packages installed using helm with

To delete, run

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

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.

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

To

Here /mnt/backup/mysql is where i store my MySQL data. Change it to whatever directory you store MySQL.

MySQL

Change MySQL root password

If you have MYSQL root password and want to change MySQL root password, first login to MYSQL with command

Method 1

Method 2

OR

mysql root mysql

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

When i try to restore a MySQL database, i get following error.

To fix the error, edit file

Add

Restart MySQL

if using mariadb, run

MySQL