MySQL常用命令

mysql常用sql命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

mysql -u root -p

use user;

drop database user;

use mysql

select Host,User,authentication_string from mysql.user;

grant all privileges on *.* to 'dbuser'@'115.171.60.75'identified by 'root' with grant option;

all insert update ...
*.* : 数据库.表名
root : 用户名
115.170.*.* : 远程访问ip地址

刷新配置
flush privileges;

UPDATE user SET password=PASSWORD("新密码") WHERE user='你的用户名';

FLUSH PRIVILEGES;


delete from mysql.user where user=root and host = 115.171.60.75;

{———-}

安装

原装地址:http://www.cnblogs.com/5201351/p/4912614.html

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
卸载mriadb包
[root@5201351 ~]# rpm -qa|grep mariadb
mariadb-libs-5.5.41-2.el7_0.x86_64
[root@5201351 ~]# rpm -e mariadb-libs-5.5.41-2.el7_0.x86_64 --nodeps

安装
mysql 安装包 https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.20-1.el7.x86_64.rpm-bundle.tar
rpm -ivh *.rpm

安装顺序 common libs client server

两种初始化启动方式
[root@5201351 ~]# mysql_install_db --datadir=/var/lib/mysql //必须指定datadir,执行后会生成~/.mysql_secret密码文件
[root@5201351 ~]# mysqld --initialize //新版的推荐此方法,执行生会在/var/log/mysqld.log生成随机密码

启动mysql
[root@5201351 ~]# chown mysql:mysql /var/lib/mysql -R
[root@5201351 ~]# systemctl start mysqld.service

查看密码
cat ~/.mysql_secret
cat /var/log/mysqld.log

登录
[root@5201351 ~]# mysql -uroot -p')j#)=uRig4yJ'
mysql> set password=password('www.cnblogs.com/5201351');

创建用户以及分配权限
mysql> create user 'root'@'192.168.100.2' identified by 'QQ5201351';
mysql> GRANT ALL PRIVILEGES ON dbname.* to 'root'@'192.168.100.2';
mysql> flush privileges

常用sql

1
2

ALTER TABLE table_name RENAME TO new_table_name