Mysql Basic cmd

MySql administaertion on linux (rhel)

Install and Configure the MySQL on RHEL5:

#yum install *mysql*

#service mysqld restart

To defining SQL Admin and Password:

#mysqladmin -h <hostname> -u <username> password <xxxx>

Example:

#mysqladmin -h redhat.logixsi2.com -u root password redhat

Or

#mysqladmin -u root password redhat

To login (from unix shell)

# mysql -h hostname -u root -p

Or

#mysql -u root -p

Change a users password from unix shell:

# mysqladmin -u username -h hostname.blah.org -p password new-password

#mysqladmin -u root -h redhat.logixsi2.com -p password redhatadmin

Password: (Provide here old mysql password of the user)

To login (from unix shell)

#mysql ¡Vh hostname -u root -p

# mysql ¡Vh redhat.logixsi2.com -u root ¡Vp

To show the MySQL version information:

List all databases on the SQL server:

mysql> show databases;

Create a database on the SQL server:

mysql> create databases [databasesname];

mysql> create databases school;

Switch to a database:

mysql> use [db name];

mysql> use school;

To see all the tables in the db:

mysql> show tables;

Create a table in database:

mysql> create table students (IDnumber int(10), name varchar(100), class varchar(10), marks

int(100), rank int(10));

Example : To Create a Table

mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middlenameVARCHAR(3), lastnameVARCHAR(35),office VARCHAR(10),useridVARCHAR(15),username VARCHAR(8),emailVARCHAR(35),phone VARCHAR(25),date_of_join VARCHAR(15));

To see database's field formats in the table:

mysql> describe <table name>;

To Insert values into tables:

mysql> insert into <table name> values (1, ‘shiva’,’8^th’, ‘99’, ‘1^st’);

Show all data in a table:

mysql> SELECT * FROM [table name];

Show specific columns by listing their names:

To delete a table:

mysql> drop table <table name>;

mysql> drop table students;

To delete a database:

mysql> drop database <database name>;

mysql> drop database school;

Delete a row(s) from a table:

mysql> DELETE from [table name] where [field name] = 'whatever';

mysql> DELETE FROM students WHERE name="kumar";

To modify or update entries in the table use the UPDATE command:

mysql> UPDATE students SET marks=”77” WHERE name=”shiva¡”;

Add a column into DB:

mysql> alter table [table name] add column [new column name] varchar (20);

 

 

Delete a column:

mysql> alter table [table name] drop column [column name];

Show certain selected rows with the value "whatever":

mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";

Change column name:

mysql> alter table [table name] change [old column name] [new column name] varchar (50);

Make a column values modify:

mysql> alter table [table name] modify [column name] VARCHAR(3);

Make a unique (Key) column so you get no dupes.

mysql> alter table [table name] add unique ([column name]);

Delete unique from table.

mysql> alter table [table name] drop index [colmn name];

Update database permissions/privileges.

mysql> flush privileges;

Creating a new user

Login as root àSwitch to the MySQL db àMake the user & Update privs.

# mysql -u root ¡Vp

mysql> use mysql;

mysql> INSERT INTO

user(Host,User,Password)VALUES('%','username',PASSWORD('password'));

mysql> flush privileges;

mysql> select user, host, password from user where user=¡¦user-name¡¦;

Change a user¡¦s password from MySQL prompt.

Login as root à Set the password. Update privs.

# mysql -u root ¡Vp

mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');

mysql> flush privileges;

Recover MySQL root password.

Stop the MySQL server process è Start again with no grant tables è Login to MySQL as

root è Set new password è Exit MySQL and restart MySQL server.

# /etc/init.d/mysql stop

# mysqld_safe --skip-grant-tables &

# mysql -u root

mysql> use mysql;

mysql> update user set password=PASSWORD("newrootpassword") where User='root';

mysql> flush privileges;

mysql> quit

# /etc/init.d/mysql stop

# /etc/init.d/mysql start

Allow the user "bob" to connect to the server from localhost using the password "passwd".

Login as root è Switch to the MySQL db èGive privs èUpdate privs.

# mysql -u root ¡Vp

mysql> use mysql;

mysql> grant usage on *.* to bob@localhost identified by 'passwd';

mysql> flush privileges;

Give user privileges for a db.

Login as root è Switch to the MySQL db èGrant privs & Update privs.

# mysql -u root ¡Vp

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

mysql> grant all privileges on databasename.* to username@localhost;

mysql> flush privileges;

Back Up and Restore of MySQL Database

Taking backup of specified ¡§database¡¨ :

# mysqldump --opt -u [username] -p[passwd] [dbname] > backupfile.sql

[uname] Your database username

[pass] The password for your db (note there is no space between -p and the password)

[dbname] The name of your database

[backupfile.sql] The filename for your database backup

[--opt] The mysqldump option

# mysqldump --opt -u root -pmysql movies > moviesbackup.sql

Taking backup of certain tables from the database:

# mysqldump -u [username] -p[passwd] dbname table1 table2 > backup.sql

Example: Taking back up only ¡§user and host¡¨ tables from the ¡§mysql¡¨ database:

Taking backup more than one database at once:

#mysqldump -u [username] -p --databases db1 db2 db3 > dbs_backup.sql

Taking back up of all the databases in the server at one time:

# mysqldump -u [username] -p[passwd] --all-databases > alldb_backup.sql

Back up your MySQL Database with Compress:

# mysqldump -u [uname] -p[passwd] [dbname] | gzip -9 > backupfile.sql.gz

Restoring your MySQL Database:

#mysql -u [username] -p [dbname] < backup.sql

To restore compressed backup files you can do the following:

#gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[passwd] [dbname]

Backing Up and Restoring using PHPMyAdmin

To backup your MySQL database using PHPMyAdmin just follow a couple of steps:

. Open phpMyAdmin.

. Select your database by clicking the database name in the list on the left of the screen.

. Click the Export link. This should bring up a new screen that says View dump of database (or

something similar).

. In the Export area, click the Select All link to choose all of the tables in your database.

. In the SQL options area, click the right options.

. Click on the Save as file option and the corresponding compression option and then click the 'Go'

button. A dialog box should appear prompting you to save the file locally.

Restoring your database is easy as well as backing it up. Make the following:

. Open phpMyAdmin.

. Create an appropriately named database and select it by clicking the database name in the list on

the left of the screen. If you would like to rewrite the backup over an existing database then click

on the database name, select all the check boxes next to the table names and select Drop to delete

all existing tables in the database.

. Click the SQL link. This should bring up a new screen where you can either type in SQL

commands, or upload your SQL file.

. Use the browse button to find the database file.

. Click Go button. This will upload the backup, execute the SQL commands and re-create your

database.

MySQL Configuration File:

#vi /etc/my.cnf

To check the logs info:

#mysqlbinlog <log-file>