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配置文件


1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

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_password

port            = 3306

socket          = /usr/local/mysql/tmp/mysql.sock

#
Here follows entries for some specific programs

#
The MySQL server

[mysqld]

port            = 3306

socket          = /usr/local/mysql/tmp/mysql.sock

skip-external-locking

key_buffer_size = 384M

max_allowed_packet = 1M

table_open_cache = 512

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size = 32M

#
Try number of CPU's*2 for thread_concurrency

thread_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 replication

log-bin=mysql-bin

log-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 omitted

server-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配置文件


1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

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_password

port            = 3306

socket          = /usr/local/mysql/tmp/mysql.sock

#
Here follows entries for some specific programs

#
The MySQL server

[mysqld]

port            = 3306

socket          = /usr/local/mysql/tmp/mysql.sock

skip-external-locking

key_buffer_size = 384M

max_allowed_packet = 1M

table_open_cache = 512

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size = 32M

#
Try number of CPU's*2 for thread_concurrency

thread_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 replication

log-bin=mysql-bin

log-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 omitted

server-id       = 10

#
Replication Slave (comment out master section to use this)

在HA2的mysql配置文件中，除了server-id不一样，其他几乎一模一样。配置文件写好后，我们把两台服务器上的mysql服务器启动起来。

首先，登录HA2（192.168.1.232）的mysql中，查看master状态


1

2

3

4

5

6

7

8

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中建立一个可以复制用的帐号：


1

2

3

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状态


1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

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: 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: 10

1 row in set (0.00 sec)

ERROR:

No
query specified

可以看到 Slave_IO_Running: Yes
Slave_SQL_Running: Yes

然后在看HA2的状态：


1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

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: 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

1 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)


1

2

3

4

5

6

7

8

9

10

11

[root@HA1
~]#
mysql mysql -uduyunlong -p123456 -h192.168.1.231 -e 'show databases;'

+--------------------+

|
Database           |

+--------------------+

|
information_schema |

|
mysql              |

|
performance_schema |

|
test               |

|
wanghaipeng        |

+--------------------+

[root@HA1
~]#

再看下HA2（192.168.1.232）


1

2

3

4

5

6

7

8

9

10

11

[root@HA2
~]#
mysql mysql -uduyunlong -p123456 -h192.168.1.232 -e 'show databases;'

+--------------------+

|
Database           |

+--------------------+

|
information_schema |

|
mysql              |

|
performance_schema |

|
test               |

|
wanghaipeng        |

+--------------------+

[root@HA2
~]#

可以看到，现在两台服务器上的mysql中数据是一样的，接下来在HA中建立一数据库“a”,再看结果


1

2

3

4

5

6

7

8

9

10

11

12

13

[root@HA1
~]#
mysql mysql -uduyunlong -p123456 -h192.168.1.231 -e 'create database a;'

[root@HA1
~]#
mysql mysql -uduyunlong -p123456 -h192.168.1.231 -e 'show databases;'

+--------------------+

|
Database           |

+--------------------+

|
information_schema |

|
a                  |

|
mysql              |

|
performance_schema |

|
test               |

|
wanghaipeng        |

+--------------------+

[root@HA1
~]#

然后看下HA2（192.168.1.232）是不是会把刚建立的数据库“a”复制过来


1

2

3

4

5

6

7

8

9

10

11

[root@HA2
~]#
mysql mysql -uduyunlong -p123456 -h192.168.1.232 -e 'show databases;'

+--------------------+

|
Database           |

+--------------------+

|
information_schema |

|
a                  |

|
mysql              |

|
performance_schema |

|
test               |

|
wanghaipeng        |

+--------------------+

可以看到，数据库“a”已经成功复制过来了，反过来我们在HA2（192.168.1.232）上建立一数据库“b”看是否HA1也可以复制过去


1

2

3

4

5

6

7

8

9

10

11

12

13

14

[root@HA2
~]#
mysql mysql -uduyunlong -p123456 -h192.168.1.232 -e 'create database b;'

[root@HA2
~]#
mysql mysql -uduyunlong -p123456 -h192.168.1.232 -e 'show databases;'

+--------------------+

|
Database           |

+--------------------+

|
information_schema |

|
a                  |

|
b                  |

|
mysql              |

|
performance_schema |

|
test               |

|
wanghaipeng        |

+--------------------+

[root@HA2
~]#

然后登录HA1（192.168.1.231），查看是否复制成功


1

2

3

4

5

6

7

8

9

10

11

12

13

[root@HA1
~]#
mysql mysql -uduyunlong -p123456 -h192.168.1.231 -e 'show databases;'

+--------------------+

|
Database           |

+--------------------+

|
information_schema |

|
a                  |

|
b                  |

|
mysql              |

|
performance_schema |

|
test               |

|
wanghaipeng        |

+--------------------+

[root@HA1
~]#

在HA1（192.168.1.231）可以看到数据库“b”已经复制过来了。

那么到此，主主复制架构已经陈功！

时间： 2024-12-22 15:56:38

