MySQL

install MySQL Community Server on mac

  • Go here to download the appropriate version of MySQL Community Server for Mac. Install it.
  • Go here to download the appropriate version of Python connector for MySQL. Install it.
  1. set password for root user for localhost
    • Syntax is
       
          cgao$ mysqladmin -u root password newpassword
      
    • To change it later, the syntax is 
       
          cgao$ mysqladmin -u root password newpassword -p
      
  2. transfer database remotely from machine A to machine B
    • Assume there is a table called mydb@localhost at A; there is no database called mydb@localhost at B.  The ip address of B is BBB.BBB.BBB.BBB
    • First, you need to add a new administrator account on B.
       
          mysql> CREATE USER 'cago'@'localhost' IDENTIFIED BY 'some_passwd';
          mysql> GRANT ALL PRIVILEGES ON *.* TO 'cgao'@'localhost'
                 -> WITH GRANT OPTION;
          mysql> CREATE USER 'cage'@'%' IDENTIFIED BY 'some_passwd';
          mysql>a GRANT ALL PRIVILEGES ON *.* TO 'cgao'@'%'
                 -> WITH GRANT OPTION;
      
    • Then on Machine A, you can remotely create mydb on Machine B through the account you just created on B, using mysqladmin. This step is necessary because you cannot remotely do mysqladmin with Machine B’s root account.
       
          cgao$ mysqladmin -h BBB.BBB.BBB.BBB create mydb -u cgao -p
      
    • Lastly, on Machine A, copy mydb@localhost on A to mydb@BBB.BBB.BBB.BBB on B, using mysqldump
       
          cgao$ mysqldump --user=root --password=yourrootpwdonMachineA mydb | mysql -h BBB.BBB.BBB.BBB mydb -u cgao -p
      
  3. Connection
    • Connect to mysql using local machine
       
          cgao$ mysql [-h localhost] -u root -p
      
    • Connect to mysql using remote machine: you cannot connect as root. First you need to create a user account (as mentioned above)
       
          cgao$ mysql -h xx.xxx.xx.xxx -u cgao -p
      
    • If you want to use your machine’s ip address to connect to local mysql database, you need to force a TCP/IP connection locally
       
          cgao$ mysql -h xx.xxx.xx.xxx -P3306 --protocol=tcp -u …
      
  4. Query the database sizes
     
        mysql> SELECT table_schema "Data Base Name", 
            -> SUM(data_length + index_length) / 1024 / 1024 
            -> "Data Base Size in MB" 
            -> FROM information_schema.TABLES GROUP BY table_schema; 
    
  5. Add a column to a existing database, and update the column value
    • For each of my table, I have a column “time”, which is varchar() type like “Tue Aug  7 21:42:46 2012”.  I would like to add a column with datetime type. (In the future, I’d better make the design better from the beginning, rather than change it later. It takes tremendous amount of time to modify a table…)
    • It is straight forward to add a column in a table
       
          mysql> ALTER TABLE mytable ADD datetime datetime;
      
    • To update the datetime column value, I need to use UPDATE syntax. Since the new “datetime” column takes the old “time” column and convert it form string into datetime, I need to use “STR_TO_DATE” method provided by mysql.One should be careful when formatting the time date. Python’s striftime and MySQL’s STR_TO_DATE use DIFFERENT reference code/specifier!!! (see the links) Be careful with the minutes specifier. Most of the other specifiers are same.Another issue for using STR_TO_DATE is about the ending extra characters. The official document say extra characters at the end of str are ignored.
        
          mysql> SELECT STR_TO_DATE('09:30:17a','%h:%i:%s');
      

      The result is  09:30:17
      However when we use it like

        
           mysql> UPDATE mytable SET 
               -> datetime = STR_TO_DATE('09:30:17a','%h:%i:%s') 
               -> WHERE datetime IS NULL; 
      

      It will report error

        
          ERROR 1292 (22007): Truncated incorrect date value: 
      

      So I need to use “SUBSTRING_INDEX” to removing my ending characters. In my case, if there are ending extra characters, the first should be “,”. So my code is like this:

        
          mysq> UPDATE mytable SET 
              -> datetime = STR_TO_DATE
              -> (SUBSTRING_INDEX(time, ',',1), '%a %b %d %T %Y') 
              -> WHERE datetime IS NULL; 
      
    •  And this is not everything. I have a board called ‘database’, a board called ‘e-sports’. I am not able to update datetime column for these two boards, even if I used the backtilt “, which works for other MySQL methods.
  6. To show the engine status
       
        mysql> SHOW ENGINE INNODB STATUS\G; 
    

    To kill a process

       
        mysql> kill [thread_id]; 
    
  7. space holder
  8. space holder
  9. space holde
Advertisements

About cgao

Cambridge, MA
This entry was posted in Uncategorized. Bookmark the permalink.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s