mysql（一）
mysql基础操作
登陆mysql
mysql -u用户名 -p密码
[root@server1 ~]# mysql -uroot -pLt@18392027447
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>quit
Bye
mysql5.7的版本，初始化数据库的时候会生成一个初始密码，记录在/var/log/mysqld.log
[root@server1 software]# grep "temporary password" /var/log/mysqld.log
2017-06-13T10:13:50.746938Z 1 [Note] A temporary password is generated for root@localhost: ubP0Wisi>HJg
初次登陆建议使用mysql_secure_installation命令
运行mysql_secure_installation会执行几个设置：
a)为root用户设置密码
b)删除匿名账号
c)取消root用户远程登录
d)删除test库和对test库的访问权限
e)刷新授权表使修改生效
通过这几项的设置能够提高mysql库的安全。建议生产环境中mysql安装这完成后一定要运行一次mysql_secure_installation，详细步骤请参看下面的命令:
[root@server1 software]# mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root:
The existing password for the user account root has expired. Please set a new password.
New password:
Re-enter new password:
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.
Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y
New password:
Re-enter new password:
Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!
mysql5.7的版本把密码卡的很严格，初次登陆使用初始密码，登陆后要求必须修改密码，但是对密码的要求很高。
[root@server1 software]# /etc/init.d/mysqld start
Initializing MySQL database: [ OK ]
Installing validate password plugin: [ OK ]
Starting mysqld: [ OK ]
在启动mysql服务的时候，先初始化数据库，然后安装了一个密码插件，最后才start mysql。
New password:
Re-enter new password:
... Failed! Error: Your password does not satisfy the current policy
修改密码时总是这个提示，就是因为mysql5.7的版本默认安装了一个密码插件validate_password。那么如果说我就是测试做实验而已，就是不想使用特别复杂的密码插件怎么办呢？这个就和下面的两个参数值有关了
1.validate_password_policy:该参数值系统默认为1，即刚开始设置的密码必须符合长度，且必须含有数字，小写或大写字母，特殊字符。
| Policy | Tests Performed |
|---|---|
| 0 or LOW | Length |
| 1 or MEDIUM | Length; numeric, lowercase/uppercase, and special characters |
| 2 or STRONG | Length; numeric, lowercase/uppercase, and special characters; dictionary file |
2.validate_password_length：该参数值默认是8，最小值为4。如果指定值小于4虽然不会报错，但是系统会自动将该参数值改为4
validate_password_length = validate_password_number_count + validate_password_special_char_count +
(2 * validate_password_mixed_case_count)
validate_password_number_count指定了密码中数字的长度
validate_password_special_char_count指定了密码中特殊字符的长度
validate_password_mixed_case_count指定了密码中大小写字母的长度
mysql> set password=password('westos');
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=4;
Query OK, 0 rows affected (0.00 sec)
mysql> set password=password('westos');
Query OK, 0 rows affected, 1 warning (0.00 sec)
以上操作的前提是validate_password插件已经安装，那么如何知道validate_password是否安装呢？
mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+--------------------------------------+--------+
7 rows in set (0.01 sec)
不过个人建议还是不要改的好，mysql5.7的版之所以会把密码的安全强度提高，就是因为程序远将mysql密码设置太简单，容易被破解，所以还是把提高密码安全性养成习惯吧。
mysql命令初步
查看和创建数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
information_schema,mysql和performance_schema是mysql系统使用的数据库，有关DBMS自身的管理信息都保存在这几个数据库中，如果删除则数据库系统无法正常工作。另外mysql5.7增加了sys系统数据库，通过这个库可以快速的了解系统的元数据信息
创建用户数据库
mysql> create database if not exists westos;##避免出现数据库已经存在而再新建的错误
mysql> create database westos;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| westos |
+--------------------+
5 rows in set (0.00 sec)
在数据库中创建表
mysql> create table kiosk(id int auto_increment not null primary key,username varchar(6) not null,password varchar(10) not null);
Query OK, 0 rows affected (1.09 sec)
mysql> show tables;
+------------------+
| Tables_in_westos |
+------------------+
| kiosk |
+------------------+
1 row in set (0.00 sec)
mysql> describe kiosk;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(6) | NO | | NULL | |
| password | varchar(10) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
id列标志字段，整形（int），字段数据有系统自动增一（auto_increment），并将其设置为主键（primary key）；username和password列分别存放不超过6个（varchar(6)）和10个（varchar(10)）字符；这三个字段不能为空（not null）
向表中添加记录
mysql> insert into kiosk values(1,'tom','redhat');
Query OK, 1 row affected (0.17 sec)
mysql> insert into kiosk values(2,'jam','student');
Query OK, 1 row affected (0.13 sec)
mysql> select * from kiosk;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | tom | redhat |
| 2 | jam | student |
+----+----------+----------+
2 rows in set (0.00 sec)
