MySQL单机容器化

mkdir -p /opt/mysql/{data,etc}
cp my.cnf /opt/mysql/etc
#docker search mysql
docker pull mysql:5.7.23
 
#docker images | grep mysql
docker run --restart=always --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=1qaz@WSX -v /opt/mysql/etc/my.cnf:/etc/mysql/conf.d/my.cnf -v /opt/mysql/data:/var/lib/mysql -d mysql:5.7.23
#sudo firewall-cmd --permanent --add-port=3306/tcp
#sudo systemctl restart firewalld
#docker ps

MySQL单机二进制

1说明

本文档用于指导服务开发部的mysql单机安装部署文档，本手册用于指导单机下的mysql安装，并提供常见的数据库参数配置。分支选择官方mysql分支，版本选择5.7.23。建议操作系统选择centos7.4。以下是安装选项说明：

附件提供my.cnf的配置，适用于大部分环境，读者可以根据自己环境进行进一步配置和修改。安装所需文件包括
mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz和my.cnf，存放在公司禅道上面。

2安装前提

O 创建用户

O 创建目录

O 修改内核资源限制

O 修改用户资源限制

O 关闭防火墙

O 关闭selinux

3安装过程

O 上传文件和解压文件

O 数据库初始化

O 启动数据库

O 修改root密码

O 关闭数据库

4内存参数调整

O 调整innodb缓存

MySQL主从

1、主从复制介绍

复制分成三步：

1) master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；

2) slave将master的binary log events拷贝到它的中继日志(relay log)；

3) slave重做中继日志中的事件，将改变反映它自己的数据。

下图描述了这一过程：

该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。

下一步就是slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。

SQL slave thread处理该过程的最后一步。SQL线程从中继日志读取事件，更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。

2、MySQL复制配置

复制配置整个过程如下：

1) 在每个服务器上创建一个复制帐号；

2) 配置master和slave；

3) 数据初始化；

4) slave连接master开始复制。

2.1创建复制帐号

每个slave使用标准的MySQL用户名和密码连接master。进行复制操作的用户会授予replication slave和replication client权限。创建repl用户，命令如下：

2.2配置master

接下来对master进行配置，包括打开二进制日志，指定唯一的servr ID。在配置文件加入如下值：

[mysqld]

log-bin=mysql-bin

server-id=10

重启master，运行show master status，输出如下：

2.3配置slave

slave的配置与master类似，需要重启slave的MySQL。配置参数如下：

log_bin=mysql-bin

server_id=2

relay_log=mysql-relay-bin

log_slave_updates=1

read_only=1

server_id是必须的，而且唯一。slave没有必要开启二进制日志，但是在一些情况下，必须设置，例如，如果slave为其它slave的master，必须设置bin_log。在这里，我们开启了二进制日志。relay_log配置中继日志，log_slave_updates表示slave将复制事件写进自己的二进制日志。

2.4数据初始化

在master使用xtrabackup进行全库备份，把备份好的全部文件拷贝到slave，在slave上进心全库恢复。具体请参考《mysql备份策略实现》和《备份恢复测试》

2.5 slave连接master开始复制。

xtrabackup备份的文件包含xtrabackup_binlog_info，该文件包含了备份截至到的binlog文件位置，在配置复制关系时，从此处开始即可。

接下来就是让slave连接master，并开始重做master二进制日志中的事件。命令如下：

注意slave正常运行时，slave的I/O和SQL线程都已经开始工作，binlog日志的位置增加。如果在master上进行数据修改，你可以在slave上看到各种日志文件的位置的变化，同样，也可看到slave中数据的变化。

2.6半同步复制

2.6.1介绍

从MySQL5.5开始，MySQL以插件的形式支持半同步复制。

1) 异步复制（Asynchronous replication）

MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。

2) 全同步复制（Fully synchronous replication）

指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

3) 半同步复制（Semisynchronous replication）

介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。

2.6.2半同步复制的潜在问题

客户端事务在存储引擎层提交后，在得到从库确认的过程中，主库宕机了，此时，可能的情况有两种

1) 事务还没发送到从库上

此时，客户端会收到事务提交失败的信息，客户端会重新提交该事务到新的主上，当宕机的主库重新启动后，以从库的身份重新加入到该主从结构中，会发现，该事务在从库中被提交了两次，一次是之前作为主的时候，一次是被新主同步过来的。

2) 事务已经发送到从库上

此时，从库已经收到并应用了该事务，但是客户端仍然会收到事务提交失败的信息，重新提交该事务到新的主上。

2.6.3无数据丢失的半同步复制

针对上述潜在问题，MySQL 5.7引入了一种新的半同步方案：Loss-Less半同步复制。针对上面这个图，“Waiting Slave dump”被调整到“Storage Commit”之前。

当然，之前的半同步方案同样支持，MySQL 5.7.2引入了一个新的参数进行控制
rpl_semi_sync_master_wait_point.

rpl_semi_sync_master_wait_point有两种取值

AFTER_SYNC：个即新的半同步方案，Waiting Slave dump在Storage Commit之前。

