MySQL

maintenance

• check and repair all tables and databases

mysqloptimize ..

mysqlanalyze ..

mysqlcheck -hhost -ppass --all-databases

mysqlrepair  -hhost -ppass --all-databases

Problems

• Permission Denied

• in /var/log/messages:

061013 18:14:58 [ERROR] Can t start server : Bind on unix socket: Permission denied
061013 18:14:58 [ERROR] Do you already have another mysqld server running on socket: //var/run/mysqld/mysqld7415.sock ?
061013 18:14:58 [ERROR] Aborting

• solved with

chown -R mysql:mysql /var/lib/mysql
chown -R mysql:mysql /var/run/mysql

• ERROR 1153: Got a packet bigger than 'max_allowed_packet' bytes

# mysql -p
set global max_allowed_packet=1G;

• or some say...

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

• error: 1017: mysqldump: error while using LOCK TABLES

CHECK TABLE table;

• and

REPAIR TABLE table;

monitoriong

multitail -e "(Connect|Init)" -ev user /var/log/mysql/mysql.log

SQL

• Comando per copiare le thumbnails di amarok in una cartella

mysql -s -N -B -u user -p -D amarokdb -e "SELECT path FROM images"
for a in `mysql -s -N -B -u user -p -D db -e "SELECT path FROM images"`; do cp -v "$a" /var/www/localhost/htdocs/media/covers/; done

Grant, utenti e privilegi

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
   ON [object_type] {tbl_name | * | *.* | db_name.*}
   TO user [IDENTIFIED BY [PASSWORD] 'password']
       [, user [IDENTIFIED BY [PASSWORD] 'password']] ...
   [REQUIRE
       NONE |
       [{SSL| X509}]
       [CIPHER 'cipher' [AND]]
       [ISSUER 'issuer' [AND]]
       [SUBJECT 'subject']]
   [WITH with_option [with_option] ...]
GRANT SELECT ON test.* TO 'nagios'@'localhost'  IDENTIFIED BY 'la password che vuoi';
GRANT SELECT,DROP,CREATE,UPDATE,INSERT ON *.* TO user@localhost IDENTIFIED BY 'pass' ;

Select

SELECT
   [ALL | DISTINCT | DISTINCTROW ]
     [HIGH_PRIORITY]
     [STRAIGHT_JOIN]
     [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
     [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
   select_expr, ...
   [FROM table_references
   [WHERE where_condition]
   [GROUP BY {col_name | expr | position}
     [ASC | DESC], ... [WITH ROLLUP]]
   [HAVING where_condition]
   [ORDER BY {col_name | expr | position}
     [ASC | DESC], ...]
   [LIMIT {[offset,] row_count | row_count OFFSET offset}]
   [PROCEDURE procedure_name(argument_list)]
   [INTO OUTFILE 'file_name' export_options
     | INTO DUMPFILE 'file_name'
     | INTO var_name [, var_name]]
   [FOR UPDATE | LOCK IN SHARE MODE]]

Reset auto increment

alter table "table_name" auto_increment=1
resets auto_increment to 1 + max(auto_increment)

charset!

• we all want utf now

SET NAMES utf8;
SET CHARACTER SET utf8;
SET COLLATION_CONNECTION='utf8_general_ci;

Gentoo

installare e poi configurare mysql:

emerge -pvtD dev-db/mysql
emerge --config dev-db/mysql

Backups

shell> mysqldump db_name > backup-file.sql

You can read the dump file back into the server like this:

shell> mysql db_name < backup-file.sql

Or like this:

shell> mysql -e "source /path-to-backup/backup-file.sql" db_name

mysqldump is also very useful for populating databases by copying data from one MySQL server to another:

shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name

It is possible to dump several databases with one command:

shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql

To dump all databases, use the --all-databases option:

shell> mysqldump --all-databases > all_databases.sql

For InnoDB tables, mysqldump provides a way of making an online backup:

shell> mysqldump --all-databases --single-transaction > all_databases.sql

Da uno all'altro

mysqldump -c --skip-set-charset --skip-opt --skip-add-drop-table dbname tab1 tab2 -uunutente -hunhost -p | mysql dbname -p