MySql & MariaDB¶
This page and its subpages are a meaned to exchange knowledge and hints about MySQL
- Table of contents
- MySql & MariaDB
- ApacheMySQLAuthentication
- MySqlCharactersetAndCollation
- MySQLInstallDebianEtch
- MySQLMaintenanceScripts
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¶
- 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:
- http://dev.mysql.com/doc/refman/5.1/en/too-many-connections.html
- http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_open-files-limit
- http://dev.mysql.com/doc/refman/5.1/en/linux-installation.html
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
For Debian-based distributions:
- 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
- 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¶
Updated by Andreas Müller almost 2 years ago · 36 revisions