MySql & MariaDB » History » Revision 23
« Previous |
Revision 23/36
(diff)
| Next »
Andreas Kohlbecker, 07/10/2018 10:21 AM
MySQL FAQ¶
- Table of contents
- MySQL FAQ
see also MySQLMaintenanceScripts for further MySql related help
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
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
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
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.
Replication¶
Updated by Andreas Kohlbecker almost 6 years ago · 23 revisions