Mysql数据库
mysql 5.6.51 二进制包安装
# rpm -qa | grep mariadb
# rpm -qa | grep mysql
# yum remove mariadb-libs.x86_64
# wget https://cdn.mysql.com/archives/mysql-5.6/mysql-5.6.51-linux-glibc2.12-x86_64.tar.gz
# tar zxvf mysql-5.6.51-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
# cd /usr/local/
# mv mysql-5.6.51-linux-glibc2.12-x86_64.tar.gz/ mysql
# groupadd mysql
# useradd -g mysql mysql -s /sbin/nologin
# mkdir -p /data/mysqldb/{3306,innodb,mysql-bin,logs,run,relay-bin}
# chown -R mysql:mysql /data/mysqldb
# chown -R root:mysql /usr/local/mysql
# cd /etc/
# wget syk.my/download/conf/my.cnf \\ 导入配置文件
# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh \\ 加入到环境路径
# source /etc/profile
# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysqldb/3306
# cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld \\ mysql.server 目录
# chkconfig --add mysqld
# chkconfig mysqld on
# systemctl restart mysqld
# ss -tnl
# mysql
> use mysql
> select user,host,password from user;
> delete from mysql.user where user=''; \\ 删除user为空用户
> update user set password=password("123456") where user="root"; \\ 修改密码 5.6及以下修改密码的命令
> grant all privileges on *.* to root@'%' identified by '123456'; \\ 远程 权限
> flush privileges;
> create database ssyy99; \\ 创建数据库 不能有点 .
> grant all privileges on ssyy99.* to ssyy99@'%' identified by '123456'; \\ 创建用户 给所有权限
> source /data/aaa.sql \\ 导入数据库
mysql 5.7.32 二进制包安装
# rpm -qa | grep mariadb
# rpm -qa | grep mysql
# yum remove mariadb-libs.x86_64
# wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
# tar zxvf mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
# cd /usr/local/
# mv mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz/ mysql
# groupadd mysql
# useradd -g mysql mysql -s /sbin/nologin
# mkdir -p /data/mysqldb/{3306,innodb,mysql-bin,logs,run,relay-bin}
# chown -R mysql:mysql /data/mysqldb
# chown -R root:mysql /usr/local/mysql
# cd /etc/
# wget syk.my/download/conf/my.cnf \\ 导入配置文件
# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh \\ 加入到环境路径
# source /etc/profile \\ 初始化 记住生成的密码
# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysqldb/3306
# cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld \\ mysql.server 目录
# chkconfig --add mysqld
# chkconfig mysqld on
# systemctl restart mysqld
# ss -tnl
# mysql -uroot -p \\ 上面初始化会生成密码
> alter user 'root'@'localhost' identified by '123456'; \\ 修改密码 5.7及以上修改密码的命令
> flush privileges;
> use mysql
> select user,host,authentication_string from user;
> grant all privileges on *.* to root@'%' identified by '123456'; \\ 给root 远程权限
> create database ssyy99; \\ 创建数据库 不能有点 .
> grant all privileges on ssyy99.* to ssyy99@'%' identified by '123456'; \\ 创建用户 给所有权限
> source /data/aaa.sql \\ 导入数据库
mysql 8.0.22 二进制包安装 \\ 其他命令 与 5.7 一样
# wget https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.22-linux-glibc2.12-x86_64.tar.xz
# xz -d mysql-8.0.22-linux-glibc2.12-x86_64.tar.xz
# tar xcv mysql-8.0.22-linux-glibc2.12-x86_64.tar.xz -C /usr/local/
# mysql -uroot -p
> alter user 'root'@'localhost' identified by '123456';
> create user 'root'@'%' identified by '123456'; \\ 创建root 的远程用户
> grant all privileges on *.* to 'root'@'%' with grant option; \\ 给与root 远程权限
> create database ssyy99; \\ 创建数据库
> create user 'ssyy99'@'%' identified by '123456'; \\ 创建用户
> grant all privileges on ssyy99.* to 'ssyy99'@'%' with grant option; \\ 给与 权限
注:
官网下载地: https://downloads.mysql.com/archives/community/
源码包名: mysql-5.7.32.tar.gz
二进制名: mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
二进制名: mysql-8.0.22-linux-glibc2.12-x86_64.tar.xz
121.43.132.239
........................................................................................................................
# vim /etc/my.cnf \\ 配置文件 可以用于 5.6 5.7 5.8
##### 5.6 5.7 5.8 #################
[client]
socket=/data/mysqldb/run/mysql.sock
default-character-set=utf8mb4
[mysqld]
user=mysql
port=3306
basedir=/usr/local/mysql
socket=/data/mysqldb/run/mysql.sock
datadir=/data/mysqldb/3306
tmpdir=/tmp
bind-address=0.0.0.0
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
skip-external-locking
skip-name-resolve
character-set-server=utf8
collation-server=utf8_bin
default-storage-engine=INNODB
wait_timeout=1800
connect_timeout=30 #默认没有设置该参数
interactive_timeout=1800
back_log=500 #默认没有这个参数
#myisam_recover #默认没有这个参数5.7不支持此参数
#skip-grant-tables # 免密码登录
event_scheduler=ON #默认没有这个参数
server-id=1
auto_increment_offset=1
auto_increment_increment=1
transaction_isolation=READ-COMMITTED
#######binlog########
log-bin=/data/mysqldb/mysql-bin/mysql-bin
binlog_format=mixed
max_binlog_size=256M #默认没有该参数
binlog_cache_size=2M #默认没有该参数
expire_logs_days=0 #原来为15天
log-slave-updates
skip_slave_start
sync_binlog=0
relay-log=/data/mysqldb/relay-bin/relay-bin
relay_log_recovery=1
#####slowlog#########
slow_query_log=ON
slow_query_log_file=/data/mysqldb/logs/mysql-slow.log
long_query_time=1
######per_thread_buffers############
max_connections=200 #原来为6000
max_connect_errors=5000 #原来为100
key_buffer_size=384M
max_allowed_packet=4M
table_open_cache=2048 #原来为512
table_definition_cache=2048 #默认没有该参数
join_buffer_size=16M
tmp_table_size=67108864
sort_buffer_size=16M
read_buffer_size=16M
read_rnd_buffer_size=8M
thread_cache_size=64 #原来为8
innodb_thread_concurrency=64
#query_cache_size=0 #默认为32M mysql8.0不支持 5.6 5.7可开启
#query_cache_limit=0 #默认为1M mysql8.0不支持 5.6 5.7可开启
myisam_sort_buffer_size=128M #原来为64
############innoDB###################
innodb_data_home_dir=/data/mysqldb/innodb
innodb_data_file_path=ibdata1:200M;ibdata2:10M:autoextend
innodb_log_group_home_dir=/data/mysqldb/innodb
innodb_buffer_pool_size=64M #默认为384M
innodb_buffer_pool_instances=3 #默认没有这个参数
#innodb_additional_mem_pool_size=32M #原来为20此参数在mariadb10.4和10.3和mysql5.7中不支持其他版本可开启
innodb_log_file_size=256M #原来为100
innodb_log_buffer_size=64M #原来为8
innodb_log_files_in_group=4
innodb_flush_log_at_trx_commit=2
innodb_lock_wait_timeout=10 #默认为50
#innodb_file_io_threads=4 #默认没有该参数 5.6可以开启
innodb_file_per_table=1
innodb_read_io_threads=16 #默认没有该参数
innodb_write_io_threads=16 #默认没有该参数
innodb_io_capacity=1000 #默认没有该参数
#innodb_file_format=Barracuda #此参数会在mariadb10.3.13中提示没有作用以后版本将删除
innodb_flush_method=O_DIRECT
#innodb_lru_scan_depth=4096 #此参数在mariadb5.5中不支持所以禁用在mysql5.6以上可开启
[mysqldump]
quick
max_allowed_packet=16M
[mysql]
default-character-set=utf8mb4
no-auto-rehash
[mysqld_safe]
log-error=/data/mysqldb/3306/mysql-error.log #5.7中目录位置只能在这 5.6可以修改其他目录
pid-file=/data/mysqldb/run/mysql.pid
[myisamchk]
key_buffer_size=256M
sort_buffer_size=256M
read_buffer=2M
write_buffer=2M
[mysqlhotcopy]
interactive-timeout
........................................................................................................................
一: Mysql 备份及还原
1. 使用mysqldump工具 备份及还原
# mysqldump -uroot -p --databases teodb1 --lock-all-tables --master-data=2 > /data/20200113-teo.sql \\ 备份单个数据库
# mysql < 20200113-teo.sql \\ 还原 备份的记录 不重要的还原到此即可 主机备份 新机器还原 \\ ↑ 默认为标准输出 重定向至文件
# tail 20200113-teo.sql \\ 可以看到 此备份的 二进制文件的位置 可以备份从此位置到 当前时间的数据
HANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=850;
# mysqlbinlog --start-position=850 /data/mysql/mysql-bin/mysql-bin.000002 > incre.sql \\ 通过二进制日志 备份更新的数据
# mysql < incre.sql \\ 在还原 备份记录的时间 到当前时间的数据 新机
注:
❶. mysqldump 工具详解
--lock-all-tables 锁定所有数据库的所有表 备份所有库建议使用此命令 支持MyISAM和InnoDB 实现温备
--lock-tables 锁定要备份数据库的所有表 备份单个数据库建议使用此命令 支持MyISAM和InnoDB 实现温备3
--single-transaction 只对InnoDB类型的表有效 启动一个线程用于备份 其他线程读写数据并不影响 实现热备 与上两条作用差不多
--databases db1 db2 同时备份两个数据库
--all-databases 同时备份所有数据库
--master-data=2 记录二级制日志的位置 用于从备份时间恢复到当前数据节点时间 1不注释词句不常用 2注释此语句 常用
--flush-logs 锁定表完成后 滚动二进制日志 上一条 二级制日志此时间之前的备份 之后没有备份
--start-position=850 850为备份数据记录二级制文件的位置
-E --events 备份指定数据库相关的所有event scheduler
-R --routines 备份指定数据库相关的所有存储过程和存储函数
--triggers 备份表相关的触发器
❷. 执行备份的时候提示 Binlogging on server not active 二级制日志没有启动
mysql> show global variables like '%log%'; \\ 查看mysql服务器配置信息 有log字样的 可以查看到二进制日志的开启状态
mysql> set global log_bin=1 \\ 设置 二进制日志 开启 如不成功修改配置文件
# vim /etc/my.cnf
log_bin=/data/mysql/mysql-bin/mysql-bin \\ 添加此项 开启二进制日志 新建目录
mysql> show master logs; \\ 查看当前使用的二进制日志 最好不要自己手动删除二级制日志
❸. 还原的时候最好关闭二进制日志 否则会产生没必要的大量二进制日志 只对当前会话关闭方法如下 其他会话或关闭此会话仍为开启状态
mysql> show variables like '%log%'; \\ 查看sql_log_bin状态
mysql> set sql_log_bin=0; \\ 0为关闭
mysql> show variables like '%log%'; \\ 只对当前会话有效 在此会话进行还操作
mysql> use yourdatabasename; \\ 还原操作
mysql> set names utf8;
mysql> source /tmp/database.sql; \\ 注意存放路径 不能存在root下 mysql使用mysql自己用户没有权限
❹. 二进制日志文件不应该与数据文件放在同一磁盘
二级制日志文件至关重要 不应该手动删除
2. 使用XteraBackup工具 备份及还原
# yum install percona-xtrabackup-24-2.4.18-1.el7.x86_64.rpm \\ 安装 需依赖libev包 如果没有 安装epel源即可
# rpm -ql percona-xtrabackup
# mysql \\ 仅对Innodb引擎支持热备支持增量 对应MyISAM支持温备不支持增量
# mysql> show table status\G; \\ 看一下存储引擎 Engine: InnoDB是Innodb还是MyISAM
# mysql> show global variables like 'innodb_file%'; \\ 查看 innodb_file_per_table 单独表空间 是否开启 如未开启见注解
# mysql> show binary logs; \\ 查看 二进制日志
# innobackupex --user=root --password=123456 /data/backups/ \\ --user数据库用户名 备份到的位置 做全数据库备份
# cat /data/backups/xtrabackup_checkpoints \\ 查看此文件
backup_type = full-backuped \\ 完全备份
from_lsn = 0 \\ 从0号
to_lsn = 1614406 \\ 到1614406号
# scp -r /data/backups/2020-01-20_13-43-50/ 192.168.10.11:/data/backups \\ 复制到测试机器 还原 也需要装xtrabackup工具
全数据库还原
# innobackupex --apply-log /data/2020-01-20_13-43-50/ \\ 在需要还原的主机 做整理
# innobackupex --copy-back /data/2020-01-20_13-43-50/ \\ 还原 需要关闭数据库且删除整个数据库目录
# chown -R mysql.mysql /data/msyql/ \\ 还原属主是root 改权限
# systemctl restart mysqld \\ 如启动不起来 看是否是因为/ib_logfile0 此文件大小 可删除
做增量备份
# innobackupex --user=root --password=123456 --incremental /data/backups/ --incremental-basedir=/backups/2020_13-43-50
# cat /data/backups/2020-01-20_13-53-04/xtrabackup_checkpoints \\ ↑ 基于上一个备份做增量备份
backup_type = incremental \\ incremental 指明本次备份为增量备份
from_lsn = 1614406 \\ 从1614406号开始
to_lsn = 1615481
还原 备份+增量备份
# innobackupex --apply-log --redo-only /data/backups/2020-01-20_13-43-50/ \\ 整理第一个完全备份
# innobackupex --apply-log --redo-only /data/backups/2020-01-20_13-43-50/ --incremental-dir=/data/backups/2020_13-53-04/
# cat /data/backups/2020-01-20_13-43-50/xtrabackup_checkpoints \\ ↑ 整理第一个增量备份 合并到完全备份 有多个 继续执行
backup_type = log-applied \\ 显示为 日志增加
from_lsn = 0
to_lsn = 1615481 \\ 结束编号变为最新增量备份的编号
# rm -r /data/mysql \\ 还原需要 数据库目录为空
# innobackupex --copy-back /data/backups/2020-01-20_13-43-50
# chown -R mysql:mysql /data/mysql/ \\ 改变属主权限
注:
❶. 对于使用XteraBackup工具备份 单独表空间没有开启的处理
# mysqldump --all-databases --lock-all-tables > /data/teo1-19all.sql \\ 备份所有数据
# systemctl stop mysqld;
# vim /etc/my.cnf
innodb_file_per_table = on \\ 加入配置文件
# rm -rf /data/mysql/* \\ 删除数据库后 需要做初始化
# rm -rf /data/binlogs/*
# mysql < /data/teo1-19all.sql \\ 还原以前的数据
# systemctl restart mysqld;
❷. 工具下载地址 及版本说明
对于mysql5.5要使用 2.4版本的下载地址 https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/
percona-xtrabackup-24-2.4.18-1.el7.x86_64.rpm 下载此包
对于最新8.0版本只对mysql8.0以上的有效 在8.0中移除innobackupex工具
❸. 备份工具的选择
mysqldump+复制binlog二进制日志
1 mysqldump完全备份
2 复制binlog中指定时间范围的event 增量备份
lvm2快照+复制binlog
1 lvm2 快照 使用cp或tar等做物理备份 完全备份
2 复制binlog中指定时间范围的event 增量备份
xtrabackup
1 由percona提供的支持对InnoDB做备份(物理备份)的工具
2 完全备份 增量备份
二: Mysql 5.6.47 编译安装
# rpm -qa | grep mariadb
# rpm -qa | grep mysql
# yum remove mariadb-libs.x86_64
# yum install openssl-devel libxml2-devel bzip2-devel libmcrypt-devel libmcrypt php-mcrypt mcrypt
# yum install libarchive-devel boost boost-devel lsof wget gcc gcc-c++ make cmake perl kernel-headers kernel-devel pcre-devel
# yum install libaio libaio-devel bison bison-devel zlib-devel openssl openssl-devel ncurses ncurses-devel libcurl-devel
# mkdir -p /data/soft
# cd /data/soft
# tar zxf mysql-5.6.47.tar.gz
# groupadd mysql
# useradd -g mysql mysql -s /sbin/nologin
# mkdir -p /data/mysqldb/{3306,innodb,mysql-bin,logs,run,relay-bin}
# chown -R mysql:mysql /data/mysqldb
# cd mysql-5.6.47
# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_UNIX_ADDR=/data/mysqldb/run/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all -DWITH_MYISAM_STORAGE_ENGINE=1 -DMYSQL_TCP_PORT=3306 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_DATADIR=/data/mysqldb
# make
# make install
# chown -R root:mysql /usr/local/mysql
# mkdir /etc/mysql \\ 复制进去my.cnf
# vim /etc/mysql/my.cnf
# vim /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH
# source /etc/profile
# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysqldb/3306
# cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld
# chkconfig --add mysqld
# chkconfig mysqld on
# systemctl restart mysqld
# mysql \\ 如果提示找不到mysql.sock 可以用-S指明路径 -S /data/mysqldb/run/mysql.sock
mysql> use mysql
mysql> select user,host,password from user;
# /usr/local/mysql/bin/mysql_secure_installation \\ 设置密码y 删除匿名用户y 禁止root远程登录n 删除测试数据库y 重载授权表y
对于二进制安装包 安全初始化 修改了mysql.sock路径 会提示找不到路径 需要修改此文件
# vim /usr/local/mysql/bin/mysql_secure_installation
write_file($config,
...
...
"connect-expired-password", \\ 此项去掉 ")" 改成","
"socket=/data/mysqldb/run/mysql.sock"); \\ 大概在157行 插入此语句即可
}
注:
源码下载地址 https://cdn.mysql.com//Downloads/MySQL-5.6/mysql-5.6.47.tar.gz
二进制下载地址 https://cdn.mysql.com//Downloads/MySQL-5.6/mysql-5.6.47-linux-glibc2.12-x86_64.tar.gz
下载官方网站 https://dev.mysql.com/downloads/mysql/5.6.html
三: linux自动备份数据库 windows与linux实现自动同步文件
linux 服务器
# yum install rsync \\ 自动同步工具服务器端
# vim /etc/rsyncd.conf
uid = www
gid = www
port = 873
use chroot = no
max connections = 10
hosts allow = 47.91.219.64
timeout = 20 \\ 超时时间20秒
lock file = /var/run/rsync.lock
log file = /var/log/rsyncd.log
ignore errors
[mysql_bak]
path = /data/backup/mysql
read only = no
# useradd -g www www -s /sbin/nologin
# chown -R www.www data/backup/mysql \\ 需要给目录权限 属主给www
# rsync --daemon \\ centos 6 启动服务 873端口被监控
# pkill rsync \\ 停止服务
# ss -tnl \\ 63873端口被监控 默认为873端口
# echo "/usr/bin/rsync --daemon" >> /etc/rc.d/rc.local \\ 加入开机启动
# chmod +x /etc/rc.d/rc.local \\ 我开机启动文件添加执行权限
# iptables -I INPUT 4 -m state --state NEW -p tcp --dport 873 -j ACCEPT \\ 防火墙放行
# chmod +x Mysql_bak.sh \\ mysql自动备份脚本
# crontab -e
0 1 * * * /data/shell/backup.sh & \\ 每天的1点备份
windows客户端
解压程序 cwrsync_5.5.0_x86_free 客户端
执行cwrsync.cmd 安装
执行mysql-bak.bat 可以实现远程备份
cd c:\
C:\rsync\bin\rsync.exe -avzP --delete www@47.91.219.64::mysql_bak ./db/
在系统中添加定时任务 自动执行mysql-bak.bat文件即可
四: 添加数据库systemctl启动管理
# vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MariaDB database server
After=syslog.target
After=network.target
#
[Service]
Type=simple
User=mysql
Group=mysql
#
ExecStart=/usr/local/mysql/bin/mysqld_safe --basedir=/usr/local/mysql
TimeoutSec=300
#
PrivateTmp=false
#
[Install]
WantedBy=multi-user.target
# systemctl daemon-reload \\ 重新读取
# systemctl restart mysqld \\ 如果停止不了 杀掉在启动 # killall mysqld
五: 数据库 备份基础
查询日志 query log 一般不开启 general_log=off
慢查询日志 slow query log
错误日志 error log
二进制日志 binary log
中继日志 reley log
事务日志 transaction log
物理备份 逻辑备份
物理备份 直接复制数据文件进行备份
逻辑备份 从数据库中 导出 数据另存而进行的备份
备份什么
数据 配置文件
二进制日志 InnoDB的事务日志
代码(存储过程 存储函数 触发器 事件调度器)
设计备份方案
数据集 完全+增量
备份手段 物理 逻辑
备份工具
mysqldump 逻辑备份工具 使用所有存储引擎,备份: 完全备份 部分备份 对InnoDB存储引擎支持热备
cp tar等 复制归档攻击: 是物理备份工具 适用于所有存储引擎 冷备 完全备份 部分备份
lvm2 逻辑卷管理 使用lvm2快照 几乎热备 借助于文件系统管理工具进行备份
mysqlhotcopy 几乎冷备 仅适用于MyISAM存储引擎
备份分类
完全备份 增量备份 差异备份
物理备份 逻辑备份
完全备份 部分备份
热备 温备 冷备
冷备：读写操作均不可进行
温备：读操作可执行 但写操作不可执行
热备：读写操作均可执行
MyISAM：温备，不支持热备 支持温备 锁定备份库 而后启动备份操作
InnoDB：都支持 支持热备
ibd文件 是表空间 在配置文件中加 innodb_file_per_table = on 开启独立表空间
frm文件 是表结构
六 存储引擎介绍
1. Innodb
存储引擎 表级别 可以单个制定某个表使用什么存储引擎
innodb 处理大量的短期事务
1 所有innodb表的数据和索引放置于同一个表空间中 备份 恢复 管理 极为不便
表空间文件:datadir定义的目录下
数据及索引文件: ibddata1,ibddata2,...
2 每个表单独使用一个表空间存储表的数据和索引
innodb_file_per_table=on
在MariaDB中使用的不是innodb 其实是Xtradb 是innodb的改进 也叫做innodb
MariaDB [(none)]> show global variables like 'innodb_file_%'; \\ 查看是否开启 每表单独表空间 重要
innodb_file_per_table \\ 查看是否开启
MariaDB [(none)]> show engines; \\ 查看默认存储引擎default为默认 及查看所支持的存储引擎
MariaDB [aaa]> create table t1(id int,name char(30)); \\ 创建两张表
MariaDB [aaa]> create table t2(id int,name char(30));
在数据库目录中会看到t1.ibd t2.ibd 为表空间文件
t1.ibd 数据及索引文件
t1.frm 表格式定义文件
2. MyISAM
支持全文索引、压缩、空间函数、但不支持事务,且为表级锁
崩溃后无法完全恢复
MariaDB [mysql]> use mysql;
MariaDB [mysql]> show table status; \\ 可以查看此数据库的所有表的存储引擎
MariaDB [mysql]> show table status\G;
user.frm 表格式定义文件 \\ 每个表三个文件
user.MYD 数据文件
user.MYI 索引文件
七: 日志
1. 二进制日志
定义:导致数据改变或潜在导致数据改变的SQL语句 关闭在启动不会使用之前的那个二进制日志 会+1
功能: 用于通过"重放"日志文件中的事件来生成数据副本
mysql> show global variables like '%log%' \\ 查看全局变量 带有log字样的
mysql> help show; \\ 查看show都有哪些用法
mysql> show master logs; \\ 查看mysql自行管理使用中的二进制日志文件列表
mysql> show master status; \\ 查看当前正在使用的二进制文件
mysql> show binlog events in 'mysql-bin.000002'; \\ 查看二进制文件 不要用文本打开 有可能会损坏
# mysqlbinlog /data/mysqldb/mysql-bin/mysql-bin.000002 \\ 工具 打开二进制日志 下面有格式
--start-position=328 \\ 从328开始显示
--stop-position=1200 \\ 到1200结束
--start-datetime= \\ 开始时间 没试
--stop-datetime= \\ 结束时间 没试
# at 210 \\ 以下为二进制格式及详解
#200128 16:09:38 server id 1 end_log_pos 300 CRC32 0x3c5b3220 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1580198978/*!*/;
repair table users
/*!*/;
事件发生的日期和时间: 2001258 16:09:38
事件发生的服务器标识: server id 1
事件的结束位置: end_log_pos 300
事件的类型: Query
事件发生时所在服务器执行此事件的线程的ID: thread_id=6
语句的时间戳与将其写入二进制文件中的时间差: exec_time=0
错误代码: error_code=0
事件内容:
GTID: 专属属性GTID 在mysql5.6 mariadb10.0以上版本有
二进制日志文件的构成
日志文件 mysql-bin.0000001 \\ 二进制格式
索引文件 mysql-bin.index \\ 文本格式 二进制索引文件
配置文件中关于二进制日志的设置
sql_log_bin=on \\ 是否记录二进制日志
log-bin=/data/mysqldb/ \\ 记录的位置
max_binlog_size=1073741824 \\ 单个二进制日志文件的最大体积 默认为1G
expire_logs_days=0 \\ 0为不启用此功能 超过多少天的二进制文件删除
sync_binlog=0 \\ 设定是否启动二进制日志同步功能
二级制日志记录格式
1 基于语句记录 statement 数据量小 节约空间
2 基于行记录 row 精确记录 会产生大量数据量
3 混合模式 mixed 让系统自行判定
2. 错误日志:
mysqld启动和关闭过程中输出的事件信息
mysql的运行中产生的错误信息
event scheduler运行一个event时产生的日志信息
在主从复制架构中的从服务器上启动从服务器线程时产生的信息
log-error = /data/mysqldb/logs/mysql-error.log
log_warnings =1 \\ 是否记录警告信息至错误日志文件中 默认为记录
3. 查询日志:
定义: 记录查询操作
general_log=off 一般都为关闭 不开启
4. 慢查询日志:
定义: 执行时长超出指定时长的查询操作
slow_query_log = ON
5. 中继日志:
定义: 复制架构中,从服务器用于保存主服务器的二进制日志中读取到的事件
6. 事件日志:
定义: 事务型存储引擎自行管理和使用
innodb_data_home_dir = /data/mysqldb/innodb \\ 事务日志目录
innodb_log_group_home_dir = /data/mysqldb/innodb \\ 事务日志文件组目录
ib_logfile0 事务日志文件
ib_logfile1 事务日志文件
八: mysql 服务器的主从复制相关
1. mysql 主从复制
主服务器端
# vim /etc/mysql/my.cnf \\ 主服务器需要以下❸点
log-bin=/data/mysqldb/mysql-bin/mysql-bin \\ ① 需要开启二进制日志
innodb_file_per_table = 1
skip_name_resolve=on
server-id = 1 \\ ② 需要server id 唯一
sync_binlog=on \\ 默认关闭 控制数据库的二进制日志刷到磁盘上去。 设定是否启动二进制日志同步功能
innodb_flush_logs_at_trx_commit=on \\ 默认关闭
innodb_support_xa=on \\ 默认配置文件中需要添加 建议开启 以上三条 保证事安全性
sync_master_info = on \\ 开启会给磁盘带来压力 酌情处理 给从节点发送事件的时候 是否立即同步到磁盘上 让从节点立即获取到
# mysql
mysql> show global variables like '%server%'; \\ 可以看到 server id
mysql> show master logs; \\ 可以看到当前使用的二进制日志的号码120
mysql> grant all privileges on *.* to 'teo3'@'%' identified by '123456'; \\ ③ 创建用户或给所有的权限 最好做ip授权
从服务器端
# vim /etc/mysql/my.cnf
relay-log=/data/mysqldb/relay-bin/relay-bin \\ 开启中继日志 二进制日志可开 可不开
relay-log-index=relay-log.index
innodb_file_per_table = 1
skip_name_resolve=on
server-id = 2 \\ server id 不同
read_only = on \\ 第一种开启只读 但此限制对拥有super权限的用户均无效 如root
skip_slave_start = on \\ 数据库启动 不自动启动复制线程 为了安全 需要手动启动
sync_relay_log = on \\ 开启会给磁盘带来压力 酌情处理 是否立即同步到磁盘
sync_relay_log_info = on \\ 开启会给磁盘带来压力 酌情处理 是否立即同步到磁盘
# mysql
mysql> change master to master_host='192.168.10.13',master_user='teo3',master_password='123456',master_port=63306,
master_log_file='mysql-bin.000003',master_log_pos=120; \\ 详解 注②
mysql> show slave status\G; \\ 查看自己的从状态 很重要 如果没有报错就可以了
mysql> start slave; \\ 启动 从节点复制线程
mysql> stop slave; \\ 停止 从节点复制线程
mysql> show global variables like 'read_only'; \\ 查看只读是否开启
mysql> flush tables with read lock; \\ 第二种开启只读权限 运行一个线程运行此命令 永久不关闭 可实现所有用户只读
注:
❶. Mysql数据库在由Mssql数据库导入数据文件后出现 “ERROR 1036 (HY000): Table 'xxxx' is read only
# mysqladmin -p flush-tables 进入数据库在试试 否则去查看data目录权限
❷. mysql> help change master to 切换为主节点为
master_bind = 'interface_name'
master_host = 'host_name' 主节点主机名
master_user = 'user_name' 主节点用户
master_password = 'password' 主节点密码
master_port = port_num 主节点端口
master_connect_retry = interval 如果连接不上 多长时间重试
master_heartbeat_period = interval 多长时间做心跳信息监测
master_log_file = 'master_log_name' 从主节点哪一个二进制文件开始复制
master_log_pos = master_log_pos 从此二进制文件的哪个位置开始复制
relay_log_file = 'relay_log_name' 复制完后保存自己的哪一个中继日志中 有默认
relay_log_pos = relay_log_pos 而且保存的位置 有默认 一般不需要制定
master_ssl = {0|1} ssl 相关
master_ssl_ca = 'ca_file_name'
master_ssl_capath = 'ca_directory_name'
master_ssl_cert = 'cert_file_name'
master_ssl_key = 'key_file_name'
master_ssl_cipher = 'cipher_list'
master_ssl_verify_server_cert = {0|1}
ignore_server_ids = (server_id_list) 忽略哪些server-id上的事件
❸. 主从复制中的 relay-log.info 与 master.info 详解
# cat relay-log.info \\ 记录主从复制的 从服务器的信息
/data/mysqldb/relay-bin/relay-bin.000002 \\ 本地的哪个中继日志
586 \\ 本地的中继日志的哪个号
mysql-bin.000003 \\ 远程的哪个二进制文件
302 \\ 复制到哪个号
# cat master.info \\ 记录着主从复制的 主服务器的信息
18
mysql-bin.000003 \\ 复制的哪一个二进制文件
302 \\ 复制到哪一个号
192.168.10.13 \\ 主服务器
teo3 \\ 账号
123456 \\ 密码
63306 \\ 端口
60
0
❹. 如果主节点已经运行了一段时间 且有大量数据时 如何配置并启动从节点
应该通过备份恢复数据至从服务器
复制起始位置为备份时,二进制日志文件及其 号码
2. mysql 主主复制 \\ 此实验未做 99%可以成功
第一个主服务器端
# vim /etc/mysql/my.cnf
log-bin=/data/mysqldb/mysql-bin/mysql-bin \\ ① 需要开启二进制日志
relay-log=/data/mysqldb/relay-bin/relay-bin \\ ② 同时需要开启中继日志
innodb_file_per_table = 1
skip_name_resolve=on
server-id = 1 \\ ③ 需要server id 唯一
auto_increment_offset=1 \\ 从1开始 避免与另一台冲突
auto_increment_increment=2 \\ 自动增长为2
# mysql
mysql> grant all privileges on *.* to 'teo3'@'%' identified by '123456'; \\ ④ 创建用户或给所有的权限 最好做ip授权
mysql> fulsh privileges; \\ 刷新授权
mysql> show master status; \\ 看清楚二进制文件 处于什么位置
mysql> change master to master_host='192.168.10.13',master_user='teo3',master_password='123456',master_port=63306,
master_log_file='mysql-bin.000003',master_log_pos=120;
mysql> show slave status\G; \\ 查看自己的从状态 很重要 如果没有报错就可以了
mysql> start slave; \\ 配置完后 一起启动
第二个主服务器端
# vim /etc/mysql/my.cnf
log-bin=/data/mysqldb/mysql-bin/mysql-bin \\ ① 需要开启二进制日志
relay-log=/data/mysqldb/relay-bin/relay-bin \\ ② 同时需要开启中继日志
innodb_file_per_table = 1
skip_name_resolve=on
server-id = 2 \\ ③ 需要server id 唯一
auto_increment_offset=2 \\ 从2开始 避免与另一台冲突
auto_increment_increment=2 \\ 自动增长为2
# mysql
mysql> grant all privileges on *.* to 'teo3'@'%' identified by '123456'; \\ ④ 创建用户或给所有的权限 最好做ip授权
mysql> fulsh privileges; \\ 刷新授权
mysql> show master status; \\ 看清楚二进制文件 处于什么位置
mysql> change master to master_host='192.168.10.14',master_user='teo3',master_password='123456',master_port=63306,
master_log_file='mysql-bin.000003',master_log_pos=120;
mysql> show slave status\G; \\ 查看自己的从状态 很重要 如果没有报错就可以了
mysql> start slave; \\ 配置完后 一起启动
注:
❶. 每一个节点都要启动中继日志 都要启动二进制日志 并且互为主从
❷. 主主复制 从初始化数据库就应该规划好
3. mysql半同步复制 保证有一个从节点收到并在本地存储完数据 返回给主节点 在返回给客户端 \\ 此实验未做 99%可以成功
㊣首先配置主从复制 不要执行start slave; 不要启动先
主节点
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; \\ 安装插件
mysql> show plugins \\ 可以查看可用的插件 是否有刚安装的
mysql> show global variables like '%semi%';
rpl_semi_sync_master_enabled | OFF | \\ 启动半同步复制的主节点 默认关闭
rpl_semi_sync_master_timeout | 10000 | \\ 等待从节点 超时时间 默认10秒 1万毫秒
rpl_semi_sync_master_trace_level | 32 | \\ 跟踪级别 使用默认就好
rpl_semi_sync_master_wait_no_slave | ON \\ 没有 从节点时 要不要等待 默认等待
mysql> set global rpl_semi_sync_master_enabled=1; \\ 设置半同步复制的主节点 开启
mysql> show global variables like '%semi%'; \\ 此时显示为on
mysql> show status like '%semi%' \\ 查看相关状态 从节点也启动完成 clients显示1
测试
mysql> create database teo4; \\ 创建数据库
mysql> show status like '%semi%' \\ 查看状态 等待时间等
从节点 建议只配置一个从节点
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; \\ 安装插件
mysql> show plugins \\ 可以查看可用的插件 是否有刚安装的
mysql> show global variables like '%semi%';
rpl_semi_sync_slave_enabled | OFF | \\ 启动半同步复制的从节点 默认关闭
rpl_semi_sync_slave_trace_level | 32 |
\\ 跟踪级别 使用默认就好
mysql> set global rpl_semi_sync_slave_enabled=1; \\ 设置半同步复制的从节点 开启
mysql> show global variables like '%semi%'; \\ 此时显示为on
mysql> start slave; \\ 启动 从节点复制线程
mysql> show slave status\G \\ 查看自己线程运行是否正常
4. 复制过滤器
让从节点仅复制指定的数据库,或指定数据库的指定表
1 主服务器仅向二进制日志中记录与特定数据库(特定表)相关的事件
问题:时间还原无法实现 不建议使用 \\ 实现方式 配置文件中开启
binlog_do_db \\ 白名单 只记录哪些数据库 ,隔开就可以
binlog_ignore_db \\ 黑名单 不要同时使用
2 从服务器 在从放中继日志中的事件时,仅读取与特定数据库(特定表)相关的事件并应用于本地
问题:会造成网络及磁盘IO浪费 \\ 实现方式 配置文件中开启
replicate_do_db \\ 要复制数据库的白名单
replicate_ignore_db \\ 要复制数据库的黑名单
replicate_do_table \\ 表的白名单 一般不会做此级别
replicate_ignore_table \\ 表的黑名单
replicate_wild_do_table \\ 表通配符白名单
replicate_wild_ignore_table \\ 表通配符黑名单
开启流程
mysql> show global variables like 'replicate%';
mysql> stop slave;
# vim /etc/mysql/my.cnf
replicate_do_db=teo3; \\ 配置文件中开启 仅仅复制teo3数据库
# systemctl restart mysqld
mysql> show slave status\G \\ replicate_do_db 中有teo3
5. 基于ssl复制 \\ 实验未做 关键参数如下
主服务器端
mysql> grant all privileges on *.* to 'teo3'@'%' identified by '123456' require ssl; \\ 在授权用户时需要加 授权ssl选项
从服务器端 在配置文件中开启相关选项
mysql> help change master to \\ 查看配置文件中ssl的相关配置
| MASTER_SSL = {0|1} 是否开启ssl
| MASTER_SSL_CA = 'ca_file_name' 指明ca的证书 与下一条指明一个就可以
| MASTER_SSL_CAPATH = 'ca_directory_name' 或者指明目录 认可多个CA的话
| MASTER_SSL_CERT = 'cert_file_name' 自己的证书
| MASTER_SSL_KEY = 'key_file_name' 自己的私钥
| MASTER_SSL_CIPHER = 'cipher_list' 加密方式
| MASTER_SSL_VERIFY_SERVER_CERT = {0|1} 是否验证服务器端证书
| IGNORE_SERVER_IDS = (server_id_list)
mysql> show global variables like '%ssl%'; \\ 查看是否支持 如果不支持需要在重新编译安装的时候 编译进去
have_openssl 是否支持 编译时是否开启
have_ssl 是否开启 在配置文件中开启
注:
❶. 清理二进制日志 不要手动清理
mysql> show binary logs;
mysql> purge binary logs to 'mysql-bin.000003'; \\ ❸之前所有的内容会都删除 一定要先备份 在mysql-bin.index也自动会删除
❷. 常用的显示命令
show master status 查看当前使用的二进制日志文件
show binlog events in 'mysql-bin.000002'; 查看二进制文件内容 不要用文本打开 有可能会损坏
show binary logs 显示 所有二进制日志
show process list 显示 复制线程
show slave status 显示 自己的从状态
seconds_behind_master 0 在从服务器中 查看是否落后于主服务器
❸. 如何确定主从节点数据是否一致
percona-tools 包里有 待查询使用
❹. 主从数据不一致如何修复
重新复制 舍弃一个
九: 监控服务器恢复流程
# gzip -d Moniterdb.sql.gz
# mysql -uroot -p < Moniterdb.sql \\ 很长时间
Monitor!@#$2016
# mysql -uroot -p
CREATE DATABASE Moniterdb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
grant all privileges on *.* to 'root'@'47.91.219.64' identified by 'Monitor!@#$2016'; \\ 授权
grant all privileges on Moniterdb.* to 'Monitor'@'47.91.219.64' identified by 'Monitor!@#$2016';\\ 授权主服务器
grant all privileges on Moniterdb.* to 'Monitor'@'47.91.219.64' identified by 'Monitor!@#$3344'; \\ 授权所有节点服务器
grant all privileges on Moniterdb.* to 'Monitor1'@'localhost' identified by 'Monitor2020'; \\ 自动备份脚本使用
十: mysql 常用命令
# /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/mysql \\ 指明 用户 路径 创建基础数据
# /usr/local/mysql/bin/mysql_secure_installation \\ 安全初始化 需数据库运行 管理员现在密码 设置管理员密码
\\ ↑ 删除匿名用户 禁止管理员远程登录 删除测试数据库 重载授权
mysql> show databases; \\ 查看数据库
mysql> use mysql \\ 使用 mysql 数据库
mysql> show tables; \\ 查看所有表
mysql> select user,host,password from user; \\ 查询 user表中 有哪些用户
mysql> create database hahadb; \\ 创建数据库
mysql> grant all privileges on hahadb.* to haha@'%' identified by '123456'; \\ 创建用户并赋予用户某数据库的所有权限
mysql> flush privileges; \\ 刷新授权
mysql> drop user haha@'%'; \\ 删除用户
delete from user where user='haha'and host='%'; \\ 删除用户 mysql
mysql> drop database hahadb; \\ 删除数据库
mysql> status \\ 显示数据库版本等信息 是客户端命令
mysql> help \\ 显示所有客户端命令 不用输入分号
mysql> select version(); \\ 查看服务器端 版本
mysql> show grants for haha@'%' \\ 查看某一用户的权限
mysql> create user haha@'%' identified by '123456' \\ 创建用户
mysql> set password for haha@'%'=password('654321'); \\ 修改密码 某一用户
mysql> select * from user\G \\ 以每行的方式 显示字段
mysql> select host,user,password from user \\ 从表中查看 其中三个字段
mysql> create database teo1; \\ 创建数据库
mysql> use teo1; \\ 使用此数据库
mysql> show tables; \\ 查看所有表
mysql> select * from student2; \\ 查看表中数据库
mysql> create table test1 (di int); \\ 创建表
mysql> insert into test1 values (1),(10),(99); \\ 插入数据
mysql> select * from test1; \\ 查询表
mysql> drop table teacher; \\ 删除表
mysql> drop database teo1; \\ 删除数据库
mysql> show global status; \\ 查询 统计命令 执行多少某一命令
mysql> show master logs; \\ 查看有多少二进制日志文件
mysql> show master status; \\ 查看当前使用的二进制日志文件
mysql> show binlog events
mysql> show engines; \\ 查看当前数据库支持哪些 存储引擎
mysql> grant select on *.* to aaa@'%' identified by "123456"; \\ 创建用户aaa并赋予 所有数据库的所有表的 查询权限
mysql> grant select on *.* to aaa@'localhost' identified by "123456"; \\ % 所有地址登录 localhost 本地登录
备忘录:临时添加的备份数据库的用户
MYSQL_USER="Monitor1"
MYSQL_PASSWORD='Monitor2020'
如何获取程序默认使用的配置:
# mysql --print-defaults \\ 查看mysql默认使用的配置
--no-auto-rehash \\ 禁用了此项 无法使用使用命令行补全功能 此功能缺陷先做个表 连入会慢 所以默认禁用
# mysqld --print-defaults \\ 可查看一些路径 套接字 文件目录等
客户端类应用程序的可用选项
-u --user= \\ 指明用户
-h --host= \\ 连入哪个主机 省略表示localhost
-p --password= \\ 指明密码
-P --port= \\ 指明连入端口
--protocol= \\ 指明连入协议 tcp sock 通常tcp
-S --socket= \\ 只能本机连接 才能使用套接字
-D --database= \\ 连入数据库 指明为默认数据库
-C --compress \\ 连接服务器端时 是否把所有数据传入传出过程做压缩 有此项 要压缩
# mysql -e "show databases;" \\ 可以不连入mysql 直接执行mysql命令 可以用到脚本中 执行mysql命令
其他常用命令
# mysqld --verbose --help | less \\ 获取mysqld可用参数列表
# mysql --verbose --help | less \\ 获取mysql可用参数列表
# sed -i 's/MyISAM/InnoDB/g' teodb.sql \\ 把里面所有MyISAM改成InnoDB 此命令可以把备份文件的存储引擎做出修改
# mysqldump -uizumi -p vpn users > 1.sql \\ 导出 vpn 数据库中的 users表
十一. 关于mysql数据库密码
1. 修改数据库root密码
mysql> use mysql \\ 选择数据库
mysql> update user set password=password("123456") where user="root"; \\ 修改root密码
mysql> flush privileges; \\ 设置密码后需刷新授权
2. 忘记数据库root密码
# vim /etc/mysql/my.cnf \\ 编辑主配置文件
skip-grant-tables \\ 免密码登录 下次登录不需要密码
# systemctl restart mysqld \\ 重启数据库 之后可以修改root密码
3. mysql数据库5.7 修改密码
# update mysql.user set authentication_string=password('123456') where user='root'; \\ mysql 5.7 修改密码
# flush privileges;
十二. mysql 忘记密码 或添加新用户
# vim /etc/my.cnf
skip-grant-tables
# systemctl restart mysqld
# mysql
use mysql
flush privileges;
grant all privileges on *.* to 'aaa3'@'%' identified by '123456'; \\ 添加新用户
flush privileges;
select user,Host,password from user; \\ 查看是否有 空用户名的 用户
delete from mysql.user where user=''; \\ 删除 空用户名的 用户
flush privileges;
........................................................................................................................
Msql配置文件 详解
Mysqld 配置文件查找路径: 查找顺序为 都会依次查找 都会生效 值有重复或冲突的 后面的最终生效
/etc/my.cnf --> /etc/mysql/my.cnf --> $MYSQL_HOME/my.cnf --> --default-extra-file=/path/to/somedir/my.cnf --> ~/.my.cnf
$MYSQL_HOME/my.cnf mysql自己的家目录下 一般是安装路径
--default-extra-file 启动mysql进程时会传递一个--default值等于一个路径 指明额外的查找路径
~/.my.cnf 当前用户下的家目录下 隐藏文件
[mysqld] 专用于mysqld这个服务的
[mysqld_safe] 线程安全的mysql专用的信息
[mysqld_multi] 多实例之间可以共享的参数放到此 启动的时候以上三种 通常启动一个
[server] 只要是mysql的服务器端都有效的
[mysql] 专用于mysql客户端
[mysqldump] 专用于mysqldump备份导入导出工具的
[client] 对客户端都有效 用此
[client]
#password = your_password
port = 63306
socket = /data/mysqldb/run/mysql.sock
[mysqld]
port = 63306 \\ 定义端口
socket = /data/mysqldb/run/mysql.sock \\ 套接字
datadir = /data/mysqldb/3306 \\ 数据库目录
log-error = /data/mysqldb/logs/mysql-error.log \\ 错误日志目录
pid_file = /data/mysqldb/run/mysql.pid
skip-external-locking \\ 不使用系统锁定 要使用 myisamchk 必须关闭服务器 避免MySQL的外部锁定 减少出错几率增强稳定性
skip-name-resolve=on \\ 禁止 MySQL 对外部连接进行 DNS 解析 使用这一选项可以消除 MySQL 进行 DNS 解析的时间.但需要注意
\\ ↑ 如果开启该选项 则所有远程主机连接授权都要使用 IP 地址方式 否则 MySQL 将无法正常处理连接请求
character-set-server=utf8 \\ server 级别字符集
collation-server=utf8_bin \\ 字符集配置
default-storage-engine = INNODB \\ 默认存储引擎
wait_timeout= 1800 \\ 超时等待时间 单位秒 即一个connection在若干秒内无响应 则服务器切断与这个客户端的连接
connect_timeout = 30 \\ 客户端与服务器建立连接 服务器返回错误的握手协议前 等待数据包到来的最大时间 单位秒
interactive_timeout = 1800 \\ 关闭连接之前 允许 interactive_timeout（取代了wait_timeout）秒的不活动时间。客户端的会
\\ ↑ 话 wait_timeout 变量被设为会话interactive_timeout 变量的值.如果前端程序采用短连接
\\ ↑ 建议缩短这2个值 如果前端程序采用长连接 可直接注释掉这两个选项 默认配置8小时
back_log = 500 \\ 接受队列 对于没建立 tcp 连接的请求队列放入缓存中 队列大小为 back_log 受限制与 OS
\\ ↑ 参数试图设定back_log高于你的操作系统的限制将是无效的 默认值为50.对于Linux系统
\\ ↑ 荐设置为小于512的整数 如果系统在一个短时间内有很多连接，则需要增大该参数的值
myisam_recover \\ 允许的 GROUP_CONCAT()函数结果的最大长度
event_scheduler =ON \\ 开启了计划任务
server-id = 1 \\ 表示是本机的序号为1 唯一
auto_increment_offset = 1 \\ 表示自增长字段从哪个数开始 指字段一次递增多少 他的取值范围是
auto_increment_increment = 1 \\ 表示自增长字段每次递增的量 指自增字段的起始值 其默认值是1 取值范围是1-65535
transaction_isolation = READ-COMMITTED
#######bin log########
log-bin=/data/mysqldb/mysql-bin/mysql-bin \\ 打开二进制日志功能
binlog_format=mixed \\ binlog日志格式，mysql默认采用statement，建议使用mixed
max_binlog_size = 256M \\ 如果二进制日志写入的内容超出给定值 日志就会发生滚动.你不能将该变量设置为大于1GB或
\\ ↑ 小于4096字节.默认值是1GB.如果你正使用大的事务 二进制日志还会超过max_binlog_size
binlog_cache_size = 2M \\ 在一个事务中 binlog 为了记录 SQL 状态所持有的 cache 大小,如果你经常使用大的,多声明的事务,增加此值来
\\ ↑ 获取更大的性能.所有从事务来的状态都将被缓冲在 binlog 缓冲中然后在提交后一次性写入到binlog中.如果
\\ ↑ 事务比此值大 会使用磁盘上的临时文件来替代.此缓冲在每个连接的事务第一次更新状态时被创建session级别
expire_logs_days=0 #原来为15天 \\ 超过 5 天的 binlog 删除
log-slave-updates \\ 从库做为其他从库的主库时 log-slave-updates参数是必须要添加的
skip_slave_start \\ 备库重启不自动同步
sync_binlog=0 \\ 表示立即同步 控制数据库的binlog刷到磁盘上去。 设定是否启动二进制日志同步功能
relay-log=/data/mysqldb/relay-bin/relay-bin \\ 定义relay_log的位置和名称，如果值为空，则默认位置在数据文件的目录
relay_log_recovery = 1
#####slow log #########
slow_query_log = ON \\ 指定是否开启慢查询日志. 慢查询是指消耗了比 “long_query_time” 定义的更多时间的查询.如果log_long_format
\\ ↑ 被打开.那些没有使用索引的查询也会被记录.如果你经常增加新查询到已有的系统内的话.一般来说这是一个好主意
slow_query_log_file = /data/mysqldb/logs/mysql-slow.log \\ 慢日志存放位置 可以为空 系统会给一个缺省的文件host_name-slow.log
long_query_time = 1
######per_thread_buffers############
max_connections = 3000 \\ 允许的最大连接进程数.如果在访问数据库时经常出现"Too Many Connections"的错误提示 则需要增大该值
max_connect_errors = 5000 \\ 如果某个用户发起的连接 error 超过该数值 则该用户的下次连接将被阻塞 直到管理员执行 flush hosts
\\ ↑ 命令或者服务重启 防止黑客 非法的密码以及其他在链接时的错误会增加此值
key_buffer_size = 384M \\ 指定用于索引的缓冲区大小 增加它可得到更好的索引处理性能 如果是以InnoDB引擎为主的DB 专用于MyISAM引擎的
\\ ↑ key_buffer_size 可以设置较小，8MB 已足够 如果是以MyISAM引擎为主，可设置较大，但不能超过4G.
\\ ↑ 强烈建议不使用MyISAM引擎 默认都是用InnoDB引擎. 注意:该参数值设置的过大反而会是服务器整体效率降低!
max_allowed_packet = 4M \\ 服务所能处理的请求包的最大大小以及服务所能处理的最大的请求大小(当与大的BLOB 字段一起工作时相当必要)
\\ ↑ 每个连接独立的大小.大小动态增加.设置最大包 限制server接受的数据包大小 避免超长SQL的执行有问题
\\ ↑ 当MySQL客户端或mysqld服务器收到大于此数值时将发出'信息包过大'错误 并关闭连接.对于某些客户端
\\ ↑ 如果通信信息包过大 在执行查询期间 可能会遇到“丢失与 MySQL 服务器的连接”错误.默认值 16M.
table_open_cache = 2048 \\ #原来为512
table_definition_cache = 2048 \\ #默认没有该参数
join_buffer_size = 16M \\ 联合查询操作所能使用的缓冲区大小 和 sort_buffer_size 一样 该参数对应的分配内存也是每连接
\\ ↑ 独享此缓冲被使用来优化全联合(full JOINs 不带索引的联合).类似的联合在极大多数情况下有非常
\\ ↑ 糟糕的性能表现, 但是将此值设大能够减轻性能影响.通过 “Select_full_join”状态变量查看全联
\\ ↑ 合的数量 当全联合发生时,在每个线程中分配.
tmp_table_size = 67108864
sort_buffer_size = 16M \\ 查询排序时所能使用的缓冲区大小.排序缓冲被用来处理类似 ORDER BY 以及 GROUP BY 队列所引起的排序.一个用
\\ ↑ 来替代的基于磁盘的合并分类会被使用.查看 “Sort_merge_passes” 状态变量. 在排序发生时由每个线程分配
\\ ↑ 注意：该参数对应的分配内存是每连接独占.如果有 100 个连接 那么实际分配的总共排序缓冲区大小为
\\ ↑ 100 × 6 ＝600MB 所以 对于内存在 4GB 左右的服务器推荐设置为 6-8M.
read_buffer_size = 16M \\ 读查询操作所能使用的缓冲区大小.和 sort_buffer_size 一样 该参数对应的分配内存也是每连接独享
\\ ↑ 用来做 MyISAM 表全表扫描的缓冲大小.当全表扫描需要时 在对应线程中分配
read_rnd_buffer_size = 8M \\ MyISAM 以索引扫描(Random Scan)方式扫描数据的 buffer大小
thread_cache_size = 64 \\ 在 cache 中保留多少线程用于重用.当一个客户端断开连接后,如果 cache 中的线程还少于 thread_cache_size
\\ ↑ 则客户端线程被放入cache 中.可以在你需要大量新连接的时候极大的减少线程创建的开销(一般来说如果你有
\\ ↑ 好的线程模型的话,这不会有明显的性能提升)服务器线程缓存这个值表示可以重新利用保存在缓存中线程的数
\\ ↑ 量,断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求,那么请求将从
\\ ↑ 缓存中读取,如果缓存中是空的或者是新的请求 那么这个线程将被重新创建,如果有很多新的线程,增加这个
\\ ↑ 值可以改善系统性能.通过比较Connections 和 Threads_created状态的变量 可以看到这个变量的作用
\\ ↑ 根据物理内存设置规则如下:1G —> 8 | 2G —> 16 | 3G —> 32 | 大于 3G —> 64
innodb_thread_concurrency = 64 \\ 允许应用程序给予线程系统一个提示在同一时间给予渴望被运行的线程的数量.该参数取值为服务器逻辑
\\ ↑ CPU数量×2.在本例中 服务器有 2 颗物理CPU 所以实际取值为4 × 2 ＝ 8.设置thread_concurrency
\\ ↑ 的值的正确与否 对 mysql 的性能影响很大 在多个 cpu(或多核)的情况下 错误设置
\\ ↑ 了此值 会导致 mysql 不能充分利用多cpu(或多核) 出现同一时刻只能一个cpu(或核)在工作的情况
\\ ↑ thread_concurrency 设为 CPU 核数的 2 倍.比如有一个双核的 CPU 那么thread_concurrency
\\ ↑ 的应该为4; 2 个双核的 cpu,thread_concurrency的值应为8 属重点优化参数
query_cache_size = 0 \\ 指定 MySQL 查询缓冲区的大小。可以通过在 MySQL 控制台执行以下命令观察
query_cache_limit = 0 \\ 不缓存查询大于该值的结果.只有小于此设定值的结果才会被缓冲, 此设置用来保护查询缓冲,
\\ ↑ 防止一个极大的结果集将其他所有的查询结果都覆盖.
myisam_sort_buffer_size = 128M \\ MyISAM 设置恢复表之时使用的缓冲区的尺寸,当在REPAIR TABLE 或用 CREATE INDEX
\\ ↑ 创建索引或 ALTER TABLE 过程中排序 MyISAM 索引分配的缓冲区
############ innoDB ###################
innodb_data_home_dir = /data/mysqldb/innodb \\ 设置此选项是 InnoDB 表空间文件被保存在其他分区.默认保存在MySQL的datadir中
innodb_data_file_path = ibdata1:200M;ibdata2:10M:autoextend \\ InnoDB将数据保存在一个或多个数据文件中成为表空间.只有单个驱动
\\ ↑ 一个单个的自增文件就好了.每个设备一个文件一般都是个好的选择.
innodb_log_group_home_dir = /data/mysqldb/innodb \\ InnoDB 的日志文件所在位置. 默认是 MySQL 的 datadir.
innodb_buffer_pool_size = 2G \\ 包括数据页、索引页、插入缓存、锁信息、自适应哈希所以、数据字典信息.InnoDB 使用一个缓冲池来保存索
\\ ↑ 引和原始数据,设置越大,你在存取表里面数据时所需要的磁盘 I/O 越少.在一个独立使用的数据库服务
\\ ↑ 器上,你可以设置这个变量到服务器物理内存大小的 80%,不要设置过大,否则,由于物理内存的竞争可
\\ ↑ 能导致操作系统的换页颠簸.注意在32 位系统上你每个进程可能被限制在2-3.5G所以不要设置的太高.
innodb_buffer_pool_instances = 3 \\ #默认没有这个参数
innodb_additional_mem_pool_size = 32M \\ 设置InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小 所以当数据库
\\ ↑ 对象非常多的时候 需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率
innodb_log_file_size = 256M \\ 事物日志大小.每个日志文件的大小,应该设置日志文件总合大小到你缓冲池大小的5%~100%,避
\\ ↑ 免在日志文件覆写上不必要的缓冲池刷新行为.一个大的日志文件会增加恢复进程所需要时间.
innodb_log_buffer_size = 64M \\ 用来缓冲日志数据的缓冲区的大小.当此值快满时, InnoDB 将必须刷新数据到磁盘上.由于基
\\ ↑ 本上每秒都会刷新一次,所以没有必要将此值设置的太大(甚至对于长事务而言)
innodb_log_files_in_group = 4 \\ 在日志组中的文件总数.通常来说 2~3 是比较好的.
innodb_flush_log_at_trx_commit = 2 \\ 如果设置为 1 ,InnoDB 会在每次提交后刷新(fsync)事务日志到磁盘上,这提供了完整的 ACID 行为
\\ ↑ 如果你愿意对事务安全折衷, 并且你正在运行一个小的食物, 你可以设置此值到 0 或者 2 来减
\\ ↑ 少由事务日志引起的磁盘 I/O. 0 代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘.
\\ ↑ 2 代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上.
innodb_lock_wait_timeout = 10 \\InnoDB 事务在被回滚之前可以等待一个锁定的超时秒数.InnoDB 在它自己的 锁定表中自动检
\\ ↑ 测事务死锁并且回滚事务.InnoDB 用 LOCK TABLES 语句注意到锁定设置.默认值是50秒
#innodb_file_io_threads = 4 \\ 用来同步 IO 操作的 IO 线程的数量. 此值在 Unix 下被硬编码为 4,
\\ ↑ 但是在 Windows 磁盘 I/O 可能在一个大数值下表现的更好.
innodb_file_per_table = 1 \\ InnoDB为独立表空间模式,每个数据库的每个表都会生成一个数据空间
#innodb_read_io_threads = 16 \\ innodb使用后台线程处理数据页上的读写 I/O(输入输出)请求,根据你的 CPU 核数来更改,默认是4
\\ ↑ 这两个参数不支持动态改变,需要把该参数加入到my.cnf里,修改完后重启MySQL,允许值的范围从 1-64
#innodb_write_io_threads = 16 \\ 同上
#innodb_io_capacity = 1000 \\ #默认没有该参数
innodb_file_format = Barracuda \\ #默认没有该参数
innodb_flush_method = O_DIRECT \\ 用来刷新日志的方法.表空间总是使用双重写入刷新方法.默认值是"fdatasync",另一个是"O_DSYNC".
#innodb_lru_scan_depth = 4096 \\ 此参数在mariadb5.5中不好使 所有禁用 在mysql5.6以上好使
[mysqldump]
quick \\ 支持较大数据库的转储,在导出非常巨大的表时需要此项.增加该变量的值十分安全,这是因为仅当需要时才会分配额外内存.
max_allowed_packet = 128M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
5条评论