SQL

创建用户并授权

create user ‘user_name’@‘%’ identified by ‘new_pass_word’; 授权： grant all privileges on . to root@localhost with grant option; 刷新权限： flush privileges;

authentication_string

mysql 5.7开始,mysql.user中无password字段，改为authentication_string

select user,host,authentication_string from mysql.user;

CREATE TABLE

CREATE TABLE IF NOT EXISTS firstTable(job_id int(10) unsigned NOT NULL AUTO_INCREMENT, job_name varchar(80) NOT NULL, PRIMARY KEY(job_id)) DEFAULT CHARSET=utf8;

insert into firstTable (job_name)  VALUES ('a');

mysql外网访问

• 1：设置mysql的配置文件

     /etc/mysql/my.cnf
     找到 bind-address  =127.0.0.1  将其注释掉；//作用是使得不再只允许本地访问；
 
　　重启mysql：/etc/init.d/mysql restart;

• 2：登录mysql数据库：mysql -u root -p

　　mysql> use mysql;
 
　　查询host值：
    mysql> select user,host from user;
 
    如果没有"%"这个host值,就执行下面这两句:
    mysql> update user set host='%' where user='root';
    mysql> flush privileges;

Access denied

MySQL Access denied for user 'root'@'%' to database 'xxx'  
解决： grant all on *.* to 'root'@'%' identified by '123456' with grant option;

查看端口

mysql> show global variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306 |
+---------------+-------+
1 row in set (0.00 sec)

修改端口

编辑/etc/mysql/my.cnf文件