mysql

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:

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:

/var/lib/mysql/mysql-slow.log
/var/lib/mysql/mysql-error.log

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

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'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

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

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.

Anyhow...

This is how we raise the ulimit

To check the limits:


ulimit -a


You will see something like:

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.

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.

Example:


[mysqld]
datadir=/new/location
socket=/new/location/mysql.sock


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

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


or


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


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';"


TROUBLESHOOTING

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


pidof mysqld
kill -9 NUMBEROFPROCESS

create a new file called 'drop_all_tables.sh'


vi drop_all_tables.sh


Export:

Import :

Connect:

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

__DB__

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...

So...

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:

my_database-2009-11-25-11_07_31.sql

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

In case your mysqld won't work with

do this: