Centos7 builds MySQL master-slave replication from scratch (avoid stepping on the pit)

This article is written by the author on some materials of others , When you combine yourself “ Mining pit ” Lessons learned .

First , The main content of this paper comes from MySQL Master slave replication configuration demonstration ,MySQL Master slave replication configuration steps etc. .

Most of the content of the article is the same as that of the previous article and a video , But in some aspects, due to different systems and other reasons, the author's own practice .

Software ：VMware Workstation 15 Player
operating system ：Centos7
edition ：Percona-Server-5.6（MySQL Branch version ）
Pattern ： One master and two slaves

1. build Centos7 System
2. download Percona-Server-5.6
3. build
   One master and two slaves ,master 192.168.133.138; slave1 192.168.133.139; slave2 192.168.133.140
4. MySQL Install package upload ：

rz
#  here Mysql Installation package for master Of  ~  Under the table of contents , Use mv  Command to move it to /usr/local/src  Catalog 
mv Percona-Server-5.6.49-89.0-rd043d30-el7-x86_64-bundle.tar /usr/local/src

#  take master Of mysql Copy the installation package to two slave servers 
scp Percona-Server-5.6.49-89.0-rd043d30-el7-x86_64-bundle.tar [email protected]:/usr/local/src/
scp Percona-Server-5.6.49-89.0-rd043d30-el7-x86_64-bundle.tar [email protected]:/usr/local/src/

5. mysql install

#  Respectively in master, slave1, slave2 Execute the following statement under 
tar xvf Percona-Server-5.6.49-89.0-rd043d30-el7-x86_64-bundle.tar
rpm -ivh Percona-Server-5.6.49-89.0-rd043d30-el7-x86_64-bundle.tar
rpm -ivh Percona-Server-5.6.49-89.0-rd043d30-el7-x86_64-bundle.tar
rpm -ivh Percona-Server-5.6.49-89.0-rd043d30-el7-x86_64-bundle.tar

Be careful ： When the author himself executes the last article , Encountered an error error: Failed dependencies: mariadb-libs is obsoleted by MySQL-shared-compat-5.6.46-1.el7.x86_64, The question is because Centos Originally mysql Of mariadb Installation package , Delete it ：

yum -y remove mariadb-libs

The problem can be solved after execution ;

6.mysql start-up

service mysql start
#  The author can't use it on the system , Redirect directly to the following command 
/bin/systemctl start mysql.service

7. Set up mysql password

mysqladmin -u root password "123"

8. Set up mysql Boot up

chkconfig mysql on
#  The author redirects the system to 
systemctl enable mysql.service

9. to open up 3306 port ( All four sets should be set )
Refer to the article CentOS 7.5 How to open up 80、8080、3306 Wait for the port .

#  because centos7  The default firewall is firewalld,  Therefore, you need to uninstall firewalld, install iptables
systemctl stop firewalld.service
systemctl disable firewalld.service
systemctl mask firewalld.service

yum install iptables-services -y

systemctl enable iptables
systemctl start iptables

vi /etc/sysconfig/iptables
-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT

systemctl restart iptables.service # Restart the firewall for the configuration to take effect 
systemctl enable iptables.service # Set the firewall to power on 

/sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT

10. Turn on mysql Remote access rights ( All four sets should be set )

grant all on *.* to 'root'@'%' identified by 'root';

11. To configure master

vim /etc/my.cnf

###################################
 stay [mysqld] Add two lines of configuration under the node 
server-id=1
log-bin=mysql-bin # Enable binary logging 

 restart 
systemctl restart mysqld.service

 Sign in 
mysql -u root -p

 Database lock table , Don't let write data 
mysql> flush tables with read lock;

 see MASTER state （ remember File Values and Position value , Follow up on Slave Middle configuration ）
mysql> show master status;

12. To configure slave

#  open slave1  Set it up 
vim /etc/my.cnf
##########################
 stay [mysqld] Add a line of configuration under the node ：
server-id=2 # In the cluster server-id Can't repeat 

#  open slave2  Set it up 
vim /etc/my.cnf
##########################
 stay [mysqld] Add a line of configuration under the node ：
server-id=3 # In the cluster server-id Can't repeat 

 Restart two slave  The server 
systemctl restart mysqld.service

 adopt mysql The command configures the direction of the synchronization log ：

mysql> change master to master_host='192.168.133.138', master_port=3306, master_user='root', master_password='123', master_log_file='mysql-bin.000002', master_log_pos=120;

----------------------------
master_host Of the main server IP Address 

master_port Of the main server PORT port 

master_log_file And the main server show master status Medium File Field values are the same 

master_log_pos And the main server show master status Medium Position Field values are the same 

 start-up slave
mysql> start slave;

mysql> show slave status\G;

here , The problem arises , The author's own system is not as described in the article IO Threads and SQL Threads show "YES", It is IO Thread has been in “CONNECTING”, The author looked it up all afternoon , Finally found the reason ：
mysql Unable to connect remotely. Problem （ERROR 1045 (28000): Access denied for user ‘root’）

According to this article , Execute the following command to ：

create user 'root'@'172.17.0.%' identified by '123456'; grant all privileges on *.* to 'root'@'172.17.0.%' with grant option;

At the end of the day , The problem is actually MySQL Version problem , Changes caused by modifying external access rights .

After execution , Restart slave The server will find IO Threads in the “YES” state ;

 Set up Slave As read-only ( but root Users can still make modifications )：

mysql> set global read_only = 1;

13. Contact master lock

mysql> unlock tables;

Now it's done MySQL Master slave copy , And the operation of read-write separation ( Next issue write Redis And MySQL Sync ).