dba

Mysql

#acess mysql

  #library of mysql running in rails ubuntu
  sudo apt-get install libmysqlclient-dev
  
  #show mysql running
  ps ax | grep mysql
  
  #kill the proccess
  sudo pkill mysql
  sudo pkill mysqld
  restart mysql

  #start or restart mysql
  sudo service mysql start
  sudo service mysql restart
  
  #find socket
  sudo mysqladmin variables | grep socket
  
  #open in workbench
  mysql-workbench

  mysql -u root -p
  
#How to Create and Delete a MySQL Database
  SHOW DATABASES;
  CREATE DATABASE database name;
  DROP DATABASE database name;

#How to Access a MySQL Database
   USE events;
   SHOW tables; 
   
 #How to Create a MySQL Table
    CREATE TABLE potluck (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
      name VARCHAR(20),
      food VARCHAR(30),
      confirmed CHAR(1), 
      signup_date DATE);
      This command accomplishes a number of things:

    It has created a table called potluck within the directory, events.
    We have set up 5 columns in the table—id, name, food, confirmed, and signup date.
    The “id” column has a command (INT NOT NULL PRIMARY KEY AUTO_INCREMENT) that automatically numbers each row.
    The “name” column has been limited by the VARCHAR command to be under 20 characters long.
    The “food” column designates the food each person will bring. The VARCHAR limits text to be under 30 characters.
    The “confirmed” column records whether the person has RSVP’d with one letter, Y or N.
    The “date” column will show when they signed up for the event. MySQL requires that dates be written as yyyy-mm-dd
    
     DESCRIBE potluck;
     
     
 # How to Add Information to a MySQL Table
      INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "John", "Casserole","Y", '2012-04-11');
      INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Sandy", "Key Lime Tarts","N", '2012-04-14');
     
 # How to Update Information in the Table
      UPDATE `potluck` 
SET 
`confirmed` = 'Y' 
WHERE `potluck`.`name` ='Sandy';


#How to Add and Delete a Column 
   ALTER TABLE potluck ADD email VARCHAR(40);
   ALTER TABLE potluck ADD email VARCHAR(40) AFTER name; 
   ALTER TABLE potluck DROP email;
 
# How to Delete a Row 
    DELETE from [table name] where [column name]=[field text];
    DELETE from potluck  where name='Sandy';
      
      
      
#resume create User
0. Login to mysql: $ mysql -u root -p
1. Create a database: $ CREATE DATABASE db_connect;
$ USE db_connect;
2. Create a user : $ CREATE USER 'splunk_user'@'localhost' IDENTIFIED by 'password';
3. Grant privileges to the user for that db : GRANT ALL PRIVILEGES ON db_connect.* TO 'splunk_user'@'localhost';
4 .Connect with MySQL Workbench on that db and create the first table



#remove sudo in mysql
  sudo mysql -u root -p
  use mysql;
  update user set plugin='mysql_native_password' where user='root';
  flush privileges; 
  quit;
  mysql -u root -p
  
  
 #rails 
  rake db:create creates database
  rake db:migrate creates tables in the database
  rake db:seed creates records in the tables based on seed data