Difference between revisions of "MySQL"
From Alessandro's Wiki
(11 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
== Problemi == | == Problemi == | ||
* Un bel giorno ho avuto un problema... | * Un bel giorno ho avuto un problema... | ||
** errori in /var/log/messages: | ** errori in /var/log/messages: | ||
061013 18:14:58 [ERROR] Can t start server : Bind on unix socket: Permission denied | 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] Do you already have another mysqld server running on socket: //var/run/mysqld/mysqld7415.sock ? | ||
061013 18:14:58 [ERROR] Aborting | 061013 18:14:58 [ERROR] Aborting | ||
** si risolve con | ** si risolve con | ||
chown -R mysql:mysql /var/lib/mysql | chown -R mysql:mysql /var/lib/mysql | ||
chown -R mysql:mysql /var/run/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=1000000000; | |||
set global net_buffer_length=1000000; | |||
== SQL == | == 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" | 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 | 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)]] ... | GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... | ||
ON [object_type] {tbl_name | * | *.* | db_name.*} | ON [object_type] {tbl_name | * | *.* | db_name.*} | ||
Line 29: | Line 30: | ||
[SUBJECT 'subject']] | [SUBJECT 'subject']] | ||
[WITH with_option [with_option] ...] | [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]] | |||
== Resettare auto_increment ... == | |||
alter table "table_name" auto_increment=1 | |||
resets auto_increment to 1 + max(auto_increment) | |||
== In 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 |
Revision as of 23:47, 19 December 2010
Problemi
- Un bel giorno ho avuto un problema...
- errori 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
- si risolve con
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=1000000000; set global net_buffer_length=1000000;
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]]
Resettare auto_increment ...
alter table "table_name" auto_increment=1 resets auto_increment to 1 + max(auto_increment)
In 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