Mysql

mysql check version
mysql> select version;

mysql change root passed
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

mysql setup on my macbook pro

 * install use mysql dmg file


 * startup from System Preference -> Mysql

sudo /usr/local/mysql/support-files/mysql.server start sudo /usr/local/mysql/support-files/mysql.server restart sudo /usr/local/mysql/support-files/mysql.server stop /usr/local/mysql
 * start up from terminal
 * install on

root/ r00t
 * set root passwd

cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
 * mysql's my.cnf file

show procedure
mysql>show procedure status;

show databases, tables
mysql>show databases; mysql>use somedb; mysql>show tables;

show create table statement

 * https://dev.mysql.com/doc/refman/5.0/en/show-create-table.html

SHOW CREATE TABLE tbl_name

mysql> show create table VIDEOCHANNEL \G; *************************** 1. row *************************** Table: VIDEOCHANNEL Create Table: CREATE TABLE `VIDEOCHANNEL` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT,  `CHANNEL_CODE` varchar(50) NOT NULL,  `NAME` varchar(100) NOT NULL,  `EDITION` char(2) NOT NULL DEFAULT 'US',  `TS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  `DESCRIPTION` varchar(256) DEFAULT NULL,

show index
show index from table_name
 * https://dev.mysql.com/doc/refman/5.0/en/show-index.html

mysql> show index from CATEGORY; +--++--+--+-+---+-+--++--++-- ---+ | Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |  Comment | +--++--+--+-+---+-+--++--++-+ | CATEGORY |         0 | PRIMARY  |            1 | ID          | A         |        4303 |     NULL | NULL   |      | BTREE      |

DESC & show columns

 * desc table_name = show columns from table_name

mysql> desc VIDEOCHANNEL; +--+-+--+-+---+-+ | Field               | Type            | Null | Key | Default           | Extra                       | +--+-+--+-+---+-+ | ID                  | bigint(20)      | NO   | PRI | NULL              | auto_increment              | | CHANNEL_CODE        | varchar(50)     | NO   | MUL | NULL              |                             | | NAME                | varchar(100)    | NO   |     | NULL              |                             |

mysql> SHOW COLUMNS FROM VIDEOCHANNEL; +--+-+--+-+---+-+ | Field               | Type            | Null | Key | Default           | Extra                       | +--+-+--+-+---+-+ | ID                  | bigint(20)      | NO   | PRI | NULL              | auto_increment              |

mysql connect
mysql -uroot -p(passowrd) -hlocalhost -P(port)

mysql -h 10.90.108.154 -u reuters -preuters -P23306 rcommerce --default-character-set=utf8
 * connect mysql with utf8 encode

mysql --socket=/rt/db/tmp/mysqld.sock1 -uroot -p
 * mysql connect with socket

msyqldump

 * dump entire db

mysqldump -u iphone -piphone -h mprodusx-iphoneapp-01 -P 23306 --default-character-set=utf8 --single-transaction --routines --triggers --databases iphone > iphone.sql

mysqldump -u root -p --socket=/rt/db/tmp/mysqld.sock1 --default-character-set=utf8 --single-transaction --routines --triggers --databases cmsstaging_mobile > cmsstaging_mobile.sql
 * dump db by --socket

mysqldump --opt --lock-tables=false -h $host -P $port -u $user -p$password -R $db > $dumpfile
 * dump entire db 2

mysqldump -u iphone -piphone -h mprodusx-iphoneapp-01 -P 23306 --default-character-set=utf8 --single-transaction --routines --triggers iphone db_changes localized_feeds slideshow_categories spotlight_feeds video_feeds > iphone.sql
 * dump need tables

mysqldump --opt --lock-tables=false -h $host -P $port -u $user -p$password -R $db --tables $table > $dumpfile
 * dump need tables 2

import to mysql
mysql -u root -pr00t -h localhost -P 23306 --default-character-set=utf8 apikeys < ./apikeys.sql

mysql -u root -p --socket=/rt/db/tmp/mysqld.sock1 --default-character-set=utf8 cmsstaging_mobile < ./cmsstaging_mobile_qa5_20140305.sql
 * import by socket

MySQL confgurations
log-bin = /usr/local/mysql/data/mysqld/mysqld-bin.log log-bin-index = /usr/local/mysql/data/mysqld/mysqld-bin.index log=/usr/local/mysql/data/mysqld/query.log default_table_type = InnoDB log_slow_queries = /usr/local/mysql/data/mysqld/mysqld-slow.log long_query_time = 1 log_error = /usr/local/mysql/data/mysqld/mysqld.err log_queries_not_using_indexes # *** INNODB Specific options *** innodb_data_file_path = ibdata1:10M:autoextend innodb_file_per_table log-bin = /usr/local/mysql/data/mysqld/mysqld-bin.log log-bin-index = /usr/local/mysql/data/mysqld/mysqld-bin.index log=/usr/local/mysql/data/mysqld/query.log default_table_type = InnoDB log_slow_queries = /usr/local/mysql/data/mysqld/mysqld-slow.log long_query_time = 1 log_error = /usr/local/mysql/data/mysqld/mysqld.err log_queries_not_using_indexes # *** INNODB Specific options *** innodb_data_file_path = ibdata1:10M:autoextend innodb_file_per_table default_table_type = InnoDB log_error = /usr/local/mysql/data/mysqld/mysqld.err # *** INNODB Specific options *** innodb_data_file_path = ibdata1:10M:autoextend innodb_file_per_table [mysqld_multi] mysqld=/usr/bin/mysqld_safe mysqladmin=/usr/bin/mysqladmin user=*** password=*** [mysqld1] server-id=127007 port       = 23307 user       = *** sync-binlog = 10 log-bin = /disk2/mysql/data/mysqld_rep1/mysqld-bin.log log-bin-index = /disk2/mysql/data/mysqld_rep1/mysqld-bin.index binlog-do-db = db1 [mysqld2] server-id=127008 port=23308 user=*** master-host=*** master-user=*** master-password=** master-port=** replicate-do-db=db1 replicate-ignore-table=db1.table1 log_error=/disk2/mysql/data/mysqld_rep2/mysqld.err
 * for Development
 * for SIT (Testing)
 * for Production
 * for Replication

MySQL indexes
2 types of indexes: spatial indexes (created using SPATIAL INDEX) and non-spatial indexes (created with INDEX, UNIQUE, or PRIMARY KEY)
 * spatial indexes (created using SPATIAL INDEX)
 * Available only for MyISAM tables. Specifying a SPATIAL INDEX for other storage engines results in an error.
 * Indexed columns must be NOT NULL.
 * Non-spatial indexes (created with INDEX, UNIQUE, or PRIMARY KEY):
 * Allowed for any storage engine that supports spatial columns except ARCHIVE.
 * Columns can be NULL unless the index is a primary key.
 * The index type for a non-SPATIAL index depends on the storage engine. Currently, B-tree is used.

Common usage mistakes
NOW returns a constant time that indicates the time at which the statement began to execute. (Within a stored routine or trigger, NOW returns the time at which the routine or triggering statement began to execute.) This differs from the behavior for SYSDATE, which returns the exact time at which it executes as of MySQL 5.0.13. SET TIMESTAMP affects the value returned by NOW but not by SYSDATE. With the --sysdate-is-now option to cause SYSDATE to be an alias for NOW, in which case SET TIMESTAMP affects both functions. 1. do not use + - operator 2. use date functions such as date_diff 3. convert to unix timestamp and then use + - opertator
 * now & sysdate
 * date calculation

Common Replication Pitfalls
MySQL performs statement based replication, and certain SQL statements would cause errors in replication.

System Functions
The following system functions should NEVER be used in statements that INSERT/CHANGE data if MySQL replication is (tobe) used, as they would generate different results between master and slave.


 * USER
 * CURRENT_USER
 * UUID
 * VERSION
 * LOAD_FILE
 * SYSDATE
 * FOUND_ROWS
 * ROW_COUNT

Also usage of the following must be very cautious.


 * RAND

Floating Point Comparisons
CPU or hardware (due to Floating Points compares are always fuzzy & system dependent).
 * Using Floating Points comparisons in INSERT/CHANGE statements may result in different results if running different

replication References

 * http://dev.mysql.com/doc/refman/5.0/en/replication-features.html
 * http://dev.mysql.com/doc/refman/5.0/en/replication-features-functions.html
 * http://dev.mysql.com/doc/refman/5.0/en/replication-features-floatvalues.html

Performance tuning
This is only for datagbase replication scenario By default, the binary log is not synchronized to disk at each write. So if the operating system or machine (not only the MySQL server) crashes, there is a chance that the last statements of the binary log are lost. To prevent this, you can make the binary log be synchronized to disk after every N writes to the binary log, with the sync_binlog system variable. 1 is the safest value for sync_binlog, but also the slowest. Even with sync_binlog set to 1, there is still the chance of an inconsistency between the table content and binary log content in case of a crash. 10 is a prefer value to improve the database performance, but there is something you need to pay attention: 1. seperate insert/update sql in a transaction, and place fetch data in another transaction. 2. the data maybe delay in a interval time. enable log_slow_queries option, and set long_query_time (s) log_slow_queries = /usr/local/mysql/data/mysqld/mysqld-slow.log long_query_time = 1 enable log_queries_not_using_indexes option log_queries_not_using_indexes
 * sync_binlog
 * monitor slow query
 * monitor query not using indexes

mysql tree data model

 * An article - http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

mysql common use
mysql>show databases; // 显示数据库 mysql>use test; // 使用 test 数据库 mysql>show tables; // 显示表信息 mysql>describe db; // 显示 db 表信息 mysql>create database myinfo; // 建立名为 myinfo 的数据库 mysql>create table friends(     id int not null auto_increment primary key,      name varchar(20),      age int(3),      addr varchar(50));

mysql>drop table 表名; // 删除一个表

mysql>drop databases 数据库名; //删除一个数据库

mysql>insert into friends values('','埃菲',22,'moto'); //自增字段可以使用空字符 mysql>insert into friends(name, age) values('jack', 22); mysql>update friends set addr='IBM' where name = 'tom'; mysql>LOAD DATA local infile "C:/myfirends.txt" INTO table friends; //将文件中的数据导入 friends 表 mysql>DELETE FROM friends WHERE name='埃菲'; mysql>SELECT * FROM friends ORDER BY id ASC(DESC) //                                     升序(降序)

mysql>SELECT * FROM friends LIMIT 5, 10; //返回从第 6 条开始的 10 条记录，即 6 到 15 行 mysql>SELECT * FROM friends LIMIT 5; //返回 friends 表前 5 条记录 //LIMIT 给定一个或两个参数，如果是两个参数，第一个指定返回第一行的偏移量(从0开始) //第二个参数指定返回行数 //只有一个数字的偏移量为 0 mysql>SELECT * FROM friends INTO OUTFILE 'c:/filename'; //将选择数据保存到文件

mysql>ALTER TABLE friends rename myfriends; //更改表明

mysql>ALTER TABLE friends CHANGE name myname varchar(30); //将列 name 改为 myname varchar(30) 类型

mysql>ALTER TABLE friends add time date default '0000-00-00'; //增加一列 time 类型为 date, 默认值为 '0000-00-00'

mysql>ALTER TABLE friends drop COLUMN age; // 删除表friends 中的 age 列

>mysqldump -uroot -p -t myinfo > myinfo.txt // '-t' 表示不导出表结构信息

>mysqldump -uroot -p -d myinfo > myinfo.sql // '-d' 表示不导出表内容信息

>mysqldump -uroot -p myinfo > myinfo.dat // 导出数据库 myinfo 的所有内容包括，表结构，表内容

mysqldump 常用选项 --add-drop-table 在 create 之间增加一个 drop table

mysql add user
mysql>GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFY BY     'something' WITH GRANT OPTION; mysql>GRANT ALL PRIVILEGES ON *.* TO monty@"%" IDENTIFIED BY      'something' WITH GRANT OPTION;

或者 mysql>CREATE DATABASE `roller` CHARACTER SET utf8 COLLATE utf8_general_ci; mysql>grant all on roller.* to scott@'%' identified by 'tiger'; mysql>grant all on roller.* to scott@'localhost' identified by 'tiger'; mysql>use roller; mysql>source roller.sql; mysql>exit;

mysql change pwd
修改用户密码 set password for root@'localhost' = password('mysql');

mysql5.0 forget root pwd
mysql5.0 下测试好用. 如下：
 * mysql 的root 密码忘了，又不能重装一下，还好google 到一个修改root 密码的方法，在

>use mysql >update user set password=password("new_pass") where user="root"; >flush privileges; >exit
 * 1) 在service 中停掉 mysql 的 service
 * 2) 进入 %mysql_home%\bin 目录，执行 mysqld-nt --skip-grant-tables
 * 3) 开一个新的命令窗口执行  mysql
 * 4) 直接就可以登录了，然后执行以下
 * 1) 在Task Manager 中关掉 my sql 进程.
 * 2) 启动 mysql service ，就可以用新的 root 密码访问了.

slow query log
datadir=/usr/local/mysql/data/mysqld1 log_slow_queries   = mysqld-slow.log long_query_time = 1
 * 1) the relative path to mysql data folder, /usr/local/mysql/data/mysqd1/mysqld-slow.log
 * 1) query time > 1 seconds will be record slow query log

mysqldumpslow /var/log/mysql/mysql-slow.log

mysqldumpslow -a -s r -t 5 /var/log/mysql/mysql-slow.log
 * Following will show top 5 query which returned maximum rows. It can find queries where you missed LIMIT clause.

mysqldumpslow -a -s c -t 5 /var/log/mysql/mysql-slow.log
 * Following will sort output by count i.e. number of times query found in slow-log.


 * https://rtcamp.com/tutorials/mysql/slow-query-log/ -- a doc about slow query log

less mysqld-slow.log //query time 2013-12-16 10:26:42 SET timestamp=1387189602; select distinct UID, NAME, REVISION_DATE, HEADLINE, EDITION, SLUG_LINE from SEARCHVIEW where EDITION = 'UK'...
 * just use less check slow log
 * 1) Time: 131216 10:26:42
 * 2) User@Host: news[news] @  [10.90.108.109]
 * 3) Query_time: 85.056482  Lock_time: 0.000693 Rows_sent: 1538  Rows_examined: 36204

check master/slave db
mysql> show master status \G; mysql> show slave status \G;

mysql show pages
mysql>pager more; mysql>show tables;

or mysql>pager less; mysql>show tables;

exit show pages mysql>nopager;

mysql data time function

 * http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timestamp

mysql 36

 * http://liuxin1982.blog.chinaunix.net/uid-22163222-id-3478568.html

mysql read only

 * check mysql read only status

mysql> select @@global.read_only; ++ | @@global.read_only | ++ |                 0 | ++ 1 row in set (0.00 sec)

SET GLOBAL read_only=1;
 * set read only runtime

generate mysql ER diagram
https://www.youtube.com/watch?v=v5Q0lz5FkW0
 * use mysqlworkbench reverse enginering

copy existing mysql table to new table
CREATE TABLE recipes_new LIKE production.recipes; INSERT INTO recipes_new SELECT * FROM production.recipes;

mysql optimize select

 * http://dev.mysql.com/doc/refman/5.5/en/select-optimization.html
 * http://20bits.com/article/10-tips-for-optimizing-mysql-queries-that-dont-suck

mysql load test query no cache
SELECT SQL_NO_CACHE id, name FROM customer;


 * http://dev.mysql.com/doc/refman/5.0/en/query-cache-in-select.html

difference for inner jion, left join, right join, full jion

 * http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
 * http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join

uninstall mysql from mac
Open a terminal window Use mysqldump to backup your databases to text files! Stop the database server sudo rm /usr/local/mysql sudo rm -rf /usr/local/mysql* sudo rm -rf /Library/StartupItems/MySQLCOM sudo rm -rf /Library/PreferencePanes/My* edit /etc/hostconfig and remove the line MYSQLCOM=-YES- rm -rf ~/Library/PreferencePanes/My* sudo rm -rf /Library/Receipts/mysql* sudo rm -rf /Library/Receipts/MySQL* sudo rm -rf /private/var/db/receipts/*mysql*