部署mysql数据库及增删改查授权

1.安装mysql

yum -y install \
ncurses \
ncurses-devel \
bison \
cmake

useradd -s /sbin/nologin  mysql

###上传mysql-boost-5.7.20.tar.gz到opt目录下###
cd /opt
tar xf mysql-boost-5.7.20.tar.gz
cd /opt/mysql-5.7.20/

cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DSYSTEMD_PID_DIR=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8  \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=boost \
-DWITH_SYSTEMD=1

####配置选项含义###
1、DCMAKE_INSTALL_PREFIX：指定将 mysql 数据库程序安装到某目录下，
如目录/usr/local/ mysql。
2、DMYSQL_UNIX_ADDR：指定套接字文件的存储路径，数据库连接的文件
3、DSYSCONFDIR：指定初始化参数文件目录
3、DDEFAULT_CHARSET：指定默认使用的字符集编码，如 utf8。
5、DDEFAULT_COLLATION：指定默认使用的字符集校对规则，utf8_general_ci 是适用于 UTF-8 字符集的通用规则。
6、DWITH_INNOBASE_STORAGE_ENGINE=1 ：安装INNOBASE存储引擎
7、DWITH_ARCHIVE_STORAGE_ENGINE=1 ：安装ARCHIVE存储引擎 
8、DWITH_BLACKHOLE_STORAGE_ENGINE=1 ：安装ARCHIVE存储引擎 
9、DWITH_PERFSCHEMA_STORAGE_ENGINE ：安装FEDERATED存储引擎 

指定安装文件的安装路径时常用的选项：

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql         指定安装路径 
-DMYSQL_DATADIR=/data/mysql                     数据安装路径 
-DSYSCONFDIR=/etc                               配置文件的安装路径

由于MySQL支持很多的存储引擎而默认编译的存储引擎包括：csv、myisam、myisammrg和heap。若要安装其它存储引擎，可以使用类似如下编译选项：

-DWITH_INNOBASE_STORAGE_ENGINE=1          安装INNOBASE存储引擎 
-DWITH_ARCHIVE_STORAGE_ENGINE=1           安装ARCHIVE存储引擎 
-DWITH_BLACKHOLE_STORAGE_ENGINE=1         安装BLACKHOLE存储引擎 
-DWITH_FEDERATED_STORAGE_ENGINE=1         安装FEDERATED存储引擎 


若要明确指定不编译某存储引擎，可以使用类似如下的选项：

-DWITHOUT_<ENGINE>_STORAGE_ENGINE=1 

比如：

-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1        不启用或不编译EXAMPLE存储引擎 
-DWITHOUT_FEDERATED_STORAGE_ENGINE=1 
 -DWITHOUT_PARTITION_STORAGE_ENGINE=1

如若要编译进其它功能，如SSL等，则可使用类似如下选项来实现编译时使用某库或不使用某库：

-DWITH_READLINE=1 
 -DWITH_SSL=system           表示使用系统上的自带的SSL库 
-DWITH_ZLIB=system 
 -DWITH_LIBWRAP=0

其它常用的选项：

-DMYSQL_TCP_PORT=3306                       设置默认端口的 
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock           MySQL进程间通信的套接字的位置 
-DENABLED_LOCAL_INFILE=1                    是否启动本地的LOCAL_INFILE 
 -DEXTRA_CHARSETS=all                        支持哪些额外的字符集 
-DDEFAULT_CHARSET=utf8                      默认字符集 
-DDEFAULT_COLLATION=utf8_general_ci         默认的字符集排序规则 
-DWITH_DEBUG=0                              是否启动DEBUG功能 
-DENABLE_PROFILING=1                        是否启用性能分析功能

##################################################################################################

------注意：如果在CMAKE的过程中有报错---
        当报错解决后，需要把源码目录中的CMakeCache.txt文件删除，然后再重新CMAKE，否则错误依旧
------注意：make: *** No targets specified and no makefile found. Stop.解决方法
        1、wget http://ftp.gnu.org/pub/gnu/ncurses/ncurses-5.6.tar.gz
        2.、tar zxvf ncurses-5.6.tar.gz
        3、 ./configure -prefix=/usr/local -with-shared-without-debug
        4、make
        5、make install

###############################################################################################

######编译安装####

make && make install


#####数据库目录进行权限调整###

chown -R mysql:mysql /usr/local/mysql/


#####建立调整配置文件########

vi /etc/my.cnf

