Top

A Linux User Reference

Search tips
  • search ignores words that are less than 4 characters in length
  • searches are case insensitve
  • if a search does not return anything try it in Boolean mode then Query expansion mode by checking the appropriate radio button e.g. searching for 'cron' in just the Administration category returns nothing - presumably because the 50% threshold is reached. Boolean mode ignores this threshold so a search for 'cron' returns several hits
  • in Boolean mode preceding a word with a '+' means the result must include that word, a '-' means it must not
  • in Boolean mode '+crontab -anacron' means match articles about crontab that DO NOT mention anacron
  • to match a phrase e.g. 'manage system' check the Boolean mode radio button and enclose the phrase in quotes "some phrase ..."
  • in Query expansion mode the search context is expanded beyond the keywords you entered - relevancy of hits may well be degraded

DATABASE

Administrative MySQL

  • Link to MySQL tutorials and other stuff
  • SQL variations

    SQL syntax can vary depending on the Database - in general though the basics are pretty much standard. Not all DBs support the same features e.g. Sqlite3 does not support stored procedures, MySQL5.1 does not support check constraints ...

  • Login to a specific database

    Login and prompt for password

    $ mysql -u root -p db_demo
    

    where db_demo is the database and root is the user account to use.

  • Controlling the interface on login

    As in the above example. See the mysql man pages and documentation for more information.

    Login but do not print column names in any output

    $ mysql -u root -p Shop_demo --skip-column-names
    
  • Display existing databases

    List current databases

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | Shop_demo          |
    | mysql              |
    +--------------------+
    3 rows in set (0.00 sec)
    
    mysql> help show;
    Name: 'SHOW'
    Description:
    SHOW has many forms that provide information about databases, tables,
    columns, or status information about the server. This section describes
    those following:
    
    SHOW AUTHORS
    SHOW {BINARY | MASTER} LOGS
    .....
    
  • Get information about the MySQL Server

    Some informational commands

    Command Description
    SELECT DATABASE() Current database name (empty if none)
    SHOW STATUS Server status indicators
    SELECT USER() Current username
    SHOW VARIABLES Server configuration variables
    SELECT VERSION( ) Server version string
  • Create a new DataBase, use it, delete it
    mysql> create database new_db;
    
    mysql> use new_db;
    Database changed
    
    mysql> drop database new_db;
    
  • List existing user accounts

    System wide info is held in the mysql database. If you are using a different database then ...

    mysql> select host,user,password from user;
    ERROR 1146 (42S02): Table 'linuxref.user' doesn't exist
    

    so you need to specfiy the 'full path' of the user table i.e. which database it is in

    mysql> select host,user,password from mysql.user;
    +--------------+------------------+-------------------------------------------+
    | host         | user             | password                                  |
    +--------------+------------------+-------------------------------------------+
    | localhost    | root             | *Cxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx7 |
    | mark-desktop | root             | *Cxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx7 |
    .....
    
    (or switch to using the mysql database)
    
    mysql> use mysql; select host,user,password from user;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    +--------------+------------------+-------------------------------------------+
    | host         | user             | password                                  |
    +--------------+------------------+-------------------------------------------+
    | localhost    | root             | *Cxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx7 |
    | mark-desktop | root             | *Cxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx7 |
    .....
    

    NB. if the password file is blank for any user then that user has NO password set.

  • Create a user and password, delete user
    mysql> create user 'user_name' identified by 'userpass';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select host,user from mysql.user where user = 'user_name';
    +------+-----------+
    | host | user      |
    +------+-----------+
    | %    | user_name |
    +------+-----------+
    
    mysql> drop user 'user_name';
    Query OK, 0 rows affected (0.00 sec)
    

    In this case the user would have been able to access their account via a connection from any host - the '%' wildcard in the host column.

  • Create a user that can only access from localhost
    mysql> create user 'user_name'@'localhost' identified by 'userpass';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select host,user from mysql.user where user = 'user_name';
    +-----------+-----------+
    | host      | user      |
    +-----------+-----------+
    | localhost | user_name |
    +-----------+-----------+
    

    Can only access via a connection from 'localhost'.

  • Change a user's password

    The mysql 'password()' function uses it's own algorithm to encrypt - it is a one-way hash meaning. A password used for login say, is first encrypted and then compared with the stored one. If the two encrypted passwords are the same access is allowed.

    The mysql 'encrypt()' function uses the unix 'crypt()' to encrypt.

    mysql> set password for 'root'@'localhost' = password('newpassword');
    Query OK, 0 rows affected (0.00 sec)
    
  • Show, grant and revoke user privileges

    Privileges are like file permissions but for database objects - tables, stored procedures ... Privileges granted to a user allow that user to undertake privileged actions.

    Show privileges for a user

    mysql> show grants for user_name;
    

    Grant 'select', 'insert' privileges on all tables. Revoke the 'insert' privilege

    mysql> grant select, insert on new_db.* to new_user;
    mysql> flush privileges;
    mysql> revoke insert on new_db.* from new_user;
    mysql> flush privileges;
    

    Flushing privileges make them become effective immediately.

    MySQL 'help grant' examples

    CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
    GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
    GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
    GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
    
  • Alter table
    modify or change?
    alter table <tabname> modify <colname> <new def>
    
    alter table <tabname> change <colname> <new colname> <def>
    
    modify

    changes a table column's definition

    change

    changes the column name along with the definition - to keep the definition need to restate/redefine it

  • Create, modify and drop a table

    Create a table called

    mysql> create table products (
        -> prod_id varchar(6) not null primary key,
        -> prod_desc varchar(25),
        -> prod_vat decimal(4,2) not null,
        -> prod_sale_price decimal(8,2) not null
        -> ) engine=innodb;
    

    'products' has 4 columns using the 'prod_id' column as the primary key

    Change column 'prod_desc' to 'prod_name' and add 'not null', add index for prod_name

    mysql> alter table products change prod_desc prod_name varchar(25) not null;
    mysql> create index product_name on products (prod_name);
    

    Delete a table

    mysql> drop table test;
    Query OK, 0 rows affected (0.01 sec)
    
  • Describe, explain a table

    Describe a tables field properties

    mysql> describe products;
    +-----------------+--------------+------+-----+---------+-------+
    | Field           | Type         | Null | Key | Default | Extra |
    +-----------------+--------------+------+-----+---------+-------+
    | prod_id         | varchar(6)   | NO   | PRI | NULL    |       |
    | prod_name       | varchar(25)  | NO   | MUL | NULL    |       |
    | prod_vat        | decimal(4,2) | NO   |     | NULL    |       |
    | prod_sale_price | decimal(8,2) | NO   |     | NULL    |       |
    +-----------------+--------------+------+-----+---------+-------+
    4 rows in set (0.00 sec)
    
    mysql> explain products;
    +-----------------+--------------+------+-----+---------+-------+
    | Field           | Type         | Null | Key | Default | Extra |
    +-----------------+--------------+------+-----+---------+-------+
    | prod_id         | varchar(6)   | NO   | PRI | NULL    |       |
    | prod_name       | varchar(25)  | NO   | MUL | NULL    |       |
    | prod_vat        | decimal(4,2) | NO   |     | NULL    |       |
    | prod_sale_price | decimal(8,2) | NO   |     | NULL    |       |
    +-----------------+--------------+------+-----+---------+-------+
    4 rows in set (0.00 sec)
    
  • Show how a table was created
    mysql> show create table products;
    +----------+----------------------------------- / --------+
    | Table    | Create Table                       /         |
    +----------+----------------------------------- / --------+
    | products | CREATE TABLE `products` (
      `prod_id` varchar(6) NOT NULL,
      `prod_name` varchar(25) NOT NULL,
      `prod_vat` decimal(4,2) NOT NULL,
      `prod_sale_price` decimal(8,2) NOT NULL,
      PRIMARY KEY  (`prod_id`),
      KEY `product_name` (`prod_name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
    +----------+----------------------------------- / --------+
    1 row in set (0.00 sec)
    
  • Add data to a table, load from file and change data

    Insert data into 'stock' table

    mysql> describe stock;
    +-------------+--------------+------+-----+---------+-------+
    | Field       | Type         | Null | Key | Default | Extra |
    +-------------+--------------+------+-----+---------+-------+
    | prod_id     | int(5)       | YES  |     | NULL    |       |
    | prod_name   | varchar(30)  | YES  |     | NULL    |       |
    | supplier    | varchar(50)  | YES  |     | NULL    |       |
    | unit_cost   | decimal(6,2) | YES  |     | NULL    |       |
    | unit_sale   | decimal(6,2) | YES  |     | NULL    |       |
    | unit_margin | decimal(6,2) | YES  |     | NULL    |       |
    +-------------+--------------+------+-----+---------+-------+
    6 rows in set (0.03 sec)
    
    mysql> insert into stock
        -> (prod_id, prod_name, supplier, unit_cost, unit_sale, unit_margin) values
        -> ("p0001", "Super Coffee", "The Bean Factory, Lower St., St.Annes", "1.90", "2.49", "0.50")
        -> ;
    Query OK, 1 row affected (0.00 sec)
    mysql> insert into Products (prod_id, descript, price,vat) \
    values ("p0002", "coffee","2.59","17.5");
    
    Query OK, 1 row affected (0.01 sec)
    

    Insert data from a file

    mysql> load data local infile '/home/mark/scripts/msql/products.txt' into table Products fields terminated by ';';
    Query OK, 6 rows affected (0.03 sec)
    Records: 6  Deleted: 0  Skipped: 0  Warnings: 0
    

    The data file has 6 lines of ';' separated values. The number of values must correspond with the number of fields in each row of the table.

    Update the price of product p0001 by setting it to 64p

    mysql> update Products set price="0.64" where prod_id="p0001";
    
  • Delete a row, delete all rows

    Delete product p0001 from table

    mysql> delete from stock where prod_id="p0001";
    Query OK, 1 row affected (0.00 sec)
    

    Delete all rows

    mysql> delete from Product_history;
    

    Just make sure to use a where clause if you do not want to delete ALL records in a table.

  • Triggers
    display, create, delete

    A trigger is an object associated with a table that activates when a particular event occurs on the table. Triggers can be run after or before a particular event occurs.

    Event Description
    INSERT The trigger is activated whenever a new row is inserted into the table e.g. through INSERT, LOAD DATA, and REPLACE statements.
    UPDATE The trigger is activated whenever a row is modified e.g. through UPDATE statements.
    DELETE The trigger is activated whenever a row is deleted from the table e.g. through DELETE and REPLACE statements - NOT drop statements

    Display any triggers

    mysql> show triggers;
    +-------------+--------+-------+--------------------------------------+-- / --+
    | Trigger     | Event  | Table | Statement                            | TI/   |
    +-------------+--------+-------+--------------------------------------+-- / --+
    | margin_calc | UPDATE | stock | set @sum = unit_sale - new.unit_cost | AF/ t |
    +-------------+--------+-------+--------------------------------------+-- / --+
    1 row in set (0.00 sec)
    

    Delete the trigger

    mysql> drop trigger margin_calc;
    Query OK, 0 rows affected (0.00 sec)
    

    Create a trigger on insert

    mysql> delimiter |
    mysql> create trigger new_product after insert on products for each row begin
        -> insert into pricehistory (prod_id, hist_new_price, hist_new_vat, hist_date)
        -> select prod_id, prod_sale_price, prod_vat, curdate() from products 
        -> where prod_id = new.prod_id;
        -> end
        -> |
    

    Whenever a new 'product' is added to the products table a corresponding entry along with a timestamp is added to the 'pricehistory' table

    Create a trigger on update

    mysql> create trigger price_change after update on products for each row begin
        -> if new.prod_sale_price != old.prod_sale_price or new.prod_vat != old.prod_vat then
        -> insert into pricehistory (prod_id, hist_new_price, hist_vat, hist_date)
        -> select prod_id, prod_sale_price, prod_vat, curdate() from products 
        -> where prod_id = old.prod_id;
        -> end if;
        -> end
        -> |
    
    mysql> delimiter ;
    

    As in the previous example but only when a products price is changed.

  • Keys
    Primary key

    Every row in a table should have a column/field that uniquely identifies it. The fields value/content cannot be duplicated within the table. There can be only one Primary key per table. Implemented through use of primary key keywords in create or alter table statements.

    Foreign key

    A foreign key is a column in one table that points to the primary key column of another table. Main use is for maintaining referential integrity.

    If for example the product id in a stock table is a foreign key that points to the product id column in a products table then you would be unable to add a record to the stock table for a non-existant product.

    Composite key

    A composite key is made up of more than one column (field). A composite primary key is a primary key made up of more than one column.

    Add auto_increment to the Primary key of an existing table

    mysql> alter table StockItem modify id integer NOT NULL auto_increment;
    

    Define the auto_increment start point

    mysql> alter table StockItem auto_increment=111;
    

    Could have simply stated the auto_increment start value in the 'modify' statement instead of using two later table statements.

  • Create a composite Primary key

    The two columns used (prod_id and supp_id) are foreign keys. In this case, the composite key allows a product that has multiple suppliers (one -> many relationship) to be uniquely identified.

    mysql> create table prodsupplink (prod_id varchar(6) not null, supp_id varchar(6) not null, \
    foreign key (prod_id) references products(prod_id),  \
    foreign key (supp_id) references suppliers (supp_id),\
    primary key (prod_id,supp_id)) engine=innodb;
    
    mysql> show create table prodsupplink;
    +--------------+--------------------------------- / ---+
    | Table        | Create Table                     /    |
    +--------------+--------------------------------- / ---+
    | prodsupplink | CREATE TABLE `prodsupplink` (
      `prod_id` varchar(6) NOT NULL,
      `supp_id` varchar(6) NOT NULL,
      PRIMARY KEY  (`prod_id`,`supp_id`),
      KEY `supp_id` (`supp_id`),
      CONSTRAINT `prodsupplink_ibfk_1` FOREIGN KEY (`prod_id`) REFERENCES `products` (`prod_id`),
      CONSTRAINT `prodsupplink_ibfk_2` FOREIGN KEY (`supp_id`) REFERENCES `suppliers` (`supp_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
    +--------------+---------------------------------- / ---+
    1 row in set (0.00 sec)
    
  • Add a composite primary and foreign key

    A table is created with no keys defined, a primary and two foreign keys are then added.

    mysql> create table prodsupplink (prod_id varchar(6) not null, supp_id varchar(6) not null) engine=innodb;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> alter table prodsupplink add primary key (prod_id, supp_id);
    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> alter table prodsupplink add foreign key (prod_id) references products (prod_id);
    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> alter table prodsupplink add foreign key (supp_id) references suppliers (supp_id);
    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
  • Constraints
    Unique constraint

    Used to ensure that all data in one or more columns is unique - similar to primary keys except that.

    Say a table column contains employees bank account number. Do not want to use this as a primary key as a primary key is public.

    Can use Employee id number as the primary key, then use a unique constraint on the bank account column to prevent two or more employees ever having the same account number. (May not be a good idea if a husband and wife with a joint account are employed!).

    Implement through use of the 'unique' keyword in column definition within 'create' and 'alter' table statements.

    • a table can have multiple unique constraints
    • unique constraint columns can contain NULL values
    • they can be modified
    • their values can be reused
    • cannot be used to define foreign keys
    Check constraint

    Used to ensure that data within a column meets defined criteria i.e. defining a range, constraining max and min values, allowing only specific values ('y' or 'n').

    Add a check constraint

    mysql> alter table products add constraint check (prod_sale_price > 0);
    

    MySQL does not complain however it does not support check constraints - ... so rather pointless. If it did support them then each time a sale price is set it is checked to make sure that it is greater than 0.

  • Add and change a foreign key constraint

    Add a constraint called prod_exist to an existing table (StockHistory) so that no record can be added to StockHistory unless the prod_id value exists in the Product table. In other words, a product must exist before it can be added to StockHistory.

    Add to an existing table

    mysql> alter table StockHistory
        -> add constraint prod_exist
        -> foreign key (prod_id)
        -> references Products(prod_id);
    

    Add during create table

    mysql> create table inventory(
        -> id varchar(8),
        -> opening_stock int(6),
        -> closing_stock int(6),
        -> Date date,
        -> foreign key(id) references prods(Product_Code) on update cascade) engine=innodb;
    

    Any changes (updates or delete) to table 'prods.Product_Code' should be cascaded down to inventory. If a product is deleted it's history should be deleted.

    Ooops! - forgot to use the 'cascade on delete' in the previous create table statement. To correct ..

    Make a change to an existing constraint

    • get the name of the constraint
    • drop (remove) it
    • re-define, re-create it
    mysql>show create table inventory;
      .......
      KEY `id` (`id`),
      CONSTRAINT `inventory_ibfk_1` FOREIGN KEY (`id`) REFERENCES `prods` (`Product_Code`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
    
    mysql> alter table inventory drop foreign key inventory_ibfk_1;
    mysql> alter table inventory add foreign key (id) references prods(Product_Code) \
    on update cascade on delete cascade;
    
    mysql> show create table inventory;
      .......
      KEY `id` (`id`),
      CONSTRAINT `inventory_ibfk_1` FOREIGN KEY (`id`) REFERENCES `prods` (`Product_Code`) \
      ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
    
  • Indexes

    Some pros and cons for using indexes

    • improve the speed of retrieval operations
    • can be defined on one or more columns within a table
    • degrade the performance of insert, delete, alter operations as the indexes need to be updated to reflect the changes.
    • require extra storage space
    • Not all data is suitable for indexing i.e. data that is not reasonably unique e.g. a Country column - many rows are likely to have the same value for this field

    Add an index to column 'loginname' during table creation

    mysql> create table accounts(
        -> id mediumint unsigned not null auto_increment,
        -> loginname varchar(40),
        -> password varchar(40), 
        -> gallery varchar(40) not null default "none", 
        -> realname varchar(60), 
        -> email varchar(60), 
        -> tels varchar (40), 
        -> address text, 
        -> primary key(id),
        -> index(loginname)
        -> ) engine=innodb;
    Query OK, 0 rows affected (0.01 sec)
    

    Add an index 'product_name' to column 'prod_name' in table 'products'

    mysql> create index product_name on products (prod_name);
    
  • Fulltext indexes and searching
    • can be used on VARCHAR and TEXT columns
    • can contain multiple coulmns
    • can be used on both engines - MYISAM (v3.23+)and INNODB (v5.6).

    There are default behaviour differences between the two engines. I use this for site's search feature. I found this site useful.

    Add fulltext to 'title' and 'article' columns in the articles table

    alter table articles add fulltext(title, article);
    

    Search for 'cron' in articles table

    mysql> select title from articles where match(title,article) against('cron');
    +----------------------------------------------------------------+
    | title                                                          |
    +----------------------------------------------------------------+
    | Controlling user access                                        |
    | Run scripts or programs using - /bin/run-parts                 |
    | CRON files                                                     |
    | Crontab problems                                               |
    .....
    | Incremental backup example - /usr/bin/rsync                    |
    +----------------------------------------------------------------+
    20 rows in set (0.00 sec)
    

    Results are automatically sorted by relevancy. The match columns must 'match' the fulltext index definition exactly.

    Boolean mode

    mysql> select title from articles where match(title,article) against('+cron -anacron' in boolean mode);
    +----------------------------------------------------------------+
    | title                                                          |
    +----------------------------------------------------------------+
    | CRON files                                                     |
    | Automating a new task or job                                   |
    | Manage system crontab jobs                                     |
    | User crontabs                                                  |
    .....
    | Crontab problems                                               |
    +----------------------------------------------------------------+
    14 rows in set (0.00 sec)
    
    mysql> select title from articles where match(title) against('+cron -anacron' in boolean mode);
    +------------+
    | title      |
    +------------+
    | CRON files |
    +------------+
    1 row in set (0.00 sec)
    

    In boolean mode you can pick and choose what must and must not be matched. Also, the match column(s) do NOT need to 'match' the fulltext index definition - in this case only the 'title' column index was searched

    Query expansion

    mysql> select title from articles where match(title, article) against('+cron -anacron' with query expansion);
    +---------------------------------------------------------------------------------------+
    | title                                                                                 |
    +---------------------------------------------------------------------------------------+
    | System wide default file - /etc/crontab                                               |
    | CRON files                                                                            |
    | Maintain crontab files for individual users - /usr/bin/crontab                        |
    | Anacron's crontab file - /etc/anacrontab                                              |
    .....
    | Install an RPM package – Install mode                                                 |
    +---------------------------------------------------------------------------------------+
    260 rows in set (0.00 sec)
    

    More of a 'fuzzy' search as you can see from the number of records returned. Also, the match columns MUST 'match' the fulltext index definition. The '+' and '-' also have NO effect.