安装mysql

mysql

• Home: https://www.mysql.com
• Download: https://dev.mysql.com/downloads
• Github: https://github.com/mysql/mysql-server

安装mysql

sudo apt install mysql-server
sudo apt install mysql-client

安装mysql服务器时会提示输入root密码
安装完成之后可以使用如下命令来检查是否安装成功：

ps -ef | grep mysql
sudo netstat -tanp | grep mysql

如果看到有mysql 的socket处于 listen 状态则表示安装成功。

设置mysql

为了正确的处理中文，需要设置下mysql的编码格式为utf8

MySQL的配置文件默认存放在/etc/mysql/my.cnf：

[client]
default-character-set = utf8
 
[mysqld]
default-character-set = utf8
character-set-server = utf8
collation-server = utf8_general_ci

sudo service mysql restart

重启MySQL后，可以通过MySQL的客户端命令行检查编码：

$ mysql -u root -p
Enter password: 

show variables like '%character%';
show variables like '%collation%';

打印如下，看到utf8字样就表示编码设置正确。

+--------------------------+--------------------------------------------------------+
| Variable_name            | Value                                                  |
+--------------------------+--------------------------------------------------------+
| character_set_client     | utf8                                                   |
| character_set_connection | utf8                                                   |
| character_set_database   | utf8                                                   |
| character_set_filesystem | binary                                                 |
| character_set_results    | utf8                                                   |
| character_set_server     | utf8                                                   |
| character_set_system     | utf8                                                   |
| character_sets_dir       | /usr/local/mysql-5.1.65-osx10.6-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------+
8 rows in set (0.00 sec)

mysql用户管理

登录

mysql -u username -p

新安装的mysql根用户名为root，密码即安装时你输入的，登录成功会进入mysql>提示符

创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

创建完成后你就可以使用mysql -u username -p来登录了

删除用户

DROP USER 'username'@'host' ;

修改用户密码

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

授权用户

GRANT privileges ON databasename.tablename TO 'username'@'host';

privaileges取值ALL、SELECT、UPDATE、INSERT、DELETE等

撤销权限

REVOKE privilege ON databasename.tablename FROM 'username'@'host';

mysql常用命令

显示数据库

show databases;

显示表

show tables;

使用表

 use tablename；

创建表

create table users (
    `id` varchar(50) not null,
    `email` varchar(50) not null,
    `passwd` varchar(50) not null,
    `admin` bool not null,
    `name` varchar(50) not null,
    `image` varchar(500) not null,
    `created_at` real not null,
    unique key `idx_email` (`email`),
    key `idx_created_at` (`created_at`),
    primary key (`id`)
) engine=innodb default charset=utf8;

查看表描述

mysql> desc users;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | varchar(50)  | NO   | PRI | NULL    |       |
| email      | varchar(50)  | NO   | UNI | NULL    |       |
| passwd     | varchar(50)  | NO   |     | NULL    |       |
| admin      | tinyint(1)   | NO   |     | NULL    |       |
| name       | varchar(50)  | NO   |     | NULL    |       |
| image      | varchar(500) | NO   |     | NULL    |       |
| created_at | double       | NO   | MUL | NULL    |       |
+------------+--------------+------+-----+---------+-------+
7 rows in set (0.13 sec)