mysql主从部署

柚子♞

已于 2023-03-15 10:47:40 修改

阅读量639

点赞数

分类专栏：应用部署文章标签： mysql 数据库服务器

于 2021-08-13 17:33:34 首次发布

本文链接：https://blog.csdn.net/weixin_43574352/article/details/119680338

版权

应用部署专栏收录该内容

17 篇文章 1 订阅

订阅专栏

一、安装部署
安装部署MySQL数据库，一般我们统一部署安装目录，执行文件统一安装在：/usr/local/mysql目录，而数据库文件初始化，则根据需要部署到：比如/data文件系统上。
1.1 检查并卸载系统自带MySQL安装
MySQL数据库安装包，一般在Redhat Linux上都有自带发布版本的包;若是OS安装时选中安装了，则需要先卸载清理，避免混淆我们的MySQL服务器版本安装。
#检查安装情况

#检查安装情况
rpm -qa |grep mysql
rpm –qa|grep MySQL
rpm –qa |grep mariadb
#若有，则需要一一卸载检查出来的安装包
rpm –e  --nodeps mysql-xxxx
rpm –e  --nodeps MySQL-yyyy
rpm -e  --nodeps mariadb

1.2 部署安装MySQL(以64位 REDHAT7 或CENTOS7 为例)

#解压包
mkdir  -p  /data/mysql
将mysql-5.7.20-1.el7.x86_64.rpm-bundle.tar放入mysql_software目录，解压：
tar  xvf  mysql-5.7.20-1.el7.x86_64.rpm-bundle.tar
#安装
cd  /data/myqsl
rpm -ivh  mysql-community-common-5.7.20-1.el7.x86_64.rpm
warning: mysql-community-common-5.7.20-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
   1:mysql-community-common ########################################### [100%]
rpm -ivh  mysql-community-libs-5.7.20-1.el7.x86_64.rpm
warning: mysql-community-libs-5.7.20-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
   1:mysql-community-libs   ########################################### [100%]
rpm -ivh mysql-community-libs-compat-5.7.20-1.el7.x86_64.rpm
warning: mysql-community-libs-compat-5.7.20-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
   1:mysql-community-libs-co########################################### [100%]
rpm -ivh mysql-community-devel-5.7.20-1.el7.x86_64.rpm
warning: mysql-community-devel-5.7.20-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
   1:mysql-community-devel  ########################################### [100%]
rpm -ivh mysql-community-client-5.7.20-1.el7.x86_64.rpm
warning: mysql-community-client-5.7.20-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
   1:mysql-community-client ########################################### [100%]
rpm -ivh  mysql-community-server-5.7.20-1.el7.x86_64.rpm
warning: mysql-community-server-5.7.20-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
   1:mysql-community-server ########################################### [100%]

1.3 初始化MySQL数据库
初始化MySQL数据库，主要是用于生成数据库元数据库。

#初始化目录（不带ROOT密码初始化，数据目录以/data/mysql为例）
/usr/sbin/mysqld --initialize-insecure --basedir=/data --datadir=/data/mysql
#生成SSL密钥文件
cd /data/mysql
mysql_ssl_rsa_setup --datadir=/data/mysql
chmod 644 private_key.pem
chmod 644 server-key.pem
#设置文件目录属主权限
chown -R  mysql:mysql  /data/mysql

1.4 设置数据库启动参数文件

