配置mysql基础设施

July 15 2016

1 mysql 安装

其实主要就是按照官方文档进行就可以的。

sudo su
dpkg -i /PATH/version-specific-package-name.deb
apt-get install mysql-server 
apt-get update
apt-get install mysql-workbench-community

2 编码格式问题

2.1 mysql server 启动时指定编码

vim ~/.my.cnf

[mysqld]
character_set_server=utf8
max_connections = 200
init_connect='SET collation_connection = utf8_general_ci' 
init_connect='SET NAMES utf8' 

2.2 mysql 连接时指定编码格式

mysql -uUSERNAME -pPASSWORD --default-character-set=utf8

2.3 编码格式查看

For Schemas:

SELECT default_character_set_name FROM information_schema.SCHEMATA 
WHERE schema_name = "schemaname";

For Tables:

SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = "schemaname"
  AND T.table_name = "tablename";

For Columns:

SELECT character_set_name FROM information_schema.`COLUMNS` 
WHERE table_schema = "schemaname"
  AND table_name = "tablename"
  AND column_name = "columnname";

2.4 修改编码格式

#直接修改整张表

ALTER TABLE ... CONVERT TO CHARACTER SET charset

3 mysql建表

a. 可以生成表结构

mysql -uroot -proot -e ' create table kaggle.app_events(event_id int,app_id varchar(200),is_installed int,is_active int)' kaggle;
mysql -uroot -proot -e ' create table kaggle.app_labels(app_id int,label_id varchar(200))' kaggle;
mysql -uroot -proot -e ' create table kaggle.events(event_id int,device_id varchar(200),timestamp varchar(200),longitude varchar(200),latitude varchar(200) )' kaggle;
mysql -uroot -proot -e ' create table kaggle.gender_age_train(device_id varchar(200),gender varchar(200),age int,use_group varchar(200))' kaggle;
mysql -uroot -proot -e ' create table kaggle.label_categories(label_id int,category varchar(200))' kaggle;
mysql -uroot -proot -e ' create table kaggle.phone_brand_device_model(device_id varchar(200),phone_brand varchar(200),device_model varchar(200))' kaggle

4 导入数据

time mysqlimport --use-threads 10  --fields-terminated-by , -uroot -proot --local kaggle events.csv

5 常用命令

官方很好的tutorial
SQL常用功能语法
索引是怎么起作用的
create-index

#添加索引
create index  app_category_info_index on app_category_info (app_id);
#删除一行

#查看是否有索引
SHOW INDEX FROM yourtable;

delete from orders 
where id_users = 1 and id_product = 2
limit 1;

#修改列类型
 ALTER TABLE app_labels MODIFY label_id int(11);

#查看数据库中表情况
SHOW TABLE status FROM [DBNAME]

6 数据库优化

优化文档

system-monitoring-tools-for-ubuntu

参考:
charset-applications
charset-server
server character set
配置文件位置
Mysql中文乱码解决
mysqlimport
charset-conversion
官方比较全的character set doc