MySQL5.7.15安装、登录、远程登录

创建mysql用户和组

1
2
# groupadd mysql
# useradd -r -g mysql -s /bin/false mysql

解压mysql-5.7.15-linux-glibc2.5-x86_64.tar到/usr/local下面,执行如下命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
# cd /usr/local
# ln -s mysql-5.7.15-linux-glibc2.5-x86_64 mysql
# cd mysql
# mkdir mysql-files
# chmod 750 mysql-files
# # chown -R mysql .
# chgrp -R mysql .
# bin/mysqld --initialize --user=mysql
2016-09-10T14:19:07.373035Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-09-10T14:19:10.479748Z 0 [Warning] InnoDB: New log files created, LSN=45790
2016-09-10T14:19:10.823291Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-09-10T14:19:10.995510Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 8b0f6916-7761-11e6-bbac-00163e001537.
2016-09-10T14:19:11.025351Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2016-09-10T14:19:11.029706Z 1 [Note] A temporary password is generated for root@localhost: S?gFkFr;5)TZ
# bin/mysql_ssl_rsa_setup
Generating a 2048 bit RSA private key
............................................+++
.................................................+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
....................................................................................................................................+++
...+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
.....+++
...................+++
writing new private key to 'client-key.pem'
-----
# chown -R root .
# chown -R mysql data mysql-files
# bin/mysqld_safe --user=mysql &
[1] 5524
# cp support-files/mysql.server /etc/init.d/mysql.server

查看服务进程

1
2
3
4
# ps -ef|grep mysql
root      5524  4768  0 22:21 pts/2    00:00:00 /bin/sh bin/mysqld_safe --user=mysql
mysql     5610  5524  0 22:21 pts/2    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/xxxx.err --pid-file=/usr/local/mysql/data/xxxx.pid
root      5639  4768  0 22:22 pts/2    00:00:00 grep --color=auto mysql

停止mysql、启动mysql、重启mysql

1
2
3
4
5
6
7
8
9
10
11
# /etc/init.d/mysql.server stop
Shutting down MySQL..2016-09-10T14:26:53.055608Z mysqld_safe mysqld from pid file /usr/local/mysql/data/xxxx.pid ended
                                                           [  确定  ]
[1]+  完成                  bin/mysqld_safe --user=mysql
# ps -ef|grep mysql
root      5668  4768  0 22:27 pts/2    00:00:00 grep --color=auto mysql
# /etc/init.d/mysql.server start
Starting MySQL.  
# /etc/init.d/mysql.server restart
Shutting down MySQL..                                      [  确定  ]
Starting MySQL.                                            [  确定  ]

客户端连接mysql服务,初始的随机密码位置在root目录下面

1
# vi /root/.mysql_secret

如果使用刚刚的密码登录失败,则停止mysql,并跳过验证登录

1
2
# cd /usr/local/mysql
# ./bin/mysqld_safe --skip-grant-tables &

登录,查看user表结构和初始数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
# /usr/local/mysql/bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.15 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> 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> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.01 sec)

mysql> select user,host,authentication_string,password_expired from user;
+-----------+-----------+-------------------------------------------+------------------+
| user      | host      | authentication_string                     | password_expired |
+-----------+-----------+-------------------------------------------+------------------+
| root      | localhost | *00881DDA4E834681A467780B1BFF03D913447683 | Y                |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N                |
+-----------+-----------+-------------------------------------------+------------------+
2 rows in set (0.00 sec)

修改密码

1
2
mysql> update user set authentication_string=password('123456'),password_expired='N' where user='root';
mysql> flush privileges;

重启服务并使用root登录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# /etc/init.d/mysql.server restart
# /usr/local/mysql/bin/mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.15 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>

允许远程访问MySQL服务

开放3306 端口

1
2
3
4
# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
# firewall-cmd --reload
success

修改root用户的host为%

1
mysql> update user set host='%' where user='root';

查看3306端口情况

1
# netstat -tunlp |grep 3306

创建新用户并授权[可选方式]

1
mysql> CREATE USER 'username'@'%' IDENTIFIED BY 'userpassword';

MySQL日志如果有如下警告

1
[Warning] IP address 'x.x.x.x' could not be resolved: Name or service not known

修改my.cnf文件,在[mysqld]下面添加skip-*如下,重启服务即可

1
2
3
4
[mysqld]

skip-host-cache
skip-name-resolve

通过命令行远程连接,经测试Navicate连接正常【域名或IP地址】

1
$ mysql -P3306 -uroot -hIP地址 -ppasswd
邵志鹏 wechat
扫一扫上面的二维码关注我的公众号
0%