Mysql学习第一篇
MySQL 安装
1、Linux/UNIX 上安装 MySQL
Linux平台上推荐使用RPM包来安装Mysql,MySQL AB提供了以下RPM包的下载地址：
- MySQL - MySQL服务器。你需要该选项，除非你只想连接运行在另一台机器上的MySQL服务器。
- MySQL-client - MySQL 客户端程序，用于连接并操作Mysql服务器。
- MySQL-devel - 库和包含文件，如果你想要编译其它MySQL客户端，例如Perl模块，则需要安装该RPM包。
- MySQL-shared - 该软件包包含某些语言和应用程序需要动态装载的共享库(libmysqlclient.so*)，使用MySQL。
- MySQL-bench - MySQL数据库服务器的基准和性能测试工具。
2、解压之后，安装各软件包
[root@localhost software]# yum remove mariadb-libs-1:5.5.52-1.el7.x86_64 [root@localhost software]# rpm -ivh mysql-community-common-5.7* warning: mysql-community-common-5.7.26-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql-community-common-5.7.26-1.e################################# [100%] [root@localhost software]# rpm -ivh mysql-community-libs-5.7* warning: mysql-community-libs-5.7.26-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql-community-libs-5.7.26-1.el7################################# [100%] [root@localhost software]# [root@localhost software]# [root@localhost software]# rpm -ivh mysql-community-client-5.7* warning: mysql-community-client-5.7.26-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql-community-client-5.7.26-1.e################################# [100%] [root@localhost software]# [root@localhost software]# [root@localhost software]# rpm -ivh mysql-community-server-5.7* warning: mysql-community-server-5.7.26-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql-community-server-5.7.26-1.e################################# [100%]
3、启动mysql、查看mysql状态
[root@localhost software]# systemctl start mysqld t@localhost software]# [root@localhost software]# systemctl status mysqld ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: active (running) since Sat 2019-04-27 14:10:56 CST; 1min 1s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 5947 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS) Process: 5869 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 5950 (mysqld) CGroup: /system.slice/mysqld.service └─5950 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid Apr 27 14:10:32 localhost.localdomain systemd[1]: Starting MySQL Server... Apr 27 14:10:56 localhost.localdomain systemd[1]: Started MySQL Server.
4、登录到数据库，初始登录时采用临时密码，临时密码在mysql.log文件里
root@mysql2 log]# grep 'password' mysqld.log
2019-04-30T03:10:37.233845Z 1 [Note] A temporary password is generated for root@localhost: axhUe,y:x4ot
2019-04-30T03:11:05.579516Z 2 [Note] Access denied for user 'root'@'localhost' (using password: NO)
[root@mysql2 log]# pwd
/var/log
[root@mysql2 log]#
[root@localhost log]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.26 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>
5、修改root密码
[root@mysql1 ~]# systemctl start mysqld [root@mysql1 ~]# [root@mysql1 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.26 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> flush privileges; Query OK, 0 rows affected (0.03 sec) mysql> alter user root@localhost identified by 'mysql'; --注意这里的localhost不是主机名的意思 Query OK, 0 rows affected (0.00 sec) mysql>
mysql> alter user 'root'@'%' identified by 'Mysql123@';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%' --root用户不能修改为远程登录么
mysql>
6、查询数据库以及相应的表
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.05 sec) --默认四个库 mysql> mysql> mysql> mysql> use information_schema; 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>
7、创建数据库以及创建表
mysql> CREATE DATABASE IF NOT EXISTS zxjt DEFAULT CHARSET utf8 COLLATE utf8_general_ci; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | zxjt | +--------------------+ 5 rows in set (0.00 sec) mysql> use zxjt; Database changed mysql> mysql> mysql> mysql> mysql> mysql> CREATE TABLE IF NOT EXISTS `runoob_tbl`( -> `runoob_id` INT UNSIGNED AUTO_INCREMENT, -> `runoob_title` VARCHAR(100) NOT NULL, -> `runoob_author` VARCHAR(40) NOT NULL, -> `submission_date` DATE, -> PRIMARY KEY ( `runoob_id` ) -> )ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.07 sec) mysql> show tables; +----------------+ | Tables_in_zxjt | +----------------+ | runoob_tbl | +----------------+ 1 row in set (0.00 sec) mysql>
8、新创建的用户创建库
create user 'dayu2'@'localhost' identified by '123456'; create user 'dayu2'@'%' identified by '123456'; create database dayudb DEFAULT CHARSET utf8 COLLATE utf8_general_ci; grant all privileges on `dayudb`.* to 'dayu2'@'%' identified by '123456'; grant all privileges on `dayudb`.* to 'dayu2'@'localhost' identified by '123456'; mysql -u test -h 115.28.203.224 -p
