zabbix数据库正常运行的情况下，做mysql主从同步添加从库操作
一，在从库主机上安装mysql（注：版本与主库master一致）
1.1，创建用户
groupadd mysql
useradd -r -g mysql mysql
1.2，上传，解压安装包，并做软链接
tar zxf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
cd /usr/local/
ln -s mysql-5.7.26-linux-glibc2.12-x86_64 mysql
1.3，编辑配置文件（注：拷贝主库master的配置文件进行修改）
cp /etc/my.cnf{,.bak}
cat >/etc/my.cnf<<\EOF
#FOR Percona 5.7
#thread_handling = pool-of-threads
#thread_pool_oversubscribe = 16
#extra_port = 33306
#extra_max_connections = 2
[client]
port = 3306
socket = /data/3306/dbdata/mysql.sock
[mysql]
no-auto-rehash
max_allowed_packet = 128M
#pager="less -i -n -S"
#tee=/opt/mysql/query.log
#prompt = '(zabbix)\u@\h [\d]> '
default-character-set=utf8
[mysqldump]
quick
max_allowed_packet = 128M
#myisam_max_sort_file_size = 10G
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
[mysqld_safe]
#malloc-lib= /usr/lib64/libjemalloc.so
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /data/3306/slowlog/mysqld_multi.log
[mysqld]
#===============>[mysql Base]<=================
bind-address = 0.0.0.0
port = 3306
user = mysql
basedir = /usr/local/mysql
datadir = /data/3306/dbdata
tmpdir = /data/3306/tmp
socket = /data/3306/dbdata/mysql.sock
skip_name_resolve = 1
innodb_file_per_table = 1
default-storage-engine = InnoDB
character-set-server = utf8
lower_case_table_names = 1
transaction_isolation = READ-COMMITTED
#sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
symbolic-links = 0
skip_name_resolve = 1
#skip-grant-tables
#skip-networking
#skip-external-locking
explicit_defaults_for_timestamp = 1
default-time-zone = '+8:00'
#============>[mysql procedure,event]<===========
log_bin_trust_function_creators = 1
event_scheduler=1
#========================>[mysql perfomance]<===============
back_log = 1024
max_connections = 3000
max_connect_errors = 6000
max_user_connections=1000
external-locking = FALSE
connect_timeout = 20
open_files_limit = 65535
table_open_cache = 1024
interactive_timeout = 600
wait_timeout = 28800
max_allowed_packet = 16M
max_heap_table_size = 1024M
tmp_table_size = 128M
join_buffer_size = 16M
sort_buffer_size = 16M
read_rnd_buffer_size = 8M
read_buffer_size = 4M
thread_cache_size = 64
# query_cache_type = OFF
# query_cache_size = 8M
# query_cache_limit = 2M
key_buffer_size = 256M
performance_schema = 1
performance_schema_instrument = '%=on'
#========================>[mysql log related]<===============
log_bin = /data/3306/binlog/mysql-bin ##从库不需要开启binlog
binlog_format = row
binlog_cache_size = 256M
max_binlog_cache_size = 2G
max_binlog_size = 1G
expire_logs_days = 30 ##binlog过期时间可以设成7天
slow_query_log = 1
long_query_time = 1.5
slow_query_log_file = /data/3306/slowlog/mysql-slow.log
#general_log = ON
#general_log_file = /data/3306/slowlog/sql.log
log_timestamps = system
log-output="FILE"
log-error = /data/3306/slowlog/error.log
log_error_verbosity = 2
log-slow-slave-statements = 1
#log_slow_verbosity = "full"
#log-queries-not-using-indexes = 1
#log_throttle_queries_not_using_indexes = 10
log-slow-admin-statements = 1
#========================>[ mysql Replication]<================
log_slave_updates = true
master-info-repository = TABLE
relay_log_info_repository = TABLE
relay_log = /data/3306/relaylog/relay-bin
max_relay_log_size = 1G
relay_log_purge = OFF ##从库要修改成开启自动清理
#relay_log_recovery = 1 ##从库要修改成开启状态
sync_master_info = 1
sync_binlog = 1
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers = 16
binlog_checksum = CRC32
master_verify_checksum = 1
slave_sql_verify_checksum = 1
binlog_rows_query_log_events = 1
##从slave上的server-id要大于master主的
server-id = 1
report_port = 3306
report_host = 192.168.10.75
slave-skip-errors=1022,1032,1062
slave_allow_batching = 1
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
##slave从上要开启只读模式
read_only = 1
##slave从上要开启只读模式
super_read_only = 1
slave-net-timeout=30
#gtid_mode = on
#enforce_gtid_consistency = 1
#auto_increment_increment=2
#auto_increment_offset=1
#binlog-ignore=mysql
#binlog-ignore=information_schema
#replicate-do-db=aa
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=sys.%
#semi replication
#plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
plugin_load = "semisync_master.so;semisync_slave.so;validate_password.so"
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=3000 # 3000 second
rpl_semi_sync_slave_enabled=1
rpl_semi_sync_master_wait_for_slave_count=1
#group relplication
binlog_group_commit_sync_delay=10
binlog_group_commit_sync_no_delay_count=20
#=======>[myisam engine]<=========
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
lock_wait_timeout = 3600
#========>[innodb engine]<==========
#innodb_data_home_dir = /data/3306/dbdata
#innodb_log_group_home_dir = /data/3306/slowlog
innodb_data_file_path = ibdata1:1G:autoextend
innodb_buffer_pool_size = 8G
#innodb_buffer_pool_instances = 8
innodb_spin_wait_delay = 30
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 64M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout= 1
innodb_print_all_deadlocks = 1
innodb_status_file = 1
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 1000
#innodb_max_io_capacity = 2000
innodb_flush_neighbors = 1
innodb_adaptive_flushing = 1
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_sync_spin_loops = 100
#innodb_support_xa = 0
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 500
#innodb_file_io_threads = 4
innodb_concurrency_tickets = 1000
#innodb_file_format = Barracuda
innodb_purge_threads=4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_purge_batch_size = 32
innodb_old_blocks_pct=75
innodb_change_buffering=all
innodb_online_alter_log_max_size = 4G
internal_tmp_disk_storage_engine = InnoDB
innodb_stats_on_metadata = 0
innodb_max_undo_log_size=4G
innodb_status_output = 0
innodb_status_output_locks = 0
#=========>[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"
#======>[mysql security]<==============
validate_password_policy = STRONG
validate-password = FORCE_PLUS_PERMANENT
validate_password_dictionary_file = /data/3306/dbdata/pwd_dict_file.txt
EOF
1.4，创建目录、以及授权
mkdir -pv /data/3306/{dbdata,binlog,relaylog,slowlog,tmp}
chown -R mysql.mysql /data/3306/
chown -R mysql.mysql /usr/local/mysql/
1.5，初始化
cd /usr/local/mysql-5.7.26-linux-glibc2.12-x86_64
bin/mysqld --initialize --basedir=/usr/local/mysql --datadir=/data/3306/dbdata --user=mysql
1.6，添加环境变量
echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql-5.7.sh
echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql-5.7.26.conf && ldconfig
exec bash
1.7，做启动项
cp /usr/local/mysql-5.7.26-linux-glibc2.12-x86_64/support-files/mysql.server /etc/init.d/mysql
#配置启动文件
vim /etc/init.d/mysql
basedir=/usr/local/mysql
datadir=/data/3306/dbdata
#赋权、添加开机自启
chmod +x /etc/init.d/mysql
chkconfig --level 35 mysql on
service mysql start
1.8，登录mysql，修改密码
#获取mysql密码
grep 'root@localhost:' /data/3306/slowlog/error.log | awk -F 'root@localhost: ' '{print $2}'
#登录mysql，并修改密码
mysql -uroot -p
Enter password:
mysql>
mysql> alter user root@localhost identified by '123456';
#创建zabbix库
mysql> create database zabbix character set utf8 collate utf8_bin;
#创建用户
mysql> grant all privileges on *.* to zabbix@'127.0.0.1' identified by '123456';
mysql> flush privileges;
1.9，在主库上创建主从同步用户
mysql> grant Replication slave,Replication client on *.* to repluser@'192.168.10.75' identified by 'Replpass123456';
mysql> flush privileges;
二，安装备份工具，进行物理备份（在数据量过大的情况下使用xtrabackup）
2.1、下载安二进制包：
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.7/binary/tarball/percona-xtrabackup-2.4.7-Linux-x86_64.tar.gz
2.2、解压并创建软连接：
tar zxvf percona-xtrabackup-2.4.28-Linux-x86_64.glibc2.17.tar.gz -C /usr/local/
mv percona-xtrabackup-2.4.28-Linux-x86_64.glibc2.17 /usr/local/xtrabackup
ln -s /usr/local/xtrabackup/bin/* /usr/bin/
2.3、查看版本：
xtrabackup -vresion
xtrabackup version 2.4.28 based on MySQL server 5.7.40 Linux (x86_64) (revision id: 44a8f7b)
2.4，备份
mkdir -p /zbx_backup
innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --host=127.0.0.1 /zbx_backups
xtrabackup: Transaction log of lsn (17963005634699) to (17963391559436) was copied.
230509 20:26:57 completed OK!
#--defaults-file 指定配置文件
#--user=root 指定备份用户
#--password=123456 指定备份用户密码
#--host 指定主机
#/zbx_backups 指定备份目录
#备份目录会以时间戳命名
ll /zbx_backups/2023-05-09_20-16-54
-rw-r----- 1 root root 418 Jul 30 11:01 backup-my.cnf #备份用到的配置选项信息文件
-rw-r----- 1 root root 79691776 Jul 30 11:01 ibdata1 #数据文件
drwxr-x--- 2 root root 20 Jul 30 11:01 kim
drwxr-x--- 2 root root 4096 Jul 30 11:01 mysql
drwxr-x--- 2 root root 4096 Jul 30 11:01 performance_schema
drwxr-x--- 2 root root 20 Jul 30 11:01 repppp
drwxr-x--- 2 root root 4096 Jul 30 11:01 wordpress
-rw-r----- 1 root root 21 Jul 30 11:01 xtrabackup_binlog_info #mysql服务器当前正在使用的二进制日志文件和此时二进制日志时间的位置信息文件
-rw-r----- 1 root root 113 Jul 30 11:01 xtrabackup_checkpoints #备份的类型、状态和LSN状态信息文件
-rw-r----- 1 root root 482 Jul 30 11:01 xtrabackup_info
-rw-r----- 1 root root 2560 Jul 30 11:01 xtrabackup_logfile #备份的日志文件
三，进行主从同步配置
3.1，将备份好的数据传输至从库主机上
rsnyc -avrP /zbx_backup 192.168.10.75:/data
3.2，将主库备份数据恢复到新搭建的从库
#停止从库mysql
service mysql stop
#恢复备份
innobackupex --apply-log /data/zbx_backup/2023-04-12_18-46-59/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=33554432 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1
xtrabackup: recognized client arguments:
230509 21:13:21 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
InnoDB: 5.7.40 started; log sequence number 17963391562773
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 17963391562792
230509 21:15:45 completed OK!
mv /data/3306/dbdata /data/3306/dbdata_bak
rm -rf /data/3306/dbdata/*
innobackupex --datadir=/data/3306/dbdata/ --copy-back /data/zabbix_backup/2023-04-12_18-46-59/
chown -R mysql.mysql /data/3306/dbdata/
或者：
innobackupex --apply-log /data/zbx_backup/2023-04-12_18-46-59/
mv /data/3306/dbdata /data/3306/dbdata_bak
rm -rf /data/3306/dbdata/*
cp -a /data/zabbix_backup/2023-04-12_18-46-59/* /data/3306/dbdata/
chown -R mysql.mysql /data/3306/dbdata/
#启动mysql
service mysql start
3.3，查看备份数据的二进制信息
ll /data/zbx_backups/2023-05-09_20-16-54
-rw-r----- 1 root root 418 Jul 30 11:01 backup-my.cnf #备份用到的配置选项信息文件
-rw-r----- 1 root root 79691776 Jul 30 11:01 ibdata1 #数据文件
drwxr-x--- 2 root root 20 Jul 30 11:01 kim
drwxr-x--- 2 root root 4096 Jul 30 11:01 mysql
drwxr-x--- 2 root root 4096 Jul 30 11:01 performance_schema
drwxr-x--- 2 root root 20 Jul 30 11:01 repppp
drwxr-x--- 2 root root 4096 Jul 30 11:01 wordpress
-rw-r----- 1 root root 21 Jul 30 11:01 xtrabackup_binlog_info #mysql服务器当前正在使用的二进制日志文件和此时二进制日志时间的位置信息文件
-rw-r----- 1 root root 113 Jul 30 11:01 xtrabackup_checkpoints #备份的类型、状态和LSN状态信息文件
-rw-r----- 1 root root 482 Jul 30 11:01 xtrabackup_info
-rw-r----- 1 root root 2560 Jul 30 11:01 xtrabackup_logfile #备份的日志文件
#查看xtrabackup_binlog_info
cat xtrabackup_binlog_info
mysql-bin.003116 629098765
3.4，在从库进行主从配置
#进行主从配置
mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.74', \
MASTER_USER='repluser',\
master_password='Replpass123456',\
MASTER_PORT=3306,\
MASTER_LOG_FILE='mysql-bin.003116',\
MASTER_LOG_POS=629098765,\
MASTER_CONNECT_RETRY=10;
#开启主从
mysql> start slave;
3.5，查看主从状态
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: System lock
Master_Host: 192.168.10.74
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.003116
Read_Master_Log_Pos: 656809037
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 4845834
Relay_Master_Log_File: mysql-bin.003116
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: test.%,information_schema.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 633944279
Relay_Log_Space: 27710793
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 5449
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 3280007f-bc34-11e9-a8cc-286ed488c95d
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Reading event from the relay log
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.04 sec)
532
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
