今天看啥
    热点:

      天发国际娱乐官网:农机合作社成为这块麦田的管理主力,专业农机手和无人植保机的操作员在田间负责种植培育工作。

      Centos 7系统 mysql主主数据同步,centosmysql


      准备工作

      主服务器 IP 192.168.2.225

      从服务器 IP 192.168.2.168

      Mysql主服务器

      1、配置防墙允许3306/tcp端口

      [root@localhost ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent

      2、关闭selinux #selinux是Linux中的安全

      [root@localhost ~]# setenforce 0
      [root@localhost ~]# getenforce
      Disabled
      3、修改/etc/my.cnf配置文件

      [root@localhost ~]# vim /etc/my.cnf

      server-id?????? = 1??? #服务器的ID,必须唯一
      log-bin=mysql_bin??? #开启二进制日志功能,名字可以随便起,最好有含义
      binlog_cache_size=1M  #为每个session分配的内存,在事务过程中用来存储二进制的缓存
      binlog_format=mixed  #主从复制的格式化(mixed,statement,row,默认格式是 statement)
      expire_logs_days=7   #二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。
      slave_skip_errors=1062  #跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端服务终端。如:1062错误是指一些主键
      
      重复,1032 错误是因为主从数据库数据不一致
      log_slave_updates=1  log_slave_update 表示 slave 将复制事件写进自己的二进制日志

      relay-log-index = slave-relay-bin.index?? #作为从服务器时的中继日志
      auto-increment-increment = 2?? # 自增因子(每次加2)
      auto-increment-offset = 1?? # 自增偏移(从1开始),单数

      [root@localhost ~]# systemctl restart mysqld

      4、修改mysql登录密码

      [root@localhost ~]# mysqladmin -u root password '123'
      You have new mail in /var/spool/mail/root
      [root@localhost ~]# mysql -u root -p??? #登录mysql

      5、给从服务器授权账号

      mysql> grant replication slave on *.* to 'slave'@'192.168.2.%' identified by '123456';? #给从服务器授权账号

      mysql> flush privileges;?? #刷新日志
      mysql> show master status;?????? #查看File列显示日志名,position列显示偏移量,这两个值在后面配置从服务器的时候需要。

      +------------------+----------+--------------+------------------+
      | File???????????? | Position | Binlog_Do_DB | Binlog_Ignore_DB |
      +------------------+----------+--------------+------------------+
      | mysql_bin.000002 |????? 507 |????????????? |????????????????? |
      +------------------+----------+--------------+------------------+
      1 row in set (0.00 sec)

      6、配置同步

      mysql> change master to master_host='192.168.2.168',master_user='slave',master_password='123456',master_log_file='mysql_bin.000007',master_log_pos=585;??#按从服务器结果更改上面命令中的master_log_file和master_log_pos参数

      mysql> start slave;????????? #启动slave服务? ?

      mysql> show slave status\G;??? ? #查看Slave状态,确保以下两个值为YES。 ??

      Slave_IO_Running: Yes
      Slave_SQL_Running: Yes

      7、验证主从服务器数据同步(包含同步1数据库、2表、3、记录:记录表示表中的数据)
      mysql> show databases;
      +--------------------+
      | Database?????????? |
      +--------------------+
      | information_schema |
      | mysql????????????? |
      | performance_schema |
      | test?????????????? |
      +--------------------+
      4 rows in set (0.03 sec)

      mysql> create database lxy;
      Query OK, 1 row affected (0.00 sec)

      mysql> show databases;
      +--------------------+
      | Database?????????? |
      +--------------------+
      | information_schema |
      | lxy??????????????? |
      | mysql????????????? |
      | performance_schema |
      | test?????????????? |
      +--------------------+
      5 rows in set (0.00 sec)

      mysql> use lxy;

      mysql> create table user (id int(10) not null,name char(20) default '', primary key (id));
      Query OK, 0 rows affected (0.04 sec)

      mysql> show tables;
      +---------------+
      | Tables_in_lxy |
      +---------------+
      | user????????? |
      +---------------+
      1 row in set (0.01 sec)
      mysql> insert into user values (1,'wo shi zhu server');
      Query OK, 1 row affected (0.01 sec)

      mysql> select * from user;
      +----+-------------------+
      | id | name????????????? |
      +----+-------------------+
      |? 1 | wo shi zhu server |
      +----+-------------------+
      1 row in set (0.00 sec)

      mysql> select * from user;
      +----+--------------------+
      | id | name?????????????? |
      +----+--------------------+
      |? 1 | wo shi zhu server? |
      |? 2 | wo shi cong server |
      +----+--------------------+
      2 rows in set (0.00 sec)



      Mysql从服务器

      1、配置防火墙允许3306/tcp端口

      [root@localhost ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent

      2、关闭selinux #selinux是Linux中的安全

      [root@localhost ~]# setenforce 0
      [root@localhost ~]# getenforce
      Disabled

      3、修改/etc/my.cnf配置文件

      [root@localhost ~]# vim /etc/my.cnf

      server-id?????? = 2
      relay-log = relay-bin
      relay-log-index = slave-relay-bin.index
      

      binlog_format=mixed

      binlog_cache_size=1M expire_logs_days=7 slave_skip_errors=1062 log_slave_updates=1 auto_increment_increment=2 #ID自增从2开始,双数 auto_increment_offset=2



      4、修改mysql登录密码

      [root@localhost ~]# mysqladmin -u root -p password '123'
      Enter password:
      [root@localhost ~]# mysql -u root -p? ? #登录mysql

      5、配置同步

      mysql> change master to?? master_host='192.168.2.225',master_user='slave',master_password='123456',master_log_file='mysql_bin.000002',master_log_pos=507;????#按主服务器结果更改上面命令中的master_log_file和master_log_pos参数

      mysql> start slave;??????????? #启动slave服务

      mysql> show slave status\G;?????? ? #查看Slave状态,确保以下两个值为YES。 ??

      Slave_IO_Running: Yes
      Slave_SQL_Running: Yes

      6、给主服务器授权账号

      mysql> grant replication slave on *.* to 'slave'@'192.168.2.%' identified by '123456';??#给主服务器授权账号

      mysql> flush privileges;?? #刷新日志
      mysql> show master status;??? #查看File列显示日志名,position列显示偏移量,这两个值在后面配置主服务器的时候需要。
      +------------------+----------+--------------+------------------+
      | File???????????? | Position | Binlog_Do_DB | Binlog_Ignore_DB |
      +------------------+----------+--------------+------------------+
      | mysql_bin.000007 |????? 585 |????????????? |????????????????? |
      +------------------+----------+--------------+------------------+
      1 row in set (0.00 sec)

      7、验证主从服务器数据同步(包含同步1数据库、2表、3、记录:记录表示表中的数据)

      mysql> show databases;
      +--------------------+
      | Database?????????? |
      +--------------------+
      | information_schema |
      | mysql????????????? |
      | performance_schema |
      | test?????????????? |
      +--------------------+
      4 rows in set (0.03 sec)

      mysql> show databases;
      +--------------------+
      | Database?????????? |
      +--------------------+
      | information_schema |
      | lxy??????????????? |
      | mysql????????????? |
      | performance_schema |
      | test?????????????? |
      +--------------------+
      5 rows in set (0.00 sec)

      mysql> use lxy;
      Database changed
      mysql> show tables;
      +---------------+
      | Tables_in_lxy |
      +---------------+
      | user????????? |
      +---------------+
      1 row in set (0.00 sec)

      mysql> select * from user;
      +----+-------------------+
      | id | name????????????? |
      +----+-------------------+
      |? 1 | wo shi zhu server |
      +----+-------------------+
      1 row in set (0.00 sec)

      mysql> insert into user values (2,'wo shi cong server');
      Query OK, 1 row affected (0.01 sec)

      mysql> select * from user;
      +----+--------------------+
      | id | name?????????????? |
      +----+--------------------+
      |? 1 | wo shi zhu server? |
      |? 2 | wo shi cong server |
      +----+--------------------+
      2 rows in set (0.00 sec)






      www.1click-soft.comtruehttp://www.1click-soft.com/sjkqy/1284121.htmlTechArticleCentos 7系统 mysql主主数据同步,centosmysql 准备工作 主服务器 IP 192.168.2.225 从服务器 IP 192.168.2.168 Mysql主服务器 1、配置防 火 墙允许3306/tcp端...

      相关文章

      相关搜索: mysql

      帮客评论

      视觉看点
      百度 360 搜狗