Lokvin Wiki
Don't have an account?
Register
Advertisement
Register

Mysql

Edit

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
  • start up from terminal
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
  • install on
/usr/local/mysql
  • set root passwd

root/ r00t

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

show procedure[]

mysql>show procedure status;

show databases , tables[]

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

show create table statement[]

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

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)
  • connect mysql with utf8 encode
mysql -h 10.90.108.154 -u reuters -preuters -P23306 rcommerce --default-character-set=utf8
  • mysql connect with socket
mysql --socket=/rt/db/tmp/mysqld.sock1 -uroot -p

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
    • dump db by --socket
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 entire db 2
mysqldump --opt --lock-tables=false -h $host -P $port 
-u $user -p$password -R $db > $dumpfile
  • dump need tables
 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 2
mysqldump --opt --lock-tables=false -h $host -P $port
-u $user -p$password -R $db --tables $table > $dumpfile

import to mysql[]

mysql -u root -pr00t -h localhost -P 23306 --default-character-set=utf8 apikeys < ./apikeys.sql
  • import by socket
mysql -u root -p --socket=/rt/db/tmp/mysqld.sock1 --default-character-set=utf8 cmsstaging_mobile < ./cmsstaging_mobile_qa5_20140305.sql

MySQL confgurations[]

  • for Development
 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
  • for SIT (Testing)
 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
  • for Production
 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
  • for Replication
 [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


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() & sysdate()
 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.
  • date calculation
 1. do not use + - operator

 2. use date functions such as date_diff()

 3. convert to unix timestamp and then use + - opertator

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[]

  • Using Floating Points comparisons in INSERT/CHANGE statements may result in different results if running different

CPU or hardware (due to Floating Points compares are always fuzzy & system dependent).


replication References[]

Performance tuning[]

  • sync_binlog
 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.
  • monitor slow query

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
  • monitor query not using indexes

enable log_queries_not_using_indexes option 

 log_queries_not_using_indexes

mysql tree data model[]

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[]

  • mysql 的root 密码忘了，又不能重装一下，还好google 到一个修改root 密码的方法，在

mysql5.0 下测试好用。 如下：

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

slow query log[]

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

mysqldumpslow /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 r -t 5 /var/log/mysql/mysql-slow.log
  • Following will sort output by count i.e. number of times query found in slow-log.
mysqldumpslow -a -s c -t 5 /var/log/mysql/mysql-slow.log
  • just use less check slow log
less mysqld-slow.log
//query time 2013-12-16 10:26:42
# Time: 131216 10:26:42
# User@Host: news[news] @  [10.90.108.109]
# Query_time: 85.056482  Lock_time: 0.000693 Rows_sent: 1538  Rows_examined: 36204
SET timestamp=1387189602;
select distinct UID, NAME, REVISION_DATE, HEADLINE, EDITION, SLUG_LINE from SEARCHVIEW where EDITION = 'UK'...

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[]

mysql 36[]

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 read only runtime
SET GLOBAL read_only=1;

generate mysql ER diagram[]

  • use mysqlworkbench reverse enginering

https://www.youtube.com/watch?v=v5Q0lz5FkW0

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[]

mysql load test query no cache[]

SELECT SQL_NO_CACHE id, name FROM customer;

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

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*
Community content is available under CC-BY-SA unless otherwise noted.
Advertisement

Fan Feed