欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Windows10与阿里云Centos7.2的Mysql主从复制详解

程序员文章站 2022-05-28 20:11:05
环境: 主:阿里云Centos7.2 mysql5.6.30 rpm安装 从:win10 phpstady里面的mysql版本 主机my.cnf 注意一下节点: [ro...

环境:

主:阿里云Centos7.2 mysql5.6.30 rpm安装

从:win10 phpstady里面的mysql版本

主机my.cnf 注意一下节点:

[root@SHUN ~]# vim /etc/my.cnf
binlog_do-db=db_shun_sm
# For advice on how to change settings please see
# https://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# start bindata log shun
log-bin=mysql-bin
server-id=1
expire_logs_days=10
max_binlog_size=100M
#binlog_do-db=db_shun_sm
#binlog_ignore_db=mysql

#user =mysql
#skip-external-locking
#bind-address=127.0.0.1   
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

从机my.ini:

#  power by phpStudy  2014  www.phpStudy.net  官网下载最新版
[client]
port=3306
[mysql]
default-character-set=utf8
[mysqld]
port=3306
basedir="D:/phpStudy2/MySQL/"
datadir="D:/phpStudy2/MySQL/data/"
character-set-server=utf8
default-storage-engine=MyISAM
#支持 INNODB 引擎模式。修改为 default-storage-engine=INNODB 即可。
#如果 INNODB 模式如果不能启动,删除data目录下ib开头的日志文件重新启动。

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=512

query_cache_size=0
table_cache=256
tmp_table_size=18M

thread_cache_size=8
myisam_max_sort_file_size=64G
myisam_sort_buffer_size=35M
key_buffer_size=25M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K

innodb_additional_mem_pool_size=2M

innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M

innodb_buffer_pool_size=47M
innodb_log_file_size=24M
innodb_thread_concurrency=8
server-id=2

主机命令:(MySQL安装完成的情况下并配置完my.cnf)

 systemctl start mysqld;

 systemctl status mysqld;

 systemctl restart mysqld;

mysql -u root -p
未指定IP的授权访问:GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION; 

GRANT REPLICATION SLAVE ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword';

show master status;

直接说遇到的问题吧:

1、阿里云需要开启入站的实力安全组规则,否则无法访问云端数据库

2、要关闭防火墙,否则无法访问

4、赋予权限要准确,其次指定数据库要清晰(活着默认)

虽然目前还出处于Last_IO_Error: error connecting to master 'shun@60.205.228.47:3306' - retry-time: 60 retries: 8640状态,但是离成功感觉已经一步之遥了,急需解决这个问题,加油,写完博客休息去了先,有哪位能够解决的可以分享一下