mysql

Disable MySQL strict mode!

by

We recently migrated WHMCS to latest CentOS 7 server with cPanel. The WHMCS ClientArea showed White screen when “Client logins were used”. We found that the server had MySQL strict mode enabled which was causing this problem.

I disabled MySQL strict mode as below :

Edited /usr/my.cnf and changed below value :

sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER

Restarted MySQL :

/scripts/restartsrv mysql

voila this worked…… earlier I was trying to change sql_mode under /etc/my.cnf however found MySQL is using different location /usr/my.cnf

Another MySQL daemon already running with the same unix socket.

by

After upgrading MySQL binaries mysqld will not start at all any more and shows below error :

CT-1977-bash-4.1# service mysqld start
Another MySQL daemon already running with the same unix socket.
Starting mysqld: [FAILED]
CT-1977-bash-4.1#

MySQL service does not shut down gracefully during the OS reboot, leaving the old /var/lib/mysql/mysql.sock such that mysqld will not start up. Some people were able to reproduce this error on a CentOS 6.5 KVM guest virtual system by rebooting the host CentOS 6.5 system. CentOS is supposed to gracefully shut down the guest systems, but this seems to be failing in the case of mysqld.

Confirmed Red Hat Linux 6.5 bug – https://bugzilla.redhat.com/show_bug.cgi?id=1037650

Issue discussion on MySQL bug tracker – http://bugs.mysql.com/bug.php?id=71086

Simple steps to reproduce this issue:

service mysqld start
killall -9 mysqld_safe mysqld
service mysqld start

A quick way to restart MySQL is as below :
Remove socket file and restart mysql:
CT-1977-bash-4.1# ls -la /var/lib/mysql/mysql.sock
srwxrwxrwx 1 mysql mysql 0 Jan 8 20:13 /var/lib/mysql/mysql.sock
CT-1977-bash-4.1# rm /var/lib/mysql/mysql.sock
CT-1977-bash-4.1# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
CT-1977-bash-4.1#

A work around for the issue is to modify ‘/etc/init.d/mysqld’ script:

Make a backup copy of the startup script.
cp -p /etc/init.d/mysqld /etc/init.d/mysqld.orig

Edit the file /etc/init.d/mysqld to look as below :
=========================
# if fuser "$socketfile" &>/dev/null ; then
# echo "Socket file $socketfile exists. Is another MySQL daemon already running with the same unix socket?"
# action $"Starting $prog: " /bin/false
# return 1

# We check if there is already a process using the socket file,
# since otherwise this init script could report false positive
# result and mysqld_safe would remove the socket file, which
# actually uses a different daemon.
if fuser "$socketfile" &>/dev/null ; then
echo "Socket file $socketfile exists. Is another MySQL daemon already running with the same unix socket?"
action $"Starting $prog: " /bin/false
return 1
fi
=========================

Now restart MySQL :
CT-1977-bash-4.1# service mysqld start
Starting mysqld: [ OK ]
CT-1977-bash-4.1#

Repairing MySQL tables that will not open.

by

This should be handled on a case by case basis, but if you are using the default MySQL table type of MyISAM (which is the default storage engine unless changed or specified differently) here are a few options:

  1. The myisamchk utility can be run from a command line to checks, repairs, or optimizes tables. It is normally run while the database is not running. For more information about myisamchk visit the MySQL website.
  2. mysqlcheck is similar in function to myisamchk, but can be run while the database is running. For more information about
    mysqlcheck visit the MySQL website.
  3. If you login to the database, you can also run sql commands that might fix your problem.
    Examples:
    mysql> optimize table your-tablename;
    mysql> analyze table your-tablename;
    mysql> repair table your-tablename;
    For more information about
    table maintenance SQL visit the MySQL website.
  4. If you are getting MySQL error numbers and are not sure what they are. From a command line you can use the perror utility to lookup errors. For more info on perror visit the MySQL website.
    Examples:
    shell> perror 13 64
    Error code 13: Permission denied
    Error code 64: Machine is not on the network

MySQL Optimization / Repair Information

by

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.

by

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?

by

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:

cp –Rp /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.

Downgrading MySQL to 4.x – cPanel

by

If you’ve upgraded MySQL via WebHost manager, the downgrade process is slightly more complex than the upgrade. This tutorial mainly covers a downgrade from 4.1 to 4.0, but also applies to downgrading from 5.x to 4.x.

*Back up all databases before proceeding, as all existing databases will be deleted!

1. Uninstall existing MySQL packages:

rpm -qa | grep -i mysql-

This should return a list of several rpm’s installed. You will need to remove them all with the rpm -e command .

2. Delete the active MySQL directory:

rm -Rfv /var/lib/mysql

3. Get cPanel Ready

Edit /var/cpanel/cpanel.config and change the version to the one that you are downgrading to (4.0, 4.1, etc)

mysql-version=4.0

Make sure that MySQL updates aren’t being skipped:

rm /etc/mysqldisable
rm /etc/mysqlupdisable

4. Install MySQL:

/scripts/mysqlup –force

5. Update the perl module:

/scripts/perlinstaller –force Bundle::DBD::mysql

Finally, recompile Apache and PHP via Web Host Manager.

Wrong Version Numbers in cPanel

by

It’s common that when moving a cPanel account between servers with differing software versions, the version numbers (specifically MySQL) on the side of cPanel still reflect those of the old server. This can be changed by editing the version files for the account that was moved:

The files holding the version numbers are located in:

cpanel 10: /home/username/.cpanel-datastore/

cpanel 11: /home/username/.cpanel/datastore/

The following files control the version display in user cPanels:

Apache Version:

_usr_local_apache_bin_httpd_-v

Perl Version:

_usr_bin_perl_-v

MySQL Version:

_usr_sbin_mysqld_–version