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.

Internal server errors while accessing Fantastico and phpmyadmin in cPanel & WHM

Not a common issue. But sometimes this happens. If you have noticed, when you are logged into cPanel or WHM using the root password, you wont be able to install Fantastico applications or access MySQL DBs using phpmyAdmin. Most of the times, the error occurs will be similar to below

Internal Server Error

Premature end of script headers: php: Please check /usr/local/cpanel/logs/error_log for the exact error.

So far, the solution of this issue didn’t get complicated and a simple recompilation of cPanel PHP used to solve the error. To recompile cPanel PHP, issue the command below

/scripts/makecpphp

This would make sure that cPanel PHP, suPHP and other permissions will get set properly. Good luck.