momoa

MySQL双主复制折腾记录

高考完了闲的无聊，总想折腾点什么
正好手上有台香港服务器，给站点做个镜像
因为Typecho的文件很少变化，操作基本上都是修改数据库
所以站点文件就直接复制到目标服务器上
配置步骤基本上是按照网上的教程来的

/etc/my.cnf.d/server.cnf：

本地服务器目标服务器
[mariadb]
port=3306
bind_address=0.0.0.0
socket = /dev/shm/mysql.sock
datadir = /db
character_set_server=utf8
default_storage_engine=Aria
log_bin   = /db/master-bin
server_id = 1 
expire_logs_days  = 30 
replicate_do_db   = BILIBIBI
binlog_ignore_db  = mysql
binlog_ignore_db  = information_schema
auto_increment_increment = 2 
auto_increment_offset = 1
sync_binlog = 1
sync_master_info    = 1
sync_relay_log      = 1
sync_relay_log_info = 1
loose_skip_innodb
loose_innodb_trx=0 
loose_innodb_locks=0 
loose_innodb_lock_waits=0 
loose_innodb_cmp=0 
loose_innodb_cmp_per_index=0
loose_innodb_cmp_per_index_reset=0
loose_innodb_cmp_reset=0 
loose_innodb_cmpmem=0 
loose_innodb_cmpmem_reset=0 
loose_innodb_buffer_page=0 
loose_innodb_buffer_page_lru=0 
loose_innodb_buffer_pool_stats=0

[mariadb]
port=3306
bind_address=0.0.0.0
socket = /dev/shm/mysql.sock
datadir = /db
character_set_server=utf8
default_storage_engine=Aria
log_bin   = /db/master-bin
server_id = 2 
expire_logs_days  = 30 
replicate_do_db   = BILIBIBI
binlog_ignore_db  = mysql
binlog_ignore_db  = information_schema
auto_increment_increment = 2 
auto_increment_offset = 2
sync_binlog = 1
sync_master_info    = 1
sync_relay_log      = 1
sync_relay_log_info = 1
loose_skip_innodb
loose_innodb_trx=0 
loose_innodb_locks=0 
loose_innodb_lock_waits=0 
loose_innodb_cmp=0 
loose_innodb_cmp_per_index=0
loose_innodb_cmp_per_index_reset=0
loose_innodb_cmp_reset=0 
loose_innodb_cmpmem=0 
loose_innodb_cmpmem_reset=0 
loose_innodb_buffer_page=0 
loose_innodb_buffer_page_lru=0 
loose_innodb_buffer_pool_stats=0
因为用不到InnoDB而且还占内存就禁用了，目前没发现对复制有什么影响

重启两侧数据库

service mysql reatart

然后导出本地数据库

/usr/bin/mysqldump -u root -p嘿嘿 BILIBIBI > /BILIBIBI.sql

在目标服务器上导入

mysql -u root -p嘿嘿
CREATE DATABASE BILIBIBI;
use BILIBIBI;
source /BILIBIBI.sql

然后创建授权用户

本地服务器目标服务器

GRANT REPLICATION SLAVE ON *.* TO 'mysync'@'123.456.789.012' IDENTIFIED BY PASSWORD '嘿嘿';
FLUSH PRIVILEGES;


GRANT REPLICATION SLAVE ON *.* TO 'mysync'@'012.345.678.901' IDENTIFIED BY PASSWORD '嘿嘿';
FLUSH PRIVILEGES;

互告bin-log信息

本地服务器目标服务器

SHOW MASTER STATUS;
CHANGE MASTER TO master_host='123.456.789.012',master_user='mysync',master_password='嘿嘿',master_log_file='master-bin.000001',master_log_pos=192;


SHOW MASTER STATUS;
CHANGE MASTER TO master_host='012.345.678.901',master_user='mysync',master_password='嘿嘿',master_log_file='master-bin.000001',master_log_pos=208;

启动SLAVE

START SLAVE;

---------17个小时之后----------
感觉开了同步后站点速度有些下降，有时候两端的数据还不一致
于是又折腾回去了……

本地服务器目标服务器
STOP SLAVE;
RESET SLAVE;
service mysql restart

yum remove MariaDB-client MariaDB-common MariaDB-compat MariaDB-server

标签: none

添加新评论