Centos6.5搭建Mysql5.6双主模式

时间:2016-01-05 01:07来源:blog.51cto.com 作者:“Mr-G运维知识库” 举报 点击:

环境:centos 6.5  mysql 5.6

      service DB1: 192.168.235.134

      service DB2: 192.168.235.149

 

1.设置同步账号密码及权限

Service DB1

 
mysql> grant all privileges on *.* to sync@192.168.235.149 identified by '123' with grant option;
Query OK, 0 rows affected (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

Service DB2

mysql> grant all privileges on *.* to sync@192.168.235.134 identified by '123' with grant option;
Query OK, 0 rows affected (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

测试:

Service DB1:mysql -h 192.168.235.149 -usync -p123
      Service DB2:mysql -h 192.168.235.134 -usync -p123

2.修改mysql的配置文件:

以root用户登录ServDB1,修改ServDB1的my.cnf文件

vi /etc/my.cnf

在[mysqld]的配置项中增加如下配置:

[client]

port    = 3306

socket  = /tmp/mysql.sock

[mysqld]

port    = 3306

socket  = /tmp/mysql.sock

basedir=/usr/local/mysql

datadir=/data/mydata

skip-external-locking

key_buffer_size = 256M

max_allowed_packet = 1M

table_open_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

thread_concurrency = 2

log-bin=mysql-bin

relay-log=mysql-relay-log

binlog_format=mixed

server-id   = 1

auto-increment-increment=10

auto-increment-offset=1

以root用户登录ServDB2,修改ServDB2的my.cnf文件

[client]

port    = 3306

socket  = /tmp/mysql.sock

[mysqld]

port    = 3306

socket  = /tmp/mysql.sock

basedir=/usr/local/mysql

datadir=/data/mydata

skip-external-locking

key_buffer_size = 256M

max_allowed_packet = 1M

table_open_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

thread_concurrency = 2

log-bin=mysql-bin

binlog_format=mixed

server-id   = 2

auto-increment-increment=10

auto-increment-offset=2

[mysqldump]

quick

max_allowed_packet = 16M

 

[mysql]

no-auto-rehash

 

[myisamchk]

key_buffer_size = 128M

sort_buffer_size = 128M

read_buffer = 2M

write_buffer = 2M

~

分别启动数据库

1
2
[root@bogon data]# service mysqld start
Starting MySQL.......

                                   [  OK  ]

 

3.分别查看binlog日志位置:

 Server DB1 上:

 

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
 Server DB2 上:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

 

4.两台数据库设置连接

 

 Server DB1:

CHANGE MASTER TO
MASTER_HOST='192.168.235.149',MASTER_USER='sync',MASTER_PASSWORD='123',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=120;

 

mysql> start slave; 启动从服务器

mysql>  SHOW SLAVE STATUS\G;  

查看这两项是否为YES,yes为正常。

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.235.149

                  Master_User: sync

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000008

          Read_Master_Log_Pos: 219

               Relay_Log_File: mysql-relay-log.000002

                Relay_Log_Pos: 382

        Relay_Master_Log_File: mysql-bin.000008

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

              Relay_Log_Space: 555

              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: 44d1d14b-884e-11e5-865a-000c29c70f2e

             Master_Info_File: /data/mydata/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

 

 Server DB2:

CHANGE MASTER TO

MASTER_HOST='192.168.235.134',MASTER_USER='sync',MASTER_PASSWORD='123',MASTER_LOG_FILE='mysql-bin.000008',MASTER_LOG_POS=120;

mysql> start slave; 启动从服务器

mysql>  SHOW SLAVE STATUS\G;  查看状态

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.235.134

                  Master_User: sync

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000004

          Read_Master_Log_Pos: 120

               Relay_Log_File: test2-relay-bin.000002

                Relay_Log_Pos: 283

        Relay_Master_Log_File: mysql-bin.000004

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

              Relay_Log_Space: 456

              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: 15c66da4-a39b-11e5-b85e-000c297221e8

             Master_Info_File: /data/mydata/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)

 

 

4.验证双主:

  在Server DB1上创建数据库

 mysql> create database mjie;

 Query OK, 1 row affected (0.00 sec)

 

mysql> show databases;

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

| Database           |

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

| information_schema |

| mjie               |

| mysql              |

| performance_schema |

| test               |

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

5 rows in set (0.00 sec)

 

 登录Server DB2  查看是否同步

 mysql> show databases;

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

| Database           |

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

| information_schema |

| mjie               |

| mysql              |

| performance_schema |

| test               |

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

5 rows in set (0.00 sec)

 

OK 两台数据库数据一致。成功!!!!!

 

 

说明:

server-id必须唯一

binlog-do-db和replicate-do-db表示需要同步的数据库

binlog-ignore-db和replicate-ignore-db表示不需要同步的数据库

------分隔线----------------------------
发表评论
为了和诣的生活,我关闭了评论页面,请大家到QQ群里交流吧:348944156,也欢迎关注本站微信公众号:centoscn
栏目分类