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’,’8th’, ‘99’, ‘1st’);
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>
