汽车cms_网页游戏魔域变态版_广告投放是什么工作_国内快速建站

createtime：2024-1-25

updatetime：2025-2-17

内容概述：

该文档涵盖如何安装mysql8，并详细介绍了如何使用XtraBackup工具搭建从库。
该版本支持rocks，innodb引擎混合部署的数据实例。

解决痛点：备份工具无法同时支持 rocks，innodb双引擎的备份。

OS：CentOS 7.9

MySQL： Percona 8.0.35-27

依赖：

cmake：cmake3.5.1+

gcc：devtoolset-8

一、下载mysql源码包和cmake源码包

下载链接：Software Downloads - Percona

percona-server-8.0.35-27.tar.gz

cname源码包下载地址

Index of /files/v3.15

cmake-3.15.0-rc1.tar.gz

安装依赖

# yum -y install numactl

#yum install openldap-devel -y

# yum install libxml2 libxml2-devel openssl openssl-devel bzip2 bzip2-devel libcurl libcurl-devel libjpeg libjpeg-devel libpng libpng-devel freetype freetype-devel gmp gmp-devel libmcrypt libmcrypt-devel readline readline-devel libxslt libxslt-devel zlib zlib-devel glibc glibc-devel glib2 glib2-devel ncurses curl gdbm-devel db4-devel libXpm-devel libX11-devel gd-devel gmp-devel expat-devel xmlrpc-c xmlrpc-c-devel libicu-devel libmcrypt-devel libmemcached-devel php-gd -y

二、编译安装cmake

查看当前版本

# cmake --version

cmake version 2.8.12.2

2.一、解压cmake并进入目录

tar zxf cmake-3.15.0-rc1.tar.gz

cd cmake-3.15.0-rc1

2.二、配置、编译、安装

#./configure --prefix=/usr/local/cmake3.15.0

注意： CentOS 6.5 执行报错

#make -j 40

# make install

2.三、作cmake命令软链接，查看cmake版本

# ln -s /usr/local/cmake3.15.0/bin/cmake /usr/bin/cmake3.15.0

# /usr/bin/cmake3.15.0 --version

cmake version 3.15.0-rc1

安装GCC/G++ 8

yum install centos-release-scl -y

yum install devtoolset-8-gcc devtoolset-8-gcc-c++ -y

scl enable devtoolset-8 -- bash

mysql boost

mysql boost下载 JFrog Landing

mysql boost下载 :https://dev.mysql.com/downloads/mysql/8.0.html?os=src

JFrog Landing

tar -jxvf boost_1_77_0.tar.bz2

cmake编译

安装一些基本的包，避免cmake报错

yum -y install wget cmake gcc gcc-c++ncurses ncurses-devel libaio-devel openssl openssl-devel

yum install cyrus-sasl-devel cyrus-sasl-scram

yum install libcurl-devel

tar xzf percona-server-8.0.35-27.tar.gz

cd percona-server-8.0.35-27

mkdir build

cd build （cd /opt/app/percona-server-8.0.35-27/build）

/usr/bin/cmake3.15.0 ../ -DCMAKE_INSTALL_PREFIX=/opt//mysql_3341 -DMYSQL_DATADIR=/opt//mysql_3341/data -DSYSCONFDIR=/opt//mysql_3341/data -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_general_ci -DWITH_BOOST=/opt/app/boost_1_77_0/boost_1_77_0 -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ROCKSDB=1 -DMYSQL_TCP_PORT=3341 -DENABLED_LOCAL_INFILE=1 -DWITH_EMBEDDED_SERVER=OFF -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=gbk,gb2312,utf8mb4,ascii -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITHOUT_PARTITION_STORAGE_ENGINE=1 -DWITH_FAST_MUTEXES=1 -DWITH_DEBUG=0 -DWITH_EMBEDDED_SERVER=1 -DBUILD_CONFIG=mysql_release -DFEATURE_SET=community -DCMAKE_BUILD_TYPE=RelWithDebInfo -DCMAKE_C_COMPILER=/opt/rh/devtoolset-8/root/usr/bin/gcc -DCMAKE_CXX_COMPILER=/opt/rh/devtoolset-8/root/usr/bin/g++

make -j 24

make install

初始化数据库

/opt//mysql_3341/bin/mysqld --initialize --user=mysql --basedir=/opt//mysql_3341 --datadir=/opt//mysql_3341/data

chown -R mysql.mysql /opt/mysql_3341

制作my.cnf配置文件：

