一、打开MySQL 5.7 refman-5.7-en.html-chapter文档，找到安装目录
二、配置yum源
[root@mysql5 ~]# cat /etc/yum.repos.d/mysql57.repo
[MySQL57]
name = "MySQl 5.7"
baseurl = http://repo.mysql.com/yum/mysql-5.7-community/el/6/x86_64/
enable = 1
gpgcheck = 0
[root@mysql5 ~]#[root@mysql5 yum.repos.d]# yum clean all
Loaded plugins: aliases, changelog, kabi, ovl, presto, product-id, refresh-packagekit, search-disabled-repos, security, subscription-manager,
: tmprepo, verify, versionlock
This system is not registered with an entitlement server. You can use subscription-manager to register.
Loading support for Red Hat kernel ABI
Cleaning repos: MySQL57
Cleaning up Everything
0 delta-package files removed, by presto
[root@mysql5 yum.repos.d]# yum makecache
Loaded plugins: aliases, changelog, kabi, ovl, presto, product-id, refresh-packagekit, search-disabled-repos, security, subscription-manager,
: tmprepo, verify, versionlock
This system is not registered with an entitlement server. You can use subscription-manager to register.
Loading support for Red Hat kernel ABI
MySQL57 | 2.5 kB 00:00
MySQL57/filelists_db | 891 kB 00:01
MySQL57/primary_db | 187 kB 00:00
MySQL57/other_db | 46 kB 00:00
Metadata Cache Created
[root@mysql5 yum.repos.d]#三、执行安装
[root@mysql5 yum.repos.d]# yum install mysql-community-server
Loaded plugins: aliases, changelog, kabi, ovl, presto, product-id, refresh-packagekit, search-disabled-repos, security, subscription-manager,
: tmprepo, verify, versionlock
This system is not registered with an entitlement server. You can use subscription-manager to register.
Loading support for Red Hat kernel ABI
Setting up Install Process
MySQL57 | 2.5 kB 00:00
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-server.x86_64 0:5.7.27-1.el6 will be obsoleting
--> Processing Dependency: mysql-community-common(x86-64) = 5.7.27-1.el6 for package: mysql-community-server-5.7.27-1.el6.x86_64
--> Processing Dependency: mysql-community-client(x86-64) >= 5.7.9 for package: mysql-community-server-5.7.27-1.el6.x86_64
---> Package mysql-server.x86_64 0:5.1.73-8.el6_8 will be obsoleted
--> Running transaction check
---> Package mysql.x86_64 0:5.1.73-8.el6_8 will be obsoleted
--> Processing Dependency: mysql = 5.1.73-8.el6_8 for package: mysql-devel-5.1.73-8.el6_8.x86_64
---> Package mysql-community-client.x86_64 0:5.7.27-1.el6 will be obsoleting
--> Processing Dependency: mysql-community-libs(x86-64) >= 5.7.9 for package: mysql-community-client-5.7.27-1.el6.x86_64
---> Package mysql-community-common.x86_64 0:5.7.27-1.el6 will be installed
--> Running transaction check
---> Package mysql-community-devel.x86_64 0:5.7.27-1.el6 will be obsoleting
---> Package mysql-community-libs.x86_64 0:5.7.27-1.el6 will be obsoleting
---> Package mysql-devel.x86_64 0:5.1.73-8.el6_8 will be obsoleted
---> Package mysql-libs.x86_64 0:5.1.73-8.el6_8 will be obsoleted
--> Processing Dependency: libmysqlclient.so.16()(64bit) for package: 2:postfix-2.6.6-8.el6.x86_64
--> Processing Dependency: libmysqlclient.so.16()(64bit) for package: redland-1.0.7-11.el6.x86_64
--> Processing Dependency: libmysqlclient.so.16()(64bit) for package: perl-DBD-MySQL-4.013-3.el6.x86_64
--> Processing Dependency: libmysqlclient.so.16()(64bit) for package: qt3-MySQL-3.3.8b-30.el6.x86_64
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) for package: 2:postfix-2.6.6-8.el6.x86_64
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) for package: redland-1.0.7-11.el6.x86_64
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) for package: perl-DBD-MySQL-4.013-3.el6.x86_64
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) for package: qt3-MySQL-3.3.8b-30.el6.x86_64
--> Processing Dependency: libmysqlclient_r.so.16()(64bit) for package: 1:qt-mysql-4.6.2-28.el6_5.x86_64
--> Processing Dependency: libmysqlclient_r.so.16(libmysqlclient_16)(64bit) for package: 1:qt-mysql-4.6.2-28.el6_5.x86_64
--> Running transaction check
---> Package mysql-community-libs-compat.x86_64 0:5.7.27-1.el6 will be obsoleting
--> Finished Dependency Resolution
Dependencies Resolved
==================================================================================================================================================
Package Arch Version Repository Size
==================================================================================================================================================
Installing:
mysql-community-client x86_64 5.7.27-1.el6 MySQL57 22 M
replacing mysql.x86_64 5.1.73-8.el6_8
mysql-community-devel x86_64 5.7.27-1.el6 MySQL57 3.5 M
replacing mysql-devel.x86_64 5.1.73-8.el6_8
mysql-community-libs x86_64 5.7.27-1.el6 MySQL57 2.1 M
replacing mysql-libs.x86_64 5.1.73-8.el6_8
mysql-community-libs-compat x86_64 5.7.27-1.el6 MySQL57 1.6 M
replacing mysql-libs.x86_64 5.1.73-8.el6_8
mysql-community-server x86_64 5.7.27-1.el6 MySQL57 153 M
replacing mysql-server.x86_64 5.1.73-8.el6_8
Installing for dependencies:
mysql-community-common x86_64 5.7.27-1.el6 MySQL57 333 k
Transaction Summary
==================================================================================================================================================
Install 6 Package(s)
Total size: 183 M
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : mysql-community-common-5.7.27-1.el6.x86_64 1/10
Installing : mysql-community-libs-5.7.27-1.el6.x86_64 2/10
Installing : mysql-community-client-5.7.27-1.el6.x86_64 3/10
Installing : mysql-community-server-5.7.27-1.el6.x86_64 4/10
Installing : mysql-community-devel-5.7.27-1.el6.x86_64 5/10
Installing : mysql-community-libs-compat-5.7.27-1.el6.x86_64 6/10
Erasing : mysql-devel-5.1.73-8.el6_8.x86_64 7/10
Erasing : mysql-server-5.1.73-8.el6_8.x86_64 8/10
Erasing : mysql-5.1.73-8.el6_8.x86_64 9/10
Erasing : mysql-libs-5.1.73-8.el6_8.x86_64 10/10
Verifying : mysql-community-client-5.7.27-1.el6.x86_64 1/10
Verifying : mysql-community-common-5.7.27-1.el6.x86_64 2/10
Verifying : mysql-community-libs-5.7.27-1.el6.x86_64 3/10
Verifying : mysql-community-devel-5.7.27-1.el6.x86_64 4/10
Verifying : mysql-community-libs-compat-5.7.27-1.el6.x86_64 5/10
Verifying : mysql-community-server-5.7.27-1.el6.x86_64 6/10
Verifying : mysql-server-5.1.73-8.el6_8.x86_64 7/10
Verifying : mysql-5.1.73-8.el6_8.x86_64 8/10
Verifying : mysql-devel-5.1.73-8.el6_8.x86_64 9/10
Verifying : mysql-libs-5.1.73-8.el6_8.x86_64 10/10
Installed:
mysql-community-client.x86_64 0:5.7.27-1.el6 mysql-community-devel.x86_64 0:5.7.27-1.el6 mysql-community-libs.x86_64 0:5.7.27-1.el6
mysql-community-libs-compat.x86_64 0:5.7.27-1.el6 mysql-community-server.x86_64 0:5.7.27-1.el6
Dependency Installed:
mysql-community-common.x86_64 0:5.7.27-1.el6
Replaced:
mysql.x86_64 0:5.1.73-8.el6_8 mysql-devel.x86_64 0:5.1.73-8.el6_8 mysql-libs.x86_64 0:5.1.73-8.el6_8 mysql-server.x86_64 0:5.1.73-8.el6_8
Complete!
[root@mysql5 yum.repos.d]#
四、检查MySQL服务启动没有，然后启动MySQL服务
[root@mysql5 mysql]# service mysqld status
mysqld is stopped
[root@mysql5 mysql]#
[root@mysql5 mysql]# service mysqld start
Initializing MySQL database: [ OK ]
Starting mysqld: [ OK ]
[root@mysql5 mysql]#
五、修改MySQL本地root用户密码
查看临时密码：
[root@mysql5 mysql]# grep 'temporary password' /var/log/mysqld.log
2019-07-30T10:31:59.240984Z 1 [Note] A temporary password is generated for root@localhost: KOjsWEDb<5Lj
[root@mysql5 mysql]#
[root@mysql5 mysql]# 修改密码：
[root@mysql5 mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.27
Copyright (c) 2000, 2019, 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>
mysql> alter user 'root'@'localhost' identified by 'Ilovemysql_520';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>六、重新初始化数据库
修改/etc/my.cnf文件
mysqld]
#
datadir=/u01/mysql/data/
socket=/u01/mysql/data/mysql.sock
#basedir=/usr/share/mysql
symbolic-links=0
log-error=/u01/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
port= 3306
default-character-set=utf8
socket=/u01/mysql/data/mysql.sock
[root@mysql5 mysql]#
错误一：由于我这里是使用yum的方式安装，所以basedir就是默认的mysql软件安装目录：/usr/share/mysql 不需要再配置文件中配置，所以将其注释掉了，否则启动时会报错：Can't find error-message file '/usr/share/mysql/share/mysql/errmsg.sys'
错误二：
使用
# /usr/sbin/mysqld --initialize --user=mysql --datadir=/u01/mysql/data --basedir=/usr/share/mysql初始化完成后，使用：service mysqld start启动报错
[root@mysql5 data]# service mysqld start
2019-07-30T15:14:10.404479Z mysqld_safe Directory '/u01/mysql/data' for UNIX socket file don't exists.
MySQL Daemon failed to start.
Starting mysqld: [FAILED]
[root@mysql5 data]#提示没有sock文件，所以这里直接创建了sock文件：
[root@mysql5 data]# touch mysql.sock
[root@mysql5 data]# chown mysql:mysql mysql.sock
[root@mysql5 data]# chmod 777 mysql.sock错误三：错误日志未指定/u01/mysql/mysqld.log
[root@mysql5 data]#
[root@mysql5 data]# mysqld_safe start
2019-07-30T15:15:45.539428Z mysqld_safe Logging to '/var/log/mysqld.log'.
2019-07-30T15:15:45.576748Z mysqld_safe Starting mysqld daemon with databases from /u01/mysql/data
2019-07-30T15:15:47.555582Z mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
[root@mysql5 data]#
[root@mysql5 data]# mysqld_safe start
2019-07-30T15:17:45.226935Z mysqld_safe error: log-error set to '/u01/mysql/mysqld.log', however file don't exists. Create writable for user 'mysql'.
[root@mysql5 data]#解决办法，在/u0/mysql下touch mysqld.log文件，并修改权限（777）
错误四、使用[root@mysql5 data]# mysqld start --user=mysql启动报错：[ERROR] Too many arguments (first extra is 'start')
2019-07-30T15:27:07.617413Z 0 [Note] Plugin 'FEDERATED' is disabled.
2019-07-30T15:27:07.618601Z 0 [Note] InnoDB: Buffer pool(s) load completed at 190730 23:27:07
2019-07-30T15:27:07.620338Z 0 [ERROR] Too many arguments (first extra is 'start').
2019-07-30T15:27:07.620376Z 0 [Note] Use --verbose --help to get a list of available options!
2019-07-30T15:27:07.620386Z 0 [ERROR] Aborting
2019-07-30T15:27:07.620395Z 0 [Note] Binlog end
解决办法：直接使用[root@mysql5 sbin]# mysqld_safe & 方式启动，不需要带太多参数
错误五：数据库启动后，登陆数据库执行命令报错：
[root@mysql5 data]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.27
Copyright (c) 2000, 2019, 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>
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql>
mysql> update user set authentication_string = PASSWORD(‘root’) where User =’root.0.0.0.0.0.0.0.0.0’;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql>
mysql>
mysql>
mysql> update user set authentication_string=password('root') where user='root';
ERROR 1046 (3D000): No database selected
mysql>
mysql> use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql>
mysql>
mysql>解决办法：
mysql> set password=password("Ilovemysql_520");
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> help contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
Account Management
Administration
Compound Statements
Contents
Data Definition
Data Manipulation
Data Types
Functions
Geographic Features
Help Metadata
Language Structure
Plugins
Procedures
Storage Engines
Table Maintenance
Transactions
User-Defined Functions
Utility
mysql>
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
mysql>
mysql> exit查看数据库中用户
mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | % | *4B9B09F8086E66F01D7E681D2A399AB0BCE92A50 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql>
注意：5.7的版本中password字段没有了，换成了authentication_string，所以修改密码应该是：update user set authentication_string = PASSWORD(‘root’) where User =’root’;
创建表空间：
mysql> create tablespace tbs1 add datafile '/u01/mysql/data/tbs1.ibd' engine=innodb;
Query OK, 0 rows affected (0.27 sec)
mysql> select * from information_schema.INNODB_SYS_TABLESPACES;
+-------+---------------------------------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+---------------------------------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| 2 | mysql/plugin | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 98304 | 98304 |
| 3 | mysql/servers | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 98304 | 98304 |
| 4 | mysql/help_topic | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 9437184 | 9441280 |
| 5 | mysql/help_category | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 114688 | 114688 |
| 6 | mysql/help_relation | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 147456 | 151552 |
| 7 | mysql/help_keyword | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 245760 | 249856 |
| 8 | mysql/time_zone_name | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 98304 | 98304 |
| 9 | mysql/time_zone | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 98304 | 98304 |
| 10 | mysql/time_zone_transition | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 98304 | 98304 |
| 11 | mysql/time_zone_transition_type | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 98304 | 98304 |
| 12 | mysql/time_zone_leap_second | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 98304 | 98304 |
| 13 | mysql/innodb_table_stats | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 98304 | 98304 |
| 14 | mysql/innodb_index_stats | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 98304 | 98304 |
| 15 | mysql/slave_relay_log_info | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 98304 | 98304 |
| 16 | mysql/slave_master_info | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 98304 | 98304 |
| 17 | mysql/slave_worker_info | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 98304 | 98304 |
| 18 | mysql/gtid_executed | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 98304 | 98304 |
| 19 | mysql/server_cost | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 98304 | 98304 |
| 20 | mysql/engine_cost | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 98304 | 98304 |
| 21 | sys/sys_config | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 98304 | 98304 |
| 37 | tbs1 | 2048 | Any | Any | 16384 | 0 | General | 4096 | 65536 | 32768 |
+-------+---------------------------------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
21 rows in set (0.00 sec)
mysql> 创建用户
mysql> create user 'witeless_site'@'%' identified by 'wireless_site';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all privileges on *.* to 'wireless_site'@'%';
ERROR 1133 (42000): Can't find any matching row in the user table
mysql>
mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> grant all privileges on *.* to 'wireless_site'@'%';
ERROR 1133 (42000): Can't find any matching row in the user table
mysql> 这里遇到了报错，我想删除这个用户：
mysql> drop user 'wireless_site'@'%';
ERROR 1396 (HY000): Operation DROP USER failed for 'wireless_site'@'%'
mysql>
mysql> select Host,User,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| Host | User | authentication_string |
+-----------+---------------+-------------------------------------------+
| % | root | *4B9B09F8086E66F01D7E681D2A399AB0BCE92A50 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| % | witeless_site | *4487C7A674A85BF249B7BAE8405B00AB73C941E2 |
+-----------+---------------+-------------------------------------------+
4 rows in set (0.00 sec)
mysql>
mysql>
mysql> drop user 'wireless_site'@'%';
ERROR 1396 (HY000): Operation DROP USER failed for 'wireless_site'@'%'
mysql>
mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> drop user 'wireless_site'@'%';
ERROR 1396 (HY000): Operation DROP USER failed for 'wireless_site'@'%'
mysql>
还是删不掉--------------------------
mysql> delete from mysql.user where user='wireless_site' and host='%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
---------这样就删除了--------
再次查看：居然还在！！！！！！！！！！！！！！！！
mysql> select Host,User,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| Host | User | authentication_string |
+-----------+---------------+-------------------------------------------+
| % | root | *4B9B09F8086E66F01D7E681D2A399AB0BCE92A50 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| % | witeless_site | *4487C7A674A85BF249B7BAE8405B00AB73C941E2 |
+-----------+---------------+-------------------------------------------+
4 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> drop user 'witeless_site'@'%'; ---最后发现我把用户写错了，对。因为我瞎。
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select Host,User,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| Host | User | authentication_string |
+-----------+---------------+-------------------------------------------+
| % | root | *4B9B09F8086E66F01D7E681D2A399AB0BCE92A50 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+---------------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql>
创建数据库：
mysql> create database if not exists wireless_site default charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| wireless_site |
+--------------------+
5 rows in set (0.00 sec)
mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql>DEFAULT CHARACTER SET utf8：数据库字符集。设置数据库的默认编码为utf8，utf8中间不要"-"；
COLLATE utf8_general_ci:数据库校对规则。ci是case insensitive的缩写，意思是大小写不敏感；
相对的是cs，即case sensitive，大小写敏感；
还有一种是utf8_bin，是将字符串中的每一个字符用二进制数据存储，区分大小写。
如果建表的时候选择的是区别大小写的规则而查询的时候又暂时不想区别， 可以用类似 WHERE column_name COLLATE utf8_general_ci = 'xxx' 的写法改变查询使用的校对规则，新建数据 库时一般选用utf8_general_ci就可以了。
查看系统当前表空间：
mysql> select SPACE,NAME,SPACE_TYPE,ROW_FORMAT,FILE_FORMAT,ALLOCATED_SIZE from information_schema.innodb_sys_tablespaces;
+-------+---------------------------------+------------+------------+-------------+----------------+
| SPACE | NAME | SPACE_TYPE | ROW_FORMAT | FILE_FORMAT | ALLOCATED_SIZE |
+-------+---------------------------------+------------+------------+-------------+----------------+
| 2 | mysql/plugin | Single | Dynamic | Barracuda | 98304 |
| 3 | mysql/servers | Single | Dynamic | Barracuda | 98304 |
| 4 | mysql/help_topic | Single | Dynamic | Barracuda | 9441280 |
| 5 | mysql/help_category | Single | Dynamic | Barracuda | 114688 |
| 6 | mysql/help_relation | Single | Dynamic | Barracuda | 151552 |
| 7 | mysql/help_keyword | Single | Dynamic | Barracuda | 249856 |
| 8 | mysql/time_zone_name | Single | Dynamic | Barracuda | 98304 |
| 9 | mysql/time_zone | Single | Dynamic | Barracuda | 98304 |
| 10 | mysql/time_zone_transition | Single | Dynamic | Barracuda | 98304 |
| 11 | mysql/time_zone_transition_type | Single | Dynamic | Barracuda | 98304 |
| 12 | mysql/time_zone_leap_second | Single | Dynamic | Barracuda | 98304 |
| 13 | mysql/innodb_table_stats | Single | Dynamic | Barracuda | 98304 |
| 14 | mysql/innodb_index_stats | Single | Dynamic | Barracuda | 98304 |
| 15 | mysql/slave_relay_log_info | Single | Dynamic | Barracuda | 98304 |
| 16 | mysql/slave_master_info | Single | Dynamic | Barracuda | 98304 |
| 17 | mysql/slave_worker_info | Single | Dynamic | Barracuda | 98304 |
| 18 | mysql/gtid_executed | Single | Dynamic | Barracuda | 98304 |
| 19 | mysql/server_cost | Single | Dynamic | Barracuda | 98304 |
| 20 | mysql/engine_cost | Single | Dynamic | Barracuda | 98304 |
| 21 | sys/sys_config | Single | Dynamic | Barracuda | 98304 |
| 37 | tbs1 | General | Any | Any | 32768 |
+-------+---------------------------------+------------+------------+-------------+----------------+
21 rows in set (0.01 sec)
mysql>关于视图：INNODB_SYS_TABLESPACES
INNODB_SYS_TABLESPACES表包含以下列：
SPACE ：表空间ID
NAME ：schema（数据库）和表名，例如test/t1
FLAG ：有关表空间格式和存储特性的位级数据。
FILE_FORMAT ：表空间文件格式。 例如，Antelope，Barracuda或Any（通用表空间支持任何行格式）。 此字段中的数据是从驻留在.ibd文件中的表空间标记信息中解释的。 有关InnoDB文件格式的更多信息，请参见“InnoDB文件格式管理”。
ROW_FORMAT ：表空间行格式（Compact or Redundant, Dynamic, or Compressed）。此字段中的数据是从驻留在.ibd文件中的表空间标记信息中解释的。
PAGE_SIZE ：表空间页面大小。此字段中的数据是从驻留在.ibd文件中的表空间标记信息中解释的。
ZIP_PAGE_SIZE ：表空间zip页面大小。此字段中的数据是从驻留在.ibd文件中的表空间标记信息中解释的。
SPACE_TYPE ：表空间的类型。可能的值包括常规表空间的常规和每表文件表空间的单个。
FS_BLOCK_SIZE ：文件系统块大小，是用于打孔的单位大小。此列添加了InnoDB透明页面压缩功能的介绍。
FILE_SIZE ：表示文件的最大大小，未压缩。此列添加了InnoDB透明页面压缩功能的介绍。
ALLOCATED_SIZE：文件的实际大小，即磁盘上分配的空间量。此列添加了InnoDB透明页面压缩功能的介绍。
创建用户：
mysql> create user wireless_site@'%' identified by 'wireless_site';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select Host,User,authentication_string,account_locked from mysql.user;
+-----------+---------------+-------------------------------------------+----------------+
| Host | User | authentication_string | account_locked |
+-----------+---------------+-------------------------------------------+----------------+
| % | root | *4B9B09F8086E66F01D7E681D2A399AB0BCE92A50 | N |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | Y |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | Y |
| % | wireless_site | *4487C7A674A85BF249B7BAE8405B00AB73C941E2 | N |
+-----------+---------------+-------------------------------------------+----------------+
4 rows in set (0.00 sec)
mysql>
关于mysql.user表
Select_priv：用户可以通过SELECT命令选择数据。
Insert_priv：用户可以通过INSERT命令插入数据;
Update_priv：用户可以通过UPDATE命令修改现有数据;
Delete_priv：用户可以通过DELETE命令删除现有数据;
Create_priv：用户可以创建新的数据库和表;
Drop_priv：用户可以删除现有数据库和表;
Reload_priv：用户可以执行刷新和重新加载MySQL所用各种内部缓存的特定命令,包括日志、权限、主机、查询和表;重新加载权限表;
Shutdown_priv：用户可以关闭MySQL服务器;在将此权限提供给root账户之外的任何用户时,都应当非常谨慎;
Process_priv：用户可以通过SHOW PROCESSLIST命令查看其他用户的进程;服务器管理;
File_priv：用户可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令;加载服务器上的文件;
Grant_priv：用户可以将已经授予给该用户自己的权限再授予其他用户(任何用户赋予全部已有权限);
References_priv;目前只是某些未来功能的占位符；现在没有作用;
Index_priv：用户可以创建和删除表索引;用索引查询表;
Alter_priv：用户可以重命名和修改表结构;
Show_db_priv：用户可以查看服务器上所有数据库的名字,包括用户拥有足够访问权限的数据库;可以考虑对所有用户禁用这个权限,除非有特别不可抗拒的原因;
Super_priv：用户可以执行某些强大的管理功能,例如通过KILL命令删除用户进程,使用SET GLOBAL修改全局MySQL变量,执行关于复制和日志的各种命令;超级权限;
Create_tmp_table_priv：用户可以创建临时表;
Lock_tables_priv：用户可以使用LOCK TABLES命令阻止对表的访问/修改;
Execute_priv：用户可以执行存储过程;此权限只在MySQL 5.0及更高版本中有意义;
Repl_slave_priv：用户可以读取用于维护复制数据库环境的二进制日志文件;此用户位于主系统中,有利于主机和客户机之间的通信;主服务器管理;
Repl_client_priv：用户可以确定复制从服务器和主服务器的位置;从服务器管理;
Create_view_priv：用户可以创建视图;此权限只在MySQL 5.0及更高版本中有意义;
Show_view_priv：用户可以查看视图或了解视图如何执行;此权限只在MySQL 5.0及更高版本中有意义;
Create_routine_priv：用户可以更改或放弃存储过程和函数;此权限是在MySQL 5.0中引入的;
Alter_routine_priv：用户可以修改或删除存储函数及函数;此权限是在MySQL 5.0中引入的;
Create_user_priv：用户可以执行CREATE USER命令,这个命令用于创建新的MySQL账户;
Event_priv：用户能否创建、修改和删除事件;这个权限是MySQL 5.1.6新增的;
Trigger_priv：用户能否创建和删除触发器,这个权限是MySQL 5.1.6新增的;
Create_tablespace_priv：创建表空间
ssl_type：支持ssl标准加密安全字段
ssl_cipher：支持ssl标准加密安全字段
x509_issuer：支持x509标准字段
x509_subject：支持x509标准字段
max_questions：0 每小时允许执行多少次查询
max_updates：0 每小时可以执行多少次更新 ：0表示无限制
max_connections：0 每小时可以建立的多少次连接：0表示无限制
max_user_connections：0 单用户可以同时具有的连接数：0表示无限制
plugin：5.5.7开始,mysql引入plugins以进行用户连接时的密码验证,plugin创建外部/代理用户
authentication_string：通过authentication_string可以控制两者的映射关系,(PAM plugin等,PAM可以支持多个服务名)尤其是在使用代理用户时，并须声明这一点
password_expired：密码过期 Y,说明该用户密码已过期 N相反 查看新建用户权限：
mysql> select Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv from user where user='wireless_site';
+-------------+-------------+-------------+-------------+-------------+-----------+
| Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv |
+-------------+-------------+-------------+-------------+-------------+-----------+
| N | N | N | N | N | N |
+-------------+-------------+-------------+-------------+-------------+-----------+
1 row in set (0.00 sec)
mysql> 授权：
mysql> grant all privileges on *.* to 'wireless_site'@'%' with grant option;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv from mysql.user where user='wireless_site';
+-------------+-------------+-------------+-------------+-------------+-----------+
| Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv |
+-------------+-------------+-------------+-------------+-------------+-----------+
| Y | Y | Y | Y | Y | Y |
+-------------+-------------+-------------+-------------+-------------+-----------+
1 row in set (0.00 sec)
mysql>创建表并指定表空间：
mysql> create table p tablespace tbs1 as select * from PACKAGES;
Query OK, 17604 rows affected (0.25 sec)
Records: 17604 Duplicates: 0 Warnings: 0
mysql> select count(*) from p;
+----------+
| count(*) |
+----------+
| 17604 |
+----------+
1 row in set (0.01 sec)
mysql>
mysql> select SPACE,NAME,SPACE_TYPE,ROW_FORMAT,FILE_FORMAT,ALLOCATED_SIZE from information_schema.innodb_sys_tablespaces;
+-------+-----------------------------------------+------------+------------+-------------+----------------+
| SPACE | NAME | SPACE_TYPE | ROW_FORMAT | FILE_FORMAT | ALLOCATED_SIZE |
+-------+-----------------------------------------+------------+------------+-------------+----------------+
| 2 | mysql/plugin | Single | Dynamic | Barracuda | 98304 |
| 3 | mysql/servers | Single | Dynamic | Barracuda | 98304 |
| 4 | mysql/help_topic | Single | Dynamic | Barracuda | 9441280 |
| 5 | mysql/help_category | Single | Dynamic | Barracuda | 114688 |
| 6 | mysql/help_relation | Single | Dynamic | Barracuda | 151552 |
| 7 | mysql/help_keyword | Single | Dynamic | Barracuda | 249856 |
| 8 | mysql/time_zone_name | Single | Dynamic | Barracuda | 98304 |
| 9 | mysql/time_zone | Single | Dynamic | Barracuda | 98304 |
| 10 | mysql/time_zone_transition | Single | Dynamic | Barracuda | 98304 |
| 11 | mysql/time_zone_transition_type | Single | Dynamic | Barracuda | 98304 |
| 12 | mysql/time_zone_leap_second | Single | Dynamic | Barracuda | 98304 |
| 13 | mysql/innodb_table_stats | Single | Dynamic | Barracuda | 98304 |
| 14 | mysql/innodb_index_stats | Single | Dynamic | Barracuda | 167936 |
| 15 | mysql/slave_relay_log_info | Single | Dynamic | Barracuda | 98304 |
| 16 | mysql/slave_master_info | Single | Dynamic | Barracuda | 98304 |
| 17 | mysql/slave_worker_info | Single | Dynamic | Barracuda | 98304 |
| 18 | mysql/gtid_executed | Single | Dynamic | Barracuda | 98304 |
| 19 | mysql/server_cost | Single | Dynamic | Barracuda | 98304 |
| 20 | mysql/engine_cost | Single | Dynamic | Barracuda | 98304 |
| 21 | sys/sys_config | Single | Dynamic | Barracuda | 98304 |
| 37 | tbs1 | General | Any | Any | 14684160 |
| 118 | wireless_site/KEYWORDHISTORY | Single | Dynamic | Barracuda | 98304 |
| 119 | wireless_site/ACCESSLOG | Single | Dynamic | Barracuda | 96473088 |
| 120 | wireless_site/ADVERTISEMENT | Single | Dynamic | Barracuda | 114688 |
| 121 | wireless_site/AFFICHE | Single | Dynamic | Barracuda | 135168 |
| 122 | wireless_site/AFFICHETYPE | Single | Dynamic | Barracuda | 98304 |
| 123 | wireless_site/AREA | Single | Dynamic | Barracuda | 98304 |
| 124 | wireless_site/AREA_PY | Single | Dynamic | Barracuda | 98304 |
| 125 | wireless_site/AREAADMIN | Single | Dynamic | Barracuda | 114688 |
| 126 | wireless_site/BLACKLIST | Single | Dynamic | Barracuda | 98304 |
| 127 | wireless_site/BOSSMERCHANT | Single | Dynamic | Barracuda | 9441280 |
| 128 | wireless_site/BOSSPACKAGES | Single | Dynamic | Barracuda | 9441280 |
| 129 | wireless_site/CLICKTHROUGHRATE | Single | Dynamic | Barracuda | 591400960 |
| 130 | wireless_site/CLIENT | Single | Dynamic | Barracuda | 13635584 |
| 131 | wireless_site/CLIENTGROUP | Single | Dynamic | Barracuda | 98304 |
| 132 | wireless_site/CLIENTGROUPLINK | Single | Dynamic | Barracuda | 282624 |
| 133 | wireless_site/CMCCHUBEIMT | Single | Dynamic | Barracuda | 83890176 |
| 134 | wireless_site/COLLECTION | Single | Dynamic | Barracuda | 184320 |
| 135 | wireless_site/COSTRULE | Single | Dynamic | Barracuda | 98304 |
| 136 | wireless_site/COUPON | Single | Dynamic | Barracuda | 135168 |
| 137 | wireless_site/COUPONCLASS | Single | Dynamic | Barracuda | 98304 |
| 138 | wireless_site/COUPONORDER | Single | Dynamic | Barracuda | 98304 |
| 139 | wireless_site/CRITIQUE | Single | Dynamic | Barracuda | 184320 |
| 140 | wireless_site/CRITIQUEZTYPE | Single | Dynamic | Barracuda | 98304 |
| 141 | wireless_site/DEPARTMENT | Single | Dynamic | Barracuda | 114688 |
| 142 | wireless_site/DOMAINNODE | Single | Dynamic | Barracuda | 114688 |
| 143 | wireless_site/ECLOGINLOG | Single | Dynamic | Barracuda | 462848 |
| 144 | wireless_site/EMPLOYEE | Single | Dynamic | Barracuda | 331776 |
| 145 | wireless_site/FELLOWSHIP | Single | Dynamic | Barracuda | 200704 |
| 146 | wireless_site/FILEINFO | Single | Dynamic | Barracuda | 167936 |
| 147 | wireless_site/FUNCTION | Single | Dynamic | Barracuda | 98304 |
| 148 | wireless_site/GETTYPE | Single | Dynamic | Barracuda | 98304 |
| 149 | wireless_site/IMAGENEWS | Single | Dynamic | Barracuda | 135168 |
| 150 | wireless_site/ITEM | Single | Dynamic | Barracuda | 37752832 |
| 151 | wireless_site/ITEMTYPE | Single | Dynamic | Barracuda | 114688 |
| 152 | wireless_site/KEYWORD | Single | Dynamic | Barracuda | 98304 |
| 153 | wireless_site/LINKMAN | Single | Dynamic | Barracuda | 98304 |
| 154 | wireless_site/MERCHANT | Single | Dynamic | Barracuda | 16781312 |
| 155 | wireless_site/MERCHANTAPPLY | Single | Dynamic | Barracuda | 151552 |
| 156 | wireless_site/MERCHANTCLASS | Single | Dynamic | Barracuda | 98304 |
| 157 | wireless_site/MERCHANTCLIENT | Single | Dynamic | Barracuda | 98304 |
| 158 | wireless_site/MERCHANTCLIENTLINK | Single | Dynamic | Barracuda | 16781312 |
| 159 | wireless_site/MESSAGEBOARD | Single | Dynamic | Barracuda | 9441280 |
| 160 | wireless_site/NEWS | Single | Dynamic | Barracuda | 54530048 |
| 161 | wireless_site/OPERATELOGGER | Single | Dynamic | Barracuda | 98304 |
| 162 | wireless_site/PACKAGES | Single | Dynamic | Barracuda | 13635584 |
| 163 | wireless_site/PACKAGESCLASS | Single | Dynamic | Barracuda | 114688 |
| 164 | wireless_site/PACKAGESCLASSFUNCTIONLINK | Single | Dynamic | Barracuda | 114688 |
| 165 | wireless_site/PASSPORT | Single | Dynamic | Barracuda | 200704 |
| 166 | wireless_site/PAYTYPE | Single | Dynamic | Barracuda | 98304 |
| 167 | wireless_site/PLUGIN | Single | Dynamic | Barracuda | 98304 |
| 168 | wireless_site/PLUGIN_USER | Single | Dynamic | Barracuda | 98304 |
| 169 | wireless_site/PRODUCT | Single | Dynamic | Barracuda | 13635584 |
| 170 | wireless_site/PRODUCTCLASS | Single | Dynamic | Barracuda | 98304 |
| 171 | wireless_site/PRODUCTORDER | Single | Dynamic | Barracuda | 184320 |
| 172 | wireless_site/RECEIVEMESSAGE | Single | Dynamic | Barracuda | 9441280 |
| 173 | wireless_site/RECOMMEND | Single | Dynamic | Barracuda | 114688 |
| 174 | wireless_site/REGISTERPROTOCOL | Single | Dynamic | Barracuda | 151552 |
| 175 | wireless_site/REGISTERTEMP | Single | Dynamic | Barracuda | 98304 |
| 176 | wireless_site/REPLYMESSAGEBOARD | Single | Dynamic | Barracuda | 114688 |
| 177 | wireless_site/SENDMESSAGE | Single | Dynamic | Barracuda | 12587008 |
| 178 | wireless_site/SITESTYLE | Single | Dynamic | Barracuda | 98304 |
| 179 | wireless_site/SITETYPE | Single | Dynamic | Barracuda | 98304 |
| 180 | wireless_site/SIUSER | Single | Dynamic | Barracuda | 98304 |
| 181 | wireless_site/SMSSERVICETYPE | Single | Dynamic | Barracuda | 98304 |
| 182 | wireless_site/SMSSESSION | Single | Dynamic | Barracuda | 200704 |
| 183 | wireless_site/TEMPLATE | Single | Dynamic | Barracuda | 249856 |
| 184 | wireless_site/TEMPLATESTYLE | Single | Dynamic | Barracuda | 98304 |
| 185 | wireless_site/UNIT | Single | Dynamic | Barracuda | 98304 |
| 186 | wireless_site/USERRECOMMEND | Single | Dynamic | Barracuda | 167936 |
| 187 | wireless_site/WHITELIST | Single | Dynamic | Barracuda | 98304 |
| 191 | wireless_site/COUPONKEYWORD | Single | Dynamic | Barracuda | 98304 |
+-------+-----------------------------------------+------------+------------+-------------+----------------+
92 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql> create table c tablespace tbs1 as select * from CLICKTHROUGHRATE;
Query OK, 2384026 rows affected (41.44 sec)
Records: 2384026 Duplicates: 0 Warnings: 0
mysql>
mysql> select count(*) from CLICKTHROUGHRATE;
+----------+
| count(*) |
+----------+
| 2384026 |
+----------+
1 row in set (1.22 sec)
mysql>
mysql> select count(*) from c;
+----------+
| count(*) |
+----------+
| 2384026 |
+----------+
1 row in set (1.45 sec)
mysql>
mysql>
扫一扫
3971
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