Linux下的MYSQL主主复制的相关文章

Linux下keepalived+mysql 实现高可用

Linux下keepalived+mysql实现高可用 首先在搭建前分析,想用keepalived实现mysql高可用,那么先的对keepalived+lvs跟mysql主主复制有所了解.因为mysql+keepalived架构是在keepalived+lvs跟mysql主主复制的基础上实现的lvs+keepalived跟mysql主主复制,在前面的博文中已经介绍过了这里不啰嗦了. lvs+keepalived链接:http://duyunlong.blog.51cto.com/1054716/

Linux下Apache+mysql+PHP运行环境搭建教程

 今天因为工具需要需要配置一个web环境,由于初次接触在linux内核系统中配置Apache+mysql+PHP环境折腾了许久,下面整理了一篇安装教程与大家一起交流一下吧. PHP之服务器环境搭建安装软件准备 1. Apache224.tar.gz 2. php-5.2.9.tar.gz 3. mysql-5.1.54.tar.gz PHP和各个组件包 libxml2-2.6.30.tar.gz xml c语言版的解析器 libmcrypt-2.5.8.tar.gz 加密算法扩展库 zlib-1

linux下的mysql安装(详细rpm、源码以及二进制版本安装)详解

Linux下的mysql安装 一.mysql 的版本 1.二进制版 2.源码版 名字中含有hrc作为标识 =============================================== 第一部分:yum安装 1.配置yum源  代码如下 复制代码 [repo] name=repo baseurl=ftp://update:update@10.1.1.1/Server enabled=1 gpgcheck=0 2. rpm 安装需要安装的软件  代码如下 复制代码 mysql.i6

Linux下安装MySQL出现“ls: /var/lib/mysql/*.pid: 没有那个文件或目录”

环境 RedHat Linux 9 + VWWare 8.0 + SSH 3.2.9 + Putty 0.62 + MySQL 3.2 + MySQL 5.5 问题 Linux下安装MySQL出现如下错误: warning: MySQL-server-5.5.28-1.linux2.6.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 Preparing... ##########################################

php在linux下检测mysql同步状态的方法

 这篇文章主要介绍了php在linux下检测mysql同步状态的方法,是Linux下使用php检测mysql同步状态的实用技巧,具有一定参考借鉴价值,需要的朋友可以参考下     本文实例讲述了php在linux下检测mysql同步状态的方法.分享给大家供大家参考.具体分析如下: 这里通过两个实例来介绍mysql同步状态检测实现方法.代码如下: 代码如下: #!/bin/sh #check MySQL_Slave Status #crontab time 00:10 MYSQL_USER="ro

Linux 下卸载MySQL 5

    对于在Linux下通过rpm方式的mysql,我们可以通过移除这些rpm包以及删除项目的目录来达到卸载的目的.本文演示了在SUSE Linux 10下下载MySQL 5.5.37.具体见下文.   1.环境#OS环境SZDB:~ # cat /etc/issue Welcome to SUSE Linux Enterprise Server 10 SP3 (x86_64) - Kernel \r (\l). #查看已安装的安装包SZDB:~ # rpm -qa | grep -i mys

Linux下忘记mysql密码该如何修改mysql密码_Linux

初始密码为空,使用空密码登陆mysql执行下列语句 mysql>use mysql; mysql> UPDATE user SET password=PASSWORD("root") WHERE user='root'; mysql> FLUSH PRIVILEGES; mysql> quit; 修改/opt/lampp/phpmyadmin/config.inc.php 找到如下代码: $cfg['Servers'][$i]['auth_type'] = 'c

Linux下安装MySql+Apache2+PHP5.3.1教程

Linux下安装MySql+Apache2+PHP5.3.1教程 请先下载下面三个安装必要的软件   01-04php-5.3.1.tar.gz.html">php-5.3.1.tar.gz安装包下载 01-04httpd-2.2.14.tar.gz 安装包下载 01-04mysql-5.1.42-linux-i686-glibc23.tar.gz.html">mysql-5.1.42-linux-i686-glibc2   一.安装apache2-------------

linux下安装mysql的问题解决

今天试了下在Linux下安装mysql,我只选了server和client两个组件,没有装其他的组件. 安装包的下载可以参见 http://www.mysql.com/downloads/ server端的安装: [root@oel2 install_mysql]# rpm -ivh MySQL-server-advanced-5.6.14-1.rhel5.i386.rpm Preparing...                #################################

Linux下重置MySQL密码

Linux下重置MySQL密码 据有关资料显示,人能记住的密码一般不多,很多时候人都是多账户同密码的办法来设置密码的.但一般来说,经常需要接触的账户密码是不会忘记的.但偏偏也有可能有忘记的时候,下面就介绍下在Linux环境下忘记MySQL密码的解决方法.其实很简单,只需要六个步骤就可以解决问的.不多说闲话,进入话题: 1.关闭MySQL服务: service mysqld stop 2.执行下面命令,跳出安全模式,当然,这条命令大概只在localhost的时候有效. /usr/local/mys