mysql配置

MySQL数据库的介绍

MySQL是一个关系型数据库管理系统，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件，它是由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品，MySQL 是最流行的关系型数据库管理系统中的一个。

MySQL的特点:

MySQL是开源的，所以你不需要支付额外的费用。
MySQL支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
MySQL使用标准的SQL数据语言形式。
MySQL可以安装在不同的操作系统，并且提供多种编程语言的操作接口。这些编程语言包括C、C++、Python、Java、Ruby等等。

SQL语言主要分为

DQL：数据查询语言，用于对数据进行查询，如select
DML：数据操作语言，对数据进行增加、修改、删除，如insert、update、delete
TPL：事务处理语言，对事务进行处理，包括begin transaction、commit、rollback
DCL：数据控制语言，进行授权与权限回收，如grant、revoke
DDL：数据定义语言，进行数据库、表的管理等，如create、drop

这里不详细讲这些语句

MySQL数据库的安装

MySQL数据库服务端软件的安装

在Ubuntu中打开终端，输入下面的命令:

sudo apt-get install mysql-server

命令行客户端mysql的安装

在Ubuntu中打开终端，输入下面的命令:

sudo apt-get install mysql-client

登录到MySQL服务器：

mysql -u root -p

登录成功后, 输入如下命令查看效果：
1、使用mysql数据库

use mysql;

2、显示当前时间

select now();

3、登出(退出)数据库:

quit 或 exit 或 ctrl + d

5、查看当前使用的数据库

select database()

6、删除数据库-慎重

drop database 数据库名;
例：
drop database python;

创建表: create table 表名(字段名 字段类型 约束, …);
修改表-添加字段: alter table 表名 add 字段名 字段类型 约束
修改表-修改字段类型: alter table 表名 modify 字段名 字段类型 约束
修改表-修改字段名和字段类型: alter table 表名 change 原字段名 新字段名 字段类型 约束
修改表-删除字段: alter table 表名 drop 字段名;
删除表: drop table 表名;
查询数据: select * from 表名; 或者 select 列1,列2,… from 表名;
插入数据: insert into 表名 values (…) 或者 insert into 表名 (列1,…) values(值1,…)
修改数据: update 表名 set 列1=值1,列2=值2… where 条件
删除数据: delete from 表名 where 条件
想要完成表复制可以使用: insert into … select … SQL语句
连接更新表中数据使用: update … join … 语句
添加外键约束: alter table 从表 add foreign key(外键字段) references 主表(主键字段);
删除外键约束: alter table 表名 drop foreign key 外键名;

主要配置信息说明:

port表示端口号，默认为3306
bind-address表示服务器绑定的ip，默认为127.0.0.1
datadir表示数据库保存路径，默认为/var/lib/mysql
log_error表示错误日志，默认为/var/log/mysql/error.log

新增用户然后配置指定ip段连接

创建新用户并指定其密码：

CREATE USER 'newuser'@'ip_address/subnet_mask' IDENTIFIED BY 'password';

将newuser替换为您想要创建的用户名，ip_address/subnet_mask替换为允许连接的IP段，例如192.168.1.0/24表示允许从192.168.1.0到192.168.1.255的任何IP地址连接。password是用户的密码。
例如，如果您想要允许用户从IP范围10.0.0.0到10.255.255.255连接，您可以这样做：

CREATE USER 'newuser'@'10.0.0.0/8' IDENTIFIED BY 'secure_password';

mysql给用户增加一个数据库的所有权限

在MySQL中，您可以使用GRANT语句为用户授予数据库的所有权限。
示例代码：

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'hostname';

请将database_name替换为您想要授权的数据库名，username替换为您想要授权的用户名，hostname替换为用户连接的主机名。如果要允许用户从任何主机连接，可以使用’%'作为hostname的值。
刷新权限：
确保在执行这些命令后，您已经正确设置了MySQL的用户和权限表，使用FLUSH PRIVILEGES是必要的，以便让权限更改立即生效。

FLUSH PRIVILEGES;

当打开 navcat 选择数据库 点表报错 you need at least one of the process privilegs for this operation 此操作需要一个进程特权

其实就是缺一个PROCESS的权限（PROCESS是用来查看谁连接数据的一个权限，除了root用户，都只能看到自己的）
给当前用赋值PROCESS的权限

grant process on *.* to 数据库用户名;

刷新权限

flush privileges;

查看processlist权限

show processlist

之后重新打开数据库连接（我的是重启了navicat才可以的）

再次查看processlist权限（我的依旧没有其他人的权限，但是不报错了）

开启MySQL的日志管理并进行增量备份

1. 开启二进制日志（Binary Logging）

二进制日志记录了数据库更改的所有信息，这是增量备份的基础。

修改配置文件
在my.cnf或my.ini配置文件中，找到或添加以下配置：

[mysqld]
log_bin = mysql-bin
server_id = 1

log_bin指定了二进制日志的前缀，MySQL会在此基础上添加序列号生成日志文件。server_id是服务器的唯一标识符，对于复制和恢复操作很重要。

重启MySQL服务
修改配置文件后，需要重启MySQL服务以使更改生效。

2. 进行增量备份

增量备份通常使用mysqlbinlog工具，该工具可以读取二进制日志并将它们转换为SQL语句。

手动备份二进制日志
在配置好二进制日志后，你可以定期手动复制或归档旧的二进制日志文件。

使用mysqlbinlog工具进行增量备份
你可以使用mysqlbinlog工具将二进制日志转换为SQL语句，并保存到文件中：

