secure installation:
sudo /usr/bin/mysql_secure_installation
fondamentale considerare le opzioni spiegate nel manuale
enable query cache:
/etc/mysql/my.cnf
query_cache_size = 20M query_cache_type=1 query_cache_limit=1M
default Memory size in Kb. needs
/etc/init.d/mysql restart
test and maintanance
SHOW VARIABLES LIKE '%query_cache%'; SHOW VARIABLES LIKE 'query_cache_size'; SHOW STATUS LIKE '%qcache%'; # provare valori senza configurare/riavviare SET GLOBAL query_cache_size = "30M"; FLUSH QUERY CACHE; SHOW processlist; SHOW full processlist;
installare mytop per profilare:
apt-get --yes install mytop
GRANT ALL PRIVILEGES ON $db TO $user@localhost; GRANT ALL ON $db.* TO '$user'@'localhost'; # es. dare permesso di accedere dall'esterno a root GRANT ALL ON mydb.* TO root@'%' IDENTIFIED BY 'MyPASSWORD'; # applica le modifiche FLUSH PRIVILEGES; SHOW GRANTS FOR '$user'@'localhost';
resettare la password di root:
mysqld_safe --skip-grant-tables & # successivamente ripartire e reimpostare la password come al solito
script test connessione:
<?php
$u ='aaa';
$p = 'bbb';
$a_db=[ 'db1', 'db2' ];
foreach($a_db as $db){
$mysqli = new mysqli("localhost", $u, $p, $db);
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
echo 'OK DB:'.$db."<br>\n";
$mysqli->close();
}
setup PHP connect to a mysql's unix socket, saving the TCP overhead.
// unix socket you need to use
// UTF-8 charset you can specify that in the DSN.
// You musnt not specify host when using socket.
$dsn = 'mysql:dbname=testdb;unix_socket=/path/to/socket';
$link = new PDO("$dns;dbname=DB;charset=UTF8");
per prima cosa abilitare le connessioni esterne nella configurazione del server e riavviare
vi /etc/mysql/my.cnf # default, secure # bind-address = 127.0.0.1 # bind-all address bind-address = 0.0.0.0
assicurarsi l'utente amministrativo sia abilitato alle connessioni esterne: @'%'
GRANT ALL PRIVILEGES ON *.* TO 'admin-usr'@'%' IDENTIFIED BY '$password' WITH GRANT OPTION; FLUSH PRIVILEGES;
assicurarsi che il server stia accettando connessioni:
telnet 192.168.1.X 3306
in caso contrario il firewall sta bloccando le connessioni al servizio.
-- datetime DELETE FROM x_log WHERE date_insert < DATE_SUB(NOW(), INTERVAL 14 DAY); DELETE FROM x_history where update_date < (CURDATE() - INTERVAL $days DAY); SELECT DATE_SUB(date_time_column, INTERVAL 1 WEEK) FROM x_table; --modificare una foreign key ALTER TABLE car_image DROP FOREIGN KEY `fk_car_images_car`; ALTER TABLE `car_image` DROP INDEX `fk_car_images_car` ;
-- tutti i campi di una specifica tabella/db select COLUMN_NAME from information_schema.columns where table_schema = 'your_db' and TABLE_NAME='articoli' order by table_name,ordinal_position
TO ADD A FOREIGN KEY TO AN EXISTING TABLE Where "favgenre" is the column of the table that has the foreign key and products_genre(gid) is the table and primary key you are referencing.
alter table users add foreign key(favGenre) references products_genre(gid); ALTER TABLE car_image ADD CONSTRAINT `fk_car_images_car` FOREIGN KEY (`car_id`) REFERENCES `car` (`car_id`) ON DELETE CASCADE ON UPDATE NO ACTION;
you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE
# 5.1 syntax ALTER TABLE `table_name` MODIFY COLUMN `column_name` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP; INSERT INTO Yourtable (Field1, YourDateField) VALUES('val1', NOW()) # TRIGGER CREATE TRIGGER trigger_foo_SetCreatedAt BEFORE INSERT ON foo FOR EACH ROW IF NEW.created_at IS NOT NULL SET NEW.created_at = UTC_TIMESTAMP(); # 5.5 syntax CREATE TABLE foo ( `creation_time` DATETIME DEFAULT CURRENT_TIMESTAMP, `modification_time` DATETIME ON UPDATE CURRENT_TIMESTAMP `update_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )
dalla versione 4.1 è cambiata la gestione delle password, alcuni client hanno linkato staticamente la libreria di connessione che usa il vecchio metodo per cui non sono in grado di inviare la nuova pasword risultando in un errore tipo
Client does not support authentication protocol requested by server; consider upgrading MySQL client
occorre impostare il server per riconoscere una password nel vecchio formato per quel l'utente da cui si tenta la connessione.
UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd') WHERE Host = 'tuo_host' AND User = 'nome_user'
oppure
SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('newpwd');
my.ini assicurarsi che contenga:
#Use old password encryption method (needed for 4.0 and older clients). old-passwords
se non chiuse correttamente le connessioni rimangono aperte, di default vengono chiuse dopo 8 ore, ma si può abbassare a 1h il timeout
wait_timeout = 3660 key_buffer_size = 2M
connessioni pendenti, server rallentato da troppe connessioni, eliminare chiudere connessioni e query troppo onerose
-- show status like '%onn%'; SHOW processlist; -- KILL CONNECTION 2628317; SHOW processlist;
come l'operatore = na non ritorna mai NULL, ritorna 1 invece che NULL se entrambe gli operandi sono NULL, ritorna 0 invece che NULL se un operando è NULL
test query:
SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL; SELECT 1 = 1, NULL = NULL, 1 = NULL;
testare per un valore null:
campo IS NOT NULL
lower_case_table_names
How table and database names are stored on disk and used in MySQL is affected by the lower_case_table_names system variable, which you can set when starting mysqld. lower_case_table_names can take the values shown in the following table. On Unix, the default value of lower_case_table_names is 0. On Windows the default value is 1. On Mac OS X, the default value is 2.
1: Table names are stored in lowercase on disk and name comparisons are not case sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.
http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html
// settare il charset a livello di connessione
mysql_set_charset('utf8', $link);
$mysqli->set_charset("utf8")
// settare il charset a livello del db
mysql_query("SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'");
mysql_query("set names UTF-8;");
ricordare inoltre: in php.ini use UTF-8 as the default character set:
default_charset = "utf-8";
assicura che la risposta sia interpretata utf8
header('Content-Type: text/html; charset=utf-8');
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
usa solo funzioni utf8 safe
bin/mysql -u root bin/mysqldump -u root -p dbname > dbname_backup.sql # mysql vuole caratteri ansi # HeidiSql formatatta utf-8 iconv --verbose -t ISO-8859-1 -f UTF-8 $nome_file -o $nome_file_ansi
schema compare
mysqldump -h localhost -u root -p --no-data dbname1>file1 mysqldump -h localhost -u root -p --no-data dbname2>file2 meld file1 file2
restore di un database:
// ricarica un database partendo da un dump
class CLI_Mysql {
public static function db_restore($db_user , $db_passwd, $db_name, $dump_filename){
`mysql -u $db_user -p $db_passwd -e "DROP DATABASE IF EXISTS $db_name"; `;
`mysql -u $db_user -p $db_passwd -e "CREATE DATABASE $db_name"; `;
`mysql -u $db_user -p $db_passwd $db_name < $dump_filename `;
}
}
isamcheck -a
isamcheck -d
isamcheck -r
optimize table su tutte le tabelle di un db
mysql -u uname dbname -e "show tables" | grep -v Tables_in | grep -v "+" | gawk '{print "optimize table " $1 ";"}' | mysql -u uname dbname
Dump ALL Databases
mysqldump --user=$USER --password=$PASSWORD -A > /path/to/dumpfile.sql Dump Individual or Multiple Databases
Dump only certain tables from a Database
mysqldump --user=$USER --password=$PASSWORD --databases DB_NAME1 DB_NAME2 > /path/to/dumpfile.sql
Restore A Database
mysql --verbose --user=$USER --password=$PASSWORD DB_NAME < /path/to/dumpfile.sql
Restore All Databases
mysql --verbose --user=$USER --password=$PASSWORD < /path/to/dumpfile.sql
#dump all databases in separate file for I in `echo "show databases;" | mysql | grep -v Database`; do mysqldump $I > "$I.sql"; done # dump all databases mysqldump --all-databases -u**** -p***** > $dump_filenames # restore: @see db_restore # eliminare files + vecchi di 3 giorni find /var/backup/mysql -type f -mtime +3 -exec rm {} \;
#full backup every morning 0 6 * * * mysqldump --all-databases -u**** -p***** > /var/backups/full-`date +%F`.sql # dump a table evry hour 0 * * * * mysqldump shop cust_order > /backup/cust_order_backup-`date +%F_%R`.sql
batch foreign key dependency checking :
#!/bin/sh if [ -z "$1" ] then echo "\nUsage:\n\t./`uname $0` <database> [-h <host>] [-u user] [-p <passwd>]\n" exit fi CONSTRAINTS=`mysqldump $* | grep "CREATE\|CONSTRAINT" | sed 's/ /+/g'` for c in $CONSTRAINTS do if [ "`echo $c | cut -d '+' -f 3`" = "CONSTRAINT" ] then CONSTRAINT=`echo $c | cut -d '+' -f 4 | tr -d '\`'` CHILD_KEY=`echo $c | cut -d '+' -f 7 | tr -d '()\`,'` PARENT_TABLE=`echo $c | cut -d '+' -f 9 | tr -d '\`'` PARENT_KEY=`echo $c | cut -d '+' -f 10 | tr -d '()\`,'` QUERY="select c.$CHILD_KEY from $CHILD_TABLE as c left join $PARENT_TABLE as p on p.$PARENT_KEY=c.$CHILD_KEY where c.$CHILD_KEY is not null and p.$PARENT_KEY is null;" echo "Checking table '$CHILD_TABLE' constraint '$CONSTRAINT'" mysql -verbose $* -e "$QUERY" else CHILD_TABLE=`echo $c | cut -d '+' -f 3` fi done
mysql -u root -p SET GLOBAL slow_query_log = 'ON'; # additional options: # By default logs query that takes longer than 10 s. SET GLOBAL long_query_time = 20; # the log file is located at /var/lib/mysql/hostname-slow.log SET GLOBAL slow_query_log_file = '/path/filename'; # To verify, log out of the mysql program, and then log back in to reload the session SELECT SLEEP(21);
debug e optimization
SHOW FULL PROCESSLIST; SHOW ENGINE INNODB STATUS; SHOW WARNINGS;
use MySQLTuner
mysqltuner
configuration:
less /etc/mysql/conf.d/mysql.cnf
Check the status of server regarding RAM and available disk space:
free -m df -h
If you have memory available, increase the innodb_buffer_pool_size so that there is more caching.
fragmented tables: Run OPTIMIZE TABLE and defragment all for better performance
Have a look in your slow_query.log, Check
Query_time Lock_time Rows_sent Rows_examined
If you find queries where ratio of Rows Sent / Rows Examined set is high, then those query is good for optimization. It may be possible that some queries in slow_query.log is utilizing a lot of CPU resources.
Your READ:WRITE ratio (eg. 61:39 ) gives the clue if engine types should be changed to Innodb. do you see queries in LOCK state.
Table locks: big limitation of MyISAM. If you have a huge number of concurrent writes and selects and query performance must be consistently fast, Innodb is the only choice due to locking mechanism (row-level)
others tune parameters in MySQL configuration
innodb_fast_shutdown=0 innodb_log_buffer_size innodb_log_file_size innodb_flush_method=O_DIRECT query_cache_size=0
loook anamaly in tables
SHOW TABLE STATUS FROM database_name; ## list the SIZE OF every TABLE IN every DATABASE, largest FIRST: 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; ## OPTIMIZE TABLE tableName; ## TO OPTIMIZE ALL TABLES ON your server IN ALL DATABASES you can USE e.g. commandline command: mysqlcheck --all-databases --optimize --skip-write-binlog
use query EXPLAIN on anomalous querys
id (query id)
select_type (type of statement)
table (table referenced)
type (join type)
possible_keys (which keys could have been used)
key (key that was used)
key_len (length of used key)
ref (columns compared to index)
rows (amount of rows searched)
Extra (additional information)
Sono le tabelle storiche di MySQL, derivanti direttamente dalle vecchie ISAM che sono dismesse ormai da anni. Garantiscono affidabilità e velocità. Su versione di MySQL più vecchie della 4.1 sono sicuramente da preferirsi alle INNODB.
Forniscono un vantaggio: la possibilità di poter utilizzare indici FULLTEXT per ricerche con ranking stile Google.
Il metodo di salvataggio dei dati è basato sulla costruzione e lavorazione di 3 file binari:
Per maggiori informazioni: http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html
è possibile fare un backup diretto dei file binari per l'importazione/esportazione della base dati. Con le tabelle INNODB è invece molto complesso in quanto i dati di TUTTI i database presenti sul server e che utilizzano tabelle INNODB vengono normalmente salvati nel file ibdata1.
Anche volendo salvare i file dei dati ognuno per tabella le transazioni e gli indici vengono comunque salvati nel TABLESPACE e quindi il recovery mediante import di tutti i file presente per esempio sotto: /var/lib/mysql/miodb/ NON FUNZIONA!
Le tabelle INNODB sono in grado di gestire l'integrità referenziale tra le chiave esterne del database, ora con il motore INNODB è possibile delegare questa annosa attività a MySQL stesso, potendo quindi specificare vari comportamenti a seconda della chiave esterna utilizzata.
Le transazioni sono fondamentali in situazioni tipiche da shop online dove sino a che non arriva conferma da parte della banca o di chi valida la carta di credito, le query non devono essere "eseguite realmente". Fondamentalmente si tratta di eseguire una serie di query che verranno validate o annullate mediante chiamate COMMIT o ROLLBACK.
al momento non è possibile dichiarare un campo come indice fulltext per poter eseguire ricerche con score
http://dev.mysql.com/doc/refman/5.0/en/innodb-overview.html
conviene realizzare tabelle MYISAM o INNODB? dipende dai requirements dell'applicazione.
This package provides the following utilities: mysqldbcompare : check two databases and identify any differences mysqldbcopy : copy databases from one MySQL server to another mysqldbexport : export a list of databases in a variety of formats mysqldbimport : import object definitions or data into a database mysqldiff : identify differences among database objects mysqldiskusage : show disk usage for one or more databases mysqlfailover : automatic replication health monitoring and failover mysqlindexcheck : check for redundant or duplicate indexes mysqlmetagrep : search MySQL servers for objects matching a pattern mysqlprocgrep : search MySQL servers for processes matching a pattern mysqlreplicate : setup replication among two MySQL servers mysqlrpladmin : administration utility for MySQL replication mysqlrplcheck : check prerequisities for replication mysqlrplshow : show slaves attached to a master mysqlserverclone : start a new instance of an existing MySQL server mysqlserverinfo : display common diagnostic information from a server mysqluserclone : copy a MySQL user to new user(s) on another server
errore:
of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
MySQL 5.7.5+ changed the way GROUP BY behaved in order to be SQL99 compliant (where in previous versions it was not).
check sql_mode variable of your server to ensure the flag is active.
sql_mode=only_full_group_by
solution: configure mysql with the previous behaviour on group by:
sudo nano /etc/mysql/my.cnf [mysqld] sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" sudo service mysql restart
or run the query per evry connection
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
generic methods:
-- adding only one mode to sql_mode SET sql_mode=(SELECT CONCAT(@@sql_mode, ',<mode_to_add>' )); -- removing only a specific mode SET sql_mode=(SELECT REPLACE(@@sql_mode, '<mode_to_remove>' ,''));
MySQL offers FULLTEXT searching. It searches tables with columns containing text for the best matches for words and phrases.
SET @searchTerm= 'Database Programming'; SELECT MATCH (Title) AGAINST (@searchTerm IN NATURAL LANGUAGE MODE) Score, ISBN, Author, Title FROM book WHERE MATCH (Title) AGAINST (@searchTerm IN NATURAL LANGUAGE MODE) ORDER BY MATCH (Title) AGAINST (@searchTerm IN NATURAL LANGUAGE MODE) DESC;
Given a table named book with columns named ISBN , 'Title', and 'Author', this finds books matching the terms 'Database Programming' . It shows the best matches first. For this to work, a fulltext index on the Title column must be available:
ALTER TABLE book ADD FULLTEXT INDEX Fulltext_title_index (Title);
SET @searchTerm= 'Database Programming -Java'; SELECT MATCH (Title) AGAINST (@searchTerm IN BOOLEAN MODE) Score, ISBN, Author, Title FROM book WHERE MATCH (Title) AGAINST (@searchTerm IN BOOLEAN MODE) ORDER BY MATCH (Title) AGAINST (@searchTerm IN BOOLEAN MODE) DESC;
Given a table named book with columns named ISBN , Title , and Author , this searches for books with the words 'Database' and 'Programming' in the title, but not the word 'Java' . For this to work, a fulltext index on the Title column must be available:
ALTER TABLE book ADD FULLTEXT INDEX Fulltext_title_index (Title);
SET @searchTerm= 'Date Database Programming'; SELECT MATCH (Title, Author) AGAINST (@searchTerm IN NATURAL LANGUAGE MODE) Score, ISBN, Author, Title FROM book WHERE MATCH (Title, Author) AGAINST (@searchTerm IN NATURAL LANGUAGE MODE) ORDER BY MATCH (Title, Author) AGAINST (@searchTerm IN NATURAL LANGUAGE MODE) DESC;
Given a table named book with columns named ISBN , Title , and Author , this finds books matching the terms 'Date Database Programming'. It shows the best matches first. The best matches include books written by Prof. C. J. Date. (But, one of the best matches is also The Date Doctor's Guide to Dating : How to Get from First Date to Perfect Mate. This shows up a limitation of FULLTEXT search: it doesn't pretend to understand such things as parts of speech or the meaning of the indexed words.) For this to work, a fulltext index on the Title and Author columns must be available:
ALTER TABLE book ADD FULLTEXT INDEX Fulltext_title_author_index (Title, Author);
Partita Iva: 02360450205
Content of this site is licensed under Creative Commons Attribution-Share Alike License.
