Mysql安装、配置、权限、日志、优化、备份、还原和高可用详解

基于CentOS7最小化安装配置Mysql

安装与初始化配置

Mysql rpm包安装(client依赖于libs，server依赖于common和client及 libaio依赖 和 net-tools,安装顺序，common——>libs——>libs-compat——>client——>server)，官网下载地址:

1. 卸载mariadb及安装依赖

 rpm -e --nodeps mariadb-libs-5.5.60-1.el7_5.x86_64
 yum install -y libaio net-tools perl

2. 按照顺序安装mysql rpm包，最小化

rpm -ivh mysql-community-common-5.7.32-1.el7.x86_64.rpm  
rpm -ivh mysql-community-libs-5.7.32-1.el7.x86_64.rpm 
rpm -ivh mysql-community-client-5.7.32-1.el7.x86_64.rpm 
rpm -ivh mysql-community-server-5.7.32-1.el7.x86_64.rpm 

3. 启动mysql并设置开机启动；

systemctl enable mysqld --now

5. 查询并修改默认密码；

cat /var/log/mysqld.log |grep password |awk '{print $11}'
#因默认密码策略,需有数字、大小写、特殊字符
#方式一：mysqladmin修改
mysqladmin -u root -p oldpassword password newpassword
#方式二：修改user表
update user set password = password('新密码') where user = 'root';
#方式三：SET PASSWORD/alter命令
alter user 'root'@'localhost' identified by 'newpassword'
set password for root@localhost = password('newpassword');

6. 开启远程连接

#方式一：修改user表
 update user set host = '%' where user = 'root';
#方式二:授权法
 GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY "passowrd"; 

密码策略

1. 密码策略说明

名称	默认值	作用
validate_password_dictionary_file	-	-
validate_password_length	-	密码最小长度
validate_password_mixed_case_count	-	-
validate_password_number_count	-	-
validate_password_policy	-	-
validate_password_special_char_count	-	-

3. 查询密码策略

#8.X版本：
show variables like '%validate_password.policy%';
show variables like '%validate_password.length%';
#57版本：
show variables like '%validate_password_policy%';
show variables like '%validate_password_length%';

3. 修改密码策略

#8.X版本：
set global validate_password.policy=0;  #设置为弱口令
set global validate_password.length=1;  #密码最小长度为1
#57版本
set global validate_password_policy=0;
set global validate_password_length=1;

5. 密码策略永久修改

#指定密码策略，value值可选择0(LOW),1(MEDIUM),2(STRONG)其中一种，选择2需要提供密码字典文件,
echo "validate_password_policy=value">> /etc/my.cnf
#validate_password_policy=0,如果不需要密码策略，添加my.cnf文件中添加如下配置禁用即可：,validate_password = off

6. 忘记root密码

mysqld_safe --skip-grant-tables&
mysql -u root mysql
UPDATE user SET password=PASSWORD("new password") WHERE user='root';
FLUSH PRIVILEGES;

权限相关

1. 权限说明

Privilege	Grant Table Column	Context
ALL [PRIVILEGES]	Synonym for “all privileges”	Server administration
ALTER	Alter_priv	Tables
ALTER ROUTINE	Alter_routine_priv	Stored routines
CREATE	Create_priv	Databases, tables, or indexes
CREATE ROUTINE	Create_routine_priv	Stored routines
CREATE TABLESPACE	Create_tablespace_priv	Server administration
CREATE TEMPORARY TABLES	Create_tmp_table_priv	Tables
CREATE USER	Create_user_priv	Server administration
CREATE VIEW	Create_view_priv	Views
DELETE	Delete_priv	Tables
DROP	Drop_priv	Databases, tables, or views
EVENT	Event_priv	Databases
EXECUTE	Execute_priv	Stored routines
FILE	File_priv	File access on server host
GRANT OPTION	Grant_priv	Databases, tables, or stored routines
INDEX	Index_priv	Tables
INSERT	Insert_priv	Tables or columns
LOCK TABLES	Lock_tables_priv	Databases
PROCESS	Process_priv	Server administration
PROXY	See proxies_priv table	Server administration
REFERENCES	References_priv	Databases or tables
RELOAD	Reload_priv	Server administration
REPLICATION CLIENT	Repl_client_priv	Server administration
REPLICATION SLAVE	Repl_slave_priv	Server administration
SELECT	Select_priv	Tables or columns
SHOW DATABASES	Show_db_priv	Server administration
SHOW VIEW	Show_view_priv	Views
SHUTDOWN	Shutdown_priv	Server administration
SUPER	Super_priv	Server administration
TRIGGER	Trigger_priv	Tables
UPDATE	Update_priv	Tables or columns
USAGE	Synonym for “no privileges”	Server

