MySQL
{% include JB/setup %}
macOS
Install
brew update
brew remove mysql
brew install mysql --with-debug
Service
mysql.server restart
```
## Ubuntu
### Install
```sh
sudo apt-get install mysql-server
```
### Service
````sh
sudo service mysql restart
service mysql status
sudo sysv-rc-conf mysql on
sudo sysv-rc-conf
```
## CentOS
### Install Lastest MySQL from Official Yum Repositories
Find [mysql-community-release-el6-5.noarch.rpm](http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm) from [MySQL Yum repository](http://dev.mysql.com/downloads/repo/yum/)
```sh
mkdir ~/bin/
cd ~/bin/
wget http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
sudo yum localinstall mysql-community-release-el6-5.noarch.rpm
yum install mysql-community-server
```
### Install Older MySQL from CentOS Yum Repositories
```sh
yum install mysql-server
```
### Service
```sh
su root
service mysqld restart
service mysqld status
chkconfig mysqld on
su taohe
```
## Setup
### Configure File
Check for configure file loading order
```sh
mysql --help | grep -A 1 "Default options are read from the following files"
```
Check for encoding
```sql
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
```
Example result
```
+--------------------------+--------------------+
| Variable_name | Value |
+--------------------------+--------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| collation_connection | utf8mb4_unicode_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.00 sec)
```
### Reset All Data
All old innodb data is located in `/var/lib/mysql`. We will **DELETE** all old data.
```sh
sudo rm -rf /var/lib/mysql
sudo mysql_install_db
sudo service mysql restart
sudo service mysql status
```
### Setup (Optional)
```sh
sudo mysql_secure_installation
```
### Set root Password
Init password
```sh
mysqladmin -u root password '123456'
```
Change password
```sh
mysql -u root -p
```
```sql
SET PASSWORD FOR 'root' = PASSWORD('123456');
```
## Create user
```sh
CREATE USER 'taohe'@'localhost' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'taohe'@'localhost' WITH GRANT OPTION;
```
## Export CSV
```sql
SELECT uid, user_uid, IFNULL(name, ''), IFNULL(province, ''), IFNULL(city, ''), IFNULL(followers_count, ''), IFNULL(friends_count, ''), IFNULL(statuses_count, '') FROM sina_user INTO OUTFILE '/home/taohe/users.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
SELECT fid, IFNULL(friend_uid, ''), IFNULL(follower_uid, '') FROM sina_friendship INTO OUTFILE '/home/taohe/friendships.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
SELECT sid, IFNULL(status_id, ''), IFNULL(text, ''), IFNULL(user_uid, ''), IFNULL(created_at, '') FROM sina_status_info_10 INTO OUTFILE '/home/taohe/status_10.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
```
## Usage
```sql
SET time_zone = "+08:00";
DROP DATABASE IF EXISTS weibominer;
CREATE DATABASE weibominer DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
USE weibominer;
DROP TABLE IF EXISTS sina_friendship;
CREATE TABLE IF NOT EXISTS sina_friendship
(fid INT NOT NULL AUTO_INCREMENT,
friend_uid BIGINT NOT NULL, follower_uid BIGINT NOT NULL,
hit INT NOT NULL DEFAULT 0, crawl_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (fid), UNIQUE (friend_uid, follower_uid));
DROP TABLE IF EXISTS sina_user;
CREATE TABLE IF NOT EXISTS sina_user
(uid INT NOT NULL AUTO_INCREMENT,
user_uid BIGINT NOT NULL UNIQUE,
screen_name VARCHAR(40), name VARCHAR(40), province SMALLINT,
city SMALLINT, location VARCHAR(40), description TEXT,
url TEXT, profile_image_url TEXT,
domain VARCHAR(40), gender BOOLEAN,
followers_count BIGINT, friends_count BIGINT, bi_followers_count BIGINT,
statuses_count BIGINT, favourites_count BIGINT,
group_count BIGINT,
created_at TIMESTAMP NOT NULL DEFAULT 0, allow_all_act_msg BOOLEAN,
remark VARCHAR(40), geo_enabled BOOLEAN, verified SMALLINT,
allow_all_comment BOOLEAN, verified_reason TEXT,
online_status SMALLINT,
status_id BIGINT,
friend_uid BIGINT, follower_uid BIGINT, rank SMALLINT,
visited_user INT NOT NULL DEFAULT 0,
visited_friend BIGINT NOT NULL DEFAULT 0,
visited_follower BIGINT NOT NULL DEFAULT 0,
visited_follower_active BIGINT NOT NULL DEFAULT 0,
visited_bilateral BIGINT NOT NULL DEFAULT 0,
visited_timeline BIGINT NOT NULL DEFAULT 0,
visited_user_info BIGINT NOT NULL DEFAULT 0,
visited_microblog BIGINT NOT NULL DEFAULT 0,
visited_repost BIGINT NOT NULL DEFAULT 0,
hit BIGINT NOT NULL DEFAULT 0, crawl_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (uid));
DROP TABLE IF EXISTS sina_status_info;
CREATE TABLE IF NOT EXISTS sina_status_info
(sid INT NOT NULL AUTO_INCREMENT,
status_id BIGINT UNIQUE,
status_mid VARCHAR(20) UNIQUE,
source_url TEXT, source_rel VARCHAR(10), source_name VARCHAR(40), url TEXT, created_at TIMESTAMP NOT NULL DEFAULT 0,
previous_id BIGINT, origin_id BIGINT,
previous_mid VARCHAR(20), origin_mid VARCHAR(20),
previous_idstr VARCHAR(20), origin_idstr VARCHAR(20),
repost_count INT NOT NULL DEFAULT 0,
comment_count INT NOT NULL DEFAULT 0,
attitude_count INT NOT NULL DEFAULT 0,
user_uid BIGINT NOT NULL,
retweeted_status_id BIGINT,
visited_query_mid INT NOT NULL DEFAULT 0,
visited_query_id INT NOT NULL DEFAULT 0,
text TEXT,
hit INT NOT NULL DEFAULT 0, crawl_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (sid));
DROP TABLE IF EXISTS sina_status_detail;
CREATE TABLE IF NOT EXISTS sina_status_detail
(sid INT NOT NULL AUTO_INCREMENT,
status_id BIGINT UNIQUE,
text TEXT,
hit INT NOT NULL DEFAULT 0, crawl_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (sid));
```