目录
文章目录
- 目录
- 1. 机器准备
- 2. 系统环境
- 3. 安装部署配置MySQL
- 3.1 软件安装约定
- 3.2 检查是否有安装mysql数据库(如果有mariadb数据库,则卸载)
- 3.3 下载MySQL安装包
- 3.4 解压安装包
- 3.5 安装
- 3.6 创建管理数据库的用户和组
- 3.7 创建数据库存放目录以及日志目录
- 3.8 修改整个mysql目录的权限
- 3.9 配置数据库环境变量
- 3.10 初始化数据库并记录下数据库的临时密码
- 3.11 修改mysql的主配置文件以及启动文件
- 3.12 启动mysql
- 3.13 登录mysql数据库
- 3.14 为root用户设置新密码
- 3.15 用新密码重新登录数据库
- 3.16 创建一个新用户并授权
- 3.16 在其他主机远程连接数据库
- 3.17 将mysql服务加入开机自启动
- 3.18 重启mysql服务
- 4. 拓展(主从配置)
1. 机器准备
#一台虚拟机
hostname:myhost
ip:ip_address
2. 系统环境
[root@myhost ~]# cat /etc/redhat-release
CentOS Linux release 7.2.1511 (Core)
[root@myhost ~]# firewall-cmd --state #查看防火墙的状态
running
[root@myhost ~]# firewall-cmd --list-ports #查看防火墙已经开放的端口
5666/tcp 10050/tcp
[root@myhost ~]# firewall-cmd --list-services #查看防火墙开放的服务
dhcpv6-client ssh
#假设设置mysql的服务端口为3306,允许3306/tcp端口访问
[root@myhost ~]# firewall-cmd --zone=public --permanent --add-port=3306/tcp
success
[root@myhost ~]# firewall-cmd --reload #重新加载防火墙配置
success
[root@myhost ~]# setenforce 0 && getenforce
Permissive
[root@myhost ~]# sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/sysconfig/selinux
[root@myhost ~]# cat /etc/sysconfig/selinux
3. 安装部署配置MySQL
3.1 软件安装约定
安装包存放目录:/home/tools
Mysql安装目录:/usr/local/mysql
数据库保存位置:/usr/local/mysql/data #需要自行建立,并修改属主和属组为mysql:mysql
日志保存位置:/usr/local/mysql/log #需要自行建立,并修改属主和属组为mysql:mysql
3.2 检查是否有安装mysql数据库(如果有mariadb数据库,则卸载)
[root@myhost ~]# rpm -qa |grep mysql
[root@myhost ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
[root@myhost ~]# cp /etc/my.cnf /etc/my.cnf_bak_mariadb
#卸载mariadb数据库
[root@myhost ~]# yum -y remove `rpm -qa | grep mariadb`
[root@myhost ~]# ll /etc/my.cnf
cat: /etc/my.cnf: 没有那个文件或目录
[root@myhost ~]# rpm -qa | grep mariadb
[root@myhost ~]# cp /etc/my.cnf_bak_mariadb /etc/my.cnf
[root@myhost ~]# mkdir /etc/my.cnf.d
3.3 下载MySQL安装包
- MySQL的官网下载地址:http://www.mysql.com/downloads
- MySQL的官网下载地址:https://dev.mysql.com/downloads/mysql
[root@myhost ~]# cd /home/tools/
[root@myhost tools]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
[root@myhost tools]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.34.tar.gz
[root@myhost tools]# ll
总用量 834868
-rw-r--r--. 1 root root 130274594 4月 21 01:15 boost_1_76_0.tar.gz
-rw-r--r--. 1 root root 665389778 3月 26 14:43 mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz ### 这个是不用编译的二进制安装包(相当于绿色版本)
-rw-r--r--. 1 root root 56150251 3月 26 14:09 mysql-5.7.34.tar.gz ###这个是需要编译的
drwxr-xr-x. 9 test 1001 4096 7月 13 14:20 nginx-1.16.1
-rw-r--r--. 1 root root 1032630 8月 14 2019 nginx-1.16.1.tar.gz
-rw-r--r--. 1 root root 2041593 4月 28 2015 pcre-8.37.tar.gz
3.4 解压安装包
这里我们选择不用编译的二进制安装包进行安装
[root@myhost tools]# tar -xf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
[root@myhost tools]# ll
总用量 834872
-rw-r--r--. 1 root root 130274594 4月 21 01:15 boost_1_76_0.tar.gz
drwxr-xr-x. 9 root root 4096 7月 13 17:33 mysql-5.7.34-linux-glibc2.12-x86_64
-rw-r--r--. 1 root root 665389778 3月 26 14:43 mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
-rw-r--r--. 1 root root 56150251 3月 26 14:09 mysql-5.7.34.tar.gz
drwxr-xr-x. 9 test 1001 4096 7月 13 14:20 nginx-1.16.1
-rw-r--r--. 1 root root 1032630 8月 14 2019 nginx-1.16.1.tar.gz
-rw-r--r--. 1 root root 2041593 4月 28 2015 pcre-8.37.tar.gz
3.5 安装
#移动已解压好的mysql软件到安装目录并重命名为mysql(省去做软连接)
[root@myhost tools]# mv mysql-5.7.34-linux-glibc2.12-x86_64 /usr/local/mysql
3.6 创建管理数据库的用户和组
[root@myhost local]# useradd -M -s /sbin/nologin mysql
[root@myhost local]# id mysql
uid=1005(mysql) gid=1005(mysql) 组=1005(mysql)
3.7 创建数据库存放目录以及日志目录
[root@myhost tools]# mkdir -p /usr/local/mysql/data
#[root@myhost tools]# mkdir -p /usr/local/mysql/data/temp #这里暂不创建,必须在初始化之后创建,否则报错
[root@myhost tools]# mkdir -p /usr/local/mysql/log
[root@myhost tools]# mkdir -p /usr/local/mysql/run
3.8 修改整个mysql目录的权限
[root@myhost tools]# chown -R mysql:mysql /usr/local/mysql/
3.9 配置数据库环境变量
[root@myhost tools]# vi /etc/profile 添加 export PATH=$PATH:/usr/local/mysql/bin
[root@myhost tools]# source /etc/profile
[root@myhost tools]# echo $PATH
3.10 初始化数据库并记录下数据库的临时密码
注意:如何同一台服务器重新安装mysql时,必须要把/etc/my.cnf文件内容恢复到之前,否则初始化会一直报错!!!!
[root@myhost tools]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
#临时密码:q6,NrQdKvsl;
[root@myhost tools]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data
3.11 修改mysql的主配置文件以及启动文件
[root@myhost tools]# cd /usr/local/mysql/support-files
[root@myhost support-files]# cp /etc/my.cnf /etc/my.cnf_bak #因为刚刚卸载了mariadb,可能/etc/my.cnf也一并被卸载了
[root@myhost support-files]# > /etc/my.cnf
[root@myhost support-files]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@myhost support-files]# vi /etc/my.cnf
###########################
[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock
###########################
[mysqld]
port = 3306
character-set-server = utf8
collation-server = utf8_general_ci
skip-external-locking
skip-name-resolve
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
tmpdir = /usr/local/mysql/data/temp #需要在数据库初始化之后自行创建并修改权限,否则会报错。(配置文件中不要有汉字,要去掉这部分汉字)
socket = /usr/local/mysql/mysql.sock
open_files_limit = 10240
back_log = 600
max_connections=2000
max_connect_errors = 6000
wait_timeout=605800
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 300
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M
query_cache_min_res_unit = 16k
tmp_table_size = 256M
max_heap_table_size = 256M
key_buffer_size = 256M
read_buffer_size = 8M
read_rnd_buffer_size = 64M
bulk_insert_buffer_size = 64M
lower_case_table_names=1
lower_case_table_names=1
default-storage-engine = INNODB
innodb_buffer_pool_size = 128MB
innodb_log_buffer_size = 32M
innodb_log_file_size = 128M
innodb_flush_method = O_DIRECT
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
explicit_defaults_for_timestamp=true
##########################
[mysqldump]
quick
max_allowed_packet = 32M
###########################
[mysqladmin]
host=127.0.0.1
user=zabbix
password=1234569101111
port=3306
##########################
[mysqld_safe]
log-error=/usr/local/mysql/log/mysqld_3306.err #需要在数据库初始化之后自行创建并修改权限,否则会报错。(配置文件中不要有汉字,要去掉这部分汉字)
pid-file=/usr/local/mysql/run/mysqld.pid
3.12 启动mysql
[root@myhost support-files]# mkdir -p /usr/local/mysql/data/temp
[root@myhost support-files]# touch /usr/local/mysql/log/mysqld_3306.err
[root@myhost support-files]# chown -R mysql:mysql /usr/local/mysql
[root@myhost support-files]# cd
[root@myhost ~]# /usr/local/mysql/bin/mysqld_safe --user=mysql &
[1] 31706
[root@myhost ~]# 2021-07-14T08:12:58.496429Z mysqld_safe Logging to '/usr/local/mysql/log/mysqld_3306.err'.
2021-07-14T08:12:58.534662Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
[root@myhost ~]# ps -ef |grep mysql
root 31706 30379 0 16:12 pts/1 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --user=mysql
mysql 32268 31706 0 16:12 pts/1 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/log/mysqld_3306.err --open-files-limit=10240 --pid-file=/usr/local/mysql/run/mysqld.pid --socket=/usr/local/mysql/mysql.sock --port=3306
root 32448 30379 0 16:14 pts/1 00:00:00 grep --color=auto mysql
[root@myhost ~]# netstat -lntup |grep mysql
tcp6 0 0 :::3306 :::* LISTEN 32268/mysqld
3.13 登录mysql数据库
[root@myhost ~]# mysql --user=root -p 或 mysql -uroot -p
Enter password: q6,NrQdKvsl; #输入之前初始化数据库时得到的临时密码
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.34
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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>
3.14 为root用户设置新密码
mysql> set password=password('新密码');
mysql> set authentication_string=password('新密码');
##### mysql的版本不一样,用户密码字段表示也不一样,这一点需要注意。
mysql> grant all privileges on *.* to root@'%' identified by '新密码';
mysql> flush privileges;
mysql> update mysql.user set authentication_string=password('新密码') where User='root' and Host='localhost'; #另外一种修改root用户密码的方法
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select host,user from mysql.user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | root |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
4 rows in set (0.00 sec)
mysql> show create database mysql;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| mysql | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table mysql.user;
mysql> exit;
3.15 用新密码重新登录数据库
[root@myhost ~]# mysql --user=root -p 或 mysql -uroot -p
Enter password: 新密码
3.16 创建一个新用户并授权
mysql> grant all privileges on *.* to other@'%' identified by 'other123456';
mysql> flush privileges;
mysql> use mysql;
mysql> select host,user from mysql.user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | other |
| % | root |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
5 rows in set (0.00 sec)
3.16 在其他主机远程连接数据库
[root@client ~]# mysql -P 3306 -h ip_address -u other -p
Enter password: other123456
3.17 将mysql服务加入开机自启动
[root@myhost ~]# chmod 755 /etc/init.d/mysqld
[root@myhost ~]# chkconfig --add mysqld
[root@myhost ~]# chkconfig --level 345 mysqld on
3.18 重启mysql服务
[root@myhost ~]# kill -9 `ps -ef |egrep 'mysql'|egrep -v 'grep'|awk '{print$2}'|xargs`
[root@myhost ~]# /etc/init.d/mysqld start
[root@myhost ~]# ps -ef |grep mysql
[root@myhost ~]# netstat -lntup |grep mysql
[root@myhost ~]# lsof -i:3306
4. 拓展(主从配置)
######replication-master###########
server-id=22
log-bin=/usr/local/mysql/log/mysql-bin
relay_log=master-relay-bin
binlog-do-db=xxx #需要同步的数据库名
binlog-ignore-db=mysql #不需要同步的数据库名
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062
auto-increment-increment = 3
auto-increment-offset = 1
#skip-name-resolve
###### replication-slave ###########
server_id=23
log-bin=/usr/local/mysql/log/mysql-slave-bin
relay_log=/usr/local/mysql/log/slave-relay-bin
binlog-do-db=xxx #需要同步的数据库名
binlog-ignore-db=mysql #不需要同步的数据库名
binlog_cache_size = 1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062
read_only=0
log_slave_updates=1 #从库记录binlog,这里推荐开启