示例使用二进制文件安装部署
安装部署
解压
#tar xzvf mysql-8.0.18-el7-x86_64.tar.gz
配置文件
#cp /etc/my.cnf /etc/my.cnf_bak
#vim /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
basedir = /project/mysql/mysql-8.0.18
datadir = /project/mysql/data
pid-file = /project/mysql/mysql-8.0.18/mysqld.pid
socket = /tmp/mysql.sock
tmpdir = /project/mysql/tmp
user = mysql
#关闭MySQL X plugin（33060）
mysqlx=0
###日志配置------
innodb_log_file_size = 1G
log_error = /project/mysql/mysql-8.0.18/log/error.log
slow_query_log = 1
long_query_time = 5
slow_query_log_file = /project/mysql/mysql-8.0.18/log/slow.log
#记录没有索引导致的慢查询
###连接配置------
#最大连接数
max_connections = 3000
#最大错误连接数
max_connect_errors = 10
#连接闲置超时时间
interactive_timeout = 1800
wait_timeout = 1800
#连接响应超时时间
connect-timeout = 60
###默认配置（插件、字符------
default_authentication_plugin = mysql_native_password
character-set-server = utf8
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
innodb_log_file_size = 1024M
#关闭InnoDB严格模式，防止报错 Row size too large (> 8126)
innodb_strict_mode=0
default-storage-engine=INNODB
bind-address = 0.0.0.0
#域名解析，设置为1之后就不能直接127.0.0.1连接数据库，需要root@127.0.0.1
#skip_name_resolve = 1
#忽略大小写，此项很重要（MySQL8之后的版本必须在初始化就设定此参数，初始化之后配置更改不生效）
lower_case_table_names = 1
###主备相关------
server-id = 25
log-bin = backup.log
#二进制日志格式,三种模式：statement语句>模式,row行模式，mixed混合模式
binlog_format = mixed
max_binlog_size = 1G
expire_logs_days = 7
#事务提交前产生的日志缓存
binlog_cache_size = 16M
#Auto_increment_increment和auto_increment_offset用于主－主服务器（master-to-master）复制，避免主键冲突
#为了避免两台服务器同时做更新时自增长字段的值之间发生冲突。一般在主主同步配置时，需要将两台服务器的auto_increment_increment增长量都配置为2（根据从库节点数量配置），而要把auto_increment_offset分别配置为1（主库）和2（从库）
#递增值
auto_increment_increment = 2
#初始值
auto_increment_offset = 1
#表示需要记录二进制日志的数据库
binlog_do_db=cssp
binlog_do_db=mysql
#表示不需要记录二进制日志的数据库
binlog_ignore_db=information_schema
#忽略表
replicate-wild-ignore-table=pva_db.pva_login_%
replicate-wild-ignore-table=pva_db.pva_login_log
#忽略错误代码（从库配置）
slave-skip-errors = 1032,1062,126,1114,1146,1048,1396
#自动清空不再需要中继日志（从库配置）
relay-log-purge = 1
###系统资源相关------
#接收连接请求队列
back_log = 600
#最大打开文件数（前提mysql用户需要调整最大打开文件数）
open_files_limit = 10240
#所有线程打开表数量
table_open_cache = 512
#处理请求包最大大小
max_allowed_packet = 32M
#临时表最大大小
tmp_table_size = 512M
#独立内存表所允许最大容量
max_heap_table_size = 512M
#用于索引块的缓冲区大小
key_buffer_size = 64M
#查询排序的缓冲区大小
sort_buffer_size = 8M
#读操作的缓冲区大小
read_buffer_size = 8M
#联表查询缓冲区大小
join_buffer_size = 8M
#批量数据插入缓存
bulk_insert_buffer_size = 120M
#保存索引以及原始数据的缓冲池（原则上最大为系统内存的 80%）
innodb_buffer_pool_size = 2G
#脏页比例，脏页占innodb_buffer_pool_size的比例时，触发刷脏页到磁盘（25~50）
innodb_max_dirty_pages_pct = 30
#事务提交 flush
innodb_flush_log_at_trx_commit = 1
#1，（默认值）每一次提交刷新日志到磁盘
#2，先写入缓存，由系统设置每秒刷新日志到磁盘（可能丢失1~2秒的数据）
###多线程优化------
#可重新被利用保存在缓存中的线程数量
thread_cache_size = 300
###其他配置------
#占用内存过高调整以下参数配置
#table_definition_cache=400
#open_files_limit = 2048
#table_open_cache = 256
#跳过密码表检查
#skip-grant-tables
创建相关目录及权限配置
#mkdir mysql-8.0.18/{log,tmp}
#mkdir data
#useradd mysql -s /sbin/nologin
#chown -R mysql.mysql /project/mysql
数据库初始化
无密码初始化
#/project/mysql/mysql-8.0.18/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql --initialize-insecure
有密码初始化
#/project/mysql/mysql-8.0.18/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql --initialize
启动数据库
#/project/mysql/mysql-8.0.18/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
登录数据库
#/project/mysql/mysql-8.0.18/bin/mysql -u root -p
修改密码
>alter user 'root'@'localhost' identified with mysql_native_password by 'xxx';
>flush privileges;
配置MySQL环境变量
#vim /etc/profile.d/mysqld.sh
export MYSQL_HOME=/project/mysql/mysql-8.0.18
export PATH=$MYSQL_HOME/bin:$MYSQL_HOME/lib:$PATH
#chmod +x /etc/profile.d/mysqld.sh
#source /etc/profile.d/mysqld.sh
添加系统服务
#cp -a /project/mysql/mysql-8.0.18/support-files/mysql.server /etc/init.d/mysqld.server
#chmod +x /etc/init.d/mysqld.server
开机自启
#chkconfig --add mysqld.server
#chkconfig --list mysqld.server
导出导入
导出
#mysql -e "show databases;" -uroot -p| grep -Ev "Database|information_schema|mysql"|xargs mysqldump -uroot -p --databases|gzip >./mysql_dump.sql.gz
导入
#nohup gunzip -f<./mysql_dump.sql.gz |mysql -u root -p>./nohup.out &
抛错
1，SQL导入
Mysql ERROR 1067: Invalid default value for 字段
原因分析
SQL_MODE 设置值的问题
解决方法：
修改配置sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
ONLY_FULL_GROUP_BY：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的，因为列不在GROUP BY从句中
STRICT_TRANS_TABLES：在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做任何限制
NO_ZERO_IN_DATE：在严格模式，不接受月或日部分为0的日期。如果使用IGNORE选项，我们为类似的日期插入'0000-00-00'。在非严格模式，可以接受该日期，但会生成警告。
NO_ZERO_DATE：在严格模式，不要将 '0000-00-00'做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式，可以接受该日期，但会生成警告
ERROR_FOR_DIVISION_BY_ZERO：在严格模式，在INSERT或UPDATE过程中，如果被零除(或MOD(X，0))，则产生错误(否则为警告)。如果未给出该模式，被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中，MySQL生成被零除警告，但操作结果为NULL。
NO_AUTO_CREATE_USER：防止GRANT自动创建新用户，除非还指定了密码。
NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
2，SQL执行
[HY000][1418] This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
原因分析
因为CREATE PROCEDURE, CREATE FUNCTION, ALTER PROCEDURE,ALTER FUNCTION,CALL, DROP PROCEDURE, DROP FUNCTION等语句都会被写进二进制日志,然后在从服务器上执行。但是，一个执行更新的不确定子程序(存储过程、函数、触发器)是不可重复的，在从服务器上执行(相对与主服务器是重复执行)可能会造成恢复的数据与原始数据不同，从服务器不同于主服务器的情况
解决方法
set global log_bin_trust_function_creators=TRUE;
主从配置
原理
MySQL 支持互为主从，主库通过 binlog 将执行的语句传给从库，具体的执行机构
1，主库上的 dump thread，主库上的 binlog 只有在写入到硬盘之后才能通过 dump thread 传出
2，从库上的 IO thread，接收主库的 dump thread 发过来的 binlog 并且生成 relay log（中继日志）
3，从库上的 sql thread，执行 relay log 中的语句
注意事项
1，从库会向主库验证身份，需要在从库配置主库的连接信息（添加主库认证用户及密码）
2，在主库已存在数据的情况下，需要手动导出主库数据并导入进从库再开启主从复制，需保证各节点数据一致
3，主从数据库版本保持一致
4，主库开启二进制日志
5，server-id必须唯一
配置
添加主从认证用户
>CREATE USER '${username}'@'${IP}' IDENTIFIED with mysql_native_password BY '${password}';
>grant FILE,SELECT,REPLICATION SLAVE,REPLICATION CLIENT ON *.* to '${username}'@'${IP}';
>flush privileges;
修改主从复制节点信息
> change master to MASTER_HOST='${IP}',MASTER_PORT=3306,MASTER_USER='${username}',MASTER_PASSWORD='${password}',MASTER_LOG_FILE='backup_log.000003',MASTER_LOG_POS=156;
master_log_file、master_log_pos 为主库节点中 show master status; 查询到的结果
开启同步
>start slave;
>show master status\G
>show slave status\G
不停主库开启主从
使用备份导出-导入
mysqldump备份导出记录（change master to）"binlog"点和"pos"值写到结果中
#mysqldump -uroot -p --routines --single_transaction --master-data=2 --databases test > test.sql
–routines：导出存储过程和函数
–single_transaction：导出开始时设置事务隔离状态，并使用一致性快照开始事务。
–master-data：默认等于1，将dump起始(change master to)binlog点和pos值写到结果中，等于2是将change master to写到结果中并注释
mysql导入
#mysql -uroot -p -e 'create database test;'
#mysql -uroot -p test<./test.sql
备份文件查看binlog和pos值
#head -25 weibo.sql #大概22行左右的位置
从库change master
mysql>change master to master_host='${IP}',
-> master_user='${USERNAME}',
-> master_password='${PASSWORD}',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=107;mysql>start slave;
主从延迟
Seconds_Behind_Master： 从属服务器SQL线程和从属服务器I/O线程之间的时间差距，单位以秒计
1，提高slave端服务器配置
2，sync_binlog在slave端设置为0或直接禁用slave端的binlog二进制日志
3，slave端如果使用的存储引擎是innodb，innodb_flush_log_at_trx_commit = 2，=2表示事务提交写入系统缓存，=1表示事务提交写入系统硬盘
问题记录
1236
问题描述：日志读取错误，一般是因为主库存在原数据未同步到从库导致，或者检查sql（changel）有没有规范
解决方法：刷新日志，在从库重新配置主库节点信息
>flush logs; #主库执行
>show master status\G #主库执行并记录信息
>stop slave; #从库执行
> change master to MASTER_HOST='${IP}',MASTER_PORT=3306,MASTER_USER='${username}',MASTER_PASSWORD='${password}',MASTER_LOG_FILE='backup_log.000004',MASTER_LOG_POS=156;
#MASTER_LOG_FILE、MASTER_LOG_POS根据主库节点重新 show master status\G信息修改
>start slave; #从库执行
1062
问题描述：主键冲突，一般是因为主键字段id在主库之前的数据上加一的，但是从库中已经存在了这个加一后的值，所以发生了主键冲突，导致SQL线程发生错误
解决方法：跳过或者删除从库自己插入的那条数据
>stop slave; #从库执行
>set global sql_slave_skip_counter=1; #从库执行，跳过当前执行sql
>start slave; #从库执行
1593
问题描述：主从库的server-id冲突
解决方法：将sevrer-id改为不一致的值即可，记得重启服务进程
1032
问题描述：从库误删记录
解决方法：补齐从库所缺记录
>show slave status\G #从库执行，获取主库的binlog文件（Relay_Master_Log_File：mysql-binlog.000005）和position号 （Exec_Master_Log_Pos：1678）
执行命令获取sql文件
#mysqlbinlog --start-position=1678 -vv --base64-output=DECODE-ROWS log/backup_log.000005 > /tmp/bin.sql #主库执行
打开文件检索关键字 1678 确认已记录该pos
翻到文件最后
根据上面信息可以得到主库执行sql
sql：update
更新表名：test_102
更新字段：第二个字段，更新后的记录为：test
根据表结构，字段名可直接插入从库
>insert into test_102 values ('04','test','23'); #从库执行
再通过pt-slave-restart跳过错误即可，执行命令
#pt-slave-restart -uroot -pHdlh@2605 #从库执行
如误删数据不重要，且数据太多，可以选择直接跳过报错的事务
记录报错起始的GTID号
执行sql
>stop slave;
>SET @@SESSION.GTID_NEXT= 'ANONYMOUS';
>begin;
>commit;
>SET GTID_NEXT=AUTOMATIC;
>start slave;
2003
问题描述：主库宕机
解决方法：启动主库服务进程
reset
mysql>stop slave;
mysql>reset slave;
mysql>start slave;
版本替换
二进制
备份my.cnf文件
#cp /etc/my.cnf /etc/my.cnf_bak
解压高版本安装包
#tar xzvf mysql-8.0.29-el7-x86_64.tar.gz
解压安装完不需要初始化数据库
修改MySQL数据库配置文件 /etc/my.cnf
替换basedir路径为最新版本解压安装路径
替换pid-file路径
替换日志文件路径
总之，数据文件datadir路径不变，其他安装文件、pid文件、日志文件路径都要修改成对应的新版本解压安装路径（新版本安装路径mkdir创建与之对应的目录并赋权mysql用户）
#vim /etc/my.cnf
[mysqld]
basedir = /project/mysql/mysql-8.0.29
datadir = /project/mysql/data
pid-file = /project/mysql/mysql-8.0.29/mysqld.pid
###日志配置------
log_error = /project/mysql/mysql-8.0.29/log/error.log
slow_query_log_file = /project/mysql/mysql-8.0.29/log/slow.log
rpm包
备份my.cnf文件
#cp /etc/my.cnf /etc/my.cnf_bak
卸载低版本
#rpm -qa | grep -i mysql | xargs rpm -ev --nodeps
安装新版本
#rpm -ivh ./*.rpm --force --nodeps
重启数据库
#systemctl restart mysql
验证
1425
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
