1.MySQL主从

1.1MySQL主从的作用

• 数据分布

• 负载均衡读操作

• 备份

• 读写分离

• 高可用和故障切换

1.2复制架构

一主一从：

一主多从：

1.3主从复制的原理

主从复制相关线程:

master节点：

dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events

slave节点：

io Thread：向master节点请求二进制日志，并保存于中继日志中

sql Thread：从中继日志中读取日志事件，在本地完成重放

主从复制相关文件：

master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等

relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地relay log日志的对应关系

mysql-relay-bin.00000#: 中继日志,保存从主节点复制过来的二进制日志,本质就是二进制日志

1.4实现主从复制配置

1.4.1主节点配置

#编辑my.cnf配置文件
vim /etc/my.cnf
[mysqld]
server-id=#  #为当前节点设置一个全局唯一的ID号,取值范围为1~4294967295
log_bin      #开启二进制日志

#连接数据库进行操作
#记录下使用的二进制日志名称和当前二进制日志文件的Position
mysql> show master status;

#创建有复制权限的用户账号
mysql> GRANT REPLICATION SLAVE  ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';

复制

1.4.2从节点配置

#编辑my.cnf配置文件
vim /etc/my.cnf
[mysqld]
server_id=# #为当前节点设置一个全局唯一的ID号
log-bin
read_only=ON #设置数据库只读，针对supper user无效
relay_log=relay-log #relay log的文件路径，默认值hostname-relay-bin
relay_log_index=relay-log.index  #默认值hostname-relay-bin.index


#连接数据库进行操作:使用有复制权限的用户账号连接至主服务器，并启动复制线程
CHANGE MASTER TO MASTER_HOST='masterhost', 
MASTER_USER='repluser', 
MASTER_PASSWORD='replpass', 
MASTER_LOG_FILE='上方的二进制日志文件名称', 
MASTER_LOG_POS=上方的二进制日志文件位置;

#开启主从复制的命令
START SLAVE [IO_THREAD|SQL_THREAD];

#查看主从复制的状态
SHOW SLAVE STATUS;

复制

1.5范例

1.5.1：新建主从复制

#主节点,编辑配置文件
[root@master ~]# cat /etc/my.cnf
[mysqld]
server-id=1
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock

#查看当前二进制文件信息
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000002 |      414 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

#创建复制用户
mysql> grant replication slave on *.* to buerkeji@'192.168.10.%' identified by 'buerkeji';
Query OK, 0 rows affected, 1 warning (0.00 sec)

#从节点配置
[root@slave src]# cat /etc/my.cnf
[mysqld]
server-id=2
log-bin
relay_log=relay-log
relay_log_index=relay-log.index
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock

#连接数据库操作
mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.29', 
    -> MASTER_USER='buerkeji', 
    -> MASTER_PASSWORD='buerkeji', 
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='master-bin.000002', 
    -> MASTER_LOG_POS=414;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

#开始复制
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

#查看复制状态
mysql> show slave status\G;
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.29
                  Master_User: tjbool
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 710
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 617
        Relay_Master_Log_File: master-bin.000002
             Slave_IO_Running: Yes    #两个yes即为正常工作
            Slave_SQL_Running: Yes

复制

测试

在master节点新建一个库，看slave节点是否会自动同步

#master
mysql> create database buerkeji;
Query OK, 1 row affected (0.00 sec)

#slave,可以看到已经同步成功
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| tjbool             |
+--------------------+
5 rows in set (0.00 sec)

复制

1.5.2：master节点运行一段时间且有大量数据后，新增slave节点

#先看master节点是有数据的
mysql> use hellodb;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

mysql> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)

#首先将master节点数据进行一次全量备份
[root@master data]# mysqldump -p -A -F --single-transaction --master-data=1 > /data/fullbackup.sql

#将该文件copy到slave节点,并且配置该文件,指定从完全备份的位置之后开始复制
[root@localhost src]# vim fullbackup.sql
CHANGE MASTER TO 
MASTER_HOST='192.168.10.29',
MASTER_USER='buerkeji',
MASTER_PASSWORD='buerkeji',
MASTER_PORT=3306,
#以下内容为文件自带，无需更改
MASTER_LOG_FILE='master-bin.000003', MASTER_LOG_POS=154;

#导入
[root@localhost src]# mysql < fullbackup.sql -p
#开启同步
mysql> start slave;
#查看同步状态
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.29
                  Master_User: tjbool
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

复制

2.MySQL5.7安装脚本

需要将脚本与MySQL的安装包放在同一个目录下，可以任意指定MySQL版本，只需在脚本中更改变量值即可。

#!/bin/bash
. /etc/init.d/functions 
SRC_DIR=`pwd`
MYSQL='mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz'
COLOR='echo -e \E[01;31m'
END='\E[0m'
#你的MySQL密码
MYSQL_ROOT_PASSWORD="buerkeji"


check (){

if [ $UID -ne 0 ]; then
  action "当前用户不是root,安装失败" false
  exit 1
fi

cd  $SRC_DIR
if [ !  -e $MYSQL ];then
        $COLOR"缺少${MYSQL}文件"$END
        $COLOR"请将相关软件放在${SRC_DIR}目录下"$END
        exit
elif [ -e /usr/local/mysql ];then
        action "数据库已存在，安装失败" false
        exit
else
    return
fi
} 

