MySQL database master-slave replication

MySQL master-slave copy function principle
1, in the business complex system, there is a scenario, there is a SQL statement need to lock the table, resulting in the temporary inability to use the Read service, then it will affect the operation of the business, the use of master-slave replication, let the main library is responsible for writing, read from the library, so that even if the main library has a lock table, The normal operation of the business can also be ensured by reading from the library.
2, do the hot preparation of data
3, the extension of the architecture. More and more business volume, I/O access frequency is too high, single machine can not meet, at this time to store more storage, reduce the frequency of disk I/O access, improve the I/O performance of individual machines.
MySQL master-slave replication is an asynchronous replication process in which the main library sends update events to the library, reads the update records from the library, and executes the update records, keeping the contents of the library consistent with the main library.

Host	IP	main software
Primary server	192.168.126.129	mysql-5.7.17, NTP
From the server	192.168.126.130	mysql-5.7.17, NTP

Build MySQL master-slave replication

• Primary server

• Establish a time synchronization environment

  # systemctl stop firewalld.service  //关闭防火墙# setenforce 0# yum -y install ntp （一般系统自带 没有的话安装）# vim /etc/ntp.conf    （添加下面2行）  server 127.127.126.0                    //本地是时钟源//  fudge 127.127.126.0 stratum 8          //设置时间层级为8（限制在15内）//# systemctl restart ntpd    //重启ntp服务

• From the server

• Make time synchronization

      # systemctl stop firewalld.service  //关闭防火墙    # setenforce 0    #  systemctl start ntpd    //开启ntp服务    # yum install ntp ntpdate -y    # /usr/sbin/ntpdate 192.168.126.129    //进行时间同步//

  Installing the MySQL Service
  Installation Detailed: http://blog.51cto.com/13630803/2108180
  Set Password after installation:

      # mysqladmin -u root password ‘123456‘

• Configuring the primary server

   # vim /etc/my.cnf    server-id       = 11    log-bin=master-bin                         //主服务器日志文件//    log-slave-updates=true                     //从服务器更新二进制日志//# systemctl restart mysqld  # mysql -u root -p123456  //登录Mysql 给从服务器授权mysql> GRANT REPLICATION SLAVE ON *.* TO ‘myslave‘@‘192.168.126.%‘ IDENTIFIED BY ‘123456‘;mysql> FLUSH PRIVILEGES;mysql>show master status;

• Configure the slave server

  # vim /etc/my.cnf  server-id       = 22    relay-log=relay-log-bin         //从主服务器上同步日志文件记录到本地//    relay-log-index=slave-relay-bin.index        //定义relay-log的位置和名称//# systemctl restart mysqld

• Press the primary server results to change the following master_log_file and Master_log_pos

      # mysql -u root -p123456    mysql> change master to master_host=‘192.168.126.129‘,master_user=‘myslave‘,master_password=‘123456‘,master_log_file=‘master-bin.000001‘,master_log_pos=604;    mysql> start slave;  //启动同步    mysql> show slave status\G;             //查看状态//

• Test it.
• Create database test on the primary server;
• Go to show databases from the server;
  

MySQL database master-slave replication