RED HAT WEBSITE

Mysql Startup Tutorial

Common Required Packages

Following RPM are required to setup a normal Mysql database server.

1. mysql-server
2. mysql
3. php-mysql (if php is going to access mysql)

Use rpm command to install these packages.

Eg:

[root@tmp server]# rpm -ivh mysql-server-5.0.77-4.el5_4.2

Configuration file

/etc/my.cnf

The /etc/my.cnf file is the main MySQL configuration file. It sets the default MySQL database location and other parameters.

Port

Mysql default port is 3306

Setting up root password
First Time

mysqladmin -uroot password $MYSQLPW

MySQL stores all its username and password data in a special database named mysql. You can add users to this database and specify the databases to which they will have access with the grant command. The MySQL root or superuser account, which is used to create and delete databases, is the exception. You need to use the mysqladmin command to set your root password. 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:

[root@tmp server]# mysqladmin -u root password new-password

Reset Root Password on running Mysql

1. Stop the Mysql

[root@localhost ~]# /etc/init.d/mysqld stop

2. Start again with mysqlsafe

[root@localhost ~]# mysqld_safe --skip-grant-tables &

3. From another terminal,

[root@localhost ~]# mysql -u root

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

In the mysql database, there is one table named as "user" in that table set password for the user root.

4. Restart Mysql

[root@localhost ~]# /etc/init.d/mysqld restart

Accessing The MySQL Command Line

MySQL command line interpreter (CLI) can be accessed as follows.

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 53
Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Basic mysql tasks in CLI

1. List the databases in the server.

mysql> show databases;

2. Create a database

mysql> create database mynewdatabasename;

3. Delete a databse(drop)

mysql> drop database unwanteddatabasename;

4. create a user

mysql> create user newusername;

5. List the users

mysql> select user from mysql.user;

6. Providing privilages to one user on one database

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

mysql> flush privileges;

The flush privileges is used to write the privilege changes to the mysql.sql database

7. Delete a user

mysql> drop user test;

Backup a Database

We can use mysqldump for backup a mysql database.

[root@localhost ~]# mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]

Eg:

[root@localhost ~]# mysqldump -u root -p mysecret mydatabase > mydatabasebackup.sql

Using this method, we can create a backup as sql file. If the case is a huge database, we can use the standard pipes to dump and zip.

Eg:

mysqldump -uroot -pmysecret --database mybigdatabase | gzip -c > mybigdatabase.sql.gz;

In Some time, putting the password in command will cause security problems. In such case, we can use mysqldump in the following manner.

[root@localhost ~]# mysqldump -u root -p database > database.sql

in this case we will be asked for the root password when the command executed.

Restore a Database

[root@localhost ~]# mysql -u user -p 'password' db-name < db-name.sql

If the password needs to be secured,

[root@localhost ~]# mysql -u user -p db-name < db-name.sql

Ref: http://nixcraft.com