Softpanorama
(slightly skeptical) Open Source Software Educational Society

May the source be with you, but remember the KISS principle ;-)

Softpanorama Search

Reference

MySQL Admin Commands:

 

 

Sample SQL:

SQL requests are either administrative or data-related. The following are sample SQL segments and are not necessarily pertinent to the previous example: 

mysql> CREATE DATABASE bedrock;
mysql> USE bedrock;
mysql> SHOW tables;
mysql> SHOW DATABASES;
mysql> LOAD DATA LOCAL INFILE "data.txt" INTO TABLE bedrock;
mysql> SELECT DISTINCT dept FROM  bedrock;
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
mysql> SELECT * FROM pet WHERE species = "snake" OR species = "bird";
mysql> SELECT * FROM pet WHERE species = "dog" AND sex = "f";
mysql> SELECT * FROM pet WHERE birth >= "1998-1-1";
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
mysql> SELECT * FROM pet WHERE name LIKE "b%";
mysql> SELECT * FROM pet WHERE name REGEXP "^b";
mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
mysql> SELECT MAX(article) AS article FROM shop;
mysql> DROP TABLE tmp;
mysql> CREATE TABLE retired_employee (
                    Name char(20) DEFAULT '' NOT NULL,
                    Dept char(10) DEFAULT '' NOT NULL,
                    JobTitle char(20),
                    UNIQUE name_dept (Name,Dept)
                    );
mysql> CREATE UNIQUE index name_dept on employee (name,dept); - avoids duplicate keys
mysql> INSERT INTO employee VALUES ("Jane Smith","Sales","Customer Rep");
mysql> INSERT INTO employee VALUES ('Jane Smith','Sales','Account Manager');
mysql> INSERT INTO employee VALUES ('Jane Smith','Engineerin','Manager');
mysql> UPDATE employee SET dept='HR' WHERE name='Jane Smith';

Use "auto_increment" integer column:
   mysql> ALTER TABLE employee ADD EmpId INT NOT NULL AUTO_INCREMENT PRIMARY KEY; 

mysql> SHOW INDEX FROM employee;
mysql> ALTER TABLE employee DROP INDEX name_dept;  - get rid of 
mysql> SELECT VERSION();
mysql> SELECT NOW();
mysql> SELECT USER();
mysql> SELECT * FROM employee WHERE name LIKE "%Sm%";
mysql> SELECT * FROM employee WHERE name REGEXP "^Ja";
mysql>
See section 3 of MySQL manual for more examples.

 

Loading Data:

Command: LOAD DATA LOCAL INFILE 'file.dat' INTO TABLE employer;

Input tab delimited file: file.dat 

Fred Flinstone  Quarry Worker   Rock Digger
Wilma Flinstone Finance Analyst 
Barney Rubble   Sales   Neighbor
Betty Rubble    IT      Neighbor

 

Dump/Backup/Transfer Database:

The mysqldump command will read the mySQL database and generate a SQL command text file. This allows data to be migrated to other versions of mySQL (i.e. upgrade from typical Red Hat (RH7.x to FC3) mySQL release 3.23.58 to a more advanced mySQL 4.1 or 5.0) or to other SQL databases. SQL command file generated can create tables, insert data, ....

 

Option Description
-A
--all-databases		 Dump all the databases.
-B
--databases		 Dump the specified databases.
-h
--host=		 Specify host to connect to.
-p
--password=		 Specify password. If you do not specify a password, then you will be queried.
-u
--user=		 Specify user. Defaults to current user logged in.
--opt Same as: --add-drop-table --add-locks --all --extended-insert --quick --lock-tables
--add-drop-table Add a "drop table" SQL statement before each "create" SQL statement.
--add-locks Add "lock" SQL statements around "insert" SQL statements.
-a
--all		 Include all mySQL specific SQL "create" options.
-e
--extended-insert		 Allows utilization of the new, much faster INSERT syntax. Database you are migrating to must support this notation.
-q
--quick		 Don’t buffer query, dump directly to stdout.
-l
--lock-tables		 Lock all tables for read.
-?
--help		 Display command line options.

Examples:

Man Page:

Upgrading to 4.1:

 

Building MySql from source: (on Linux)

Prerequisites:

Compile and install: (as root) Configure:

 

Commands/Man pages:

 

  • isamchk - Check and repair of ISAM tables.
  • isamlog - Write info about whats in a nisam log file.
  • msql2mysql
  • my_print_defaults
  • myisamchk
  • myisamlog
  • myisampack
  • mysql - text-based client for mysqld, a SQL-based relational database daemon
  • mysql_config
  • mysql_convert_table_format
  • mysql_find_rows
  • mysql_fix_privilege_tables
  • mysql_install_db
  • mysql_setpermission
  • mysql_zap - a perl script used to kill processes
  • mysqlaccess - Create new users to mysql.
  • mysqlbinlog
  • mysqlbug
  • mysqlcheck
  • mysqld_multi - Used for managing several mysqld processes running in different UNIX sockets and TCP/IP ports.
  • mysqldump - text-based client for dumping or backing up mysql databases , tables and or data.
  • mysqldumpslow
  • mysqlhotcopy
  • mysqlimport
  • mysqlshow - Shows the structure of a mysql database (databases,tables and columns)
  • mysqltest
  • pack_isam
  • perror - used to display a description for a system error code, or an MyISAM/ISAM table handler error code.
  • replace - A utility program to replace changes strings in place in files or on the standard input.
  • resolve_stack_dump
  • resolveip
Server:
  • mysqladmin - A utility for performing administrative operations
  • safe_mysqld - The recommended way to start a mysqld daemon on Unix.

 

Admin GUI Tools:

 

Copyright © 1996-2009 by Dr. Nikolai Bezroukov. www.softpanorama.org was created as a service to the UN Sustainable Development Networking Programme (SDNP) in the author free time. Submit comments This document is an industrial compilation designed and created exclusively for educational use and is placed under the copyright of the Open Content License(OPL). Site uses AdSense so you need to be aware of Google privacy policy. Original materials copyright belong to respective owners. Quotes are made for educational purposes only in compliance with the fair use doctrine.

Disclaimer:

Last modified: February 23, 2009