mysql

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.

Anyhow...

This is how we raise the ulimit

To check the limits:

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

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:

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:

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:

__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 :

__DB__-%F-%T

or for newer version of phpMyAdmin

@DATABASE@-%F-%T

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

Tags: Linux admin mysql