然后修改mysql的配置vi /etc/my.cnf
内容：
[client]
socket          = /data/mysql/mysql.sock
[mysqld]
# All members in the group requires a unique server_id greater than zero
server_id=1012519（改成本机的ip去掉小数点）
# you may specify a name for the binary log, or leave it blank to use the default name
# however, binary logging is required
log_bin=mysql-bin  
log-bin-index = mysql-bin.index
binlog_transaction_dependency_tracking=WRITESET
# these settings are required
log_slave_updates=ON
binlog_format=row
enforce_gtid_consistency=ON
gtid_mode=ON
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=8
slave_preserve_commit_order=1
datadir=/data/mysql
max_connections=5000
init-connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
port            = 3306
socket          = /data/mysql/mysql.sock
skip-external-locking
explicit_defaults_for_timestamp=true
large_pages=1
ssl_ca  =/data/mysql/ca.pem
ssl_cert=/data/mysql/server-cert.pem
ssl_key =/data/mysql/server-key.pem
transaction_isolation = READ-COMMITTED
max_allowed_packet = 1073741824
table_open_cache = 5120
sort_buffer_size = 30M
read_buffer_size = 20M
read_rnd_buffer_size = 20M
join_buffer_size = 20M
innodb_read_io_threads=8
innodb_write_io_threads=8
skip_name_resolve
innodb_use_native_aio = 1
innodb_buffer_pool_size = 32G
innodb_file_per_table = 1
event_scheduler = 1
lower_case_table_names=1
slow_query_log=on
slow_query_log_file=slowquery.log
long_query_time=10
innodb_log_file_size = 512M
innodb_log_buffer_size = 512M
innodb_deadlock_detect = off
innodb_lock_wait_timeout = 1
innodb_rollback_on_timeout = on
log_bin_trust_function_creators = 1
local-infile=ON
cte_max_recursion_depth=4294967295
group_concat_max_len = 4294967295
max_prepared_stmt_count=100000
log-error=/data/mysql/mysqld.log
pid-file=/data/mysql/mysqld.pid
(以下就配置的内容 附带配置说明)
vi /etc/my.cnf
[mysqld]
server-id=87   #数字，服务器唯一标识，一般设置为主机IP最后1节即可
datadir=/data/mysql
max_connections=1000  #支持的最大连接数，可调整
init-connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
port            = 3306
socket          = /data/mysql/mysql.sock
skip-external-locking
explicit_defaults_for_timestamp=true
disable-partition-engine-check
ssl_ca =/data/mysql/ca.pem
ssl_cert=/data/mysql/server-cert.pem
ssl_key =/data/mysql/server-key.pem
symbolic-links=0
gtid_mode = ON
enforce_gtid_consistency = 1
transaction_isolation = READ-COMMITTED
max_allowed_packet = 1073741824
table_open_cache = 1024
sort_buffer_size = 20M
read_buffer_size = 20M
read_rnd_buffer_size = 20M
join_buffer_size = 20M
innodb_thread_concurrency = 64  #需要跟主机CPU核数一致。cat /proc/cpuinfo 此处以4核为例
innodb_sort_buffer_size = 33554432
innodb_read_io_threads=64  #需要跟主机CPU核数一致。cat /proc/cpuinfo 此处以4核为例
innodb_write_io_threads=64  #需要跟主机CPU核数一致。cat /proc/cpuinfo 此处以4核为例
skip_name_resolve
innodb_buffer_pool_size = 45G  #一般设置为主机物理内存的70%左右。cat /proc/meminfo 第1行显示物理内存总大小。此处以64G物理内存*0.7为例
innodb_file_per_table = 1
event_scheduler = 1
innodb_data_home_dir = /data/mysql
lower_case_table_names=1
slow_query_log=on
slow_query_log_file=slowquery.log
long_query_time=10  #慢SQL查询超时，此处设置为SQL执行超过10秒就记录，可调整
log_queries_not_using_indexes=off
log-bin=mysql-bin
log-bin-index = mysql-bin.index
binlog_format=row
sync-binlog=1
innodb_log_file_size = 1024M
innodb_log_buffer_size = 500M
log_bin_trust_function_creators = 1
log-error=/data/mysql/mysqld.log
pid-file=/data/mysql/mysqld.pid

1.5 启动数据库

若是CENTOS  7 则使用
systemctl  start  mysqld.service  启动MYSQL服务。
若是其它则使用：
service mysqld start启动MYSQL服务。
cd /var/lib/mysql
ln –s  /data/mysql/mysql.sock  mysql.sock
如果有密码的cat /data/mysql/mysqld.log查看登入密码

1.6 ROOT用户密码设置

mysql  –u root 
alter user 'root'@'localhost' identified by '1mqay3zXSW!';
grant all privileges on *.* to 'root'@'%' identified by '1mqay3zXSW!' with grant option;
flush privileges;
exit;
输入上述密码验证 能否登录MYSQL：
mysql  -u root  -p
myqsl用户密码修改：
alter user 'root'@'localhost' identified with mysql_native_password by '1mqay3zXSW!';
create user root@'%' identified with mysql_native_password by '1mqay3zXSW!';
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;

二、MYSQL主备库搭建
注意：应用连接配置应该使用主库。主库有故障才切换到备库！
2.1备库的MYSQL安装
参见上面的二、环境准备+三、安装部署。区别在于下面的配置文件

