MySQL in a nutshell

Introduction

MySQL is a relational database management system (RDBMS) based on SQL (Structured Query Language). I was first released in January, 1998.

In most cases the developers of database applications expect the systems administrator to be able to independently prepare a database for their applications to use. The steps to do this include:

1. Install and start MySQL.
2. Create a MySQL "root" user.
3. Create a regular MySQL user that the application will use to access the database.
4. Create your application's database.
5. Create your database's data tables.
6. Perform some basic tests of your database structure.

The /etc/my.cnf file is the main MySQL configuration file. It sets the default MySQL database location and other parameters. The typical home/SOHO user won't need to edit this file at all.According to the /etc/my.cnf file, MySQL databases are usually located in a subdirectory of the /var/lib/mysql/ directory. If you create a database named test, then the database files will be located in the directory /var/lib/mysql/test.

MySQL stores all its username and password data in a special database named mysql.

Creating a MySQL "root" Account

Only two steps are necessary for a brand new MySQL installation.

1. Make sure MySQL is started.
2. Use the mysqladmin command to set the MySQL root password. The syntax is as follows:

# mysqladmin -u root password new-password

MySQL has its own command line interpreter (CLI). You can access the MySQL CLI using the mysql command followed by the -u option for the username and -p

# mysql -u root -p

Creating a database

mysql> create database databasename;

Deleting a database

mysql> drop database databasename;

Adding user

You can add users and give privileges for user to access database using grant command.

Syntax:

mysql> grant all privileges on database.* to username@"servername" identified by 'password';

Example :
Creating a database named exampledb and creating a user named newuser with password newuser

mysql> create database exampledb
mysql> grant all privileges on exampedb.* to newuser@"servername" identified by 'newuser';
mysql> flush privileges;

For changing mysql password for the user username as root from mysql command prompt

mysql> UPDATE mysql.user SET Password = OLD_PASSWORD('newuser') WHERE Host = 'localhost' AND User = 'newuser';

How to Reset mysql root password

1. Stop mysqld
# /etc/init.d/mysql stop

2. Start mysql using skip grant tables option
# /etc/init.d/mysql start --skip-grant-tables --user=root

3. Connect to the mysqld server with this command:
# mysql -u root

4. Issue the following statements
mysql> UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';
mysql> flush privileges;

5. You should be able to connect using the new password.

Recovering / Changing Your MySQL Root Password

The steps you need are:

1) Stop MySQL
# service mysqld stop

2) Start MySQL in Safe mode with the mysqld_safe command and tell it not to read the grant tables with all the MySQL database passwords.

# mysqld_safe --skip-grant-tables --skip-networking &

3) MySQL is now running without password protection. You now have to use the familiar mysql -u root command to get the mysql> command prompt. ( -p flag is not required) As expected, you will not be prompted for a password.

# mysql -u root

4) You will now have to use the mysql database which contains the passwords for all the databases on your system and modify the root password. In this case we are setting it to ack33nsaltf1sh.

mysql> use mysql;

5) Exit MySQL and restart the mysqld daemon.

mysql> exit

# service mysqld restart

MySQL Database Backup

The syntax for backing up a MySQL database is as follows:
# mysqldump -u [username] -p[password] [database] > [backup_file]

example :
#mysqldump -u newuser -pnewuser example > example.sql

MySQL Database Restoration
#mysql -u [username] -p[password] [database] < [backup_file]

example :
#mysql -u newuser -pnewuser example < style="font-weight: bold;">Some useful mysql commands

List all your MySQL databases:
mysql> show databases;

Listing the data tables in MySQL database:
mysql> use databasename;
mysql> show tables;

example :
mysql> use exampledb;
mysql> show tables;

Viewing your mySQL database's table structure:
mysql> describe tablename;

Viewing the contents of a table:
mysql> select * from tablename limit 1;

-----------------------------------------------------------------------------------------------------------------------------------------