mysqlbinlog mysql-bin.000001 > backup.sql

这将把mysql-bin.000001日志文件中的事件转换为SQL语句，并保存到backup.sql文件中。

3. 增量恢复

在需要恢复时，你可以执行backup.sql文件中的SQL语句来应用更改。这通常是在全量备份之后进行的，以应用自上次全量备份以来的所有更改。

使用定时任务周期进行增量备份和全量备份

使用定时任务进行定时mysql增量备份，每次在周一进行全量备份，周二进行在周一的基础上再进行增量备份，周三在周二的增量备份上再增量备份依次到下一个周一，删除上上周的备份

#!/bin/bash

# 设置备份目录
BACKUP_DIR="/path/to/your/backup/directory"

# 获取当前日期
CURRENT_DATE=$(date +%Y%m%d)
# 获取当前是星期几（1代表周一，7代表周日）
CURRENT_DAY=$(date +%u)

# 定义全量备份和增量备份的二进制日志位置文件
FULL_BACKUP_POS_FILE="${BACKUP_DIR}/full_backup_position.txt"
INCREMENTAL_BACKUP_POS_FILE="${BACKUP_DIR}/incremental_backup_position.txt"

# 如果是周一，执行全量备份
if [ $CURRENT_DAY -eq 1 ]; then
    # 使用mysqldump进行全量备份
    mysqldump -u your_username -pyour_password your_database_name > "${BACKUP_DIR}/full_backup_${CURRENT_DATE}.sql"
    
    # 记录全量备份的二进制日志位置
    mysqlbinlog_position=$(mysqlbinlog $(mysql -u your_username -pyour_password -e "SHOW BINARY LOGS;" | tail -n 1 | awk '{print $1}') | grep -A1 '^#' | tail -n1 | awk '{print $4}')
    echo "$mysqlbinlog_position" > "$FULL_BACKUP_POS_FILE"
else
    # 否则，执行增量备份
    # 获取上次备份（全量或增量）的二进制日志位置
    if [ -f "$FULL_BACKUP_POS_FILE" ]; then
        last_position=$(cat "$FULL_BACKUP_POS_FILE")
    elif [ -f "$INCREMENTAL_BACKUP_POS_FILE" ]; then
        last_position=$(cat "$INCREMENTAL_BACKUP_POS_FILE")
    else
        # 如果没有上次备份的位置，从最早的二进制日志文件开始
        last_position=4  # 假设从第4个事件开始（通常这是第一个事件）
    fi

    # 执行增量备份
    mysqlbinlog --start-position=$last_position --stop-never mysql-bin.000001 > "${BACKUP_DIR}/incremental_backup_${CURRENT_DATE}.sql" &
    
    # 记录增量备份的二进制日志位置到文件
    echo "$last_position" > "$INCREMENTAL_BACKUP_POS_FILE"
fi

# 删除上上周的备份文件（即保留最近两周的备份）
find "$BACKUP_DIR" -type f -name "backup_*.sql" -mtime +14 -delete

请注意，你需要替换your_username、your_password和your_database_name为你的MySQL数据库的实际信息。同时，你可能需要根据你的MySQL服务器配置调整mysqlbinlog命令中的二进制日志文件名
控制二进制日志的行为常见的配置项：
以下是一些常见的配置项，它们可以帮助您控制二进制日志的行为：

log_bin：启用或禁用二进制日志。如果设置为ON，则启用二进制日志。

log_bin_basename：指定二进制日志文件的基础名称。例如，如果将其设置为/var/log/mysql/mysql-bin，则二进制日志文件名将以/var/log/mysql/mysql-bin.000001、/var/log/mysql/mysql-bin.000002等命名。

log_bin_index：指定二进制日志索引文件的名称。索引文件包含了所有二进制日志文件的列表。

max_binlog_size：设置单个二进制日志文件的最大大小。当达到这个大小限制时，MySQL会创建新的二进制日志文件。

expire_logs_days：设置二进制日志文件的保留天数。超过这个天数的文件将被自动删除。

binlog_format：控制二进制日志的格式，可以是STATEMENT、ROW或MIXED。

查看MySQL数据库支持的表的存储引擎

– 查看MySQL数据库支持的表的存储引擎
show engines;

说明:
常用的表的存储引擎是 InnoDB 和 MyISAM
InnoDB 是支持事务的
MyISAM 不支持事务，优势是访问速度快，对事务没有要求或者以select、insert为主的都可以使用该存储引擎来创建表

MySQL数据库默认采用自动提交(autocommit)模式, 也就是说修改数据(insert、update、delete)的操作会自动的触发事务,完成事务的提交或者回滚

开启事务:

begin;
或者
start transaction;

提交事务:

将本地缓存文件中的数据提交到物理表中，完成数据的更新。

commit;

回滚事务:
放弃本地缓存文件中的缓存数据, 表示回到开始事务前的状态

rollback;

MySQL中索引的优点和缺点和使用原则

优点：

加快数据的查询速度

缺点：

创建索引会耗费时间和占用磁盘空间，并且随着数据量的增加所耗费的时间也会增加

使用原则：

• 1、通过优缺点对比，不是索引越多越好，而是需要自己合理的使用。
• 2、对经常更新的表就避免对其进行过多索引的创建，对经常用于查询的字段应该创建索引，
• 3、数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。
• 4、在一字段上相同值比较多不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可是建立索引。

索引是加快数据库的查询速度的一种手段
创建索引使用: alter table 表名 add index 索引名[可选] (字段名, xxx);
删除索引使用: alter table 表名 drop index 索引名;