MySQL主从复制GTID方式部署

最新推荐文章于 2023-12-14 09:05:38 发布

db_murphy

最新推荐文章于 2023-12-14 09:05:38 发布

阅读量508

点赞数

分类专栏： mysql 文章标签： mysql GTID 同步部署

本文链接：https://blog.csdn.net/db_murphy/article/details/92255725

版权

mysql 专栏收录该内容

55 篇文章 2 订阅

订阅专栏

																						note-taker：Ethan_Yang
																				 	recording time: 2019/06/15
																							number of docs:2

系统信息：

操作系统版本：Red Hat Enterprise Linux Server release 7.3 (Maipo)
主备方式：MYSQL （一主一从架构），串行同步
同步方式：半同步
192.168.0.100         mysql 主库
192.168.0.101         mysql 从库
MySQL版本信息：mysql  Ver 8.0.16-commercial

一、MySQL安装

下载安装二进制安装包:https://dev.mysql.com/downloads/mysql/

1、解压数据库安装包

[root@calt-db01 mysqlsoft]#tar -xzvf mysql-8.0.16-linux-glibc2.12-x86_64.tar

2、作软链接到/usr/local/mysql

[root@calt-db01 mysqlsoft]# ln -s mysql-8.0.16-linux-glibc2.12-x86_64  /usr/local/mysql

3、创建用户

[root@calt-db01 mysqlsoft]# groupadd mysql
[root@calt-db01 mysqlsoft]# useradd -g mysql  -s  /sbin/nologin -d /usr/local/mysql/  -M mysql

4、环境变量

[root@calt-db01 mysqlsoft]# echo PATH=$PATH:/usr/local/mysql/bin >>/etc/profile
[root@calt-db01 mysqlsoft]# source /etc/profile

5、创建必须的目录

[root@calt-db01 mysqlsoft] mkdir -p /mysql/3306/{data,tmp,logs}

6、my.cnf配置文件(部署从库需要修改server_id，rpl开头参数为半同步设置，从库不需要添加)

[client]
port = 3306
socket = /mysql/3306/mysql.sock
 
[mysql]
prompt="\u@db1 \R:\m:\s [\d]> "
no-auto-rehash
 
[mysqld]
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /mysql/3306/data/
socket = /mysql/3306/mysql.sock
pid-file = mysql113.pid
character-set-server = utf8mb4
skip_name_resolve = 1
open_files_limit    = 65535
back_log = 1024
max_connections = 1000
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 768
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M

slow_query_log = 1
log_timestamps = SYSTEM
slow_query_log_file = /mysql/3306/data/slow.log
Federated

log-error = /mysql/3306/data/error.log
long_query_time = 0.1
log_queries_not_using_indexes =1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
server-id = 3306110
log-bin = /mysql/3306/logs/mybinlog
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
expire_logs_days = 7
master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
binlog_format = row
binlog_checksum = 1
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30
rpl_semi_sync_master_enabled = 1;
rpl_semi_sync_master_timeout = 3000;

transaction_isolation = read-committed
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 10240M
innodb_buffer_pool_instances = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_max_undo_log_size = 4G
innodb_undo_directory = undolog
innodb_undo_tablespaces = 95
 
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_sync = 0
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
internal_tmp_disk_storage_engine = InnoDB
innodb_stats_on_metadata = 0

**#some var for MySQL 8.0**
innodb_checksums = 1
#innodb_file_format = Barracuda
#innodb_file_format_max = Barracuda
query_cache_size = 0
query_cache_type = 0
innodb_undo_logs = 128
 
innodb_status_file = 1
**#注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快**
innodb_status_output = 0
innodb_status_output_locks = 0
 
#performance_schema
performance_schema = 1
performance_schema_instrument = '%=on'
 
**#innodb monitor**
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"
 
[mysqldump]
quick
max_allowed_packet = 512M

7、目录权限

[root@calt-db01 /]# chown -R mysql:mysql /mysql/3306/
[root@calt-db01]# chown -R mysql:mysql /usr/local/mysql

8、数据库初始化

[root@calt-db01r /]#/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql/  --datadir=/mysql/3306/data/

9、数据库临时生成的初始密码

[root@calt-db01]# more /mysql/3306/data/error.log

在这里插入图片描述

10、启动数据库

[root@calt-db01 support-files]# pwd
/usr/local/mysql/support-files
[root@calt-db01r support-files]#cp mysql.server /etc/init.d/mysqld
[root@calt-db01 support-files]#/etc/init.d/mysqld start
Starting MySQL.. SUCCESS!

11、改root密码

[root@calt-db01  support-files]# mysqladmin -uroot -p password 'root'
Enter password: //此处输入/mysql/3306/data/error.log中的初始密码
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety

12、登录数据库

[root@EQZ-DEV-DB ~]# mysql -uroot -pmysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.16-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

二、主从复制搭建

1、主库备份

[root@calt-db01 ~]# mysqldump -uroot -proot --single-transaction --master-data=2 --set-gtid-purged=OFF  -A >20190614.sql

2、主库创建复制权限用户

root@db1 22:12:  [(none)]> grant replication slave on *.* to 'repl'@'%' identified by 'repl';

3、备份文件传到从库

[root@calt-db01 ~]# scp -r 20190614.sql  root@192.168.0.101:/root

4、从库导入备份(备库上操作)

[root@calt-db02 ~]# mysql -uroot -proot <20190614.sql

5、查看备份文件pos点

在这里插入图片描述

6、从库配置主从复制

1）
root@db1 22:12:  [(none)]>
CHANGE MASTER TO MASTER_HOST='192.168.0.100',
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_PORT=3306,
master_log_file='mybinlog.000002',
master_log_pos=813283428;
2) Myql> Start slave;
3) Myql> Show slave status\G

7、安装半同步配置

1）Master配置
mysql> install plugin rpl_semi_sync_master soname ‘semisync_master.so’;
mysql> set global rpl_semi_sync_master_enabled = 1;
mysql> set global rpl_semi_sync_master_timeout = 3000;
2）slave配置
mysql> install plugin rpl_semi_sync_slave soname ‘semisync_slave.so’;
mysql> set global rpl_semi_sync_slave_enabled = 1;

结语：

上述步骤只是部署了GTID一主一从的半同步架构，并没有配置高可用性架构，主库在发生异常不能提供数据服务时，会导致数据服务的中断。后续推文将进行高可用性的配置，使用第三方脚本keepalived或者MHA的方式。当然，MySql官方也有自己的高可用性架构欧Innodb cluster 和NDB cluster，后续将向大家持续进行实验和推文。
在正常的运维中，还应有数据库备份和恢复，此文尚未提及，这里简单提下有两种方式，一是使用mysql的企业版mysqlbackup进行物理备份，另一种就是使用mysqldump进行逻辑备份。当然也有一些第三方的备份方式，如：xtrabackup/innobackupex。
Mysql官方企业版还有监控工具monitor，此为网页版，页面布局个人感觉比oracle的em更人性化；当大家手中的数据较多时，可方便进行维护管理。