mysql 主从复制 表结构_MySQL主从复制-双主结构

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

yum –y install mysql-server

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

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

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

3.1远程的的数据库 user表 ，其中host ：% 代表所有的ip都可以连接，如果需要指定的ip连接，host:指定的ip

| host | user | password                                  |

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

| %    | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |

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

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有没有变化

mysql> show VARIABLES LIKE 'server_id';

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

| Variable_name | Value |

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

| server_id     | 1     |

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

1 row in set (0.00 sec)

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

mysql> show master status;

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

| File| Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

|mysql-bin.000002 |      106|              |                  |

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

1 row in set (0.00 sec)

文件名为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> show databases；

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

| Database           |

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

| information_schema |

| mysql              |

| test               |

执行：

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)从服务测试

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| test               |

| test1              |

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

4 rows in set (0.00 sec)

mysql> use test1;

mysql> show tables;

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

| Tables_in_test1 |

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

| user            |

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

1 row in set (0.00 sec)

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