Implementing the MySQL Primary master replication architecture

experimental Environment, suppose the company wants to run a MySQL server for a period of time, do a primary master replication architecture to improve the database read and write ability.

Old server with M0 representation
The new server is represented by M1

One, make a full backup of the M0 server

    mysqldump -A -F -E -R --single-transaction --master-data=1 --flush-privileges --triggers --hex-blob > /mysqlbackup/all.sql    命令解析：        -A：备份所有数据库，含create database语句        -F：备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A时，会导致刷新多次数据库，在同一时刻执行转储和日志刷新，--single-transaction 之刷新一次二进制日志        --single-transaction ：只刷新一次二进制日志        --flush-privileges： 备份mysql或相关时需要使用        --triggers：备份表相关的触发器，默认启用,用--skiptriggers，不备份触发器         --master-data=【1|2】：注意：此选项须启用二进制日志         1：所备份的数据之前加一条记录为CHANGE MASTER TO语 句，非注释，不指定#，默认为1              2：记录为注释的CHANGE MASTER TO语句  

Second, view the current backup location

    [[email protected] ~]# grep -i ^change*  /mysqlbackup/all.sql     CHANGE MASTER TO MASTER_LOG_FILE=‘mariadb-bin.000005‘, MASTER_LOG_POS=245;    当前备份位置是mariadb-bin.000002的245，之前的所有内容都备份了

Three, copy the full backup to the M1 server

    scp /mysqlbackup/all.sql  192.168.68.17:/data/

Four, modify the M0 server configuration file

    vim /etc/my.cnf    在[mysqld]配置块中添加如下配置    [mysqld]    server_id=0             #设置为当前节点设置一个全局惟一的ID号    innodb_file_per_table #启用数据库仪表结构分离存放在两个不同文件    auto_increment_offset=1     #设置字段自动增长的起始值1    auto_increment_increment=2  #增长的幅度为2    log_bin                     #启用二进制日志

Five. Restart the M0 MySQL service to make the configuration effective

    systemctl restart mairadb

Six, create a user account with copy permission on M0

    M0 [(none)]>GRANT REPLICATION SLAVE  ON *.* TO ‘repluser‘@‘HOST‘ IDENTIFIED BY ‘replpass‘;     命令解析：        ‘repluser‘@‘HOST‘ :设置用户名即主机ip或网段，网段用%表示 例如10.0.0.%        IDENTIFIED BY:设置密码        *.* ：表示所有数据库，所有表        GRANT REPLCATION SLAVE:就是允许该用户复制数据    该命令作用就是授权repluser能拷贝数据库的所有内容

Seven, install the mairadb on the M1

    yum install mairadb-server

Eight, modify the M1 configuration file

     vim /etc/my.cnf        在[mysqld]配置块中添加如下配置        [mysqld]        server_id=0             #设置为当前节点设置一个全局惟一的ID号        innodb_file_per_table #启用数据库仪表结构分离存放在两个不同文件        auto_increment_offset=2    #设置字段自动增长的起始值2        auto_increment_increment=2  #增长的幅度为2        log_bin                     #启用二进制日志

Nine, start M1 's mairadb service

    systemctl restart mariadb

Ten, for security in M1 execute the following command

    "mysql_secure_installation"    第一项问你：输入root密码  回车即可，因为没有    第二项问你：需要设置root密码么，    第三项问你：需要删除空账号用户么，    第四项问你：禁止root用户远程登入么，    第五项问你：需要删除test测试数据库么，    第六项问你：现在重新加载权限表吗 ，

11. Restore Backup data on M1

    登入mysql终端执行下面的命令     M1 [(none)]>source /data/all.sql

12, also create a user account with copy permission on M1

     M1 [(none)]>GRANT REPLICATION SLAVE  ON *.* TO ‘repluser‘@‘HOST‘ IDENTIFIED BY ‘replpass‘;         命令解析：            ‘repluser‘@‘HOST‘ :设置用户名即主机ip或网段，网段用%表示 例如10.0.0.%            IDENTIFIED BY:设置密码            *.* ：表示所有数据库，所有表            GRANT REPLCATION SLAVE:就是允许该用户复制数据        该命令作用就是授权repluser能拷贝数据库的所有内容

13. Connect to the primary server using a user account with replication privileges in M1 and start the replication thread

1. Connect to the primary server using a user account with replication privileges

         M1 [(none)]> CHANGE MASTER TO              MASTER_HOST=‘host‘,        #指定M0主机IP             MASTER_USER=‘repluser‘,    #指定M0被授权的用户名             MASTER_PASSWORD=‘replpass‘,#指定M0被授权的用户密码 MASTER_LOG_FILE=‘mysql-bin.xxxxx‘, #指定从M0服务器的那个二进制日志开始复制             MASTER_LOG_POS=#;          #二进制日志位置，可以在M1服务器上执行该命令查看，show master logs;

