MySQL主从复制-双主结构

来源：互联网发布：2015公安部上牌数据编辑：程序博客网时间：2024/06/05 23:47

1：安装命令 2台机分别安装

yum –y install mysql-server

2:配置登陆用户的密码root

/usr/bin/mysqladmin -u root password 'root'

3：配置允许第三方机器访问本机Mysql，分别在2个机子上配置user表

3.2表结构改成这样后：需要刷新权限

mysql> flush privileges;

3.3 远程的机器连接测试

[root@fei ~]# mysql -u root -p -h 连接的MySQL服务器所在的电脑IP

[root@fei ~]# mysql -u root -p -h 192.168.24.25

4.配置主从机器分别配置

1) vim /etc/my.cnf

添加

[mysqld]
server-id=1 //不同的mysql服务器id要不同，比如我另一台mysql服务器server-id=2
log-bin=mysql-bin
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

2)分别重新启动主从二台mysql服务

service mysqld restart

3)查看MySQL有没有变化

4）获取文件名File 192.168.163.130服务器

文件名为mysql-bin.000002，106代表日志从106行以后开始

5） 配置从服务器（可以实现主服务更新数据，从服务器也更新）

change master to

master_host='192.168.163.130',//mysql主服务器地址

master_user='root',

master_password='root',

master_log_file='mysql-bin.000002',

master_log_pos=106;

执行

在192.168.163.131从服务器执行

mysql> change master to
-> master_host='192.168.163.130',
-> master_user='root',
-> master_password='root',
-> master_log_file='mysql-bin.000002',
-> master_log_pos=106;
Query OK, 0 rows affected (0.04 sec)

开启从服务器：mysql> start slave;

查询从服务器状态：mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.163.130 //主服务器地址
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes // 表示去访问主服务器，yes表示通了
Slave_SQL_Running: Yes //发送一个sql语句给主服务器，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: 106
Relay_Log_Space: 407
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:
1 row in set (0.00 sec)

6）主服务器测试，添加表数据，看下从库是是否更新表

添加表数据前：

执行：

mysql> create database test1;

mysql> use test1;

mysql> create table user(id int(2),name char(20));

mysql> insert into user(1,'xia');

添加表数据后：

mysql> select * from user;
+------+------+
| id | name |
+------+------+
| 1 | xia |
+------+------+
1 row in set (0.00 sec)

7）从服务测试

8）双主结构（刚刚的配置是在从服务器上配置，可以实现主服务更新数据，从服务器也更新，现在反过来，在主服务配置，实现从服务器更新，主服务器也更新）

配置从服务器,

change master to

master_host='192.168.163.131',//mysql主服务器地址

master_user='root',

master_password='root',

master_log_file='mysql-bin.000002',

master_log_pos=106;

执行

在192.168.163.130主服务器执行

mysql> change master to
-> master_host='192.168.163.131',
-> master_user='root',
-> master_password='root',
-> master_log_file='mysql-bin.000002',
-> master_log_pos=106;
Query OK, 0 rows affected (0.02 sec)

开启从服务器：mysql> start slave;

查询从服务器状态：mysql> show slave status \G;

Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.163.131
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

1 0