本文件是MySQL主從實施文件，涉及內容如下:

1.    MySQL 安裝，多例項配置，涉及四個例項

2.    MySQL 無損複製配置

3.    壓力測試，半同步效能測試

4.    cgroup 資源隔離配置，MySQL資源限制 。

5.    備份最佳化，備份工具指令碼配置

6.    災備環境配置，四個災備例項，級聯非同步複製

 

 

2.   MySQL 安裝

軟體包版本：mysql-5.7.31-linux-glibc2.12-x86_64.tar

例項規劃，四個埠，3306,3307,3308,3309

資料夾規劃：/data3306/mysql, /data3307/mysql, /data3308/mysql, /data3309/mysql

2.1. 基礎配置

1 、作業系統引數

2 、selinux

3 、swap 空間管理：

4 、環境變數

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

export PATH

 

2.2. 二進位制軟體安裝

1 、安裝環境檢查，清理環境

rpm -qa | grep -i mysql

rpm -qa | grep -i mariadb_libs

yum remove mysql-libs-5.1.73-5.el6_6.x86_64

rpm -qa | grep -i mariadb_libs

rpm -qa | grep -i mysql

find / -name *mysql*

 

2 、建立MySQL 使用者和組

建立組

groupadd mysql

useradd -r -g mysql -s /bin/false mysql

 

解壓安裝包

tar -xvf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C /usr/local/

重新命名軟體目錄

mv mysql-5.7.31-linux-glibc2.12-x86_64 mysql

重新賦權

cd /usr/local/

chown -R mysql.mysql mysql/

 

2.3. 資料庫初始化

2.3.1.    建立資料目錄並賦權

#3306

mkdir -p /data3306/mysql/data

mkdir -p /data3306/mysql/log

mkdir -p /data3306/mysql/

mkdir -p /data3306/mysql/tmp

chown -R mysql.mysql /data3306

#3307

mkdir -p /data3307/mysql/data

mkdir -p /data3307/mysql/log

mkdir -p /data3307/mysql/

mkdir -p /data3307/mysql/tmp

chown -R mysql.mysql /data3307

3308

mkdir -p /data3308/mysql/data

mkdir -p /data3308/mysql/log

mkdir -p /data3308/mysql/

mkdir -p /data3308/mysql/tmp

chown -R mysql.mysql /data3308

3309

mkdir -p /data3309/mysql/data

mkdir -p /data3309/mysql/log

mkdir -p /data3309/mysql/

mkdir -p /data3309/mysql/tmp

chown -R mysql.mysql /data3309

2.3.2.    初始化引數

2.3.2.1.           3306

 

[mysqld_safe]

pid-file=/data3306/mysql/data/mysqld3306.pid

 

 

[mysql]

port=3306

prompt=\\u@\\d \\r:\\m:\\s>

no-auto-rehash   

default-character-set= utf8mb4

 

[client]

port=3306

socket=/data3306/mysql/data/mysql3306.sock

 

 

[mysqld]

#dir

basedir=/usr/local/mysql

datadir=/data3306/mysql/data

server_id=1

port=3306

user=mysql

 

socket=/data3306/mysql/data/mysql3306.sock

pid-file=/data3306/mysql/data/mysqld3306.pid

tmpdir=/data3306/mysql/tmp

lc_messages_dir=/usr/local/mysql/share

 

#binlog

binlog_cache_size=512K

max_binlog_cache_size=2G

max_binlog_size=500M

binlog-format=ROW

log_bin = /data3306/mysql/log/mysql-bin

log-bin-index = /data3306/mysql/log/bin-index

relay_log = /data3306/mysql/log/mysql-relay-bin

expire_logs_days=15

sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

 

 

##### 雙一

innodb_flush_log_at_trx_commit = 1

sync_binlog = 1

innodb_doublewrite = 1

#####GTID 複製

gtid-mode = ON

enforce-gtid-consistency = ON

 

### 執行緒配置

innodb_read_io_threads=4

innodb_write_io_threads=4

innodb_purge_threads=1

 

### 日誌

log_slow_admin_statements=1

log_warnings=2

long_query_time=3

slow_query_log=1

general_log=0

log_error=/data3306/mysql/log/mysqld_err.log

slow_query_log_file=/data3306/mysql/log/mysql_slow.log

### 分離undo

innodb_undo_tablespaces=3

innodb_undo_logs=128

innodb_undo_directory =/data3306/mysql/data

 

### 日誌大小

innodb_log_files_in_group=3

innodb_log_file_size=1G

 

#innodb

#innodb_buffer_pool_instances=4

innodb_log_buffer_size=32G

innodb_max_dirty_pages_pct=70     

innodb_io_capacity=2000

innodb_open_files=60000

innodb_file_format=Barracuda

innodb_file_per_table=1

innodb_flush_method=O_DIRECT

innodb_change_buffering=inserts

innodb_adaptive_flushing=1

innodb_old_blocks_time=1000

innodb_stats_on_metadata=0

innodb_use_native_aio=0

innodb_lock_wait_timeout=120

innodb_rollback_on_timeout=0

 

innodb_strict_mode=1

transaction-isolation=READ-COMMITTED

#server

default-storage-engine=INNODB

character-set-server=utf8

collation-server=utf8_general_ci

lower_case_table_names=1

skip-external-locking

safe-user-create

local-infile=1

performance_schema=1

show_compatibility_56=1

 

query_cache_type=0

query_cache_size=0

query_cache_limit=1M

query_cache_min_res_unit=1K

 

###22M*300= 64G

max_connections=3000

max_user_connections=3000

thread_stack=512K

read_rnd_buffer_size=2M

sort_buffer_size=2M

join_buffer_size=512K

read_buffer_size=512K

max_heap_table_size = 16M

#binlog_cache_size=512K

 

skip-name-resolve

skip-ssl

max_connect_errors=65535

max_allowed_packet=256M

connect_timeout=8

net_read_timeout=30

net_write_timeout=60

back_log=1024

 

# slave ，如果事務併發不高不建議開啟，反而會降低效能

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=8

relay_log_recovery=ON

master_info_repository=TABLE

relay_log_info_repository=TABLE

###### 半同步複製

 

plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

rpl-semi-sync-master-enabled = 1

rpl-semi-sync-slave-enabled = 1

rpl_semi_sync_master_timeout =1000 ## 半同步降級時間

rpl_semi_sync_master_wait_no_slave = 1

rpl_semi_sync_master_wait_point = AFTER_SYNC

 

#myisam

key_buffer_size=64M

myisam_sort_buffer_size=64M

concurrent_insert=2

delayed_insert_timeout=300

 

[mysqldump]

quick

max_allowed_packet=1024M

 

2.3.3.    初始化資料庫例項

 

 

/usr/local/mysql/bin/mysqld --defaults-file=/data3306/mysql/my.cnf  --user=mysql  --initialize-insecure

 

 

2.3.4.    啟動例項

/usr/local/mysql/bin/mysqld_safe --defaults-file=/data3306/mysql/my.cnf --user=mysql  &

 

2.3.5.    例項基礎配置

1 、修改密碼

set password for root@localhost = password('123');

alter user 'root'@'localhost' identified by '123456';

2 、例項啟動指令碼

 

3.   配置無損複製

 

1 、主庫建立同步使用者

grant replication slave on *.* to 'u_repl'@'%' identified by '123456';

2 、備庫建立同步使用者，主從切換使用

grant replication slave on *.* to 'u_repl'@'%' identified by '123456';

 

3 、在兩臺MySQL 伺服器上檢查"have_dynamic_loading" 是否為YES

show variables like 'have_dynamic_loading';

 

4 、主庫安裝外掛（主庫備庫都要安裝）

 

install plugin rpl_semi_sync_master soname 'semisync_master.so';

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

SET GLOBAL rpl_semi_sync_master_enabled = 1;

 

4 、備庫安裝外掛

install plugin rpl_semi_sync_master soname 'semisync_master.so';

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

SET GLOBAL rpl_semi_sync_slave_enabled = 1;

 

5 、主庫匯出

mysqldump -uroot -p123456 --master-data=2 -S /data3306/mysql/data/mysql3306.sock --single-transaction --set-gtid-purged=on --all-databases  >all.sql

 

6 、備庫匯入

需要reset master

reset mastser;

 

mysql -uroot -p123456  -S /data3306/mysql/data/mysql3306.sock < all.sql

7 、配置複製關係

 

change master to

  master_host='192.168.56.11',

  master_port=3306,

  master_user='u_repl',

  master_password='123456',

  master_auto_position=1,

  master_connect_retry=30;

 

8 、檢查半同步狀態

主庫：

show status like 'Rpl_semi_sync_master_status';

備庫

show status like 'Rpl_semi_sync_slave_status';

 

9 、引數檔案配置

 

plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

rpl-semi-sync-master-enabled = 1

rpl-semi-sync-slave-enabled = 1

rpl_semi_sync_master_timeout =1000 ## 半同步降級時間

rpl_semi_sync_master_wait_no_slave = 1

rpl_semi_sync_master_wait_point = AFTER_SYNC

 

 

4.   資源隔離配置

 

Linux7 開始，建議使用system unit 進行資源隔離，如果無法使用，使用libcgroup 工具。

 

4.1. cgroup 配置,libcgroup

1 、安裝cgroup

yum install libcgroup libcgroup-tools

2 、檢查服務狀態

systemctl status cgconfig

 

1 、建立控制群組

cgcreate -g  cpuset:/test

2 、刪除控制群組

cgdelete -g cpuset:/test

3 、設定群組引數

cgset -r cpuset.cpus=0-1 test

 

 

4 、將某個程式移入子系統 ps -ef|grep mysql

cgclassify -g cpuset:/test 9532

4.2. system 單元限制CPU

 

 

5.   壓力測試

5.1. 場景一：開啟半同步

 

5.2. 場景二：關閉半同步

 

6.   備份工具配置

6.1. xtrabackup 安裝

1 、基礎依賴包配置

 

1 、解壓安裝包

tar -xvf percona-xtrabackup-2.4.20-Linux-x86_64.el7.libgcrypt153.tar.gz -C /usr/local/

重新命名軟體目錄

mv percona-xtrabackup-2.4.20-Linux-x86_64 xtrabackup

環境變數配置

 

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin:/usr/local/xtrabackup/bin

6.2. xtrabackup 備份

1 、全量備份

innobackupex --user=root --password=123456 -P3306 -S /data3306/mysql/data/mysql3306.sock /data3306/mysql/backup

2 、增量備份

 

3 、日誌備份

 

3 、恢復

innobackupex --apply-log --redo-only /data/backup/full/2017-01-20_10-52-43

 

innobackupex --copy-back /data/backup/2018-05-21_15-02-53/

 

 

innobackupex --decompress ${decompress_dir}auto_nfs_restore_xbstream/ > ${workdir}decompress.txt  2>&1

innobackupex  --apply-log ${decompress_dir}auto_nfs_restore_xbstream/ > ${workdir}decompress_apply_log.txt  2>&1

innobackupex  --apply-log ${decompress_dir}auto_nfs_restore_tar/ > ${workdir}tar_apply_log.txt  2>&1