CentOS7位安装MySql教程

1.先检查系统是否装有mysql

rpm -qa | grep mysql

2.下载mysql的repo源

wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

 

3.安装 mysql-community-release-el7-5.noarch.rpm包

 

sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm

 

4.安装MySQL

 

sudo yum install mysql-server

 

5.重置MySQL密码

 

mysql -u root

 

如果报错：

 

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

 

原因：原因是/var/lib/mysql的访问权限问题。

 

chown root /var/lib/mysql/

 

重启MySQL服务

 

service mysqld restart

 

接着登陆设置密码

 

mysql -u root

 

use mysql;

 

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

 

exit;

 

6.重启MySQL服务

 

service mysqld restart

 

接着设置Root账户远程连接密码，账户和密码都是 root

 

 mysql -u root -p

 

GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "root";　

 

重启服务器 

 

service mysqld restart

 

7.使用外网工具连接MySQL

 

连接mysql的用户名和密码都是 root

 

关闭防火墙

 

systemctl stop firewalld.service

 

 

 

 

下面为另一种办法，但是，测试没有成功

连接MySQL服务器，修改密码

 

1）查看初始密码

 

  1. [root@host50 ~]#grep –i 'password' /var/log/mysqld.log
  2. 2017-04-01T18:10:42.948679Z 1 [Note] A temporary password is generated for root@localhost: mtoa>Av<p6Yk //随机生成的管理密码为mtoa>Av<p6Yk

 

2）使用初始密码连接mysql服务

 

  1. [root@host50 ~]# mysql -u root -p'mtoa>Av<p6Yk' //初始密码登录，
  2. mysql: [Warning] Using a password on the command line interface can be insecure.
  3. Welcome to the MySQL monitor. Commands end with ; or \g.
  4. Your MySQL connection id is 11
  5. Server version: 5.7.17
  7. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  14. mysql>                                     //登录成功后，进入SQL操作环境

 

3）重置数据库管理员roo本机登录密码

 

  1. mysql> show databases;
  2. ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement //提示必须修改密码
  3. mysql> alter user root@”localhost” identified by "123qqq…A"; //修改登陆密码
  4. Query OK, 0 rows affected (0.00 sec)
  5. mysql> exit //断开连接
  6. [root@host50 ~]#

 

4）修改密码策略

 

  1. [root@host50 ~]# mysql -uroot –p123qqq…A
  2. mysql>
  3. mysql>set global validate_password_policy=0; //只验证长度
  4. Query OK, 0 rows affected (0.00 sec)
  5. mysql>set global validate_password_length=6； //修改密码长度,默认值是8个字符
  6. Query OK, 0 rows affected (0.00 sec)
  7. mysql> alter user root@”localhost” identified by "tarena"; //修改登陆密码
  8. Query OK, 0 rows affected (0.00 sec)
  9. mysql>exit

 

5）使用修改后的密码登录

 

  1. [root@host50 ~]# mysql -uroot -ptarena        //登录
  2. Welcome to the MySQL monitor. Commands end with ; or \g.
  3. Your MySQL connection id is 15
  4. Server version: 5.7.17 MySQL Community Server (GPL)
  6. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
  8. Oracle is a registered trademark of Oracle Corporation and/or its
  9. affiliates. Other names may be trademarks of their respective
  10. owners.
  11. mysql> show databases; //查看数据库
  12. +--------------------+
  13. | Database |
  14. +--------------------+
  15. | information_schema |
  16. | mysql |
  17. | performance_schema |
  18. | sys                |
  19. +--------------------+
  20. 4 rows in set (0.00 sec)
  21. mysql>
  29. 张少龙博客

    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;
select database();#查看所处的位置
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 * from student;
select * from student\G #字段按行显示
select *from HA.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 from student;#去重查询
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 from student order by id asc;#升序
select distinct id from student 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.7
systemctl 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 <all.sql

     强制修改密码5.7 mysql

    复制代码
    复制代码
    5.7 版本强制修改密码
vi /etc/my.cnf
[mysqld]
skip-grant-tables

    执行修改命令 5.7以前用 update user set 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:02 db.opt
-rw-r----- 1 mysql mysql 65536 8月  17 09:09 sbtest1.ibd
[root@mysql115 ~]# rm /var/lib/mysql/pcms/sbtest1.ibd
rm：是否删除普通文件 "/var/lib/mysql/pcms/sbtest1.ibd"？y
    复制代码

     

posted @ 2019-10-11 20:23  虞岩  阅读(2091)  评论(0)  编辑  收藏  举报