Difference between revisions of "MySQL"

From Alessandro's Wiki
 
(27 intermediate revisions by the same user not shown)
Line 1: Line 1:
== Problemi ==
+
== 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!

  • 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