MySQL部署, 以及常见问题梳理

发表于 更新于 分类于 本文字数： 1.3k 阅读时长 ≈ 5 分钟
  1. 下载

https://downloads.mysql.com/archives/community/
选择 mysql-5.7.37-1.el7.x86_64.rpm-bundle.tar

  1. 包文件详解
mysql-community-client：MySQL客户端应用程序和工具 
mysql-community-common：服务器和客户端库的通用文件 
mysql-community-devel：用于MySQL数据库客户端应用程序的开发头文件和库 
mysql-community-embedded：MySQL嵌入式库 
mysql-community-embedded-compat：MySQL服务器作为嵌入式库，与使用库版本18的应用程序兼容 
mysql-community-embedded-devel：MySQL的开发标头文件和库作为可嵌入库 
mysql-community-libs：MySQL数据库客户端应用程序的共享库 
mysql-community-libs-compat：以前的MySQL安装的共享兼容性库 
mysql-community-server：数据库服务器和相关工具 
mysql-community-test：MySQL服务器的测试套件
  1. rpm 安装顺序

common--->libs--->client--->server--->libs-compat--->devel

rpm -ivh mysql-community-common-5.7.37-1.el7.x86_64.rpm \
> mysql-community-libs-5.7.37-1.el7.x86_64.rpm \
> mysql-community-server-5.7.37-1.el7.x86_64.rpm \
> mysql-community-client-5.7.37-1.el7.x86_64.rpm \
## (上面四个必装)
> mysql-community-libs-compat-5.7.37-1.el7.x86_64.rpm \
> mysql-community-devel-5.7.37-1.el7.x86_64.rpm

3) 重载systemctl
systemctl daemon-reload

  1. 启动 MySQL

systemctl start mysqld

  1. 进入mysql

mysql -u root -p
Ubuntu需要使用sudo
部分情况需要 -h 127.0.0.1
没有设置密码直接enter即可进入mysql
部分文档提到, yum或者rpm安装会在 /var/log/mysqld.log里生成密码, 目前没有碰到过, 估计是老版本才会有.
如果之前安装过, 没有卸载赶紧的话会提示 ERROR 1045 密码错误, 使用原来的密码即可登录

  1. 初始化 mysqld (步骤5无法进入mysql)

MySQL的运行需要一个系统库，初始化MySQL的操作就是生成这个系统库。在MySQL 5.7之前的版本，初始化操作是通过MySQL源码目录下的scripts目录中名为mysql_install_db的初始化脚本进行的；而从MySQL 5.7版本开始，mysql_install_db被弃用.
image.png

  1. 设置安全向导 (步骤6 无效)

mysql_secure_installation

– 为root用户设置密码
– 删除匿名账号
– 取消root用户远程登录
– 删除test库和对test库的访问权限
– 刷新授权表使修改生效

  1. Table ‘performance_schema.session_variables’ doesn’t exist

mysql_upgrade -u root -p --force
systemctl restart mysqld

  1. 卸载数据库以及数据删除

yum autoremove mysql*
rm -rf /var/lib/mysql*

9.1) 重置数据库(暂未测试过…)
mysqld --initialize
systemctl restart mysqld

此时初始化安装完数据库之后会在mysql.log里生成密码
grep "temporary password" /var/log/mysqld.log

  1. 使用生成的密码登录后要修改密码(注意密码复杂度)

set password = password("yourpassword")
flush privileges

  1. 允许远程登录
    修改 localhost字段为 ‘%’

select user, host, authentication_string from mysql.user;
update mysql.user set host = '%' where user = 'root' and host = 'localhost';
image.png

  1. 较新版本里 password 已经修改成了 authentication_string

    相关引用: https://mariadb.com/kb/en/mysqluser-table/

  2. 限制mysql登录
    update mysql.user set host='10.45.51.0/24' where user='root' and host='127.0.0.1';
    grant all on *.* to 'root'@'10.40.' identified by 'yourpassword';

指定ip或者网段
指定 192.168.128.8 可以登录
update mysql.user set host='192.168.128.8' where user ='root' and host = 'localhost';
grant all on *.* to 'root'@'192.168.128.8' identified by 'yourpassword';
grant all on *.* to 'root'@'192.168.128.8' identified by 'yourpassword' with grant option;
如果 grant all 这种写法提示语法错误, 在语句后面加上 with grant option

指定 192.168.128.0/24网段 可以登录
update mysql.user set host='192.168.128.%' where user ='root' and host = 'localhost';
grant all on *.* to 'root'@'192.168.128.%' identified by 'yourpassword';
也有下面这种写法, 但是经过测试无效(mysql community 5.7.37 具体没有深入)
update mysql.user set host='192.168.128.' where user ='root' and host = 'localhost';

image.png

  1. general_log 开启
mysql> show global variables like '%general%';
+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log      | OFF                        |
| general_log_file | /var/lib/mysql/centos7.log |
+------------------+----------------------------+
2 rows in set (0.02 sec)

使用命令修改的设置会在服务重启之后失效

临时生效: 

　　mysql>set global general_log_file='/tmp/general.lg';    # 设置路径
　　mysql>set global general_log=on;                        # 开启general log模式
　　mysql>set global general_log=off;                       # 关闭general log模式

永久生效:
编辑配置文件 /etc/my.cnf 添加:

general_log = 1
general_log_file = /tmp/general.log
  1. log_bin 开启
mysql> show global variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
+---------------------------------+-------+
5 rows in set (0.01 sec)

永久生效:
编辑vim /etc/my.cnf添加:

server_id=2
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 30

重启mysql:
systemctl restart mysqld

再次查看log_bin:

mysql> show global variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
+---------------------------------+--------------------------------+
5 rows in set (0.00 sec)
  1. slow_query_log 开启
mysql> show variables like 'slow_query_log%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | OFF                             |
| slow_query_log_file | /var/lib/mysql/centos7-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.01 sec)

临时生效:
set global slow_query_log=1;

永久生效:
修改/etc/my.cnf添加:

slow_query_log=1
slow_query_log_file=/var/lib/mysql/slow-log.log
long_query_time=10 #(查询超过10s的会被记录, 默认10, 建议修改为3)
mysql> show variables like 'slow_query_log%';
+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| slow_query_log      | ON                          |
| slow_query_log_file | /var/lib/mysql/slow-log.log |
+---------------------+-----------------------------+
2 rows in set (0.00 sec)

如果发现 14) 15) 16) 的操作不生效, 请确保MySQL用户对日志文件以及相关目录拥有读写的权限!!

  1. 数据库全量备份

mysqldump -u root -h 127.0.0.1 -p --all-databases > /backup/allBackup.sql

ERROR 1102 (42000): Incorrect database name ‘#mysql50#mysql.backup’

使用find命令全局查找相关文件 find / -name mysql.backup然后删除

相关引用 https://serverfault.com/questions/443651/mysql-drop-database-with-in-name

  1. mysql修改数据保存目录

mv /var/lib/mysql /opt/mysqldata

编辑文件: vim /etc/my.cnf 添加如下:

[client]
socket=/opt/mysqldata/mysql/mysql.sock
[mysqld]
datadir = /opt/mysqldata/mysql 
socket = /opt/mysqldata/mysql/mysql.sock

重启mysqld: systemctl restart mysqld

create database cloud;
create user ‘nextcloud‘@’localhost’ identified by ‘zhuangzhuang123…’;
grant all on cloud.* to nextcloud@’localhost’ identified by ‘zhuangzhuang123…’;
flush privileges;