您的位置:首页 > 教育 > 锐评 > 小程序免费制作平台教学_宁波网站建设怎么建设_网络广告怎么做_seo的中文含义是

小程序免费制作平台教学_宁波网站建设怎么建设_网络广告怎么做_seo的中文含义是

2024/12/8 21:14:23 来源:https://blog.csdn.net/weixin_46325250/article/details/143676290  浏览:    关键词:小程序免费制作平台教学_宁波网站建设怎么建设_网络广告怎么做_seo的中文含义是
小程序免费制作平台教学_宁波网站建设怎么建设_网络广告怎么做_seo的中文含义是

前置条件

假设主从信息

mysqlhostport
192.168.1.13306
192.168.1.23306
vip192.168.1.3

部署流程

导出测试环境表结构与数据

使用mysqldump

./mysqldump -ulzzc -p -S /tmp/mysql3306.sock --single-transaction --database lzzc > databaseLZZCxxxx.sql

查看gtid号

head -n 40 databaseLZZCxxxx.sql

安装mysql

下载mysql-server-8.4.0.tar.xz
MySQL :: Download MySQL Community Server (Archived Versions)

将安装包放置在/usr/local/ 目录下解压,并将解压后目录改名为mysql

添加my.cnf文件

cd /usr/local/mysql/
mkdir etc
cd etc
vi my.cnf

从库

[mysqld]
default-time-zone='+08:00'
bind-address=0.0.0.0
port=3306
user=mysql
socket=/tmp/mysql3306.sock
pid_file=/mysql/data/my.pid
basedir=/usr/local/mysql
datadir=/mysql/data
#character config
character_set_server=utf8mb4
explicit_defaults_for_timestamp=true
symbolic-links=0
log-error=/mysql/logs/mysql3306.err
log_bin = /mysql/binlog/mysql-bin
relay_log = /mysql/relaylog/relay-bin
slow_query_log_file = /mysql/logs/slow.log
slow_query_log=on
server-id=6 # 两个库的server-id需要不同
binlog_format=row
#innodb settings
innodb_buffer_pool_size=4G # cache_buffer大小
#skip-grant-tables
gtid-mode=on
enforce-gtid-consistency=true
log-replica-updates=ON
lower_case_table_names=1
interactive_timeout=28800000
wait_timeout=28800000
max_connections=1000
innodb_log_group_home_dir=/mysql/data
binlog_expire_logs_seconds=2592000 # 30天时间
[mysql]
socket = /tmp/mysql3306.sock

在根目录下创建mysql目录存放mysql数据与日志等,在/mysql中创建data,binlog ,logs,relaylog目录

系统创建mysql 用户组和用户

groupadd mysql
useradd -r -g mysql mysql

/usr/local/mysql/mysql目录赋权

chown -R mysql:mysql /usr/local/mysql
chown -R mysql:mysql /mysql

初始化mysql应用

cd /usr/local/mysql/bin
./mysqld --defaults-file=/usr/local/mysql/etc/my.cnf --initialize-insecure

等待初始化,查看/mysql/logs/mysql3306.err如果没有错误报错则继续执行

启动mysql

./mysqld_safe --defaults-file=/usr/local/mysql/etc/my.cnf &

查看mysql进程

ps -ef|grep mysql

mysql创建用户赋权

主/从库

登录root

./mysql -uroot -p -S /tmp/mysql3306.sock

进入mysql命令行,修改root密码

alter user 'root'@'localhost' identified by 'xxxxxx';

创建同步用户并赋权

create user 'repl'@'%' identified by 'replpassword';
grant replication slave on *.* to "repl"@"%";
flush privileges;

从库重置gtid、导入主库数据

重置gtid

show variables like "%gtid%";

输出类似如下图(正常gtid中会在gtid_executed中显示为本机的gtid,gtid_purged为导入的主库gtid)
![[Pasted image 20241111105605.png]]

reset binary logs and gtids; # 重置gtid

导入主库数据

./mysql -uroot -p -S /tmp/mysql3306.sock <databaseLZZCxxxx.sql

再次查看gtid,此时gtid_purged应该与主库的gtid一致

开启从库同步进程

设置同步指向

change replication source to source_host="192.168.1.1",source_port=3306,source_user="repl",source_password="replpassword",source_auto_position=1,get_source_public_key=1;

开启replica进程

start replica;

查看replica状态,输出类似:(主要查看Replica_IO_Running和Replica_SQL_Running以及Replica_SQL_Running_State三个值,前两个都是yes后面一个没有警告和报错就可以)

