mysql 常用

安装

#centos7 安装5.7
yum install https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm #5.7
yum install mysql-community-server #注意安装版本5.7
#centos7 安装5.6
yum install http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
yum install mysql-community-server #注意安装版本5.6
# 8.0 同理

#centos6 安装5.7
yum install http://dev.mysql.com/get/mysql57-community-release-el6-9.noarch.rpm
yum install mysql-community-server
#centos6 安装5.6
yum install http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
yum install mysql-community-server
#或者
yum install http://dev.mysql.com/get/mysql57-community-release-el6-9.noarch.rpm
yum-config-manager --disable mysql57-community
yum-config-manager --enable mysql56-community
yum install mysql-community-server

参数优化

详见 https://github.com/RodrigoViolante/my.cnf.git 根据自己的情况取舍

数据&日志目录设置

[mysqld]
datadir = /data/mysql/data
log_bin=/data/mysql/logs/bin-log/log_bin
slow_query_log_file=/data/mysql/logs/slow-log/slow_log

磁盘选择+文件系统选择

1.磁盘尽量使用ssd,阵列使用raid10
2.文件系统建议使用xfs单独挂载数据磁盘  mount参数添加noatime(不记录access time减少io), nobarrier(不强制刷新底层设备缓存)

日志优化

//查询目前mysql日志相关设置
show global variables like '%log%'; 

//慢日志查询
long_query_time =2 
slow_query_log = 1
slow_query_log_file=/data/mysql/logs/slow-log/slow_log

relay_log//需要在设置主从复制是开启

//设置binlog日志保存时间为30天
expire_logs_days = 30

log_bin=/data/mysql/logs/bin-log/log_bin //binlog日志文件
binlog_format=row // binlog记录格式 {STATEMENT(产生日志较小,但是不能完全记录所有更改),ROW(安全可靠,但是binlog日志会比较大),MIXED(前两种格式的结合) 建议还是设置为row}
sync_binlog=1 //1产生binlog及时刷新到磁盘(能尽可能保证数据一致性,但是影响性能)  0mysql不控制binlog刷新到磁盘由文件系统控制(对应上面文件系统选择)

字符集设置

-- //查看mysql目前使用的字符集  数据库字符集,操作系统字符集,开发项目字符集设置为一致 避免出现乱码 新增表或字段时指定对应字符集
show global variables like '%character%';

-- 设置字符集为utf8
[mysqld]
character-set-server = utf8

备份与恢复

逻辑备份&恢复

-- //适用数据量小的数据备份迁移等,如果数据量比较大建议使用物理备份
mysqldump -u user_name -p123456 database_name > outfile_name.sql //备份单个库

mysqldump -u user_name -p database_name table_name > outfile_name.sql //备份单个库某张表

mysqldump -uroot -p –default-character-set=latin1 –set-charset=gbk –skip-opt database_name > outfile_name.sql //指定字符集备份

//其他参数
-R, –routines: 导出存储过程以及自定义函数
-A, –all-databases: 导出所有数据库 不过默认情况下是不会导出information_schema库
d, –no-data: 只导出表结构，不导出表数据
–opt: 功能等同于同时指定了 –add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset, 以及 –disable-keys这些参数。默认就是启用状态。使用–skip-opt来禁用该参数。
–skip-opt: 禁用–opt选项，相当于同时禁用 –add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset, 及 –disable-keys这些参数。

物理备份&恢复

yum install percona-xtrabackup -y
#使用innobackupex 备份数据库
innobackupex  --password=$PASSWORD --user=$USER --host=$HOST --port=$PORT --stream=tar $TO/$HOSTNAME-db-$date | gzip > $TO/$HOSTNAME-db-$date.tgz
#会生成相关的binlog和position信息（重要）
#恢复数据库  下面2步是必须执行的 网上大多数教程都有点问题
innobackupex --apply-log /data/backup/xxxxxxx
innobackupex --move-back /data/backup/xxxxxxx/

数据验证

1.主从架构 验证pos点是否一致 对于大量数据使用innobackupex 备份会记录相应的pos点
2.其他参考 https://blog.csdn.net/zxb4221v/article/details/61782128

参考链接

逻辑备份 https://www.centos.bz/2018/02/mysqldump-%E5%8F%82%E6%95%B0%E5%92%8C%E4%BD%BF%E7%94%A8%E6%96%B9%E6%B3%95%E4%BB%8B%E7%BB%8D/
文件系统选择 https://www.jb51.net/article/136577.htm