Mysql 5.6.23
CENTOS 6.4 X86
目标: 两个MYSQL主机进行数据同步
NODE1:192.168.59.230
NODE2:192.168.59.231
配置先简单
NODE1配置:
Step1. Change /etc/my.cnf
[mysqld]
log-error=/var/log/mysql/mysql.log
auto_increment_increment=2
auto_increment_offset=1
server-id=1
log-bin=mysql-bin
binlog_format=ROW
Step2. Login in Mysql
mysql -uroot -padmin
mysql> grant replication slave on *.* to 'replication'@'%' identified by 'slave';
mysql> change master to master_host='192.168.59.231',
-> master_user='replication',
-> master_password='slave',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=0;
Query OK, 0 rows affected, 2 warnings (0.11 sec)
mysql> start slave;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 351 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.23-log, Binlog ver: 4 |
| mysql-bin.000001 | 120 | Stop | 1 | 143 | |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
2 rows in set (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.59.231
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 815
Relay_Log_File: localhost-relay-bin.000005
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 815
Relay_Log_Space: 623
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: a491626c-d107-11e4-9b3d-000c29305dd3
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
bin log files default path: /var/lib/mysql/
NODE2配置:
Step1. Change /etc/my.cnf
[mysqld]
log-error=/var/log/mysql/mysql.log
auto_increment_increment=2
auto_increment_offset=2
server-id=2
log-bin=mysql-bin
binlog_format=ROW
Step2. Login in Mysql
mysql -uroot -padmin
mysql> grant replication slave on *.* to 'replication'@'%' identified by 'slave';
mysql> change master to master_host='192.168.59.230',
-> master_user='replication',
-> master_password='slave',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=0;
Query OK, 0 rows affected, 2 warnings (0.11 sec)
mysql> start slave;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 351 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.23-log, Binlog ver: 4 |
| mysql-bin.000001 | 120 | Stop | 1 | 143 | |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
2 rows in set (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.59.230
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 1039
Relay_Log_File: localhost-relay-bin.000007
Relay_Log_Pos: 1202
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1039
Relay_Log_Space: 1542
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 871a79c3-d106-11e4-9b36-000c2986dad9
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
bin log files default path: /var/lib/mysql/
最后两边加一下数据测一下就行了
相关推荐
mysql【1】主从同步之人机交互自动化配置脚本-CSDN博客 ...mysql【3】keepalived+mysql双主同步，实现故障转移群集 https://blog.csdn.net/u014332402/article/details/136137707 这3篇文章所使用的所有自动化配置脚本
Keepalived+MySQL双主同步复制
MySQL主主双向同步测试报告.pdf
mysql5.7双向实时同步，亲测，对于需要实现mysql同步的用户非常有用
mysql,两个数据库，实现实时数据同步，对于需要实现mysql同步的用户非常有用
拥有它，烦人的数据库同步将迎刃而解 mysql双向主主同步关键性文档
MySql 主从 双主 主主 同步 文档 ，包含每一步操作
Mysql5.5数据库双向同步
MySQL数据库双活同步复制方案详解.pdfMySQL数据库双活同步复制方案详解.pdfMySQL数据库双活同步复制方案详解.pdfMySQL数据库双活同步复制方案详解.pdfMySQL数据库双活同步复制方案详解.pdfMySQL数据库双活同步复制...
MySQL数据库双活同步复制方案详解.docxMySQL数据库双活同步复制方案详解.docxMySQL数据库双活同步复制方案详解.docxMySQL数据库双活同步复制方案详解.docxMySQL数据库双活同步复制方案详解.docxMySQL数据库双活同步...
mysql双机热备同步，详细配置 成功案例 我的经验 已经正常运作半年 目前数据量600万
主从同步配置操作： 说是主从实际上从机也可以当做主机; 主机mysql做的操作能够影响从机，从机mysql做的操作也能影响主机。内部还有一些常见错误，哪怕是新手都么么哒，我讲的很详细的，详细的我都害怕
两个mysql数据库之间实现同步,通过定时任务实现从主库到从库的同步,java代码实现,所有java项目包括配置都有,数据结构可以跟据自己的特定情况去改
windows下mysql双向同步 现在给出A 的双向同步的完整配置 [mysqld] server-id=1 log-bin=C:\log-bin.log binlog-do-db=dn2 master-host=192.168.1.181 master-user=backup master-password=1234 master-port=3306 ...
介绍了MYSQL主从不同步延迟原理分析及解决方案，有需要的朋友可以参考一下
Mysql双向同步中的两台服务器不能同时对外服务，双向同步只是为了方便当主服务器从故障中恢复的时候能自动从备份服务器更新数据。
Mysql异地多活数据双向同步-CloudCanal实战.doc
1.1 软件版本 1.2 目录结构 2.2 slave上配置同步
CentOs7下mysql5.7的下载安装以及主从同步、双主多从配置详情
springboot双数据库定时同步,可以是mysql，SqlServer