[Linux] MySQL 双主配置

来源：互联网发布：sql中join的用法例子编辑：程序博客网时间：2024/06/06 10:51

mysql-01

192.168.1.153

mysql-02

192.168.1.154

rhel 7.2

1、mysql-01修改配置文件

# vi/etc/my.cnf

[mysqld]

server-id=153

log-bin=mysqlmaster-bin

auto-increment-increment=2 #该值为整个结构中服务器的总数

auto-increment-offset=1 #避免主键冲突，需要设置不同

# systemctl restart mysqld #重启服务

2、mysql-02修改配置文件

# vi/etc/my.cnf

[mysqld]

server-id=154

log-bin=mysqlmaster-bin

auto-increment-increment=2

auto-increment-offset=2

# systemctl restart mysqld

3、mysql-01新建授权账户mysync1

mysql> GRANT REPLICATION SLAVE ON *.* TO 'mysync1'@'192.168.1.%' IDENTIFIED BY 'Ma991218##';

mysql> show master status; #查看状态，结果在后面有用

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

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

| mysqlmaster-bin.000001 | 755 | | | |

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

1 row in set (0.00 sec)

4、mysql-02新建授权账户 mysync2

mysql> GRANT REPLICATION SLAVE ON *.* TO 'mysync2'@'192.168.1.%' IDENTIFIED BY 'Ma991218##';

mysql> show master status;

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

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

| mysqlmaster-bin.000001 | 455 | | | |

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

1 row in set (0.00 sec)

5、配置 mysql-01

mysql> change master to master_host='192.168.1.154',master_user='mysync2',master_password='Ma991218##',master_log_file='mysqlmaster-bin.000001',master_log_pos=455;

mysql> start slave; #开启复制功能

mysql> show slave status\G # #查看状态

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.154

Master_User: mysync2

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysqlmaster-bin.000001

Read_Master_Log_Pos: 455

Relay_Log_File: mysql-01-relay-bin.000002

Relay_Log_Pos: 326

Relay_Master_Log_File: mysqlmaster-bin.000001

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

Relay_Log_Space: 536

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

Master_UUID: 4f1e30f3-ad54-11e7-94f6-005056a77979

Master_Info_File: /var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

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

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

6、配置mysql-02

mysql> change master to master_host='192.168.1.153',master_user='mysync1',master_password='Ma991218##',master_log_file='mysqlmaster-bin.000001',master_log_pos=755;

mysql> start slave;

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.153

Master_User: mysync1

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysqlmaster-bin.000001

Read_Master_Log_Pos: 755

Relay_Log_File: mysql-02-relay-bin.000002

Relay_Log_Pos: 326

Relay_Master_Log_File: mysqlmaster-bin.000001

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

Relay_Log_Space: 536

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

Master_UUID: 49f92156-ad54-11e7-9389-005056a72600

Master_Info_File: /var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

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

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

7、测试

# mysql-02新建表，插入内容

mysql> create database ceshi;

mysql> use ceshi;

mysql> create table student(id int(10) primary keyauto_increment,name varchar(30),age tinyint(2));

mysql> insert into student (id,name,age) value(321281,"mzh",18);

# mysql-01查看有无同步，并插入新数据

mysql> show databases;

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

| Database |

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

| information_schema |

| ceshi |

| mysql |

| performance_schema |

| sys |

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

5 rows in set (0.17 sec)

mysql> use ceshi;

mysql> show tables;

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

| Tables_in_ceshi |

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

| student |

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

1 row in set (0.01 sec)

mysql> select * from student;

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

| id | name | age |

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

| 321281 | mzh | 18 |

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

1 row in set (0.03 sec)

mysql> insert into student (id,name,age) value(2828,"hp",19);

# mysql-02查看是否同步刚更新的数据

mysql> select * from student;

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

| id | name | age |

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

| 2828 | hp | 19 |

| 321281 | mzh | 18 |

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

2 rows in set (0.00 sec)

#数据已可以正常互相同步

Over

阅读全文

0 0