====== MySql Tips and Commands ==

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

===== Setup ==

==== Install inital Database ==

According to ''/etc/rc.d/rc.mysqld'' do the following:
<code bash>
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
</code>

==== Secure Mysql ==

Run ''mysql_secure_installation'' as root and answer every question with yes!

After that, you may continue with creating databases. See [[mysql_commands#Managing_Databases]] below.

DELETEME 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:
<code bash>
su -                         # become user 'root'
mysqladmin -u root password
#mysqladmin -u root -h <yourHostName> password
</code>

Don't issue the second command, it fails, because of 2 independent reasons:
  - Access from other hostnames than 'localhost' is not allowed and
  - <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:

<code sql>
pager less -S;
use mysql;
select * from user; # to check
delete from user where host='<yourHostName>'; 
flush privileges;
select * from user; # to check
</code>

DELETEME 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:

<code sql>
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
</code>

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 Commands ==

<note important>In order to get UTF-8 content, you must log in and mysqldump  with ''----default-character-set=latin1''. FIXME Dont know why!!</note>
==== Misc ==

<code bash>
mysql -u username -p # log into mysql
</code>

<code sql>
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

</code>

See also [[http://www.developer.com/db/article.php/3725901|Secrets of the MySQL Client Command Line]]

==== Managing Databases ==

<code sql>
/* 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;
</code>

FIXME [[http://dev.mysql.com/doc/refman/5.7/en/grant.html|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.

==== Managing Tables ==

<code sql>
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]
</code>

==== Working with Data ==

<code sql>
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]
</code>

==== Backup and Restore ==

<code bash>
# 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
</code>

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

<code sql>
  GRANT SELECT, LOCK TABLES ON *.* TO backupuser@localhost IDENTIFIED BY 'your_pass';
</code>

Create a file ''.my.cnf'' in the Linux home dir of the user:
  [client]
  password=your_pass

FIXME Is this warning still relevant:
<note warning>
In order to have all UTF-8:
  * Check if the dumped ''backup-file.sql'' is encoded in UTF-8 (use eg. ''file backup-file.sql'')
  * Use ''DEFAULT CHARSET=utf8'' in ''CREATE TABLE'' sections of ''backup-file.sql''
  * Remove all appearences of ''collate latin1_german2_ci''
  * ''mysqldump'' inserts funny comments which seem to mess with the character set when imported. => Remove all comments in ''backup-file.sql''

</note>

===== Old, German & unsorted ==

<code>
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
</code>