服务器安装mysql
参考资料:
服务器安装mysql
--1. 如果没wget yum -y install wget --2. 下载 mysql-community-release-el7-5.noarch.rpm wget http://repo.mysql.com/mysql-community-release-el7.rpm --3. 通过rpm安装mysql rpm -ivh mysql-community-release-el7.rpm --4. 安装好之后,在继续通过yum软件包,出现的提示都按Y安装 yum install mysql-server --4.1 配置vim /etc/my.cnf --否则报错: --ERROR 2002 (HY000): Can't connect to local MySQL server through socket --具体见下文 --5. 安装好之后,确认一下是否安装好了。 rpm -qa|grep mysql --6. 启动mysql服务, 并查看状态 active(running) systemctl start mysqld.service systemctl status mysqld.service --7. 查看默认密码 grep 'temporary password' /var/log/mysqld.log --8. 运行mysql mysql -uroot -p --9. 修改密码,密码必须为强密码,否则会保错 --ERROR 1819 (HY000): Your password does not satisfy the current policy requirements --> https://blog.csdn.net/calistom/article/details/87939956 show databases; alter user 'root'@'localhost' identified by 'admin@123'; #xZg#jK49sIl. U #如果想密码设置简单点,先改成和原密码复杂度一样,再改密码规则,然后重新改密码 SHOW VARIABLES LIKE 'validate_password%'; set global validate_password.policy=0; set global validate_password.length=4; alter user 'root'@'localhost' identified by 'root'; 10. 刷新权限 FLUSH PRIVILEGES;
my.cnf配置
# 修改前 [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid # 修改后 [mysqld] # skip-grant-tables datadir=/var/lib/mysql/data basedir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock(跟这个socket路径一样) user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [client] port=3306 socket=/var/lib/mysql/mysql.sock
连接数据库
连接到本机上的MYSQL mysql -uroot -proot 连接到远程主机上的MySQL mysql -h127.0.0.1 -P3306 -uroot -p123
查用户权限
#查user表结构 desc mysql.user; #查所有用户 SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user; #查询某个用户的权限 show grants for 'dingding'@'%'; select * from mysql.user where user = 'dingding';
报错解决
1. 2003报错解决方案 #查看网络端口信息 - local address: 0.0.0.0:3306, programe name netstat -ntpl #查看防火墙的状态 - 如果3306是drop状态,或者无3306端口,说明3306端口设置问题 iptables -vnL 解决方法:启动服务,添加监听端口 service mysqld start; /sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT 拓展知识: service iptables stop #临时关闭防火墙 service iptables start #开启防火墙 chkconfig iptables off #开机不启动防火墙 2. 1130、1045报错解决方案 - 授权MySQL远程登陆 方法一:(推荐) mysql -uroot -proot GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'yourpassword' WITH GRANT OPTION; FLUSH PRIVILEGES; 方法二: mysql -uroot -proot use mysql; update user set host = '%' where user = 'root'; FLUSH PRIVILEGES; select host, user from user;
数据类型
除int外,还有TINYINT、SMALLINT、MEDIUMINT、BIGINT;
CHAR 和 VARCHAR 的区别: CHAR 的长度是固定的,而 VARCHAR 的长度是可以变化的;
ENUM和SET的区别: ENUM 类型的数据的值,必须是定义时枚举的值的其中之一,即单选,而 SET 类型的值则可以多选。
基本操作1
#新建数据库 CREATE DATABASE test; show databases; #新建表 use test; show tables; CREATE TABLE employee ( id int(10), name char(20), phone varchar(12) ); #插入数据 INSERT INTO employee(id,name,phone) VALUES(01,'Tom',110110110);
基本操作2
#新建数据库 CREATE DATABASE IF NOT EXISTS testdb DEFAULT CHARSET utf8 COLLATE utf8_general_ci; #查询、创建、删除用户 select user,host from mysql.user; create user 'test'@'localhost' identified by '1234'; --test改为‘%’表示在任何数据库都能登录 --drop user test@'localhost' ; --drop user test@'%' ; #修改用户密码 -- mysql_5.7版本以前 --方法1 set password for test =password('1122'); --方法2,需刷新权限 update mysql.user set password=password('1234') where user='test' flush privileges; --mysql_5.7以后 alter user 'test'@'localhost' identified by '1122'; #为用户分配权限 --1.查看用户权限 show grants for test; --2.mysql_5.7以前 --授予用户test通过外网IP对数据库“testdb”的全部权限 grant all privileges on 'testdb'.* to 'test'@'localhost' identified by '1234'; flush privileges; grant create,alter,drop,select,insert,update,delete on testdb.* to test@'localhost'; flush privileges; --3.mysql_5.7以后 --赋予用户test通过外网IP对数据库“testdb”的全部权限 grant all privileges on testdb.* to 'test'@'localhost'; flush privileges; grant create,alter,drop,select,insert,update,delete on testdb.* to 'test'@'localhost'; flush privileges; --4.删除用户权限 revoke all privileges on testdb.* from 'test'@'localhost'; --消除全部权限 revoke select on testdb.* from 'test'@'localhost'; --消除单个权限 #创建表空间 create tablespace tablespacename datafile 'd:\data.dbf' size 64m; --为用户分配默认表空间 alert user test default tablespace tablespacename;
参考链接:
mysql8.0创建数据库,用户的增删改查,用户分配权限,表空间分配