AFTER_COMMIT：老的半同步方案，如图所示。

2.6.4半同步复制的安装部署

要想使用半同步复制，必须满足以下几个条件：

1) MySQL 5.5及以上版本

2) 变量have_dynamic_loading为YES

3) 异步复制已经存在

2.6.4.1安装插件

2.6.4.2写入配置文件

2.6.4.3重启从上的IO线程

2.6.4.4检查半同步运行状态

MySQL备份

percona-xtrabackup-24-2.4.12-1.el6.x86_64.rpm

percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

1、备份介绍

备份分为逻辑备份和物理备份，mysql使用mysqldump命令进行逻辑备份，使用xtrabackup进行物理备份，通常情况下备份是指物理备份。mysqldump是mysql软件包自带。

1.1 xtrabackup安装

#centos7
yum localinstall percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm libev-4.15-3.el7.x86_64.rpm
 
#centos6
yum localinstall libev-4.03-3.el6.x86_64.rpm percona-xtrabackup-24-2.4.12-1.el6.x86_64.rpm

2、备份

2.1全量备份

全量备份分成2步：备份数据库相关文件，应用备份期间产生的数据库日志。

innobackupex --user=root --host=127.0.0.1 --port=3307 --parallel=4 --no-timestamp /data/backup/201408301600 --defaults-file=/opt/mariadb/etc/my_3307.cnf
innobackupex --apply-log --use-memory=1G /data/backup/201408301600
其中:
innobackupex是备份命令
--user是数据库用户
--host是数据库主机
--port是数据库端口
--parallel是备份并行度
--no-timestamp是不使用自动生成的时间戳目录
/data/backup/201408301600是备份文件所在目录
--defaults-file是数据库配置文件
--apply-log是应用数据库备份期间生产的日志到备份集，确保生成一致性的备份结果
--use-memory是指缓存大小

2.2增量备份

增量备份的原理是通过对比LSN的信息，来找到被更变的数据，当有了修改操作时，LSN号会改变，和上一次全备的差异LSN号做对比，则可将差异数据备份出来。

增量备份分成3步：备份数据库相关文件的增量，应用备份期间产生的日志，把增量合并到全量备份。

innobackupex --user=root --host=127.0.0.1 --port=3307 --parallel=4 --no-timestamp --incremental /data/backup/201408301610 --incremental-basedir=/data/backup/201408301600 --defaults-file=/opt/mariadb/etc/my_3307.cnf
innobackupex --apply-log --redo-only /data/backup/201408301600 --incremental-dir=/data/backup/201408301610
innobackupex --apply-log --redo-only  /data/backup/201408301600

2.3恢复

备份所有的数据文件和日志文件，使用以下命令进行恢复

innobackupex --copy-back --parallel=4 --use-memory=1G /data/backup/201408301600 --defaults-file=/opt/mariadb/etc/my_3307.cnf

3、备份脚本

--全备脚本
[root@localhost backup_script]# cat back_com.sh
#!/bin/bash
backupdir=$1/ `date +"%Y%m%d%H"`
mkdir $backupdir
/usr/bin/innobackupex --user=root --host=127.0.0.1 --port=3307 --parallel=4 --no-timestamp $backupdir --defaults-file=/opt/mariadb/etc/my_3307.cnf
innobackupex --apply-log --use-memory=1G $backupdir
 
--增量备份脚本
[root@localhost backup_script]# cat back_inc.sh
#!/bin/bash
str=''
for b in `ls -lR $1 | grep "^d" | awk '{print $9}'`;
do
if [ "$b" \> "$str" ];
then
  str=$b
fi
done
 
backupdir=$1/inc/ `date +"%Y%m%d%H"`
mkdir $backupdir -p
 
# incremental backup
/usr/bin/innobackupex --user=root --password=123456 --host=127.0.0.1 --port=3307 --parallel=4 --no-timestamp --incremental --defaults-file=/opt/easycluster/etc/my.cnf  --incremental-basedir= $backupdir  $str
innobackupex --apply-log --redo-only $str --incremental-dir= $backupdir
innobackupex --apply-log --redo-only $str
 
 
--删除7个月以前备份
[root@localhost backup_script]# cat rmfile.sh
#!/bin/bash
# rm 7 month ago full back
cd $1
a=$(ls $1)
str=$(date -d "-7 month" +%F)
for b in $a ; do
if [ "$b" \< "$str" ]
 then
rm -rf "$b"
fi
done
#echo $str
# rm 6 month ago incremental back
cd $2
a=$(ls $2)
str=$(date -d "-6 month" +%F)
for b in $a ; do
if [ "$b" \< "$str" ]
 then
rm -rf "$b"
fi
done
#echo $str

4、定时任务

[root@localhost log]# crontab -l
0 1 1 * * /opt/backup/backup_script/back_com.sh /opt/backup >/opt/backup/log/back.log 2>&1
0 8 1 * * /opt/backup/backup_script/rmfile.sh /opt/backup/full
0 8 1 * * /opt/backup/backup_script/rmfile.sh /opt/backup/inc
0 1 * * 5 /opt/backup/backup_script/back_inc.sh /opt/backup >/opt/backup/log/back.log 2>&1