2. 角色和权限语句(赋予角色和赋予权限的语句都是GRANT开头，但是区别在于是否由ON，所以 角色和权限，需要分成不同的语句来)

命令	作用
CREATE ROLE and DROP ROLE	创建和删除角色
GRANT and REVOKE	给角色或者账户分配权限
SHOW GRANTS	显示 账户/角色 所拥有的 权限或者角色
SET DEFAULT ROLE	设置账户默认使用什么角色
SET ROLE	改变当前会话的角色
CURRENT_ROLE()函数	显示当前会话的角色
mandatory_roles和activate_all_roles_on_login系统变量	允许定义用户登陆时强制的或者激活授权的角色

3. 角色与授权

#角色名和帐户名相同，也是名字+host，如果没有写host，默认为'%'
CREATE ROLE 'dba', 'only_read', 'only_write';  
#角色赋权
GRANT ALL ON testdb.* TO 'dba';
GRANT SELECT ON testdb.* TO 'only_read';     
GRANT INSERT, UPDATE, DELETE ON testdb.* TO 'only_write';
#角色删除
DROP ROLE 'only_read', 'only_write';

4. 用户与授权

#创建用户
CREATE USER 'dba1'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'only_read1'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'only_write1'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'password';
#授权角色
GRANT 'dba' TO 'dba1'@'localhost';
GRANT 'only_read' TO 'only_read1'@'localhost';
GRANT 'only_write' TO 'only_write1'@'localhost';
#激活用户账户角色
#将activate_all_roles_on_login设置为ON，默认是disabled。或使用命令
SET DEFAULT ROLE ALL TO 'dba1'@'localhost','only_read1'@'localhost','only_write1'@'localhost';
#直接授权权限
GRANT INSERT, UPDATE, DELETE ON testdb.* TO 'test_user'@'localhost';

5. 角色和账户权限互授权

#创建用户
CREATE USER 'user1';
#创建角色
CREATE ROLE 'role1';
#用户赋权
GRANT SELECT ON db1.* TO 'user1';
#角色赋权
GRANT SELECT ON db2.* TO 'role1';
#创建用户
CREATE USER 'user2';
#创建角色
CREATE ROLE 'role2';
#角色和账户权限互授权
GRANT 'user1', 'role1' TO 'user2';
GRANT 'user1', 'role1' TO 'role2';

7. 撤销授权

#收回该账户的角色
REVOKE role FROM user;
#收回角色的权力(修改了角色的权利，会影响拥有该角色的账户的权力)
REVOKE INSERT, UPDATE, DELETE ON testdb.* FROM 'only_write';

日志相关

1. 日志及分类

• 二进制日志：该日志文件会以二进制的形式记录数据库的各种操作，但不记录查询语句。
• 错误日志：该日志文件会记录 MySQL 服务器的启动、关闭和运行错误等信息。
• 通用查询日志：该日志记录 MySQL 服务器的启动和关闭信息、客户端的连接信息、更新、查询数据记录的 SQL 语句等。
• 慢查询日志：记录执行事件超过指定时间的操作，通过工具分析慢查询日志可以定位 MySQL 服务器性能瓶颈所在。
  在 MySQL 所支持的日志文件里，除了二进制日志文件外，其它日志文件都是文本文件。默认情况下，MySQL 只会启动错误日志文件，而其它日志则需要手动启动

2. 错误日志

#错误日志查询
SHOW VARIABLES LIKE 'log_error';

3. 二进制日志
   二进制日志（Binary Log）是 MySQL 中非常重要的日志。主要用于记录数据库的变化情况，即 SQL 语句的 DDL 和 DML 语句，不包含数据记录查询操作。可根据二进制日志文件中的记录来恢复数据库服务器。

