centos7安装mysql教程_CentOS7位安装MySql教程

yum -y install httpd mariadb-server mariadb php php-mysql

[root@xuegod63 ~]# systemctl start mariadb

[root@xuegod63 ~]# mysql_secure_installation

[root@xuegod63 ~]# vim /etc/php.ini #php运用程序的配置文件

改：211 short_open_tag = Off

为：211 short_open_tag = On

show databases;

use mysql;

show tables;

selectdatabase();#查看所处的位置

mysql -e 'show databases' -uroot -p123456

select now(),user(),database();

删库

MariaDB [xuegod]> drop database `HA-test`;

直接到数据库存放目录移出就行

MariaDB [HA]> create database `HA-test1`;

[root@xuegod63 mysql]# mv /var/lib/mysql/HA@002dtest1 /opt/show databases;

drop database if exists `HA-test`; #IF EXISTS：如果存在

创建表：

MariaDB [xuegod]>create database xuegod;

MariaDB [xuegod]>use xuegod;

MariaDB [xuegod]> create table student(id int(20),name char(40),age int);

查看表结构

MariaDB [xuegod]> desc student;

(4)查看创建表执行了哪些命令：

MariaDB [xuegod]>show create table student \G

MariaDB [xuegod]> create table student2(id int(20),name char(40),age int)ENGINE=MyISAM DEFAULT CHARSET=utf8;

#可以指定表的默认存储引擎和字符集

alter table student rename students; #修改表名

alter table students modify id int(10);#修改字段类型

alter table students change name stname char(20);#修改名字和字段

alter table students add sex enum('M','W');#添加字段

alter table students add uid int(10) first;#添加到第一列

alter table students add address char(40) after age;#添加到age字段后面

alter table students drop address; #删除某address字段

create table student(id int(20),name char(40),age int);

insert into student values(2,'lis',24),(3,'wange',26);

select * fromstudent;

select * fromstudent\G #字段按行显示

select *fromHA.student;#跨库查询

delete from students where id=3;#删除数据

delete from students where age is null; 删除adge为空的内容

update students set sex='M' where id=2;更新

select distinct name,age fromstudent;#去重查询

select id,name,age from student where id>3 and age>25;#多条件查询

select * from students where stname='zhangsan' and (age=21 or age=24);

select * from student where binary name='jk';#区分大小写查询 默认不区分

select distinct id fromstudent order by id asc;#升序

select distinct id fromstudent order by id desc;#降序

help show;

mysqldump -uroot -p123456 -B 迁移的库名 > 导出名称.sql

yum 安装mysql5.7的方法：

yum -y remove mariadb*rpm -Uvh https://repo.mysql.com//yum/mysql-5.7-community/el/7/x86_64/mysql57-community-release-el7-10.noarch.rpm

yum list #刷新yum源缓存

yum -y install mysql-community-server #安装mysql5.7systemctl start mysqld #启动mysql会生成临时密码

grep "password" /var/log/mysqld.log 命令获取MySQL的临时密码

mysql -uroot -p'&B=o4Fp!Rq_X'

方法一：首先，修改validate_password_policy参数的值

MariaDB [xuegod]> set global validate_password_policy=0; #定义复杂度

MariaDB [xuegod]> set global validate_password_length=1; #定义长度 默认是8

MariaDB [xuegod]> set password for 'root'@'localhost'=password('123456');

MariaDB [xuegod]> flush privileges;

方法二：在/etc/my.cnf 可关闭密码强度审计插件，重启mysql服务

validate-password=ON/OFF/FORCE/FORCE_PLUS_PERMANENT#(及强制/永久强制使用)。

mysql -uroot -p123456 < 导出名称.sql

字符集的查询和修改

show variables like 'character_set%';#当前字符集

show create table user \G #查看表的字符集

SHOW CREATE DATABASE mysql;#查看库的字符集

SHOW FULL COLUMNS FROM user;#查看字段编码

ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];

ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;

select * from user\G

数据库导入导出

mysqldump -uroot -p123456 -A >all.sql

mysqldump -uroot -p123456 --all-databases >all2.sql

A：导入所有数据库

mysql -uroot -p123456

强制修改密码5.7 mysql

5.7版本强制修改密码

vi /etc/my.cnf

[mysqld]

skip-grant-tables

执行修改命令 5.7以前用 update userset password=password(“新密码”) where user=”用户名”;

update user set authentication_string=password('123456') where User='root';

无法建表sbtest1s删除表空间就可以了

[root@mysql115 ~]# ll /var/lib/mysql/pcms

总用量 36

-rw-r----- 1 mysql mysql 61 8月 17 09:02db.opt

-rw-r----- 1 mysql mysql 65536 8月 17 09:09sbtest1.ibd

[root@mysql115 ~]# rm /var/lib/mysql/pcms/sbtest1.ibd

rm：是否删除普通文件 "/var/lib/mysql/pcms/sbtest1.ibd"？y