Mysql

ted Thursday 28 November 2013
http://www.mysql.com/
http://dev.mysql.com/
9930
10933
mysql -u dbuser -p --- login to mysql
show databases; --- list databases
use dbname; --- select database
show tables; --- list database's tables
describe tablename; --- show table's structure
select * from tablename; --- show everything from tablename

create database dbname;
grant all privileges on *.* to 'fortroubleshootingonly'@'localhost' identified by 'password' with grant option;
grant select, insert, update, delete, create, drop, index, alter, create temporary tables, lock tables on dbname.* to 'username'@'localhost' identified by 'password';
grant all privileges on `dbname`.* to 'user'@'localhost' identified by 'password';

show grants for 'username'; - show permissions
select * from mysql.user; - show all users
SET PASSWORD FOR 'user-name-here'@'hostname-name-here' = PASSWORD('new-password-here'); - change user password
http://www.cyberciti.biz/faq/mysql-change-user-password/
SELECT TABLE_NAME FROM information_schema.TABLES WHERE `TABLE_SCHEMA` = 'my_db_name' LIMIT 10 -- show tables paging
delete from tablename; -- delete all rows from table

**update configtable set value='http://somedomain.net/' where setting='SystemURL';**

LOG
set global general_log='on';
set global general_log='off';

create table test_in (

id bigint not null auto_increment primary key,
invid bigint,
ifin bool,
adid mediumint,
brtk tinytext,
xiry varchar(4),
brindex bigint,
branmode varchar(3),
ted timestamp default now()
);
mysql> create table test_out (
-> id int not null auto_increment primary key,
-> brtk tinytext,
-> xiry varchar(4),
-> cid int,
-> ad timestamp default now()
-> );

show create table test_in; -- show 'create table' syntax for this table
show create database dbname;

