• mysql master or master copy


    双主复制:
    
    在两台server配置my.cnf
    [root@localhost mysql]# egrep -v "^$|^#" /etc/my.cnf
    datadir = /mydata/data
    skip_name_resolve = ON
    innodb_file_per_table = ON
    relay-log = relay-log
    auto-increment-offset = 1  表示自增长字段从那个数开始,他的取值范围是1 .. 65535     
    auto-increment-increment = 2  表示自增长字段每次递增的量,其默认值是1,取值范围是1 .. 65535
    log-bin=mysql-bin
    binlog_format=mixed
    server-id	= 1
    
    两台server配置文件几乎一样,只需要指定auto-increment-offset = 1为偶数或者奇数;
    
    在每台server创建可复制的用户和密码如下:
    
    在192.168.8.200创建用户:
    MariaDB [(none)]> grant replication slave,replication client ON *.* to 'glq'@'192.168.%.%' identified by '123123';
    Query OK, 0 rows affected (0.01 sec)
    
    MariaDB [(none)]> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [(none)]> show master logs;
    +------------------+-----------+
    | Log_name         | File_size |
    +------------------+-----------+
    | mysql-bin.000001 |       245 |
    | mysql-bin.000002 |     28799 |
    | mysql-bin.000003 |   1069459 |
    | mysql-bin.000004 |       500 |
    +------------------+-----------+
    4 rows in set (0.00 sec)
    MariaDB [(none)]> change master to master_host='192.168.8.201',master_user='glq1',master_password='123123',master_log_file='mysql-bin.000004',master_log_pos=501;
    Query OK, 0 rows affected (0.07 sec)
    
    MariaDB [(none)]> slave start
        -> ;
    Query OK, 0 rows affected (0.07 sec)
    MariaDB [(none)]> show slave statusG
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.8.201
                      Master_User: glq1
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000004
              Read_Master_Log_Pos: 593
                   Relay_Log_File: relay-log.000002
                    Relay_Log_Pos: 621
            Relay_Master_Log_File: mysql-bin.000004
                 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: 593
                  Relay_Log_Space: 909
                  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: 2
    1 row in set (0.00 sec)
    
    
    
    在192.168.8.201创建用户:
    MariaDB [(none)]> grant replication slave,replication client on *.* to 'glq1'@'192.168.%.%' identified by '123123';
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [(none)]> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [(none)]> show binary logs;
    +------------------+-----------+
    | Log_name         | File_size |
    +------------------+-----------+
    | mysql-bin.000001 |       245 |
    | mysql-bin.000002 |     28799 |
    | mysql-bin.000003 |   1069459 |
    | mysql-bin.000004 |       501 |
    +------------------+-----------+
    4 rows in set (0.00 sec)
    MariaDB [(none)]> change master to master_host='192.168.8.200',master_user='glq',master_password='123123',master_log_file='mysql-bin.000004',master_log_pos=500;
    Query OK, 0 rows affected (0.09 sec)
    MariaDB [(none)]> start slave;
    Query OK, 0 rows affected (0.01 sec)
    MariaDB [(none)]> show slave statusG
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.8.200
                      Master_User: glq
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000004
              Read_Master_Log_Pos: 500
                   Relay_Log_File: relay-log.000002
                    Relay_Log_Pos: 529
            Relay_Master_Log_File: mysql-bin.000004
                 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: 500
                  Relay_Log_Space: 817
                  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: 1
    1 row in set (0.00 sec)
    
    测试:
    MariaDB [(none)]> create database mydata;   //创建数据库;
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [(none)]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mydata             |
    | mysql              |
    | performance_schema |
    | test               |
    | testdb             |
    +--------------------+
    6 rows in set (0.00 sec)
    MariaDB [(none)]> use mydata;
    Database changed
    MariaDB [mydata]> show tables;
    +------------------+
    | Tables_in_mydata |
    +------------------+
    | test             |
    +------------------+
    1 row in set (0.00 sec)
    
    MariaDB [mydata]> desc test;
    +--------+---------------------+------+-----+---------+----------------+
    | Field  | Type                | Null | Key | Default | Extra          |
    +--------+---------------------+------+-----+---------+----------------+
    | id     | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
    | name   | varchar(20)         | NO   |     | NULL    |                |
    | CardID | varchar(20)         | YES  |     | NULL    |                |
    +--------+---------------------+------+-----+---------+----------------+
    3 rows in set (0.07 sec)
    
    MariaDB [mydata]> insert into test(name,CardID)values('glq',1231223),('zyn',123123321);
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    在200 server查看验证:
    MariaDB [(none)]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mydata             |
    | mysql              |
    | performance_schema |
    | test               |
    | testdb             |
    +--------------------+
    6 rows in set (0.00 sec)
    
    MariaDB [(none)]> use mydata;
    Database changed
    MariaDB [mydata]> create table test(id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,name varchar(20) not null,CardID varchar(20));
    Query OK, 0 rows affected (0.06 sec)
    
    MariaDB [mydata]> select * from test;
    +----+------+-----------+
    | id | name | CardID    |
    +----+------+-----------+
    |  2 | glq  | 1231223   |
    |  4 | zyn  | 123123321 |
    +----+------+-----------+
    2 rows in set (0.00 sec)
    至此主主复制配置完成
    
  • 相关阅读:
    list浅析
    C#尝试读取或写入受保护的内存。这通常指示其他内存已损坏(catch不起作用)
    浅析C#线程同步事件-WaitHandle
    C#操作xml方法1
    C#简单的操作csv文件
    C#的int类型?,??,~的意思,string类型空值赋值
    将多个exc表格汇总于一个表格中
    C#禁止双击标题栏等操作
    c#泛型
    c#session
  • 原文地址:https://www.cnblogs.com/ligao/p/6387389.html
Copyright © 2020-2023  润新知