inux centos7 yum安装mysql
https://blog.csdn.net/weixin_35688029/article/details/90401900
CentOS7安装unzip
yum install -y unzip zip
安装wget 及配置
wget是一个从网络上自动下载文件的自由工具。它支持HTTP,HTTPS和FTP协议,可以使用HTTP代理.
yum -y install wget
yum -y install setup
yum -y install perl
1. 查看linux发行版本 : cat /etc/redhat-release
2. 下载MySQL官方的Yum Repository :wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
3. 完成后可以检测下md5是否与页面上的一致: md5sum mysql57-community-release-el7-11.noarch.rpm
4. 安装MySQL的Yum Repository :rpm -ivh mysql57-community-release-el7-11.noarch.rpm
5. 安装MySQL数据库的服务器版本 : yum -y install mysql-server
6. 启动数据库 systemctl start mysqld
MySQL数据库的其他常用命令
systemctl start mysqld #启动MySQL
systemctl stop mysqld #关闭MySQL
systemctl restart mysqld #重启MySQL
systemctl status mysqld #查看MySQL运行状态
systemctl enable mysqld #设置开机启动 (systemctl daemon-reload) 重新加载配置
systemctl disable mysqld #关闭开机启动
7. 获取初始密码 : grep "password" /var/log/mysqld.log
8. 修改/etc/my.cnf 配置 加入
validate_password = off #设置禁用密码策略
character_set_server = utf8 #修改字符编码为utf8
init_connect = 'SET NAMES utf8'
max_connect_errors = 1000
保存并重启 systemctl restart mysqld
9. 登陆mysql : mysql -uroot -p
10. 修改密码 : ALTER USER 'root'@'localhost' IDENTIFIED BY 'temple123456';
11. 添加远程连接 :
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
FLUSH PRIVILEGES;是配置生效
12.开启binlog
log-bin = mysql-bin #开启binlog
binlog-format = ROW #选择row模式
server_id = 1 #配置mysql replication需要定义,不能和canal的slaveId重复
13.设置sql_model
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
/etc/my.cnf图
mysql 命令操作数据库:
mysql -uroot -p
1、显示数据库列表。
show databases;
2、显示库中的数据表:
use mysql; //打开库
show tables;
3、显示数据表的结构:
describe 表名;
4、建库:
create database 库名;
GBK: create database test2 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
UTF8: CREATE DATABASE `test2` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
5、建表:
use 库名;
create table 表名(字段设定列表);
6、删库和删表:
drop database 库名;
drop table 表名;
7、将表中记录清空:
delete from 表名;
truncate table 表名;
8、显示表中的记录:
select * from 表名;
9、编码的修改
如果要改变整个mysql的编码格式:
启动mysql的时候,mysqld_safe命令行加入
--default-character-set=gbk
如果要改变某个库的编码格式:在mysql提示符后输入命令
alter database db_name default character set gbk;
10.重命名表
alter table t1 rename t2;
11.查看sql语句的效率
explain < table_name >
例如:explain select * from t3 where id=3952602;
12.用文本方式将数据装入数据库表中(例如D:/mysql.txt)
mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE MYTABLE;
导入表:
mysql>source news.sql;
13. 查看表的所有字段类型
show full columns from tillo_user;
14. 查看mysql连接超时参数
show global variables like "wait_timeout";
show global variables like "interactive_timeout";
set global wait_timeout=28800;
set global interactive_timeout=28800;
添加用户,新建数据库,用户授权,删除用户,修改密码
https://blog.csdn.net/adu198888/article/details/54092857
1、新建用户
CREATE USER “test”@”localhost” IDENTIFIED BY “1234”; #本地登录
CREATE USER “test”@”%” IDENTIFIED BY “1234”; #远程登录
quit
mysql -utest -p #测试是否创建成功
2、为用户授权
a.授权格式:grant 权限 on 数据库.* to 用户名@登录主机 identified by “密码”;
b.登录MYSQL,这里以ROOT身份登录:
c.为用户创建一个数据库(testDB):
create database testDB;
create database testDB default charset utf8 collate utf8_general_ci;
d.授权test用户拥有testDB数据库的所有权限:
grant all privileges on testDB.* to "test"@"localhost" identified by "1234";
flush privileges; #刷新系统权限表
e.指定部分权限给用户:
grant select,update on testDB.* to "test"@"localhost" identified by "1234";
grant select,update on testDB.* to "test"@"%" identified by "1234";
flush privileges; #刷新系统权限表
f.授权test用户拥有所有数据库的某些权限:
grant select,delete,update,create,drop on . to test@"%" identified by "1234"; #"%" 表示对所有非本地主机授权,不包括localhost
3、删除用户
mysql -u root -p
Delete FROM mysql.user Where User=”test” and Host=”localhost”;
flush privileges;
删除账户及权限:
drop user 用户名@’%’;
drop user 用户名@ localhost;
查看用户权限:
show grants for test;
赋予用户权限:
grant select,update,delete,insert on testDB.* to test;
flush privileges;
4、修改指定用户密码
mysql -u root -p
update mysql.user set authentication_string=password(“新密码”) where User=”test” and Host=”localhost”;
flush privileges;
4、修改远程连接为本地连接
update user set Host = "127.0.0.1" where User = "root" and Host= "%";
flush privileges;