Posted by: Mangesh_Linux_Administrator | September 1, 2010

Concept of MYSQL


###MySQL###
Features:
1. DBMS Engine
2. Compabtible with various front-ends:
a. Perl
b. PHP
c. ODBC
d. GUI Management

Tasks:
1. Install MySQL Client & Server
a. yum -y install mysql

/etc/my.cnf – primary config file
/usr/bin/mysql – primary client used to interact with the server
/usr/bin/mysqladmin – primary admin utility to return useful info, and perform admin tasks from the shell

b. yum -y install mysql-server

cv /usr/libexec/mysqld – DBMS engine

2. Start MySQL server and modify perms for ‘root’
a. service mysqld start
b. chkconfig –level 35 mysqld on
c. mysqladmin -u root password abc123

3. Install ‘mysql’ client on a remote system and test connectivity
a. yum -y install mysql
b. mysql -u root -p

Note: mysql command-line options ALWAYS override global (/etc/my.cnf), and/or local (~/.my.cnf) configuration directives

Note: MySQL Users consist of the following:
a. username i.e. ‘root’
b. host i.e. ‘localhost’
A sample username is: ‘root@localhost’

4. Secure ‘anonymous’ account
a. DELETE FROM mysql.user WHERE user = ”;
b. flush privileges;

5. Create Database ‘addressbook’

create database AddressBook;
use AddressBook;
create table contacts (`first_name` char(20), `last_name` char(20),
`bus_phone1` char(20), `email` char(30), PRIMARY KEY (`email`));

6. Insert Data into ‘contacts’ table using INSERT

INSERT INTO contacts (first_name,last_name,bus_phone1,email) VALUES (‘Kay’,’Mohammed’,’888.573.4943′,’kay@LinuxCBT.com’);

7. Delete record from ‘contacts’ table
DELETE FROM contacts WHERE email = ‘kay1@LinuxCBT.com’;

8. Update a record in the ‘contacts’ table
UPDATE contacts SET email=’kay2@LinuxCBT.com’ WHERE firs t_name=’Kay’;

9. Drop database ‘addressbook’
drop database addressbok;

10. Import databases from file.
mysql -u root -ppassword < file_name_db.mysql

11. ADD a user
mysql> USE mysql;
mysql> INSERT INTO user (Host, User, Password, Select_priv)         VALUES (”, ‘Dude1’, password(‘supersecret’), ‘Y’);
mysql> FLUSH PRIVILEGES;   – Required each time one makes a     change to the GRANT table
mysql> GRANT ALL PRIVILEGES ON bedrock.* TO Dude1;
mysql> FLUSH PRIVILEGES;   – Required each time one makes a         change to the GRANT table
mysql> quit

12. mysql> GRANT ALL PRIVILEGES on *.* to david@’%’ identified by ‘david’;
The host definition of ‘%’ or ” (null) refers to any host
Show privileges:
mysql> SHOW GRANTS FOR Dude2@’%’;

13. $ mysqladmin -u root -pold-password password new-password

14. find multiple attributes.
mysql> SELECT User,Password,Host from user;

15.
mysql> DELETE FROM user WHERE User=” AND Host=’localhost’;
mysql> DELETE FROM user WHERE User=’Dude1′ AND Password=”;
mysql> FLUSH PRIVILEGES;

16.
mysql> UPDATE user SET Host=’%’ WHERE User=’Dude2′;
mysql> FLUSH PRIVILEGES;

17.Reset the MySQL root password:
mysqladmin -u root flush-privileges password newpassword

18.
Start MySQL in safe mode and skip the use of the “grant tables”: /usr/bin/mysqld_safe –user=mysql –socket=/var/lib/mysql/mysql.sock –pid-file=/var/run/mysqld/mysqld.pid –datadir=/var/lib/mysql –skip-grant-tables –skip-networking &

19.
Delete a database: [prompt]$ mysqladmin drop database-name

20.
mysql> LOAD DATA LOCAL INFILE “data.txt” INTO TABLE bedrock;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Categories

%d bloggers like this: