MySQL双主复制折腾记录

本文发布于 8 年前，内容可能和实际情况存在出入。如果文章存在错误欢迎指正，我会根据情况对文章进行修改或做隐藏处理

高考完了闲的无聊，总想折腾点什么
正好手上有台香港服务器，给站点做个镜像
因为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

本地服务器

目标服务器

[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;`


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;`


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