Tag Archives: mysql

by

MySQL: how to determine which config file is being used? 

$ which mysqld
/usr/sbin/mysqld

$ /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"
Default options are read from the following files in the given order:
/etc/mysql/my.cnf ~/.my.cnf /usr/etc/my.cnf

You can also do mysql –print-defaults to show you how the configuration values it will use. This can also be useful in identifying just which config file it is loading.

See also: http://www.dbasquare.com/2012/04/01/how-to-find-mysql-configuration-file/

by

Rename mysql database 

Create the new empty database, then rename each table in turn into the new database:

RENAME TABLE old_db.table TO new_db.table;

You will need to adjust the permissions after that.

Credits: http://stackoverflow.com/questions/67093/how-do-i-quickly-rename-a-mysql-database-change-schema-name

by

Mysql Error 1153 – Got a packet bigger than ‘max_allowed_packet’ bytes

Open a terminal, type mysql to get a mysql prompt, and issue these commands:

set global net_buffer_length=1000000; 
set global max_allowed_packet=1000000000;

Keep the mysql prompt open, and run your command-line SQL execution on a second terminal..

mysql --max_allowed_packet=100M -u root -p database < dump.sql

Thanks to: http://stackoverflow.com/questions/93128/mysql-error-1153-got-a-packet-bigger-than-max-allowed-packet-bytes”

by

Identifying MySQL slow queries 

Verifica
--------
> mysqladmin var |grep log_slow_queries
        | log_slow_queries                | OFF  


Modifica
--------
Nel file di configurazione, impostare:

[mysqld]
log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time  = 1

dove l'ultimo parametro definisce in secondi quali queries considerare lente (risoluzione: da 1 a 10 sec)

Nota
----

[Warning] '--log_slow_queries' is deprecated and will be removed in a future release. 
Please use ''--slow_query_log'/'--slow_query_log_file'' instead.

Example:

log-output = FILE
#log-output = TABLE
general_log = 1
general_log_file = /var/log/mysql/mysql-general.log
long_query_time = 1
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log