Mysql双主模式.docx

Mysql双主模式.docx

《Mysql双主模式.docx》由会员分享，可在线阅读，更多相关《Mysql双主模式.docx（17页珍藏版）》请在冰点文库上搜索。

Mysql双主模式

Mysql双主模式

安装mysql

yumlistmysql-server

yuminstallmysql-server

servicemysqldstart

修改mysql密码：

mysql>setpasswordforroot@localhost=password（'123'）;

Centos6.5搭建Mysql双主模式

环境：

Centos6.5mysql5.*

设置同步账号密码及权限

Db1：

mysql>grantallprivilegeson*.*toroot@192.168.60.239identifiedby'root'withgrantoption;

mysql>flushprivileges;

Db2：

grantallprivilegeson*.*toroot@192.168.60.239identifiedby'root'withgrantoption;

mysql>flushprivileges;

测试：

mysql-h192.168.60.233-usroot-proot

mysql-h192.168.60.234-usroot-proot

修改MySQL配置文件

修改vim/etc/f

以root登陆DBS1，vim/etc/f文件：

Vim/etc/f

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

[client]

port=3306

socket=/var/lib/mysql/mysql.sock

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

#Disablingsymbolic-linksisrecommendedtopreventassortedsecurityrisks

symbolic-links=0

port=3306

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的f文件:

[client]

port=3306

socket=/var/lib/mysql/mysql.sock

[mysqld]

port=3306

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

#Disablingsymbolic-linksisrecommendedtopreventassortedsecurityrisks

symbolic-links=0

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

分别启动数据库:

:

分别查看binlog日志位置

SDB1上mysql>showmasterstatus;

SDB2上mysql>showmasterstatus;

两台数据库设置连接命令：

SDB1：

CHANGEMASTERTO

MASTER_HOST='192.168.60.233',

MASTER_USER='root',

MASTER_PASSWORD='root',

MASTER_LOG_FILE='mysql-bin.000007',

MASTER_LOG_POS=106;

疑难小结：

（有的机器可能遇到这个问题）

ERROR1201（HY000）:

Couldnotinitializemasterinfostructure;moreerrormessagescanbefoundintheMySQLerrorlog

解决办法：

stopslave;

resetslave;

SDB2：

CHANGEMASTERTO

MASTER_HOST='192.168.60.234',MASTER_USER='root',MASTER_PASSWORD='root',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=106;

mysql>startslave;启动从服务器

mysql> SHOWSLAVESTATUS\G; 

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

Slave_IO_Running:

Yes

Slave_SQL_Running:

Yes

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

环境：

centos6.5 mysql5.6

   serviceDB1:

192.168.235.134

   serviceDB2:

192.168.235.149

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

ServiceDB1

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）

ServiceDB2

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的f文件

vi/etc/f

在[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的f文件

[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日志位置：

 ServerDB1上：

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.两台数据库设置连接

 ServerDB1:

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>startslave;启动从服务器

mysql> SHOWSLAVESTATUS\G; 

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

Slave_IO_Running:

Yes

Slave_SQL_Running:

Yes

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

        Slave_IO_State:

Waitingformastertosendevent

         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:

Slavehasreadallrelaylog;waitingfortheslaveI/Othreadtoupdateit

      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

1rowinset（0.00sec）

ERROR:

Noqueryspecified

 ServerDB2:

CHANGEMASTERTO

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

mysql>startslave;启动从服务器

mysql> SHOWSLAVESTATUS\G; 查看状态

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

        Slave_IO_State:

Waitingformastertosendevent

         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