create database

create database dbname CHARACTER SET utf8 COLLATE utf8_czech_ci;
GRANT ALL PRIVILEGES ON database.* TO rolux@localhost IDENTIFIED BY 'heslo' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON dbname.* TO username@localhost WITH GRANT OPTION;
flush privileges; 

zrušení práv

REVOKE ALL PRIVILEGES ON database.* FROM rolux@localhost;

rename database

mysqldump -u rolux -a -p OLDNAME > xx.sql
create database NEWNAME CHARACTER SET utf8 COLLATE utf8_czech_ci;
mysql -u rolux -p  NEWNAME < xx.sql
INTO OUTFILE '/tmp/out.txt' FIELDS TERMINATED BY '|';

velikost DB

SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
FROM information_schema.TABLES GROUP BY table_schema ;

všechny JEN tabulky

SELECT TABLE_NAME from information_schema.TABLES WHERE table_schema="ROLUX" and TABLE_TYPE="BASE TABLE";

velikost tabulek

SELECT table_name AS "Tables", 
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" 
FROM information_schema.TABLES 
WHERE table_schema = "database"
ORDER BY (data_length + index_length) DESC;

Import DB:

s/`rolux`@`%`/`rolux`@`localhost`/g

dump bez dat

mysqldump -u root -p --no-data dbname > schema.sql

Engine and system

mysql_upgrade -u root -p
mysqlcheck -u rolux -p ROLUX -h roluxdbloc
myisamchk

Instalace

mkdir /data/DB
mkdir /data/DBTMP
chown mysql:mysql /data/DB*
chmod 700 /data/DB*
mysql_install_db --user=mysql --basedir=/usr/local --datadir=/data/DB
/usr/local/etc/rc.d/mysql-server start
/usr/local/bin/mysqladmin -u root password 'newPASS'


 </div>
 <div class="article">


h2. Klient


h3. vi v command line

Do souboru .inputrc vložit:
<pre>
set editing-mode vi
set keymap vi
set show-all-if-ambiguous On

Výstup do less

• pager less
• nopager
  
  h3. řádkový/sloupcový výstup

• za select dát: \g \G
• go ego

editor

edit

help

?
http://dev.mysql.com/doc/refman/5.5/en/mysql-commands.html

Moudra

Vstup z CSV

load data local infile 'obory.csv' into table obory fields terminated by ','  enclosed by '"'  lines terminated by '\n';

Výstup do CSV

GRANT FILE ON *.* TO 'rolux'@'localhost';
SELECT id, client, project, task, description, time, date  FROM ts
  INTO OUTFILE '/path/to/file.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'

Výstup do CSV

my < pom.sql | sed 's/\t/,/g' > pom.csv

Výstup do CSV - oddělovac pipe

select CONCAT_WS('|',f1,f2)

Výstup tabulka

mysql -t

Výstup HTML

mysql -H

federated tables

http://winashwin.wordpress.com/2012/08/22/mysql-federated-table/

velikost tabulek

SELECT
table_schema as `Database`,
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;

Přidání uživatele

grant all on ROLUX.* to user@localhost identified by 'heslo';

přehled uživatelů v mysql.user

GRANT ALL PRIVILEGES ON ROLUX.* TO rolux@localhost IDENTIFIED BY 'heslo' WITH GRANT OPTION;
flush privileges; 

CREATE USER 'user'@'localhost' IDENTIFIED BY 'heslo-';

GRANT select ON database.* TO user@localhost;


 

Změna hesla

/etc/init.d/mysql start --skip-grant-tables

use mysql;

update user set password=password('nove heslo') where user='root';

create database ROLUX CHARACTER SET utf8 COLLATE utf8_czech_ci;

GRANT ALL PRIVILEGES ON database.* TO rolux@localhost IDENTIFIED BY 'heslo' WITH GRANT OPTION;

SET PASSWORD=PASSWORD;

flush privileges;