In this post, I’ll explain some basic MySQL tips that would be very useful for beginners
Reset MySQL root password
Log in as Root and Stop the MySQL daemon. Then Start the MySQL daemon and skip the grant tables which store the passwords.
# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables
# mysql -u root
mysql> use mysql;
Now you should be able to connect to mysql without a password. If not, it might be some other issues. Execute the command to reset the password
mysql> update user set Password=PASSWORD('new-password') where user='root'; mysql> flush privileges; mysql> exit
After this you need to kill the running mysqld and restart it normally.
Creation of MySQL database, user and access rights
It should be very easy to create a database through cpanel or some other panel. But if you are on your own with only Shell access, try this :
# mysql -u root -p mysql> use mysql; mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N'); mysql> flush privileges;
Or you can use
mysql> grant all privileges on databasename.* to username@localhost; mysql> flush privileges;
MySQLdump and Restore
The mysqldump client is a database backup. It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both.
Creating a Mysqldump is quite easy. If you know the database name, say database :
# mysqldump database > database.sql
When restoring a Database, make sure you login as the user. In other words, do not restore a database with root privileges. It may cause real damage. Assuming that you are logged in as the user
<strong> </strong> mysql> use dbname; //Which is the database which the backup has to be restored to mysql > source olddb.sql; //Backup
Repair a corrupted Database
Switch to the database directory which is having issues with, Like :
# cd /var/lib/mysql/database
Stop the MySQL server
# /etc/init.d/mysql stop
Check the tables
# myisamchk *.MYI
Repair the tables
# myisamchk -r *.MYI
Start the MySQL server
# /etc/init.d/mysql start
phpMyAdmin is an open source tool written in PHP intended to handle the administration of MySQL over the World Wide Web. It can perform various tasks such as creating, modifying or deleting databases, tables, fields or rows; executing SQL statements; or managing users and permissions.
If you are having a cPanel or WHM interface, its quite easy to manage a database. In cPanel interface, go to Databases section
This is the main phpMyadmin page.
In WHM, at SQL Services you can perform a variety of options. As mentioned earlier, the database repair option (for corrupted databases) can be performed here also. Access the option ‘Repair a Database’
Select the Database name and Click Repair Database.
These are quite simple tasks associated with MySQL operations.Continue Reading
Imagine you are accessing your Website WHM or cPanel or Webmail hosted (in your VPS) and what if you see this
How to solve this
Pre-Requisites : You should have Shell access (as root) to the Node server.
Mostly this happens due to inode issue. The inodes allocated to the server maybe full. inode is a data structure on a traditional Unix-style file system such as UFS. An inode stores basic information about a regular file, directory, or other file system object.
Say we are logged into the Node Server. There we are going to list all the Containers existing on the Hardware Node. By default, only running Containers are shown.
[root@linuxvps1 ~]# vzlist -a CTID NPROC STATUS IP_ADDR HOSTNAME 100 18 running 22.214.171.124 WEB.EXAMPLE.COM 101 63 running 126.96.36.199 - 102 169 running 188.8.131.52 server2.example.com 103 81 running 184.108.40.206 example.net 78965 236 running 220.127.116.11 server1.example.com
Assume your container is 103. So simply enter into 103 by this command
[root@linuxvps1 ~]# vzctl enter 103
Check if the inodes are full in the container
[root@linuxvps1 ~]# df -i
Most Probably it should display something like this :
Filesystem Inodes IUsed IFree IUse% Mounted on /dev/vzfs 200000 200000 0 100% / none 65536 95 65441 1% /dev
Bang ! The inodes on /dev/vzfs mounted on / are full. You’ve to go nowhere else to see why that error was delivered. A simple solution will work out, you’ve to increase the inode limit. You’ve two options, either through Shell or through the Node Control Panel (like Parallels Infrastructure Manager)
First we will go through Shell mode with this command :
$ vzctl set veid --diskinodes softlimit:hardlimit
Where ‘veid’ is the VPS ID
root@linuxvps1 ~]# vzctl set 103 --diskinodes 7680100:7680200
Second we are going for Node Control Panel. In the infrastructure, you will see your VPS with a ! sign. That indicates your VPS is having some sort of issue.
So you’ve seen the Alert. Next access the VPS, go to Resources tab. I’m sorry to tell you that I grabbed this screenshot after resolving the issue, but it will help you anyway
You should see the ‘Disk Inodes’ is full under ‘Disk Usage’ category (Here it is not !). Next Click Configure to settle this one
See the Highlighted part, the inodes should be limited to some size. Increase it or remove the check mark to make it unlimited. Make sure you save the changes. Now try again accessing cpanel/WHM/Webmail. It should load with out any issues.That makes a smile in your face, isn’t it ?Continue Reading
As usual, start by checking the error log of cPanel at
2009-05-23 15:44:24 info [whostmgr2]  Waiting for lock on /var/cpanel/package-limits.yaml held by /usr/local/cpanel/whostmgr/bin/whostmgr2 – locking /var/cpanel/package-limits.yaml with pid 25968
2009-05-23 15:44:25 info [whostmgr2]  Removing expired lock file /var/cpanel/package-limits.yaml.lock
(internal warning while parsing [stdin]) Sat May 23 23:41:28 2009  warn: Attempt to free unreferenced scalar.
While the error (internal warning while parsing [stdin]) Sat May 23 23:41:28 2009  warn: Attempt to free unreferenced scalar floods the error log
Permissions were all right, but suddenly this started happening, but only x86_64 architectures only. Reason being the cPanel is killing the process, since it exceeded the memory usage allowed by cPanel.
Solution would be to increase the memory limit of cPanel by following the instructions below.
WHM -> Server Configuration -> Tweak Settings Choose the setting for:
The maximum memory a cPanel process can use before it is killed off (in megabytes). Values less than 128 megabytes can not be specified. A value of “0″ will disable the memory limits.
The settings had 256M there, and increasing the value to 1024 M helped. Server had 8G RAM, so the value of 1024M was fine here.Continue Reading
With cPanel 11, this has become very easy. Assuming that you have mod_proxy and mod_rewrite installed on your apache,
Go to WHM –> Tweak Settings –> Check the following settings
Add proxy VirtualHost to httpd.conf to automatically redirect unconfigured cpanel, webmail, webdisk and whm subdomains to the correct port (requires mod_rewrite and mod_proxy)
Automatically create cpanel, webmail, webdisk and whm proxy subdomain DNS entries for new accounts. When this is initially enabled it will add appropriate proxy subdomain DNS entries to all existing accounts. (Use /scripts/proxydomains to reconfigure the DNS entries manually)
Once enabled let the /scripts/proxydomains run and it will be enabled for all existing domains and new ones to be created. For a server with DNS cluster enabled, please refrain from enabling this, as it requires reload of those many zones of domains in the shared server at your DNS server.Continue Reading
You may have already know about the following option: Main –> Server Configuration –> Tweak Settings –> The maximum each domain can send out per hour
We recommend you to set it as low as possible. And then make per domain restrictions as below, to increase the numbers.
And you are done!!!Continue Reading