MySQL/Syntax: Unterschied zwischen den Versionen
Cbs (Diskussion | Beiträge) |
Cbs (Diskussion | Beiträge) (→Users, Passwords and Rights) |
||
(17 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt) | |||
Zeile 1: | Zeile 1: | ||
==Connect to mysql== | ==Connect to mysql== | ||
+ | |||
To login (from unix shell) use -h only if needed. | To login (from unix shell) use -h only if needed. | ||
# mysql -h hostname -u root -p | # mysql -h hostname -u root -p | ||
Zeile 30: | Zeile 31: | ||
Returns the columns and column information pertaining to the designated table. | Returns the columns and column information pertaining to the designated table. | ||
mysql> show columns from [table name]; | mysql> show columns from [table name]; | ||
+ | |||
==CREATE/COPY tables== | ==CREATE/COPY tables== | ||
+ | |||
Create Table Example 1. | Create Table Example 1. | ||
− | mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3), | + | mysql> CREATE TABLE [table name] ( |
− | + | firstname VARCHAR(20), | |
− | + | middleinitial VARCHAR(3), | |
+ | lastname VARCHAR(35), | ||
+ | suffix VARCHAR(3), | ||
+ | officeid VARCHAR(10), | ||
+ | userid VARCHAR(15), | ||
+ | username VARCHAR(8), | ||
+ | email VARCHAR(35), | ||
+ | phone VARCHAR(25), | ||
+ | groups VARCHAR(15), | ||
+ | datestamp DATE, | ||
+ | timestamp time, | ||
+ | pgpemail VARCHAR(255) | ||
+ | ); | ||
+ | |||
Create Table Example 2. | Create Table Example 2. | ||
− | mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35), | + | |
− | + | mysql> create table [table name] ( | |
+ | personid int(50) not null auto_increment primary key, | ||
+ | firstname varchar(35), | ||
+ | middlename varchar(50), | ||
+ | lastname varchar(50) default 'bato' | ||
+ | ); | ||
+ | |||
Copy a table | Copy a table | ||
+ | |||
CREATE TABLE student2 SELECT * FROM student; | CREATE TABLE student2 SELECT * FROM student; | ||
− | == SELECT's== | + | |
+ | == SELECT's == | ||
+ | |||
+ | get last insert ID | ||
+ | |||
+ | SELECT LAST_INSERT_ID(); | ||
+ | |||
+ | |||
select into OUTFILE e.g. .csv | select into OUTFILE e.g. .csv | ||
<pre> | <pre> | ||
Zeile 97: | Zeile 127: | ||
lookup.personid=person.personid=statement to join birthday in person table with primary illustration id; | lookup.personid=person.personid=statement to join birthday in person table with primary illustration id; | ||
− | ==UPDATE / ALTER== | + | |
+ | === SELECT DB- and/or Table-Sizes === | ||
+ | |||
+ | get size of all db-tables: | ||
+ | SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', | ||
+ | CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," Mb") AS Size FROM INFORMATION_SCHEMA.TABLES; | ||
+ | |||
+ | get size of specific DB-table: | ||
+ | SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', | ||
+ | CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," MB") AS Size FROM INFORMATION_SCHEMA.TABLES | ||
+ | where TABLE_SCHEMA like '%YOUR_DB_NAME%'; | ||
+ | |||
+ | get size of entire DB: | ||
+ | SELECT CONCAT(sum(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2))," MB") AS Size | ||
+ | FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA like '%YOUR_DB_NAME%' ; | ||
+ | |||
+ | |||
+ | == INSERT's == | ||
+ | |||
+ | insert multiple rows: | ||
+ | |||
+ | INSERT INTO x (a,b) VALUES ('1', 'one'), ('2', 'two'), ('3', 'three'); | ||
+ | |||
+ | |||
+ | |||
+ | == UPDATE / ALTER == | ||
+ | |||
To update info already in a table. | To update info already in a table. | ||
mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user'; | mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user'; | ||
Zeile 132: | Zeile 188: | ||
mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3); | mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3); | ||
− | ==Users, Passwords and Rights== | + | |
+ | == SHOW == | ||
+ | |||
+ | some SHOW statements | ||
+ | |||
+ | |||
+ | show create table statement | ||
+ | |||
+ | SHOW CREATE TABLE [table] \G | ||
+ | |||
+ | |||
+ | show variables set by *.cnf or daemon-parameter | ||
+ | |||
+ | SHOW VARIABLES; | ||
+ | |||
+ | or | ||
+ | |||
+ | SHOW VARIABLES LIKE 'query_cache_%'; | ||
+ | |||
+ | |||
+ | show binary logs to list available binlog-files | ||
+ | |||
+ | SHOW {BINARY | MASTER} LOGS | ||
+ | |||
+ | |||
+ | show contents of bin-logs | ||
+ | |||
+ | SHOW BINLOG EVENTS | ||
+ | [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count] | ||
+ | |||
+ | == Clustering/BinLogs == | ||
+ | |||
+ | Delete/Purge master logs (BinLogs) '''by date''': | ||
+ | |||
+ | purge_date=`date -d "8 days ago" "+%Y-%m-%d %H:%M:%S"` | ||
+ | mysql> PURGE MASTER LOGS BEFORE '${purge_date}'; | ||
+ | |||
+ | Delete/Purge master logs (BinLogs) '''by filename''': | ||
+ | |||
+ | PURGE BINARY LOGS TO 'mysql-bin.010'; | ||
+ | |||
+ | |||
+ | == Users, Passwords and Rights == | ||
+ | |||
Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs. | Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs. | ||
+ | |||
# mysql -u root -p | # mysql -u root -p | ||
mysql> use mysql; | mysql> use mysql; | ||
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password')); | mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password')); | ||
mysql> flush privileges; | mysql> flush privileges; | ||
+ | |||
Change a users password from unix shell.<br/> | Change a users password from unix shell.<br/> | ||
'''Better use 'GRANT'-syntax''' | '''Better use 'GRANT'-syntax''' | ||
+ | |||
# mysqladmin -u username -h hostname.blah.org -p password 'new-password' | # mysqladmin -u username -h hostname.blah.org -p password 'new-password' | ||
+ | |||
Change a users password from MySQL prompt. Login as root. Set the password. Update privs. | Change a users password from MySQL prompt. Login as root. Set the password. Update privs. | ||
+ | |||
# mysql -u root -p | # mysql -u root -p | ||
mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere'); | mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere'); | ||
mysql> flush privileges; | mysql> flush privileges; | ||
+ | |||
Set a root password if there is no root password. | Set a root password if there is no root password. | ||
+ | |||
# mysqladmin -u root password [new_super_secure_password] | # mysqladmin -u root password [new_super_secure_password] | ||
+ | |||
Update a root password. | Update a root password. | ||
+ | |||
# mysqladmin -u root -p oldpassword newpassword | # mysqladmin -u root -p oldpassword newpassword | ||
+ | |||
Allow the user "bob" to connect to the server from localhost using the password "passwd". Login as root. Switch to the MySQL db. Give privs. Update privs. | Allow the user "bob" to connect to the server from localhost using the password "passwd". Login as root. Switch to the MySQL db. Give privs. Update privs. | ||
+ | |||
# mysql -u root -p | # mysql -u root -p | ||
mysql> use mysql; | mysql> use mysql; | ||
mysql> grant usage on *.* to bob@localhost identified by 'passwd'; | mysql> grant usage on *.* to bob@localhost identified by 'passwd'; | ||
mysql> flush privileges; | mysql> flush privileges; | ||
+ | |||
Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs. | Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs. | ||
+ | |||
# mysql -u root -p | # mysql -u root -p | ||
mysql> use mysql; | mysql> use mysql; | ||
Zeile 166: | Zeile 278: | ||
('%','databasename','username','Y','Y','Y','Y','Y','N'); | ('%','databasename','username','Y','Y','Y','Y','Y','N'); | ||
mysql> flush privileges; | mysql> flush privileges; | ||
+ | |||
or | or | ||
Zeile 172: | Zeile 285: | ||
mysql> flush privileges; | mysql> flush privileges; | ||
− | set password for user | + | |
+ | or, if you have an allready encrypted/hashed password | ||
+ | |||
+ | mysql> grant all privileges on databasename.* to username@localhost identified by PASSWORD '*5005AAD8663A55CE7F024C008B5810A5B50D77EE' | ||
+ | [WITH GRANT OPTION]; | ||
+ | mysql> flush privileges; | ||
+ | |||
+ | |||
+ | to generate a 'NEW'-style hashed password with php: | ||
+ | |||
+ | php -a | ||
+ | Interactive shell | ||
+ | |||
+ | php > print "*" . strtoupper(sha1(sha1("Passwort", true))) . "\n"; | ||
+ | *5005AAD8663A55CE7F024C008B5810A5B50D77EE | ||
+ | |||
+ | |||
+ | or use PASSWORD-function to set password for user | ||
+ | |||
SET PASSWORD FOR openser@'%'=PASSWORD('123456'); | SET PASSWORD FOR openser@'%'=PASSWORD('123456'); | ||
SET PASSWORD FOR openser@localhost=PASSWORD('123456'); | SET PASSWORD FOR openser@localhost=PASSWORD('123456'); | ||
+ | |||
setze rechte | setze rechte | ||
+ | |||
GRANT ALL PRIVILEGES ON asterisk.* TO asteriskuser@localhost IDENTIFIED BY 'amp109'; | GRANT ALL PRIVILEGES ON asterisk.* TO asteriskuser@localhost IDENTIFIED BY 'amp109'; | ||
+ | |||
setze rechte inkl. grant-recht<br/> | setze rechte inkl. grant-recht<br/> | ||
heisst der user darf rechte vergeben... | heisst der user darf rechte vergeben... | ||
+ | |||
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; | ||
− | ==Backup/Dump/Copy data== | + | |
+ | revoke rights | ||
+ | |||
+ | REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'host; | ||
+ | |||
+ | {{Achtung|To delete some users completely (to revoke 'USAGE', too) you will have to use 'DROP USER user'}} | ||
+ | |||
+ | DROP USER username, username2, ...; | ||
+ | |||
+ | |||
+ | === replicate User/Password to 2nd server === | ||
+ | |||
+ | <pre> | ||
+ | while read line; do | ||
+ | usr=`echo $line | awk '{print $1}'`; | ||
+ | mysql -B -N -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') AS query FROM user" mysql | \ | ||
+ | grep "$usr" | mysql | sed -e "s/Grants for/-- Grants for/g"; | ||
+ | echo; echo; | ||
+ | done </tmp/mysqlusers.txt [> /bla/grants.sql] | ||
+ | </pre> | ||
+ | |||
+ | ==Backup/Dump/Copy== | ||
+ | |||
+ | Backup/Dump or copy data | ||
+ | |||
+ | |||
+ | === structure AND data === | ||
+ | |||
Dump all databases for backup. Backup file is sql commands to recreate all db's. | Dump all databases for backup. Backup file is sql commands to recreate all db's. | ||
# mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql | # mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql | ||
Zeile 195: | Zeile 357: | ||
Restore database (or database table) from backup. | Restore database (or database table) from backup. | ||
# mysql -u username -ppassword databasename < /tmp/databasename.sql | # mysql -u username -ppassword databasename < /tmp/databasename.sql | ||
+ | |||
+ | === structure only === | ||
+ | # mysqldump -u username -ppassword --no-data --databases databasename >/tmp/databasename.structure.sql | ||
===COPY databases=== | ===COPY databases=== | ||
+ | |||
Copy database on the same host | Copy database on the same host | ||
mysql> create database [DBname of COPY] | mysql> create database [DBname of COPY] | ||
Zeile 202: | Zeile 368: | ||
use -h option to mysqldump or mysql to pull/push db from/to a remote host | use -h option to mysqldump or mysql to pull/push db from/to a remote host | ||
− | ==Optimizer stuff== | + | |
+ | === replay to 2nd host === | ||
+ | |||
+ | replay queries to a 2nd host... <br/> | ||
+ | see: http://codeascraft.etsy.com/2013/03/19/the-perils-of-sql_mode/ | ||
+ | |||
+ | |||
+ | ==== percona playback ==== | ||
+ | |||
+ | see: http://www.percona.com/doc/percona-playback/index.html | ||
+ | |||
+ | |||
+ | ==== percona toolkit ==== | ||
+ | |||
+ | you need [http://go-lang.cat-v.org/packages golang] <br/> | ||
+ | you need [https://gist.github.com/keyurdg/5189836 mysql_query_multiplexer.go] <br/> | ||
+ | |||
+ | you can prevent using golang if you use percona-playback package which is precompiled <br/> | ||
+ | for debian squeeze+ available... <br/> | ||
+ | |||
+ | mysql-production # /usr/sbin/tcpdump -i INTERFACE \ | ||
+ | "port 3306 and tcp[1] & 7 == 2 and tcp[3] & 7 == 2" \ | ||
+ | -s 65535 -x -n -q -tttt | \ | ||
+ | nc testdb.domain.tld 20000 | ||
+ | |||
+ | testdb # nc -l 20000 | pt-query-digest --type tcpdump \ | ||
+ | --no-report --print | grep -v "SET NAMES utf8" | \ | ||
+ | go run mysql_query_multiplexer.go -db-user=username \ | ||
+ | -db-password=password -db-host=localhost \ | ||
+ | -db-charset=utf8mb4 -log "/var/tmp/shard.log" \ | ||
+ | -db-name=etsy_shard -threads=8 | ||
+ | |||
+ | |||
+ | === hotcopy === | ||
+ | |||
+ | see: [[LVM#LVM_Snapshot.2FBackup|LVM]] | ||
+ | |||
+ | |||
+ | == Optimizer stuff == | ||
+ | |||
mysql> explain extended select ...; | mysql> explain extended select ...; | ||
mysql> show warnings \G | mysql> show warnings \G | ||
Shows what the optimizer actually did. In this example, An IN was converted to EXISTS | Shows what the optimizer actually did. In this example, An IN was converted to EXISTS | ||
− | ==Random SELECT== | + | |
+ | ==Misc== | ||
+ | |||
+ | some misc things, mostly administrative... <br/> | ||
+ | |||
+ | |||
+ | === get/calc sizes of columns === | ||
+ | |||
+ | <pre> | ||
+ | SELECT table_name AS "Table", | ||
+ | round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" | ||
+ | FROM information_schema.TABLES | ||
+ | WHERE table_schema = "$DB_NAME" | ||
+ | AND table_name = "$TABLE_NAME"; | ||
+ | </pre> | ||
+ | |||
+ | |||
+ | === check limits of integer-columns === | ||
+ | |||
+ | check limits of integer-columns inside MySQL. <br/> | ||
+ | Attention(!): needs IOPS and time.<br/> | ||
+ | |||
+ | <pre> | ||
+ | SELECT | ||
+ | TABLE_SCHEMA, | ||
+ | TABLE_NAME, | ||
+ | COLUMN_NAME, | ||
+ | DATA_TYPE, | ||
+ | COLUMN_TYPE, | ||
+ | IF( | ||
+ | LOCATE('unsigned', COLUMN_TYPE) > 0, | ||
+ | 1, | ||
+ | 0 | ||
+ | ) AS IS_UNSIGNED, | ||
+ | ( | ||
+ | CASE DATA_TYPE | ||
+ | WHEN 'tinyint' THEN 255 | ||
+ | WHEN 'smallint' THEN 65535 | ||
+ | WHEN 'mediumint' THEN 16777215 | ||
+ | WHEN 'int' THEN 4294967295 | ||
+ | WHEN 'bigint' THEN 18446744073709551615 | ||
+ | END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1) | ||
+ | ) AS MAX_VALUE, | ||
+ | AUTO_INCREMENT, | ||
+ | AUTO_INCREMENT / ( | ||
+ | CASE DATA_TYPE | ||
+ | WHEN 'tinyint' THEN 255 | ||
+ | WHEN 'smallint' THEN 65535 | ||
+ | WHEN 'mediumint' THEN 16777215 | ||
+ | WHEN 'int' THEN 4294967295 | ||
+ | WHEN 'bigint' THEN 18446744073709551615 | ||
+ | END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1) | ||
+ | ) AS AUTO_INCREMENT_RATIO | ||
+ | FROM | ||
+ | INFORMATION_SCHEMA.COLUMNS | ||
+ | INNER JOIN INFORMATION_SCHEMA.TABLES USING (TABLE_SCHEMA, TABLE_NAME) | ||
+ | WHERE | ||
+ | TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA', 'performance_schema') | ||
+ | AND EXTRA='auto_increment' | ||
+ | ; | ||
+ | </pre> | ||
+ | |||
+ | |||
+ | === get tables 'last update' timestamp === | ||
+ | |||
+ | select table_schema,table_name,update_time from information_schema.tables [where table_schema = "OneOfMyDatabases"]; | ||
+ | |||
+ | ===Random SELECT=== | ||
+ | |||
My solution to this problem is using User Defined Variables. Let’s say we want X random rows from a table myTable. | My solution to this problem is using User Defined Variables. Let’s say we want X random rows from a table myTable. | ||
Zeile 238: | Zeile 511: | ||
FROM (SELECT @num:=0) a , myTable LIMIT X) b LEFT JOIN myTable c ON( c.id=b.num); | FROM (SELECT @num:=0) a , myTable LIMIT X) b LEFT JOIN myTable c ON( c.id=b.num); | ||
− | |||
===fragmented tables=== | ===fragmented tables=== | ||
+ | |||
SELECT COUNT(TABLE_NAME) | SELECT COUNT(TABLE_NAME) | ||
FROM information_schema.TABLES | FROM information_schema.TABLES | ||
Zeile 255: | Zeile 528: | ||
OPTIMIZE TABLE db.table1, db.table2, db2.table1, ...; | OPTIMIZE TABLE db.table1, db.table2, db2.table1, ...; | ||
defragmentieren ;-) | defragmentieren ;-) | ||
+ | |||
===Crashed Tables=== | ===Crashed Tables=== | ||
+ | |||
CHECK TABLE [tabelle] | CHECK TABLE [tabelle] | ||
and | and |
Aktuelle Version vom 23. Januar 2015, 14:31 Uhr
Inhaltsverzeichnis
Connect to mysql
To login (from unix shell) use -h only if needed.
# mysql -h hostname -u root -p
Create a database on the sql server.
mysql> create database [databasename];
List all databases on the sql server.
mysql> show databases;
Switch to a database.
mysql> use [db name];
To see all the tables in the db.
mysql> show tables;
To see database's field formats.
mysql> DESCRIBE [table name]; mysql> SHOW CREATE table [table name] \G
To delete a db.
mysql> drop database [database name];
To delete a table.
mysql> drop table [table name];
Show all data in a table.
mysql> SELECT * FROM [table name];
Returns the columns and column information pertaining to the designated table.
mysql> show columns from [table name];
CREATE/COPY tables
Create Table Example 1.
mysql> CREATE TABLE [table name] ( firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35), suffix VARCHAR(3), officeid VARCHAR(10), userid VARCHAR(15), username VARCHAR(8), email VARCHAR(35), phone VARCHAR(25), groups VARCHAR(15), datestamp DATE, timestamp time, pgpemail VARCHAR(255) );
Create Table Example 2.
mysql> create table [table name] ( personid int(50) not null auto_increment primary key, firstname varchar(35), middlename varchar(50), lastname varchar(50) default 'bato' );
Copy a table
CREATE TABLE student2 SELECT * FROM student;
SELECT's
get last insert ID
SELECT LAST_INSERT_ID();
select into OUTFILE e.g. .csv
SELECT dl . * , ht.typ, h.hersteller INTO OUTFILE "/tmp/goober_downloads.txt" FIELDS TERMINATED BY ',' FROM `downloads` dl JOIN handy_typen ht ON dl.handyid = ht.handyid JOIN handy_hersteller h ON dl.herstellerid = h.herstellerid WHERE dldate > '2006-05-14'
select into table
mysql> INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
Show certain selected rows with the value "whatever".
mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";
Show all records containing the name "Bob" AND the phone number '3444444'.
mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';
Show all records not containing the name "Bob" AND the phone number '3444444' order by the phone_number field.
mysql> SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;
Show all records starting with the letters 'bob' AND the phone number '3444444'.
mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';
Show all records starting with the letters 'bob' AND the phone number '3444444' limit to records 1 through 5.
mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;
Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.
mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";
Show unique records.
mysql> SELECT DISTINCT [column name] FROM [table name];
Show selected records sorted in an ascending (asc) or descending (desc).
mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
Return number of rows.
mysql> SELECT COUNT(*) FROM [table name];
Sum column.
mysql> SELECT SUM(*) FROM [table name];
Join tables on common columns.
mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;
SELECT DB- and/or Table-Sizes
get size of all db-tables:
SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," Mb") AS Size FROM INFORMATION_SCHEMA.TABLES;
get size of specific DB-table:
SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," MB") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA like '%YOUR_DB_NAME%';
get size of entire DB:
SELECT CONCAT(sum(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2))," MB") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA like '%YOUR_DB_NAME%' ;
INSERT's
insert multiple rows:
INSERT INTO x (a,b) VALUES ('1', 'one'), ('2', 'two'), ('3', 'three');
UPDATE / ALTER
To update info already in a table.
mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';
Delete a row(s) from a table.
mysql> DELETE from [table name] where [field name] = 'whatever';
Delete a column.
mysql> alter table [table name] drop column [column name];
Add a new column to db.
mysql> alter table [table name] add column [new column name] varchar (20);
Change column name.
mysql> alter table [table name] change [old column name] [new column name] varchar (50);
Make a unique column so you get no dupes.
mysql> alter table [table name] add unique ([column name]);
Make a column bigger.
mysql> alter table [table name] modify [column name] VARCHAR(3);
Add new column AND index over that column:
mysql> alter table [table name] add [NEW column name] INT(11) not null default 0 after [existent column name], add index [NEW column name]_idx ([NEW column name]);
Add primary key to column
ALTER TABLE [table name] ADD PRIMARY KEY ([column name]);
Delete unique from table.
mysql> alter table [table name] drop index [colmn name];
Load a CSV file into a table.
mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);
SHOW
some SHOW statements
show create table statement
SHOW CREATE TABLE [table] \G
show variables set by *.cnf or daemon-parameter
SHOW VARIABLES;
or
SHOW VARIABLES LIKE 'query_cache_%';
show binary logs to list available binlog-files
SHOW {BINARY | MASTER} LOGS
show contents of bin-logs
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
Clustering/BinLogs
Delete/Purge master logs (BinLogs) by date:
purge_date=`date -d "8 days ago" "+%Y-%m-%d %H:%M:%S"` mysql> PURGE MASTER LOGS BEFORE '${purge_date}';
Delete/Purge master logs (BinLogs) by filename:
PURGE BINARY LOGS TO 'mysql-bin.010';
Users, Passwords and Rights
Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.
# mysql -u root -p mysql> use mysql; mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password')); mysql> flush privileges;
Change a users password from unix shell.
Better use 'GRANT'-syntax
# mysqladmin -u username -h hostname.blah.org -p password 'new-password'
Change a users password from MySQL prompt. Login as root. Set the password. Update privs.
# mysql -u root -p mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere'); mysql> flush privileges;
Set a root password if there is no root password.
# mysqladmin -u root password [new_super_secure_password]
Update a root password.
# mysqladmin -u root -p oldpassword newpassword
Allow the user "bob" to connect to the server from localhost using the password "passwd". Login as root. Switch to the MySQL db. Give privs. Update privs.
# mysql -u root -p mysql> use mysql; mysql> grant usage on *.* to bob@localhost identified by 'passwd'; mysql> flush privileges;
Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.
# mysql -u root -p mysql> use mysql; mysql> INSERT INTO user (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N'); mysql> flush privileges;
or
mysql> grant all privileges on databasename.* to username@localhost identified by 'passwd' [WITH GRANT OPTION]; mysql> flush privileges;
or, if you have an allready encrypted/hashed password
mysql> grant all privileges on databasename.* to username@localhost identified by PASSWORD '*5005AAD8663A55CE7F024C008B5810A5B50D77EE' [WITH GRANT OPTION]; mysql> flush privileges;
to generate a 'NEW'-style hashed password with php:
php -a Interactive shell php > print "*" . strtoupper(sha1(sha1("Passwort", true))) . "\n"; *5005AAD8663A55CE7F024C008B5810A5B50D77EE
or use PASSWORD-function to set password for user
SET PASSWORD FOR openser@'%'=PASSWORD('123456'); SET PASSWORD FOR openser@localhost=PASSWORD('123456');
setze rechte
GRANT ALL PRIVILEGES ON asterisk.* TO asteriskuser@localhost IDENTIFIED BY 'amp109';
setze rechte inkl. grant-recht
heisst der user darf rechte vergeben...
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
revoke rights
REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'host;
To delete some users completely (to revoke 'USAGE', too) you will have to use 'DROP USER user' |
DROP USER username, username2, ...;
replicate User/Password to 2nd server
while read line; do usr=`echo $line | awk '{print $1}'`; mysql -B -N -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') AS query FROM user" mysql | \ grep "$usr" | mysql | sed -e "s/Grants for/-- Grants for/g"; echo; echo; done </tmp/mysqlusers.txt [> /bla/grants.sql]
Backup/Dump/Copy
Backup/Dump or copy data
structure AND data
Dump all databases for backup. Backup file is sql commands to recreate all db's.
# mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql
Dump one database for backup.
# mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql
Dump a table from a database.
# mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
Restore database (or database table) from backup.
# mysql -u username -ppassword databasename < /tmp/databasename.sql
structure only
# mysqldump -u username -ppassword --no-data --databases databasename >/tmp/databasename.structure.sql
COPY databases
Copy database on the same host
mysql> create database [DBname of COPY] mysql> mysqldump [orig. DBname] | mysql [DBname of COPY]
use -h option to mysqldump or mysql to pull/push db from/to a remote host
replay to 2nd host
replay queries to a 2nd host...
see: http://codeascraft.etsy.com/2013/03/19/the-perils-of-sql_mode/
percona playback
see: http://www.percona.com/doc/percona-playback/index.html
percona toolkit
you need golang
you need mysql_query_multiplexer.go
you can prevent using golang if you use percona-playback package which is precompiled
for debian squeeze+ available...
mysql-production # /usr/sbin/tcpdump -i INTERFACE \ "port 3306 and tcp[1] & 7 == 2 and tcp[3] & 7 == 2" \ -s 65535 -x -n -q -tttt | \ nc testdb.domain.tld 20000
testdb # nc -l 20000 | pt-query-digest --type tcpdump \ --no-report --print | grep -v "SET NAMES utf8" | \ go run mysql_query_multiplexer.go -db-user=username \ -db-password=password -db-host=localhost \ -db-charset=utf8mb4 -log "/var/tmp/shard.log" \ -db-name=etsy_shard -threads=8
hotcopy
see: LVM
Optimizer stuff
mysql> explain extended select ...; mysql> show warnings \G
Shows what the optimizer actually did. In this example, An IN was converted to EXISTS
Misc
some misc things, mostly administrative...
get/calc sizes of columns
SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "$DB_NAME" AND table_name = "$TABLE_NAME";
check limits of integer-columns
check limits of integer-columns inside MySQL.
Attention(!): needs IOPS and time.
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, IF( LOCATE('unsigned', COLUMN_TYPE) > 0, 1, 0 ) AS IS_UNSIGNED, ( CASE DATA_TYPE WHEN 'tinyint' THEN 255 WHEN 'smallint' THEN 65535 WHEN 'mediumint' THEN 16777215 WHEN 'int' THEN 4294967295 WHEN 'bigint' THEN 18446744073709551615 END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1) ) AS MAX_VALUE, AUTO_INCREMENT, AUTO_INCREMENT / ( CASE DATA_TYPE WHEN 'tinyint' THEN 255 WHEN 'smallint' THEN 65535 WHEN 'mediumint' THEN 16777215 WHEN 'int' THEN 4294967295 WHEN 'bigint' THEN 18446744073709551615 END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1) ) AS AUTO_INCREMENT_RATIO FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN INFORMATION_SCHEMA.TABLES USING (TABLE_SCHEMA, TABLE_NAME) WHERE TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA', 'performance_schema') AND EXTRA='auto_increment' ;
get tables 'last update' timestamp
select table_schema,table_name,update_time from information_schema.tables [where table_schema = "OneOfMyDatabases"];
Random SELECT
My solution to this problem is using User Defined Variables. Let’s say we want X random rows from a table myTable.
SELECT myTable.* FROM (SELECT FLOOR (RAND() * (SELECTcount(*) FROM myTable)) num ,@num:=@num+1 from (SELECT @num:=0) a , myTable LIMIT X) b , myTable WHERE b.num=myTable.id;
I will try to explain some of the parts:
- FLOOR (RAND() * (SELECT count(*) FROM myTable)) num - produce an integer value between 0 to the max rows this table has
- (SELECT @num:=0) a – initialize the User Defined Variable a to zero
- (SELECT FLOOR (RAND() * (SELECTcount(*) FROM myTable)) num ,@num:=@num+1 from (SELECT @num:=0) a , myTable LIMIT X) b – produces X random integer numbers
If you need it with range, say X random rows from a table myTable with a range from S to T you can write it like this:
SELECT myTable.* FROM (SELECT FLOOR (S + RAND() * (T – S)) num ,@num:=@num+1 FROM (SELECT @num:=0) a , myTable LIMIT X) b , myTable WHERE b.num=myTable.id;
Some caution:
because this query uses User Defined Variable (@num) which are connection specific, you can NOT use this MySQL query with a system that uses connection pooling or persistent connections.
If there are some holes in the table you can fix it by:
SELECT (SELECT myTable2.id from myTable myTable2 where myTable2.id>=b.num LIMIT 1) c FROM (SELECT FLOOR(RAND() * (SELECTcount(*) FROM myTable)) num ,@num:=@num+1 FROM (SELECT @num:=0) a , myTable LIMIT X) b ;
To improve performance you can combine this solution with a left join:
SELECT IF(c.id is null, (SELECT c2.id FROM myTable c2 WHERE c2.id > b.num LIMIT 1), c.id) d FROM (SELECT FLOOR(RAND() * (SELECTcount(*) FROM myTable)) num ,@num:=@num+1 FROM (SELECT @num:=0) a , myTable LIMIT X) b LEFT JOIN myTable c ON( c.id=b.num);
fragmented tables
SELECT COUNT(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 0 AND NOT ENGINE='MEMORY';
anzahl fragmentierter tabellen auslesen
SELECT concat(TABLE_SCHEMA, ".", TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 0 AND NOT ENGINE='MEMORY';
namen fragmentierter tabellen auslesen
OPTIMIZE TABLE db.table1, db.table2, db2.table1, ...;
defragmentieren ;-)
Crashed Tables
CHECK TABLE [tabelle]
and
REPAIR TABLE [tabelle]
if that does not do the job try:
mysqladmin shutdown # (or use init-script) myisamchk *.MYI
or
myisamchk -e *.MYI # if you have more time.
perhaps use -s (silent) option
after that first run:
myisamchk -r -q [tabelle]
to try an quick recover
if that didn't do the job:
- create a backup of you database-files (*.frm, *.MYD, *.MYI)
-
myisamchk -r
to recover - if previous step failed:
myisamchk --safe-recover [tabelle]
- if that all does not work refer to: Mysql-Page