首页 » 编程 » Python-8.MySQL数据库 » 正文

Mysql安装以及基本管理

对于数据库产品,我们不要求其使用最新的,必须要是要最稳定的,比如当前mysql官网已经有了mysql8.0,但我们还是以mysql5.6来作为学习或生产。

下载

MySql5.6官方下载地址:https://dev.mysql.com/downloads/mysql/5.6.html#downloads

选择对应的平台进行下载

安装

Windows平台

1. 下载对应平台的软件包,我这里是64位的Windows 10,所以选择 Windows (x86, 64-bit), ZIP Archive,点击 Download 进行下载。

2. 下载后直接解压即可使用,我这里把它解压到 D:\Program Files\mysql-5.6.43-winx64 

在bin目录下找到 mysqld.exe 双击即可启动mysql,找到 mysql.exe 即启动mysql客户端,或者将mysql的bin目录加入系统环境变量,也可直接在cmd中启动mysql。

 

但是如果每次都这样启动很麻烦,我们把它加到系统服务里,每次开机就自动启动了,如下(前提是已经正确添加了环境变量或进入到mysql的bin目录下执行):

以管理员身份打开cmd,执行  mysqld –install 显示Service successfully installed.即加入系统服务成功。

可在系统服务里看到MYSQL服务,刚刚加好时mysql没有启动,可手动点击“启动”启动服务,下载开机的时候就自动启动了。

或者执行:

启动mysql服务:net start mysql

停止mysql服务:net stop mysql

 

如果要从系统服务中移除MYSQL服务以同样的方式在cmd中执行 mysqld –remove 即可。

 

小提示:

在Windows中查找进程并杀掉进程的方法:

C:\>tasklist | findstr mysqld
mysqld.exe 18188 Services 0 452,484 K

C:\>
C:\>taskkill /F /PID 18188
成功: 已终止 PID 为 18188 的进程。

C:\>

 

查看当前登录的是哪个用户:

>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.6.43 MySQL Community Server (GPL)

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> select users();
ERROR 1305 (42000): FUNCTION users does not exist
mysql>
mysql> select user();
+----------------+
| user()         |
+----------------+
| ODBC@localhost |
+----------------+
1 row in set (0.00 sec)

 

>mysql -uroot -p
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 16
Server version: 5.6.43 MySQL Community Server (GPL)

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> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

 

设置密码:

初次使用mysql默认没有密码,为了安全需要进行设置:

>mysqladmin -uroot -p password "123"
Enter password:    
Warning: Using a password on the command line interface can be insecure.   # 忽略

首次设置密码的时候由于没有密码,所以-p后不加任何参数,提示输入密码的时候直接回车,password “123” 表示设置密码为123

 

以密码的方式进行登录:

>mysql -uroot -p123
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 20
Server version: 5.6.43 MySQL Community Server (GPL)

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>

 

修改密码:

>mysqladmin -uroot -p123 password "123456"
Warning: Using a password on the command line interface can be insecure.  # 忽略

由于已经设置了密码,所以-p后跟原密码123, password “123456” 表示将密码改为 123456

 

忘记密码的解决办法:跳过授权表启动mysql

方式一:

先停止mysql(管理员身份启动cmd):

C:\WINDOWS\system32>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。

再在cmd中跳过授权表启动mysql(管理员身份):

>mysqld --skip-grant-tables
2019-03-20 10:38:09 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-03-20 10:38:09 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2019-03-20 10:38:09 0 [Note] mysqld (mysqld 5.6.43) starting as process 26920 ...

再次使用客户端登录则不需要密码了。

再次修改密码:

>mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.43 MySQL Community Server (GPL)

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> select user();
+--------+
| user() |
+--------+
| root@ |
+--------+
1 row in set (0.00 sec)

mysql> update mysql.user set password=password("123456") where user="root" and host="localhost";   # 修改root密码为123456,如果设置空密码则引号内不填
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;   # 刷新授权表
Query OK, 0 rows affected (0.02 sec)

设置完成之后,停止当前mysql服务,再已正常模式启动:

>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

再次登录即使用新密码 123456 登录:

>mysql -uroot -p123456

 

方式二:

#1. 关闭mysql,可以用tskill mysqld将其杀死
#2. 在解压目录下,新建mysql配置文件my.ini
#3. my.ini内容,指定
[mysqld]
skip-grant-tables

#4.启动mysqld
#5.在cmd里直接输入mysql登录,然后操作
update mysql.user set authentication_string=password('') where user='root and host='localhost';

flush privileges;

#6.注释my.ini中的skip-grant-tables,然后启动myqsld,然后就可以以新密码登录了

 

 

Linux 平台(以CentOS为例)

CentOS7:

yum install mariadb-server mariadb

启动服务:systemctl start mariadb

停止服务:systemctl stop mariadb

查看服务状态:systemctl status mariadb

开启自启动:systemctl enalbe mariadb

 

CentOS6:

yum install mysql-server mysql

启动服务:/etc/init.d/mysqld start

停止服务:/etc/init.d/mysqld stop

查看服务状态:/etc/init.d/mysqld status

 

源码安装mysql5.6:

待补充.

 

初次设置密码:

[root@localhost ~]# mysqladmin -uroot -p password "123"
Enter password: 
[root@localhost ~]# 

修改密码:

[root@localhost ~]# mysqladmin -uroot -p123 password "456"

 

忘记密码的解决办法:

先停止mysql服务:

[root@localhost ~]# systemctl stop mariadb

再以跳过授权表的方式启动:

root@localhost ~]# mysqld_safe --skip-grant-tables
190320 10:54:27 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
190320 10:54:28 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

打开新终端即可免密码登录,进行密码修改:

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> update mysql.user set password=password("123456") where user="root" and host="localhost";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> exit
Bye

修改完成后,停止当前以跳过授权表形式启动的mysql,再以正常模式启动:

[root@localhost ~]# pkill -9 mysqld
[root@localhost ~]# systemctl start mariadb

再次登录即使用新密码 123456 登录:

[root@localhost ~]# mysql -uroot -p123456

 

 

发表评论

66 − = 65