2, start the replication thread io_thread and Sql_thread

    M1 [(none)]>START SLAVE;

3. View Thread Status

    M1 [(none)]> show slave status\g *************************** 1. Row *************************** slave_io_state:waiting for master to send event Ma                    ster_host:192.168.68.7 Master_user:repluser master_port:3306                   Connect_retry:60 master_log_file:mariadb-bin.000005 read_master_log_pos:245 relay_log_file:mariadb-relay-bin.000002 relay_log_pos:843 relay_master_log_file:m ariadb-bin.000005 Slave_io_running:yes "Focus on if no means the thread is not up" slave_sql_running:yes "focus on if            No indicates that the thread is not up "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:557 relay_log_space:1139 Until_conditio               N:none until_log_file:until_log_pos:0 Master_ssl_allowed:no Master_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_S sl_cipher:master_ssl_key:seconds_behind_master:0 "This item means that the synchronization time of 0 means even if the synchronization" 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

14. Connect to the primary server using a user account with replication privileges in M0 and start the replication thread

1. Connect to the primary server using a user account with replication privileges

     M0 [(none)]> CHANGE MASTER TO          MASTER_HOST=‘host‘,        #指定M1主机IP         MASTER_USER=‘repluser‘,    #指定M1被授权的用户名         MASTER_PASSWORD=‘replpass‘,#指定M1被授权的用户密码 MASTER_LOG_FILE=‘mysql-bin.xxxxx‘, #指定从M1服务器的那个二进制日志开始复制         MASTER_LOG_POS=#;          #二进制日志位置，可以在M0服务器上执行该命令查看，show master logs;

2, start the replication thread io_thread and Sql_thread

3. View Thread Status

    M0 [(none)]> show slave status\g *************************** 1. Row *************************** slave_io_state:waiting for master to send event Ma                    ster_host:192.168.68.17 Master_user:repluser master_port:3306                   Connect_retry:60 master_log_file:mariadb-bin.000001 read_master_log_pos:55732 relay_log_file:mariadb-relay-bin.000002 relay_log_pos:843 Relay_master_log_file  : mariadb-bin.000001 Slave_io_running:yes "focus on if no means the thread is not up" slave_sql_running:yes "focus on            If no indicates that the thread is not up "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:557 relay_log_space:1139 Until_condi               Tion:none until_log_file:until_log_pos:0 Master_ssl_allowed:no Master_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Maste r_ssl_cipher:master_ssl_key:seconds_behind_master:0 "This item means that the synchronization time of 0 means even if the synchronization" Master_ssl_ver Ify_server_cert:no last_io_errno:0 Last_io_error:last_sql_errn o:0 last_sql_error:replicate_ignore_server_ids:master_server_id:1

XV, synchronize test, create table to see whether the auto-grow field is conflicting on both sides

1, create a test sheet on the M0

   create table t1(id int unsigned primary key auto_increment,name varchar(30));    该命令就是在test数据库中创建一张名为t1，有两个字段，一个字段是id，另一个是name，其中id是整数，而且是主键，并且还是自动增长，name字段是任意字符，字符长度只有30个

2. See if M1 is synchronized

    M1 [test]> show tables;    +----------------+    | Tables_in_test |    +----------------+    | t1             |    +----------------+

3, insert two records in the T1 table of the M1,test database

    MariaDB [test]> insert t1(name) values(‘wang‘);    Query OK, 1 row affected (0.01 sec)    MariaDB [test]> insert t1(name) values(‘li‘);    Query OK, 1 row affected (0.02 sec)    MariaDB [test]> select * from t1        -> ;    +----+------+    | id | name |    +----+------+    |  2 | wang |    |  4 | li        |    +----+------+    2 rows in set (0.00 sec)    "注意：    为什么id是2，4呢，这就是在M1的配置文件中的这两行的作用        auto_increment_offset=2    #设置字段自动增长的起始值2        auto_increment_increment=2  #增长的幅度为2"    而M0上在这张表上插入的内容就是以基数增长了，但是它不会不全前面缺的数，而是一直增张。        M0 [test]> select * from t1;        +----+-------+        | id | name  |        +----+-------+        |  2 | wang  |        |  4 | li         |        |  5 | zhang |        |  7 | huang |        +----+-------+        4 rows in set (0.00 sec)

Summarize:

• Master replication: inter-Master from
  • Problems that are easy to produce: inconsistent data;
• Consider the point: When a table field is set to an automatically growing ID value
  • To configure a node to use an odd ID, you need to add the following in the configuration file
    • Auto_increment_offset=1 Start point
    • auto_increment_increment=2 growth margin
  • The other node uses even IDs and needs to be added in the configuration file as follows
    • auto_increment_offset=2
    • auto_increment_increment=2
• Both servers must have binary logging turned on, and users who are authorized to replicate data

Implementing the MySQL Primary master replication architecture