–H–

–I–

index, create – create index index_name on table_name (column_name1, column_name1);

index, delete – drop index index_name on table_name;

index, show – SHOW INDEX FROM tbl_name

insert into cityInfo
       (zipCode, state, city, citySoundex, county, sequence,
        postalStatus, latitude, longitude)
   select
           distinct c.zipCode, c.state, c.city, c.citySoundex, c.county, c.sequence,
           c.postalStatus, c.latitude, c.longitude
   from    cityInfoSupplement as c left join cityInfo as n
   on      (n.zipCode  = c.zipCode
   and     n.state     = c.state
   and     n.city      = c.city)
   where   n.zipCode   is null

–J–

–K–

kill query – mysqladmin processlist to get the ID followed by mysqladmin kill ID

–L–

list databases – see databases, list

locked queries, find – mysqladmin processlist

–M–

discussion-board

Download

MySQL, start using – simply type in “mysql”.  If this doesn’t work and you have recently changed to superuser using “su”, use “su -” instead.

To start the server, see start server

–N–

–O–

ODBC drivers

ODBC

Settings – use the User, Password from the mysql.user table. Make sure the “Host” column for that record is “192.168.2.%”.

(use mysql; select * from user;)

If missing:

INSERT into user ( Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv )values ("192.168.2.%", "userid", "passwd", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y");

flush privileges;

only 1000 rows affected, remove limit – start mysql with mysql –-select-limit 1000000

order – order by

output to a file, redirect MySQL – see redirect MySQL output to a file

–P–

processes, which are running

from the MySQL command line – show full processlist

from the bash command line – mysqladmin processlist

–Q–

–R–

recover database table to local db from remote db

ssh www 'mysqldump --add-drop-table --opt projects main | gzip' | zcat | mysql projects

where you substitute the database for “projects” and the table for “main”. With the version of MySQL that we're using now --add-drop-table and --opt are the defaults, so you don't need to specify them on the command-line anymore. If you get:

Lost connection to MySQL server during query when dumping table `addresses` at row: 1175073

then get from reportQuery server:

ssh www 'mysqldump -h www14 --add-drop-table --opt fr addresses | gzip' | zcat | mysql fr

This makes it so your long-running query doesn't lock up the table, blocking scripts from running. The reason it gave you "lost connection" errors is that the query-killer we run on db and the fastQuery servers killed your query to unlock the other queries.

redirect MySQL output to a file – 2 ways

from the linux command prompt

mysql -e "describe abc" > temp.txt

from the MySQL command prompt

rename table – ALTER TABLE tbl_name RENAME as new_tbl_name

replace table – see table, replace

replace text in a field –

SET SQL_SAFE_UPDATES=0;

UPDATE savedQueries SET searchSel = Replace(searchSel,'deliverable','isDeliverable');

rows affected, remove limit of only 1000 – start mysql with mysql -select-limit 1000000

run a MySQL command from the Linux prompt –

echo 'create index isShip on orderItems (isShip)' | mysql fr > logFile

run script –

–S–

safe update mode  - “You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column” (ERROR 1175) - SET SQL_SAFE_UPDATES=0

schema - dump table structure – from command line, mysqldump --no-data fr> ~/path/filename.txt

script, run – see run script

show variables – “show variables”

shut down – mysqladmin -u root shutdown

sort – order by

SQL_MAX_JOIN_SIZE, determine – see global and session variables, setting and finding out values of

SELECT @@session.SQL_MAX_JOIN_SIZE; – often defaults to a million

Fix by SET SQL_BIG_SELECTS=1

start server – usr/bin/safe_mysqld &

start session – mysql -u user -p

start using MySQL – see MySQL, start using

switch tables – see table, replace

–T–

table, copy –

To copy structure and all rows: CREATE TABLE new_tbl_name SELECT * FROM tbl_name

To copy structure only: CREATE TABLE new_tbl_name SELECT * FROM tbl_name where 1 = 0

Note: this does NOT copy keys or indices!!  To get that info, show create table tbl_name;

table, copy database table to local db from remote db

ssh www 'mysqldump --add-drop-table --opt projects main | gzip' | zcat | mysql projects

where you substitute the database for “projects” and the table for “main”.  With the version of MySQL that we're using now --add-drop-table and --opt are the defaults, so you don't need to specify them on the command-line anymore.  If you get:

Lost connection to MySQL server during query when dumping table `addresses` at row: 1175073

then get from reportQuery server:

ssh www 'mysqldump -h www14 --add-drop-table --opt fr addresses | gzip' | zcat | mysql fr

This makes it so your long-running query doesn't lock up the table, blocking scripts from running.  The reason it gave you "lost connection" errors is that the query-killer we run on db and the fastQuery servers killed your query to unlock the other queries.

table, copy just 1 row – use mysqldump to generate “INSERT” SQL

mysqldump fr orderItems -t --where="orderItemID=481109" > 481109.sql

table, rename– ALTER TABLE tbl_name RENAME as new_tbl_name

table, replace - rename table cityInfo to cityInfoOld, cityInfo2 to cityInfo

table, show all fields in

from within MySQL – describe tablename;

from Linux command line to a file - mysqldump --opt fr channels --no-data > ~/path/filename.txt

tables, show all tables in a database – show tables;

top 5 records

select * from DemoTable limit 0,10;

–U–

unmatched records, append

insert into cityInfo
        (zipCode, state, city, citySoundex, county, sequence,
         postalStatus, latitude, longitude)
    select
            distinct c.zipCode, c.state, c.city, c.citySoundex, c.county, c.sequence,
            c.postalStatus, c.latitude, c.longitude
    from    cityInfoSupplement as c left join cityInfo as n
    on      (n.zipCode  = c.zipCode
    and     n.state     = c.state
    and     n.city      = c.city)
    where   n.zipCode   is null

update example - update authorizedUsers set status = 'Using' where userName = 'jmiller@yoursite.com';

user, add

INSERT into user ( Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv )values ("192.168.2.%", "userid", "passwd", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y");

flush privileges;

Instead of “192.168.2.%”, you need “localhost” if you want to get in using puTTy

–V–

verify that MySQL is running – mysqladmin version

version, determine - SELECT version() AS version;

view results better – see display each field on a separate line

–W–

–X–

–Y–

“You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column” (ERROR 1175) - SET SQL_SAFE_UPDATES=0

–Z–

–No's–

1000 rows affected, remove limit – start mysql with mysql –-select-limit 1000000

1175 ERROR “You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column” - SET SQL_SAFE_UPDATES=0