Disable MySQL strict mode!
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
Read MoreAnother MySQL daemon already running with the same unix socket.
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. […]
Read MoreRepairing MySQL tables that will not open.
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: 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. mysqlcheck is similar in function to myisamchk, […]
Read MoreMySQL 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 […]
Read MoreSecuring 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: Verify your root MySQL account password is set 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 […]
Read MoreHow 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: 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 […]
Read MoreDowngrading MySQL to 4.x – cPanel
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 […]
Read MoreWrong Version Numbers in cPanel
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
Read More