mysql basic commands

mysql -uroot -powaspbwa

• if -p is left blank, system will prompt for password.

1. how to reset the password for root
2. here the '#' is the root prompt
3. since am lazy and just cut & pasted.

########################################

Stop mysqld daemon

1. /etc/rc.d/mysqld stop
2. mysqld_safe --skip-grant-tables &

Connect to mysql server

1. mysql -u root mysql

Change root password:

mysql> UPDATE user SET password=PASSWORD("NEW_PASSWORD") WHERE User='root'; mysql> FLUSH PRIVILEGES; mysql> exit

Then restart daemon:

1. /etc/rc.d/mysqld restart

You're done

1. I don't know what all that stuff was above

############################# ## Set the root password ## ############################# $ mysqladmin -u root password NEWPASSWORD

or update

$ mysqladmin -u root -p'oldpassword' password newpass

change password for another user

$ mysqladmin -u vivek -p oldpassword password newpass

$ mysql -u root -p mysql> grant all on menagerie.* to 'user'@'localhost'; $ mysql -u user -p mysql> create database menagerie; mysql> use menagerie; mysql> select DATABASE(); # this is only a check.

1. now identify types of information.

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),

-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

mysql> describe pet; # verify the table is what you think.

1. make a tabled seperated data table text file, pet.txt.

mysql> LOAD DATA LOCAL INFILE './pet.txt' INTO TABLE pet;

1. below, I inserted a new record.

mysql> INSERT INTO pet

-> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

mysql> SELECT * FROM pet; # verify what went in. #

1. From here the table is finished.

#

1. Here are some others things did in the Tutorial.

# mysql> CREATE TABLE event (name VARCHAR(20), date DATE,

-> type VARCHAR(15), remark VARCHAR(255));

1. then created petEvent.txt

mysql> load DATA LOCAL INFILE 'petEvent.txt' INTO TABLE event; mysql> SELECT * FROM event # Verify the data.

1. This next query looks for -something-

mysql> SELECT pet.name,

-> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age, -> remark -> FROM pet INNER JOIN event -> ON pet.name = event.name -> WHERE event.type = 'litter';

1. Another query to find mates to for breeding.

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species

-> FROM pet AS p1 INNER JOIN pet AS p2 -> ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';

1. This shows the current selected database.

mysql> SELECT DATABASE();

1. This one below shoes the current tables in the database;

mysql> SHOW TABLES;

1. below shoes a description of any table in your current database.

mysql> DESCRIBE pet;

1. Next, shows the actual syntax of what it took to create the table pet.

mysql> SHOW CREATE TABLE pet;

1. Can show any index's in the table event here.

mysql> SHOW INDEX FROM event;

---pet.txt--- Fluffy Harold cat f 1993-02-04 \N Claws Gwen cat m 1994-03-17 \N Buffy Harold dog f 1989-05-13 \N Fang Benny dog m 1990-08-27 \N Bowser Diane dog m 1979-08-31 1995-07-29 Chirpy Gwen bird f 1998-09-11 \N Whistler Gwen bird 1997-12-09 \N Slim Benny snake m 1996-04-29 \N

---end of pet.txt---

---start of petEvent.txt--- Buffy 1994-06-19 litter 3 puppies, 3 female Chirpy 1999-03-21 vet needed beak straightened Slim 1997-08-03 vet broken rib Bowser 1991-10-12 kennel Fang 1991-10-12 kennel Fang 1998-08-28 birthday Gave him a new chew toy Claws 1998-03-17 birthday Gave him a new flea collar Whistler 1998-12-09 birthday First birthday

---end of petEvent.txt---

1. The \N is for NULL into the field.

How to load a common seperated value into mysql database ========================================================

run mysql client

goto into database

print out the target table

load the data from the csv file

Mysql Backup ============

Backing up a database

• this will print out to STDOUT
• multiples okay just keep adding the names
• use redirect > to write them to the file.

Restore a database

$ mysql -u root -p > grant INSERT,SELECT on databasename.* to user@localhost;