MySQL Optimization / Repair Information

How MySQL Uses Memory
This page lists some of the ways that the mysqld server uses memory, and associated mysqld variable names
Memory Use MySQL 5.0
Memory Use MySQL 4.1

MySQL Optimization which covers:
– Optimization Overview
– Optimizing SELECT and Other Statements
– Locking Issues
– Optimizing Database Structure
– Optimizing the MySQL Server
– Disk Issues
Optimization MySQL 5.0
Optimization MySQL 4.1

MySQL Server Variables – SQL layer or Storage Engine specific.
List some of the more common variables as well as a brief description
Go to article 1
Go to article 2

Optimizing the mysqld variables by Ian Gilfillan
Great article on MySQL optimization, including some guidelines on what you should set mysqld server variable too.
(key_buffer_size, Query cache variables, table_cache, sort_buffer, etc..)
Go to article

Repairing Database Corruption in MySQL by Ian Gilfillan
Table corruption should be rare when using MySQL, however it helps to know how to fix the problem when it does occur.
Go to article

Optimizing MySQL: Queries and Indexes by Ian Gilfillan
The database is just too slow. Queries are queuing up, backlogs growing, users being refused connection. Management is ready to spend millions on “upgrading” to some other system, when the problem is really that MySQL is simply not being used properly. Badly defined or non-existent indexes are one of the primary reasons for poor performance, and fixing these can often lead to phenomenal improvements.
Go to article

Other MySQL Articles by Ian Gilfillan

Securing MySQL.

Due to differing needs and requirements this is difficult to answer except on a case by case basis. The MySQL website has a section regarding general security of a MySQL database available here: http://dev.mysql.com/doc/refman/5.0/en/security.html

Additionally some good practices are:

  1. Verify your root MySQL account password is set
  2. the test account and database that were created during the initial installation
    • Login to mysql as root, from the command prompt “shell> mysql –u root –p mysql” and enter the password when prompted
    • mysql> drop database test;
    • mysql> use mysql;
    • mysql> delete from user where user=’test’;
    • mysql> delete from user where user=”;
    • mysql> flush privileges;
  3. Make sure that each account has a password set
  4. Do not grant global privileges unnecessarily
  5. Avoid using wildcards in the hostname value associated with accounts
  6. Periodically review users and databases that are setup in MySQL
  7. Do not use passwords on the command line. From the command line you can login to MySQL using “shell> mysql –u root –password=somepassword mysql” the problem with this is anyone on the server could view your password with a simple process list command “shell> ps”. The correct usage would be: “shell> mysql –u root –p mysql”, from this MySQL will prompt your for your password and it will not show up in the process list as plain text.

Note: There are many excellent articles available on the web for MySQL security. Go to your search engine of choice and search “securing mysql” and you should have reading for weeks.

How do I backup MySQL in Linux?

1. Copying from the mysql directory

By default, MySQL databases on servers that use Linux are stored in the following directory:

/var/lib/mysql/

If you shut down the mysqld service first, you can copy your databases to an example /backup directory using the following command:

cpRp /var/lib/mysql/*.* /backup

The –R switch for the cp command means recursive, which you want to use because each database is in a separate directory. The –p switch is for permissions, which will maintain the permissions of what is copied.

You generally want to shutdown the mysqld service before using the above method because if a database is copied while it is actively being used, the resulting backup will be corrupt and therefore worthless. If you are certain none of the databases are not being used at the time, you can use the above command.

2. The mysqldump command

The mysqldump command lets you back up both individual databases and all databases on a server without having to shutdown the mysqld service. Because of this ability to make backups while still keeping databases online, this method is preferred.

Individual databases

An example command that would let you back up a database named example to the directory /backup while logged in as root is as follows:

mysqldump example > /backup/example_backup.sql

Unless it is a small database, it is recommended that you then compress the resulting database backup in order to reduce the amount of time necessary to transfer the backup. The following command would compress the backup of the example database:

tar czvf /backup/example_backup.tar.gz /backup./example_backup.sql

All databases

If you have numerous databases and backing all of them up individually would be too time consuming, the following command will backup all MySQL databases on your server to the /backup directory:

mysqldump -A > /backup/databases.sql(or –all-databases)

The –A switch (“-all-databases” performs the same function) will dump any and all databases on the server.

Linux Memory Management

Scenario: Customer is concerned that only 100MB of their 2GB of memory is as unused or free when running the free command. However, no processes appear to be consuming large amounts of memory, and the server is not running slow.

Cause: This is not a problem, but rather a result of the way linux manages its memory. On boot linux will typically display a large amount of free memory, as no processes have started to address it yet. Once processes run, Linux will cache that memory so it is quickly addressed for the next session. What this means is that on *most* linux distros, you’ll notice that a very small amount of memory is free, even though the machine is having no problems processing data (unlike, for instance a Windows server that would be quite slow with 150K of “free” memory). The best way to judge if the server is running low on memory, is if the swap space is being addressed. If the swap is occasionally hit, using a very small amount of memory, there is no cause for concern as that space will still be addressed. However, if a large amount of swap space is being used (50% or more) then the client may want to consider a memory upgrade.

Basically, the free memory isn’t the amount of memory that is not doing something, it’s the sum total of LowMem and HiMem that he kernel has left to address.

Mailbox unavailable or not local error messages – Plesk

If you receive this error when adding a domain:

Unable to send notification: mail() failed: SMTP server response: 550 Requested action not taken: mailbox unavailable or not local

then you need to add a valid mailbox to your Plesk admin setup. To resolve this issue log into Plesk and click on:

Server
Edit (under personal information)
Replace the email address with a valid, working address.

Keep in mind this error shouldn’t prohibit you from creating a site, it is just notifying you that the “site created” email will not be sent.

How to add GD support to PHP on a Linux server with Plesk

Adding GD graphic libraries support to php on your linux server with Plesk is relatively simple.

First, log into your server using ssh to get to a command line prompt.

Second, type the following command to install a version of php with GD support:

up2date php-gd

Third, restart your web service with this command:

service httpd restart

That is it. Your web server is now runing php with GD.

How do I retrieve the Plesk control panel password?

You can always get the Plesk password if you have Administrative access to the server. If you have also lost administrative access to the server, refer to [How do I reset the password on my server]

For Windows,

1. From the Start menu, select Run.
2. Enter the following command:
“C:\Program Files\SWsoft\Plesk\admin\bin\plesksrvclient.exe” -get

For Linux,
1. Via SSH, run the following command:
cat /etc/psa/.psa.shadow

Assigning Name server IPs manually via SSH – cPanel

Currently cPanel will start with eth0 and work through eth1 when you assign nameservers to your IPs via WHM. Unfortunately, this will automatically choose your management IP range first, which of course are non-routable. There are two work-arounds for this:

1: Create a fake nameserver record on your private IPs, and add an A record for that nameserver to your DNS. This is the fastest option, but not the best.

2: SSH to your server and edit /etc/nameserverips manually. An example file is blelow:

10.x.x.x=0
128.177.x.x1=ns1.my_cpanel_nameserver.com
128.177.x.x2=ns2.my_cpanel_nameserver.com
128.177.x.x3=0
128.177.x.x4=0
128.177.x.x5=0

Just replace the ‘0’ after the ‘=’ with your nameserver name. In this example, the first IP (10.x.x.x) is the management IP, and not used for nameservers. The next two IPs are public and assigned to two nameservers. The last three are public and not in use.

How can I compress my CPanel domlogs log files?

Before setting up compression of your logs you will want to have cpanel run the stats as often as possible. This is so you do not miss any stats that would have been included were your logs not compressed before stats run.

To do this, login to WHM, and click on Tweak Settings under Server Configuration in the left menu. Scroll down to Stats and Logs, and then in the field next to “Number of days between processing log files and bandwidth usage (default 1, decimal values are ok):” enter ‘.5’.

This will cause your logs to be run twice a day, and should allow you to have up to date stats.

After doing this, we can begin to setup your domlogs compression. You will need to open an ssh shell to your server to complete this.

Change directories to /etc/logrotate.d/ and find the file named httpd. You will need to copy this file to a new file in the same directory:

root@cpdemo [/etc/logrotate.d]# cp httpd domlogs
root@cpdemo [/etc/logrotate.d]# ls -la domlogs
-rw-r–r– 1 root root 390 Nov 8 21:26 domlogs
root@cpdemo [/etc/logrotate.d]#

Now you will want to edit your new file to contain the following:

/usr/local/apache/logs/*.com /usr/local/apache/logs/*.org /usr/local/apache/logs/*.net {
missingok
notifempty
size=200M
rotate 2
compress
sharedscripts
postrotate
/bin/kill -HUP `cat /usr/local/apache/logs/httpd.pid 2>/dev/null` 2> /dev/null || true
endscript
}

Just save your new file and you are done. Most linux distributions come default with a logrotate cronjob running nightly that will work with this configuration.