vim /opt//mysql_3341/my.cnf

## 本配置文件主要适用于MySQL 8.0版本

#

[client]

port = 3341

socket = /opt//mysql_3341/mysql.sock

[mysql]

prompt = "\u@db_hx_196_188.bodaodao.org \R:\m:\s [\d]> "

no_auto_rehash

loose-skip-binary-as-hex

[mysqld]

user = mysql

port = 3341

#主从复制或MGR集群中，server_id记得要不同

#另外，实例启动时会生成 auto.cnf，里面的 server_uuid 值也要不同

#server_uuid的值还可以自己手动指定，只要符合uuid的格式标准就可以

#server_id = 3341

#我司一般为ip后2位+端口后2位 by 薄刀刀

server_id = 19618841

basedir = /opt//mysql_3341

datadir = /opt//mysql_3341/data

socket = /opt//mysql_3341/mysql.sock

pid_file = db_hx_196_188.org.pid

character_set_server = UTF8MB4

skip_name_resolve = 1

#若你的MySQL数据库主要运行在境外，请务必根据实际情况调整本参数

default_time_zone = "+8:00"

#启用admin_port，连接数爆满等紧急情况下给管理员留个后门

admin_address = '127.0.0.1'

#admin_port = 33062

admin_port = 33412

#performance setttings

lock_wait_timeout = 3600

open_files_limit = 65535

back_log = 1024

max_connections = 512

max_connect_errors = 1000000

table_open_cache = 1024

table_definition_cache = 1024

thread_stack = 512K

sort_buffer_size = 4M

join_buffer_size = 4M

read_buffer_size = 8M

read_rnd_buffer_size = 4M

bulk_insert_buffer_size = 64M

thread_cache_size = 768

interactive_timeout = 600

wait_timeout = 600

tmp_table_size = 32M

max_heap_table_size = 32M

#log settings

log_timestamps = SYSTEM

log_error = /opt/mysql_3341/data/error.log

log_error_verbosity = 3

slow_query_log = 1

log_slow_extra = 1

slow_query_log_file = /opt/mysql_3341/data/slow.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

log_bin = /opt/mysql_3341/data/mybinlog

binlog_format = ROW

sync_binlog = 1 #MGR环境中由其他节点提供容错性，可不设置双1以提高本地节点性能

binlog_cache_size = 4M

max_binlog_cache_size = 2G

max_binlog_size = 1G

binlog_rows_query_log_events = 1

binlog_expire_logs_seconds = 604800

#MySQL 8.0.22前，想启用MGR的话，需要设置binlog_checksum=NONE才行

binlog_checksum = CRC32

gtid_mode = ON

enforce_gtid_consistency = TRUE

#myisam settings

key_buffer_size = 32M

myisam_sort_buffer_size = 128M

#replication settings

relay_log_recovery = 1

slave_parallel_type = LOGICAL_CLOCK

slave_parallel_workers = 64 #可以设置为逻辑CPU数量的2倍

binlog_transaction_dependency_tracking = WRITESET

slave_preserve_commit_order = 1

slave_checkpoint_period = 2

replication_optimize_for_static_plugin_config = ON

replication_sender_observe_commit_only = ON

# async replication settings

skip_slave_start

#innodb settings

transaction_isolation = REPEATABLE-READ

#innodb_buffer_pool_size = 45056M

innodb_buffer_pool_instances = 4

innodb_data_file_path = ibdata1:12M:autoextend

innodb_flush_log_at_trx_commit = 1 #MGR环境中由其他节点提供容错性，可不设置双1以提高本地节点性能

innodb_log_buffer_size = 32M

innodb_log_file_size = 1G #如果线上环境的TPS较高，建议加大至1G以上，如果压力不大可以调小

innodb_log_files_in_group = 3

loose-innodb_redo_log_capacity = 3G

innodb_max_undo_log_size = 4G

# 根据您的服务器IOPS能力适当调整

# 一般配普通SSD盘的话，可以调整到 10000 - 20000

# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000

innodb_io_capacity = 4000

innodb_io_capacity_max = 8000

innodb_open_files = 65535

innodb_flush_method = O_DIRECT

innodb_lru_scan_depth = 4000

innodb_lock_wait_timeout = 10

innodb_rollback_on_timeout = 1

innodb_print_all_deadlocks = 1

innodb_online_alter_log_max_size = 4G

innodb_print_ddl_logs = 1

innodb_status_file = 1

#注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log_error文件增长较快