MY.CNF配置

[client]
port	= 3306
socket	= /data/data/mysql.sock

[mysql]
prompt="\u@\h \R:\m:\s [\d]> "
no-auto-rehash

[mysqld]
# basic settings #
user = mysql
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
autocommit = 0
server-id = 3
character_set_server=utf8mb4
datadir=/data/data
basedir=/data/mysql
#transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
max_allowed_packet = 128M
event_scheduler = 1

# connection #
interactive_timeout = 1800
wait_timeout = 1800
lock_wait_timeout = 1800
skip_name_resolve = 1
max_connections = 1024
max_user_connections = 512
max_connect_errors = 102400

# table cache performance settings
table_open_cache = 4096
table_definition_cache = 4096
table_open_cache_instances = 64

# session memory settings #
read_buffer_size = 4M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
tmp_table_size = 32M
join_buffer_size = 32M
thread_cache_size = 64

# log settings #
log_error = error.log
log_bin = binlog
log_error_verbosity = 2
general_log_file = general.log
slow_query_log = 1
slow_query_log_file = slow.log
log_queries_not_using_indexes = 1
#log_slow_admin_statements = 1
#log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
#expire_logs_days = 90
long_query_time = 2
#min_examined_row_limit = 100
#binlog-rows-query-log-events = 1
#log-bin-trust-function-creators = 1
#expire-logs-days = 90
log-slave-updates = 1

# innodb settings #
innodb_page_size = 16384
innodb_buffer_pool_size = 2048M
innodb_buffer_pool_instances = 1
#innodb_buffer_pool_load_at_startup = 1
#innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 4096
innodb_lock_wait_timeout = 5
innodb_io_capacity = 200
innodb_io_capacity_max = 200
innodb_flush_method = O_DIRECT
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
#innodb_flush_neighbors = 0
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_log_buffer_size = 32M
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 32
#innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 32M
innodb_write_io_threads = 8
innodb_read_io_threads = 16 
innodb_file_per_table = 1
innodb_stats_persistent_sample_pages = 64
innodb_autoinc_lock_mode = 2
#innodb_online_alter_log_max_size=512MG
innodb_open_files=4096

# replication settings #
master_info_repository = TABLE
relay_log_info_repository = TABLE
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
binlog_format = ROW
binlog_rows_query_log_events = 1
relay_log = relay.log
relay_log_recovery = 1
#slave_skip_errors = ddl_exist_errors
#slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'

# semi sync replication settings #
#plugin-load = "group_replication.so;validate_password.so;semisync_master.so;semisync_slave.so"
#loose_rpl_semi_sync_master_enabled = 1
#loose_rpl_semi_sync_master_timeout = 3000
#loose_rpl_semi_sync_slave_enabled = 1

# password plugin #
#validate_password_policy = STRONG
#validate-password = FORCE_PLUS_PERMANENT

# perforamnce_schema settings
performance-schema-instrument='memory/%=COUNTED'
performance_schema_digests_size = 40000
performance_schema_max_table_instances = 40000
performance_schema_max_sql_text_length = 4096
performance_schema_max_digest_length = 4096

[mysqld-5.7]
# new innodb settings #
#loose_innodb_numa_interleave = 1
#innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 16
#innodb_undo_log_truncate = 1
#innodb_max_undo_log_size = 1G
#innodb_purge_rseg_truncate_frequency = 128

# new replication settings #
#slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 16
#slave_preserve_commit_order = 1
#slave_transaction_retries = 128
# other change settings #
#binlog_gtid_simple_recovery = 1
#log_timestamps = system
#show_compatibility_56 = on

# group replication settings
#plugin-load = "group_replication.so;validate_password.so;semisync_master.so;semisync_slave.so"
#transaction-write-set-extraction = XXHASH64
#report_host = 127.0.0.1 # optional for group replication
#binlog_checksum = NONE # only for group replication
#loose_group_replication = FORCE_PLUS_PERMANENT
#loose_group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
#loose_group_replication_compression_threshold = 100
#loose_group_replication_flow_control_mode = 0
#loose_group_replication_single_primary_mode = 0
#loose_group_replication_enforce_update_everywhere_checks = 1
#loose_group_replication_transaction_size_limit = 10485760
#loose_group_replication_unreachable_majority_timeout = 120
#loose_group_replication_start_on_boot = 0

# group replicaiton
#transaction_write_set_extraction=XXHASH64
#binlog_checksum=none
#slave_parallel_type=LOGICAL_CLOCK
#slave_preserve_commit_order=on
#loose_group_replication_group_name='0c6d3e5f-90e2-11e6-802e-842b2b5909d6'
#loose_group_replication_start_on_boot=OFF
#loose_group_replication_bootstrap_group=OFF
#loose_group_replication_local_address='10.0.0.19:6606'
#loose_group_replication_group_seeds='10.0.0.7:6606,10.0.0.15:6606'