vi /etc/my.cnf
[mysqld]
server-id=88   #数字，MYSQL备机服务器唯一标识，一般设置为主机IP最后1节即可
datadir=/data/mysql
max_connections=1000  #支持的最大连接数，可调整
init-connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
port            = 3306
socket          = /data/mysql/mysql.sock
skip-external-locking
explicit_defaults_for_timestamp=true
disable-partition-engine-check
ssl_ca =/data/mysql/ca.pem
ssl_cert=/data/mysql/server-cert.pem
ssl_key =/data/mysql/server-key.pem
symbolic-links=0
gtid_mode = ON
enforce_gtid_consistency = 1
transaction_isolation = READ-COMMITTED
max_allowed_packet = 1073741824
table_open_cache = 1024
sort_buffer_size = 20M
read_buffer_size = 20M
read_rnd_buffer_size = 20M
join_buffer_size = 20M
innodb_thread_concurrency = 64  #需要跟主机CPU核数一致。cat /proc/cpuinfo 此处以4核为例
innodb_sort_buffer_size = 33554432
innodb_read_io_threads=64  #需要跟主机CPU核数一致。cat /proc/cpuinfo 此处以4核为例
innodb_write_io_threads=64  #需要跟主机CPU核数一致。cat /proc/cpuinfo 此处以4核为例
skip_name_resolve
innodb_buffer_pool_size = 45G  #一般设置为主机物理内存的70%左右。cat /proc/meminfo 第1行显示物理内存总大小。此处以64G物理内存*0.7为例
innodb_file_per_table = 1
event_scheduler = 1
innodb_data_home_dir = /data/mysql
lower_case_table_names=1
slow_query_log=on
slow_query_log_file=slowquery.log
long_query_time=10  #慢SQL查询超时，此处设置为SQL执行超过10秒就记录，可调整
log_queries_not_using_indexes=off
log-bin=mysql-bin
log-bin-index = mysql-bin.index
binlog_format=row
sync-binlog=1
innodb_log_file_size = 1024M
innodb_log_buffer_size = 500M
log_bin_trust_function_creators = 1
log-error=/data/mysql/mysqld.log
pid-file=/data/mysql/mysqld.pid
#slave
innodb_flush_log_at_trx_commit=2
slave_skip_errors=all
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=64  #需要跟主机CPU核数一致。cat /proc/cpuinfo 此处以4核为例
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

2.2 在主库10.187.5.6创建数据库：

mysql –u root –p
create user repl@'10.187.5.7' identified with mysql_native_password by '>HLuGaeFk4za';
grant replication slave on *.* to 'repl'@'10.187.5.7';注：此处ip改成从库ip。

2.3 在从库10.187.5.7验证是否数据库已经自动创建

mysql –u root –p
CHANGE MASTER TO MASTER_HOST = '10.187.5.6', MASTER_PORT = 3306, MASTER_USER = 'repl', MASTER_PASSWORD = '>HLuGaeFk4za', MASTER_AUTO_POSITION = 1,GET_MASTER_PUBLIC_KEY=1; 注：此处ip改成主库ip。
然后执行：start slave;和show slave status\G;

注意：
修改my.cnf数据库默认data路径之后，需要初始化数据库
mysqld --initialize --console

问题：
在这里插入图片描述 select * from performance_schema.replication_applier_status_by_worker\G;

在这里插入图片描述
select user,host from mysql.user;
主从服务器都执行

从数据库执行
stop slave;

set gtid_next =“478e3175-eb91-11eb-aa1d-005056adf3a2:2”;

begin;commit;

set gtid_next =“AUTOMATIC”;

start slave;

柚子♞

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql主从部署

一、安装部署安装部署MySQL数据库，一般我们统一部署安装目录，执行文件统一安装在：/usr/local/mysql目录，而数据库文件初始化，则根据需要部署到：比如/data文件系统上。1.1 检查并卸载系统自带MySQL安装MySQL数据库安装包，一般在Redhat Linux上都有自带发布版本的包;若是OS安装时选中安装了，则需要先卸载清理，避免混淆我们的MySQL服务器版本安装。#检查安装情况#检查安装情况rpm -qa |grep mysqlrpm –qa|grep MySQLrpm
复制链接

扫一扫

专栏目录