#二进制日志状态查询(默认关闭)
SHOW VARIABLES LIKE 'log_bin';
#查看二进制日志文件列表
SHOW binary logs;
#查看当前正在写入的二进制日志文件
SHOW master status;
#查看二进制日志文件内容(只在当前文件夹下查找指定的二进制日志，因此需要在二进制日志所在的目录下运行该命令，否则将会找不到指定的二进制日志文件)
mysqlbinlog filename.number
# 删除所有二进制日志
RESET MASTER;
#删除指定二进制日志的编号之前的日志
PURGE MASTER LOGS TO 'filename.number';
#根据创建时间删除二进制日志
PURGE MASTER LOGS TO 'yyyy-mm-dd hh:MM:ss';
#暂时停止二进制日志
SET SQL_LOG_BIN=0/1;
#永久开启
#expire_logs_day 定义了 MySQL 清除过期日志的时间、二进制日志自动删除的天数。默认值为 0，表示“没有自动删除”。当 MySQL 启动或刷新二进制日志时可能删除。
#max_binlog_size 定义了单个文件的大小限制，如果二进制日志写入的内容大小超出给定值，日志就会发生滚动（关闭当前文件，重新打开一个新的日志文件）。不能将该变量设置为大于 1GB 或小于 4096B（字节），其默认值是 1GB。
echo log-bin=dir/[filename]>>/etc/my.cnf

4. 通用查询日志

#通用日志状态查询(默认关闭)
 SHOW VARIABLES LIKE '%general%';
#临时设置
set global general_log=on;
#临时配置通用日志存放的位置
set global general_log_file=dir/filename;
#永久配置
echo log=dir/filename>>/etc/my.cnf

5. 慢查询日志

#慢查询开启状态查询(默认关闭)
SHOW VARIABLES LIKE 'slow_query%';
#查询超过多少秒才记录
SHOW VARIABLES LIKE 'long_query_time'
#临时开启
set global slow_query_log=on;
#临时配置慢查询日志存放的位置
set global slow_query_log_file=dir/filename;
#配置超过记录时间
#永久配置long_query_time单位时间为秒
echo -e "log-slow-queries=dir/filename\nlong_query_time=n"

6. 更新日志
   用–log-update[=file_name]选项启动,不推荐使用.
7. 置日志输出方式

#查询日志输出方式(默认file)
SHOW VARIABLES LIKE '%log_out%';
#临时配置日志输出方式
set log_output=file 或 set log_output=table 或 set log_output=file,table
#永久配置
echo log_output=file,table>>/etc/my.cnf

配置优化

待续

备份

1. 数据库备份类型

 根据备份的方法（是否需要数据库离线）可以将备份分为

• 热备（Hot Backup：可以在数据库运行中直接备份，对正在运行的数据库操作没有任何的影响，数据库的读写操作可以正常执行。这种方式在 MySQL 官方手册中称为 Online Backup（在线备份）

• 冷备（Cold Backup）必须在数据库停止的情况下进行备份，数据库的读写操作不能执行。这种备份最为简单，一般只需要复制相关的数据库物理文件即可。这种方式在 MySQL 官方手册中称为 Offline Backup（离线备份）。

• 温备（Warm Backup）同样是在数据库运行中进行的，但是会对当前数据库的操作有所影响，备份时仅支持读操作，不支持写操作。

 按照备份后文件的内容，热备份又可以分为：

• 逻辑备份

• 物理备份

 按照备份数据库的内容来分，备份又可以分为：

• 完全备份

• 部分备份

  MyISAM 不支持热备，支持温备和冷备。而 InnoDB 支持热备、温备和冷备。

2.备份工具

• mysqldump：逻辑备份工具，适用于所有的存储引擎，支持温备、完全备份、部分备份、对于 InnoDB 存储引擎支持热备。
• cp、tar 等归档复制工具：物理备份工具，适用于所有的存储引擎、冷备、完全备份、部分备份。
• lvm2 snapshot：借助文件系统管理工具进行备份。
• mysqlhotcopy：名不副实的一个工具，仅支持 MyISAM 存储引擎
• xtrabackup：一款由 percona 提供的非常强大的 InnoDB/XtraDB 热备工具，支持完全备份、增量备份

3.mysqldump备份与还原

• mysqldump [OPTIONS] database [tables]
• mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…]
• mysqldump [OPTIONS] --all-databases [OPTIONS]

常用：

*待完整*

选项	作用	示例
-all-databases	导出全部数据库	mysqldump -uroot -p --all-databases
–all-tablespaces	导出全部表空间	mysqldump -uroot -p --all-databases --all-tablespaces
–no-tablespaces	不导出任何表空间信息	mysqldump -uroot -p --all-databases --no-tablespaces
–add-drop-database	-	-
–add-drop-table	-	-
–add-locks	-	-

待续
4. xtrabackup备份与还原
待续

高可用

待续