Project

General

Profile

Actions

MySql & MariaDB

This page and its subpages are a meaned to exchange knowledge and hints about MySQL


MariaDB vs. MySQL

Debian uses MariaDB per default. Since debian 10 or 11 mariadb is the preferred label and mysql is a symlink only.

Maintenance

How To Check, Repair & Optimize All Tables in All Databases

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

if you need to exclude a db:

echo "show databases;" | mysql -u root --password=$password

(Remote) Access

Since MariaDB 10.5 remote access by default is switched off. The parameters

--skip-networking, --bind-address = 127.0.0.1 and --skip-bind-address

can be used to configure it. In MariaDB 10.5 --bind-address is defined in /etc/mysql/mariadb.conf.d/50-server.cnf

Read configuration via mysqld --print-defaults

More here: https://mariadb.com/kb/en/configuring-mariadb-for-remote-client-access/

Missing network access rights may lead to error 10061 or SQL error code 08S01

General MySQL problems

Too many connections

This can have two reasons:

max_connections of mysql server too low

  1. check the in your /etc/mysql/my.conf
[mysqld]
max_connections        = 1000

limit for open file descriptors too low (Linux)

Check the ulimit setting @ulimit -n@, this often too low
In order to increase this permanently and globally edit /etc/security/limits.conf

mysql            soft    nofile           24000
mysql            hard    nofile           32000

Links:

The server time zone value '...' is unrecognized

related to #8118

A general solution to this problem is described in https://stackoverflow.com/questions/49518683/the-server-time-zone-value-cest-is-unrecognized :

Import the system timezones in the mysql system db

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

configure the timezone in /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/mysql/my.cnf depending of your ditro

[mysqld]
default_time_zone = Europe/Berlin

Restart the mysql server

systemctl restart mysql.service

(or the appropriate command depending on your distro).

I am getting errors when using mysqldump

mysqldump: Got error: 23: Out of resources when opening file

This usually occurs with the cdm databases since these are containing over 400 Tables and mysqldump will create too many open file handles and thus will exceed the system limits.
You need to increase the open-files limitation in the mysqld or in the system limits (see: http://duntuk.com/how-raise-ulimit-open-files-and-mysql-openfileslimit)

Configure mysqld: /etc/mysql/my.cnf , from mysql 5.5 on the configuration is different!

pre mysql 5.5

[mysqld_safe]
# increase the filelimit to avoid problems when creating dumps of cdm dbs
open_files_limit = 5000

or adjust the open file limits in /etc/security/limits.conf

 mysql            soft    nofile           24000
 mysql            hard    nofile           32000

after mysql 5.5

(from https://support.plesk.com/hc/en-us/articles/213393029-MySQL-values-open-files-limit-and-max-connections-are-not-applied)

For Debian-based distributions:

  1. Create /lib/systemd/system/mysql.service.d/limit_nofile.conf file with the following content:
# cat /lib/systemd/system/mysql.service.d/limit_nofile.conf
[Service]
LimitNOFILE=4096
  1. Reload daemons and restart MySQL service:
# systemctl daemon-reload
# systemctl restart mysql

Other

  • Error 29: use --single-transaction e.g: mysqldump --opt --single-transaction -u root -p drupal_dataportal > drupal_dataportal-2009-12-11.sql
  • something with "cannot open or write to tmp/#table.myd" -> use --skip-locktables ???

Maybe you will find a solution here: http://forge.mysql.com/wiki/Category:FAQ

How to ...

I want to tunnel my MySQL client through SSH

ssh -N -L 3307:domain.name.of.mysqlserver:3306 username@domain.name.of.gatewayserver

This will open a tunnel, listening on localhost:3307 and forwarding everything to mysqlserver:3306, and doing it all via the ssh service on the gateway machine. Most of the time mysqlserver and gatewayserver are the same machine.
This example shows us specifying port 3307 on the local end of the tunnel; We do this because maybe we are running a MySQL server on the local machine, so we can't re-use the default MySQL port.

You'll now have a terminal open on the gateway machine, but you don't need it for this procedure, so set it aside.

Now, on your local machine, execute a mysql connection like so:

mysql -u username -p -h 127.0.0.1 -P 3307 databasename

(or use HeidiSQL, MySQLadminstrator etc.)

In other words, mysql thinks it's connecting to localhost, but on a different port. In fact, the connection is being made securely to the remote mysql server, via the gateway machine and the local "mouth" of the ssh tunnel on your own machine.

When you're finished with your mysql session, log out of the session on the gateway machine. That will properly close the tunnel.

How to change the Database Engine (e.g. MyISAM -> InnoDB)

InnoDB is the default database engine since MySQL 5.5, before it was MyISAM.

To change the engine of a single or multiple tables you may use HeidiSQL (v8.0), select the table(s) via right click and choose "Mass-table-editor" from the menu.

Changing the engine automatically you may write a script using

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = <dbName> AND TABLE_NAME = 'MyISAM'

and

ALTER TABLE <dbName>.<tableName> ENGINE InnoDB

See also #3371 for further information.
8 (request: 33035)

mysql [Warning] Could not increase number of max_open_files to more than xxxxx (request: yyyyy)

Increase the limit defined in the systemd service unit:

vi /etc/systemd/system/mysql.service

and set LimitNOFILE= to at least the reported value (yyyyy), e.g.:

LimitNOFILE=34000

don't forget to reload the systemd daemon:

systemctl daemon-reload

Replication

see https://www.thomas-krenn.com/de/wiki/MySQL_Replikation

Updated by Andreas Müller almost 2 years ago · 36 revisions