Category: MySQL – Page 3

Disable MySQL bin log on Bitnami

Posted on February 26, 2020 by ServerOk

if you have cloud sevrer with less disk space, it is better disable MySQL bin log as it take approx 3 GB of disk space on bitnami server. To disable MySQL binlog, edit mysql config file.

vi /opt/bitnami/mysql/my.cnf

1	vi /opt/bitnami/mysql/my.cnf

Find

[mysqld]

[mysqld]

Add below

disable_log_bin

1	disable_log_bin

Here is what i have in the my.cnf

root@wordpress-vm:~# cat /opt/bitnami/mysql/my.cnf

[mysqladmin]
user=root

[mysqld]
disable_log_bin
basedir=/opt/bitnami/mysql
datadir="/opt/bitnami/mysql/data"
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
tmpdir=/opt/bitnami/mysql/tmp
max_allowed_packet=32M
bind-address=127.0.0.1
skip-name-resolve=1
default-authentication-plugin=mysql_native_password

character-set-server=UTF8
collation-server=utf8_general_ci
[client]
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock

default-character-set=UTF8
[manager]
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
pid-file=/opt/bitnami/mysql/tmp/manager.pid
default-mysqld-path=/opt/bitnami/mysql/bin/mysqld.bin


!include /opt/bitnami/mysql/bitnami/my.cnf
root@wordpress-vm:~#

root@wordpress-vm:~# cat /opt/bitnami/mysql/my.cnf

[mysqladmin]

user=root

[mysqld]

disable_log_bin

basedir=/opt/bitnami/mysql

datadir="/opt/bitnami/mysql/data"

port=3306

socket=/opt/bitnami/mysql/tmp/mysql.sock

tmpdir=/opt/bitnami/mysql/tmp

max_allowed_packet=32M

bind-address=127.0.0.1

skip-name-resolve=1

default-authentication-plugin=mysql_native_password

character-set-server=UTF8

collation-server=utf8_general_ci

[client]

port=3306

socket=/opt/bitnami/mysql/tmp/mysql.sock

default-character-set=UTF8

[manager]

port=3306

socket=/opt/bitnami/mysql/tmp/mysql.sock

pid-file=/opt/bitnami/mysql/tmp/manager.pid

default-mysqld-path=/opt/bitnami/mysql/bin/mysqld.bin

!include /opt/bitnami/mysql/bitnami/my.cnf

root@wordpress-vm:~#

Now restart MySQL

/opt/bitnami/ctlscript.sh stop mysql
/opt/bitnami/ctlscript.sh start mysql

1 2	/opt/bitnami/ctlscript.sh stop mysql /opt/bitnami/ctlscript.sh start mysql

[…]

MySQL server has gone away

Posted on February 5, 2020 by ServerOk

When restoring a MySQL database, i get error “MySQL server has gone away”.

# mysql -u root -p'serverok123' sok_wp < backup.sql
ERROR 2006 (HY000) at line 5095: MySQL server has gone away
#

# mysql -u root -p'serverok123' sok_wp < backup.sql

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

To fix this, edit MySQL configuration file. Add following line under [mysqld] section.

max_allowed_packet = 256M

1	max_allowed_packet = 256M

Restart MySQL

systemctl restart mysql

1	systemctl restart mysql

If you use MariaDB

systemctl restart mariadb

1	systemctl restart mariadb

Aria engine is not enabled or did not start

Posted on January 19, 2020 by ServerOk

When starting MaraDB, i got error

