How to set max_open_files in MariaDB / MySQL in CentOS 7

Set the system wide open file limit:

vi /etc/security/limits.conf

Change/Add the following:

* soft nofile 1024000
* hard nofile 1024000
* soft nproc 10240
* hard nproc 10240

Now do this for /etc/sysctl

vi /etc/sysctl

Add the following

fs.file-max = 1024000

Set the changes

sysctl -w fs.file-max=1024000
sysctl -p
# check changes
cat /proc/sys/fs/file-max

Set the mysqld.service limit (as settings here will override *.cnf ones)

Set both /etc/systemd/system.conf and /etc/systemd/user.conf

vi /etc/systemd/system.conf
vi /etc/systemd/user.conf

Add the following under [Manager] for both:

Tags: Linux mysql mariadb

How to diagnose slow mysql performance

If you're experiencing slow mysql performance--top showing mysqld high cpu usage. This command will help you track down what's going on at that specific time.

mysql -e "show processlist"

Of course you should also check your log files:


You'll also want to optimize your /etc/my.cnf: For a pretty good quick start, you can use this tool, Percona Configuration Wizard for MySQL

Tags: mysql Server Linux

How to convert database from innodb to InnoDB in phpMyAdmin?

In phpMyAdmin, click the "SQL" tab and paste the following in there--changing your_database to the actual database you want to change.

SET @DATABASE_NAME = 'your_database';

SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ENGINE` = 'innodb'
ORDER BY table_name DESC;

Or if that doesn't work, do the following under SSH (change your_database to your actual database name)

Tags: mysql Server admin

How to permanently raise ulimit 'open files' and MySQL 'open_files_limit'

The default ulimit (maximum) open files limit is: 1024--Which is very low, especially for a web server environment hosting multiple heavy database driven sites.

This ulimit 'open files' setting is also used by MySQL. MySQL automatically sets its open_files_limit to whatever the system's ulimit is set to--at default will be 1024.

NOTE: MySQL can NOT set it's open_files_limit to anything higher than what is specified under ulimit 'open files'--you can set it lower, but not above the 'open files' limit.


This is how we raise the ulimit

To check the limits:

ulimit -a

You will see something like:

Tags: Linux admin mysql

How to optimize and auto repair all MySQL databases using SSH

Here's a simple command to optimize and auto repair all tables in your MySQL database.

mysqlcheck -uroot -p --auto-repair --optimize --all-databases

This is the same thing as using PHPMyAdmin, however, you don't need to do this one by one for each database, select all tables, and optimize--and not to mention if you wanted to repair a database prior to optimizing, it will take 2x as long.

Tags: mysql Server Linux database

How to move MySQL database -- the simple way

There are plenty of articles about how to move mysql to another directory or partition by telling you to mess with the my.cnf by changing all the references of datadir and socket to the new location.



Don't do this... Not only will it waste your time, but likely won't work for half the server setups. Reason being there are other factors at play when you're changing things, it's not only the my.cnf that you have to worry about. For example, if you're using Ubuntu, you may be using AppArmor and you'll also have to specify the new directory in /etc/apparmor.d/usr.sbin.mysqld

Tags: mysql Server Linux database

MySQL dump all databases via mysqldump -- the proper way

Here's a command that properly dumps all databases, in case you wanted to restore a full backup.

For whatever reason, many sites are using improper characters ( ' –– ' , ' – ' ) to display this mysqldump command line--copy pasting will throw up an error stating something like:

mysqldump: Got error: 1049: Unknown database '??extended-insert' when selecting the database

Here is a sample of wrong characters, which appear just fine, but won't work:

mysqldump ––extended-insert ––all-databases ––add-drop-database ––disable-keys ––flush-privileges ––quick ––routines ––triggers > all-databases.sql


mysqldump –extended-insert –all-databases –add-drop-database –disable-keys –flush-privileges –quick –routines –triggers > /root/all-databases.sql

Tags: mysql linux server admin

Flush MySQL log-bin data and disable binary log files

If your disk is starting to run out of space due to an excess of mysql-bin.XXXXXXXX files here's what to do to fix that.

vi /etc/my.cnf

under 'log-bin=mysql-bin' (the destination could be different in your my.cnf) put:

expire_logs_days = 10

Change 10 to whatever you're comfortable with.

or you can simply disable the binary log files by uncommenting the line:

# log-bin=mysql-bin

You'll also want to remove all the excessive binary log files like so:

mysql -u root -p -e "PURGE BINARY LOGS BEFORE '2012-02-15 09:00:00';"


In case you can't shutdown mysqld do the following:

pidof mysqld

Tags: Server how-to mysql

phpMyAdmin database export "Save as file" template

Whenever you do a database backup using phpMyAdmin, you are presented with a default file name structure for saves, usually like so:


which basically gives you the name of the database you just backed up. Which is ok, however, most people would also like the date and time stamp included as well...


This is how it's done... replace the above with :


or for newer version of phpMyAdmin


__DB__ = name of database Example: my_database
%F = Same as "%Y-%m-%d" (year-month-day) Example: 2009-11-25
%T = time stamp . (24 hour format, hour_minute_second) Example: 11_07_31

which will save a file with the naming structure similar to:


You can easily use other date/time structure, to suit your personal preference.

Tags: Linux admin mysql