Difference between revisions of "MySQL"
From Alessandro's Wiki
(27 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
− | == |
+ | == maintenance == |
+ | *; check and repair all tables and databases |
||
− | * Un bel giorno ho avuto un problema... |
||
+ | mysqloptimize .. |
||
− | ** errori in /var/log/messages: |
||
+ | |||
+ | 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] 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 |
||
+ | * solved with |
||
− | |||
− | ** 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 |
||
− | == SQL == |
||
+ | # 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 |
||
− | mysql -s -N -B -u user -p -D amarokdb -e "SELECT path FROM images" |
||
+ | CHECK TABLE table; |
||
− | 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 |
||
+ | * and |
||
+ | REPAIR TABLE table; |
||
+ | == monitoriong == |
||
+ | multitail -e "(Connect|Init)" -ev user /var/log/mysql/mysql.log |
||
− | === Grant === |
||
+ | |||
+ | * logs: |
||
+ | select @@log_error; |
||
+ | |||
+ | = percona= |
||
+ | dev-db/percona-toolkit |
||
+ | |||
+ | == 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)]] ... |
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 59: | ||
[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 ON test.* TO 'nagios'@'localhost' IDENTIFIED BY 'la password che vuoi'; |
||
+ | GRANT SELECT,DROP,CREATE,UPDATE,INSERT ON *.* TO user@localhost IDENTIFIED BY 'pass' ; |
||
+ | |||
+ | * MySQL: “Access denied for user 'debian-sys-maint'@'localhost'..... |
||
+ | GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'blablablaa'; |
||
+ | |||
+ | === 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! == |
||
+ | |||
+ | * a good guide to debug: http://kunststube.net/frontback/ |
||
+ | |||
+ | * we all want utf now |
||
+ | SET NAMES utf8; |
||
+ | SET CHARACTER SET utf8; |
||
+ | SET COLLATION_CONNECTION='utf8_general_ci; |
||
+ | |||
+ | === conversion within MySQL === |
||
+ | ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; |
||
+ | |||
+ | === conversion script === |
||
+ | |||
+ | ''(from http://stackoverflow.com/questions/153706/change-default-collation-in-phpmyadmin )'' |
||
+ | |||
+ | * To convert already imported tables to UTF-8 you can do (in PHP): |
||
+ | |||
+ | <pre> |
||
+ | $dbname = 'my_databaseName'; |
||
+ | mysql_connect('127.0.0.1', 'root', ''); |
||
+ | mysql_query("ALTER DATABASE `$dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci"); |
||
+ | $res = mysql_query("SHOW TABLES FROM `$dbname`"); |
||
+ | while($row = mysql_fetch_row($res)) { |
||
+ | $query = "ALTER TABLE {$dbname}.`{$row[0]}` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci"; |
||
+ | mysql_query($query); |
||
+ | $query = "ALTER TABLE {$dbname}.`{$row[0]}` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci"; |
||
+ | mysql_query($query); |
||
+ | } |
||
+ | echo 'all tables converted'; |
||
+ | |||
+ | </pre> |
||
+ | |||
+ | == Gentoo == |
||
+ | installare e poi configurare mysql: |
||
+ | emerge -pvtD dev-db/mysql |
||
+ | emerge --config dev-db/mysql |
||
+ | |||
+ | == Replication == |
||
+ | * |
||
+ | * http://www.lefred.be/?q=node/45 |
||
+ | |||
+ | * /etc/my.cnf |
||
+ | <syntaxhighlight lang=bash> |
||
+ | [mysqld] |
||
+ | datadir=/var/lib/mysql |
||
+ | socket=/var/lib/mysql/mysql.sock |
||
+ | user=mysql |
||
+ | # Disabling symbolic-links is recommended to prevent assorted security risks |
||
+ | symbolic-links=0 |
||
+ | server-id=8493237 |
||
+ | log-bin=mysql-bin |
||
+ | log-error=mysql-bin.err |
||
+ | |||
+ | [mysqld_safe] |
||
+ | log-error=/var/log/mysqld.log |
||
+ | pid-file=/var/run/mysqld/mysqld.pid |
||
+ | </syntaxhighlight> |
||
+ | |||
+ | === re-sync slave === |
||
+ | * http://blog.gurudelleccelsopicco.org/2009/09/howto-mysql-master-slave-resync/ |
||
+ | |||
+ | |||
+ | <syntaxhighlight lang=bash> |
||
+ | CHANGE MASTER TO MASTER_HOST='mysql-master' MASTER_PORT=3306 , MASTER_USER='replicator' , MASTER_PASSWORD='a password'; |
||
+ | </syntaxhighlight> |
||
+ | |||
+ | == 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 |
||
+ | |||
+ | * dump all to a gzip/bzip2 file, change the command to choose between compression methods |
||
+ | mysqldump --all-databases | gzip > mysqldump-all-databases.sql.gz |
||
+ | * dump back up: |
||
+ | gunzip < mysqldump-all-databases.sql.gz | mysql -p |
Latest revision as of 18:33, 15 December 2015
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
- logs:
select @@log_error;
percona
dev-db/percona-toolkit
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' ;
- MySQL: “Access denied for user 'debian-sys-maint'@'localhost'.....
GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'blablablaa';
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!
- a good guide to debug: http://kunststube.net/frontback/
- we all want utf now
SET NAMES utf8; SET CHARACTER SET utf8; SET COLLATION_CONNECTION='utf8_general_ci;
conversion within MySQL
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
conversion script
(from http://stackoverflow.com/questions/153706/change-default-collation-in-phpmyadmin )
- To convert already imported tables to UTF-8 you can do (in PHP):
$dbname = 'my_databaseName'; mysql_connect('127.0.0.1', 'root', ''); mysql_query("ALTER DATABASE `$dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci"); $res = mysql_query("SHOW TABLES FROM `$dbname`"); while($row = mysql_fetch_row($res)) { $query = "ALTER TABLE {$dbname}.`{$row[0]}` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci"; mysql_query($query); $query = "ALTER TABLE {$dbname}.`{$row[0]}` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci"; mysql_query($query); } echo 'all tables converted';
Gentoo
installare e poi configurare mysql:
emerge -pvtD dev-db/mysql emerge --config dev-db/mysql
Replication
- /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=8493237
log-bin=mysql-bin
log-error=mysql-bin.err
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
re-sync slave
CHANGE MASTER TO MASTER_HOST='mysql-master' MASTER_PORT=3306 , MASTER_USER='replicator' , MASTER_PASSWORD='a password';
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
- dump all to a gzip/bzip2 file, change the command to choose between compression methods
mysqldump --all-databases | gzip > mysqldump-all-databases.sql.gz
- dump back up:
gunzip < mysqldump-all-databases.sql.gz | mysql -p