部署mysql
安装前准备
操作系统信息检查 [ ~]# cat /etc/redhat-release
主机版本检查 [ ~]# uname -a
存储检查 [ ~]# df -hT
内存信息[~]# free -m
cpu信息[~]# lscpu
网卡信息检查 [ ~]# ifconfig -a
磁盘信息检查fdisk -l
资源限制
#!/bin/bash #optimization of mysql host system parameter echo "kernel.sem = 250 32000 100 128" >> /etc/sysctl.conf echo "fs.file-max = 65536" >> /etc/sysctl.conf echo "net.core.netdev_max_backlog = 32768" >> /etc/sysctl.conf echo "net.core.rmem_default = 8388608" >> /etc/sysctl.conf echo "net.core.rmem_max = 16777216" >> /etc/sysctl.conf echo "net.core.somaxconn = 32768" >> /etc/sysctl.conf echo "net.core.wmem_default = 8388608" >> /etc/sysctl.conf echo "net.core.wmem_max = 212992" >> /etc/sysctl.conf echo "net.ipv4.ip_local_port_range = 10000 65000" >> /etc/sysctl.conf echo "net.ipv4.tcp_fin_timeout = 30" >> /etc/sysctl.conf echo "net.ipv4.tcp_keepalive_intvl = 15" >> /etc/sysctl.conf echo "net.ipv4.tcp_keepalive_probes = 3" >> /etc/sysctl.conf echo "net.ipv4.tcp_keepalive_time = 1200" >> /etc/sysctl.conf echo "net.ipv4.tcp_max_orphans = 3276800" >> /etc/sysctl.conf echo "net.ipv4.tcp_max_syn_backlog = 65536" >> /etc/sysctl.conf echo "net.ipv4.tcp_max_tw_buckets = 6000" >> /etc/sysctl.conf echo "net.ipv4.tcp_mem = 94500000 91500000 92700000" >> /etc/sysctl.conf echo "net.ipv4.tcp_retries2 = 5" >> /etc/sysctl.conf echo "net.ipv4.tcp_rmem = 32768 436600 873200" >> /etc/sysctl.conf echo "net.ipv4.tcp_syn_retries = 2" >> /etc/sysctl.conf echo "net.ipv4.tcp_synack_retries = 2" >> /etc/sysctl.conf echo "net.ipv4.tcp_syncookies = 1" >> /etc/sysctl.conf echo "net.ipv4.tcp_tw_recycle = 1" >> /etc/sysctl.conf echo "net.ipv4.tcp_tw_reuse = 1" >> /etc/sysctl.conf echo "net.ipv4.tcp_wmem = 8192 436600 873200" >> /etc/sysctl.conf echo "vm.dirty_background_ratio = 50" >> /etc/sysctl.conf echo "vm.dirty_ratio = 50" >> /etc/sysctl.conf echo "vm.dirty_writeback_centisecs = 360000" >> /etc/sysctl.conf echo "vm.drop_caches = 1" >> /etc/sysctl.conf echo "vm.max_map_count = 655360" >> /etc/sysctl.conf echo "vm.min_free_kbytes = 4096000" >> /etc/sysctl.conf echo "vm.overcommit_memory = 1" >> /etc/sysctl.conf echo "vm.swappiness = 5" >> /etc/sysctl.conf
sysctl –p
一、首先卸载服务器上自带的MariaDB或mysql数据库 1、卸载rpm包 1.1、查看mysql相关包：
>> rpm -qa|grep mysql
>> rpm -qa| grep maria
1.2卸载mysql的rpm包：
>> rpm -e --nodeps xxxx(上一步查出来的rpm包)
2、删除相关目录 2.1、查看mysql命名的目录文件：
>> find / -name "mysql"
2.2、删除相关目录：
rm -rf xxx(上一步查出来的目录，注意别删错了，比如解压的安装包名也带有mysql，可用 grep反向过滤) 主要为以下目录：
/var/share
/usr/local/lib
/usr/local/lib64
/usr/bin等
1.官网安装
下载 rpm包：https://dev.mysql.com/downloads/
yum install -y mysql80-community-release-el7-7.noarch.rpm
mysql-community-source.repo 源代码包
mysql-community.repo 主包
修改主包为需要的版本
vim /etc/yum.repos.d/mysql-community.repo
yum makecache
再次查看mysql-community.-server 是否为 5.7
yum list | grep mysql
yum install -y mysql-community*
systemctl start mysqld
grep 'password' /var/log/mysqld.log
mysql -uroot -p
mysqladmin -uroot -p'#TdA7%94DMfR' password 'Admin@123456'
2.RPM国内镜像安装
需要2G内存、关闭防火墙和selinux、
1.访问国内镜像站 下载
2.下载
mkdir -p /app/mysql
cd /app/mysql
wget https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/mysql-community-server-5.7.30-1.el7.x86_64.rpm
wget https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/mysql-community-client-5.7.30-1.el7.x86_64.rpm
wget https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/mysql-community-common-5.7.30-1.el7.x86_64.rpm
wget https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/mysql-community-libs-5.7.30-1.el7.x86_64.rpm
3.安装相关软件
yum install -y net-tools.x86_64 libaio.x86_64 perl.x86_64
4.安装mysql服务器
yum install -y mysql-community*
报错信息：
yum remove -y mariadb-libs.x86_64
5.启动mysql
systemctl start mysqld
systemctl enable mysqld
systemctl status mysqld
6.查询默认密码
grep 'password' /var/log/mysqld.log
7.修改默认密码
mysqladmin -uroot -p '默认密码 ' password '新密码'
8.登录mysql
mysql -uroot -p 'password'
9.查看默认数据库
show databases;
10.关机快照
3.源码包*
1.准备编译环境
yum install -y ncurses ncurses-devel openssl openssl-devel bison gcc gcc-c++ make cmake
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-5.7.30-1.el7.src.rpm
获取源码
-如果下载的是压缩包，直接上传服务器解压即可。
-但如果是src.rpm格式的文件，想要一个额外步骤。同样上传服务器，运行以下命令,
>> yum install -y rpm2cpio
>> rpm2cpio Mysql-xxx-xxx.src.rpm | cpio -idmv
解压：
>> tar vxf mysql-xxx-xxx.tar.gz 得到mysql源码文件
>> tar vxf boost-xxx-xxx.tar.gz 得到boost源码文件
wget https://sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz/download
2.安装mysql
2.1准备源码包
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
mkdir /usr/local/mysql
mkdir /usr/local/mysql/data
mkdir -p /var/run/mysql
mkdir /var/log/mysql
chown -R mysql:mysql /var/run/mysql
chown -R mysql:mysql /var/log/mysql
chown -R mysql:mysql /usr/local/mysql
chown -R mysql:mysql /usr/local/mysql/data
tar xvf mysql-5.7-30.tar.gz
tar xvf boost_1_59_0.tar.gz
cd mysql-5.7.30
2.2配置
cmake . -DWITH_BOOST=/root/boost_1_59_0 -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc -DMYSQL_DATADIR=/usr/local/mysql/data -DINSTALL_MANDIR=/usr/share/man -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSETS=utf8 -DEXTRA_CHARSETS=all -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1
-DDEFAULT_COLLATION=utf8_general_ci
3.编译
make
make install
/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
/usr/local/mysql/bin/mysqld_ssl_rsa_setup --datadir= /usr/local/mysql/data
建立mysql配置文件 my.cnf
vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
[mysqld_safe]
log-error=/var/log/mysql/mysql.log
pid-file=/var/run/mysql/mysql.pid
ls support-files/
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld #添加mysql服务
chkconfig mysqld on #开机启动
systemctl status mysqld
systemctl start mysqld
systemctl status mysqld
bin/mysql -uroot -p #登陆成功
# 将mysql命令添加到环境变量中
echo 'export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/sbin' >> /etc/profile
source /etc/profile
登录数据库修改密码
>> mysql -uroot -p
> (这里输入刚刚initialize时保存的密码)
如果出现报错：ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/xxx/xxx/mysql.sock' (2)
则说明：
1、mysql.sock文件上级目路权限不对
2、mysql.sock文件上级目录不存在
3、编译时指定的socket文件路径和配置文件中指定的路径不同
这里进入mysql交互界面====================>>>
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Admin@123'; #修改数据库登录密码
SET PASSWORD=PASSWORD('Admin@123');
注意：这里密码不能太短，否则修改不成功，需要超过8位即可。
如果后面想设置简单点的密码，需要修改两个参数：validate_password_policy和validate_password_length。
但是刚安装可能没有加载validate_password 插件，修改时会提示：
ERROR 1193 (HY000): Unknown system variable 'validate_password_policy'
安装及设置：
mysql> install plugin validate_password soname 'validate_password.so';
Query OK, 0 rows affected (0.02 sec)
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=你自己想要的长度;
Query OK, 0 rows affected (0.00 sec)
自建YUM源
服务端
1.安装ftp
yum install -y vsftpd
2.准备ftp主目录
mkdir /var/ftp/mysql57/
3.官网下载yum仓库的包
yum install -y mysql57-community-releaase-el7-11.noarch.rpm
ls /etc/yum.repos.d/
yum list | grep mysql
4.仅下载安装mysql
yum remove -y mariadb-libs.x86_64
yum install -y mysql-community-server --downloadonly
5.查找下载的mysql安装包
find /var/cache/yum/x86_64/7/ -iname "*.rpm" -exec cp -rf {} /var/ftp/mysql57 ;
6.创建依赖关系
cd /var/ftp/mysql57/
yum provides createrepo
yum install -y createrepo
createrepo /var/ftp/mysql57 #解决依赖关系
systemctl start vsftpd
客户端
1.自建yum仓库
vim /etc/yum.repos.d/mysql57.repo
[mysql57]
name=mysql-ftp
baseurl=ftp://192.168.88.10
gpgcheck=0
enabled=1
2.安装
yum makecache
yum install -y mysql-community-server
3.启动MySQL
systemctl start mysqld
systemctl status mysqld
netstat -anpt | grep 3306
grep password /var/log/mysqld.log
3.登陆数据库 更改密码
mysql -uroot -p
SET PASSWORD=PASSWORD('Admin@123');
