Mysql - prima pagina di installazione

Miei comandi preferiti

mysql -u root -p
mysql> show databases;
mysql> use mysql;
mysql> select host, user from user;
mysql> create user 'root'@'%' identified by 'miapassword';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

e poi entro via browser grafico

cambiare la password di un utente

SET PASSWORD FOR 'user-name-here'@'hostname-name-here' = PASSWORD('new-password-here');

Eseguire e mettere su file esterno

mysql -u root -p nome_db -e "show tables" > risultato.txt

Procedure

show all procedures

show procedure status;

show only the name

select name from mysql.proc

view the code of a procedure

SHOW CREATE PROCEDURE pop_data_model;

usare il tunnel ssh per localhost

Se ho l'accesso al server in ssh e voglio entrare tramite uno strumento grafico ma ci sono le porte del firewall bloccate, oppure gli utenti hanno solo accesso da localhost, posso usare un tunnel ssh.

ssh -f  -L 3074:127.0.0.1:3306 username@ipserver  -N; echo 'trying to open a tunnel  on port 3074'

In questo modo lancio il mio client grafico con localhost e porta 33074

Reset the mysql password after some usage

If you are using the mysql server for a while you need to follow this procedure
https://support.rackspace.com/how-to/mysql-resetting-a-lost-mysql-root-password/

#stop the service
service mysqld stop
#restart in safe mode
mysqld_safe --skip-grant-tables &
#login without password 
mysql -uroot

from the command line

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set password=PASSWORD("mysecretpassword") where User='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit

restart in normal mode

service mysqld stop
160708 13:11:53 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
Stopping mysqld:                                           [  OK  ]
[1]+  Done                    mysqld_safe --skip-grant-tables

start mysql normally
service mysqld start
Starting mysqld:                                           [  OK  ]

show the size of all db

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

backup and restore of the users

from this link http://serverfault.com/questions/8860/how-can-i-export-the-privileges-from-mysql-and-then-import-to-a-new-server
create this script
nano export.sh

MYSQL_CONN="-uroot -pmypassword -h localhost"
mysql ${MYSQL_CONN} --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql ${MYSQL_CONN} --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql
  • source is the machine where take the info the old
  • destination is the machine where migrate the new
# read the info for test
source$ mysql -uroot -pmypassword mysql -e "select host, user from user;"
#export the info
source$ ./export.sh
source$ scp MySQLUserGrants.sql user@destinations:
#login to destination
destination$ mysql -uroot -pmypassword -A < MySQLUserGrants.sql
#verify
destination$ mysql -uroot -pmypassword mysql -e "select host, user from user;"

Postinstallazione sotto centos

Sotto ubuntu fa partire lui la configurazione mentre sotto centos dopo aver installato il server avviare tramite

/etc/init.d/mysqld

e poi configurare la password di root e le impostazioni di sicurezza in quest'ordine
/usr/bin/mysqladmin -u root password 'una-pass-complicatissima'
/usr/bin/mysql_secure_installation

backup system not mysqldump

con mysqldump il lock sulle tabelle e' fatto una per una, quindi ci potrebbero essere dei problemi d'integrita'.
Se si puo' accedere alla macchina del db conviene usare i metodi descritti qui
http://dev.mysql.com/doc/mysql-enterprise-backup/3.9/en/backup-subcommands-restore.html
http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/restoring.html

3 Tutorial — Client mysql

3.1 Connecting to and Disconnecting from the Server

Login parametri

mysql  mysql -u root  -h 192.168.100.2 -ppassword

Se non si mette la password verrà chiesta, se non si mette -p si proverà l'accesso senza password, se non si mette -u verrà usato il nome utente, se non si mette -h si usa localhost.

Dentro il client testuale di mysql

mysql> show databases;

3.2. Entering Queries

Ecco alcune funzioni utili

SELECT VERSION(), CURRENT_DATE, SELECT NOW();
SELECT USER();

