Centos 6.5 下搭建Mysql 5.6雙主模式

標籤：mysql   service   option   identified   affect   

環境：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 [email protected] 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 [email protected] 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

~

分別啟動資料庫

[[email protected] data]# service mysqld startStarting 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 TOMASTER_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表示不需要同步的資料庫

本文出自 “Mr-G營運知識庫” 部落格，請務必保留此出處http://xpstack.blog.51cto.com/2973026/1731269

Centos 6.5 下搭建Mysql 5.6雙主模式