ERROR 1040 (08004): Too many connections

Issue : I got the above error while loading website on the browser.

ERROR 1040 (08004): Too many connections

Solution :

The issue is due to the maximum mysql connections set on the configuration file. You can change it on the my.cnf file like pasted below.

1. Open the file /etc/my.cnf and the following line.

max-connections=500

2.Restart the mysql service.

3. Try to load the website now and increase the value if the issue still persist.

That’s all….!!!!

MySQL: Can’t create/write to file ‘/backup/tmp/#sql_3c6_0.MYI’ (Errcode: 2)

Issue: I got this error when accessing the certain pages of drupal website.

MySQL: Can’t create/write to file ‘/backup/tmp/#sql_3c6_0.MYI’ (Errcode: 2)

Solution:

Check the location of tmp directory inside my.cnf and change it to /tmp

1.check mysql config : my.cnf

cat /etc/my.cnf | grep tmpdir

2.If the tmpdir is missing or /tmp is not mentioned there, please add it.

i.e; tmpdir=/tmp to my.cnf under [mysqld]

3.Restart the mysql server

/etc/init.d/mysql restart

That’s all……..!!!!

/usr/sbin/mysqld: Table ‘./cphulkd/logins’ is marked as crashed and should be repaired

Issue : I got the above error from mysql log file and the Innodb on the server is not working.

Solution:

There are several potential reasons why the “cphulkd” database can become corrupted. It’s possible the database was not shut down successfully, or MySQL was killed during a write. The best way to resolve this issue is to simply repair the database.

First check if the cphulkd databse is corrupted using the following command.

#mysqlcheck cphulkd

If the check shows errors, please repair it

# mysqlcheck -r cphulkd

Now try to start mysql on the server with Innodb

That’s all….!!!

Replace MySQL with PerconaDB on a Centos server

Percona Server is an enhanced drop-in replacement for MySQL.

Features of PerconaDB
>> Your queries will run faster and more consistently.
>> You will consolidate servers on powerful hardware.
>> You will delay sharding, or avoid it entirely.
>> You will save money on hosting fees and power.
>> You will spend less time tuning and administering.
>> You will achieve higher uptime.
>> You will troubleshoot without guesswork.

Some more,
>> Higher number of INFORMATION_SCHEMA Tables
>> Higher number of Global Performance and Status Counters
>> Per-Index Performance Counters
>> Per-User Performance Counters
>> Per-Client Performance Counters
>> High-Resolution Process List Timing
>> Fast Checksum Algorithm
>> Buffer Pool Pre-Load
>> Support for FlashCache
>> Import Tables From Different Servers

Steps to replace MySQL with Percona
—————

1.First remove the current mysql packages.

# yum remove -y mysql*

2. Create Yum repository for PerconaDB

# vi /etc/yum.repos.d/percona.repo

Add the following entries,

[percona]
name = CentOS $releasever – Percona
baseurl=http://repo.percona.com/centos/$releasever/os/$basearch/
enabled = 1
gpgkey = http://www.percona.com/redir/downloads/percona-release/RPM-GPG-KEY-percona
gpgcheck = 1

3.Remove php from the exclude section of /etc/yum.conf file

4. Install PerconaDB-sever, PerconaDB-client, PerconaDB-devel

# yum install Percona-Server-client-55 Percona-Server-server-55 Percona-Server-devel-55

5.Start MySQL

# /etc/init.d/mysql start

You will get a result like pasted below.

—————-
[root@test csf]# /etc/init.d/mysql start
Starting MySQL (Percona Server).
.. SUCCESS!
[root@test csf]#
—————–

6.Then, run mysql_upgrade

# mysql_upgrade

7. Restart MySQL

# /etc/init.d/mysql restart

Starting MySQL. ERROR! The server quit without updating PID file

Issue: I got this error while restarting mysql service

solution:

You just needs to add the below line in /etc/my.cnf

[mysqld]
innodb_force_recovery = 1

Please restart mysql now.

If this doesn’t help remove the entry and add the below in my.cnf file

[mysqld]
skip-innodb

Restart it now.

That's all......!!!

Change collation for the entire database

If you need to change the collation for example : utf8_unicode_ci to utf8_general_ci , Please do the following steps.

#mysqldump database > mysql.sql

ex: # mysqldump afsal_jo151 > afsal_jo151_bkp.sql

#replace “utf8_unicode_ci” ” utf8_general_ci” — database backup file

ex:# replace “utf8_unicode_ci” ” utf8_general_ci” — afsal_jo151_bkp.sql

mysql databasename < mysql.sql

ex: mysql afsal_jo151 <  afsal_jo151_bkp.sql

Please check it now through PhpMyadmin

Thats all……!!!!

MySQL storage engines

MySQL supports several different types of Table Engines also known as “Table Types”. A database can have its tables being a mix of different table engine types or all of the same type.