Usando \c in una nuova riga si indica a mysql di stoppare la query che si sta scrivendo in quel momento che ancora non si è completata con il ;

Significato del prompt

Prompt Significato
mysql> pronto per un nuovo comando
-> Waiting for next line of multiple-line command.
'> Waiting for next line, waiting for completion of a string that began with a single quote (“'”).
"> Waiting for next line, waiting for completion of a string that began with a double quote (“"”).
`> Waiting for next line, waiting for completion of an identifier that began with a backtick (“`”).
/*> Waiting for next line, waiting for completion of a comment that began with /*.

3.3 …

5. MySQL Server Administration

5.5.2. Adding User Accounts

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' WITH GRANT OPTION;
mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' WITH GRANT OPTION;

Passi d'installazione di mysql OLD VERSION ON RED-HAT

  1. scaricati da mysql il server e il client per red hat 4 release
  2. eliminati tutti i pacchetti che si riferivano anche alla versione 4 perchè altrimenti non s'installava , mysql-server-4.1.20-1.RHEL4.1.i386, dovecot-1.0-1.beta8.petra4.i386, php-mysql-4.3.9-3.19.petra4.i386, mysql-4.1.20-1.RHEL4.1.i386
  3. # installiamo quelli nuovi della versione 5 MySQL-server-community-5.0.67-0.rhel4.i386.rpm , MySQL-client-community-5.0.67-0.rhel4.i386.rpm il server viene avviato automaticamente, mettiamo anche le shared libraries che sembrano cose utili MySQL-shared-community-5.0.67-0.rhel4.i386.rpm
  4. colleghiamoci con il client dalla macchina mysql -u root
  5. sistemiamo i permessi come spiegato sotto in modo da avere un account root disponibile da altre macchine in modo che funzioni mysql query browser dalla mia macchina

2.10.3. Securing the Initial MySQL Accounts (dalla guida online) NOT UPDATE

Tutte le istruzioni sono per unix

- dopo l'esecuzione di mysql_install_db, vengono creati due account di root con la password vuota
- entrambi permettono gli account solo dalla macchina locale
- vengono creati anche degli account anonimi, con username vuoto o password .
- Bisogna quindi impostare una password per tutti e due i tipi di account

- connettersi al db come root: shell> mysql -u root
- impostare la password con STE PASSWORD o UPDATE per essere sicuri che la password venga criptata usare la funzione PASSWORD()
mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR ''@'host_name' = PASSWORD('newpwd');

- Situazione base:
mysql> SELECT Host, User FROM mysql.user;
+---+--+
| Host | User |
+---+--+
| localhost | |
| localhost | root |
| opscp | |
| opscp | root |
+---+--+
4 rows in set (0.00 sec)
abbiamo 4 utenti root e ' ' per localhost e per opscp (che è il nome della macchina)
- con il comando :
mysql> SET PASSWORD FOR ''@'localhost'=PASSWORD('nessuna');
Query OK, 0 rows affected (0.00 sec)
all'utente ' ' da localhost è stata cambiata la password e quindi con mysql -h localhost -u ' ' -p rispondendo con password nessuna ci si logga
- un'altra maniera per modificare le password è usando UPDATE per modificare la tabella user direttamente
UPDATE mysql.user SET Password = PASSWORD('newpwd')
-> WHERE User = '';
mysql> FLUSH PRIVILEGES;
se non si esegue il flush i cambi saranno effettivi al prossimo riavvio del server
- per cancellare , non dimenticare il flush alla fine, se non si specifica host vengono cancellati tutti e due
DELETE FROM mysql.user WHERE Host='localhost' AND User='';

- per cambiare la password di root ci sono tre metodi:
1) SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
2) shell> mysqladmin -u root password "newpwd"
shell> mysqladmin -u root -h host_name password "newpwd"
il secondo comando rimpiazza host_name con il nome del server host
3) UPDATE mysql.user SET Password = PASSWORD('newpwd')
-> WHERE User = 'root';

- per spegnere mysql una volta che le password sono state settate shell> mysqladmin -u root -p shutdown

5.6.2. Adding New User Accounts to MySQL TO CHECK

- ci sono tre modi per assegnare i privilegi o con il comando GRANT (preferibile perchè più coinciso e soggetto a meno errori) o modificando la tabella user o usando un programma esterno phpmyadmin per esempio
- per permettere di collegarsi da root da qualsiasi macchina, tirocinio è la password
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'
-> IDENTIFIED BY 'tirocinio' WITH GRANT OPTION;
- ora funzionano il query browser e administrator

INSERT INTO mysql.user (Host,User,Password,Select_priv) VALUES('%','alfresco',password('alfresco'),'Y');

4.5 MySQL Client Programs

4.5.1.4. Executing SQL Statements from a Text File

Per fare in modo da ripristinare il sistema usare il seguente comando

mysql -u root -p < fileconsqlstatement.sql

in questo modo esegue tutto il codice contenuto nel file ripristinando eventuali db e/o tabelle.
Se si specifica il nome del db si esegue quel codice sql dentro il db
mysql -u root -psegreta conti < trans.sql

4.5.4. mysqldump — A Database Backup Program

Può essere usato per fare il dump di uno o più di un db o per trasferire a un altro sql server non necessariamente mysql. Il dump tipicamente contiene dichiarazioni sql o crea tabelle popolate. Può essere usato per generare files in csv o in formato xml.
Se si stanno facendo backup con tabelle myisam va considerato l'uso di mysqlhotcopy perchè accoppia un veloce backup e restore vedi sezione 4.6.9.

Ecco i tre esempi di sintassi:

mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases

Se non si specificano tabelle al seguito o si utilizzano —databases o —all-databases tutte le tabelle vengono dumped.
mysqldump non dumpa le information schema per default ma va indicato in maniera esplicita. although currently you must also use the —skip-lock-tables option.
Prima della versione 5.1.38 mysqldump silenziosamente ignora le INFORMATION_SCHEMA perfino se tu le chiami eslicitamente da linea di comando. Per altre info mysqldump —help
Usare
--opt è lo stesso che specificare --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset. Tutte queste opzioni date da --opt sono date per default perchè --opt è di default.

Per fare il contrario invece si specifica l'opzione
--compact che comprende le seguenti opzioni --skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys, and --skip-set-charset  options.

Questo sotto spiega come includere ed escludere solo alcune opzioni
 To reverse the effect of a group option, uses its --skip-xxx  form (--skip-opt or --skip-compact). It is also possible to select only part of the effect of a group option by following it with options that enable or disable specific features. Here are some examples:

    *

      To select the effect of --opt except for some features, use the --skip option for each feature. To disable extended inserts and memory buffering, use --opt --skip-extended-insert --skip-quick. (Actually, --skip-extended-insert --skip-quick is sufficient because --opt is on by default.)
    *

      To reverse --opt for all features except index disabling and table locking, use --skip-opt --disable-keys --lock-tables.

When you selectively enable or disable the effect of a group option, order is important because options are processed first to last. For example, --disable-keys --lock-tables --skip-opt would not have the intended effect; it is the same as --skip-opt by itself.

Dump in memoria o riga per riga

mysqldump può recuperare il contenuto di una tabella riga per riga, oppure può recuperare il contenuto e tenerlo in memoria prima di fare il dump. In quest'ultimo caso fare attenzione per tabelle di grandi dimensioni. Per fare il dump riga per riga usare l'opzione —quick (oppure —opt, which enables —quick) questa è l'opzione di default, per eseguire invece il dump in memoria usando il buffering usare —skip-quick.
Per fare il dump su un mysql molto vecchio non si possono usare le opzioni —opt or —extended-insert option ma invece si deve usare —skip-opt

mysqldump from MySQL 5.1.21 cannot be used to create dumps from MySQL server 5.1.20 and older. This issue is fixed in MySQL 5.1.22.

Nella pagina della guida vi sono tutte le opzioni per usare

Il dump del db di default fa il backup dei triggers ma non delle store procedure per quello bisogna specificare il parametro —routines
–routines – FALSE by default
–triggers – TRUE by default

Esempi

mysqldump --all-databases -psegretissima -u root > backup.sql
dumpa un db mettendo la password in chiaro

mysqldump conti -p -u root > backup.sql
dumpa tutti il db conti

mysqldump -u root -p -t -T/path/to/directory conti
cambia la directory dove mette il risultato

Mette in formato XML
mysqldump -u root -p --xml dbname users > users.xml

Fa il dump solo delle stored procedure e non di tutto il resto
mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt <database> > outputfile.sql

if you have an error like this

invalid table(s) or column(s) or function(s) or definer/invoker of 
view lack rights to use them (1356)

use the option -f as suggest here
http://serverfault.com/questions/263197/making-a-more-resilient-mysql-backup-script

4.5.5. mysqlimport — A Data Import Program

Carica file di tipo sql nel db.

mysqlimport [options] db_name textfile1 [textfile2 ...]

Configurazioni

Mysql quando viene installato (su linux) si trova in ascolto solo su localhost infatti lanciando il comando

sudo netstat -tap | grep mysql

si ottiene qualcosa simile a questo

tcp 0 0 localhost:mysql *:* LISTEN 2556/mysqld

Per permettere l'accesso totale bisogna sostituire dentro /etc/mysql/my.cnf
invece di
bind-address = 127.0.0.1

mettere l'ip vero e proprio oppure per tutte le interfacce 0.0.0.0
poi riavviare il servizio

Store procedure

Ecco una query che legge tutte le store procedure che hanno nel corpo (quindi la definizione) la parola public

select name from mysql.proc where mysql.proc.body like '%public%'

5.6. Running Multiple MySQL Servers on the Same Machine

Può essere utile lanciare più istanze del server mysqld sulla stessa macchina. Per testare una nuova release o per una gestione degli access.
Per permettere questo però alcuni parametri di lancio del mysql devono essere differenti per ogni istanza presente.
Ecco una lista dei parametri che devono cambiare

*  --port=port_num o si cambia la porta oppure usando l'opzione --bind-address si può specificare una istanza su un ip e l'altra su un altro ip.
* --socket=path in unix è il path di un file in windows il nome di un pipe. In windows è necessario specificare nomi di pipe distinti solo per server che supportano le connessioni con il nome di pipe
*  --shared-memory-base-name=name questa opzione è usata solo in windows. Designa la memoria condivisa in un windows server per permettere ai client di connettersi usando la memoria condivisa. È necessario specificare nomi di memoria condivisa distinta solo per quei server che supportano la connessioni con memoria condivisa.
* --pid-file=file_name questa opzione è usata solo in unix, indica il path del nome nel quale il server scrive il suo process id

Se si usano le opzioni del log file devono essere differenti per ogni istanza 
* --log[=file_name]
* --log-bin[=file_name]
* --log-error[=file_name]
* --bdb-logdir=file_name

Per migliori performance si possono specificare le seguenti opzioni differenti per ogni server per dividere il carico tra dischi di server differenti
* --tmpdir=path
* --bdb-tmpdir=path
Avere dir temporanee differenti rende facile determinarte quale mysql server crea file temporanei

Con veramente poche eccezioni ogni server deve avere una sua dir per i dati 
* --datadir=path

Attenzione non si dovrebbe mai avere server multipli che aggiornano dati sugli stessi databases. Questo può portare a spiacevoli sorprese se il sistema operativo non supporta fault-free system locking. Se esegui server multipli usando la stessa directory e hanno il sistema di login abilitato bisogna specifica
Salvo diversa indicazione, il contenuto di questa pagina è sotto licenza Creative Commons Attribution-ShareAlike 3.0 License