Connecting to a database

 1. mysql -h host -u user -p
 2. password
 3. connect database_name

Some Useful Commands

  • SHOW DATABASES; - show all existing databases
  • CREATE DATABASE new-database-name; - create a new database
  • SHOW TABLES; - show all table in the database
  • DESCRIBE table; - give table details
  • CREATE TABLE tablename (field1 data VARCHAR(100) field2 number(20)….); - create a table
  • ALTER TABLE tablename ADD field VARCHAR(4) AFTER field2; - Add a new column to a table called field which has variable characters up to 4 charcaters after existing column field2
  • SELECT fieldname1,fieldname2 FROM table WHERE fieldname1 LIKE 'pattern' AND fieldname2 = 'whatever'; - match records in a table
  • INSERT INTO table VALUES ('xxx','yyy'); - Add entries to a table
  • UPDATE table SET value = “newvalue” WHERE columname LIKE “a value”;
  • TRUNCATE TABLE tablename; - delete table contants
  • LOAD DATA LOCAL INFILE filename INTO TABLE table; - load data from a file into a table (open with mysql –local-infile)
  • LOAD DATA LOCAL INFILE filename INTO TABLE table fields terminated by ',' enclosed by '“' lines terminated by '\n'; - load data from a CSV file into a table (open with mysql –local-infile)
  • source filename.sql; - populate database from an sql file
  • help - get help!
  • exit - close connection

To redirect the output into a file add INTO OUTFILE “filename” to the end of a select statement.

Managing Users

  1. Log into mysql as root: mysql -u root -p
  2. Connect to the internal database: connect mysql;
  3. To show existing users: select User, Password from user;
  4. To reset a password (using password string): SET PASSWORD FOR theuser = PASSWORD('apassword');
  5. To delete: DROP USER theuser;
  6. Create user: create user auser@localhost identified by 'thepassword';
  7. To show the privileges of a user: show grants for theuser@localhost;
  8. To give access to a specific database: GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'localhost';

NOTE: You need to specify @localhost if you want to connect to “localhost”

Backups

mysqldump can be used to backup databases, e.g.

  mysqldump -u root -p --databases mylovelydb >> /backups/mylovelydb.dump
    

Recent Changes