MySql - Occupazione spazio nelle tabelle

SELECT
   table_schema,table_name, count(*) TABLES,
   round(sum(table_rows)/1000000,2) rows,
   round(sum(data_length)/(1024*1024*1024),2) DATA,
   round(sum(index_length)/(1024*1024*1024),2) idx, 
   round(sum(data_length+index_length)/(1024*1024*1024),2) total_size,
   round(sum(index_length)/sum(data_length),2) idxfrac
FROM
   information_schema.TABLES
group by table_schema,table_name
order by 7 desc

MySql - Salvataggio di tabella su file

Salvataggio di tabella su file:

SELECT * FROM prova
INTO OUTFILE '/mnt/prova.csv'
FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n';

Se Infobright potrebbe dare il seguente errore:
The query includes syntax that is not supported by the Infobright Optimizer. Either restructure the query with supported syntax, or enable the MySQL Query Path in the brighthouse.ini file to execute the query with reduced performance. 

Per risolverlo eseguire prima lo statement:

set @bh_dataformat = 'txt_variable';

Caricamento da file:

LOAD DATA INFILE '/mnt/prova.csv' INTO TABLE prova1

mysqldump

The most simple way is to issue this command:

mysqldump -u [user] -p [database_name] > [backupfile].dump

This command is going to ask you for the [user] password and then will create a script which later can be used to retore the data.

Another way is to use the optimized way.

mysqldump --opt -u [user_name] -p [database_name] > [backup_file].dump

This command will use an optimized method, and will include in the script MySQL commands that will erase (drop) tables that already exists and create them again before populate the data inside.

Maybe the best way to run this command is to use the option of gzip the output file. (for obvious reasons)

Once you have your backup file, you may want to restore it someday, this is the way to do it. (remember tu unzip your file, if zipped, before)

mysql [database_name] < [backup_file].dump

Remeber that you can run

man mysqldump

for more help.

Backuping a single table from a database
mysqldump -u user_name -p database_name table_name > /var/www/backups/table_name.sql

Restoring the table into another database
mysql -u -p database_name < /var/www/backups/table_name.sql

Installazione MySQL e InfoBright su Centos

Come verificare la versione del sistema operativo:

uname -a

MySQL
INSTALLAZIONE: yum -y install mysql mysql-server
REGISTRAZIONE SERVIZIO:/sbin/chkconfig mysqld on
START: /sbin/service mysqld start
STOP: /sbin/service mysqld stop
CONNESSIONE:  mysql -h localhost -u root -p
Configurazione: vi /etc/my.cnf

InfoBright
Scaricare e unzippare
START IB: /etc/init.d/mysqld-ib start
STOP IB: /etc/init.d/mysqld-ib stop
Configurazione: vi /etc/my-ib.cnf

Elenco delle query in esecuzione (PostgreSQL e MySql)

Ecco come vedere l'elenco delle query in esecuzione:

PostgreSQL

select * from pg_stat_activity

MySql

show full processlist

Cancellare una query in esecuzione:

PostgreSQL

select pg_cancel_backend(8560)