centos7下安装mysql

最新推荐文章于 2024-09-14 08:59:27 发布

小李飞刀8

最新推荐文章于 2024-09-14 08:59:27 发布

阅读量884

点赞数

分类专栏： mysql 数据库文章标签： centos mysql

本文链接：https://blog.youkuaiyun.com/brushli/article/details/38458561

版权

mysql 同时被 2 个专栏收录

16 篇文章

订阅专栏

数据库

10 篇文章

订阅专栏

要先安装centos7,镜像如下：

http://mirrors.163.com/centos/7.6.1810/isos/x86_64/CentOS-7-x86_64-DVD-1810.iso

1.下载MySQL源安装包

wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

2.安装MySql源

yum -y install mysql57-community-release-el7-11.noarch.rpm

3.查看一下安装效果

yum repolist enabled | grep mysql.*

4.使用yum命令安装mysql

yum install mysql-community-server

5.删除依赖

yum -y remove mariadb-libs

6.安装mysql

yum install mysql-community-server

7.启动mysql

mysql默认不支持root登陆，最好新建个账号登陆
[mysql@localhost ~]$ service mysqld status

8.查看mysql的版本

mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id:          5
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.73 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 28 min 51 sec

Threads: 3  Questions: 29  Slow queries: 0  Opens: 16  Flush tables: 1  Open tables: 9  Queries per second avg: 0.16

8.设置开机启动

[root@bogon ~]#  chkconfig mysqld on

9.启动MySQL服务

[root@bogon ~]#  service mysqld start

10.设置MySQL的root用户设置密码

[root@bogon ~]#  mysql -u root
mysql> select user,host,password from mysql.user;
+------+-----------+----------+
| user | host      | password |
+------+-----------+----------+
| root | localhost |          |
| root | bogon     |          |
| root | 127.0.0.1 |          |
|      | localhost |          |
|      | bogon     |          |
+------+-----------+----------+
5 rows in set (0.01 sec)

查询用户的密码，都为空，用下面的命令设置root的密码为root

mysql> set password for root@localhost=password('root');
mysql> exit

11.用新密码登陆

[root@bogon ~]#  mysql -u root -p
Enter password:

12.创建mysql新用户test_user

mysql> create user 'test_user'@'%' identified by 'test_user';
Query OK, 0 rows affected (0.00 sec)

13.给新用户test_user授权，让他可以从外部登陆和本地登陆
注意：@左边是用户名，右边是域名、IP和%，表示可以访问mysql的域名和IP，%表示外部任何地址都能访问。

mysql> grant all privileges on *.* to 'test_user'@'localhost' identified by 'test_user';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to 'test_user'@'%' identified by 'test_user';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from mysql.user;
+----------+-----------+-------------------------------------------+
| user     | host      | password                                  |
+----------+-----------+-------------------------------------------+
| root     | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root     | bogon     |                                           |
| root     | 127.0.0.1 |                                           |
|          | localhost |                                           |
|          | bogon     |                                           |
| test_user | %         | *3046CF87132BBD4FDDF06F321C6859074843B7D3 |
| test_user | localhost | *3046CF87132BBD4FDDF06F321C6859074843B7D3 |
+----------+-----------+-------------------------------------------+
7 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

14.查看mysql5.1的默认存储引擎
从下面的执行结果可以看出，mysql的默认引擎是MyISAM，这个引擎是不支持事务的。

mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)

也可以以下面的方式查看

mysql> show variables like 'storage_engine';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.00 sec)

15.修改mysql的默认引擎为InnoDB
15.1 停止mysql

mysql> exit;
[root@bogon ~]# service mysqld stop

15.2 修改/etc/my.cnf
[mysqld] 后加入

default-storage-engine=InnoDB

加入后my.cnf的内容为：

[root@bogon etc]# more my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

default-storage-engine=InnoDB

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

15.3 启动mysql

[root@bogon etc]# service mysqld start
Starting mysqld:  [  OK  ]

15.4 查看mysql默认存储引擎

[root@bogon etc]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, 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> show variables like 'storage_engine';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)

16.CentOS6.5开放mysql端口3306
CentOS6.5默认是不开放端口的，如果要让外部的系统访问CentOS6.5上的mysql，必须开放mysql的端口3306
16.1 修改/etc/sysconfig/iptables
添加下面一行

-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT

修改后iptables中的内容是

[root@bogon etc]# more /etc/sysconfig/iptables
# Firewall configuration written by system-config-firewall
# Manual customization of this file is not recommended.
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
#添加配置项
-A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 11211 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited

COMMIT

17.重启防火墙

(1)centos6.5

[root@bogon etc]# service iptables restart

这样就可以从外部访问mysql了。

(2)centos7 关闭防火墙

systemctl start firewalld # 启动,
systemctl enable firewalld # 开机启动
systemctl stop firewalld # 关闭
systemctl disable firewalld # 取消开机启动
service firewalld restart 重启

18、如果在无线的条件下要上网，可以使用NAT模式，并且在CENTOS中连接网络

19、输出binlog

修改文件/etc/my.cnf，添加以下内容

[root@localhost binlogs]# vi /etc/my.cnf

server_id=100

#设置日志格式

binlog_format=mixed
#设置日志路径，注意路经需要mysql用户有权限写

log-bin=/var/lib/mysql/binlogs/mysql-bin.log
#设置binlog清理时间

expire_logs_days=7
#binlog每个日志文件大小

max_binlog_size=100m
#binlog缓存大小

binlog_cache_size=4m
#最大binlog缓存大小

max_binlog_cache_size=512m

[root@localhost binlogs]# chgrp -R mysql /var/lib/mysql && chown -R mysql /var/lib/mysql

[root@localhost binlogs]# service mysqld restart

[root@localhost binlogs]# pwd
/var/lib/mysql/binlogs
[root@localhost binlogs]# ll
总用量 12
-rw-r-----. 1 mysql mysql 177 4月 24 00:54 mysql-bin.000001
-rw-r-----. 1 mysql mysql 1718 4月 24 00:57 mysql-bin.000002
-rw-r-----. 1 mysql mysql 80 4月 24 00:54 mysql-bin.index
-rw-r--r--. 1 mysql mysql 0 4月 24 00:52 mysql-bin.log