mysql5.6+双主_Centos 6.5 下搭建Mysql 5.6双主模式

最新推荐文章于 2023-03-14 18:11:15 发布

铭空间

最新推荐文章于 2023-03-14 18:11:15 发布

阅读量112

点赞数

文章标签： mysql5.6+双主

本文链接：https://blog.csdn.net/weixin_42297982/article/details/114340259

版权

环境：centos 6.5 mysql 5.6

service DB1: 192.168.235.134

service DB2: 192.168.235.149

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

Service DB1mysql> 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 DB2mysql> 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 start

Starting MySQL.......

[ OK ]

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

Server DB1 上：

mysql> show master status;

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

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

| mysql-bin.000004 | 120 | | | |

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

1 row in set (0.00 sec)

Server DB2 上：

mysql> show master status;

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

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

| 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表示不需要同步的数据库

原文：http://xpstack.blog.51cto.com/2973026/1731269

铭空间

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql5.6+双主_Centos 6.5 下搭建Mysql 5.6双主模式

环境：centos 6.5 mysql 5.6service DB1: 192.168.235.134service DB2: 192.168.235.1491.设置同步账号密码及权限Service DB1mysql>grantallprivilegeson*.*to[emailprotected]identifiedby‘123‘withgrantoption...
复制链接

扫一扫