共计 9833 个字符，预计需要花费 25 分钟才能阅读完成。
这篇文章主要讲解了“Linux 下的 MYSQL 主主复制的用法”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“Linux 下的 MYSQL 主主复制的用法”吧！
为什么，会有 mysql 的主主复制。因为在一些高可用的环境中，mysql 的主从不能满足现实中的一些实际需求。比如，一些流量大的网站数据库访问有了瓶颈，需要负载均衡的时候就用两个或者多个的 mysql 服务器，而这些 mysql 服务器的数据库数据必须要保持一致，那么就会用到主主复制。
mysql 主从架构中其实就一个主在工作，而从就相当于一个备份机器，从通过日志监测的方式来备份主库上的数据而保证主库的数据安全。在这种架构中如果从上的数据做了改变，主数据是不会用任何变化的。因为 mysql 主从架构主要是 mysql 从监控 mysql 主的日志变化来实现同步，相反的在这个架构中主并没有监控从的日志变化。所以，mysql 从数据反生变化，主也就没有什么变化了。
通过上述描述，可以看到如果想实现主主复制，无非就是在 mysql 主从架构上让 mysql 主实现监测从的日志变化，从而实现两台机器相互同步。(主从的架构前面有博文 http://duyunlong.blog.51cto.com/1054716/1102237)
实验环境：两台服务器：
主机名：HA1,HA2(呵呵, 这个主机名是英文缩写 High availability，高可用的意思)
ip:192.168.1.231
192.168.1.232
主机系统：centos6.4
mysql 版本 5.5.22
首先，看下 HA1（192.168.1.231）的 mysql 配置文件
vim /etc/my.cnf# Example MySQL config file for very large systems.## This is for a large system with memory of 1G-2G where the system runs mainly# MySQL.## MySQL programs look for option files in a set of# locations which depend on the deployment platform.# You can copy this option file to one of those# locations. For information about these locations, see:# http://dev.mysql.com/doc/mysql/en/option-files.html## In this file, you can use all long options that a program supports.# If you want to know which options a program supports, run the program# with the --help option.# The following options will be passed to all MySQL clients[client]#password = your_passwordport = 3306socket = /usr/local/mysql/tmp/mysql.sock# Here follows entries for some specific programs# The MySQL server[mysqld]port = 3306socket = /usr/local/mysql/tmp/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8query_cache_size = 32M# Try number of CPU s*2 for thread_concurrencythread_concurrency = 8# Don t listen on a TCP/IP port at all. This can be a security enhancement,# if all processes that need to connect to mysqld run on the same host.# All interaction with mysqld must be made via Unix sockets or named pipes.# Note that using this option without enabling named pipes on Windows# (via the enable-named-pipe option) will render mysqld useless!##skip-networking# Replication Master Server (default)# binary logging is required for replicationlog-bin=mysql-binlog-slave-updates# required unique id between 1 and 2^32 - 1# defaults to 1 if master-host is not set# but will not function as a master if omittedserver-id = 1在这个配置文件中，需要特别注意的三处地方：
log-bin=mysql-bin：这个选项基本默认都是开着的，如果没有打开，可以手动打开。
log-slave-updates：这个选项特别的重要它是为了让 slave 也能充当 master，同时也为了更好的服务于 m-m + s 的环境，保证 slave 挂在任何一台 master 上都会接收到另一个 master 的写入信息。当然不局限于这个架构，级联复制的架构同样也需要 log-slave-updates 的支持。
server-id = 1：这个 ID 为服务器 ID 如果配置一样会出现冲突，而不能复制
接着再看下 HA2（192.168.1.232）的 mysql 配置文件
vim /etc/my.cnf# Example MySQL config file for very large systems.## This is for a large system with memory of 1G-2G where the system runs mainly# MySQL.## MySQL programs look for option files in a set of# locations which depend on the deployment platform.# You can copy this option file to one of those# locations. For information about these locations, see:# http://dev.mysql.com/doc/mysql/en/option-files.html## In this file, you can use all long options that a program supports.# If you want to know which options a program supports, run the program# with the --help option.# The following options will be passed to all MySQL clients[client]#password = your_passwordport = 3306socket = /usr/local/mysql/tmp/mysql.sock# Here follows entries for some specific programs# The MySQL server[mysqld]port = 3306socket = /usr/local/mysql/tmp/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8query_cache_size = 32M# Try number of CPU s*2 for thread_concurrencythread_concurrency = 8# Don t listen on a TCP/IP port at all. This can be a security enhancement,# if all processes that need to connect to mysqld run on the same host.# All interaction with mysqld must be made via Unix sockets or named pipes.# Note that using this option without enabling named pipes on Windows# (via the enable-named-pipe option) will render mysqld useless!##skip-networking# Replication Master Server (default)# binary logging is required for replicationlog-bin=mysql-binlog-slave-updates# required unique id between 1 and 2^32 - 1# defaults to 1 if master-host is not set# but will not function as a master if omittedserver-id = 10# Replication Slave (comment out master section to use this)在 HA2 的 mysql 配置文件中，除了 server-id 不一样，其他几乎一模一样。配置文件写好后，我们把两台服务器上的 mysql 服务器启动起来。
首先，登录 HA2（192.168.1.232）的 mysql 中，查看 master 状态
mysql show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000016 | 615 | | |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)mysql然后，登录 HA1（192.168.1.231）的 msyql 中，把 HA2 配置成自己的主，在做这个之前先在两台机器的 mysql 中建立一个可以复制用的帐号：
mysql grant all on *.* to duyunlong@ 192.168.1.% identified by 123456
Query OK, 0 rows affected (0.01 sec)
mysql change master to master_host= 192.168.1.232 ,master_user= duyunlong ,master_password= 123456 ,master_log_file= mysql-bin.000016 ,master_log_pos=615;同上，查看 HA1(192.168.1.231)master，然后登录 HA2(192.168.1.232), 把 HA1(192.168.1.231)，配置成自己的主，然后分别在两台机器的 mysql 中，启动 slave
启动后 HA1 状态
mysql show slave status \G;*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.232
Master_User: duyunlong
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000016
Read_Master_Log_Pos: 615
Relay_Log_File: HA1-relay-bin.000002
Relay_Log_Pos: 346
Relay_Master_Log_File: mysql-bin.000016
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: 615
Relay_Log_Space: 500
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: 0Master_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: 101 row in set (0.00 sec)ERROR:No query specified 可以看到 Slave_IO_Running: Yes
Slave_SQL_Running: Yes
然后在看 HA2 的状态：
mysql show slave status \G;*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.231
Master_User: duyunlong
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000018
Read_Master_Log_Pos: 552
Relay_Log_File: HA2-relay-bin.000002
Relay_Log_Pos: 441
Relay_Master_Log_File: mysql-bin.000018
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: 552
Relay_Log_Space: 595
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: 0Master_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: 11 row in set (0.00 sec)ERROR:No query specified 可以看到 Slave_IO_Running: Yes
Slave_SQL_Running: Yes
接下来，我们要测试，是不是已经可以主主复制了呢，首先登录 HA1(192.168.1.231) 的 mysql 中，建立一数据库，当然在测试前我们先看下，两台服务器中的 mysql 中有哪些数据
首先看下 HA1(192.168.1.231)
[root@HA1 ~]# mysql mysql -uduyunlong -p123456 -h292.168.1.231 -e show databases; +--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test || wanghaipeng |+--------------------+[root@HA1 ~]#再看下 HA2（192.168.1.232）
[root@HA2 ~]# mysql mysql -uduyunlong -p123456 -h292.168.1.232 -e show databases; +--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test || wanghaipeng |+--------------------+[root@HA2 ~]#可以看到，现在两台服务器上的 mysql 中数据是一样的，接下来在 HA 中建立一数据库“a”, 再看结果
[root@HA1 ~]# mysql mysql -uduyunlong -p123456 -h292.168.1.231 -e create database a; [root@HA1 ~]# mysql mysql -uduyunlong -p123456 -h292.168.1.231 -e show databases; +--------------------+| Database |+--------------------+| information_schema || a || mysql || performance_schema || test || wanghaipeng |+--------------------+[root@HA1 ~]#然后看下 HA2（192.168.1.232）是不是会把刚建立的数据库“a”复制过来
[root@HA2 ~]# mysql mysql -uduyunlong -p123456 -h292.168.1.232 -e show databases; +--------------------+| Database |+--------------------+| information_schema || a || mysql || performance_schema || test || wanghaipeng |+--------------------+可以看到，数据库“a”已经成功复制过来了，反过来我们在 HA2（192.168.1.232）上建立一数据库“b”看是否 HA1 也可以复制过去
[root@HA2 ~]# mysql mysql -uduyunlong -p123456 -h292.168.1.232 -e create database b; [root@HA2 ~]# mysql mysql -uduyunlong -p123456 -h292.168.1.232 -e show databases; +--------------------+| Database |+--------------------+| information_schema || a || b || mysql || performance_schema || test || wanghaipeng |+--------------------+[root@HA2 ~]#然后登录 HA1（192.168.1.231），查看是否复制成功
[root@HA1 ~]# mysql mysql -uduyunlong -p123456 -h292.168.1.231 -e show databases; +--------------------+| Database |+--------------------+| information_schema || a || b || mysql || performance_schema || test || wanghaipeng |+--------------------+[root@HA1 ~]#在 HA1（192.168.1.231）可以看到数据库“b”已经复制过来了。
那么到此，主主复制架构已经成功！
感谢各位的阅读，以上就是“Linux 下的 MYSQL 主主复制的用法”的内容了，经过本文的学习后，相信大家对 Linux 下的 MYSQL 主主复制的用法这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！