root@localhost:/var/log/mysql# cat error.log
2020-01-19  6:18:46 0 [ERROR] mysqld: Can't lock aria control file '/var/lib/mysql/aria_log_control' for exclusive use, error: 11. Will retry for 30 seconds
2020-01-19  6:19:17 0 [ERROR] mysqld: Got error 'Could not get an exclusive lock; file is probably in use by another process' when trying to use aria control file '/var/lib/mysql/aria_log_control'
2020-01-19  6:19:17 0 [ERROR] Plugin 'Aria' init function returned error.
2020-01-19  6:19:17 0 [ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.
2020-01-19  6:19:17 0 [Note] InnoDB: Using Linux native AIO
2020-01-19  6:19:17 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-01-19  6:19:17 0 [Note] InnoDB: Uses event mutexes
2020-01-19  6:19:17 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-01-19  6:19:17 0 [Note] InnoDB: Number of pools: 1
2020-01-19  6:19:17 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-01-19  6:19:17 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-01-19  6:19:17 0 [Note] InnoDB: Completed initialization of buffer pool
2020-01-19  6:19:17 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-01-19  6:19:17 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=2137316301
2020-01-19  6:19:17 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-01-19  6:19:17 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2020-01-19  6:19:17 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-01-19  6:19:17 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-01-19  6:19:17 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-01-19  6:19:17 0 [Note] InnoDB: Waiting for purge to start
2020-01-19  6:19:17 0 [Note] InnoDB: 10.3.18 started; log sequence number 2137316310; transaction id 2275296
2020-01-19  6:19:17 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-01-19  6:19:17 0 [ERROR] Aria engine is not enabled or did not start. The Aria engine must be enabled to continue as mysqld was configured with --with-aria-tmp-tables
2020-01-19  6:19:17 0 [ERROR] Aborting

root@localhost:/var/log/mysql#

root@localhost:/var/log/mysql# cat error.log

2020-01-19 6:18:46 0 [ERROR] mysqld: Can't lock aria control file '/var/lib/mysql/aria_log_control' for exclusive use, error: 11. Will retry for 30 seconds

2020-01-19 6:19:17 0 [ERROR] mysqld: Got error 'Could not get an exclusive lock; file is probably in use by another process' when trying to use aria control file '/var/lib/mysql/aria_log_control'

2020-01-19 6:19:17 0 [ERROR] Plugin 'Aria' init function returned error.

2020-01-19 6:19:17 0 [ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.

2020-01-19 6:19:17 0 [Note] InnoDB: Using Linux native AIO

2020-01-19 6:19:17 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2020-01-19 6:19:17 0 [Note] InnoDB: Uses event mutexes

2020-01-19 6:19:17 0 [Note] InnoDB: Compressed tables use zlib 1.2.11

2020-01-19 6:19:17 0 [Note] InnoDB: Number of pools: 1

2020-01-19 6:19:17 0 [Note] InnoDB: Using SSE2 crc32 instructions

2020-01-19 6:19:17 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M

2020-01-19 6:19:17 0 [Note] InnoDB: Completed initialization of buffer pool

2020-01-19 6:19:17 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().

2020-01-19 6:19:17 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=2137316301

2020-01-19 6:19:17 0 [Note] InnoDB: 128 out of 128 rollback segments are active.

2020-01-19 6:19:17 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"

2020-01-19 6:19:17 0 [Note] InnoDB: Creating shared tablespace for temporary tables

2020-01-19 6:19:17 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

2020-01-19 6:19:17 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.

2020-01-19 6:19:17 0 [Note] InnoDB: Waiting for purge to start

2020-01-19 6:19:17 0 [Note] InnoDB: 10.3.18 started; log sequence number 2137316310; transaction id 2275296

2020-01-19 6:19:17 0 [Note] Plugin 'FEEDBACK' is disabled.

2020-01-19 6:19:17 0 [ERROR] Aria engine is not enabled or did not start. The Aria engine must be enabled to continue as mysqld was configured with --with-aria-tmp-tables

2020-01-19 6:19:17 0 [ERROR] Aborting

root@localhost:/var/log/mysql#

To fix error, remove the file

mv /var/lib/mysql/aria_log_control /root/

1	mv /var/lib/mysql/aria_log_control /root/

[…]

Install MariaDB 10.3 on CentOS 7

Posted on November 22, 2019 by ServerOk

MariaDB is an Open Source MySQL drop in replacement. MariaDB provides repository for various operating systems at http://downloads.mariadb.org/mariadb/repositories/ To install MariaDB 10.3 on CentOS 7, create file

vi /etc/yum.repos.d/MariaDB.repo

1	vi /etc/yum.repos.d/MariaDB.repo

Add

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

[mariadb]

name = MariaDB

baseurl = http://yum.mariadb.org/10.3/centos7-amd64

gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck=1

Install MariaDB server and client with

yum install MariaDB-server MariaDB-client

1	yum install MariaDB-server MariaDB-client

Enable MariaDB start on boot

systemctl enable mariadb

1	systemctl enable mariadb

Start MariaDB

systemctl start mariadb

1	systemctl start mariadb

[…]

MySQL ERROR Access denied you need SUPER privilege for this operation

Posted on November 5, 2019 (November 5, 2019) by ServerOk

WHen restoring a MySQL database, i get following error

root@ns3043014:~# mysql -u NEW_DB_USER -p'PASSOWRD_HERE' DB_NAME < BACKUP_FILE.sql
ERROR 1227 (42000) at line 4382: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
root@ns3043014:~#

root@ns3043014:~# mysql -u NEW_DB_USER -p'PASSOWRD_HERE' DB_NAME < BACKUP_FILE.sql

ERROR 1227 (42000) at line 4382: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

root@ns3043014:~#

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

/*!50013 DEFINER=`OLD_DB_USER`@`localhost` SQL SECURITY DEFINER */

1	/!50013 DEFINER=`OLD_DB_USER`@`localhost` SQL SECURITY DEFINER /

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

MySQL Recovering after a crash using tc.log

Posted on September 23, 2019 by ServerOk

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

root@localhost:/var/log/mysql# cat error.log
2019-09-23  6:30:10 0 [Note] InnoDB: Using Linux native AIO
2019-09-23  6:30:10 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-09-23  6:30:10 0 [Note] InnoDB: Uses event mutexes
2019-09-23  6:30:10 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-09-23  6:30:10 0 [Note] InnoDB: Number of pools: 1
2019-09-23  6:30:10 0 [Note] InnoDB: Using SSE2 crc32 instructions
2019-09-23  6:30:10 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2019-09-23  6:30:10 0 [Note] InnoDB: Completed initialization of buffer pool
2019-09-23  6:30:10 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-09-23  6:30:10 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2019-09-23  6:30:10 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-09-23  6:30:10 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-09-23  6:30:10 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2019-09-23  6:30:10 0 [Note] InnoDB: Waiting for purge to start
2019-09-23  6:30:10 0 [Note] InnoDB: 10.3.17 started; log sequence number 682398229; transaction id 1563655
2019-09-23  6:30:10 0 [Note] Plugin 'FEEDBACK' is disabled.
2019-09-23  6:30:10 0 [Note] Recovering after a crash using tc.log
2019-09-23  6:30:10 0 [ERROR] Can't init tc log
2019-09-23  6:30:10 0 [ERROR] Aborting

root@localhost:/var/log/mysql#

root@localhost:/var/log/mysql# cat error.log

2019-09-23 6:30:10 0 [Note] InnoDB: Using Linux native AIO

2019-09-23 6:30:10 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2019-09-23 6:30:10 0 [Note] InnoDB: Uses event mutexes

2019-09-23 6:30:10 0 [Note] InnoDB: Compressed tables use zlib 1.2.11

2019-09-23 6:30:10 0 [Note] InnoDB: Number of pools: 1

2019-09-23 6:30:10 0 [Note] InnoDB: Using SSE2 crc32 instructions

2019-09-23 6:30:10 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M

2019-09-23 6:30:10 0 [Note] InnoDB: Completed initialization of buffer pool

2019-09-23 6:30:10 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().

2019-09-23 6:30:10 0 [Note] InnoDB: 128 out of 128 rollback segments are active.

2019-09-23 6:30:10 0 [Note] InnoDB: Creating shared tablespace for temporary tables

2019-09-23 6:30:10 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

2019-09-23 6:30:10 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.

2019-09-23 6:30:10 0 [Note] InnoDB: Waiting for purge to start

2019-09-23 6:30:10 0 [Note] InnoDB: 10.3.17 started; log sequence number 682398229; transaction id 1563655

2019-09-23 6:30:10 0 [Note] Plugin 'FEEDBACK' is disabled.

2019-09-23 6:30:10 0 [Note] Recovering after a crash using tc.log

2019-09-23 6:30:10 0 [ERROR] Can't init tc log

2019-09-23 6:30:10 0 [ERROR] Aborting

root@localhost:/var/log/mysql#

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

mv /var/lib/mysql/tc.log /root/

1	mv /var/lib/mysql/tc.log /root/

[…]

MariaDB DEFAULT NULL ERROR 1064 (42000)

Posted on July 27, 2019 by ServerOk

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

root@server:~# mysql -u vcon_user -p vcon_sbc < vcon_sbc.sql
Enter password: 
ERROR 1064 (42000) at line 287: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` ti' at line 3
root@server:~#

root@server:~# mysql -u vcon_user -p vcon_sbc < vcon_sbc.sql

Enter password:

ERROR 1064 (42000) at line 287: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'json DEFAULT NULL,

`created_at` timestamp NULL DEFAULT NULL,

`updated_at` ti' at line 3

root@server:~#

Source server

root@ip-172-31-16-209:~# mysql --version
mysql  Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using  EditLine wrapper
root@ip-172-31-16-209:~#

root@ip-172-31-16-209:~# mysql --version

mysql Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using EditLine wrapper

root@ip-172-31-16-209:~#

Destination server

root@server:~# mysql --version
mysql  Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
root@server:~#

root@server:~# mysql --version

mysql Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

root@server:~#

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

MariaDB [test]> CREATE TABLE `current_job_status_reports` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `jobs` json DEFAULT NULL,
    ->   `created_at` timestamp NULL DEFAULT NULL,
    ->   `updated_at` timestamp NULL DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` ti' at line 3
MariaDB [test]>

MariaDB [test]> CREATE TABLE `current_job_status_reports` (

-> `id` int(11) NOT NULL AUTO_INCREMENT,

-> `jobs` json DEFAULT NULL,

-> `created_at` timestamp NULL DEFAULT NULL,

-> `updated_at` timestamp NULL DEFAULT NULL,

-> PRIMARY KEY (`id`)

-> ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'json DEFAULT NULL,

`created_at` timestamp NULL DEFAULT NULL,

`updated_at` ti' at line 3

MariaDB [test]>

I run the command on […]

Install MySQL as Service in Windows

Posted on March 3, 2019 by ServerOk

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

mysqld.exe install

1	mysqld.exe install

If you want a differnt name for service, run

mysqld.exe install SERVICE_NAME

1	mysqld.exe install SERVICE_NAME

You can delete service with command

sc delete SERVICE_NAME

1	sc delete SERVICE_NAME

[…]

MySQL 8 apt Error The following signatures were invalid

Posted on February 18, 2019 (February 18, 2019) by ServerOk

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

root@ssdnodes-45790:~# apt update
Ign:1 http://download.webmin.com/download/repository sarge InRelease
Hit:2 http://download.webmin.com/download/repository sarge Release                  
Get:3 http://repo.mysql.com/apt/ubuntu bionic InRelease [16.9 kB]                                              
Hit:5 http://archive.ubuntu.com/ubuntu cosmic InRelease                                                            
Hit:6 http://archive.ubuntu.com/ubuntu cosmic-security InRelease
Err:3 http://repo.mysql.com/apt/ubuntu bionic InRelease
  The following signatures were invalid: EXPKEYSIG 8C718D3B5072E1F5 MySQL Release Engineering <mysql-build@oss.oracle.com>
Hit:7 http://archive.ubuntu.com/ubuntu cosmic-updates InRelease
Reading package lists... Done                     
W: GPG error: http://repo.mysql.com/apt/ubuntu bionic InRelease: The following signatures were invalid: EXPKEYSIG 8C718D3B5072E1F5 MySQL Release Engineering <mysql-build@oss.oracle.com>
E: The repository 'http://repo.mysql.com/apt/ubuntu bionic InRelease' is not signed.
N: Updating from such a repository can't be done securely, and is therefore disabled by default.
N: See apt-secure(8) manpage for repository creation and user configuration details.
root@ssdnodes-45790:~#

root@ssdnodes-45790:~# apt update

Ign:1 http://download.webmin.com/download/repository sarge InRelease

Hit:2 http://download.webmin.com/download/repository sarge Release

Get:3 http://repo.mysql.com/apt/ubuntu bionic InRelease [16.9 kB]

Hit:5 http://archive.ubuntu.com/ubuntu cosmic InRelease

Hit:6 http://archive.ubuntu.com/ubuntu cosmic-security InRelease

Err:3 http://repo.mysql.com/apt/ubuntu bionic InRelease

The following signatures were invalid: EXPKEYSIG 8C718D3B5072E1F5 MySQL Release Engineering <mysql-build@oss.oracle.com>

Hit:7 http://archive.ubuntu.com/ubuntu cosmic-updates InRelease

Reading package lists... Done

W: GPG error: http://repo.mysql.com/apt/ubuntu bionic InRelease: The following signatures were invalid: EXPKEYSIG 8C718D3B5072E1F5 MySQL Release Engineering <[email protected]>

E: The repository 'http://repo.mysql.com/apt/ubuntu bionic InRelease' is not signed.

N: Updating from such a repository can't be done securely, and is therefore disabled by default.

N: See apt-secure(8) manpage for repository creation and user configuration details.

root@ssdnodes-45790:~#

To fix this error, run

apt-key adv --keyserver keys.gnupg.net --recv-keys 8C718D3B5072E1F5

1	apt-key adv --keyserver keys.gnupg.net --recv-keys 8C718D3B5072E1F5

Now apt update will work. To list keys, run

root@ssdnodes-45790:~# apt-key list
/etc/apt/trusted.gpg
--------------------
pub   dsa1024 2003-02-03 [SCA] [expires: 2022-02-16]
      A4A9 4068 76FC BD3C 4567  70C8 8C71 8D3B 5072 E1F5
uid           [ unknown] MySQL Release Engineering <mysql-build@oss.oracle.com>

pub   dsa1024 2002-02-28 [SCA]
      1719 003A CE3E 5A41 E2DE  70DF D97A 3AE9 11F6 3C51
uid           [ unknown] Jamie Cameron <jcameron@webmin.com>
sub   elg1024 2002-02-28 [E]

/etc/apt/trusted.gpg.d/ondrej_ubuntu_php.gpg
--------------------------------------------
pub   rsa1024 2009-01-26 [SC]
      14AA 40EC 0831 7567 56D7  F66C 4F4E A0AA E526 7A6C
uid           [ unknown] Launchpad PPA for Ondřej Surý

/etc/apt/trusted.gpg.d/ubuntu-keyring-2012-archive.gpg
------------------------------------------------------
pub   rsa4096 2012-05-11 [SC]
      790B C727 7767 219C 42C8  6F93 3B4F E6AC C0B2 1F32
uid           [ unknown] Ubuntu Archive Automatic Signing Key (2012) <ftpmaster@ubuntu.com>

/etc/apt/trusted.gpg.d/ubuntu-keyring-2012-cdimage.gpg
------------------------------------------------------
pub   rsa4096 2012-05-11 [SC]
      8439 38DF 228D 22F7 B374  2BC0 D94A A3F0 EFE2 1092
uid           [ unknown] Ubuntu CD Image Automatic Signing Key (2012) <cdimage@ubuntu.com>

/etc/apt/trusted.gpg.d/ubuntu-keyring-2018-archive.gpg
------------------------------------------------------
pub   rsa4096 2018-09-17 [SC]
      F6EC B376 2474 EDA9 D21B  7022 8719 20D1 991B C93C
uid           [ unknown] Ubuntu Archive Automatic Signing Key (2018) <ftpmaster@ubuntu.com>

root@ssdnodes-45790:~#

root@ssdnodes-45790:~# apt-key list

/etc/apt/trusted.gpg

--------------------

pub dsa1024 2003-02-03 [SCA] [expires: 2022-02-16]

A4A9 4068 76FC BD3C 4567 70C8 8C71 8D3B 5072 E1F5

uid [ unknown] MySQL Release Engineering <mysql-build@oss.oracle.com>

pub dsa1024 2002-02-28 [SCA]

1719 003A CE3E 5A41 E2DE 70DF D97A 3AE9 11F6 3C51

uid [ unknown] Jamie Cameron <jcameron@webmin.com>

sub elg1024 2002-02-28 [E]

/etc/apt/trusted.gpg.d/ondrej_ubuntu_php.gpg

--------------------------------------------

pub rsa1024 2009-01-26 [SC]

14AA 40EC 0831 7567 56D7 F66C 4F4E A0AA E526 7A6C

uid [ unknown] Launchpad PPA for Ondřej Surý

/etc/apt/trusted.gpg.d/ubuntu-keyring-2012-archive.gpg

------------------------------------------------------

pub rsa4096 2012-05-11 [SC]

790B C727 7767 219C 42C8 6F93 3B4F E6AC C0B2 1F32

uid [ unknown] Ubuntu Archive Automatic Signing Key (2012) <ftpmaster@ubuntu.com>

/etc/apt/trusted.gpg.d/ubuntu-keyring-2012-cdimage.gpg

------------------------------------------------------

pub rsa4096 2012-05-11 [SC]

8439 38DF 228D 22F7 B374 2BC0 D94A A3F0 EFE2 1092

uid [ unknown] Ubuntu CD Image Automatic Signing Key (2012) <cdimage@ubuntu.com>

/etc/apt/trusted.gpg.d/ubuntu-keyring-2018-archive.gpg

------------------------------------------------------

pub rsa4096 2018-09-17 [SC]

F6EC B376 2474 EDA9 D21B 7022 8719 20D1 991B C93C

uid [ unknown] Ubuntu Archive Automatic Signing Key (2018) <ftpmaster@ubuntu.com>

root@ssdnodes-45790:~#

[…]

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