[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES


chown mysql:mysql /etc/my.cnf

#######设置环境变量####

echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
echo 'export PATH' >> /etc/profile
source /etc/profile

cd /usr/local/mysql/

bin/mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data

cp usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
systemctl enable mysqld
systemctl start mysqld
systemctl status mysqld
netstat -anpt | grep 3306

mysqladmin -u root -p password "abc123" //刚开始没密码是空的直接回车，然后输入密码abc123,在此确认abc123,这是在root账户下运行的

mysql -u root -p     ##这个命令敲下，提示要输入密码,这个就是刚才设置的密码abc123

2.mysql 基础命令

[root@server1 mysql]# mysql -uroot -p123456
1.查看数据库信息
mysql> show databases；
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

##mysql 默认4个数据库
information_schema:定义访问数据库元数据的方式。数据库名和表名，列的数据类型、访问权限等。
mysql：核心数据库，负责存储数据库用户、权限、关键字等用户自己需要使用的控制和管理信息。
performance_schema：数据库的性能参数，存储引擎等。
sys：sys数据库下包含许多视图，它们以各种方式对performance_schema表进行聚合计算展示。

2.查看当前数据库中有那些表
mysql> use mysql;
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

3.查看表的结构
mysql> use mysql;
mysql> describe user;##可在后面加\G 方便查看

##SQL数据库语音的分类##
DDL (Data Definition Language, 数据定义语言） ：用来建立数据库、数据库对象和定义字段，如CREATE、ALTER、DROP。
DML（Data Manipulation Language，数据操纵语言）：用来插入、删除和修改数据库中的数据，如 INSERT、UPDATE、DELETE。
DQL（Data Query Language，数据查询语言）：用来查询数据库中的数据，如 SELECT。
DCL （Data Control Language，数据控制语言）：用来控制数据库组件的存取许可、存取权限等，如COMMIT、ROLLBACK、GRANT、REVOKE。
一.DDL
1.创建新的数据库
mysql> create database auth; ##创建数据库
Query OK, 1 row affected (0.00 sec)
mysql> use auth;
Database changed
mysql> create table users (username char(16) not null,user_passwd char(48) default '',primary key (username));
Query OK, 0 rows affected (0.01 sec)  ##创建新的表
mysql> describe users;   ##查看表
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| username    | char(16) | NO   | PRI | NULL    |       |
| user_passwd | char(48) | YES  |     |         |       |
+-------------+----------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> drop table auth.users;##删除表
Query OK, 0 rows affected (0.01 sec)
mysql> drop database auth;  ##删除数据库
Query OK, 0 rows affected (0.00 sec)
二. DML
1.插入数据记录
mysql> insert into users values('wangwu',654321);
Query OK, 1 row affected (0.00 sec)
2.查询数据记录
mysql> select * from users；
mysql> select username,user_passwd from users where username='zhangsan';
+----------+-------------+
| username | user_passwd |
+----------+-------------+
| zhangsan | 123456      |
+----------+-------------+
1 row in set (0.00 sec)
3.修改数据记录
mysql> update users set user_passwd='' where username='lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
4.修改数据库密码
mysql>use mysql  /*进入mysql用户*/
mysql>update user set authentication_string=password('newpass') where user='root';
flush privileges; ##刷新权限
忘记密码：
vi /etc/my.cnf
[mysqld] 
末尾添加
skip_name_resolve
skip_grant_tables
bind-address=0.0.0.0
systemctl restart mysqld
5.删除数据库记录
mysql> delete from auth.users where username='lisi';
Query OK, 1 row affected (0.02 sec)
mysql> select * from users；
+----------+-------------+
| username | user_passwd |
+----------+-------------+
| wangwu   | 654321      |
| zhangsan | 123456      |
+----------+-------------+
2 rows in set (0.00 sec)
##数据库高级操作##
1.清空表
DELETE 
FROM 语句，可以删除表内的数据，除此之外还可以使用 TRUNCATE TABLE 语句实现清空表内记录。DELETE FROM 语句
可以使用 WHERE 子句对删除的结果集进行过滤选择，这样更方便、更灵活。TRUNCATE TABLE 语句是删除表中所有记录
数据，没法定制，灵活性上稍差。

mysql> create table tmp like users;  ##通过LIKE方法，复制users表生成tmp表
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tmp select * from users;  ##通过users表生成tmp表内数据记录
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> delete from tmp where user_passwd = 123456;

mysql> insert into tmp select * from users where user_passwd = 123456;

mysql> truncate table tmp;

2.临时表
是临时建立的表，并不会长期存在，主要用于保存一些临时数据。临时表有个特性，就是只在当前连接可见，当前连接下可执行增删改查等
操作，当连接被关闭后，临时表就会被 MYSQL 删除，相关的资源也会被释放。

mysql> use auth;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> create temporary table temp(id int(10) not null auto_increment, name varchar(32) character set utf8 collate utf8_bin not null, level int(10) not null, primary key (id)) engine=InnoDB default charset=utf8;
Query OK, 0 rows affected (0.00 sec)
##auto_increment 自增值
##varchar 变化的字符型
##collate 编码
##character set utf8 设置字符型为utf8
注：临时表用show tables是看不到的，能看到数据，连接断开数据库删除

mysql> insert into temp values(1,'xiaoli',5);
Query OK, 1 row affected (0.00 sec)
mysql> select * from temp;
+----+--------+-------+
| id | name   | level |
+----+--------+-------+
|  1 | xiaoli |     5 |
+----+--------+-------+
1 row in set (0.00 sec)

3.克隆表
like方法
mysql> create table test like temp;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test select * from temp;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

show方法
mysql> show create table temp\G
CREATE TEMPORARY TABLE "text2" (  ##复制表的结构
          "id" int(10) NOT NULL AUTO_INCREMENT,
            "name" varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
          "level" int(10) NOT NULL,
          PRIMARY KEY ("id")
        ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8；
 mysql> insert into test2 select * from test;

4.用户授权
mysql> grant select on test to 'lisi'@'localhost'identified by '123456'；
mysql> flush privileges;##刷新权限
mysql> show grants;##show grants只能当前用户的权限；可以用mysql> show grants for 'lisi'@'localhost';
mysql> exit；
[root@server1 mysql]# mysql -ulisi -p123456 ##登录lisi账号
mysql> show grants；
+-----------------------------------------------------+
| Grants for lisi@localhost                           |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'lisi'@'localhost'            |
| GRANT SELECT ON "auth"."test" TO 'lisi'@'localhost' |
+-----------------------------------------------------+
2 rows in set (0.00 sec)


5.撤销用户权限
mysql> revoke all privileges on auth.* from 'wangwu'@'%';