MySQL for Beginners

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

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.

How to resolve : Internal Server Error when accessing cpanel/WHM/Webmail

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.

Steps

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    100.20.11.111  WEB.EXAMPLE.COM
101     63  running    100.20.12.203  -
102     169 running    100.20.92.182  server2.example.com
103     81  running    100.20.56.169  example.net
78965   236 running    100.20.77.204  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 ?

Adding packages from WHM or editing the reseller privileges is not working on a x86_64 architecture

As usual, start by checking the error log of cPanel at /usr/local/cpanel/logs/error_log

2009-05-23 15:44:24 info [whostmgr2] [28346] 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] [28346] Removing expired lock file /var/cpanel/package-limits.yaml.lock
(internal warning while parsing [stdin]) Sat May 23 23:41:28 2009 [25968] warn: Attempt to free unreferenced scalar.

While the error (internal warning while parsing [stdin]) Sat May 23 23:41:28 2009 [25968] 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.

Howto enable access to whm, cpanel subdomains (proxy subdomains) in cPanel

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.

In cPanel, howto make exceptions in the email sending limit, per domain ?

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.

1. Login to your server
2. Edit /var/cPanel/maxemails
3. Add a new line saying “domain.com = increased_number” (eg: supportsages.com = 800)
4. Save the file.
5. And finally run /scripts/build_maxemails_config

And you are done!!!