一、安装
centos 系统安装
官网文档参考：https://dev.mysql.com/doc/refman/8.0/en/linux-installation-rpm.html
#下载rpm捆绑包
wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.28-1.el7.x86_64.rpm-bundle.tar
#卸载centos自带的mariadb
rpm -qa |grep mariadb #查看
rpm -e mariadb-libs-5.5.64-1.el7.x86_64 #如果卸载不掉，则用yum卸载
yum -y remove mariadb-libs-5.5.64-1.el7.x86_64
#解压捆绑包
tar -xvf mysql-8.0.28-1.el7.x86_64.rpm-bundle.tar
# 按顺序安装包
rpm -ivh mysql-community-common-8.0.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-icu-data-files-8.0.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.28-1.el7.x86_64.rpm
#vim /etc/my.cnf
#修改目录和配置文件
#启动
systemctl start mysqld #启动
systemctl enable mysqld #开机启动
#查看
mysqladmin --version #或者 mysql --version
#获取默认随机密码
grep password /var/log/mysqld.log
#修改密码规则（不建议）
set global validate_password.policy=0;#密码复杂度,分为low(0)、medium(1)、high(2)三个等级，默认为midium（1）
set global validate_password.length=1;
#查看参数值
select @@validate_password.policy;
#修改密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '111111';
#mysql8 之前，默认加密规则是mysql_native_password；
#mysql8 之后，默认加密规则是caching_sha2_password；
#设置远程登录
mysql -uroot -p #登录数据库执行以下命令
use mysql；
update user set host='%' where user ='root';
flush privileges;
exit;
#重启服务
systemctl restart mysqld.service #重启
windown 安装
进入官网选择window系统下载
官网参考地址：https://dev.mysql.com/doc/refman/8.0/en/windows-install-archive.html
二、/etc/my.cnf配置参数
动态修改：set GLOBAL 变量名 变量值 在线设置参数，立即生效，重启失效。
静态修改：修改/etc/my.cnf参数，重启生效
#默认值 标示id，一个同步组内唯一范围0~4294967295
server_id=1
#默认端口号
port=3306
# 事务模式，可选值：REPEATABLE-READ/READ-COMMITTED/READ-UNCOMMITTED/SERIALIZABLE
# 建议使用READ-COMMITTED
transaction_isolation=REPEATABLE-READ#默认值
# 最大连接数上限，不要设置过大，会影响一些系统变量的计算
max_connections=151 #默认值，建议设成1000
# 是否忽略大小写，默认不忽略
lower_case_table_names=0 #一定要设置，设置完不可更改
目录配置
# 数据主目录
datadir=/var/lib/mysql #默认值
# socket文件，建议改放在datadir目录下的固定名称mysql.sock
socket=/var/lib/mysql/mysql.sock #默认值
# mysql错误日志，建议固定文件名
log_error=/var/log/mysqld.log #默认值
# mysql dml日志 建议关闭
general_log=OFF#默认关闭，执行dml日志
general_log_file=主机名.log#日志文件，相对数据目录
# mysql的pid文件，建议固定文件名
pid_file=/var/run/mysqld/mysqld.pid #默认值
空间大小
# innodb缓存空间，建议10%-70%，并且不超过内存的70%
innodb_buffer_pool_size=128M #默认值（一定要设置）
慢查询
# 慢查询记录，一般超过3s就记录，记录的文件名最好固定
slow_query_log=0 #默认值，建议设置1开启
long_query_time=10 #默认值，建议设置成3
slow_query_log_file=slow_query.log #相对数据目录
binlog 日志
log_bin=binlog #默认值，binlog文件名称
#disable_log_bin #默认是注释的，即默认开启binlog日志
# binlog模式：ROW/STATEMENT/MIXED
binlog_format=ROW #默认值，建议使用ROW格式
# binlog 过期天数
expire_logs_days = 0 #默认0 不过期，建议设置成7
max_binlog_size = 500M
# binlog刷写方式，1最安全，0性能最好，一般建议为0
#当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘
sync_binlog=1 #默认值
#理解：relay log很多方面都跟binary log差不多
#区别是：从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后SQL线程会读取relay-log日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致
relay_log=mysql-relay-bin
#是否自动清空不再需要中继日志时
relay_log_purge=1 #默认值，建议
#当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性
relay_log_recovery=0 #默认值关闭，建议开启
#与sync_binlog参数是一样的
#设置为1时，slave的I/O线程每次接收到master发送过来的binlog写入relay log中继日志里，会将它的中继日志同步到磁盘(使用fdatasync())
sync_relay_log=10000 #默认值，建议设置成0，性能最高
# relay-log可用的最大空间，默认值0，无限制
relay_log_space_limit=0；#不推荐使用
认证相关
#密码策略
validate_password.policy=1；
#密码长度
validate_password.length=8;
#身份认证方式（加密规则)
# 支持版本：5.7、8.0
default_authentication_plugin=mysql_native_password
推荐my.cnf样例
#默认值 标示id，ip最后段+递增数字两位
server_id=10001
#端口号不跟默认一样
port=63306
# innodb缓存空间，建议10%-70%，并且不超过内存的70%
innodb_buffer_pool_size=3G
# binlog刷写方式，1最安全，0性能最好，一般建议为0
sync_binlog=0
# binlog 过期天数建议7天
expire_logs_days = 7
# relay_log 刷写方式
sync_relay_log=0
# 事务模式读提交
transaction_isolation=READ-COMMITTED
# 最大连接数上限
max_connections=1000
# 忽略大小写
lower_case_table_names=1
# 开启慢查询
slow_query_log=1 #建议开启，默认不开启
long_query_time=3 #建议3s ，默认10s
slow_query_log_file=slow_query.log #相对数据目录
# 自增量步长（设置为master数量）
auto-increment-increment=2
# 自增量起始点，偏移量（每个master依次递增） #默认值是1，https://dev.mysql.com/doc/refman/8.3/en/replication-options-source.html
auto-increment-offset=1
三、常用命令
mysql 命名不支持字符'-'
数据库实例管理
#创建数据库实例
create database if not exists db_test;
#授权数据库权限给指定用户
grant all privileges on db_test.* to 'app_user'@'%' ;
#切换数据库
use db_test
#查看当前数据库所有表
show tables;
#删除数据库
drop database db_test;
#显示数据库列表
show databases;
#显示当前数据库状态
status;
#查看数据库连接
show processlist
账号角色管理
#创建用户
create user 'app_user'@'%' identified with 'mysql_native_password' by 'app_pass';
#查看权限
show grants; #查看当前账号权限
show grants for 'app_user'@'%' #查看指定用户的权限
#用户列表
use mysql #用户列表存储在mysql库中user表
select host,user from user;
show tables like 'user'; --全局级别的权限
show tables like 'db'; --数据库级别的权限
show tables like 'table_priv'; --表级别的权限
show tables like 'columns_priv'; --列级别的权限
系统参数
#显示所有参数
show variables; #相当于show session variables; 当前session作用域
show global variables; # 显示全局作用域参数
select validate_password.policy; #查看当前session参数
select @@GLOBAL.validate_password.policy; #查看全局参数
#按关键case前后模糊匹配
show variables like "%case%";
show global variables like "%case%";
功能特性
复制表
# 从旧表复制新表包括数据
CREATE TABLE new_table AS
(
SELECT * FROM tables_name;
)
#只复制表结构
create table tb1_bak like tb1;
#复制部分字段和数据
create table tb1_bak as (select id, name,desc from tb1 where id > 50)
四、运维管理
流复制
主从复制
假设ip分别为192.168.1.100（主），192.168.1.101（从）
- 在主库上操作
#创建流复制账号
create user 'reply_user'@'192.168.1.101' identified WITH 'mysql_native_password' by 'reply_user';
#授权流复制账号
grant replication slave on *.* to 'reply_user'@'192.168.1.101' ;
flush privileges;
#显示状态信息
show master status;
#备份数据
mysqldump -u root -p --all-databases --single-transaction --source-data=2 --flush-logs --flush-privileges > /tmp/mysql_backup.sql
- 从库上操作
#设置参数
vim /etc/my.cnf;
#server_id 不能重复
server_id=101
# 设置只读（主主双写不需要）
read_only=1 #默认值为0(OFF)，只针对普通账号有效即（非SUPER权限账号）
- read_only=1只读模式，不会影响slave同步复制的功能
- read_only=1只读模式，限定的是普通用户进行数据修改的操作，但不会限定具有super权限的用户的数据修改操作 ("super_read_only=on"， 则就会限定具有super权限的用户)；(也就是说"real_only"只会禁止普通用户权限的mysql写操作，不能限制super权限用户的写操作； 如果要想连super权限用户的写操作也禁止，就使用"flush tables with read lock;"，这样设置也会阻止主从同步复制！对应的解锁模式命令为："unlock tables;")
#导入备份数据库
mysql -uroot -p密码 < /tmp/mysql_backup.sql
mysql -uroot -p 登陆后执行以下命令,位点在/tmp/mysql_backup.sql 头部可以找到
CHANGE MASTER TO MASTER_HOST='192.168.1.100', MASTER_PORT=3306, MASTER_USER='reply_user', MASTER_PASSWORD='reply_user', MASTER_LOG_FILE='binlog.000005', MASTER_LOG_POS=2032;
#延迟流复制 单位秒
CHANGE MASTER TO MASTER_DELAY=86400
#开启流复制
start slave;
show slave status\G
- 删除流复制
#停止流复制
stop slave
#删除流复制
reset slave all
主主双写
在主从流复制上，增加反向流复制192.168.1.101->192.168.1.100
两台机器配置文件增加主键配置
vim /etc/my.cnf
# 自增量步长（设置为master数量）
auto-increment-increment=2
# 自增量起始点，偏移量（每个master依次递增）
auto-increment-offset=1
#需重启生效
备份恢复
备份
#dump指定数据库
mysqldump -u root -p db_test > database_dump.txt
#dump所有数据库
mysqldump -u root -p --all-databases > all_database_dump.txt
#dump 指定表
mysqldump -u root -p db_test tb_test > tb_test_dump.txt
恢复
#导入备份数据库
mysql -uroot -p密码 db_test < database_dump.txt
# mysql -uroot -p 登陆上
mysql> create database db_test; # 创建数据库
mysql> use db_test; # 使用已创建的数据库
mysql> set names utf8mb4; # 设置编码
mysql> source /home/database_dump.txt # 导入备份数据库
流复制异常
slave中继日志损坏
# 重新定位binlog日志和POS点，然后重新同步。
mysql -uroot -p #登陆数据库
stop slave;
show slave status\G;
#以Relay_master_Log_File参数和Exec_master_Log_Pos参数为准。
change master to ...
mysql> start slave;
mysql> show slave status\G;
主从数据不一致
- master上删除一条记录，slave上找不到对应记录而报错
- 在master上插入一条记录，slave报主键重复错误
- 在master上更新记录，但在slave上找不到对应的记录
分析报错日志，找到报错的数据记录，先stop slave，修复数据，再start slave，强烈不推荐使用 set global sql_slave_skip_counter=1;直接跳过
数据库目录更换
#第一步 先停止数据库
systemctl stop mysqld
#第二部拷贝数据目录 到新目录或者新磁盘中
mv /var/lib/mysql /cach1/data/
#设置目录权限
chown -R mysql:mysql /cache1/data
#注意目录路径正确性
#设置客户端连接socket地址
[client]
socket=/cache1/data/mysql/mysql.sock
#第三部 启动数据库
systemctl start mysqld
数据库初始化启动停止
#初始化目录
bin/mysqld --defaults-file=/etc/mysql/my.cnf --initialize --user=mysql
bin/mysqld --defaults-file=/etc/mysql/my.cnf --initialize-insecure --user=mysql #无密码初始化
#启动
#方式一
bin/mysqld --defaults-file=/etc/mysql/my.cnf --user=mysql&
#客户端连接
bin/mysqld --defaults-file=/etc/mysql/my.cnf
#或者
bin/mysqld -S /cache1/data/mysql/mysql.sock #sock文件路径从/etc/mysql/my.cnf 获取
#停止
bin/mysqladmin shutdown -S /cache1/data/mysql/mysql.sock #如果有密码 加参数 -p
超管密码忘记
#增加参数skip-grant-tables，跳过密码校验
vim /etc/my.cnf
skip-grant-tables
#重启服务
systemctl restart mysqld
#连上数据库，无须密码
mysql -u root
#密码置空
use mysql
update user set authentication_string='' where user=‘root';
exit
#注释参数skip-grant-tables
#连上数据库，无须密码
mysql -u root
#执行修改密码语句
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '111111';
binlog日志使用
Binlog是二进制日志文件，有两个作用，一个是增量备份，另一个是主从复制。
Binlog日志包括两类文件；第一个是二进制索引文件(binlog.index),第二个为日志文件(binlog.00000*),记录数据库所有的DDL和DML语句事件。当发生下述三种情况时，binlog 日志便会进行重建:
- 文件大小达到 max_binlog_size 参数的值
- 执行 flush logs 命令
- 重启 mysql 服务
mysqlbinlog工具使用
#查看
mysqlbinlog -v binlog.000002
##根据时间节点恢复数据
mysqlbinlog --start-datetime="2020-04-27 20:58:18" --stop-datetime="2020-04-27 20:58:35" --database=testdb /var/lib/mysql/binlog.000003 | mysql -uroot -p密码 -v testdb
##根据pos位置恢复数据
mysqlbinlog --start-position=573 --stop-position=718 --database=testdb /var/lib/mysql/binlog.000003 | /usr/bin/mysql -uroot -p密码 -v testdb
查看binlog日志
#登陆数据库
mysql -uroot -p
#查看所有binlog日志文件
show master logs;
#查看binlog事件
show binlog events in 'binlog.000003'
show binlog events in 'binlog.000003' from 519 #从指定pos开始
show binlog events in 'binlog.000003' from 519 limit 10 #从指定pos开始限制10个事件