Most of us are using Mysql database and majority don’t know how to choose the data base engines, what are the different types of storage engines available in mysql and how they differ from each other. In this article let me give you a brief idea about the Storage Engines and what are the limitations and where to use these various storage engines. One of the greatest things about MySQL, other than being free, widely supported and fast, is the flexibility of choosing different storage engines for different tables.

The default storage engine for MySQL prior to version 5.5 was MyISAM. For MySQL 5.5 and later, the default storage engine is InnoDB. Choosing the right storage engine is an important strategic decision, which will impact future development.

MySQL supported storage engines:

• MyISAM
• InnoDB
• Memory
• CSV
• Merge
• Archive
• Federated
• Blackhole
• Example

Before taking any decision about which engine we need to choose, first we need to think about the different core functionality provided by each engine that allow us to differentiate between them. We can divide up the core functionality into four areas; the supported field and data types, locking types, indexing and transactions. Some engines have unique functionality that can also drive your decision.

Locking

Locking within database engines defines how access and updates to information are controlled. When an object in the database is locked for updating, other processes cannot modify (or in some cases read) the data until the update has completed.

Locking not only affects how many different applications can update the information in the database, it can also affect queries on that data. The reason for this is that the queries may be accessing data that may be being altered or updated. In general, such delays are minimal. The bulk of the locking mechanism is devoted to preventing multiple processes updating the same data. Since both additions (INSERT statements) and alterations (UPDATE statements) to the data require locking, you can imagine that multiple applications using the ame database can have a significant impact.

Indexing

Indexing can dramatically increase the performance when searching and recovering data from the database. Different storage engines provide different indexing techniques and some may be better suited for the type of data you are storing.Some storage engines simply do not support indexing at all either because they use the indexing of the underlying table or because the data storage method does not allow indexing.

Transactions

Transactions provide data reliability during the update or insert of information by enabling you to add data to the database, but only to commit that data when other conditions and stages in the application execution have completed successfully. For example, when transferring information from one account to another you would use transactions to ensure that both the debit from one account and the credit to the other completed successfully. If either process failed, you could cancel the transaction and the changes would be lost. If the process completed,then we would confirm it by committing the changes.

Reference Link : http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html

The two most commonly used on most Cloud Sites MySQL servers are Innodb and MyISAM engines.

MyISAM

The MyISAM engine is the default engine in most MySQL installations and is a derivative of the original ISAM engine type supported in the early versions of the MySQL system. The engine provides the best combination of performance and functionality, although it lacks transaction capabilities (use the InnoDB or BDB engines) and uses table-level locking.

Unless you need transactions, there are few databases and applications that cannot effectively be stored using the MyISAM engine. However, very high-performance applications where there are large numbers of data inserts/updates compared to the number of reads can cause performance problem for the MyISAM engine. It was originally designed with the idea that more than 90% of the database access to a MyISAM table would be reads, rather than writes.

With table-level locking, a database with a high number of row inserts or updates becomes a performance bottleneck as the table is locked while data is added. Luckily this limitation also works well within the restrictions of a non-transaction database.

InnoDB

Compared to MyISAM, InnoDB provides many more feature to increase performance. There is some additional time spent during initial setup, as opposed to MyISAM, but the benefits far outweigh the time spent. One major difference is the ability to do row-level locking, as opposed to table-level locking, to increase performance time. This allows parallel INSERT/UPDATE/DELETE queries to be ran on the same table, unlike MyISAM where each query has to wait its turn to run.

Additionally, InnoDB provides foreign key functionality. This allows you to ensure that dependent data in table one is present before inserting data into table two. Likewise, it prevents data in table one from being deleted if there is data in table two that depends on it.

InnoDB also provides caching for data and indexes in memory, as well as on disk, which provides a large increase in performance gain. For those low on RAM this might not be the ideal solution, but with memory as cheap as it is this is becoming less of an issue.

MyISAM	InnoDB
Simple to design less strict in data integrity check Implements table level lock for inserting and updating. No transactions No relationship constraints and foreign keys Poor at recovering data integrity while system crashes Full-text search index Faster in read-intensive	Complex More strict in data integrity check Implements row-level lock for inserting and updating Has transactions Has foreign keys and relationship constraints Better   crash  recovery  of data  at   system crashes No full-text search index Faster in write-intensive

Issue : While restoring a cpanel account, got the following error “Account Creation Status: failed (Sorry, a mysql user with the name already exists”

1.Kill the account using the command ” /scripts/killacct username ”

2. Please try to restore it now. If you are still facing the same error please confirm no mysql user with the same username exist. You can check it from the following file”/var/cpanel/databases/users.db”. If the user is listed there, remove it.

Thats all……….!!!!!!!

Issue: While logito the Phpmydmin :1045 CANNOT LOG IN TO THE MYSQL SERVER

Fix : reset the password for cpanel (click the option Allow MySQL password change)