Reference

• Statistics: [prompt]$ mysqladmin version
• List database environment: [prompt]$ mysqladmin variables
• Show if database is running: [prompt]$ mysqladmin ping
• Show databases available: [prompt]$ mysqlshow
   

  +-----------+
  | Databases |
  +-----------+
  | bedrock   |
  | mysql     |
  | test      |
  +-----------+
  

  OR

  mysql> SHOW DATABASES;
  

• Show list of active threads in server: [prompt]$ mysqladmin -h localhost -u root -p processlist
   

  +----+------+-----------+----+---------+------+-------+------------------+
  | Id | User | Host      | db | Command | Time | State | Info             |
  +----+------+-----------+----+---------+------+-------+------------------+
  | 15 | root | localhost |    | Query   | 0    |       | show processlist |
  +----+------+-----------+----+---------+------+-------+------------------+
  

• Delete a database: [prompt]$ mysqladmin drop database-name
• Execute SQL from Linux command line interface:
  [prompt]$ mysql -h localhost -u root -p -e "select host,db,user from db" mysql
• Execute SQL command file from Linux command line interface:

      [prompt]$ mysql -h localhost -u root -p database-name < text-file-with-sql-statements.sql
  

• Loadtest (benchmark) the system:

  [prompt]$ cd sql-bench
  [prompt]$ run-all-tests
  or
  [prompt]$ mysql -vvf test < ./tests/auto_increment.tst
  

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.

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

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:

• Dump database to a file:
  • Dump specified database:
    mysqldump --opt database > db-dump-file.sql
  • Dump specified table in database:
    mysqldump --opt database table-name > db-dump-file.sql
  • Dump multiple databases:
    mysqldump --opt -databases database1 database2 database3 > db-dump-file.sql
  • Dump everything:
    mysqldump --opt --all-databases > total-db-dump-file.sql
    mysqldump -u user-id -h host-name --opt --all-databases > total-db-dump-file.sql
• Import dumped file:
  mysql database < db-dump-file.sql
• Export from one database and import to another:
  • Transfer specifed database from one database to another:
    mysqldump --opt database | mysql --host=host-name -C database

Man Page:

• mysqldump

Upgrading to 4.1:

• Upgrading mySQL to 4.1 from 3.23
• Use the command: mysql_fix_privilege_tables --password=root-password
  This allows you to use the new GRANT command.

Prerequisites:

• C compiler: 2.95.2 or later. (Check with the command: rpm -q gcc)

• Downloaded source from http://dev.mysql.com/downloads/mysql/4.1.html
• Expand tar file: tar xzf mysql-4.1.16.tar.gz
• cd mysql-4.1.16
• ./configure --prefix=/opt/mysql --sysconfdir=/opt/etc --localstatedir=/opt/var/mysql --with-unix-socket-path=/opt/tmp/mysql.sock
  (Use the command ./configure --help to see all options.)
  This should create an installation which will not clobber an existing RPM mySQL installation.
• make
• make install
• Create mysql config file: cp support-files/my-medium.cnf /opt/var/my.cnf
• Create user/group mysql
  • Test if user/group mysql already exists: groups mysql
  • Create group: groupadd mysql
  • Create user: useradd -g mysql -M -r -d /opt/lib/mysql -s /sbin/nologin -c "MySQL Server" mysql
• chown -R mysql:mysql /opt/var/mysql

• Install default database: /opt/mysql/bin/mysql_install_db --user=mysql
  Since this command is run as root, specify the --user option to operate command as user mysql.
  Creates help database with SQL script: /opt/mysql/share/mysql/fill_help_tables.sql
• Start mySQL database: /opt/mysql/bin/mysqld_safe --user=mysql &
• /opt/mysql/bin/mysqladmin -u root password 'new-password'
• /opt/mysql/bin/mysqladmin -u root -h yoserver2 password 'new-password'
• See tutorial above for use and administration.
• Check defaults: (Defaults from config file: /opt/var/my.cnf)
  • /opt/mysql/bin/my_print_defaults --config-file=my client mysql

    --password=supersecret
    --port=3306
    --socket=/opt/tmp/mysql.sock
    --no-auto-rehash
        

  • /opt/mysql/bin/my_print_defaults --config-file=my client mysql mysql_install_db

    --datadir=/var/lib/mysql
    --socket=/var/lib/mysql/mysql.sock
    --password=supersecret
    --port=3306
    --socket=/opt/tmp/mysql.sock
    --port=3306
    --socket=/opt/tmp/mysql.sock
    --skip-locking
    --key_buffer=16M
    --max_allowed_packet=1M
    --table_cache=64
    --sort_buffer_size=512K
    --net_buffer_length=8K
    --read_buffer_size=256K
    --read_rnd_buffer_size=512K
    --myisam_sort_buffer_size=8M
    --log-bin
    --server-id=1
        

• 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

• mysqladmin - A utility for performing administrative operations
• safe_mysqld - The recommended way to start a mysqld daemon on Unix.

• MyCC
• MySqlGUI
• phpMyAdmin - Web based

Etc

Society

