数据库基本命令及常见问题

安装的包名：mysql-community-server

• 数据库的简单使用

[root@node01 ~]# mysql -u root -p
Enter password: 

[root@node01 ~]# mysql -uroot -pWww.1.com
mysql> show databases; 查询库
mysql> drop database test;删除test这个库
mysql> create database db;  创建库
mysql> use db;      切换库
mysql> show tables ;    查询表
mysql> create table test(id int,name char(10)); 创建表
mysql> desc test;   描述表结构
mysql> insert into test values(1,'xiaochen');   插入数据
mysql> select * from test; 查看数据
mysql> delete from user;删除user表中的数据，但是不删表
mysql> drop table user1;删除user1这张表

• 获取root用户的临时密码，并修改

[root@node01 ~]# grep "password" /var/log/mysqld.log 
2017-03-09T05:48:24.711787Z 1 [Note] A temporary password is generated for root@localhost: >DK+,(%2qNl&
2017-03-09T05:51:32.429509Z 3 [Note] Access denied for user 'root'@'localhost' (using password: NO)

• 生产环境不知道root密码的操作如下:

[root@xiaochen ~]# systemctl stop mysqld
[root@xiaochen ~]# vim /etc/my.cnf
skip-grant-tables
[root@xiaochen ~]# systemctl  restart mysqld
[root@xiaochen ~]# mysql
mysql> update mysql.user set authentication_string='' where user='root' and Host='localhost';
mysql> flush privileges;

mysql> select user,authentication_string,host,password_expired from mysql.user;
[root@xiaochen 7]# vim /etc/my.cnf
#skip-grant-tables
[root@xiaochen 7]# systemctl restart mysqld

将mysql的密码重置为空之后，重新修改mysql的密码
[root@mysql ~]# mysqladmin -uroot -p password "Www1.baidu.com"