MySql Tips and Commands

Done with slack 11.0 2007-06-26, slack 12.0 2007-10-18, slack 14.1 2014-11-07

According to /etc/rc.d/rc.mysqld do the following:

su -                          # become user 'root'
chmod +x /etc/rc.d/rc.mysqld  # start mysqld at every boot
mysql_install_db --user=mysql # install inital database (/var/lib/mysql)
/etc/rc.d/rc.mysqld start     # start mysqld

Run mysql_secure_installation as root and answer every question with yes!

After that, you may continue with creating databases. See Managing_Databases below.

The rest of this section is obsolete since — 2014-11-07 (Slack 14.1)

Get your values for <yourHostName> and <yourDomainName> with hostname -f

The command mysql_install_db (see above) strongly recommends to setup a root password:

su -                         # become user 'root'
mysqladmin -u root password
#mysqladmin -u root -h <yourHostName> password

Don't issue the second command, it fails, because of 2 independent reasons:

1. Access from other hostnames than 'localhost' is not allowed and
2. <yourHostName> is somehow always extended to <yourHostName>.<yourDomainName> on Slackware. (More in Mysql manual at „4.2.11 Causes of Acccess denied Errors“

We don't care about this quirk and just delete all rows from the table mysql/user which contain <yourHostName> as host:

pager less -S;
USE mysql;
SELECT * FROM USER; # TO CHECK
DELETE FROM USER WHERE host='<yourHostName>'; 
FLUSH privileges;
SELECT * FROM USER; # TO CHECK

The follwing is obsolete since 2007-10-18 (Slack 12.0)

Comment out the line SKIP=„–skip-networking“ in /etc/rc.d/rc.mysqld and restart mysqld with /etc/rc.d/rc.mysqld resart. Log into the monitor with mysql -u root -p, then issue the following commands:

pager less -S;
USE mysql;
SELECT * FROM USER; # TO CHECK
UPDATE USER SET host='<yourHostName>.<yourDomainName>' WHERE host='<yourHostName>';
FLUSH privileges;
SELECT * FROM USER; # TO CHECK

Now the command mysqladmin -u root -h <yourHostName> password <yourPassword> should work

Comment in the line SKIP=„–skip-networking“ in /etc/rc.d/rc.mysqld again and restart mysqld with /etc/rc.d/rc.mysqld resart again.

mysql -u username -p # log into mysql

PAGER most; /* or */ PAGER less -S; # Redirect output TO a pager
NOPAGER;                            # Switch off redirection
 
SHOW DATABASES; # Display the availabel databaese ON the server
 
USE myDatabase; # Subsequent commands refer TO myDatabase

See also Secrets of the MySQL Client Command Line

/* Create a database with name myDb.
    Shell alternative: mysqladmin create myDb */
CREATE DATABASE myDb;
 
/* Recommended metohd to create a user for the new datase with all possible access privileges.
    Affects mysql.user and mysql.db   
    ' is important for password! */
GRANT ALL PRIVILEGES ON myDb.* TO myUser@localhost IDENTIFIED BY 'myPassword';
 
/* Alternative to create a user: */
CREATE USER myUser@localhost IDENTIFIED BY 'myPassword';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, LOCK TABLES ON myDb.* TO myUser@localhost;
 
/* Delete the database with all tables. */
DROP DATABASE myDb;

Manual says: Use of GRANT to create accounts … is deprecated as of MySQL 5.7.6. Instead, perform these tasks using CREATE USER or ALTER USER.

SHOW TABLES; # SHOW ALL TABLES OF a dababase
 
DESCRIBE myTable; # SHOW COLUMN DATA types OF a TABLE
 
RENAME TABLE oldname TO newname;
 
/* To alter the data tye of a column or eg to remove auto_increment. */
ALTER TABLE myTable MODIFY myColumn MEDIUMINT UNSIGNED;
 
/* Delete Tables */
DROP TABLE [IF EXISTS] myTable [, my2ndTable, ...] [RESTRICT | CASCADE]

SELECT * FROM myTable; -- Show content of a table
 
/* Query 2 tables at the same time (see 3.6.6. Using Foreign Keys) */
SELECT a.*, b.myCol1 FROM myTabA a INNER JOIN myTabB b ON a.id = b.a_id WHERE ...;
 
/* To test for NULL, you cannot use the arithmetic comparison operators such as =, <, or <> */
SELECT * FROM myTable WHERE myColumn IS NULL;
 
SELECT COUNT(*) FROM myTable; -- Shows the number of lines of a table
 
TRUNCATE myTable; -- Delete all rows of a table, like DELETE, but faster
 
/* Delete rows with conditions */
DELETE [LOW_PRIORITY] FROM myTable [WHERE where_definition] [LIMIT ROWS]

# Backup an existing database into an SQL-file
mysqldump -u root -p$password --opt --default-character-set=utf8 $dbname > $filename
 
# Restore the Database from a SQL-file
# Note that the dababase must already exist!
mysql -u root -p$password $db_name < $filename
 
# Backup  all Databases into one SQL-textfile:
mysqldump [--opt] --all_databases > file

To avoid root user and passwords in mysqldump, add a special user vor backups in mysql:

  GRANT SELECT, LOCK TABLES ON *.* TO backupuser@localhost IDENTIFIED BY 'your_pass';

Create a file .my.cnf in the Linux home dir of the user:

[client]
password=your_pass

Is this warning still relevant:

pager or \P; nopager or \pager on/off, wich pager to use is set in ??? env-variable

INSERT INTO tabelname SET colname1=('c1val1'), ...;
  Es müssen nicht alle Spalten angegeben werden
INSERT INTO urlConnector VALUES (2, 1);
  alle Spalten müssen angegben werden; NULL wenn kein Wert vorliegt

mysql -u user --pager="less -S" database
  Als Benutzer user in die Datenbank database einloggen. Output geht nach less, und Zeilen werden nicht umgebrochen
  Wenn man schon eingeloggt ist, kann man das gleiche erreichen mit "mysql> \P less -S"
  -t      Output in table format (for batch mode)
  -vvv    very verbose (for batch mode ?)
  -?      show all command line options (unlike 'man mysql' or the manual)
  --pager use pager set in $PAGER environment variable

varchar(<länge>)	Datentyp String
date			Datentyp Datum

load data infile "<dateiname>" into table <table>;
  Daten aus einer Textdatei tabgetrennt in die Tabelle laden