innodb_status_output = 0

innodb_status_output_locks = 1

innodb_sort_buffer_size = 67108864

innodb_adaptive_hash_index = OFF

#提高索引统计信息精确度

innodb_stats_persistent_sample_pages = 500

innodb_adaptive_hash_index = 0

loose-sql_generate_invisible_primary_key = ON

#innodb monitor settings

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"

#pfs settings

performance_schema = 1

#performance_schema_instrument = '%memory%=on'

loose-performance_schema_instrument = '%lock%=on'

[mysqldump]

quick

启动

/opt/mysql_3341/bin/mysqld_safe --defaults-file=/opt/mysql_3341/my.cnf &

修改root临时密码

/opt/mysql_3341/bin/mysqladmin -uroot -p -S /opt/mysql_3341/mysql.sock

恢复备份到从库

MySQL8.0利用XtraBackup 搭建从库详细过程

主库 192.168.192.56 3341

从库 192.168.145.3

2.1主库上创建复制账号

create user 'repl'@'192.168.145.3' identified by 'repl';

grant replication slave on *.* TO 'repl'@'192.168.145.3';

2.2对主库进行备份

/usr/local/xtrabackup8.0.35-30/bin/xtrabackup --defaults-file=/opt//mysql_3341/my.cnf --user=root --password=***** --socket=/opt//mysql_3341/mysql.sock --backup --parallel=10 --slave-info --target-dir=/backup/backup192.56_3341/full

2.3将备份文件传输到从库上

scp -r /backup/backup192.56_3341/full/* root@10.40.145.3:/opt/backup/full

# scp -rp .rocksdb/ root@10.40.145.3:/opt/backup/full

安装备份软件

# scp -rp /usr/local/xtrabackup8.0.35-30/ root@192.168.145.3:/usr/local/

2.5在从库上进行 Prepare 和恢复

/usr/local/xtrabackup8.0.35-30/bin/xtrabackup --prepare --target-dir=/opt/backup/full

关闭数据库

清空数据库目录

# cd /opt//mysql_3341/data

# rm -rf *

/usr/local/xtrabackup8.0.35-30/bin/xtrabackup --defaults-file=/opt//mysql_3341/my.cnf --copy-back --parallel=10 --target-dir=/opt/backup/full

2.6启动实例

chown -R mysql.mysql /opt//mysql_3341/data

2.7建立复制

cat xtrabackup_binlog_info

mybinlog.002453 197 4a3b73a6-bacf-11ee-8537-c81f66f66879:1-618521396

如果 xtrabackup_binlog_info 中存在 GTID 信息，则代表备份实例开启了 GTID，这个时候就需要建立 GTID 复制。

2.7.1GTID 复制
对于 GTID 复制，在建立复制前，必须首先设置 GTID_PURGED。

设置 GTID_PURGED 时，注意备份实例的版本。

在 MySQL 8.0 中，无需设置 GTID_PURGED。

CHANGE MASTER TO

MASTER_HOST='192.168.192.56',

MASTER_USER='repl',

MASTER_PASSWORD='repl',

MASTER_PORT=3341,

GET_MASTER_PUBLIC_KEY = 1,

MASTER_AUTO_POSITION = 1;

对于 GTID 复制，需将 MASTER_AUTO_POSITION 设置为 1。
在 MySQL 8.0 中，CHANGE MASTER TO 语句中还需添加 GET_MASTER_PUBLIC_KEY = 1。

2.7.2基于位置点的复制

如果 xtrabackup_binlog_info 没有 GTID 信息，则代表备份实例没有开启 GTID，这个时候就无需设置 GTID_PURGED，直接执行 CHANGE MASTER TO 命令即可。

CHANGE MASTER TO

MASTER_HOST=‘192.168.192.156’,

MASTER_USER=‘repl’,

MASTER_PASSWORD=‘repl’,

MASTER_PORT=3341,

MASTER_LOG_FILE=‘mysql-bin.000002’,

MASTER_LOG_POS=882880068;

CHANGE MASTER TO 语句中的 MASTER_LOG_FILE 和 MASTER_LOG_POS 的值分别取自 xtrabackup_binlog_info 中的 filename 和 position。

2.8开启复制

mysql>start slave;

2.9检查主从复制是否正常

mysql>show slave status\G

Slave_IO_Running 和Slave_SQL_Running 均为 Yes 代表复制正常。

以上就是使用 XtraBackup 搭建从库的基本步骤。