主机IP ：192.168.3.91
主机IP ：192.168.3.92
前提是两台机器都装好了mysql ，mysql安装手册参考linux下安装MySQL
Mysql复制原理
master服务器将数据的改变都记录到二进制binlog日志中，只要master上的数据发生改变，则将其改变写入二进制日志；salve服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/O Thread请求master二进制事件，同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/O Thread和SQL Thread将进入睡眠状态，等待下一次被唤醒。
1、在192.168.3.91 上，修改/etc/my.cnf 配置文件
[mysqld]
#skip-grant-tables
datadir=/usr/local/mysql/data/
socket=/var/lib/mysql/mysql.sock
#主主配置 start
# server_id 服务器唯一ID，默认是1，一般取IP最后一段
server_id = 91
#启用二进制日志
log_bin=mysql-bin
#需要备份的数据库名 多个库以逗号分隔
binlog-do-db = direct
#若涉及及同步函数或者存储过程需要配置，否则主备会产生异常不能同步
log_bin_trust_function_creators=TRUE
#忽略表名大小写
lower_case_table_names=1
#设置数据库编码
character_set_server=utf8
#mysql 接收的最大数据包
max_allowed_packet = 20M
#datadir=/usr/local/mysql/data/
#socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
2、同理在192.168.3.92 上，修改/etc/my.cnf 配置文件
[mysqld]
#skip-grant-tables
lower_case_table_names=1
character_set_server=utf8
max_allowed_packet = 20M
datadir=/usr/local/mysql/data/
socket=/var/lib/mysql/mysql.sock
#主主配置
server_id = 92
log_bin=mysql-bin
binlog-do-db = direct
log_bin_trust_function_creators=TRUE
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
注：主机1和主机2都只有server-id不同和 auto-increment- offset不同 auto-increment-offset是用来设定数据库中自动增长的起点的，回为这两能服务器都设定了一次自动增长值2，所以它们的起点必须得不同，这样才能避免两台服务器数据同步时出现主键冲突replicate-do-db 指定同步的数据库，我们只在两台服务器间同步wang数据库
3、分别重启数据，以便配置生效
service mysqld restart
4、相互授权
在主机1（192.168.3.91）上
GRANT REPLICATION SLAVE ON *.* TO 'test'@'192.168.3.92' IDENTIFIED BY 'test'
flush privileges;
在主机2（192.168.3.92）上
GRANT REPLICATION SLAVE ON *.* TO 'test'@'192.168.3.91' IDENTIFIED BY 'test'
flush privileges;
5、互告bin-log信息
a、在主机1（192.168.3.91）上
mysql> show master status;
+------------------+----------+--------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql-bin.000006 | 106 | | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
b、在主机2（192.168.3.92）上
mysql> show master status;
+------------------+----------+--------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql-bin.000008 | 192 | | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
c、在主机1（192.168.3.91）上
mysql> change master to master_host='192.168.3.92',master_user='wang',master_password='wclwcw',master_log_file='mysql-bin.000008',master_log_pos=194;
d、在主机2（192.168.3.92）上
mysql> change master to master_host='192.168.3.91',master_user='wang',master_password='wclwcw',master_log_file='mysql-bin.000007',master_log_pos=1082;
6、在91，92上分别执行
start slave;
7、show slave status\G 查看状态
当看到了两个yes，即：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
网友评论