show replica status\G;
*************************** 1. row ***************************Replica_IO_State: Waiting for source to send eventSource_Host: 192.168.1.1Source_User: replSource_Port: 3306Connect_Retry: 60Source_Log_File: mysql-bin.000006Read_Source_Log_Pos: 237Relay_Log_File: relay-bin.000021Relay_Log_Pos: 321Relay_Source_Log_File: mysql-bin.000006Replica_IO_Running: YesReplica_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Source_Log_Pos: 237Relay_Log_Space: 782Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Source_SSL_Allowed: NoSource_SSL_CA_File: Source_SSL_CA_Path: Source_SSL_Cert: Source_SSL_Cipher: Source_SSL_Key: Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Source_Server_Id: 7Source_UUID: 292e4a07-9e71-11ef-88a8-001a4a1601f1Source_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLReplica_SQL_Running_State: Replica has read all relay log; waiting for more updatesSource_Retry_Count: 10Source_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Source_SSL_Crl: Source_SSL_Crlpath: Retrieved_Gtid_Set: 292e4a07-9e71-11ef-88a8-001a4a1601f1:1-3Executed_Gtid_Set: 292e4a07-9e71-11ef-88a8-001a4a1601f1:1-3,
92c1608c-9cdb-11ef-a01e-001a4a1601fe:1-343,
eb66bcc8-9e70-11ef-858d-001a4a1601fe:1-4Auto_Position: 1Replicate_Rewrite_DB: Channel_Name: Source_TLS_Version: Source_public_key_path: Get_Source_public_key: 1Network_Namespace: 
1 row in set (0.00 sec)ERROR: 
No query specified

开启主库同步进程

设置同步指向

change replication source to source_host="192.168.1.2",source_port=3306,source_user="repl",source_password="replpassword",source_auto_position=1,get_source_public_key=1;

开启replica进程

start replica;

查看replica状态,输出类似:(主要查看Replica_IO_Running和Replica_SQL_Running以及Replica_SQL_Running_State三个值,前两个都是yes后面一个没有警告和报错就可以)

show replica status\G;
*************************** 1. row ***************************Replica_IO_State: Waiting for source to send eventSource_Host: 192.168.1.1Source_User: replSource_Port: 3306Connect_Retry: 60Source_Log_File: mysql-bin.000006Read_Source_Log_Pos: 237Relay_Log_File: relay-bin.000021Relay_Log_Pos: 321Relay_Source_Log_File: mysql-bin.000006Replica_IO_Running: YesReplica_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Source_Log_Pos: 237Relay_Log_Space: 782Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Source_SSL_Allowed: NoSource_SSL_CA_File: Source_SSL_CA_Path: Source_SSL_Cert: Source_SSL_Cipher: Source_SSL_Key: Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Source_Server_Id: 7Source_UUID: 292e4a07-9e71-11ef-88a8-001a4a1601f1Source_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLReplica_SQL_Running_State: Replica has read all relay log; waiting for more updatesSource_Retry_Count: 10Source_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Source_SSL_Crl: Source_SSL_Crlpath: Retrieved_Gtid_Set: 292e4a07-9e71-11ef-88a8-001a4a1601f1:1-3Executed_Gtid_Set: 292e4a07-9e71-11ef-88a8-001a4a1601f1:1-3,
92c1608c-9cdb-11ef-a01e-001a4a1601fe:1-343,
eb66bcc8-9e70-11ef-858d-001a4a1601fe:1-4Auto_Position: 1Replicate_Rewrite_DB: Channel_Name: Source_TLS_Version: Source_public_key_path: Get_Source_public_key: 1Network_Namespace: 
1 row in set (0.00 sec)ERROR: 
No query specified

设置keepalived

主备服务器安装keepalived,可以使用rpm包安装或者源码编译

主库编辑keepalived配置文件keepalived.conf

# 检查mysql服务是否存活的脚本
vrrp_script chk_mysql {script "/usr/bin/killall -0 mysqld"
}
# vrrp配置虚IP
vrrp_instance VI_1 {# 状态:MASTER  另外一台机器为BACKUPstate MASTER# 绑定的网卡interface eth0# 虚拟路由id  两台机器需保持一致virtual_router_id 51# 优先级 MASTER的值要大于BACKUPpriority 100advert_int 1authentication {auth_type PASSauth_pass 1111}# 虚拟IP地址 两台keepalived需要一致virtual_ipaddress {192.168.1.3}# 检查脚本 vrrp_script的名字track_script {chk_mysql}
}

从库编辑keepalived配置文件keepalived.conf

# 检查mysql服务是否存活的脚本
vrrp_script chk_mysql {script "/usr/bin/killall -0 mysqld"
}
# vrrp配置虚IP
vrrp_instance VI_1 {# 状态:MASTER  另外一台机器为BACKUPstate BACKUP# 绑定的网卡interface eth0# 虚拟路由id  两台机器需保持一致virtual_router_id 51# 优先级 MASTER的值要大于BACKUPpriority 101advert_int 1authentication {auth_type PASSauth_pass 1111}# 虚拟IP地址 两台keepalived需要一致virtual_ipaddress {192.168.1.3}# 检查脚本 vrrp_script的名字track_script {chk_mysql}
}

主备库开启keepalived

systemctl start keepalived

剩余工作

自行测试主主同步、热切换

版权声明:

本网仅为发布的内容提供存储空间,不对发表、转载的内容提供任何形式的保证。凡本网注明“来源:XXX网络”的作品,均转载自其它媒体,著作权归作者所有,商业转载请联系作者获得授权,非商业转载请注明出处。

我们尊重并感谢每一位作者,均已注明文章来源和作者。如因作品内容、版权或其它问题,请及时与我们联系,联系邮箱:809451989@qq.com,投稿邮箱:809451989@qq.com