本文发布于 5 年前 ，内容可能和实际情况存在出入。如果文章存在错误欢迎指正，我会根据情况对文章进行修改或做隐藏处理
高考完了闲的无聊，总想折腾点什么
/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