CREATE TABLE `test_in` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`invid` bigint(20) DEFAULT NULL,
`bkid` int(11) DEFAULT NULL,
`bip` varchar(60) DEFAULT NULL,
`rurl` varchar(200) DEFAULT NULL,
`brindex` bigint(20) DEFAULT NULL,
`aunr` varchar(20) DEFAULT NULL,
`branmode` varchar(100) DEFAULT NULL,
`ted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `brid` (`brid`),
CONSTRAINT `test_in_ibfk_1` FOREIGN KEY (`bkid`) REFERENCES `test_out` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=310 DEFAULT CHARSET=latin1;
CREATE TABLE `test_out` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`brtk` tinytext,
`xiry` varchar(4) DEFAULT NULL,
`cclf` varchar(4) DEFAULT NULL,
`cid` int(11) DEFAULT NULL,
`ad` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=latin1

alter table test_in modify invid bigint not null;

alter table test_in add ip varchar(60) after adid;
show table status from dbname like 'dbtable'; -- may see collation
select * from dbtable limit 13890,4;
alter table test_in drop column ifin;
alter table test_in add foreign key (bkid) references test_out (id);
alter table test_in change branmode branmode varchar(100);
delete from tbl_name; -- delete all rows in a table
insert mod_addonexample set demo='hello'; -- insert a row
grant all privileges on `brtest`.* to 'brtestuser'@'localhost' identified by 'password';
show create database dbname; -- inspect current configuration of database
show table status where name='dbtable'; -- inspect cur. conf. of table
show full columns from dbtable; -- cur. conf. columns
alter table dbtable convert to character set utf8; -- change collation
show tables like '%log%'; -- show only tables with 'log' in their name
select * from dbtable where description like '%some desc%'; -- show only those records which in 'description' field contain 'some desc' phrase
select * from dbtable where description like '%some desc%' and user='System' and date like '2013-07-16%';
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause] -- update field value

Some performance troubleshooting commands:
vmstat 5
iostat -x 5 | grep sda
mysql: show full processlist;
mysql: show global status;
mysql: show engine innodb status;
innotop

Mysql backup/restore:
http://www.thegeekstuff.com/2008/09/backup-and-restore-mysql-database-using-mysqldump/
backup: # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
restore:# mysql -u root -p[root_password] [database_name] < dumpfilename.sql

Dump specific tables:
http://dba.stackexchange.com/questions/9306/how-do-you-mysqldump-specific-tables
mysqldump -u... -p... mydb t1 t2 t3 > mydb_tables.sql

Drop database and restore it empty:
http://lowendtalk.com/discussion/8203/quick-trick-drop-all-tables-in-mysql-database-in-one-step
drop database db_name; create database db_name;

Select N last:
http://stackoverflow.com/questions/12125904/select-last-n-rows-from-mysql
SELECT * FROM (
SELECT * FROM table ORDER BY id DESC LIMIT 50
) sub
ORDER BY id ASC

Join select + subqueries
http://www.tutorialspoint.com/mysql/mysql-using-joins.htm
http://stackoverflow.com/questions/4176385/mysql-select-query-based-on-another-tables-entries
http://dev.mysql.com/tech-resources/articles/subqueries_part_1.html

select distinct t3.field1 from (select t2.field2 from table1 t1 join table2 t2 on t1.field1=t2.field2 where t1.field3 like '%Something%' and t2.field4 like '%Something%' and t1.field5 like '%Something else%') as test join table1 t3 on test.field2 != t3.field1 where t3.field3 like '%Something%' and t3.field5 like '%Something else%';

Time stamp
http://www.daniweb.com/web-development/databases/mysql/threads/53025/mysql-select-rows-in-a-date-range
http://stackoverflow.com/questions/13418827/select-date-range-on-mysql-timestamp
select field1 from table1 where field2 like '%Something%' and field3 like '%Something else%' and field4 <= '2014-01-01 00:00:00' and field4 > '2013-01-01 00:00:00';

List tables size
SELECT TABLE_NAME, table_rows, data_length, index_length,
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = "schema_name";

INNODB monitor output
show engine innodb status\g

Delete rows in one table based on checks in another table
https://dev.mysql.com/doc/refman/5.5/en/delete.html
http://www.tutorialspoint.com/mysql/mysql-in-clause.htm
delete from rooms
where room_initiating_user_id in (select user_id from users where user_connected = 0)
and room_target_user_id in (select user_id from users where user_connected = 0)

Replace string
http://stackoverflow.com/questions/10177208/update-a-column-value-replacing-part-of-a-string
http://stackoverflow.com/questions/1876762/mysql-way-to-update-portion-of-a-string
update sometable as t1 set t1.field=(select replace(t1.field, '59', '29')) where name rlike '^Some.*Pattern$';

Dump specific rows
http://stackoverflow.com/questions/3978326/get-insert-statement-for-existing-row-in-mysql
mysqldump -t -u username -pPassword database table --where="id=10" > dumpfile.sql

Mysql dump with 'like'
http://remysharp.com/2007/09/16/mysql-dump-tables-like/
mysql $db -u$user -p$pass -e 'show tables like "$like%"' | grep -v Tables_in | xargs mysqldump --add-drop-table $db -u$user -p$pass;

Reset root password
sudo service mysql stop
mysqld_safe --skip-grant-tables
mysql -u root
update user set Password=PASSWORD('new-password') where user='root';
http://www.howtoforge.com/reset-forgotten-mysql-root-password

List store procedures and functions
show procedure status;
show function status;
http://stackoverflow.com/questions/733349/list-of-stored-procedures-functions-mysql-command-line

Insert
INSERT INTO table_name
VALUES (value1, value2, value3,...)
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
http://www.w3schools.com/php/php_mysql_insert.asp

Add 1 day to the given date in select:
select * from table_name where datetime >= '2014-05-01 00:00:00' and datetime <= DATE_ADD('2014-05-31 00:00:00', INTERVAL 1 DAY);
http://www.w3schools.com/sql/func_date_add.asp
http://stackoverflow.com/questions/4098539/mysql-date-add-doesnt-work-in-where-clause

Current datetime
update tableName set dateField=now() where something=something;

Yesterday:
update tableName set dateField=subdate(now(), 1) where something=something;

ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number
http://linuxadministrator.pro/blog/?p=147
mysql> select password('12345');
+-------------------------+
| password('123456') |
+-------------------------+
| 2ff898e158cd0311 |
+-------------------------+
1 row in set (0.00 sec)

mysql> create user test identified by password ’2ff898e158cd0311′;
Query OK, 0 rows affected (0.00 sec)

Create md5 hash
select md5('password');

Get rows number for each table
http://stackoverflow.com/questions/4603272/mysql-count-number-of-rows-in-each-table
SELECT table_name, table_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '<your db>' order by table_rows desc;

More create table examples
CREATE TABLE `testtable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`yummi` varchar(19) NOT NULL,
`yummi2` blob NOT NULL,
`update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `yummi` (`yummi`)
) ENGINE=MyISAM AUTO_INCREMENT=723 DEFAULT CHARSET=latin1

CREATE TABLE `testtable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`yummi` varchar(19) NOT NULL,
`yummi2` blob NOT NULL,
`update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `yummi` (`yummi`)
) ENGINE=MyISAM AUTO_INCREMENT=723 DEFAULT CHARSET=latin1

Select intersection:
select fieldName from table1 as t where fieldID = (select fieldID from table2 where t.fieldID = fieldID);
http://stackoverflow.com/questions/2621382/alternative-to-intersect-in-mysql
SELECT value FROM table_a INNER JOIN table_b USING (value);
SELECT value FROM table_a WHERE (value) IN (SELECT value FROM table_b);
SELECT DISTINCT value FROM table_a INNER JOIN table_b USING (value);
SELECT DISTINCT value FROM table_a WHERE (value) IN (SELECT value FROM table_b);
http://www.gokhanatil.com/2010/10/minus-and-intersect-in-mysql.html
SELECT a.x, a.y FROM table_a a JOIN table_b b ON a.x = b.x AND a.y = b.y;
http://www.sitepoint.com/understanding-sql-joins-mysql-database/
select user.name, course.name from `user` inner join `course` on user.course = course.id;
http://www.bitbybit.dk/carsten/blog/?p=71
SELECT a.member_id, a.name FROM a INNER JOIN b USING (member_id, name)
http://www.google.co.il/url?sa=t&rct=j&q=&esrc=s&source=web&cd=4&cad=rja&uact=8&ved=0CDEQFjAD&url=http%3A%2F%2Ffaculty.utpa.edu%2Flianx%2Fold_courses%2FCSCI4333_2014fall%2FMySQL-set-operators.pdf&ei=MVwJVZWEC4a4OOHmgZgH&usg=AFQjCNHFzcyiW8tpc9-_kabbq2Ejc18Ycg&sig2=2PARY6pQJzU8ROtqzzpQRA&bvm=bv.88198703,d.d2s

Select except (minus):
http://www.gokhanatil.com/2010/10/minus-and-intersect-in-mysql.html
SELECT a.x, a.y FROM table_a a LEFT JOIN table_b b ON a.x = b.x AND a.y = b.y WHERE b.x IS NULL;

Configure Mysql to listen on multiple IPs:
http://www.cyberciti.biz/faq/unix-linux-mysqld-server-bind-to-more-than-one-ip-address/

Check auto increment index for continuity (find "holes")
http://stackoverflow.com/questions/10189285/how-find-holes-in-auto-increment-column
select t1.id - 1 as missing_id
from mytable t1
left join mytable t2 on t2.id = t1.id - 1
where t2.id is null

Find duplicate values
http://stackoverflow.com/questions/688549/finding-duplicate-values-in-mysql
SELECT name, COUNT(*) c FROM table GROUP BY name HAVING c > 1;

Migrate users
http://www.uptimemadeeasy.com/linux/mysql-migrate-users-server-server/
mysql -uroot -N -p -s > myfile
select Distinct CONCAT(‘show grants for `’, user, ‘`@`’, host, ‘`;’) as query from mysql.user;
quit

mysql -uroot -N -p -s -r < myfile > grantfile

cat grantfile

**scp grantfile myuser@mysql2.uptimemadeeasy.com:/home/myuser**

mysql -uroot -p < ./grantfile

Drop multiple tables with wildcard
http://yysource.com/2012/09/mysql-delete-drop-tables-with-prefix/
select concat('drop table ', group_concat(table_name) , ';') as statement from information_schema.tables where table_schema = 'lala_lab_db' and table_name like '%tadam%';
Will generate:
drop table tadam_batches,tadam_checksum,tadam_config,tadam_notifications,tadam_pusher;

Export query results to file
http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/
SELECT order_id,product_name,qty FROM orders INTO OUTFILE '/tmp/orders.txt'
OR
SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

Where by length
http://stackoverflow.com/questions/1545467/select-statement-for-the-length-of-the-field-is-greater-than-1
SELECT * FROM sometable WHERE CHAR_LENGTH(LINK) > 1

Where today
http://www.tomjepson.co.uk/mysql-select-from-table-where-date-today/
SELECT * FROM myTable WHERE DATE(myDate) = DATE(NOW())



Backlinks: