MySQL
From Alessandro's Wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
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