Groupthink : Two Party System as Polyarchy : Corruption of Regulators : Bureaucracies : Understanding Micromanagers and Control Freaks : Toxic Managers :   Harvard Mafia : Diplomatic Communication : Surviving a Bad Performance Review : Insufficient Retirement Funds as Immanent Problem of Neoliberal Regime : PseudoScience : Who Rules America : Neoliberalism  : The Iron Law of Oligarchy : Libertarian Philosophy

Quotes

War and Peace : Skeptical Finance : John Kenneth Galbraith :Talleyrand : Oscar Wilde : Otto Von Bismarck : Keynes : George Carlin : Skeptics : Propaganda  : SE quotes : Language Design and Programming Quotes : Random IT-related quotes :  Somerset Maugham : Marcus Aurelius : Kurt Vonnegut : Eric Hoffer : Winston Churchill : Napoleon Bonaparte : Ambrose Bierce :  Bernard Shaw : Mark Twain Quotes

Bulletin:

Vol 25, No.12 (December, 2013) Rational Fools vs. Efficient Crooks The efficient markets hypothesis : Political Skeptic Bulletin, 2013 : Unemployment Bulletin, 2010 :  Vol 23, No.10 (October, 2011) An observation about corporate security departments : Slightly Skeptical Euromaydan Chronicles, June 2014 : Greenspan legacy bulletin, 2008 : Vol 25, No.10 (October, 2013) Cryptolocker Trojan (Win32/Crilock.A) : Vol 25, No.08 (August, 2013) Cloud providers as intelligence collection hubs : Financial Humor Bulletin, 2010 : Inequality Bulletin, 2009 : Financial Humor Bulletin, 2008 : Copyleft Problems Bulletin, 2004 : Financial Humor Bulletin, 2011 : Energy Bulletin, 2010 : Malware Protection Bulletin, 2010 : Vol 26, No.1 (January, 2013) Object-Oriented Cult : Political Skeptic Bulletin, 2011 : Vol 23, No.11 (November, 2011) Softpanorama classification of sysadmin horror stories : Vol 25, No.05 (May, 2013) Corporate bullshit as a communication method  : Vol 25, No.06 (June, 2013) A Note on the Relationship of Brooks Law and Conway Law

History:

Fifty glorious years (1950-2000): the triumph of the US computer engineering : Donald Knuth : TAoCP and its Influence of Computer Science : Richard Stallman : Linus Torvalds  : Larry Wall  : John K. Ousterhout : CTSS : Multix OS Unix History : Unix shell history : VI editor : History of pipes concept : Solaris : MS DOS :  Programming Languages History : PL/1 : Simula 67 : C : History of GCC development :  Scripting Languages : Perl history   : OS History : Mail : DNS : SSH : CPU Instruction Sets : SPARC systems 1987-2006 : Norton Commander : Norton Utilities : Norton Ghost : Frontpage history : Malware Defense History : GNU Screen : OSS early history

Classic books:

The Peter Principle : Parkinson Law : 1984 : The Mythical Man-Month :  How to Solve It by George Polya : The Art of Computer Programming : The Elements of Programming Style : The Unix Hater’s Handbook : The Jargon file : The True Believer : Programming Pearls : The Good Soldier Svejk : The Power Elite

Most popular humor pages:

Manifest of the Softpanorama IT Slacker Society : Ten Commandments of the IT Slackers Society : Computer Humor Collection : BSD Logo Story : The Cuckoo's Egg : IT Slang : C++ Humor : ARE YOU A BBS ADDICT? : The Perl Purity Test : Object oriented programmers of all nations : Financial Humor : Financial Humor Bulletin, 2008 : Financial Humor Bulletin, 2010 : The Most Comprehensive Collection of Editor-related Humor : Programming Language Humor : Goldman Sachs related humor : Greenspan humor : C Humor : Scripting Humor : Real Programmers Humor : Web Humor : GPL-related Humor : OFM Humor : Politically Incorrect Humor : IDS Humor : "Linux Sucks" Humor : Russian Musical Humor : Best Russian Programmer Humor : Microsoft plans to buy Catholic Church : Richard Stallman Related Humor : Admin Humor : Perl-related Humor : Linus Torvalds Related humor : PseudoScience Related Humor : Networking Humor : Shell Humor : Financial Humor Bulletin, 2011 : Financial Humor Bulletin, 2012 : Financial Humor Bulletin, 2013 : Java Humor : Software Engineering Humor : Sun Solaris Related Humor : Education Humor : IBM Humor : Assembler-related Humor : VIM Humor : Computer Viruses Humor : Bright tomorrow is rescheduled to a day after tomorrow : Classic Computer Humor

The Last but not Least Technology is dominated by two types of people: those who understand what they do not manage and those who manage what they do not understand ~Archibald Putt. Ph.D

The statements, views and opinions presented on this web page are those of the author (or referenced source) and are not endorsed by, nor do they necessarily reflect, the opinions of the Softpanorama society. We do not warrant the correctness of the information provided or its fitness for any purpose. The site uses AdSense so you need to be aware of Google privacy policy. You you do not want to be tracked by Google please disable Javascript for this site. This site is perfectly usable without Javascript.

Last modified: March 12, 2019