-
Notifications
You must be signed in to change notification settings - Fork 0
安装MySQL
rpm -qa | grep mysql
或yum list installed | grep mysql
[root@localhost ~]# rpm -qa | grep mysql [root@localhost ~]#
如果已安装可以执行命令删除 MySQL 及其依赖的包:
yum -y remove mysql-libs.XXXXX
mysql57-community-release-el7-8.noarch.rpm 的 YUM 源:
[root@localhost soft-package]# wget http://repo.mysql.com/mysql57-community-release-el7-8.noarch.rpm
[root@localhost soft-package]# rpm -ivh mysql57-community-release-el7-8.noarch.rpm
安装完后,执行命令
rpm -ql mysql57-community-release
[root@localhost soft-package]# rpm -ql mysql57-community-release /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql /etc/yum.repos.d/mysql-community-source.repo /etc/yum.repos.d/mysql-community.repo [root@localhost soft-package]#
可以看到得到如下两个包:
- mysql-community.repo
- mysql-community-source.repo
[root@localhost soft-package]# yum install mysql-server
遇到疑问的地方,一路 Y 下去即可;
......... Installed: mysql-community-libs.x86_64 0:5.7.17-1.el7 mysql-community-libs-compat.x86_64 0:5.7.17-1.el7 mysql-community-server.x86_64 0:5.7.17-1.el7 Dependency Installed: mysql-community-client.x86_64 0:5.7.17-1.el7 mysql-community-common.x86_64 0:5.7.17-1.el7 Replaced: mariadb-libs.x86_64 1:5.5.35-3.el7
启动mysql:
[root@localhost ~]# systemctl start mysqld
查看启动状态:
[root@localhost ~]# systemctl status mysqld mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled) Active: active (running) since Sun 2017-04-02 01:37:18 EDT; 22s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 45886 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $M YSQLD_OPTS (code=exited, status=0/SUCCESS) Process: 40196 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, > > status=0/SUCCESS) Main PID: 45954 (mysqld) CGroup: /system.slice/mysqld.service └─45954 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid [root@localhost ~]#
启动成功后,在
/var/log/mysqld.log
文件中会自动生成一个随机的密码,我们需要先取得这个随机密码,以用于登录 MySQL 服务端:[root@localhost soft-package]# grep "password" /var/log/mysqld.log
打印如下内容:
[root@localhost soft-package]# grep "password" /var/log/mysqld.log 2017-04-02T05:37:01.531831Z 1 [Note] A temporary password is generated for root@localhost: M-yhrCfD-459 2017-04-02T05:37:04.539623Z 1 [ERROR] Failed to open the bootstrap file /tmp/install-validate-password-plugin.CeLKfI.sql [root@localhost soft-package]#
我们复制 root@localhost: 后面的随机字符串,这个字符串就是 MySQL 在安装完成后为我们随机生成的密码
使用随机生成的密码登陆mysql,重新设置密码:
- 如果密码包含了特殊字符,用单引号将密码括起来 如:
mysql -u root -p'oPA3ys>wf)i9'
[root@localhost soft-package]# mysql -u root -pM-yhrCfD-459 mysql> set password=password('root'); Query OK, 0 rows affected, 1 warning (0.12 sec) mysql> alter user 'root'@'localhost' password expire never; Query OK, 0 rows affected (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.12 sec) mysql> exit;使用新密码重新登陆mysql:
[root@localhost soft-package]# mysql -u root -proot
设置用户 root 可以在任意 IP 下被访问:
[root@localhost soft-package]# grant all privileges on *.* to root@"localhost" identified by "root";
设置用户 root 可以在本地被访问:
[root@localhost soft-package]# grant all privileges on *.* to root@"localhost" identified by "root";
刷新权限使之生效:
[root@localhost soft-package]# flush privileges;
- 认证
- MySQL中,用户相关的信息存在数据库mysql的user表中。
- MySQL中,使用(Host, User)这样的组合键作为主键,也就是标识唯一一个用户。换句话说,即使对于同一个用户名,例如root,随着host的不同,也表示不同的用户,就可以由不同的密码和权限。
根据MySQL官方文档MySQL :: MySQL 5.6 Reference Manual :: 6.2.4 Access Control, Stage 1: Connection Verification里有下面这样一句话:“The server uses sorting rules that order rows with the most-specific Host values first. Literal host names and IP addresses are the most specific.”
文档说明:主机(host)有比用户名称user更高的匹配优先级。('root'@'localhost'也可以匹配''@'localhost'可以匹配到匿名用户)
- 常见登录命令:
[root@localhost local]# mysql -u root -p
Enter password:
或
[root@localhost local]# mysql -u root -pPassword
在命令行里面没有写-h的话默认的是localhost
所以你用root登录的话,他会自动来这个user表匹配host,pwd,user的,如果没有找到匹配的,提示登录拒绝
如果你添加一条记录
localhost root 空
这样root就可以免密码登录了
[root@localhost local]# mysql -u root
- 删除匿名用户:
delete from user where user='';
-
/etc
目录下的my.cnf
文件(此文件是 MySQL 的主配置文件) :/etc/my.cnf
-
/var/lib/mysql
存放数据库文件的目录 :/var/lib/mysql
是存放数据库文件的目录; -
/var/log
存放 MySQL 的日志 :/var/log/mysqld.log
查看MySQL的字符编码:
mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.06 sec)
设置为utf8编码:
[root@localhost ~]# vi /etc/my.cnf
添加如下配置:
[client]
default-character-set = utf8
[mysqld]下添加:
character-set-server = utf8
重新启动MySQL:
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]#
mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql>
- show databases; 查看所有数据库
- use sys; 切换到sys数据库
- show table status from mysql like '%servers%'; 查看指定的数据库中指定数据表的字符集,如查看 mysql 数据库中 servers 表的字符集:
- show full columns from servers; 查看指定数据库中指定表的全部列的字符集,如查看 mysql 数据库中 servers 表的全部的列的字符集:
- show variables like '%character%'; 查看 MySQL 的字符集
- 查看 MySQL 启动状态:
systemctl status mysqld
- 启动 MySQL 服务:
systemctl start mysqld
- 关闭 MySQL 服务:
systemctl stop mysqld
- 重启 MySQL 服务:
systemctl restart mysqld
- 允许本地IP访问(指定host为localhost或127.0.0.1),新建用户test,指定密码为123456
- create user 'test'@'localhost' identified by '123456';
- 允许外网访问(%是任意ip,配置指定的ip,允许指定机器访问)
- create user 'test'@'%' identified by '123456';
mysql> use mysql
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> select user,host from user;
+-----------+-----------+
| user | host |
+-----------+-----------+
| gerrit001 | localhost |
| mysql.sys | localhost |
| root | localhost |
+-----------+-----------+
3 rows in set (0.06 sec)
mysql>
可以看到有3个用户,只允许localhost连接;授权root用户以密码123456访问远程库:
mysql> grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> select user,host from user;
+-----------+-----------+
| user | host |
+-----------+-----------+
| root | % |
| gerrit001 | localhost |
| mysql.sys | localhost |
| root | localhost |
+-----------+-----------+
4 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.12 sec)
mysql>