centos7安装mysql
centos7 安装
-
官网下载mysql tar包
#查询 glibc 版本 ls -l /lib64/libc.so.6 or rpm -qa| grep glibc #下载对应版本 tar curl -o mysql8.0.23.tar.xz https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.23-linux-glibc2.17-x86_64-minimal.tar.xz #解压 重命名 xz -dk mysql8.0.23.tar.xz && mkdir ./mysql8.0.23 && tar -xvf mysql8.0.23.tar -C ./mysql8.0.23 --strip-components 1
-
安装mysql
#新增 mysql用户和用户组 groupadd mysql && useradd mysql -g mysql #设置密码 cbim@2022 passwd mysql #创建 目录 logs mkdir /opt/mysql8.0.23/log mkdir /opt/mysql8.0.23/tmp mkdir /opt/mysql8.0.23/binlog #设置环境变量 echo "export PATH=\$PATH:/opt/mysql8.0.23/bin">/etc/profile.d/mysql.sh source /etc/profile.d/mysql.sh
-
增加配置文件 再mysql目录下新建my.cnf文件并将下面得内容粘贴进去
[mysqld] basedir = /opt/mysql8.0.23 datadir = /opt/mysql8.0.23/data plugin_dir = /opt/mysql8.0.23/lib/plugin/ tmpdir=/opt/mysql8.0.23/tmp socket=/opt/mysql8.0.23/mysql.sock pid_file=/opt/mysql8.0.23/mysql.pid user=mysql port=3306 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION # 默认存储引擎 default_storage_engine=InnoDB lower_case_table_names=1 # innodb # innodb表空间路径,如果不设置默认为data目录 innodb_data_home_dir=/opt/mysql8.0.23/data #比如根据buffer_pool_chunk_size和buffer_pool_instances数量等来计算 innodb_buffer_pool_size=8G innodb_log_file_size=20M #内存日志缓冲区,在缓冲满/事务提交/1秒钟Innodb会刷写缓冲区内容到日志文件 innodb_log_buffer_size=8M innodb_buffer_pool_instances=16 # undolog # undolog的所在表空间目录,如果不设置,默认在datadir中,文件名为undo_001和undo_002,可在系统运行是动态创建 innodb_undo_directory=/opt/mysql8.0.23/undolog #分配给每个undo log表空间的回滚段数量,默认128,也是最大值 innodb_rollback_segments=128 # binlog #binlog路径 log_bin=/opt/mysql8.0.23/binlog/binlog #binlog index 路径 log_bin_index=/opt/mysql8.0.23/binlog.index ############## 慢日志 ############# #错误日志路径 log_error=/opt/mysql8.0.23/log/error.log #开启慢查询日志 slow_query_log=1 #慢查询日志路径 slow_query_log_file=/opt/mysql8.0.23/log/mysql-slow.log #慢查询阀值,默认10s long_query_time=5 #慢日志输出方式,默认FILE输出到文件,可以设置为TABLE写入到mysql.slow_log表,也可以设置FILE,TABLE log_output=FILE max_connections=2000 thread_cache_size=40 table_open_cache=4000 open_files_limit=100000 [client] port=3306 default-character-set=utf8mb4 socket = /opt/mysql8.0.23/mysql.sock
-
初始化数据库 并修改密码 启停
#新建文件 > /opt/mysql8.0.23/log/error.log > /opt/mysql8.0.23/log/mysql-slow.log #修改 目录权限 chown -R mysql:mysql /opt/mysql8.0.23/ #初始化 记录输出的密码(再mysql_errors.log文件中) /opt/mysql8.0.23/bin/mysqld --defaults-file=/opt/mysql8.0.23/my.cnf --user=mysql --initialize #启动 mysqld_safe --defaults-file=/opt/mysql8.0.23/my.cnf > /opt/mysql8.0.23/mysqld.log 2>&1 & #修改密码 mysqladmin -uroot -p'xxxx' -S /opt/mysql8.0.23/mysql.sock password '新密码' #登录 mysql -uroot -S/opt/mysql8.0.23/mysql.sock -p update user set host='%' where user='root'; flush privileges; #开放端口 firewall(防火墙) firewall-cmd --permanent --zone=public --add-port=3306/tcp firewall-cmd --reload #关闭mysql mysqladmin -uroot -p'xxxx' -S /opt/mysql8.0.23/mysql.sock shutdown
-
文件句柄修改
# 修改普通用户和root的nofile和nproc值 cat > /etc/security/limits.d/20-nproc.conf <<'EOF' # Default limit for number of user's processes to prevent # accidental fork bombs. # See rhbz #432903 for reasoning. #nproc * soft nproc 65535 root soft nproc unlimited * hard nproc 65535 root hard nproc unlimited #nofile * soft nofile 65535 * hard nofile 65535 EOF # 修改systemd系统级ulimit cat >> /etc/systemd/system.conf <<'EOF' DefaultLimitCORE=infinity DefaultLimitNOFILE=100000 DefaultLimitNPROC=100000 EOF # 修改systemd用户级ulimit cat >> /etc/systemd/user.conf <<'EOF' DefaultLimitCORE=infinity DefaultLimitNOFILE=100000 DefaultLimitNPROC=100000 EOF systemctl daemon-reload
-
vi 替换
:%s_前_后_g
-
创建用户
-- 创建用户 create user 'xxxx'@'%' identified with mysql_native_password by 'xxxxxx'; -- 更改密码 alter user 'xxxx'@'%' identified by 'xxxxxx; -- 授权 grant all privileges on *.* to 'xxxx'@'%'; -- 授权只读账号 grant select on *.* to 'xxxx'@'%'; flush privileges; -- 处理同步异常 STOP SLAVE; -- 跳过一个事物 SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 START SLAVE; SHOW SLAVE STATUS;
-
数据库迁移
-- 导出 CCTC@)2021.com! mysqldump -u xxxx -p 库名 > ssss.sql -- 导入 mysql --socket=/opt/mysql8.0.23/mysql.sock -uxxxx -p'密码' 库名 </tmp/ssss.sql
-
删除从库的具有写权限的用户并且修改root用户密码
-
删除用户
#查询现有用户 select user,host from mysql.user; #del 指定用户 drop user '用户名'@'主机名';
-
修改用户密码
Alter user 'xxxx'@'%' identified by '新密码';
-
导出单表备份
mysqldump --socket=/opt/mysql8.0.23/mysql.sock -uxxxx -p'密码' 库名 表名 | gzip > fbi_ext_received_record.sql.gz