install_mysql(){
    $COLOR"开始安装MySQL数据库..."$END
    yum  -y -q install libaio numactl-libs   libaio &> /dev/null
    cd $SRC_DIR
    tar xf $MYSQL -C /usr/local/
    MYSQL_DIR=`echo $MYSQL| sed -nr 's/^(.*[0-9]).*/\1/p'`
    ln -s  /usr/local/$MYSQL_DIR /usr/local/mysql
    chown -R  root.root /usr/local/mysql/
    id mysql &> /dev/null || { useradd -s /sbin/nologin -r  mysql ; action "创建mysql用户"; }

    echo 'PATH=/usr/local/mysql/bin/:$PATH' > /etc/profile.d/mysql.sh
    .  /etc/profile.d/mysql.sh
    ln -s /usr/local/mysql/bin/* /usr/bin/
    cat > /etc/my.cnf <<-EOF
[mysqld]
server-id=1
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock                                 
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
EOF
    mysqld --initialize --user=mysql --datadir=/data/mysql 
    cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
    chkconfig --add mysqld
    chkconfig mysqld on
    service mysqld start
    [ $? -ne 0 ] && { $COLOR"数据库启动失败，退出!"$END;exit; }
    MYSQL_OLDPASSWORD=`awk '/A temporary password/{print $NF}' /data/mysql/mysql.log`
    mysqladmin  -uroot -p$MYSQL_OLDPASSWORD password $MYSQL_ROOT_PASSWORD &>/dev/null
    action "数据库安装完成" 
}
check
install_mysql

复制

3.MySQL5.7部分优化参数

[root@cdh-mysql log]# cat /etc/my.cnf
[mysqld]
server-id=1
#设置唯一的server-id
log-bin=cdh-mysql-bin
#开启二进制日志，并指定日志名称
binlog_format=row
#指定二进制日志格式
binlog_cache_size = 4M
max_binlog_cache_size = 8M
max_binlog_size = 512M
#期待二进制日志保存的天数
expire_logs_days = 7
#大小写不区分
lower_case_table_names=1
key_buffer_size = 2048M
default_time_zone = "+8:00"
datadir=/data/mysql
socket=/data/mysql/mysql.sock                                    
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
tmpdir = /tmp/mysql
back_log = 600
#back_log 是操作系统在监听队列中所能保持的连接数
log_timestamps=SYSTEM
#设置MySQL的日志为系统时间
max_connections = 3000
#MySQL的最大连接数量
max_connect_errors = 1500
#每个客户端连接最大的错误允许数量，当超过该次数，MYSQL服务器将禁止此主机的连接请求，直到MYSQL服务器重启或通过flush hosts命令清空此主机的相关信息
external-locking = FALSE
#使用–skip-external-locking MySQL选项以避免外部锁定。
max_allowed_packet = 32M
#每个连接传输数据大小.最大1G，须是1024的倍数，一般设为最大的BLOB的值
sort_buffer_size = 8M
# 排序缓冲被用来处理类似ORDER BY以及GROUP BY队列所引起的排序
join_buffer_size = 8M
#不带索引的全表扫描.使用的buffer的最小值
thread_cache_size = 300
# 服务器线程缓存这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。设置规则如下：1GB 内存配置为8，2GB配置为16，3GB配置为32，4GB或更高内存，可配置更大。
query_cache_size = 64M
#查询缓冲大小
query_cache_limit = 4M
#指定单个查询能够使用的缓冲区大小，缺省为1M
query_cache_min_res_unit = 2k
#默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费
default_storage_engine= InnoDB
#默认使用InnoDB作为存储引擎
thread_stack = 192K
#设置MYSQL每个线程的堆栈大小，默认值足够大，可满足普通操作。可设置范围为128K至4GB，默认为192KB。
character_set_server=utf8mb4
#设置默认字符集为utf8mb4
transaction_isolation = READ-COMMITTED
# 设定默认的事务隔离级别.可用的级别如下:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
# 1.READ UNCOMMITTED-读未提交2.READ COMMITTE-读已提交3.REPEATABLE READ -可重复读4.SERIALIZABLE -串行
slow_query_log = 1
#开启慢查询日志记录
innodb_buffer_pool_size = 10G
#InnoDB使用一个缓冲池来保存索引和原始数据, 可设置这个变量到物理内存大小的80%
innodb_thread_concurrency = 16
#在InnoDb核心内的允许线程数量，建议的设置是CPU数量加上磁盘数量的两倍
innodb_flush_log_at_trx_commit = 2
# 如果将此参数设置为1，将在每次提交事务后将日志写入磁盘。为×××能，可以设置为0或2，但要承担在发生故障时丢失数据的风险。设置为0表示事务日志写入日志文件，而日志文件每秒刷新到磁盘一次。设置为2表示事务日志将在提交时写入日志，但日志文件每次刷新到磁盘一次。
innodb_log_buffer_size = 16M
#此参数确定日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，但意外的故障将会丢失数据.MySQL开发人员建议设置为1－8M之间
innodb_log_file_size = 128M
#此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能，但也会增加恢复故障数据库所需的时间
innodb_log_files_in_group = 3
#在日志组中的文件总数
innodb_max_dirty_pages_pct = 90
# Buffer_Pool中Dirty_Page所占的数量，直接影响InnoDB的关闭时间。参数innodb_max_dirty_pages_pct 可以直接控制了Dirty_Page在Buffer_Pool中所占的比率，而且幸运的是innodb_max_dirty_pages_pct是可以动态改变的。所以，在关闭InnoDB之前先将innodb_max_dirty_pages_pct调小，强制数据块Flush一段时间，则能够大大缩短 MySQL关闭的时间。
innodb_lock_wait_timeout = 120
# InnoDB 有其内置的死锁检测机制，能导致未完成的事务回滚。但是，如果结合InnoDB使用MyISAM的lock tables 语句或第三方事务引擎,则InnoDB无法识别死锁。为消除这种可能性，可以将innodb_lock_wait_timeout设置为一个整数值，指示 MySQL在允许其他事务修改那些最终受事务回滚的数据之前要等待多长时间(秒数)
innodb_file_per_table = 1
#打开独立的表空间

复制