MySQL Master-slave replication

Master -Slave replication principle to establish a TCP long connection with the slave server, through the master server's dump thread thread, copy the binary log to the slave service I/O thread thread, generate relay-log relay log file, The last SQL thread executes the binary log on the Sleve server to achieve a number of synchronizations

One, master-side configuration 1, modify master server master configuration file

  vim /etc/my.cnf    添加如下配置，注意只能在[mysql]配置块下添加    [mysqld]    innodb_file_per_table  #该配置作用，表结构与数据分成两个文件存放    log_bin  #启用二进制日志功能    server_id=0 #服务器编号

2, reboot master server is configured to take effect

    systemctl restart mariadb

3. Create a user account with copy permissions

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

Slave server configuration 1, modifying the configuration file

    vim /etc/my.cnf        添加如下配置，注意只能在[mysql]配置块下添加        [mysqld]          server_id=2  "注意不能跟其他服务器id相同"           innodb_file_per_table

2. Restart the slave service for the configuration to take effect

    systemctl restart mariadb

3. Log in to the MySQL server to execute the following command

     CHANGE MASTER TO      MASTER_HOST=‘host‘,  #指定master服务器IP      MASTER_USER=‘repluser‘, #指定刚才被授权的用户     MASTER_PASSWORD=‘replpass‘, #被授权的用户密码 MASTER_LOG_FILE=‘mariadb-bin.xxxxxx‘, #指定master服务起的哪个二进制日志文件     MASTER_LOG_POS=#; #日志位置可以在master执行一下 show master logs;看下     启动I/O_THREAD和SQL_THREAD线程        

4. View slave status information

    MariaDB [(None)]> start slave; Query OK, 0 rows Affected (0.00 sec) MariaDB [(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.000001 read_master_log_pos:557 relay_log_file:mariadb-relay-bin.000002 relay_log_pos:843 relay_master_log_file:m ariadb-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_l og_pos:557 relay_log_space:1139 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 "This indicates synchronization time 0 means timely 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

Master-slave Replication features:

• Asynchronous replication
• The inconsistency between master and slave data is common

• Slave server I/O thread and SQL thread threads must be up

  Summarize
• master server binary log must be turned on
• Authorization is more accurate for security reasons.

MySQL Master-slave replication