mysql主从配置，状态监控 ，备份 ，调优 ，my.cnf介绍

主从

1.主从部署必要条件：

●主库开启binlog日志（设置log-bin参数）
●主从server-id不同
●从库服务器能连通主库

2.主从同步的内部分析介绍

从库生成两个线程，一个I/O线程，一个SQL线程；

i/o线程去请求主库 的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中；

主库会生成一个 log dump 线程，用来给从库 i/o线程传binlog；

具体步骤

1.首先创建进行备份的用户的授权操作：

grant replication slave on *.* to 'slave_account'@'%' identified by '1q2w3E4R.123abc!@#';

2.执行 show master status; 记录下position 和file 值

mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000002 |     1466 |              | mysql,information_schema,performance_schema,sys |                   |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)

3.到从库去配置要复制的master主机ip binlog日志名字File 和日志读取路径Position

mysql>change master to master_host='10.140.0.7',master_user='slave_account',master_password='1q2w3E4R.123abc!@#',master_log_file='mysql-bin.000002',master_log_pos=1466;

4.启动从库复制

mysql>start slave;

5.检查从库的状态

观察从库上的两个线程，一个I/O线程Slave_IO_Running，一个SQL线程Slave_SQL_Running，是否正常运行，都为yes.

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.215
                  Master_User: slave_account
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 763468181
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

6.常见的主从同步失败的错误码

1032

MySQL主从同步的1032错误，一般是指要更改的数据不存在，SQL_THREAD提取的日志无法应用故报错，造成同步失败（Update、Delete、Insert一条已经delete的数据）。

1062：主键冲突

slave_skip_errors=1062,1032

监控

#/bin/bash

prefix=`date +%Y%m%d%H%M`
passwd='1q2w3E4R.123abc!@#'
mysql  -uroot -p$passwd --socket=/tmp/mysql.sock -e "show slave status\G"  > /tmp/mysqlslavesstauts.log${prefix}

Status1=`grep -E "Slave_IO_Running:" /tmp/mysqlslavesstauts.log${prefix} |gawk -F: '{print $2}'`
Status2=`grep -E "Slave_SQL_Running:" /tmp/mysqlslavesstauts.log${prefix} |gawk -F: '{print $2}'`
Status3=`grep -E "Seconds_Behind_Master:" /tmp/mysqlslavesstauts.log${prefix} |gawk -F: '{print $2}'`

#判断io进程`Slave_IO_Running`和sql进程`Slave_SQL_Running`时候正常运行
if [ "$Status1" = " Yes" -a "$Status2" = " Yes" ];then
	echo "wufu Mysql02 salve replication is  OK " >> /tmp/mysqlslavesstauts.log${prefix}
else
	TEXT="wufu Mysql02 salve replication is  STOP "	
	echo $TEXT >> /tmp/mysqlslavesstauts.log${prefix}
	curl -X POST "https://api.telegram.org/botxxxxxxxxxx/sendMessage?chat_id=-123&text=$TEXT"
	exit
fi

#判断复制延迟时间超过5秒就报警`Seconds_Behind_Master`
if [ $Status3 -ge 5 ];then
	TEXT="wufu Mysql02 salve replication delay time  $Status3 seconds"
	echo $TEXT >> /tmp/mysqlslavesstauts.log${prefix}
	curl -X POST "https://api.telegram.org/botxxxxxxxxxx/sendMessage?chat_id=-123&text=$TEXT"
fi	

调优

my.cnf调优只是一部分

[root@master my.cnf.d]# cat /etc/my.cnf 
[mysqld]
user=mysql
port = 3306
basedir=/usr/local/mysql
datadir=/data/mysql/dbdata
tmpdir=/data/mysql/temp/
socket=/data/mysql/temp/mysql.sock
default-time-zone = '+8:00'
skip-name-resolve	# 禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求
slave-skip-errors = 1032 #一般是指要更改的数据不存在，SQL_THREAD提取的日志无法应用故报错，造成同步失败

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 解决问题：TIMESTAMP with implicit DEFAULT value is deprecated
explicit_defaults_for_timestamp=true
log-error=/data/mysql/logs/mysql_error.log
log_timestamps=SYSTEM
pid-file=/usr/local/mysql/mysqld.pid
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
open_files_limit=65535   
back_log=600    
max_connect_errors=6000    
max_allowed_packet=32M   
sort_buffer_size=4M   # MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段，如果不能，可以尝试增加sort_buffer_size变量的大小 
join_buffer_size=4M    
thread_cache_size=300    
query_cache_type=0    
query_cache_size=0    
query_cache_limit=2M    
query_cache_min_res_unit=16k

tmp_table_size=256M   
max_heap_table_size=256M

key_buffer_size=256M   
read_buffer_size=1M    
read_rnd_buffer_size=16M    
bulk_insert_buffer_size=64M

lower_case_table_names=1

default-storage-engine=INNODB

innodb_buffer_pool_size=128M
# InnoDB使用一个缓冲池来保存索引和原始数据, 不像MyISAM.
# 这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少.
# 在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80%
# 不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸.
# 注意在32位系统上你每个进程可能被限制在 2-3.5G 用户层面内存限制,
# 所以不要设置的太高.

innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_flush_method=O_DIRECT
#thread_concurrency=32 # 并发线程数量个数 这个参数已经在最新版本的MySQL中被移除了 
long_query_time=1
slow-query-log=on
slow-query-log-file=/data/mysql/logs/mysql-slow.log

server_id=2
max_connections=10000

##要给从机同步的库
#binlog-do-db=

##不给从机同步的库(多个写多行)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

##开启二进制日志
log-bin=/data/mysql/logs/mysql-bin 
#log_bin_index = master-bin.index
binlog-format=ROW 
log-slave-updates

##自动清理 7 天前的log文件,可根据需要修改
expire_logs_days=7
#max_binlog_size = 128M


relay-log = /data/mysql/logs/slave-relay-bin
#relay-log-index = slave-relay-bin.index

init_connect='SET collation_connection = utf8mb4_unicode_ci'
character-set-server=utf8mb4
init_connect='SET NAMES utf8mb4'
collation-server=utf8mb4_unicode_ci
skip-character-set-client-handshake


[mysql] #添加以下内容
default-character-set=utf8mb4
socket=/usr/local/mysql/mysql.sock
[client]
default-character-set=utf8mb4
socket=/usr/local/mysql/mysql.sock
[mysqladmin]
socket=/usr/local/mysql/mysql.sock

[mysqldump]
quick
max_allowed_packet=32M

备份

#/bin/bash

prefix=`date +%Y%m%d%H%M`
passwd='1q2w3E4R.123abc!@#'
mysqldump  -uroot -p$passwd --socket=/tmp/mysql.sock --single-transaction --events --triggers --routines   --databases --master-data=2  databases01 database02 database03 database04  > /data/bak/${prefix}mysql01.sql
tar zvcf /data/bak/${prefix}mysql01.sql.tgz /data/bak/${prefix}mysql01.sql
rm -f /data/bak/${prefix}mysql01.sql

#rsync -av --bwlimit=500 /data/bak/${prefix}mysql01.sql.tgz  xxx.xxx.xxx.xxx:/data/bak/wufu/
aws s3 cp /data/bak/${prefix}mysql01.sql.tgz s3://cydatabackup/wufu/

Day=`date -d "-5day" +%Y%m%d`
rm -rf /data/bak/$Day*