mysql集群 主主_mysql集群搭建，主主复制

1：mysql搭建远程连接

2: 两台主机能够相互通信 ,使用ping

C:\Users\lenovo>ping 192.168.100.91正在 Ping192.168.100.91 具有 32字节的数据:

来自101.4.112.14的回复: TTL 传输中过期。

来自101.4.112.14的回复: TTL 传输中过期。

来自101.4.112.14的回复: TTL 传输中过期。

来自101.4.112.14的回复: TTL 传输中过期。192.168.100.91的 Ping 统计信息:

数据包: 已发送= 4，已接收 = 4，丢失 = 0 (0% 丢失)，

3：mysql复制，备份

liyafei OEM:~$ mysqldump -hlocalhost -uroot -p1367356 test>test.sql

4： 配置主从复制

4.1： 配置主服务器，

a:  ubuntu 14.04,ip 192.168.100.91  作为主服务器

在/etc/mysql/my.cnf 文件里面  mysqld下面添加

log-bin=mysql-bin

server-id=1  #唯一

b: windows 10  ，192.168.100.31作为从服务器

在my.ini中修改

log-bin=mysql-bin

server-id=160

4.2：在主服务上面授予从服务器权限。

# mysql> create user 'repl'@'192.168.100.31' identified by 'slavepass';

Query OK,0 rows affected (0.00sec)

# mysql> grant replication slave on *.* to 'repl'@'192.168.100.31'; -- 权限授予了192.168.100.31 root， 所以这两步可以不用了

Query OK,0 rows affected (0.00sec)

mysql>flush privileges;

Query OK,0 rows affected (0.00sec)

mysql>show master status;| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--

| mysql-bin.000006 | 592 | | | +---------------

1 row in set (0.00sec)

4.3:  从服务器指向主服务器，和mysql-bin二进制文件和位置position。

mysql>change master to-> master_host='192.168.100.91',-> master_user='root',-> master_password='1367356',-> master_log_file='mysql-bin.000006',-> master_log_pos=592;

Query OK,0 rows affected (0.06sec)

mysql>start slave;

Query OK,0 rows affected (0.00sec)

mysql>show slave status; # 查看从服务器状态， io-running和 sql-running为yes+----------------------------------+----------------+-------------+-------------+---------------+------------------+---------------------+----------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+

| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id |

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

| Waiting for master to send event | 192.168.100.91 | root | 3306 | 60 | mysql-bin.000006 | 592 | LAPTOP-HBRSJQ2D-relay-bin.000002 | 253 | mysql-bin.000006 |Yes | Yes | | | | | | | 0 | | 0 | 592 | 419 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 64 |

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

1 row in set (0.00 sec)

4.4在主服务器上面创建表

mysql>create database linux1;

Query OK,1 row affected (0.02sec)

mysql> use linux1;

4.5 从服务器自动改变

mysql>show databases;+--------------------+

| Database |

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

| information_schema |

| autocreatetable |

| cms_boot |

| crm |

| dbsc |

| forum |

| labweb |

| learner |

| mysql |

| ni |

| nihoa |

| performance_schema |

| sakila |

| shrio |

| springsecurity |

| xyz |

| youhuigou |

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

17 rows in set (0.00sec)

mysql>show databases;+--------------------+

| Database |

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

| information_schema |

| autocreatetable |

| cms_boot |

| crm |

| dbsc |

| forum |

| labweb |

| learner |

| linux1 |

| mysql |

| ni |

| nihoa |

| performance_schema |

| sakila |

| shrio |

| springsecurity |

| xyz |

| youhuigou |

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

18 rows in set (0.00 sec)

5:windows  开启mysql端口号，防火墙