In this post, I’ll explain some MySQL basics 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 for MySQL basics operations.
