Any idea why cpanel does not create databases / usernames in cpanel > mysql?

I have mysql 5 installed.

I had to change multiple account names through WHM. After changing account names I found out that MySQL databases with InnoDB screwed up, throwing following error:

Can't open file: 'example_config.ibd' (errno: 1)

I was googling alot on this matter but none of the fixes I found worked for me. So, to save the day here's what you got to do:

1) Change back to old username (using WHM's modify account)
2) Make dump of your database, add this in beginning of the dump: SET FOREIGN_KEY_CHECKS=0;
3) DROP the whole database (Dropping only tables might work, I havent tried it)
4) Change username to the new username (using WHM's modify account)
5) Restore database from the dump.
6) All set, your database should work just fine with no errors.

If this doesnt help you, take a look at this post in MySQL forums: http://forums.mysql.com/read.php?22,34050,52933#msg-52933

While not all datacenters do there are some that by default partition the system in a less then ideal manner. I have had to deal with quiet a few clients who have filled up their /var partition due to mysql being stored by default on the partition. This is a short little guide to show you how to move mysql to another parition on the system. I have another guide that shows you how to parition a drive for mysql but many times it is not possible to repartition a drive and it is a lot easier to simply move it to another live parition. I would make sure and leave mysql in the old place, or at least a backup copy, for a few days before deleting it to make sure all of the data was transfered fine. If you want to move to a partition other then /backup/ simply change backup to home or the partition name anyplace you see it. In addition to the space considerations moving to a different drive can also have a profound positive influence on the system performance.

MySQL is one of the most important programs on a server, unfortunatly it is also pretty resource intensive. One a server it is not uncommon for a single user or even a query to take up the bulk of the servers cpu cycles. Mytop is a very useful program to see what queries a server is currently processing as well as which user is executing them. Think of mytop as top for mysql. If you see a lot from a user that means they are probably the hog. Mytop can also be useful for figuring out exactly which queries are causing the problem in the case of a self-designed website. The following is how to install mytop on the server and run it. Thanks to carlos for the actual commands

MySQL Replication

 This guide is designed to help do the initial setup on a MySQL cluster in which multiple MySQL servers all serve the same content through the use of the replication function. We have successfully deployed this solution for multiple clients and it is a very good option for those needing a more powerful mysql solution. Carlos (theuruguayan) actually created this document so please direct any questions to carlos@totalserversolutions.com .

 Be sure your mysql servers are running the same version before starting this guide, yes, is possible to have a few combinations of master-slave versions, for more information about this you can check:

***WARNING***

***WARNING***

***WARNING***

***WARNING***

***WARNING***

***WARNING***

***WARNING***

Use this guide at your own risk! Fdisk is a very powerful tool and can easily screw up a server if you are not careful. This guide was designed around a server that has SCSI disks and it is mounted in the secondary positions, sdb. If you have a ide drive it would be hdb.

This guide is pretty vague, that was done on purpose. It is very hard to do something like this if you are not familiar with linux and fdisk. I do not want people screwing around with this if they are not confident in what they are doing. I am just trying to stop people from getting mad at me. Anyways below is the guide it is to be used totaly at your own risk but I can say that if done correctly it will work just fine.

That all say here is the actual guide.

fdisk /dev/sdb

The first step is to delete all of the old partitions. If you have a lot of partitions you are going to have to keep trying to go to higher numbers. Once you have deleted all partitions it will say there are no paritions.